mysql慢查询的开启

摘要:mysql 慢查询(slow query log日志)的开启与分析,mysql 默认情况下是没有启动慢查询的,开启后对我们追踪慢查询的 sql 及分析优化起到很好的作用。

慢查询主要配置项

slow_query_log=OFF:默认关闭,是否开启慢查询记录。

long_query_time=10.00000:默认 10 秒,sql 执行超时执行时间。

slow_query_log_file=/usr/local/mysql/var/localhost-slow.log:慢查询的默认记录文件,这个文件是在开启慢查询的时候生成的,如果删除改文件之后,想重新生成直接在 mysql 客户端执行 flush slow logs; 命令即可。

log_queries_not_using_indexes=OFF:默认关闭,记录没有使用索引的查询语句,即使这个 sql 没有达到慢查询的记录标准。

log_output=FILE:慢查询信息存储在 file 文件中或者是 table 数据表 mysql.slow_log 中,默认是 FILE。

开启慢查询

# 查看是否开启慢查询,默认为 slow_query_log=off 关闭
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | OFF                                      |
| slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)

# 打开慢查询日志,只要打开就会再 slow_query_log_file 配置路径下生成一个慢查询日志文件
mysql> set global slow_query_log=on; 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | ON                                      |
| slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)

# 查看慢查询日志,默认为空
[root@localhost var]# pwd
/usr/local/mysql/var
[root@localhost var]# cat localhost-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.5.60-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

# 修改超时记录时间,默认是 10 秒
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

# 为了测试方便改成 0.5 秒
mysql> set long_query_time=0.5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+
1 row in set (0.00 sec)

# 测试一条超过执行时间超过 0.5 秒以上的 sql
mysql> select id from ask_anwser_title where sContents like '%小学生自控能力差,意识不足~需要家长引导%';     
+-----+
| id  |
+-----+
|   2 |
| 362 |
+-----+
2 rows in set (1.41 sec)

# 查看慢查询日志,慢查询的 sql 已经记录,通过日志可以看出,该 sql 执行时间,什么数据库,什么时间执行,具体 sql 语句都会详细记录
[root@localhost var]# cat localhost-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.5.60-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 190815 11:29:20
# User@Host: root[root] @ localhost []
# Query_time: 1.413524  Lock_time: 0.000239 Rows_sent: 2  Rows_examined: 823562
use tongzhuo_ask_db;
SET timestamp=1565839760;
select id from ask_anwser_title where sContents like '%小学生自控能力差,意识不足~需要家长引导%'

慢查询日志生成命令

# 如果删除慢查询日志,使用 flush slow logs 命令生成即可
mysql> flush slow logs;
Query OK, 0 rows affected (0.03 sec)

选择慢查询信息存放位置

# 默认是以文件的方式存放
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

# 更改存储 table 存储方式后,就不会往 file 方式中存储了
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

# table 的慢查询记录存储格式如下
mysql> select * from mysql.slow_log limit 1 \G
*************************** 1. row ***************************
    start_time: 2019-08-15 18:37:45
     user_host: root[root] @ localhost []
    query_time: 00:00:00
     lock_time: 00:00:00
     rows_sent: 1
 rows_examined: 1
            db: tongzhuo_ask_db
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: select id from ask_anwser_title where status = 0 limit 1
1 row in set (0.00 sec)

慢查询记录的时候并不会过滤,如果一条慢 sql 执行了 100 次,那它就会再日志中记录 100 次,如果我们要分析慢日志可以使用 mysql 自带的 mysqldumpslow 工具来分析慢查询日志。

# 具体使用参数自行查看
[root@localhost bin]# ./mysqldumpslow -help

# 常用参数如下
-s:排序方式,值如下
    c:查询次数
    t:查询时间
    l:锁定时间
    r:返回记录
    ac:平均查询次数
    al:平均锁定时间
    ar:平均返回记录书
    at:平均查询时间
-t:top N查询
-g:正则表达式

# 例:
# 获取访问次数最多的 5 个 sql
[root@localhost bin]# ./mysqldumpslow -s c -t 5 /usr/local/mysql/var/localhost-slow.log 

# 按照时间倒序最近的 5 个 sql
[root@localhost bin]# ./mysqldumpslow -s t -t 5 /usr/local/mysql/var/localhost-slow.log

# 按照时间倒序且含有 like 的 5 个 sql
[root@localhost bin]# ./mysqldumpslow -s t -t 3 -g "like" /usr/local/mysql/var/localhost-slow.log

以上的设置都是临时的,如果 mysql 重启后就会恢复原来的,慢查询只是一种记录方式,我们要在平时开发的时候就要优化好 sql,考虑到这条 sql 的执行效率问题,不能等到生产环境之后才发现去优化。

结束语:感谢您对本网站文章的浏览,欢迎您的分享和转载,但转载请说明文章出处。
Top