SQL优化工具

十四、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%';

SQL优化工具

slow_query_logOFF表示关闭了慢查询日志,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软件,也可以在剖析中查看到这些数据。
SQL优化工具

如果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数据库服务器的参数进行调优

上一篇:LR手动关联参数化问题总结


下一篇:Jquery前端输入框自动补全与C#后台代码