MySQL培训资料(具体优化部分)

1北京中企开源信息技术有限公司MySQL数据库调优介绍北京中企开源信息技术有限公司2北京中企开源信息技术有限公司大纲•MySQL参数调整•MySQL运行状态监控•MySQL的SQL性能优化•MySQL的视图、存储过程和触发器•MySQL函数简介•MySQL的常用工具•问题解答3北京中企开源信息技术有限公司MySQL参数调整•MySQL参数说明参数个数在不断增多和废弃，从MySQL5.0.37的227个增加到5.1.30的267个，比较常见的一个例子是：MySQL4使用的一个参数log_long_format，从MySQL5开始已经不再使用，被log_queries_not_using_indexes代替了4北京中企开源信息技术有限公司MySQL参数调整•MySQL参数查看方法1,查询所有参数和帮助说明命令例如：/usr/sbin/mysqld--help–verbose2，MySQL中执行查询命令mysqlshowvariables;5北京中企开源信息技术有限公司MySQL参数调整•常用参数说明1.最大连接数:max_connections2.缺省字符集：default-character-set3.缺省存储引擎：default-storage-engine4.键缓存大小：key_buffer_size6北京中企开源信息技术有限公司MySQL参数调整•常用参数说明5.innodb缓冲池大小:innodb_buffer_pool_size6,innodb引擎字典缓存innodb_additional_mem_pool_size7.查询缓存大小：query_cache_size8.设置表缓存table_cache9.慢速查询时间：long_query_time10.慢速查询日志：log_slow_queries11.超时设置参数：wait_timeoutinteractive_timeout7北京中企开源信息技术有限公司MySQL参数调整•怎样修改参数1,直接使用推荐的参数配置文件my-small.ini=64Mmy-medium.ini32M-64Mmy-large.ini512Mmy-huge.ini1G-2Gmy-innodb-heavy-4G.ini4GBRPM安装方式在目录/usr/share/mysql下8北京中企开源信息技术有限公司MySQL参数调整•怎样修改参数2,永久修改参数修改参数文件：Windows安装目录\my.inilinux/etc/my.cnf3,在线修改setsession参数名=修改值setglobal参数名=修改值9北京中企开源信息技术有限公司MySQL运行状态监控•1,查看累计状态值mysqlstatus;Threads:89Questions:223522728Slowqueries:50965Opens:287992Flushtables:1Opentables:40000Queriespersecondavg:133.05710北京中企开源信息技术有限公司MySQL运行状态监控•2，查看线程状态mysqlshowprocesslist;11北京中企开源信息技术有限公司MySQL运行状态监控3，查看其它运行状态值mysqlshowglobalstatus;12北京中企开源信息技术有限公司MySQL运行状态监控4，查看InnoDB引擎运行状态值mysqlshowinnodbstatus\G;13北京中企开源信息技术有限公司MySQL的sql优化•查看执行计划mysqlexplainselect*from…where…mysqldescselect*from…where…14北京中企开源信息技术有限公司MySQL的sql优化•创建索引主键外键复合索引前缀索引createindexind_company2_nameoncompany2(name(4));全文索引15北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧1、选取最适用的字段数据类型整型优先于文本类型char类型优先于varchar类型，varchar类型优先于text类型字段长度能短勿长尽量定义为notnull所有字段为定长类型的MyiSam表存取最快性别等字段使用ENUM类型16北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧2,定期分析整理表analyzetable表名;optimizetable表名;17北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧3、使用连接（JOIN）来代替子查询(Sub-Queries)子查询：SELECT*FROMcustomerinfoWHERECustomerIDin(SELECTCustomerIDFROMsalesinfo)改写成：SELECTCi.*FROMcustomerinfoci,salesinfosfWHEREci.CustomerID=sf.CustomerID思考：如果是notin查询应该怎么改写？18北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧4,在建有索引的字段上不要进行表达式运算不好：SELECT*FROMorderWHEREYEAR(OrderDate)2001;好：SELECT*FROMorderWHEREOrderDate2001-01-01;不好：SELECT*FROMinventoryWHEREAmount/724;好：SELECT*FROMinventoryWHEREAmount24*7;19北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧5,避免在查询条件中进行强制类型转换SELECT*FROMstudentsWHERESNO=1000;SNO定义为char(4);20北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧6，正确使用LIKE关键字和通配符带like的sql:SELECT*FROMbooksWHEREnamelikeMySQL%修改为：SELECT*FROMbooksWHEREname=MySQLandnameMySQM21北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧7，使用联合(UNION)来代替手动创建的临时表SELECTName,PhoneFROMclientUNIONSELECTName,BirthDateFROMauthorUNIONSELECTName,SupplierFROMproductMySQL从4.0的版本开始支持UNION查询22北京中企开源信息技术有限公司MySQL的sql优化•一些常用的mysql优化技巧8，使用事务批量插入记录INSERTINTO`T_VISITIP`VALUES(38094,'10.10.10.0',10,'2007-02-28'),(38095,'10.10.10.1',10,'2007-02-28'),(38096,'10.10.10.2',10,'2007-02-28'),(38097,'10.10.10.3',10,'2007-02-28'),………………………………..;23北京中企开源信息技术有限公司MySQL的sql优化•begin;INSERTINTOaVALUES(1,23),(2,34),(4,33);INSERTINTOaVALUES(8,26),(6,29);………………………commit;LOCKTABLESaWRITE;INSERTINTOaVALUES(1,23),(2,34),(4,33);INSERTINTOaVALUES(8,26),(6,29);UNLOCKTABLES;24北京中企开源信息技术有限公司MySQL的视图、存储过程和触发器•视图定义：视图是一种虚拟存在的表，本身不保存数据，数据来自基表，可以象表一样的访问。MySQL5.0开始支持。优点：1，简洁。2，安全性。3，数据独立性。25北京中企开源信息技术有限公司MySQL的视图、存储过程和触发器•视图一个视图定义例子：CREATEORREPLACEVIEWstaff_list_viewASSELECTs.staff_id,s.first_name,s.last_name,a.addressFROMstaffASs,addressASaWHEREs.address_id=a.address_id;26北京中企开源信息技术有限公司MySQL的视图、存储过程和触发器•视图1，视图的更新有很多限制，简单的视图可以更新，比如包含聚合函数(SUM,MIN,MAX,COUNT等),DISTINCT,GROUPBY,HAVING,UNION或者UNIONALL的视图就不能更新2，显示视图命令mysqlshowtablestatus;mysqlshowtablestatuslike‘staff_list’\Gmysqlshowcreateviewstaff_list\G27北京中企开源信息技术有限公司MySQL的视图、存储过程、函数和触发器•存储过程和函数定义：存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。优点：1，简化前端应用程序2，减少数据传输，提高效率28北京中企开源信息技术有限公司MySQL的视图、存储过程、函数和触发器•创建存储过程的例子：•mysqldelimiter$$•mysqlCREATEPROCEDUREprocedure1•-(INparameter1INTEGER)•-BEGIN•-DECLAREvariable1CHAR(10);•-IFparameter1=17THEN•-SETvariable1='birds';•-ELSE•-SETvariable1='beasts';•-ENDIF;•-INSERTINTOtable1VALUES(variable1);•-END$$•QueryOK,0rowsaffected(0.04sec)•mysqldelimiter;29北京中企开源信息技术有限公司MySQL的视图、存储过程、函数和触发器•调用存储过程方法mysqlcallprocedure1(15);•查看存储过程命令mysqlshowprocedurestatus;mysqlshowprocedurestatuslike‘procedure1’\G;mysqlshowcreateprocedureprocedure1;30北京中企开源信息技术有限公司MySQL的视图、存储过程、函数和触发器•存储过程中游标的使用例子•createprocedurepayment_stat()•begin•declarei_staff_idint;•declared_amountdecimal(5,2);•declarecur_paymentcursorforselectstaff_id,amountfrompayment;•declareexithandlerfornotfoundclosecur_payment;•set@x1=0;•set@x2=0;•opencur_payment;•repeat•fetchcur_paymentintoi_staff_id,d_amount;•ifi_staff_id=2then•set@x1=@x1+d_amount;•else•set@x2=@x2+d_amount;•endif;•until0endrepeat;•closecur_payment;•end;31北京中企开源信息技术有限公司MySQL函数简介•函数分类1，字符串函数2，数值函数3，日期和时间函数4，流程函数5，其它常用函数32北京中企开源信息技术有限公司MySQL的常用工具•推荐使用以下工具1，EMSSQLManager2005LiteforMySQL2，MySQLAdmi