在优化 SQL 查询时，EXPLAIN 是数据库开发中不可或缺的工具。它能展示查询的实行筹划，显现数据库优化器在访问表和索引时的具体策略。本文将详细剖析 EXPLAIN 的返回列，并通过丰富的示例和场景分析，教您如何理解和优化复杂的 SQL 查询。
什么是 EXPLAIN？
EXPLAIN 是 MySQL 提供的分析查询筹划的命令，实行后会返回一张表格，展示 SQL 查询在实际实行时会采用的策略。通太过析这些列的数据，我们可以发现：
- 是否进行了全表扫描。
- 索引是否被精确使用。
- 哪些地方需要优化。
使用方法
例如：
- EXPLAIN SELECT * FROM users WHERE id = 5;
EXPLAIN 返回的各列详解
1. id 列
id 表现查询实行的次序和嵌套条理。
- 当查询中有子查询或联合查询时，不同的查询部分会分配不同的 id。
- 数值越大，优先级越高，意味着该部分的查询会优先实行。
- 假如id序号相同，从上往下实行。
- 假如两种都存在，先实行序号大，在同级从上往下实行。
- 假如表现NULL，最后实行。表现效果集，并且不需要使用它来进行查询。
常见值含义：
- id = 1：表现最外层查询。
- id = 2 或更大：表现子查询或更深条理的查询。
示例：简单查询
- EXPLAIN SELECT * FROM users;
idselect_typetable…1SIMPLEusers… 示例：嵌套查询
- EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
idselect_typetable…1PRIMARYusers…2DEPENDENT SUBQUERYorders…
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;
idselect_typetable…1PRIMARYusers…2UNIONorders…3UNION RESULT…
3. table 列
table 表现查询涉及的表名或别名。假如查询中使用了临时表或派生表，这里会表现临时表的名称。
示例：
- EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
idselect_typetable…1PRIMARYusers…2SUBQUERYorders…
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';
typeExtraALLUsing where 示例：索引查询
- EXPLAIN SELECT * FROM users WHERE id = 1;
typekeyconstPRIMARY
6. possible_keys 列
possible_keys 列表现查询中大概使用的索引。
7. key 列
key 列表现查询实际使用的索引。
- 假如该列为 NULL，表现查询未使用索引，大概需要查抄索引设计。
8. key_len 列
key_len 表现优化器使用的索引字节长度。
9. ref 列
ref 列表现查询中与索引匹配的列或常量。
10. rows 列
rows 列表现查询过程中需要扫描的行数。
11. filtered 列
filtered 列表现通过查询条件过滤后保留的数据百分比。
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;
idselect_typetabletypekeyrowsExtra1PRIMARYusersALLNULL1000Using where2DEPENDENT SUBQUERYordersrefidx50Using where; Using index3UNIONarchived_usersALLNULL500
总结
通过对 EXPLAIN 返回列的理解和应用，我们可以辨认查询的性能瓶颈。优化 SQL 的关键是：
- 使用合适的索引，克制全表扫描。
- 优化子查询，尽量减少嵌套层级。
- 使用 EXPLAIN 定位问题，逐步优化查询结构。
博客主页: 总是学不会.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
