查询截取分析

Mr.Liu大约 4 分钟

查询截取分析

实际情况问题解决流程

收到监控系统报告，系统变慢，假如是sql变慢
开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
explain+慢SQL分析
show profile，查询SQL在Mysql服务器里面的执行细节和生命周期情况
运维经理 or DBA，进行SQL数据库服务器的参数调优。

慢查询日志

是什么

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

怎么用

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

命令行临时修改

# 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
SHOW VARIABLES LIKE '%slow_query_log%';
# 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效
set global slow_query_log = 1;

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

# 查看配置时间
SHOW VARIABLES LIKE 'long_query_time%';

怎么修改慢查询的标准时间？

# 设置慢的阙值时间
set global long_query_time=3;
# 设置后无效需要重新连接或新开一个会话才能看到修改值。
SHOW VARIABLES LIKE 'long_query_time%';
# 查询当前系统中有多少条慢查询记录
show global variables like'long_query_time';

配置文件永久修改

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，[mysqld]下增加或修改参数后，然后重启MySQL服务器。

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/query-slow.log
# 关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）
long_query_time = 3
log_output = FILE

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具，mysqldumpslow。

# 得到返回记录集最多的10个SQL
mysqldumpslow-s r -t 10 /var/fib/mysql/query-slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow-s c -t 10 /var/lib/mysql/query-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow-s t -t 10 -g "left join" /var/lib/mysql/query-slow.log
# 另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况
mysqldumpslow-s r -t 10 /var/lib/mysql/query-slow.log | more

Show Profile

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

官网： http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤

是否支持，看看当前的mysql版本是否支持

Show variables like 'profiling';
# 默认是关闭，使用前需要开启
Show variables like "profiling%";

开启功能，默认是关闭，使用前需要开启

set profiling = on;

运行SQL,假如存在运行速度慢的SQL语句

select * from emp group by id%10 limit 150000;

查看结果

show profiles;

诊断SQL

show profile cpu，block io for query Query_ID; # Query_ID即上一步前面的问题SQL数字号码

日常开发需要注意的结论

# 有以下任一情况，说明SQL语句有问题,必须优化
converting HEAP to MyISAM # 查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table # 创建临时表:拷贝数据到临时表,用完再删除
Copying to tmp table on disk # 把内存中临时表复制到磁盘，危险！！！
locked

全局查询日志

配置启用

在mysql的my.cnf中，设置如下：

#开启
general_log = 1
#记录日志文件的路径
general_log_file = /path/logfile
#输出格式
log_output = FILE

命令启用

set global general_log = 1;
set global log_output = 'TABLE';
# 此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;

会抓取所有的SQL语句,永远不要在生产环境开启这个功能