MySQL 优化流程：定位低效率 SQL、explain 分析、show profile 分析以及 trace 追踪

发表于 更新于 分类于 阅读次数： 14 本文字数： 4.2k 阅读时长 ≈ 15 分钟

MySQL 优化流程 SQL 优化流程：show status 查询 SQL 执行频率、explain 分析、show profile 分析和 trace 追踪。

MySQL 优化流程 ——SQL 优化流程。MySQL 优化篇（基于 MySQL8.0.28 测试验证）。

只停留在看上面，提升效果甚微。应该带着思考去测试佐证，或者使用（同类书籍）新版本进行对比，这样带来的效果更好。最重要的一环，养成阅读官方文档，是一个良好的习惯。能编写官方文档，至少证明他们在这个领域是有很高的造诣，对用法足够熟练。

我：我可以和您多聊几句吗？
面试官：就你那点小心思，我还不知道吗？面试官微微一笑：一样揍得你鼻青脸肿。

我：只是想借点技能过来，多学点总归没坏处。
面试官：小伙子，真不错，年纪轻轻，挺有觉悟的。

面试官：对 SQL 语句优化流程，你了解多少。
我：会那么一丢丢，也总结了一些。

面试官：请开始你的表演。
我：很荣幸与你展开讨论，请看继续往下看。

在对 MySQL 进行举例并使用到数据库表，大多数情况使用 MySQL 官方提供的 sakila（模拟电影出租信息管理系统）和 world 数据库，类似于 Oracle 的 scott 用户。

tips：图片资源可能被防盗链（寄）了，可以右键属性复制地址在地址栏查看哈。

你可以将这篇博文，当成过度到 MySQL8.0 的参考资料。友情提示：经验是用来参考，不是拿来即用。如果您能看到并分享这篇文章，我很荣幸。如果有误导您的地方，我表示抱歉。

如果没有进行特别说明，一般是基于 MySQL8.0.28 进行测试验证。官方文档非常具有参考意义。目前市面上针对 MySQL8.0 书籍还比较少，部分停留在 5.6.x 和 5.7.x 版本，但仍然具有借鉴意义。

在这里插入图片描述

个人理解有限，难免出现错误偏差。所有测试，仅供参考。

如果感觉对你起到作用，有参考意义，想获取原 markdown 文件，那就看完文章在文末给出的仓库链接进行获取吧。

[toc]

MySQL 优化流程 ——SQL 语句优化流程

注意：在某些情况，你自己测试的结果可能与我演示有所不同，我省略了查询结果的部分参数。

本文侧重点在 SQL 优化流程以及 MySQL 锁问题（MyISAM 和 InnoDB 存储引擎）。图片可能会挂，演示时尽量使用 SQL 查询语句返回结果进行示例。篇幅很长，因此使用 markdown 语法加了目录。

起初，也只是想看 MySQL8.0.28 有哪些变化，后面索性结合书籍和官方文档总结了一篇。花了将近两周，基本是每天完善一点，因为个人只有晚上和周末有时间总结并测试验证。如果有错别字，也请多多担待。如果你能看到并分享这篇文章，我很荣幸。如果有误导你的地方，我表示抱歉。

如果你是从 MySQL5.6 或者 5.7 版本过渡到 MySQL8.0。学习之前，建议线看官方文档这一章节：1.3 What Is New MySQL8.0 。在做对比的时候，文档中带有 Note 标识是你应该注意的地方。比如下面这张截图：

01 优化 SQL 语句流程

登录到 mysql 字符命令界面：

1
mysql -uroot -p

登录时指定端口和主机地址方式：

1
mysql -h 192.168.245.147 -uroot -p -P 3307

使用？ show 帮助命令查询 show status 用法，截取部分语法如下：

1
2
? show
SHOW [GLOBAL | SESSION] STATUS [like_or_where]

1 通过 show status 查询 SQL 执行频率

如果不加参数，默认采用 session 级别，也可以加上 global 参数进行测试一下。

使用 session 与 global 参数区别：

  • session：当前连接统计的结果，默认为 session 级别；

  • global：上次数据库启动至今统计结果，需要手动那个指定 global 参数。

下面就列举示例进行说明，分别使用 like 去查询所有以及匹配 CURD 操作（select、insert、update、delete）：

查询当前 session 所有统计记录，如果直接在字符命令界面去查询，共有 175 条记录，大多数情况会采用工具去执行：

