当前位置：首页 > news >正文

MySQL数据库 — Explain命令

news 来源：原创 2024/9/20 16:32:54

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 时出现。它表示从存储引擎中检索的数据中有多少符合查询条件。这个信息有助于了解在执行查询时，存储引擎返回的记录数量与实际需要的记录数量之间的差距，从而优化查询性能。