MySQL EXPLAIN 详解
EXPLAIN 是 MySQL 中用来分析 SQL 查询执行计划的命令，能够帮助开发者了解查询优化器如何执行查询，并识别潜在的性能瓶颈。通过 EXPLAIN，我们可以获取查询的执行顺序、索引使用情况、表扫描方式等信息，从而优化查询性能。
EXPLAIN 可以用于 SELECT、DELETE、INSERT 和 UPDATE 语句，以查看其执行计划。
1. 基本语法
EXPLAIN [EXTENDED] SELECT * FROM your_table WHERE your_conditions;
EXPLAIN：用于显示查询的执行计划。EXTENDED（可选）：显示更详细的执行计划，包括优化器的选择过程，特别是在子查询中。
示例：
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
2. EXPLAIN 输出字段说明
执行 EXPLAIN 命令时，MySQL 会输出以下字段，这些字段提供了查询执行计划的详细信息：
| 字段名称 | 描述 |
|---|---|
id | 查询的标识符。通常用于区分不同的查询，数字越小的查询先执行。如果有子查询，则子查询的 id 值会大于主查询的 id。 |
select_type | 查询的类型。标明了查询的执行方式。常见值有： SIMPLE（简单查询） PRIMARY（主查询） UNION（联合查询） SUBQUERY（子查询） |
table | 当前查询涉及的表。显示当前处理的表名。 |
type | 连接类型，表示查询中从表中读取数据的方式，连接类型对查询性能有很大的影响。常见值有： ALL（全表扫描） index（索引扫描） range（范围扫描） ref（查找单一值） eq_ref（等值连接） const（常量值） NULL（没有访问数据） |
possible_keys | 查询可能使用的索引。MySQL 优化器认为可以用来加速查询的索引列表。 |
key | 实际使用的索引。如果查询没有使用索引，显示 NULL。 |
key_len | 使用的索引的长度。表示 MySQL 实际用于检索的索引的字节数。 |
ref | 显示哪些列或常数与 key 列一起用于查找数据。通常是 const 或表中的列。 |
rows | 估算扫描的行数。MySQL 优化器基于索引和查询条件来预测需要扫描的行数。 |
Extra | 额外的信息，提供有关查询执行的其他细节。常见的值有： Using index（只使用索引，不需要访问表数据） Using where（在扫描过程中应用了 WHERE 子句） Using temporary（查询使用了临时表） Using filesort（查询需要进行文件排序） |
3. 示例分析
假设我们有如下的查询：
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
假设输出：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | ref | department_idx | department_idx | 4 | const | 100 | Using where |
字段解释：
- id：查询的 ID 是
1，说明这是主查询（没有子查询）。 - select_type：查询类型是
SIMPLE，表示这是一个简单的查询，没有子查询。 - table：当前查询操作的表是
employees。 - type：查询类型是
ref，表示它使用了一个索引来查找行（ref类型通常用于非唯一索引）。 - possible_keys：可能使用的索引是
department_idx，该索引可能被用于优化查询。 - key：实际使用的索引是
department_idx，表明查询实际使用了索引来加速查找。 - key_len：使用的索引的长度为
4字节，表明索引列的长度。 - ref：
const，表示department_id的值是常量（在WHERE子句中）。 - rows：预计扫描的行数是
100，表示 MySQL 预计扫描 100 行数据。 - Extra：
Using where，表示在查询过程中应用了WHERE子句。
4. 优化 EXPLAIN 输出
EXPLAIN 可以帮助识别查询的潜在问题，并进行优化：
4.1 全表扫描 (ALL)
如果 type 显示为 ALL，则表示查询正在执行全表扫描，这通常意味着没有使用索引，查询性能可能很差。
解决方案：
- 为查询的列创建合适的索引。
- 检查查询条件和索引是否匹配。
4.2 使用索引
如果查询没有使用任何索引，或者 key 显示为 NULL，则表明查询没有利用索引，这可能导致查询效率低下。
解决方案：
- 确保查询中使用了正确的索引。
- 如果查询使用了不合适的索引，考虑优化索引或调整查询条件。
4.3 文件排序 (Using filesort)
如果 Extra 列显示 Using filesort，表示查询在排序时使用了外部排序（通常是磁盘上的临时文件），这通常会影响性能。
解决方案：
- 确保查询中的
ORDER BY子句使用了索引。 - 尽量避免对大数据集进行排序操作。
4.4 临时表 (Using temporary)
如果 Extra 列显示 Using temporary，表示查询使用了临时表来处理中间结果，这通常意味着查询涉及到复杂的 JOIN 或子查询，可能会影响性能。
解决方案：
- 优化查询，减少临时表的使用。
- 考虑拆分查询，减少临时表的需求。
5. EXPLAIN 的扩展使用
5.1 EXPLAIN EXTENDED
EXPLAIN EXTENDED 会提供更多的查询优化信息，尤其在子查询和优化器选择方面非常有用。
EXPLAIN EXTENDED SELECT * FROM employees WHERE department_id = 10;
5.2 EXPLAIN 与 ANALYZE
EXPLAIN ANALYZE 在 MySQL 8.0.18 及以上版本中可用。它不仅提供执行计划，还包括实际的执行时间和其他运行时数据。
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;
这对于调优和评估查询实际性能非常有帮助。
6. 总结
EXPLAIN 是分析 SQL 查询性能的强大工具，帮助开发者理解查询如何执行并优化查询。通过查看 EXPLAIN 的输出，开发者可以：
- 识别全表扫描或缺乏索引的问题。
- 发现性能瓶颈，如文件排序和临时表的使用。
- 进一步优化查询，提高数据库的响应速度和效率。
发表回复