MYSQL 慢查询使用方法

MYSQL慢查询介绍

分析MySQL语句查询性能的问题时候，可以在MySQL记录中查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。MYSQL自带的慢查询分析工具mysqldumpslow可对慢查询日志进行分析：主要功能是, 统计sql的执行信息，其中包括 ：

• 出现次数(Count),
• 执行最长时间(Time),
• 累计总耗费时间(Time),
• 等待锁的时间(Lock),
• 发送给客户端的行总数(Rows),
• 扫描的行总数(Rows),

用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).

1. 开启慢SQL的配置
   1. LIUNX 系统 在mysql配置文件my.cnf中增加

slow_query_log

slow_query_log_file=/usr/local/mysql/data/zhoucentos-slow.log

long_query_time=0.1

• Slow_query_log  这是一个布尔型变量，默认为真。没有这变量，数据库不会打印慢查询的日志。

• log-slow-log_file=/export/servers/mysql/bin/mysql_slow.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)
• long_query_time=0.1(记录超过的时间，默认为10s)，与DBA沟通，性能测试分析问题时可以将该值设为0.1即100毫秒，这样分析的粒度更详细。
  备选 ：log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)。log-long-format (如果设置了，所有没有使用索引的查询也将被记录)
  1. Windows下配置:

在my.ini的[mysqld]添加如下语句：
log-slow-queries = E:\web\mysql\log\mysqlslowquery.log
long_query_time = 0.1(其他参数如上)

 

注： 配置完成后，重新mysql服务配置才能生效。

 

1. 慢查询开启与关闭
   1. 配置完成，连接数据库检查慢查询日志是否开启：

命令如下：mysql> show variables like '%slow_query_log%';

1. 1. 如果没有打开，请开启,slow_query_log

开启命令：mysql> set @@global.slow_query_log = on;

关闭命令：mysql> set @@global.slow_query_log = off;

1. 1. 再次检查是否开启成功

mysql> show variables like '%slow_query_log%';

1. 1. 检查目录中是否生成文件

 /mysql目录下是否存在mysql_slow.log
[root@localhost mysql]# ls -l mysql_slow.log

1. 慢查询日志分析

3.1  Linux系统:

使用mysql自带命令mysqldumpslow查看

常用命令，通过 mysqldumpslow –help查看

• -s，是order的排序，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序
• -a，倒序排列
• -t，是top n的意思，即为返回前面多少条的数据
• -g，后边可以写一个正则匹配模式，大小写不敏感的

例如：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 at -t 50 host-slow.log 显示出耗时最长的50个SQL语句的执行信息
 

以Count: 32  Time=0.26s (8s)  Lock=0.00s (0s)  Rows=10.0 (320), wos_20120719[wos_20120719]@2host 为例：

Count: 32  该SQL总共执行32次

Time = 0.26s (8s)  平均每次执行该SQL耗时0.26秒，总共耗时32（次）*0.26（秒）=8秒。

Lock=0.00s(0s)   lock时间0秒

Rows =10.0(320)  每次执行SQL影响数据库表中的10行记录，总共影响 10（行）*32（次）=320行记录

3.2  Windows系统:

当你是第一次开启mysql的慢查询，会在你指定的目录下创建这个记录文件，本文就是mysqlslowquery.log，这个文件的内容大致如下(第一次开启MYSQL慢查询的情况下)
E:\web\mysql\bin\mysqld, Version: 5.4.3-beta-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument

可以通过如下的命令来查看慢查询的记录数：

mysql> show global status like ‘%slow%’;
+———————+——-+
| Variable_name | Value |
+———————+——-+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+———————+——-+