一篇了解explain工具

EXPLAIN

MySQL的EXPLAIN工具的使用及查询属性和对应属性值的含义

MySQL的EXPLAIN工具是一个强大的查询分析工具，用于显示MySQL如何执行一个SELECT语句，并提供查询计划的详细信息。这有助于开发者了解查询的性能瓶颈，并进行相应的优化。

EXPLAIN工具的使用

EXPLAIN工具的使用非常简单，只需要在SELECT语句前加上EXPLAIN关键字即可。例如：

EXPLAIN SELECT * FROM users;

这条命令会返回MySQL执行上述SELECT语句的详细执行计划。

查询属性和对应属性值的含义

EXPLAIN命令返回的结果是一个表格，包含了多个字段，每个字段都提供了关于查询执行计划的详细信息。以下是这些字段及其含义的总结：

字段名	含义
id	查询的标识符，表示查询中SELECT子句或表的执行顺序。如果查询包含子查询，MySQL会为每个子查询分配一个唯一的id。
select_type	查询的类型，如SIMPLE（简单查询）、PRIMARY（查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY）、SUBQUERY（在SELECT或WHERE列表中包含了子查询）、DERIVED（在FROM列表中包含的子查询被标记为DERIVED）、UNION（若第二个SELECT出现在UNION之后）、UNION RESULT（从UNION表获取结果的SELECT）等。
table	输出行所引用的表。
partitions	匹配的分区信息，对于未分区的表，该字段为NULL。
type	连接类型，显示了MySQL决定如何查找表中的行。常见的类型有ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（非唯一性索引扫描）、eq_ref（唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配）、const/system（表通过索引一次就找到，用于比较primary key或者unique索引）。这些类型按照性能从低到高排列。
possible_keys	显示可能应用在这张表上的索引，一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key	实际使用的索引名称。如果没有使用索引，则为NULL。
key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
ref	显示索引的哪一列或常量被用于查找值。
rows	MySQL认为必须检查的用来返回请求数据的行数。这个值只是一个估算值，不是精确值。
filtered	表示返回结果的行占开始查找的行的百分比。
Extra	包含不适合在其他列中显示但非常重要的额外信息。如Using index（表示查询使用了索引覆盖，无需回表查询）、Using where（表示查询中使用了WHERE子句来过滤结果）、Using temporary（表示MySQL需要创建临时表来存储查询结果）等。

示例

假设有一个查询语句：

EXPLAIN SELECT * FROM users WHERE age > 20;

执行上述EXPLAIN命令后，可能会得到类似以下的结果（具体值会根据实际数据库结构和数据分布而有所不同）：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	range	age_index	age	4	NULL	1000	10.00	Using where

这个示例中，MySQL使用了名为age_index的索引来查找age字段大于20的行，并预计需要扫描大约1000行数据，其中大约10%的行满足条件。

通过EXPLAIN命令及其返回的结果，开发者可以深入了解MySQL如何执行查询，并根据这些信息对查询进行优化，以提高数据库的性能。

优缺点

EXPLAIN 工具是 MySQL（以及其他一些关系型数据库管理系统）中一个非常有用的性能分析工具，它提供了查询执行计划的详细信息。这个工具对于数据库管理员、开发者以及性能调优专家来说，是理解和优化 SQL 查询的重要工具。以下是 EXPLAIN 工具的优缺点：

优点

1. 提供详细的查询执行计划：EXPLAIN 可以显示数据库如何执行 SQL 查询，包括使用哪些索引、表的连接类型、预计扫描的行数等。这些信息对于理解查询性能和潜在瓶颈至关重要。

2. 帮助识别性能问题：通过查看 EXPLAIN 的输出，可以迅速识别出是否使用了不合适的索引、是否进行了全表扫描、是否有大量的数据需要过滤等性能问题。

3. 支持优化查询：基于 EXPLAIN 提供的信息，可以对查询进行优化，比如通过添加或修改索引、重写查询逻辑等方式来提高查询效率。

4. 易于使用：EXPLAIN 命令的使用非常简单，只需要在 SQL 查询前加上 EXPLAIN 关键字即可。这使得任何有权限访问数据库的用户都可以轻松地使用它来分析查询。

5. 支持复杂的查询：EXPLAIN 不仅适用于简单的 SELECT 查询，还可以用于分析包含子查询、联合查询（UNION）、连接（JOIN）等复杂查询的执行计划。

缺点

1. 性能开销：虽然 EXPLAIN 本身对数据库性能的影响通常很小，但在生产环境中频繁地对大量查询执行 EXPLAIN 可能会增加额外的负担。此外，对于特别复杂的查询，EXPLAIN 的执行可能需要一些时间。

2. 估计值的局限性：EXPLAIN 输出的某些值（如 rows 和 filtered）是估计值，而不是精确值。这意味着虽然它们可以提供有用的指导，但在某些情况下可能不够准确。

3. 需要专业知识：要充分利用 EXPLAIN 的输出，需要具备一定的数据库查询优化知识和经验。对于初学者来说，可能会感到难以理解或解释这些信息。