1
2
3
4
5
6
7
8
9
10
11
12
show status LIKE 'com_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_admin_commands                  | 0     |
| Com_assign_to_keycache              | 0     |
| Com_alter_db                        | 0     |
| Com_commit                | 0     |
| Com_rollback                    | 0     |
+-------------------------------------+-------+
...
175 rows in set (0.00 sec)

Com_xx 部分参数作用说明：

  1. Com_xx：代表某某语句执行次数，一般我们关心的是 CURD 操作（select、insert、update、delete）。
  2. Com_select：执行 select 操作次数，每次累加 1 次。
  3. Com_insert：执行 insert 操作次数，对于批量执行插入的 insert 操作只累加 1 次。
  4. Com_update：执行 update 操作次数。
  5. Com_delete：执行 delete 操作次数。

以上这些参数对所有存储引擎表操作均会进行累计。但也有一些参数只针对 InnoDB 存储引擎，累加算法有些许不同。

查询 innodb 参数如下，列举部分：

1
2
3
4
5
6
7
8
9
10
11
show status LIKE 'innodb_rows%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_rows_deleted                   | 0                                                |
| Innodb_rows_inserted                  | 0                                                |
| Innodb_rows_read                      | 0                                                |
| Innodb_rows_updated                   | 0                                                |
+---------------------------------------+--------------------------------------------------+
...
61 rows in set (0.00 sec)
  • InnoDB_rows_read：执行 select 查询返回行数。
  • InnoDB_rows_inserted：执行 insert 插入操作返回行数。
  • InnoDB_rows_updated：执行 update 更新操作返回行数。
  • InnoDB_rows_deleted：执行 delete 删除操作返回行数。

通过上面几个参数，可以轻松了解当前数据库应用是以插入更新为主还是查询操作为主，以及各种 SQL 大概执行比例是多少。

对于更新操作执行次数计数，无论是提交还是回滚都会进行累加。

对于事务型应用，可以通过 Com_commit 和 Com_rollback 了解事务提交与回滚情况。对回滚操作非常频繁的数据库，可能存在应用编写问题。

有几个参数便于用户了解数据库情况：

1
2
3
show status LIKE 'conn%';
show status LIKE 'upti%';
show status LIKE 'slow_q%';
  • Connections：试图连接 MySQL 服务器次数。
  • Uptime：服务器工作时间。
  • Slow_queries：慢查询次数。

对优化 SQL 语句流程就介绍这么多，主要对关心的（CURD 以及事务）各个参数熟练操作运用。

2 定位执行效率较低的 SQL 语句

可以通过两种方式定位执行效率较低 SQL 语句：

  1. 使用参数：--log-slow-queries [=file_name]，MySQL 会将 long_query_time 的 SQL 语句日志写入文件；
  2. 使用参数 show processlist：查询 MySQL 线程状态、是否锁表。

慢查询日志在查询结束以后才记录，在应用反映执行效率问题时查询慢查询慢查询日志并不能定位问题。可以使用 show processlist，查看当前 MySQL 在进行的线程：线程状态、是否锁表，实时查看 SQL 执行状态。

3 使用 explain 分析执行效率低的 SQL 语句

参考 mysql8.0 官方文档 explain：

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

