基本信息

slow_query_log的作用是找出那些耗时很久的sql以便来进行优化。它记录的信息由两个变量控制，一个是long_query_time，另一个是min_examined_row_limit。long_query_time决定了执行超过多少秒的sql将会被记录(默认10s)，min_examined_row_limit决定了至少检查多少行(默认是0,即此参数禁用)。
默认情况下，管理语句将不会被记录，也不会记录不使用索引进行的查询。如果需要控制这两种语句，可以使用log_slow_admin_statements与log_queries_not_using_indexes控制。当使用log_queries_not_using_indexes时，日志会增长的很快，可以使用log_throttle_queries_not_using_indexes这个变量来控制记录的条数。
管理语句包括以下：
ALTER TABLE,ANALYZE TABLE,CHECK TABLE,CREATE INDEX,DROP INDEX,OPTIMIZE TABLE,REPAIR TABLE

慢查询日志的格式：
query_time:执行查询所消耗的时间。
lock_time:获得锁的时间
rows_sent:发送到客户端的行数
rows_examined:服务端检索的行数

管理操作

查看慢日志是否开启：

mysql> show variables like 'slow_query_log';
mysql> show variables like 'slow_query_log_file';

开启&关闭：

mysql> set global slow_query_log=1/0; --开启或关闭慢查询日志
mysql> set global slow_query_log_file='/data/slow.log' --指定慢查询日志文件的路径
mysql> set long_query_time=0.5; --设置慢查询的时间阈值
mysql> set global log_slow_admin_statements=1/0; --开启或关闭记录管理语句
mysql> set global log_queries_not_using_indexes =1/0; --开启或者关闭没有使用索引的查询

如果开启了慢查询日志，但是没有指定慢查询日志文件的路径及名称。默认情况下会是在数据目录下，名称是host_name-slow.log。

相关参数

slow_query_log

是否打开慢查询日志。
log_output控制日志记录的目录，如果log_output这个变量是空的，即使slow_query_log这个变量打开了，也不会记录日志。

slow_query_log_file

如果log_output的变量值为FILE，那么慢查询日志将记录到slow_query_log_file指定的文件中。

long_query_time

该变量的含义是，超过多少秒的查询会被记录到慢查询日志。
如果这个值设得非常小(<1s)，那么短时间内文件会变的非常大。非常小的值只适合在测试环境，或者在生产环境只开一小段时间。

min_examine_row_limit

检索行数小于这个变量的值将不会被记录到慢查询日志中。

log_slow_admin_statements

将慢管理语句也记录到慢查询日志。
管理语句包括:
ALTER TABLE,ANALYZE TABLE,CHECK TABLE,CREATE INDEX,DROP INDEX,OPTIMIZE TABLE,REPAIR TABLE

log_queries_not_using_indexes

配合slow_query_log变量使用。记录返回所有行的语句。
这个变量并不意味着不使用索引就会被记录。如果一个语句使用了全索引扫描，它也会被记录，因为索引没有限制行的数量。

log_short_format

记录慢查询日志时是否仅记录简略信息。

log_throttle_queries_not_using_indexes

这个变量配合log_queries_not_using_indexes使用，如果log_queries_not_using_indexes已经打开了。那么该变量可以控制每分钟写入到慢查询的条数。0代表没有限制。

mysqldumpslow

mysqldumpslow是用来解析慢查询日志文件并汇总其内容的工具。
mysqldumpslow的语法：

mysqldumpslow [options] [log_file ...]

没有参数的列子：

Reading mysql slow query log from /usr/local/mysql/data/mysqld57-slow.log
Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1

Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 limit N

Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t1 select * from t1

选项：

常用的参数：
-g 需要匹配相应的字符串，忽略大小写。
-t 显示多少条
-s 排序方式
t，at：以查询时间或者平均查询时间排序
l，al：以锁时间或者平均锁时间排序
r，ar：以发送到客户端的行数或者平均发送的行数排序
c：以使用次数排序

