为什么要开启慢查询？

找到要优化的sql，慢查询日志是mysql自带的一项功能，可以记录执行超过指定时间的sql语句。

怎么开启慢查询

1：修改配置文件my.conf，(需要重启，配置永久有效，不会丢失)

查找my.cnf 文件

• linux 下默认路径一般为 /etc/my.cnf (windows下叫my.ini)
• 添加以下内容到 [mysqld] 下 service mysqld restart

#开启慢查询日志
slow_query_log=ON
#耗时多长会记录到慢查询日志中(秒)
long_query_time=0.5
#慢查询日志的存储方式，可以同时支持  文件和表存储
log_output='FILE,TABLE'
1
2
3
4
5
6

• 重启mysql 服务器， 输入 SHOW VARIABLES LIKE 'slow_query_log'查询是否开启 ，ON已经开启

2：通过全局变量设置(无需重启，一旦重启，配置丢失)

• 在客户端直接执行 以下命令

SHOW VARIABLES LIKE 'slow_query_log'

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 0.001;

SET GLOBAL log_output = 'FILE,TABLE';
1
2
3
4
5
6
7

找到慢SQL

通过表查询

MySQL慢查询日志的基本使用教程

pdf 0星 超过10%的资源 62KB

下载

• 当slow_query_log 包含 TABLE 时，可直接通过sql 查询 ，如果没有，有可能是刚开启，还没查询到超时的sql

SELECT * FROM mysql.slow_log
1

• 部分字段含义

start_time		执行这条sql语句的时间
user_host			用户名 和主机名
query_time		查询sql语句花费的时间
lock_time			获取锁的时间
rows_sent			返回的行数
rows_examined	服务器查询的行数
db						查询的数据库
sql_text			执行的sql语句
thread_id			执行的线程id
1
2
3
4
5
6
7
8
9

• 例如我们可以按照耗时时间进行排序 ，查询出最耗时的sql

SELECT
	* 
FROM
	mysql.slow_log
	
ORDER BY query_time DESC
1
2
3
4
5
6

通过文件查询

• 首先需要开启 FILE ，可以通过 SHOW VARIABLES LIKE 'log_output' 查询是否开启 文件存储
  
• 在查询日志存储的路径 可以通过 SHOW VARIABLES LIKE 'slow_query_log_file' 查询
  
  
• 可以使用mysqldumpslow 分析：
  例如：查询出耗时最长的 前10条语句

mysqldumpslow -s t -t 10 -a xxx-slow.log
1

• 具体参数如下：

➜ 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           展示更详细的信息
  -d           debug
  -s ORDER     以哪种方式排序，默认at
                al: 平均锁定时间
                ar: 平均返回记录数
                at: 平均查询时间
                 c: 访问计数
                 l: 锁定时间
                 r: 返回记录
                 t: 查询时间
  -r           将-s的排序倒序
  -t NUM       top n的意思，展示最前面的几条
  -a           不去将数字展示成N，将字符串展示成'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   后边可以写一个正则，只有符合正则的行会展示
  -h HOSTNAME  慢查询日志以 主机名-slow.log的格式命名，-h可指定读取指定主机名的慢查询日志，默认情况下是*，读取所有的慢查询日志
  -i NAME      MySQL Server的实例名称（如果使用了mysql.server startup脚本的话）
  -l           不将锁定时间从总时间中减去

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

• 官方慢sql文档