Mysql性能优化——慢查询分析
MYSQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候瓶颈就会出现在网络上，我们可以用mpstat， iostat，sar和 vmstat来查看系统的性能状态。
除了服务器硬件的性能瓶颈，对于Mysql系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用 EXPLAIN分析查询以及调整MYSQL的内部配置。
一、查询与索引优化分析
在优化mysql时，通常需要对数据库进行分析，常见的分析手段有慢查询日志， EXPLAIN分析查询， profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
性能瓶颈定位
1、show命令
通过show命令查看mysql状态及变量，找到系统的瓶颈，
Mysql > show variables; 查看mysql服务器配置信息
Mysql > show global status; 查看mysql服务器运行的各种状态值
#mysqadmin variables -u username -ppassword ——显示系统变量
#mysqladmin extended-status -u username -ppassword ——显示状态信息
其余show命令可以参考：mysql >help show
2、慢查询日志
慢查询日志开启方法一：
在配置文件my.cnf中[mysqld]下加入以下内容并重启mysql服务
Slow_query_log=1 //0关闭 1开启
Slow_query_log_file=/usr/local/mysql/data/slow-query.log //慢查询日志存放地点
Long_query_time=1 //表示查询超过1秒才记录
注：在my.cnf中添加log-queries-not-using-indexes参数，表示向慢查询日志中记录没有使用索引的查询。
慢查询日志开启方法二：
通过命令行设置变量来即时启动慢查询日志
查看慢查询的设置信息
打开慢查询日志文件查看
从日志中，可以发现查询时间超过0.01秒的sql，而小于0.01秒的没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用 mysqldumpslow工具（Msql客户端安装自带）来对慢查询日志进行分类汇总。 mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。（具体参数可以参考#mysqldumpslow --help）
优化上面的慢查询
二、explain分析查询
使用explain可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。可以帮助分析查询语句或表结构的性能结果。
通过explain查看，发现没有使用索引查询，而是全表扫描。
优化：在stuname列上创建索引
Mysql > create index index_stuname ontest1.tb1(stuname)；
再次执行explain
发现查询语句使用了index_stuname索引查询而非全表查询。
三、Profiling分析查询
如果觉得explain的信息不够详细，可以通过profiling命令得到更准确的sql执行消耗系统资源的信息
查看profiling是否开启
打开profiling功能：mysql >set profiling=1；并查看是否开启
执行测试语句：
四、配置优化
下面列出了对性能优化影响较大的主要变量，分为连接请求的变量和缓冲区变量
1、连接请求变量
Max_connections //mysql的最大连接数
Back_log //mysql能暂存的连接数量
Wait_timeout //mysql在关闭一个非交互的连接之前所要等待的秒数
Wait_timeout //mysql在关闭一个交互的连接之前所要等待的秒数
2、缓冲区变量
Key_buffer_size //索引缓冲区的大小
Query_cache_size //查询缓存简称QC
Max_connect_errors //阻止过多尝试失败的客户端，防止暴力破解密码
Sort_buffer_size //每个需要进行排序的线程分配该大小的缓冲区
Max_allowed_packet=32M //限制server接受的数据包大小
Join_buffer_size=2M //用于表间关联缓存的大小
Thread_cache_size=300 //可以重新利用保存在缓存中线程的数量
3、配置InnoDB的变量
Innodb_buffer_pool_size //指定大小的内存来缓冲数据和索引
Innodb_flush_log_at_trx_commit //控制inodb将log buffer总的数据写入日志文件并flush磁盘的时间点
Innodb_thread_concurrency=0 //设置innodb线程的并发数量
Innodb_log_buffer_size //确定日志文件所用的内存大小
Innodb_log_file_size=50M //确定数据日志文件的大小
Innodb_log_files_in_group=3 //以循环方式将日志文件写到多个文件
Read_buffer_size=1M //mysql读入缓冲区大小
Read_rnd_buffer_size=16M //mysql的随机读缓存区大小
Bulk_insert_buffer_size=64M //批量插入数据缓存大小
在优化之前执行mysqlslap工具测试
相关优化参数总结：
