SQL优化工具

2022-05-09 22:21:33

十四、SQL优化工具

1 慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，运行10秒以上的SQL语句会被记录下来。由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

1.1 开启慢查询日志

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要，一般不建议开启该参数，因为开启慢查询日志或多或少会带来一定的性能影响。

首先查看mysql的慢查询日志是否开启，通过如下命令：

SHOW VARIABLES LIKE '%slow_query_log%';

slow_query_log为OFF表示关闭了慢查询日志，slow_query_log_file表示日志的存放位置。

使用如下命令开启慢查询日志：

SET GLOBAL slow_query_log = 1;

使用此方法开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf。修改如下位置即可：

[mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/Heygo-slow.log

1.2 修改参数

开启慢查询日志后，什么样的SQL会记录到慢查询里面？这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，使用如下命令查看慢SQL记录的阈值：

SHOW VARIABLES LIKE 'long_query_time%';

另外，运行时间正好等于long_query_time并不会被记录下来。

可以手动修改这个阈值，通过命令临时修改（永久修改需要修改my.cnf）：

SET GLOBAL long_query_time = 5;

执行之后需要重新连接或者新开一个客户端连接才能看到修改值。

1.3 查看日志

现在，执行如下语句，一定会被慢日志记录：

SELECT sleep(6);

慢查询日志文件默认在/var/lib/mysql/ 下，后缀为-slow.log，查看一下：

cat 68926f356828-slow.log 

mysqld, Version: 5.7.37 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2022-01-28T10:57:27.035023Z
# User@Host: root[root] @  [192.168.142.3]  Id:     7
# Query_time: 6.000578  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1643367447;
SELECT sleep(6);

查询当前系统中有多少条慢查询记录：

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

1.4 日志分析命令

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

# 查看帮助：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 # 表示按何种方式排序 默认为at
                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  # 只显示前N条查询
  -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

常见使用示例：

# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/68926f356828-slow.log 
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/68926f356828-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/68926f356828-slow.log
# 在使用这些命令时结合 | 和more使用，因为如果日志很大，可能会刷屏
mysqldumpslow -s r -t 10 /var/lib/mysql/68926f356828-slow.log | more

2 Show Profile

Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

2.1 开启Show Profile

查看Show Profile是否开启，使用如下命令：

SHOW VARIABLES LIKE 'profiling%';

开启Show Profile：

SET profiling = ON;

2.2 使用Show Profile

下面运行几条SQL尝试一下：

# 普通sql
select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;

# 慢sql
select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;

查看结果：

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                |
+----------+------------+----------------------------------------------------------------------+
|        1 | 0.00052700 | show variables like 'profiling%'                                     |
|        2 | 0.00030300 | select * from tbl_emp                                                |
|        3 | 0.00010650 | select * from tbl_emp e inner join tbl_dept d on e.'deptId' = d.'id' |
|        4 | 0.00031625 | select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id     |
|        5 | 0.00042100 | select * from tbl_emp e left join tbl_dept d on e.deptId = d.id      |
|        6 | 0.38621875 | select * from emp group by id%20 limit 150000                        |
|        7 | 0.00014900 | select * from emp group by id%20 order by 150000                     |
|        8 | 0.38649000 | select * from emp group by id%20 order by 5                          |
|        9 | 0.06782700 | select COUNT(*) from emp                                             |
|       10 | 0.35434400 | select * from emp group by id%10 limit 150000                        |
+----------+------------+----------------------------------------------------------------------+

2.3 诊断SQL

查看SQL语句执行的具体流程以及每个步骤花费的时间：

SHOW PROFILE cpu,block io FOR QUERY SQL编号;
# 查询参数解释：

# ALL：显示所有的开销信息
# BLOCK IO：显示块IO相关开销
# CONTEXT SWITCHES：上下文切换相关开销
# CPU：显示CPU相关开销信息
# IPC：显示发送和接收相关开销信息
# MEMORY：显示内存相关开销信息
# PAGE FAULTS：显示页面错误相关开销信息
# SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息
# SWAPS：显示交换次数相关开销的信息

示例：

mysql> SHOW PROFILE cpu,block io FOR QUERY 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000055 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000046 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000089 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

如果你使用Navicat软件，也可以在剖析中查看到这些数据。

如果status状态出现以下几种，需要警惕：

converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table：创建临时表，mysql 先将拷贝数据到临时表，然后用完再将临时表删除
Copying to tmp table on disk：把内存中临时表复制到磁盘
locked：锁表

3 SQL优化思路

配合慢查询日志和Show Profile，梳理一下SQL的优化思路。当整个sql server变慢之后，可以按照如下方法进行分析：

开启慢查询，捕获慢sql
使用explain对慢查询日志中的sql进行分析
使用show profile查询sql在mysql服务器里面的执行细节和生命周期情况
对sql数据库服务器的参数进行调优

码农公寓