MYSQL性能优化 慢查询分析
1）性能瓶颈定位
Show 命令 慢查询日志 explain 分析查询 profiling 分析查询
2）索引及查询优化 3）配置优化
MySQL 数据库是常见的两个瓶颈是 CPU 和 I/O 的瓶颈，CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。
可以用mpstat, iostat, sar 和 vmstat 来查看系统的性能状态。
iostat,
优化数据库的性
能，通常有三种：使用索引，使用 EXPLAIN 分析查询以及调整 MySQL 的内部配置。
1 、 查询与索引优化分析
在优化 MySQL 时，通常需要对数据库进行分析EXPLAIN 分析查询，profiling 分析以及 show 命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
查看 MySQL 服务器配置信息 mysql> show variables;
查看 MySQL 服务器运行的各种状态值 mysql> show global status;
# mysqladmin variables -u username -ppassword——显示系统变量
# mysqladmin extended-status -u username -ppassword——显示状态信息
比较全的 show 命令的使用可参考：mysql>help show
或 http://dev.mysql.com/doc/refman/5.7/en/show.html
慢查询日志开启：
在配置文件 my.cnf 中在 [mysqld] 一行下面加入 3 个配置参数，并重启 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 参数，表示向慢查询日志中记录下没有使用索引的查询。
二 查看慢查询的设置信息
mysql> show variables like '%slow_query_log%';
mysql> show variables like '%long_query_time%';
化这个 sql 查询语句
mysql> select * from test1.tb1 where entertime < '2016-9-3' or entertime > '2016-9-3';
explain 分析查询
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的
SQL 语句的。
explain 模拟优化器执行 sql 语句来看是没有使用索引查询的，而是全表扫描
优化方法：在 stuname 列上创建索引
mysql> create index index_stuname on test1.tb1(stuname);
再次执行 explain
mysql> explain select * from test1.tb1 where stuname='admin'\G;
profiling 分析查询
通过慢日志查询可以知道哪些 SQL 语句执行效率低下，通过 explain 我们可以得知 SQL 语句具体执行情况，索引使用等，还可以结合 show 命令查看执行状态。
select @@profiling; //0 表示未开启
执行要测试的 sql 语句
mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';
mysql> show profiles\G; //可以得到被执行的 SQL 语句的时间和 ID
status:是 profile 里的状态，duration：是 status 状态下的耗时。
Mysql配置优化
Mysql 参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况，以及机器硬件配置都有关系，优化不可能一次性完成，需要不断的观察以及调试才有可能得到最佳效果。
1） 连接请求的变量：
1. max_connections
数值过小会经常出现 ERROR 1040: Too many connections 错误，可以过 mysql> show status like
'connections';通配符查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数)，以定夺该值的大小。
最大连接数
max_used_connections / max_connections * 100% （理想值≈ 85%）
如果 max_used_connections 跟 max_connections 相同那么就是 max_connections 设置过低或者超过服务器负载上限了，低于 10%则设置过大。
2.back_log
MySQL 能暂存的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求，它就会起作用。
如何设置 back_log?
修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容，如设置最大连接数为 1024back_log = 数值 重启
3.wait_timeout 和 interactive_timeout
wait_timeout -- 指的是 MySQL在关闭一个非交互的连接之前所要等待的秒数interactive_time -- 指的是 mysql 在关闭一个交互的连接之前所要等待的秒数。
wait_timeout：
（1）如果设置大小，那么连接关闭的很快，从而使一些持久的连接不起作用
（2）如果设置太大，容易造成连接打开时间过长，在 show processlist 时，能看到太多的 sleep
状态的连接，从而造成 too many connections 错误
3）一般希望 wait_timeout 尽可能地低
interactive_timeout 的设置将要对你的 web application 没有多大的影响
查看 wait_timeout 和 interactive_timeout
mysql> show variables like '%wait_tmeout%';
mysql> show variables like '%interactive_timeout%';
如何设置 wait_timeout 和 interactive_timeout ?
修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容
wait_timeout=100
interactive_timeout=100
重启 MySQL Server 进入后，查看设置已经生效。
全局缓冲：
4.key_buffer_size
key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 设置是否合理。
一共有 6 个索引读取请求，有 3 个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。可以使用检查状态值 created_tmp_disk_tables
调整 key_buffer_size
默认配置数值是 8388608(8M)，主机有 4GB 内存，可以调优值为 268435456(256MB)修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容
5. query_cache_size(查询缓存简称 QC)
使用查询缓冲，MySQL 将查询结果存放在缓冲区中，今后对于同样的 SELECT 语句（区分大小写），将直接从缓冲区中读取结果。
如果该值显示较大，则说明 Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE 会对缓存中的碎片进行整理
Qcache_queries_in_cache：当前 Query Cache 中 cache 的 Query 数量；Qcache_total_blocks：当前 Query Cache 中的 block 数量；。查询服务器关于 query_cache 的配置：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小 ，query_cache_min_res_unit 的配置是一柄”双刃剑”，默认是 4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
在优化之前执行 mysqlslap 工具进行测试
优化之后执行 mysqlslap 工具进行测试
本文转自柴鑫旺 51CTO博客，原文链接：http://blog.51cto.com/chaixinwang/1943286，如需转载请自行联系原作者
