mysql性能优化-慢查询分析、优化索引和配置.doc

Forpersonaluseonlyinstudyandresearch;mercialusemysql性能优化-慢查询分析、优化索引和配置目录一、优化概述二、查询与索引优化分析1性能瓶颈定位Show命令慢查询日志explain分析查询profiling分析查询 2索引及查询优化三、配置优化1)   max_connections2)   back_log3)   interactive_timeout4)   key_buffer_size5)   query_cache_size6)   record_buffer_size7)   read_rnd_buffer_size8)   sort_buffer_size9)   join_buffer_size10)  table_cache11)  max_heap_table_size12)  tmp_table_size13)  thread_cache_size14)  thread_concurrency15)  wait_timeout 一、 优化概述MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上,我们可以用mpstat,iostat,sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈,对于MySQL系统本身,我们可以使用工具来优化数据库的性能,通常有三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置。二、查询与索引优化分析在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。1性能瓶颈定位Show命令我们可以通过show命令查看MySQL状态及变量,找到系统的瓶颈:Mysql>showstatus——显示状态信息(扩展showstatuslike‘XXX’)Mysql>showvariables——显示系统变量(扩展showvariableslike‘XXX’)Mysql>showinnodbstatus——显示InnoDB存储引擎的状态Mysql>showprocesslist——查看当前SQL执行,包括执行状态、是否锁表等Shell>mysqladminvariables-uusername-ppassword——显示系统变量Shell>mysqladminextended-status-uusername-ppassword——显示状态信息查看状态变量及帮助:Shell>mysqld–verbose–help[|more#逐行显示] 比较全的Show命令的使用可参考:http://blog.//18/慢查询日志慢查询日志开启:[mysqld]一行下面加入两个配置参数log-slow-queries=/data/mysqldata/slow-           long_query_time=2                                                                注:log-slow-queries参数为慢查询日志存放的位置,一般这个目录要有mysql的运行帐号的可写权限,一般都将这个目录设置为mysql的数据存放目录;long_query_time=2中的2表示查询超过两秒才记录;-queries-not-using-indexes参数,表示记录下没有使用索引的查询。log-slow-queries=/data/mysqldata/slow-           long_query_time=10                                                               log-queries-not-using-indexes                                             慢查询日志开启方法二:我们可以通过命令行设置变量来即时启动慢日志查询。由下图可知慢日志没有打开,slow_launch_time=# 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads计数器将增加设置慢日志开启MySQL后可以查询long_query_time的值。 为了方便测试,可以将修改慢查询时间为5秒。慢查询分析mysqldump