MySQL的慢查询日志可以用来找出执行时间过长的查询语句,并进行针对性的优化。
一、slow log相关参数
以下参数都是动态参数,可以在实例运行时修改。
slow_query_log=1 #是否启用慢查询日志,1为启用,0为禁用
slow_query_log_file=slow.log #指定慢查询日志文件的路径和名字,可使用绝对路径指定;默认值是'主机名_slow.log',位于datadir目录
long_query_time=2 #SQL语句运行时间阈值,执行时间大于参数值的语句才会被记录下来
min_examined_row_limit=100 #SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志,即使这个语句执行时间超过了long_query_time的阈值
log_queries_not_using_indexes=1 #将没有使用索引的语句记录到慢查询日志
log_throttle_queries_not_using_indexes=10 #设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间
log-slow-admin-statements=1 #记录执行缓慢的管理SQL,如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。
log_slow_slave_statements=0 #记录从库上执行的慢查询语句
log_timestamps=system #5.7版本新增时间戳所属时区参数,默认记录UTC时区的时间戳到慢查询日志,应修改为记录系统时区
log_output=FILE,TABLE #指定慢查询日志的输出方式,从5.5版本开始可以记录到日志文件(FILE,慢查询日志)和数据库表(TABLE,mysql.slow_log)中
二、使用MySQL slow log
1. 在线修改数据库相关参数:
mysql> set global slow_query_log=1;
mysql> set global slow_query_log_file='my57-slow.log';
mysql> set global long_query_time=2;
mysql> set global min_examined_row_limit=50;
mysql> set global log_queries_not_using_indexes=1;
mysql> set global log_throttle_queries_not_using_indexes=10;
mysql> set global log_slow_admin_statements=1;
2. 查看slow log内容:
/usr/local/mysql/bin/mysqld, Version: 5.7.17 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql1.sock
Time Id Command Argument
3. 执行一个很慢的SQL后,slow log记录的内容:
# Time: 2017-03-13T09:48:23.823837Z
# User@Host: root[root] @ localhost [] Id: 7
# Query_time: 2.898604 Lock_time: 0.000295 Rows_sent: 2700216 Rows_examined: 300033
use emp;
SET timestamp=1489398503;
select * from employees,departments;
# Time: 2017-03-13T09:49:13.610594Z
# User@Host: root[root] @ localhost [] Id: 7
# Query_time: 0.529270 Lock_time: 0.000228 Rows_sent: 300024 Rows_examined: 300024
SET timestamp=1489398553;
select * from employees;
可以看到上面的例子中记录了两条SQL语句,第一句符合了执行时间大于2秒的阈值,第二句符合了没有使用索引的限制。与在第一步中设置的参数值相符。
三、根据MySQL slow log记录进行优化
从慢查询日志中找到执行时间过长或没有使用索引的语句后,我们可以通过分析执行计划对语句进行调优:
mysql> explain extended select * from employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 2 warnings (0.02 sec)
四、使用slow log的Tips
1. 慢查询日志可能随着系统运行时间而增长的很大,因此需要定期做日志轮转。
在线轮转慢查询日志的方法为:
a. 修改slow log文件名
b. 执行flush slow logs;命令打开新的日志文件
2. 慢查询日志是文本文件,可以使用mysqldumpslow命令对日志文件进行汇总处理,以减轻分析日志的工作量。