【Mysql优化】EXPLAIN 返回列详解：深入 SQL 查询优化的工具
文章目录
- 什么是 EXPLAIN？
- 使用方法
- `EXPLAIN` 返回的各列详解
- 1. `id` 列
- 示例：简单查询
- 示例：嵌套查询
- 2. `select_type` 列
- 示例：UNION 查询
- 3. `table` 列
- 示例：
- 4. `partitions` 列
- 5. `type` 列
- 示例：全表扫描
- 示例：索引查询
- 6. `possible_keys` 列
- 7. `key` 列
- 8. `key_len` 列
- 9. `ref` 列
- 10. `rows` 列
- 11. `filtered` 列
- 12. `Extra` 列
- 综合示例：复杂查询的分析
- 示例：子查询与联合查询
- 总结
在优化 SQL 查询时，EXPLAIN 是数据库开发中不可或缺的工具。它能展示查询的执行计划，揭示数据库优化器在访问表和索引时的具体策略。本文将详细解析 EXPLAIN 的返回列，并通过丰富的示例和场景分析，教您如何理解和优化复杂的 SQL 查询。
什么是 EXPLAIN？
EXPLAIN 是 MySQL 提供的分析查询计划的命令，执行后会返回一张表格，展示 SQL 查询在实际执行时会采用的策略。通过分析这些列的数据，我们可以发现：
- 是否进行了全表扫描。
- 索引是否被正确利用。
- 哪些地方需要优化。
使用方法
EXPLAIN [SQL查询语句]
例如：
EXPLAIN SELECT * FROM users WHERE id = 5;
EXPLAIN 返回的各列详解
1. id 列
id 表示查询执行的顺序和嵌套层次。
- 当查询中有子查询或联合查询时，不同的查询部分会分配不同的
id。 - 数值越大，优先级越高，意味着该部分的查询会优先执行。
- 如果id序号相同，从上往下执行。
- 如果两种都存在，先执行序号大，在同级从上往下执行。
- 如果显示NULL，最后执行。表示结果集，并且不需要使用它来进行查询。
常见值含义：
id = 1：表示最外层查询。id = 2或更大：表示子查询或更深层次的查询。
示例：简单查询
EXPLAIN SELECT * FROM users;
结果：
| id | select_type | table | … |
|---|---|---|---|
| 1 | SIMPLE | users | … |
示例：嵌套查询
EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
结果：
| id | select_type | table | … |
|---|---|---|---|
| 1 | PRIMARY | users | … |
| 2 | DEPENDENT SUBQUERY | orders | … |
2. select_type 列
select_type 表示当前查询的类型。
常见值：
-
SIMPLE：简单查询，不包含子查询或联合查询。 -
PRIMARY：复杂查询的最外层部分。- 比如使用union或union all时，id为1的记录select_type通常是primary
-
SUBQUERY：子查询。- 指在 select 语句中出现的子查询语句,结果不依赖于外部查询（不在from语句中）
-
DEPENDENT SUBQUERY：依赖外层查询结果的子查询。explain select orders.*,(select name from products where products.id = orders.user_id) from orders; -
DERIVED：派生表，FROM子句中的子查询。（Mysql5.7好像对衍生表合并优化了） -
UNION和UNION RESULT：UNION查询的各部分。
示例：UNION 查询
EXPLAIN SELECT * FROM users UNION SELECT * FROM orders;
结果：
| id | select_type | table | … |
|---|---|---|---|
| 1 | PRIMARY | users | … |
| 2 | UNION | orders | … |
| 3 | UNION RESULT | … |
3. table 列
table 表示查询涉及的表名或别名。如果查询中使用了临时表或派生表，这里会显示临时表的名称。
示例：
EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
结果：
| id | select_type | table | … |
|---|---|---|---|
| 1 | PRIMARY | users | … |
| 2 | SUBQUERY | orders | … |
4. partitions 列
partitions 表示查询中涉及的表分区名称。
- 如果表是分区表，查询时会显示相关的分区名称。
- 如果没有分区表或未启用分区，该列为
NULL。
有的人可能不知道这个是神马，可以看看这个文章:【Mysql】数据库分区技术详解
5. type 列
type 列显示查询的访问方式，表示优化器扫描表或索引的效率。
一般来说保证range级别，最好能达到ref级别
访问方式（从低到高的效率排序）：
ALL：全表扫描。index：扫描整个索引，优于全表扫描。range：索引范围扫描，例如BETWEEN或范围比较。- 使用非唯一索引扫描部分索引，比如使用索引获取某些范围区间的记录
ref：索引等值扫描，例如外键查询。- 基于非唯一索引连接两个表或通过二级索引列与常量进行等值匹配，可能会存在多条匹配记录
eq_ref：索引唯一扫描，通常用于主键或唯一索引查询。- 基于主键或唯一索引连接两个表，对于每个索引键值，只有一条匹配记录，被驱动表的类型为’eq_ref’
const：恒定查询，优化器可以直接使用值查询，效率最高。- 基于主键或唯一索引查看一行，当MySQL对查询某部分进行优化，使用这些类型访问转换成常量查询，效率高
system：const类型的一种特殊场景，查询的表只有一行记录的情况，并且该表使用的存储引擎的统计数据是精确的- InnoDb存储引擎的统计数据不是精确的。虽然type类型为ALL，但是只有一条数据；
示例：全表扫描
EXPLAIN SELECT * FROM users WHERE name = 'John';
结果：
| type | Extra |
|---|---|
| ALL | Using where |
示例：索引查询
EXPLAIN SELECT * FROM users WHERE id = 1;
结果：
| type | key |
|---|---|
| const | PRIMARY |
6. possible_keys 列
possible_keys 列显示查询中可能使用的索引。
- 如果为
NULL，表示没有适合的索引，需要优化。
7. key 列
key 列显示查询实际使用的索引。
- 如果该列为
NULL，表示查询未使用索引，可能需要检查索引设计。
8. key_len 列
key_len 表示优化器使用的索引字节长度。
- 值越大，表示索引越有效。
9. ref 列
ref 列显示查询中与索引匹配的列或常量。
10. rows 列
rows 列表示查询过程中需要扫描的行数。
- 值越小，查询性能越高。
11. filtered 列
filtered 列显示通过查询条件过滤后保留的数据百分比。
- 值为
100表示完全匹配查询条件。
12. Extra 列
Extra 列显示查询优化器在执行查询时的额外信息。
常见值：
Using index：表示使用覆盖索引。Using where：通过WHERE子句进行数据过滤。Using temporary：使用临时表处理查询。Using filesort：未使用索引排序。
综合示例：复杂查询的分析
示例：子查询与联合查询
EXPLAIN SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE total > 1000)
UNION
SELECT * FROM archived_users;
结果：
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | PRIMARY | users | ALL | NULL | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | orders | ref | idx | 50 | Using where; Using index |
| 3 | UNION | archived_users | ALL | NULL | 500 |
总结
通过对 EXPLAIN 返回列的理解和应用，我们可以识别查询的性能瓶颈。优化 SQL 的关键是：
- 使用合适的索引，避免全表扫描。
- 优化子查询，尽量减少嵌套层级。
- 使用
EXPLAIN定位问题，逐步优化查询结构。
博客主页: 总是学不会.