MySQL数据库 — Explain命令
EXPLAIN 命令在 MySQL 查询优化中发挥了重要作用。通过 EXPLAIN 的输出，可以获取有关查询执行计划的详细信息，从而有助于优化和调试查询。不过，它也有一定的局限性。
使用Explain
EXPLAIN 语句通过在查询前加上 EXPLAIN 关键字来展示查询的执行计划，而不是实际执行查询。这让我们能够了解查询是如何被优化器处理的，查看各个步骤的详细信息，包括访问的表、使用的索引、行数估计等，从而帮助我们优化查询性能。
基本 EXPLAIN 用法
假设我们有一个名为 employees 的表，如下所示：
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2),
INDEX (department_id)
);
我们可以用 EXPLAIN 来查看一个简单查询的执行计划：
EXPLAIN SELECT * FROM employees WHERE department_id = 5;
这将显示关于如何访问 employees 表的详细信息，包括使用的索引和扫描的行数等。
使用 EXPLAIN 进行连接查询
假设还有一个名为 departments 的表，结构如下：
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100)
);
我们要查询 employees 和 departments 表的连接信息，可以使用如下 SQL 查询：
EXPLAIN SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000;
这将展示连接操作的执行计划，包括每个表的访问方法和连接的具体细节。
使用 EXPLAIN 分析复杂查询
考虑一个复杂的查询，涉及子查询和排序操作：
EXPLAIN SELECT name
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
)
ORDER BY salary DESC;
通过 EXPLAIN 可以查看这个查询在执行过程中使用了哪些操作，如是否使用了临时表、文件排序等。
使用 EXPLAIN ANALYZE
在 MySQL 8.0.18 及更高版本中，EXPLAIN ANALYZE 提供了执行计划的实际执行统计信息，而不仅仅是估算信息：
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 5;
EXPLAIN ANALYZE 会展示实际执行的时间和行数，提供比 EXPLAIN 更详细的性能数据。
使用 EXPLAIN 分析 INSERT 查询
EXPLAIN 通常用于 SELECT 查询，但也可以用来分析 INSERT 操作：
EXPLAIN INSERT INTO employees (name, department_id, salary) VALUES ('Alice', 5, 70000);
Explain有两个主要的变种
- EXPLAIN EXTENDED：提供了比普通 EXPLAIN 更详细的信息，包括一个“逆向编译”的查询语句，可以通过 SHOW WARNINGS 查看。这有助于理解查询优化器如何处理和优化查询。
- EXPLAIN PARTITIONS：用于显示查询将访问的分区，如果查询涉及分区表的话。
不过，EXPLAIN 也有一些局限性：
- 不会展示触发器、存储过程或用户定义函数（UDF）对查询的影响。
- 无法直接支持存储过程，但可以通过提取查询来进行分析。
- 不会揭示 MySQL 在查询执行过程中所做的具体优化。
- 对于一些操作（如内存排序和临时文件使用），可能会使用相同的描述词，容易造成误解。
- 对于复杂的子查询，EXPLAIN 可能执行子查询，并将其结果存储在临时表中，可能会影响到外层查询的优化结果。
Explain中的列
id 列：
功能：标识查询中的每一行的执行顺序。数字越大优先级越高。NULL 表示该行是最终结果集的一部分，不需要进一步查询。
select_type 列：
功能：描述查询的复杂程度。
- SIMPLE：简单查询，没有子查询或 UNION。
- PRIMARY：外层的主查询，可能包含 UNION 或子查询。
- UNION：UNION 连接的查询中的第二个及以后的查询。
- DEPENDENT UNION：受外部查询影响的 UNION 查询。
- UNION RESULT：UNION 的结果集。
- SUBQUERY：非 FROM 子句中的子查询。
- DEPENDENT SUBQUERY：受外部查询影响的子查询。
- DERIVED：FROM 子句中的子查询，也叫派生表。
table 列：
功能：显示正在访问的表名或别名。如果涉及临时表或 UNION 结果集，会显示为 <derived N> 或 <union M,N>。
type 列：
功能：显示访问表的类型，从最优到最差排序。
- system：表只有一行数据或为空表。
- const：使用唯一索引或主键，返回唯一记录。
- eq_ref：连接表时使用主键或唯一索引，返回唯一行。
- ref：等值查找，但不要求唯一性。
- fulltext：全文索引查找。
- ref_or_null：包括 NULL 值的等值查找。
- unique_subquery：子查询返回唯一值。
- index_subquery：使用索引的子查询。
- range：范围扫描，例如 >, <, BETWEEN。
- index_merge：使用多个索引的交集或并集。
- index：全索引扫描。
- all：全表扫描。
possible_keys 列：
功能：列出查询可能使用的索引。这个列表是优化过程早期创建的，因此有些罗列出来的索引有可能后续是没用的。
key 列：
功能：实际使用的索引。如果查询涉及多个索引，这里可能列出多个索引。
key_len 列：
功能：显示用于处理查询的索引长度。对于多列索引，只计算实际使用的列。
ref 列：
功能：显示索引的使用情况，如常数值、连接字段或函数。
rows 列：
功能：估算的扫描行数。这是优化器的估算值，不一定是精确值。
extra 列：
功能：提供额外的执行信息。
- distinct：查询使用了 DISTINCT 关键字。这表示数据库在返回结果时需要去除重复的记录。
- no tables used：查询不涉及任何表，例如直接从 dual 表中查询，通常用于计算常量表达式。
- using filesort：数据库在执行排序操作时不能利用索引，因此需要额外的文件排序。这通常发生在使用 ORDER BY 或 GROUP BY 语句时。
- using index：查询只通过索引就能获得所有需要的数据，而不需要回表操作。这通常意味着索引包含了所有需要的列。
- using join buffer (block nested-loop)：数据库使用了块嵌套循环连接（BNL）。这是一个在连接表时的优化策略，通常用于处理较大的数据集。
- using join buffer (batched key access)：数据库使用了分批键访问（BKA）优化策略。这种方法减少了对内表的多次循环，并提高了连接操作的效率。
- using sort_union：在处理 UNION 操作时，数据库先进行排序合并，然后再返回结果。这种方法在处理 UNION 查询时较为常见。
- using union：数据库使用了 UNION 操作，并从各个条件的结果集中获取并集。
- using sort_intersection：在处理多个条件的 INTERSECT 操作时，数据库先对结果进行排序并计算交集，然后返回结果。
- using temporary：数据库在执行查询时使用了临时表来存储中间结果。这些临时表可能存储在内存中或磁盘上。
- using where：数据库从存储引擎返回的记录中，部分记录需要在服务器层进行进一步的过滤以满足查询条件。
- firstmatch(tb_name)：这是 MySQL 5.6 及之后版本引入的优化特性，表示在处理包含 IN 子查询的查询时，如果子查询的内表数据量较大，可能会使用此优化策略。
- loosescan(m..n)：这是 MySQL 5.6 及之后版本引入的优化特性，表示在处理 IN 类型的子查询时，查询可能会返回重复记录，数据库会进行优化处理。
- select tables optimized away：某些表由于优化而被忽略。例如，当查询只涉及常量或简单表达式时，数据库可能会优化掉不必要的表。
filtered 列：
功能：filtered 列显示了在服务器层进行过滤后，记录的比例（百分比），通常在使用 EXPLAIN EXTENDED 时出现。它表示从存储引擎中检索的数据中有多少符合查询条件。这个信息有助于了解在执行查询时，存储引擎返回的记录数量与实际需要的记录数量之间的差距，从而优化查询性能。