MySQL篇—执行计划介绍（第二篇，总共三篇）

💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨

💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️

💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖

    士别三日，自上次分享以来，大家应该对上篇的文章内容进行了深入消化与理解。今天给大家带来第二篇的内容——执行计划。在上篇文章中我们有了解到optimizer优化器根据统计信息对每个sql语句执行最优的执行计划（执行计划受统计信息影响）。并且通过执行计划能够帮助我们了解数据库在执行查询时采用的具体策略、使用的索引以及各种操作的执行顺序等信息，因此对于SQL查询的优化非常重要。

    因为统计信息和执行计划涉及到的内容过多，为了使大家更好消化，我将分成三篇文章来进行介绍，以便大家因为篇幅过长而感到阅读疲惫。三篇的内容分别如下，让大家先做了解：

第一篇：持久化和非持久化统计信息介绍

第二篇：执行计划介绍（当前篇）

第三篇：执行计划之覆盖索引Using index和条件过滤Using where详细介绍

---

目录

查看SQL的执行计划

explain语法一：explain + SQL语句（默认FORMAT = TRADITIONAL输出格式为表格）

explain语法二：explain FORMAT = JSON + SQL语句

explain语法三：explain FORMAT = TREE + SQL语句

explain语法四：explain analyze + SQL语句

---

废话不多说，让我们开始今天的内容。

    MySQL中的SQL执行计划能够帮助我们了解数据库在执行查询时采用的具体策略、使用的索引以及各种操作的执行顺序等信息，因此对于SQL查询的优化非常重要。下面是SQL执行计划在SQL查询优化中的作用：

1）评估查询性能：SQL 执行计划可以让我们了解到 MySQL 在执行查询时所采用的具体策略和每个步骤所需的时间，从而评估查询的性能表现。比如，我们可以查看每个操作使用的索引类型或临时表的创建情况，有助于我们确定查询是否需要进行优化，以及应该优化哪些部分。

2）定位性能问题：如果SQL查询执行缓慢，我们可以通过 SQL 执行计划来定位性能问题所在。例如，我们可以查看查询语句中是否存在不必要的排序、全表扫描、临时表创建等问题，从而确定性能瓶颈并进行调整。

3）判断索引是否有效：SQL 执行计划可以让我们了解到 MySQL 是否使用了正确的索引来执行查询，进而判断我们为表设置的索引是否有效。如果 MySQL 没有使用索引，那么可能是我们设置的索引有问题，需要重新考虑索引的创建方式。

4）选择正确的查询方案：在SQL查询优化中，有时候我们需要选择不同的查询方案来完成同样的查询操作。SQL 执行计划可以让我们了解到MySQL计划使用哪种查询方案，并可以根据不同的情况调整查询方案或者SQL语句结构。

总之，SQL 执行计划是 SQL 查询优化的重要工具，可以帮助我们找到问题所在，优化查询性能并提高数据库的运行效率。

              

查看SQL的执行计划

    通过explain查看执行计划的方式有多种，今天主要是详细介绍语法一，其他的方式我这里不多做介绍，因为都是大同小异，有兴趣的小伙伴可以私信我。

EXPLAIN语法：

官方文档对EXPLAIN的介绍：MySQL :: MySQL 8.0 Reference Manual :: 15.8.2 EXPLAIN Statement

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

explain_type: {
    FORMAT = format_name
}

