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

《mysql性能优化-慢查询分析、优化索引和配置》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、mysql性能优化-慢查询分析、优化索引和配置目录一、优化概述二、查询与索引优化分析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_buf

2、fer_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,sa

3、r和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。二、查询与索引优化分析在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。1性能瓶颈定位Show命令我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：Mysql>showst

4、atus——显示状态信息（扩展showstatuslike‘XXX’）Mysql>showvariables——显示系统变量（扩展showvariableslike‘XXX’）Mysql>showinnodbstatus——显示InnoDB存储引擎的状态Mysql>showprocesslist——查看当前SQL执行，包括执行状态、是否锁表等Shell>mysqladminvariables-uusername-ppassword——显示系统变量Shell>mysqladminextended-status-uusername-ppassword——显

5、示状态信息查看状态变量及帮助：Shell>mysqld–verbose–help[

6、more#逐行显示] 比较全的Show命令的使用可参考：http://blog.phpbean.com/a.cn/18/慢查询日志慢查询日志开启：在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数log-slow-queries=/data/mysqldata/slow-query.log           long_query_time=2                                                 

7、               注：log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；long_query_time=2中的2表示查询超过两秒才记录；在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。log-slow-queries=/data/mysqldata/slow-query.log           long_query_time=10           

8、                                                    log-queries-not-using-indexes                                             慢查询日志开启方法二：我们可以通过命令行设置变量来即时启动慢日志查询。由下图可知慢日志没有打开，slow_launch_time=# 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads计数器将增加设置慢日志开启MySQL后可以查询long_query_time的值。 为了方便

9、测试，可以将修改慢查询时间为5秒。慢查询分析mysqldumpslow我们可以通过打开log文