【实践篇】MySQL执行计划详解

本文知识大纲速览

1. 前言

MySQL执行计划是MySQL提供的一种查看SQL与数据库交互行为的工具。可以很直观的帮助开发者发现问题并进行SQL优化。最常用的是EXPLAIN命令。

使用EXPLAIN关键字可以模拟优化器的行为，返回MySQL如何使用索引进行搜索和表的连接顺序（JOIN；)，并且可以让我们知道在MySQL解析我们的查询时，每个动作后的信息。

查询执行计划信息包含：

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以被用到
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

当我们使用EXPLAIN开头，对查询做分析预测后，解析的结果能告诉我们：

1. 哪些部分或者说哪个环节消耗的时间多
2. 哪些索引被使用，哪些没有被使用上
3. 查询与表的行数和读取方式
4. 总之，就是看待查询各项操作的代价如何

要理解和解释执行计划，就需要了解它包含的一些关键信息，并可以对其进行适当的优化。例如，在合适的地方建立索引，改变JOIN顺序，重写查询等，都可以改善查询的效率。

2. 基本介绍

1. 什么是执行计划

执行计划是MySQL数据库在执行SQL查询时的一个操作步骤集合。它描述了数据库如何执行SQL语句，以及如何从数据表中检索或更新数据。执行计划包括了多种信息，如数据读取的顺序，数据过滤的方式，连接表的方式等。

2. 如何查看执行计划

在MySQL中，可以通过在查询语句前面添加"EXPLAIN"关键字来查看该查询的执行计划。例如：

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

这将会返回一个表，其中包含了执行计划的详细信息。

3. 执行计划的组成部分

1. id：查询的序列号，表示查询的执行顺序。
2. select_type：查询的类型，例如：SIMPLE（简单查询），PRIMARY（主查询），SUBQUERY（子查询）等。
3. table：输出结果集的表。
4. type：连接类型，表示MySQL在表中找到所需行的方式，常见的类型有：ALL（全表扫描），index（全索引扫描），range（范围扫描）等。
5. possible_keys：可能应用的索引。
6. key：实际应用的索引，如果为NULL，则没有使用索引。
7. key_len：表示索引字段的长度，如果为NULL，则表示不使用索引。
8. ref：显示关键字的比较，是常数还是字段等。
9. rows：根据表统计信息及索引选用情况，大致估算出查找所需读取的行数。
10. Extra：包含MySQL解决查询的详细信息，如：Using index（使用了覆盖索引），Using where（使用了WHERE过滤器）等。

3. 执行计划的关键元素

1. id

这是查询的标识符，代表查询的组执行顺序。相同的id说明在同一执行阶段。

2. select_type

这个列标明查询的类型。常见的查询类型有SIMPLE（简单查询，不包含子查询或者UNION操作）, PRIMARY（主查询，外层查询）, SUBQUERY（子查询，在SELECT或者WHERE列表中）等。

3. table:

输出结果集的表。对于多表查询，会显示访问每个表的顺序。

4. type:

这是表示MySQL如何对表的行进行遍历的类型。各种类型从最好到最坏分别是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

5. possible_keys:

显示可能用于查询的索引。它并不代表这个索引一定会被使用。

6. key:

实际使用的索引。如果为NULL，则没有使用索引。

7. key_len

表示索引字段的长度，如果为NULL，则表示不使用索引。

8. ref:

显示使用哪个列或常量与key列进行比较。

9. rows:

估计MySQL需要读取的行数来执行查询。

10. Extra

提供了关于MySQL如何解析查询及查询是否使用索引等信息。
常见的值有Using where, Using temporary, Using filesort等。这些都是MySQL优化器在生成查询执行计划时的一些策略说明：

1. Using where：表示MySQL服务器将在存储引擎检索行后再进行过滤，这说明查询中有一个WHERE子句，并且存储引擎不会自动过滤这些行。

2. Using temporary：表示MySQL需要使用一个临时表来存储结果集，这通常发生在对不同列的多次排序时。

3. Using filesort：表示MySQL需要进行额外的步骤来找出如何检索行，因为它不能仅通过索引来完成。这通常出现在ORDER BY或GROUP BY查询中，当无法使用索引顺序来完成排序时，会进行额外的排序步骤。

这些策略都可能对查询性能产生影响。例如，"Using temporary"和"Using filesort"都可能会导致查询速度变
慢，尤其是在处理大量数据时。

4. Using index：表示使用了覆盖索引（Covering Index），即所有需要的数据都被直接从索引中读取，而无需从数据表中读取。使用覆盖索引可以大大提高查询性能。

5. Using join buffer：表示使用了连接缓冲，这是MySQL优化连接操作的一种方式，可以提高连接多张表的效率。

6. Using sort union：表示使用排序合并算法进行OR操作，该算法可以在不同的索引之间进行排序和合并操作。