(https://dev.mysql.com/doc/refman/8.0/en/explain.html)

通过上述步骤查询到低效率 SQL 语句，然后使用 explain 或者 desc 命令获取 MySQL 如何执行查询 select 语句。

语法：explain [SQL 语句]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
explain [SQL语句]
-- 例如
mysql> explain select * from sakila.city\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 600
     filtered: 100.00
        Extra: NULL

desc 语法：desc [SQL 语句 & 表名]

world 数据库是官方提供，文初有给链接。

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
-- 示例查询world数据库city表结构
desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 分析查询语句信息
mysql> desc select * from world.city\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4046
     filtered: 100.00
        Extra: NULL

以上是对 explain 与 desc 语法的介绍，以及简单使用。侧重点不在 desc，主要以 explain 进行说明。

接下来对各个参数进行演示说明：

序号 explain & desc 参数 作用
1 id 查询标识符。
2 select_type select 类型，一般有 simple、primary、union、subquery。
3 table 输出结果集表。查询的表名，如果使用了别名，则显示别名。
4 partitions 对分区的支持。
5 type 执行计划分析使用访问类型，ALL 代表全表扫描。
6 possible_keys 查询时可使用的索引。
7 key 实际使用到的索引。
8 key_len 使用到索引字段长度。
9 ref 与索引比较的列。在 type 中类型的一种，使用到索引。
10 rows 扫描行数，并不代表实际使用 count (*) 检索的所有行数，是一个估值。
11 filtered 过滤恒定成立条件。
12 Extra 执行情况说明和描述，包含不适合在其它列中显示，但对执行计划有帮助的额外信息。

常见访问类型（type）：

在这里插入图片描述

1
2
3
+------+--------+--------+------+---------+---------------+----------+
| ALL  | index  | range  | ref  | eq_ref  | const,system  |   NULL   | 
+------+--------+--------+------+---------+---------------+----------+

性能天梯排行榜：由左至右，依次递增。

3.1、type=ALL：代表全表扫描，MySQL 遍历全表匹配行。

示例：演示 type 为 ALL 执行计划

1
explain select * from world.city;

在这里插入图片描述

3.2、type=index：索引全扫描，MySQL 遍历整个索引匹配行。

如果不清除哪一个是主键或者是 index，使用 desc 命令查看，desc world.city

示例：演示 type 为 index 执行计划

1
explain select id from world.city;

在这里插入图片描述

3.3、type=range：索引范围扫描，常见于 <、<=、>、>=、between 等操作符。

=, <>, >, >=, <,<=, IS NULL, <=>, BETWEEN, LIKE, or IN()

8.8.2 explain output format range 介绍

示例：演示 type 为 range 执行计划

1
explain select * from world.city c where c.id<6;

3.4、type=ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回某个单独值匹配记录行。

示例：演示 type 为 ref 执行计划

1
explain select * from world.city where countrycode='AFG';

在这里插入图片描述

ref 往往还经常出现在 join 操作中。

示例：演示 type 为 ref 执行计划，使用 inner join 内连接

1
explain select * from world.city t1 inner join world.countrylanguage t2 on t1.countrycode=t2.countrycode;

3.5、type=eq_ref：与 ref 类似，区别 eq_ref 使用唯一索引。每个索引键值，表中只有一条匹配记录行。简单来说，在多表连接查询中使用 primary key 或者 unique index 作为关联条件。

示例：演示 type 为 eq_ref 执行计划

1
explain select * from sakila.film t1,sakila.film_text t2 where t1.film_id=t2.film_id;

在这里插入图片描述

3.6、type=const&system：单表中最多有一条匹配行，查询速度很快。这条匹配行中其它列值可以被优化器在当前查询中当做常量来处理。例如，根据主键 primary key 或者唯一索引 unique key 进行查询。

示例：演示 type 为 const 执行计划

1
explain select * from world.city t where t.id=7;

3.7、type=NULL：MySQL 不用访问表或索引，直接得到结果。

示例：演示 type 为 NULL 执行计划

1
explain select 1;

以前，只知道统计查询表使用 MyISAM 存储引擎非常快，但不知其原理。使用 explain 分析了下，看到访问类型（type）是 NULL，瞬间有点明白了。下图是使用 InnoDB 与 MyISAM 存储引擎表的对比：

个人只演示常见的几种。官方示例比较多，比如：ref_or_null、index_merge 以及 index_subquery 等等。

你可以找到参考文档：

8.8.3 Extended EXPLAIN Output Format

tips：在 MySQL8.0 中移除了 explain extended，使用这条命令分析 SQL 语句会报（1064（42000））。

某种场景下，使用 explain 并不能满足我们需求，需要更高效定位问题，此时可以配合 show profile 命令联合分析。

4 show profile 分析 SQL

查看当前 MySQL 版本对 profile 是否支持：如果是 YES，代表是支持的。

1
2
3
4
5
6
7
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

默认 show profiling 是关闭的，可以通过 set 命令设置 session 级别开启 profiling：

1
2
3
4
5
6
7
select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

开启 profiling：设置 profiling 参数值为 1，默认是 0。

1
2
3
4
5
6
7
8
9
10
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

示例：

  1. 统计查询 world 数据库 city 表行记录数；
  2. 执行 show profiles 命令分析 SQL。

统计 city 表记录：

1
2
3
4
5
6
7
mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.01 sec)

使用 show profiles 命令分析：

示例：

1
2
3
4
5
6
7
8
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00017800 | select @@profiling              |
|        2 | 0.00115675 | select count(*) from world.city |
+----------+------------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

使用 show profile for query 语句可以查询到执行过程中线程更多信息：状态、消耗时间

示例：截取部分参数作为演示。

1
2
3
4
5
6
7
8
9
mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000059 |
| Executing hook on transaction  | 0.000003 |
...
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)

更具上面查到的参数值，可以进一步分析是哪些影响到查询效率。

更多用法请参考官方文档：

13.7.7.30 SHOW PROFILE Statement

13.7.7.31 SHOW PROFILES Statement

