mysql 的慢查询调优

原文：http://blog.csdn.net/zm2714/article/details/8510893

 

慢查询定义及作用

慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

 

开启慢查询日志

在mysql的配置文件(linux是my.cnf、windows是my.ini)中的mysqld下方添加以下参数:

#这个参数设置为ON，或1，可以捕获执行时间超过一定数值的SQL语句

slow_query_log = 1

#当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短，单位秒，缺省是10秒。

long_query_time = 1

#这个参数设置为ON或1，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

log_queries_not_using_indexes = 1

#记录慢查询日志的文件名

slow_query_log_file=/data/mysql/log/slow-query.log

 

注意

log-slow-queries <slow_query_log_file>指定日志文件存放位置，该目录文件一定要有写的权限。可以不用设置，系统会给一个缺省的文件host_name-slow.log ；这2个参数类似，一般都知道明确的文件日志位置；

 

给mysql用户组的mysql用户对某个目录或文件，赋读写权限；

chown -R mysql:mysql /var/log/slow-query.log 
chmod 660 /var/log/ slow-query.log 

给mysql用户组的mysql用户的某个目录或文件赋写权限； 

chown -R mysql:mysql /var/log/slow-query.log

chmod +w /var/log/slow-query.log

 

log-long-format

简单的说log-long-format选项是用来设置日志的格式，它是以扩展方式记录有关事件。扩展方式可记录谁发出查询和什么时候发出查询的信息。可使我们更好地掌握客户端的操作情况。

准确的说，它是记录激活的更新日志、二进制更新日志、和慢查询日志的大量信息。例如，所有查询的用户名和时间戳将记录下来。不赞成选用该选项，因为它现在代表 默认记录行为。

log-short-format

记录激活的更新日志、二进制更新日志、和慢查询日志的少量信息。例如，用户名和时间戳不记录下来。

 

配置文件修改后必须保存、重启mysql服务器；

查看日志方法：

方法1：tail -f /data/mysql/log/slow-query.log  通过这种tail -f    日志文件    命令模式可以查看；

 

# Time: 151028 10:46:06

# User@Host: root[root] @  [172.168.48.196]  Id:   298

# Query_time: 0.104143  Lock_time: 0.000479 Rows_sent: 5  Rows_examined: 9502

SET timestamp=1446000366;

SELECT CASE WHEN c.street IS NULL THEN CONCAT(c.province,c.city,c.town,c.detailAddress) ELSE CONCAT(c.province,c.city,c.town,c.street,c.detailAddress)  END  AS customerAddress,u.userId as workerId,u.realName repairman_name,u.telephone repairman_phone,s.arrAdd repairman_address FROM dispatch_info a JOIN  user_info u ON (a.serverId=u.sermanagerId OR a.serverId=u.userId)   AND a.object_id='312497904'  AND u.isDelete=0 JOIN customer_info c ON a.customerId=c.customerId LEFT OUTER JOIN (SELECT us.id,us.arrAdd,us.workId FROM user_sign us,(SELECT MAX(id) as id from user_sign GROUP BY workId)uus where us.id=uus.id ) s ON u.userId=s.workId;

 

 

第一行,SQL查询执行的时间 
第二行,执行SQL查询的连接信息 
第三行记录了一些我们比较有用的信息 
Query_time SQL执行的时间,越长则越慢 
Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间 
Rows_sent 查询返回的行数 
Rows_examined 查询检查的行数

 

方法2：不用重启数据库，通过设置mysql的全局变量模式来查看；

登陆到mysql上执行一下sql脚本即可

mysql> set global slow_query_log=ON;

mysql> set global long_query_time=1;

mysql> set global log_queries_not_using_indexes = 1;     #打开没有使用索引的sql

然后通过一下命令查看是否成功

查看long_query_time 的值

mysql> show variables like '%long%';

+--------------------------------------------------------+----------+

| Variable_name                                          | Value    |

+--------------------------------------------------------+----------+

| long_query_time                                        | 1.000000 |

| performance_schema_events_stages_history_long_size     | 10000    |

| performance_schema_events_statements_history_long_size | 10000    |

| performance_schema_events_waits_history_long_size      | 10000    |

+--------------------------------------------------------+----------+

 

mysql> show variables like 'slow%';

+---------------------+--------------------------------+

| Variable_name       | Value                          |

+---------------------+--------------------------------+

| slow_launch_time    | 2                              |

| slow_query_log      | ON                             |

| slow_query_log_file | /data/mysql/log/slow-query.log |

+---------------------+--------------------------------+

 

方法3：

随着mysql数据库服务器运行时间的增加，可能会有越来越多的SQL查询被记录到了慢查询日志文件中，这时要分析该文章就显得不是很容易了。mysql提供的mysqldumpslow命令，可以很好地解决这个问题。

 

使用方法如下：

命令行下，进入 mysql/bin 目录，输入 mysqldumpslow ?help 或 --help 可以看到这个工具的参数

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log 

上述命令可以看出访问次数最多的 20 个 sql 语句和返回记录集最多的 20 个 sql 

mysqldumpslow -t 10 -s t -g "left join" host-slow.log

这个是按照时间返回前 10 条里面含有左连接的 sql 语句。

mysqldumpslow -s c -t 10 /database/mysql/slow-log

这会输出记录次数最多的10条SQL语句，其中：

-s order，是表示按照何种方式排序，order值有：c、t、l、r 分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；
-t num，即为返回前面多少条的数据；
-g pattern，pattern可以写一个正则匹配模式，大小写不敏感的；

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化的第一步，也是非常重要的一步。

相关命令

查看慢查询的记录数

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| Slow_launch_threads | 0     |

| Slow_queries        | 3444  |

+---------------------+-------+

2 rows in set (0.00 sec)

 

查看log_queries_not_using_indexes状态

mysql> show variables like 'log_queries_not_using_indexes';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| log_queries_not_using_indexes | ON    |

+-------------------------------+-------+

1 row in set (0.00 sec)