MySQL5.6如何优化慢查询的SQL语句--慢日志介绍_MySQL

最近有个开发团队抱怨我们平台包含的mysql cluster不行，总是报mysql的”heartbeat Error“，分析了他们收集的日志，没有发现mysql cluster节点之间的网络有问题，倒是发现了一个很有意思的现象，在mysql的slow log里面发现了大量的慢查询，基本上都是要1秒钟以上，很明显这种耗时高的慢查询会影响系统行为，指出这点让开发团队去优化他们的应用，很好奇他们竟然不知道有这些东西可以分析。。。。。。

数据库响应慢问题最多的就是查询了。现在大部分数据库都提供了性能分析的帮助手段。例如Oracle中会帮你直接找出慢的语句，并且提供优化方案。在MySQL中就要自己开启慢日志记录加以分析（记录可以保存在表或者文件中，默认是保存在文件中，我们系统使用的就是默认方式）。

先看看MySQL慢查询日志里面的记录长什么样的：

Time                 Id Command    Argument
# Time: 141010  9:33:57
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 0.000342  Lock_time: 0.000142 Rows_sent: 3  Rows_examined: 3
use test;
SET timestamp=1412904837;
select * from t;

这个日志应该很好理解了，第一个#记录时间戳，第二个#记录执行命令的用户和地址信息，第三个#记录执行查询的时间、锁的时间、返回行数、被扫描的行数。接着后面记录真正执行的SQL语句。还可以通过以下命令看看cvs存储格式每个字段意义。

SHOW CREATE TABLE mysql.slow_log;

接下来说说如何获取和分析慢日志吧。

查看MySQL慢日志参数

进入启动好的MySQL，执行以下命令

mysql> show variables like '%slow_query%';
+---------------------------+----------------------------------------+
| Variable_name             | Value                                  |
+---------------------------+----------------------------------------+
| slow_query_log            | OFF                                    |
| slow_query_log_file       | /usr/local/mysql/data/cloudlu-slow.log |
+---------------------------+----------------------------------------+

这里告诉我们慢日志的日志存放位置，慢日志是否有开启。

那么什么样的查询需要被日志呢？在MySQL中，没有index的查询以及超过指定时间同时超过指定扫描行数的查询需要记录在慢日志查询里面。

那么它们的参数又是怎么查看的呢？

没有index的查询记录开关

mysql> show global variables like '%indexes%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+

第一个参数表示是否开启记录没有index的查询，第二个参数用来做日志记录的流量控制，一分钟可以记录多少条，默认0是表示不限制。

超过指定时长的查询开关

mysql> show global variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

就一个参数指定超过多少时长的查询需要被记录

超过指定行数的扫描查询开关

mysql> show variables like  '%min_examined_row_limit%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

默认是0，代表不现在扫描行数

设置开启MySQL慢日志参数

进入MySQL，输入以下命令或者在MySQL的启动配置文件里面修改或者给MySQL添加启动参数，进入MySQL后的修改如下：

set global long_query_time=0.1;
set global log_queries_not_using_indexes=on;
set global slow_query_log = on;

这里要斟酌的有2点，第一是超过什么时长的日志是有问题的，这个由系统需求来决定。第二是没有使用indexes的日志每分钟要记录多少条，要防止日志太多对性能产生影响。