format_name: {
  | TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
  | SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

EXPLAIN [options] FOR CONNECTION connection_id：获取在命名连接中执行的可解释语句的执行计划。意思就是在另一个会话上去查看其他会话正在执行SQL的执行计划，通常的做法是在另一个会话上输入show porcesslist（或者其他查询SQL的语句），有正在执行的SQL那么通过EXPLAIN [options] FOR CONNECTION加上show processlist输出的ID，那么可以看到相关SQL的执行计划。

FORMAT = format_name：选项可用于选择输出格式。默认以表格格式显示输出(FORMAT = TRADITIONAL)。可以指定其他输出格式，JSON格式以JSON格式显示信息(FORMAT = JSON)。在MySQL 8.0.16及更高版本中，TREE提供了树状输出(FORMAT = TREE)，比传统格式更精确地描述了查询处理，并且它是唯一显示哈希连接用法的格式，和EXPLAIN ANALYZE输出的内容大致相同。在MySQL 8.0.32中添加的explain_format系统变量在用于获取表列信息时，影响对explain的输出，参数的值包括TRADITIONAL (DEFAULT)、JSON、TREE。

explainable_stmt：EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句的执行计划的解析。在MySQL 8.0.19及更高版本中，它还可以使用TABLE语句，TABLE语句是MySQL 8.0.19中引入的DML语句，它返回指定表的行和列，和SELECT查询表有些类似，但功能又没SELECT多。

注意：SQL语句加上explain不会真正执行SQL语句，它仅会模拟MySQL在执行该语句时所做的操作，并返回MySQL在执行该语句时使用的查询计划信息。

            

explain语法一：explain + SQL语句（默认FORMAT = TRADITIONAL输出格式为表格）

mysql> explain select * from tb t1 join tb2 t2 on t1.id=t2.id;

需要特别关注的字段type、possible_keys、key、key_len、ref、rows、Extra。官方文档解释输出列：MySQL :: MySQL 8.0 Reference Manual :: 10.8.2 EXPLAIN Output Format

explain输出列	描述
id （JSON名称：select_id）	SELECT标识符。这是查询中SELECT的序列号。如果该行引用其他行的并集结果，则该值可以为NULL。在这种情况下，表列显示一个类似于＜union M,N＞的值，表示该行指的是id值为M和N的行的并集。
select_type （JSON名称：无）	SELECT的类型。JSON格式的EXPLAIN将SELECT类型公开为query_block的属性，除非它是SIMPLE或PRIMARY。类型比较多，参考官方文档。
table （JSON名称：table_name）	输出行所引用的表的名称。
partitions （JSON名称：partitions）	查询将从中匹配记录的分区。对于未分区的表该值为NULL。
type （JSON名称：access_type）	联接类型。类型比较多，参考官方文档。
possible_keys （JSON名称：possible_ keys）	possible_keys列表示MySQL可以从中选择查找该表中的行的索引。请注意，此列完全独立于EXPLAIN输出中显示的表的顺序。这意味着possible_keys中的一些键在实际中可能无法使用生成的表顺序。如果此列为NULL（或在JSON格式的输出中未定义），则不存在相关索引。在这种情况下，您可以通过检查WHERE子句来检查它是否引用了适合进行索引的一个或多个列，从而提高查询的性能。如果是，请创建一个适当的索引ALTER TABLE语句，并再次使用EXPLAIN检查查询。要查看表的索引，请使用SHOW INDEX FROM tbl_name。
key （JSON名称：key）	这key列表示MySQL实际决定使用的键(索引)。如果MySQL决定使用possible_keys索引来查找行，该索引被列为键值。有可能key可以命名一个不在possible_keys价值。如果没有一个possible_keys索引适合于查找行，但是查询选择的所有列都是其他索引的列。也就是说，命名索引覆盖了所选的列，因此尽管它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效。       为InnoDB，即使查询也选择了主键，辅助索引也可能会覆盖选定的列，因为InnoDB存储每个辅助索引的主键值。如果key是NULL，MySQL找不到索引来更有效地执行查询。要强制MySQL使用或忽略possible_keys列，使用FORCE INDEX, USE INDEX，或者IGNORE INDEX在您的查询中。         为MyISAM表格，运行ANALYZE TABLE帮助优化器选择更好的索引。为MyISAM表格，myisamchk -分析做同样的事。
key_len （JSON名称：key_length）	key_len列表示MySQL决定使用的密钥的长度。key_len的值使您能够确定MySQL实际使用多部分密钥的多少部分。如果key列表示NULL，那么key_len列也表示NULL。由于密钥存储格式的原因，可以为NULL的列的密钥长度比NOT NULL列的密钥长一个。
ref （JSON名称：ref）	ref列显示将哪些列或常量与键列中命名的索引进行比较，以便从表中选择行。如果该值是func，则使用的值是某个函数的结果。要查看哪个函数，请在EXPLAIN后面使用SHOW WARNINGS来查看扩展的EXPLAIN输出。函数实际上可能是一个运算符，例如算术运算符。
rows （JSON名称：rows）	rows列表示MySQL认为执行查询必须检查的行数。也就是表示优化器估计在执行某个查询操作时，数据库系统需要扫描或处理的行数。这些行可以是从表中读取的记录，也可以是索引中的记录，具体取决于查询的类型和涉及的表结构。     需要注意rows代表优化器根据统计信息和估算模型预测的行数，而不是实际的扫描行数。这是一个估算值，表示在执行这个操作时需要处理多少行数据。     rows估算值有助于识别潜在的性能瓶颈，如果某个操作的rows估算值很高，可能表明需要改进索引或优化查询以提高性能。     不同的操作对rows的影响：         ①表扫描（ALL 类型）：如果执行计划显示了 ALL 类型的扫描，那么 rows 表示从整个表中扫描的行数。因为是全表扫描所以这里的行数和自动更新持久化统计信息是一致的，如果自动更新持久化统计信息后发现与实际count(*)数据量差距较大，可考虑增加表采样的数据页，可以参考👉MySQL篇—持久化和非持久化统计信息介绍（第一篇，总共三篇）-CSDN博客👈博客的案例“（6）解决统计信息差别较大的问题（执行计划受统计信息影响，统计信息不准会导致执行计划不准）”。         ②索引扫描：如果查询使用了索引，那么 rows 可能表示通过索引扫描的行数。         ③范围扫描：如果查询包含范围条件（如 BETWEEN 或 >），rows 会表示在满足条件的情况下需要扫描的行数。        ④连接操作：在多表连接中，rows 可能表示在某个表或连接条件下需要处理的行数。
filtered （JSON名称：filtered）	已筛选列表示按表条件筛选的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。从100开始递减的值表示过滤量的增加。rows显示检查的估计行数，rows×filtered显示与下表连接的行数。例如，如果行数为1000，过滤后的行数为50.00（50%），则与下表连接的行数是1000×50%=500。
Extra （JSON名称：无）	本列包含有关MySQL如何解析查询的其他信息。没有一个JSON属性对应于Extra列；但是，此列中可能出现的值将作为JSON属性或消息属性的文本公开。类型比较多，参考官方文档。

                  

explain语法二：explain FORMAT = JSON + SQL语句

mysql> explain FORMAT = JSON select * from tb t1 join tb2 t2 on t1.id=t2.id\G;

            

explain语法三：explain FORMAT = TREE + SQL语句

mysql> explain FORMAT = TREE select * from tb t1 join tb2 t2 on t1.id=t2.id\G;

               

explain语法四：explain analyze + SQL语句

mysql> explain analyze select * from tb t1 join tb2 t2 on t1.id=t2.id\G;

---

    今天执行计划的内容就介绍到这里，只是对执行计划输出的内容做了介绍，下一篇我会用实例执行的执行计划的案例来介绍。