7. Using index condition：表示使用了索引条件推送（Index Condition Pushdown），即将部分WHERE子句条件在存储引擎层进行计算，避免了不必要的行扫描和传输。

MySQL查询优化器常见的一些策略。这些策略并非越多越好，而是根据具体查询和数据表结构来选择最适合的策略。优化器的目标是尽可能减少查询处理所需的磁盘I/O和CPU时间。

4. 底层原理

MySQL的执行计划基于查询优化器，它的主要任务是找到执行SQL查询的最优方式。优化器会考虑各种可能的执行计划，比如使用哪个索引，以何种顺序连接表，等等，然后根据估算的成本选择一个最优的执行计划。

当你执行一个SQL查询时，MySQL首先会解析这个查询，检查语法是否正确，然后生成一个对应的内部数据结构，我们称之为解析树。接着，优化器会使用各种规则和启发式方法，对解析树进行优化，生成一个或多个可能的执行计划。每个执行计划都对应一个可能的查询执行路径，包括使用哪个索引，以何种顺序连接表，等等。

然后，优化器会对每个执行计划进行成本估算，这个成本主要基于读取数据的数量，也就是IO操作的数量。此外，优化器还会考虑CPU消耗，内存消耗等因素。

最后，优化器会选择成本最低的执行计划来执行这个查询。执行计划中的每个步骤都会被转换为一系列的底层操作，比如读取磁盘上的数据，执行计算，等等，这些操作最终由MySQL的存储引擎来执行。

当你使用EXPLAIN命令查看执行计划时，你看到的就是优化器生成的这个最优执行计划的详细信息。

5. 执行计划示例解读

以下是一个复杂的SQL查询示例

SELECT p.product_name, c.category_name, s.supplier_name, SUM(od.quantity) as total_quantity
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
INNER JOIN suppliers s ON p.supplier_id = s.supplier_id
INNER JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_name, c.category_name, s.supplier_name
HAVING total_quantity > 100
ORDER BY total_quantity DESC;

这个查询涉及到多个表的连接，并使用了聚合函数和分组操作。查询的目标是获取每个产品的名称、所属类别、供应商名称以及总销量，并按照销量进行降序排序。
它包含了多个表的连接操作和聚合函数的使用。通过INNER JOIN语句将四个表(products, categories, suppliers, order_details)连接起来，使用ON子句指定连接的条件。然后通过GROUP BY子句对产品名称、类别名称和供应商名称进行分组，使用SUM函数计算每个组别的总销量。最后，在HAVING子句中对总销量进行筛选，只返回销量大于100的数据。最后，使用ORDER BY子句对总销量进行降序排序。

这个复杂的查询可以用于分析产品销售情况，找出销量最高的产品，并了解它们所属的类别和供应商。
EXPLAIN 的输出可能如下

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
1	SIMPLE	c	ref	PRIMARY	PRIMARY	4	db.p.category_id	1	NULL
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	4	db.p.supplier_id	1	NULL
1	SIMPLE	od	ref	p2	prod_id	4	db.p.product_id	10	Using Where

1. 所有四个表 products，categories，suppliers，order_details 都参与了查询（操作1代表简单查询没有UNION或子查询）。
2. 表 p（products）要对行进行全表扫描(类型为ALL)，结果集预计的行数为1000，并且需要额外的操作（temporary和filesort）用来处理GROUP BY和ORDER BY。
3. 表 c (categories) 和 s (suppliers) 均是使用了ref查找方式对应索引PRIMARY，意味着它们确认supplier_id 和 category_id是作为索引存在的，并且每一个可供该基表使用的联接列都参与了索引查找，在联接时每找出一行。
4. 表 od (order_details)使用了索引prod_id，该操作与提供的prod_id匹配行，并在对结果进行执行，并过滤掉结果中未满足WHERE子句条件的记录。

这个计划告诉我们，这个查询可能的优化：可能要考虑为产量表 products 添加以product_id作为主键的索引，用于减少全表扫描的影响。

本文知识图谱

1. MySQL执行计划

• 用途：查看SQL与数据库交互行为的工具
• 命令：EXPLAIN

2. 查询执行计划信息

• 表的读取顺序
• 数据读取操作的操作类型
• 可用的索引与实际使用的索引
• 表之间的引用
• 优化器查询的行数

3 执行计划的关键元素

• id：查询的执行顺序
• select_type：查询的类型
• table：输出结果集的表
• type：MySQL在表中找到所需行的方式
• possible_keys：可能应用的索引
• key：实际应用的索引
• key_len：索引字段的长度
• ref：关键字的比较
• rows：查找所需读取的行数
• Extra：MySQL解决查询的详细信息

4. 底层原理

• 基于查询优化器
• 优化器任务：找到执行SQL查询的最优方式
• 执行计划：优化器生成的最优执行计划的详细信息