1
2
3
4
5
6
7
8
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO | CONTEXT SWITCHES | CPU   | IPC
| MEMORY   | PAGE FAULTS      | SOURCE  | SWAPS
}

比如从 BLOCK IO（锁输入和输出操作）、CPU（用户系统 CPU 消耗时间）、内存等等着手分析。

判断用户 CPU 消耗时间，可以统计数据量大一点的表：我统计这张表模拟数据为 1kw 条。

1
2
3
4
5
6
show profile CPU for query 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| executing                      | 1.685893 | 5.593750 |   0.375000 |
+--------------------------------+----------+----------+------------+

5 使用 trace 分析优化器如何选择执行计划

查看 trace 是否开启：OPTIMIZER_TRACE

  • enabled：默认为 off。on 代表开启，off 代表关闭。
  • one_line：json 格式显示，是否以一行显示。on 代表一行显示，off 代表多行显示（格式化）。
1
2
3
4
5
6
select @@OPTIMIZER_TRACE;
+-------------------------+
| @@OPTIMIZER_TRACE       |
+-------------------------+
| enabled=on,one_line=on  |
+-------------------------+

示例：临时开启 trace，在字符命令行中使用，测试建议还是使用一行显示比较好。

1
set OPTIMIZER_TRACE="enabled=on,one_line=on";

示例：

  1. 查询 world 数据库 city（城市）表前两行记录。
  2. 然后使用 trace（optimizer_trace 分析）追踪。
1
2
3
4
5
6
7
8
9
10
-- 1. 查询world数据库city（城市）表前两行记录。
select * from world.city limit 0,2;
-- 2. 然后使用trace追踪。
select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: select * from world.city limit 0,2
                            TRACE: {"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` limit 0,2"}]}},{"join_optimization": {"select#": 1,"steps": [{"table_dependencies": [{"table": "`world`.`city`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"rows_estimation": [{"table": "`world`.`city`","table_scan": {"rows": 4046,"cost": 9.375}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`world`.`city`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 4046,"access_type": "scan","resulting_rows": 4046,"cost": 413.975,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 4046,"cost_for_plan": 413.975,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`world`.`city`","attached": null}]}},{"finalizing_table_conditions": []},{"refine_plan": [{"table": "`world`.`city`"}]}]}},{"join_execution": {"select#": 1,"steps": []}}]}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

6 定位问题后采取相应优化方法

建立索引：在常用字段上建立，不常用字段（应该考虑是否建立）。

经过上述步骤第 3 步 explain 分析 SQL 查询语句，使用 explain 执行计划发现使用全表扫描（大量数据）非常耗时间。

在相应字段建立索引，然后进行分析，扫描行数明细减少，大大提高数据库访问速度。

02 MySQL 官方示例数据库

给出 sakila-db 数据库包含三个文件，便于大家获取与使用：

  1. sakila-schema.sql：数据库表结构；
  2. sakila-data.sql：数据库示例模拟数据；
  3. sakila.mwb：数据库物理模型，在 MySQL workbench 中可以打开查看。

https://downloads.mysql.com/docs/sakila-db.zip

只是用于用于简单测试学习，建议使用 world-db：

world-db 数据库，包含三张表：city、country、countrylanguage。

https://downloads.mysql.com/docs/world-db.zip

最后附上官方示例数据库，sakila-db 数据库一个非常完整的示例。包含：视图、函数、触发器以及存储过程，当然也存在使用外键。

关于 MySQL 索引问题，会在后续博文中进行总结。

参考资料 & 鸣谢：

  • 《深入浅出 MySQL 第 2 版 数据库开发、优化与管理维护》
  • 《MySQL 技术内幕 InnoDB 存储引擎 第 2 版》
  • MySQL8.0 官网文档：refman-8.0-en.pdf，如果学习新版本，官方文档是非常不错的选择。

虽然书籍年份比较久远（停留在 MySQL5.6.x 版本），但仍然具有借鉴意义。

最后，对以上书籍和官方文档所有作者表示衷心感谢。让我充分体会到：前人栽树，后人乘凉。

莫问收获，但问耕耘

能看到这里的，都是帅哥靓妹。以上是本次 MySQL 优化流程 ——SQL 语句优化流程的全部内容，希望能对你的工作与学习有所帮助。感觉写的好，就拿出你的一键三连。如果感觉总结的不到位，也希望能留下您宝贵的意见，我会在文章中定期进行调整优化。好记性不如烂笔头，多实践多积累。你会发现，自己的知识宝库越来越丰富。原创不易，转载也请标明出处和作者，尊重原创。