常用示例：

shell> mysqldumpslow -s c -t 10 /data/slow.log # 取出使用最多的10条慢查询
shell> mysqldumpslow -s t -t 3 /data/slow.log # 取出查询时间最慢的3条慢查询
shell> mysqldumpslow -s t -t 10 -g “left join” /data/slow.log # 取出按照时间排序的前10条里面含有左连接的查询语句
shell> mysqldumpslow -s r -t 10 -g 'left join' /data/slow.log # 取出发送到客户端最多的10条，且里面包含左连接的语句

构建一个大表：

CREATE TABLE dept(
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0 comment '编号',
dname VARCHAR(20)  NOT NULL  DEFAULT "" comment '名称',
loc VARCHAR(13) NOT NULL DEFAULT "" comment '地点'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '部门表' ;

CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0,
ename VARCHAR(20) NOT NULL DEFAULT "" comment '名字',
job VARCHAR(9) NOT NULL DEFAULT "" comment '工作',
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '上级编号',
hiredate DATE NOT NULL comment '入职时间',
sal DECIMAL(7,2)  NOT NULL comment '薪水',
comm DECIMAL(7,2) NOT NULL comment '红利',
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '部门编号'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '雇员表';

CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment '等级',
losal DECIMAL(17,2)  NOT NULL comment '最低工资',
hisal DECIMAL(17,2)  NOT NULL comment '最高工资'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '工资级别表';

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $
create function rand_num()
returns tinyint(6) READS SQL DATA
begin
  declare return_num tinyint(6) default 0;
  set return_num = floor(1+rand()*30);
  return return_num;
end $

delimiter $
create function rand_string(n INT)
returns varchar(255) READS SQL DATA
begin
  declare chars_str varchar(100) default
  'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  declare return_str varchar(255) default '';
  declare i int default 0;
  while i < n do
    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    set i = i + 1;
  end while;
  return return_str;
end $

delimiter $
create procedure insert_emp(in start int(10),in max_num int(10))
begin
  declare i int default 0;
  #set autocommit =0 把autocommit设置成0,关闭自动提交;
  set autocommit = 0;
  repeat
    set i = i + 1;
    insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    until i = max_num
  end repeat;
  commit;
end $

delimiter ;
call insert_emp(1,1000000);

执行一个like查询

mysql> select * from emp where ename like 'd%';
......
| 999716 | dRZwiB | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |      6 |
| 999753 | dLtMDJ | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     14 |
| 999809 | DXgLom | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     11 |
| 999812 | djLdiF | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     17 |
| 999816 | DJxuFS | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     14 |
| 999870 | duLwYF | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |      3 |
| 999871 | DHAJDY | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |      4 |
| 999901 | DFsZUx | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     18 |
| 999941 | dWzJXL | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     23 |
| 999973 | dJnkoN | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     30 |
| 999974 | DUNiBg | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |      3 |
| 999998 | dJkVbu | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     26 |
| 999999 | dRWhWo | SALESMAN |   1 | 2021-05-25 | 2000.00 | 400.00 |     17 |
+--------+--------+----------+-----+------------+---------+--------+--------+
38550 rows in set (0.32 sec)

查看slow日志的内容：

[root@centos7 data]# more centos7-slow.log 
/app/mysql/bin/mysqld, Version: 5.7.30 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-05-25T05:06:16.608820Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 0.320662  Lock_time: 0.000129 Rows_sent: 38550  Rows_examined: 1000000
use abc;
SET timestamp=1621919176;
select * from emp where ename like 'd%';

使用slowdumpslow查看slow log

[root@centos7 data]# mysqldumpslow -s c -t 1 /data/centos7-slow.log 

Reading mysql slow query log from /data/centos7-slow.log
Count: 1  Time=0.32s (0s)  Lock=0.00s (0s)  Rows=38550.0 (38550), root[root]@localhost
  select * from emp where ename like 'S'