本文实战使用Mysql 8

前一篇文章我们分析了如何通过Explain分析一个sql语句的执行计划和问题点。 那么在分析一个sql之前，我们得要拿到这条sql，我们想要优化这条sql，就需要先去定位到这条有问题或者说查询效率慢的sql，本文学习如何在生产环境下定位慢查询。

慢查询

分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。

当Mysql性能下降时，通过开启慢查询来获得哪条SQL语句造成的响应过慢，进行分析处理。当然开启慢查询会带来CPU损耗与日志记录的IO开销，所以我们要间断性的打开慢查询日志来查看Mysql运行状态。

如何定位慢查询

当发现你服务器数据库返回结果很慢的时候，我们需要去定位数据库具体哪条sql比较慢，这时候需要执行一些命令：

1、临时开启慢查询日志开关

开启才会将执行时间较长的查询通过日志的方式进行记录

  1. mysql>set global slow_query_log = on;

2、临时设置慢查询时间临界点

查询时间高于这个临界点的都会被记录到慢查询日志中【单位/秒】

默认情况下，mysql认为超过10秒才是一个慢查询

  1. mysql>set long_query_time = 1; # 所有执行时间超过1秒的sql都将被记录到慢查询日志文件

3、设置慢查询存储的方式

默认是none,可以设置为4种值，”FILE”、”TABLE”、”FILE,TABLE”、”NONE”,如果是table则慢查询信息会保存到mysql库下的slow_log表中,一般不设置”FILE,TABLE”，因为会徒增不必要的I/O开销

  1. mysql>set globle log_output = file; # 设置慢查询日志输出形式为文件形式

4、查询慢查询日志的开启状态和慢查询日志储存的位置

  1. mysql>show variables like "%slow%";  #查看MySQL慢查询相关设置

image.png

5、设置记录所有没有利用索引的查询

性能优化时开启此项,平时不要开启

  1. mysql>set log_queries_not_using_indexes on;

当然，上述设置都是命令临时设置的，可以在mysql的配置文件my.cnf中配置相关属性：

  1. slow_query_log = on -- 开启日志;
  2. slow_query_log_file = /data/f/mysql_slow_cw.log -- 记录日志的log文件; 注意:window上必须写绝对路径,比如 D:/wamp/bin/mysql/mysql5.5.16/data/show-slow.log 
  3. long_query_time = 1 -- 慢查询临界时间;
  4. log-queries-not-using-indexes = on -- 表示记录没有使用索引的查询

测试慢查询记录

  1. mysql>select sleep(3);

image.png
查看慢查询日志是否记录了上述语句：

  1.  root> cd /opt/mysql-8.0.19/data/
  3.  root> cat -n VM_0_5_centos-slow.log

image.png
像上面这点信息还不够看，我们需要慢查询分析工具mysqldumpslow来帮助我们分析慢查询

使用mysqldumpslow分析慢查询

  1. mysqldumpslow -t 10  VM_0_5_centos-slow.log # 显示出慢查询日志中最慢的10条sql

image.png

至此，我们就已经可以定位到具体的慢查询了。

mysqldumpslow工具

mysqldumpslow是mysql安装后就自带的工具，用于分析慢查询日志

  1. Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  -- 后跟参数以及log文件的绝对地址;
  3.   -s            what to sort by (al, at, ar, c, l, r, t), 'at' is default 排序
  4.                 al: average lock time  平均锁定时间
  5.                 ar: average rows sent 平均返回记录数
  6.                 at: average query time 平均执行时间
  7.                 c: count 执行计数
  8.                 l: lock time 锁定时间
  9.                 r: rows sent 返回记录
  10.                 t: query time 执行时间
  12.   -r           reverse the sort order (largest last instead of first) 倒序
  13.   -t NUM       just show the top n queries 只查看排行上的多少条数据
  14.   -a           don't abstract all numbers to N and strings to 'S' 
  15.   -n NUM       abstract numbers with at least n digits within names
  16.   -g PATTERN   grep: only consider stmts that include this string 通过正则表达式匹配我们想要查看的内容
  17.   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
  18.                default is '*', i.e. match all
  19.   -i NAME      name of server instance (if using mysql.server startup script)
  20.   -l           don't subtract lock time from total time

常用参数命令

  1. mysqldumpslow -s c -t 10 /mysql/data/VM_0_5_centos-slow.log # 取出使用最多的10条慢查询
  3. mysqldumpslow -s t -t 3 /mysql/data/VM_0_5_centos-slow.log # 取出查询时间最慢的3条慢查询
  5. mysqldumpslow -s t -t 10 -g 'left join' /mysql/data/VM_0_5_centos-slow.log # 取出按照时间排序的前10条里面含有左连接的查询语句

返回内容解析

mysqldumpslow分析结果不会显示具体完整的sql语句,只会显示sql的组成结构
**
Count：执行次数
Time：单次执行最长时间（后面跟着的是总执行时间）
Lock：单次锁定最长时间（后面跟着的是总锁定时间）
Rows：返回结果行数
User@Host：对应sql的执行主机和用户
select sleep(N)：SQL 的执行内容

  1. Reading mysql slow query log from VM_0_5_centos-slow.log
  2. Count: 3  Time=3.00s (9s)  Lock=0.00s (0s)  Rows=1.0 (3), root[root]@localhost
  3.   select sleep(N)
  5. Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=6.0 (6), root[root]@localhost
  6.   show variables like "S"
  8. Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  9.   set long_query_time = N

我们还可以利用别的工具替代mysqldumpslow（当然，原生的只有mysqldumpslow），如pt-query-digest等，此处不做赘述。