4. 依赖数据库内部实现：EXPLAIN 的输出格式和含义可能会因数据库的不同版本或不同配置而有所不同。因此，在迁移到新的数据库版本或更改数据库配置时，可能需要重新评估 EXPLAIN 的输出。

5. 不直接提供解决方案：EXPLAIN 只是提供了一个查询执行计划的快照，它指出了潜在的性能问题，但不会自动提供解决方案。解决这些问题需要数据库管理员或开发者的进一步分析和努力。

总的来说，EXPLAIN 是一个强大的工具，但它也有其局限性。为了有效地使用它，需要了解其优点和缺点，并结合其他工具和技术来进行综合分析和优化。

扩展

在SQL查询中，当你在FROM子句中使用子查询时，这个子查询会被MySQL（以及其他支持类似功能的数据库系统）视为一个临时的、匿名的表（也被称为派生表或内联视图）。这个临时的表在查询执行期间被创建，用于存储子查询的结果集，然后主查询可以从这个临时表中检索数据，就像从任何其他表中检索数据一样。

当这种情况发生时，MySQL可能会在EXPLAIN查询计划的输出中，将这个子查询标记为DERIVED。这个标记表示该子查询的结果集被当作了一个派生表来处理。

示例

假设我们有两个表：employees（员工表）和departments（部门表），我们想要找出每个部门中薪资最高的员工。我们可以使用以下查询来实现这一点：

SELECT d.department_name, e.*
FROM departments d
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) AS max_salaries ON d.id = max_salaries.department_id
JOIN employees e ON e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;

在这个查询中，子查询：

SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id

被用作一个派生表（或内联视图），并通过别名max_salaries在外部查询中被引用。在EXPLAIN的输出中，这个子查询可能会被标记为DERIVED，表明它的结果集被当作了一个临时的表来处理。

为什么使用DERIVED？

• 可读性：虽然子查询可以直接在JOIN条件中使用，但将它们作为派生表可以使查询更加清晰和易于理解。
• 性能优化：在某些情况下，将子查询作为派生表可以使得查询优化器更容易找到更有效的执行计划。然而，这并不总是意味着性能会更好，因为额外的临时表创建和索引缺失（如果派生表没有适当的索引）可能会引入额外的开销。
• 复杂查询的解构：对于非常复杂的查询，将部分查询分解为派生表可以使得整个查询的解构和调试过程更加容易。

注意事项

• 性能：虽然派生表在某些情况下可以提高查询的清晰度和可读性，但它们也可能导致性能问题，特别是当子查询返回大量数据时。在这种情况下，考虑使用其他优化技术，如临时表、物化视图或索引。
• 索引：派生表不会自动继承原始表的索引。如果需要在派生表上进行高效的查找或连接操作，请考虑在子查询中显式地创建索引（尽管这在SQL中通常不是直接可行的，但可以通过其他方式，如使用临时表并创建索引来实现）。然而，在大多数情况下，数据库查询优化器会尝试为派生表找到有效的执行计划，而无需显式索引。

在MySQL的EXPLAIN输出中，虽然原生的EXPLAIN结果并不直接包含一列来概述或总结每个属性的作用，但我们可以根据每个属性的含义来手动添加一列来概述它们的作用。下面是一个扩展后的表格，其中包含了一个额外的“概述”列，用于简要描述每个属性的作用：

字段名	含义	概述
id	查询的标识符，表示查询中SELECT子句或表的执行顺序。	标识查询执行的顺序或子查询的层次。
select_type	查询的类型，如SIMPLE、PRIMARY、SUBQUERY等。	说明查询的基本类型，如是否包含子查询或联合查询。
table	输出行所引用的表。	指定查询涉及的表名。
partitions	匹配的分区信息，对于未分区的表，该字段为NULL。	显示查询涉及的分区（如果表是分区表）。
type	连接类型，如ALL、index、range等。	描述MySQL如何查找表中的行，包括是否使用了索引以及索引的类型。
possible_keys	显示可能应用在这张表上的索引。	列出查询中可能用到的索引，但不一定实际使用。
key	实际使用的索引名称。	指出查询中实际使用的索引名称。
key_len	表示索引中使用的字节数。	显示索引使用的长度，有助于理解索引的覆盖范围。
ref	显示索引的哪一列或常量被用于查找值。	指示索引列或常量如何与WHERE子句中的条件匹配。
rows	MySQL认为必须检查的行数来返回请求数据。	估计为了找到结果集需要检查的行数，有助于评估查询效率。
filtered	返回结果的行占开始查找的行的百分比。	表示经过WHERE条件过滤后剩余行的比例。
Extra	包含额外的、不适合在其他列中显示但非常重要的信息。	提供关于查询执行的额外信息，如是否使用了索引覆盖、临时表等。
概述	-	对上述各字段的作用进行简要总结，帮助理解查询执行计划的各个方面。

请注意，“概述”列的内容是根据每个字段的含义手动添加的，并不是EXPLAIN命令直接输出的内容。这个额外的列可以帮助开发者更快速地理解每个属性的重要性及其对查询性能的影响。在实际应用中，你可以根据自己的需求来定制这个“概述”列的内容。