1.慢查询日志介绍
简单地理解,慢查询日志(slow query log)就是记录执行时间超出指定值(long_query_time)或其他指定条件(例如,没有使用到索引,结果集大于1000行)的SQL语句。
2.慢查询日志相关参数说明
慢查询的参数,对于数据库SQL的优化非常重要,是SQL优化的前提,因此,这里以表的形式进行说明,具体见表
3.慢查询日志重要参数配置
企业中常见的配置慢查询的参数为:
slow-query-log = ON #<==慢查询开启开关
long_query_time = 2 #<==记录大于2秒的SQL语句。
log_queries_not_using_indexes #<==没有使用到索引的SQL语句。
slow-query-log-file = /data/mysql/slow/slow.log #<==记录SQL语句的文件。
min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。
可将上述参数配置到my.cnf里,配置完毕重启MySQL服务,并进行检查:
mysql> show variables like ‘slow_query%’;
±--------------------±----------------------------+
| Variable_name | Value |
±--------------------±----------------------------+
| slow_query_log | ON | #<==开关已打开。
| slow_query_log_file | /data/mysql/slow.log | #<==文件路径已生效。
±--------------------±----------------------------+
2 rows in set (0.00 sec)
mysql> show variables like ‘%long_query%’;
±----------------±---------+
| Variable_name | Value |
±----------------±---------+
| long_query_time | 2.000000 | #<==记录大于2秒的查询已生效。
±----------------±---------+
1 row in set (0.01 sec)
mysql> show variables like ‘%log_queries_not%’;
±------------------------------±------+
| Variable_name | Value |
±------------------------------±------+
| log_queries_not_using_indexes | ON | #<==记录没有使用索引的查询已生效。
±------------------------------±------+
1 row in set (0.00 sec)
mysql> show variables like ‘%min_examined_row_limit%’;
±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| min_examined_row_limit | 800 | #<==记录查询结果集大于800行的SQL已生效。
±-----------------------±------+
1 row in set (0.00 sec)
到此,就已经设定好记录慢查询SQL语句的条件了,那么,对于每天所产生的大量慢查询,又该如何处理和分析呢?
4.慢查询日志的刷新方法
在工作中,可以利用定时任务按天对慢查询日志进行切割,然后再分析。
示例切割脚本如下:
[root@oldboy data]# mkdir /server/scripts/ -p
[root@oldboy data]# cat /server/scripts/cut_slow_log.sh
export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin
cd /data/mysql/slow
mv slow.log slow.log.$(date +%F)
mysqladmin flush -log
将上述脚本放入定时任务,每天0点执行切割任务,配置结果如下:
[root@oldboy data]# tail -2 /var/spool/cron/root
#cut mysql slow log by oldboy at 20180324
00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh >/dev/null 2>&1
5.使用工具分析慢查询日志
实际工作中,慢查询的日志可能非常多,给运维人员的优化工作带来了一定的困难,MySQL官方提供了慢查询的分析工具mysqldumpslow,有兴趣的读者可以参考官方手册。
下面为大家介绍一款很不错的第三方分析工具mysqlsla(需要单独安装该工具)。
(1)安装mysqlsla
请提前下载好mysqlsla-2.03.tar.gz到指定目录下,然后执行如下命令安装:
yum install perl-devel perl-DBI -y
rpm -qa perl-devel perl-DBI
tar -xf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make
make install
(2)利用mysqlsla工具分析慢查询
mysqlsla命令的默认路径为:/usr/local/bin/mysqlsla。
简单语法如下:
mysqlsla -lt slow [SlowLogFilePath] > [ResultFilePath]
在实际工作中,通常使用脚本调用mysqlsla工具进行分析,然后每天早晨8点,把分析结果发给企业的核心人员(DBA、运维总监、CTO、研发总监、核心开发),最后由DBA配合核心开发共同优化这些棘手的SQL慢查询。