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

MySQL数据库调优介绍,北京中企开源信息技术有限公司,大纲,MySQL参数调整MySQL运行状态监控MySQL的SQL性能优化MySQL的视图、存储过程和触发器MySQL函数简介MySQL的常用工具问题解答,MySQL参数调整,MySQL参数说明参数个数在不断增多和废弃，从MySQL5.0.37的227个增加到5.1.30的267个，比较常见的一个例子是：MySQL4使用的一个参数log_long_format，从MySQL5开始已经不再使用，被log_queries_not_using_indexes代替了,MySQL参数调整,MySQL参数查看方法1,查询所有参数和帮助说明命令例如：/usr/sbin/mysqld-helpverbose2，MySQL中执行查询命令mysqlshowvariables;,MySQL参数调整,常用参数说明1.最大连接数:max_connections2.缺省字符集：default-character-set3.缺省存储引擎：default-storage-engine4.键缓存大小：key_buffer_size,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_timeout,MySQL参数调整,怎样修改参数1,直接使用推荐的参数配置文件my-small.inistatus;Threads:89Questions:223522728Slowqueries:50965Opens:287992Flushtables:1Opentables:40000Queriespersecondavg:133.057,MySQL运行状态监控,2，查看线程状态mysqlshowprocesslist;,MySQL运行状态监控,3，查看其它运行状态值mysqlshowglobalstatus;,MySQL运行状态监控,4，查看InnoDB引擎运行状态值mysqlshowinnodbstatusG;,MySQL的sql优化,查看执行计划mysqlexplainselect*fromwheremysqldescselect*fromwhere,MySQL的sql优化,创建索引主键外键复合索引前缀索引createindexind_company2_nameoncompany2(name(4);全文索引,MySQL的sql优化,一些常用的mysql优化技巧1、选取最适用的字段数据类型整型优先于文本类型char类型优先于varchar类型，varchar类型优先于text类型字段长度能短勿长尽量定义为notnull所有字段为定长类型的MyiSam表存取最快性别等字段使用ENUM类型,MySQL的sql优化,一些常用的mysql优化技巧2,定期分析整理表analyzetable表名;optimizetable表名;,MySQL的sql优化,一些常用的mysql优化技巧3、使用连接（JOIN）来代替子查询(Sub-Queries)子查询：SELECT*FROMcustomerinfoWHERECustomerIDin(SELECTCustomerIDFROMsalesinfo)改写成：SELECTCi.*FROMcustomerinfoci,salesinfosfWHEREci.CustomerID=sf.CustomerID思考：如果是notin查询应该怎么改写？,MySQL的sql优化,一些常用的mysql优化技巧4,在建有索引的字段上不要进行表达式运算不好：SELECT*FROMorderWHEREYEAR(OrderDate)2001;好：SELECT*FROMorderWHEREOrderDate2001-01-01;不好：SELECT*FROMinventoryWHEREAmount/7showtablestatuslikestaff_listGmysqlshowcreateviewstaff_listG,MySQL的视图、存储过程、函数和触发器,存储过程和函数定义：存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。优点：1，简化前端应用程序2，减少数据传输，提高效率,MySQL的视图、存储过程、函数和触发器,创建存储过程的例子：mysqldelimiter$mysqlCREATEPROCEDUREprocedure1-(INparameter1INTEGER)-BEGIN-DECLAREvariable1CHAR(10);-IFparameter1=17THEN-SETvariable1=birds;-ELSE-SETvariable1=beasts;-ENDIF;-INSERTINTOtable1VALUES(variable1);-END$QueryOK,0rowsaffected(0.04sec)mysqldelimiter;,MySQL的视图、存储过程、函数和触发器,调用存储过程方法mysqlcallprocedure1(15);查看存储过程命令mysqlshowprocedurestatus;mysqlshowprocedurestatuslikeprocedure1G;mysqlshowcreateprocedureprocedure1;,MySQL的视图、存储过程、函数和触发器,存储过程中游标的使用例子createprocedurepayment_stat()begindeclarei_staff_idint;declared_amountdecimal(5,2);declarecur_paymentcursorforselectstaff_id,amountfrompayment;declareexithandlerfornotfoundclosecur_payment;setx1=0;setx2=0;opencur_payment;repeatfetchcur_paymentintoi_staff_id,d_amount;ifi_staff_id=2thensetx1=x1+d_amount;elsesetx2=x2+d_amount;endif;until0endrepeat;closecur_payment;end;,MySQL函数简介,函数分类1，字符串函数2，数值函数3，日期和时间函