07-mysql高级篇-查询截取分析2
点击蓝色“潭时录”关注我丫
每天和小潭一起快乐的学习~
你好,我是在学mysql的小潭。在上一期的推文中我们学习了查询截取分析中查询优化部分的知识,熟悉了有关排序和分组关键字和索引结合的使用及优化方法。今天,我们将继续学习有关查询截取中慢查询日志的知识。
什么是慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阈值的语句。具体指运行时间超过long_query_time值的SQL,会被记录到慢查询日志中,其中long_query_time默认值为10,单位为秒,可自定义。我们可以根据慢查询日志收集超过给定查询时间的SQL,进而结合explain进行全面的分析和优化。
慢查询日志怎么用
默认情况下,MySQL数据库没有开启慢查询日志,需要手动设置该参数。如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会对性能产生一定的影响。慢查询日志支持将日志记录写入文件。
查看慢查询日志是否开启及其开启方法:
默认:SHOW VARIABLES LIKE '%slow_query_log%';
开启:set global slow_query_log=1; (只对当前数据库连接有效,MySQL重启后该设置会失效)
如果要让该参数永久生效,则需要修改配置文件my.cnf。在[mysqld]下增加或修改参数slow_query_log和slow_query_log_file后,重启MySQL服务器。配置参数如下。
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
# slow_query_log_file参数指定慢查询日志的存放位置,如果没有指定该参数,系统会默认提供以host_name-slow.log为名的缺省文件。
哪些记录会被写到慢查询日志,是由参数long_query_time控制,默认情况下long_query_time的值为10秒,可使用如下命令进行查看。
SHOW VARIABLES LIKE 'long_query_time%';
可以使用命令修改,也可以在配置文件my.cnf中修改该参数。
假如运行时间正好等于long_query_time时,并不会被记录到慢查询日志中,即判断条件是大于long_query_time,而不是大于等于。
慢查询案例
查看当前多少秒算慢:SHOW VARIABLES LIKE '%long_query_time%';
设置慢阈值时间:set global long_query_time=3;
设置后看不出变化的原因:需要重新连接或新开一个会话才能看到修改值。
重开会话后查询:SHOW VARIABLES LIKE '%long_query_time%';
本会话中命令添加global: show GLOBAL variables like '%long_query_time%';
记录慢SQL及后续分析:使用sleep函数模拟慢查询SQL
查看慢查询日志:
查询当前系统中有多少条慢查询记录:show global status like '%slow_queries%';
如果需要永久生效慢查询记录,可以将如下参数配置到my.cnf的[mysqld]下:
[ ]下配置:
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time=3
log_output=FILE
日志分析工具mysqldumpslow
在生产环境中手动分析日志,查找和分析SQL将是很大的工作量,可以使用MySQL提供的日志分析工具mysqldumpslow来简化此项工作。
查看mysqldumpslow的帮助信息:
s: 表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
t: 返回前面多少条的数据
g: 后边搭配一个正则匹配模式,大小写不敏感
工作常用参考:
得到返回记录集最多的10个SQL:
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
得到访问次数最多的10个SQL:
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
得到按时间排序的前10条中含有左连接的查询语句:
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
建议在使用这些命令时结合|和more使用,否则可能会出现爆屏情况:
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more
示例:
下期预告:批量插入数据脚本