mysql之慢查询

慢查询

慢查询：超过指定时间的SQL语句查询称为慢查询。这个指定时间可配置，由long_query_time决定，以s为单位。默认情况下，mysql数据库并不启动慢查询日志，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持写入数据库。

配置参数解释：

slow_query_log : 是否开启慢查询日志， 1表示开启，0表示关闭。
log-slow-queries: mysql数据库慢查询日志存储路径，系统默认缺省host_name_slow.log。
long_query_time：慢查询阈值，当查询时间多余设定的阈值，记录日志。
log_queries_not_using_indexes: 未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output: 日志存储方式。log_output='FILE'，表示将日志文件存入文件，默认为FILE.log_output='TABLE',表示将日志存入数据库，这样日志信息会存入mysql.slow_log表中。也可以同时记录到表和文件中，记录到专用的日志表并文件要消耗更多的系统资源，建议优先记录到文件。

show variables like '%slow_query_log%'
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |

使用set global slow_query_log=1 开启慢查询日志只对当前数据库生效，要永久生效就必须修改配置文件my.cnf，

slow_query_log =1

slow_query_log_file=/tmp/mysql_slow.log

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
 
mysql> set global long_query_time=4;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

如上所示，我修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。你用show variables like 'long_query_time'查看是当前会话的变量值，你也可以不用重新连接会话，而是用show global variables like 'long_query_time';

日志分析工具

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

[root@DB-Server ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
 
Parse and summarize the MySQL slow query log. Options are
 
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
 
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

常用选项：
-s, 表示按照何种方式排序
-t 是top n的意思，表示返回前面多少条数据
-g 后面可以写正则匹配模式，大小写不敏感

举列：

// 得到返回记录集最多的10个SQL:
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
// 得到访问次数最多的10个SQL:
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
// 得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

建议在使用这些命令时结合 | 和more 使用，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more