MySQL - 查询优化指南之『EXPLAIN 命令』详解

        EXPLAIN 是 MySQL 中用于分析 SQL 查询执行计划的关键工具，它能帮助开发者理解查询的执行方式并找出性能瓶颈。下面我将详细介绍 EXPLAIN 的使用方法和输出字段的含义，并通过实际示例说明如何优化查询。

 1.EXPLAIN 基础用法

EXPLAIN SELECT * FROM users WHERE id = 1;

 2.EXPLAIN 输出字段详解

EXPLAIN结果示例：

+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | users | range | idx_age_username| idx_age_username| 4       | NULL | 500  | Using where; Using index |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+

1. id (查询标识符)

• 表示 SELECT 查询的序列号
• 相同 id 表示同一查询的不同部分
• 不同 id 按从大到小执行，相同 id 按从上到下执行

2. select_type (查询类型)

类型	描述
SIMPLE	简单查询(不包含子查询或UNION)
PRIMARY	最外层查询
SUBQUERY	子查询中的第一个SELECT
DERIVED	派生表(FROM子句中的子查询)
UNION	UNION中的第二个或后面的SELECT
UNION RESULT	UNION的结果

 3. table (访问的表)

• 显示查询涉及的表名
• 如果是派生表，显示为 `<derivedN>`
• 如果是联合结果，显示为 `<unionM,N>`

4. type (访问类型)  - 『最重要字段』

表示 MySQL 如何查找表中的行，从最优到最差排序：

类型	描述	优化建议
system	表只有一行记录	无需优化
const	通过主键或唯一索引查找单行	最佳情况
eq_ref	多表关联时使用主键或唯一索引	理想连接
ref	使用非唯一索引查找	常见高效类型
range	索引范围扫描	可接受
index	全索引扫描	考虑优化
ALL	全表扫描	需要优化

5. possible_keys (可能使用的索引)

• 查询可能使用的索引列表
• 如果为 NULL，表示没有可用的索引

6. key (实际使用的索引)

• 查询实际使用的索引
• 如果为 NULL，表示没有使用索引

7. key_len (使用的索引长度)

• 表示索引中使用的字节数
• 可以判断是否使用了索引的全部部分

 8. ref (索引比较的列)

• 显示索引与哪些列或常量进行比较

 9. rows (预估需要检查的行数)

• MySQL 估计要检查的行数
• - 值越小越好

10. filtered (过滤百分比)

• 表示存储引擎返回的数据在server层过滤后剩余的比例
• 100表示没有过滤

11. Extra (额外信息) - 『重要优化提示』

包含 MySQL 解决查询的额外信息：

信息	含义	优化建议
Using index	使用覆盖索引	良好
Using where	在存储引擎检索后过滤	可能需要索引
Using temporary	使用临时表	需要优化
Using filesort	使用文件排序	需要优化
Using join buffer	使用连接缓冲	可调整join_buffer_size

3.实际优化案例分析

案例1：全表扫描问题

原始查询：

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

输出：

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

问题分析：

• type: ALL 表示全表扫描
• key: NULL 表示没有使用索引
• rows: 100000 需要扫描所有行

优化方案：

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

优化后 EXPLAIN：

+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_customer_id | idx_customer_id | 5       | const | 10   |       |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-------+

案例2：文件排序问题

原始查询：

EXPLAIN SELECT * FROM products ORDER BY price DESC;

输出：

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 5000 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

问题分析：

• -Extra: Using filesort 表示需要额外排序
• - 没有使用索引进行排序

优化方案：

ALTER TABLE products ADD INDEX idx_price (price);

优化后 EXPLAIN：

+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key       | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------+
|  1 | SIMPLE      | products | index | NULL          | idx_price | 4       | NULL | 5000 |       |
+----+-------------+----------+-------+---------------+-----------+---------+------+------+-------+

 4.高级优化技巧

1. 复合索引顺序：将选择性高的列放在前面
2. 避免索引失效：不要在索引列上使用函数或计算
3. 使用覆盖索引：只查询索引包含的列
4. 优化JOIN操作：确保JOIN字段有索引
5. 避免SELECT  * ：只查询需要的列
6. 合理使用子查询：有时JOIN比子查询更高效

通过系统分析 EXPLAIN 输出，结合这些优化技巧，可以显著提升 MySQL 查询性能。