ToB企服应用市场:ToB评测及商务社交产业平台

标题: 【Mysql优化】EXPLAIN 返回列详解：深入 SQL 查询优化的工具 [打印本页]

---

作者: 立山    时间: 2024-12-20 00:44
标题: 【Mysql优化】EXPLAIN 返回列详解：深入 SQL 查询优化的工具
在优化 SQL 查询时，EXPLAIN 是数据库开发中不可或缺的工具。它能展示查询的实行筹划，显现数据库优化器在访问表和索引时的具体策略。本文将详细剖析 EXPLAIN 的返回列，并通过丰富的示例和场景分析，教您如何理解和优化复杂的 SQL 查询。

---

什么是 EXPLAIN？

EXPLAIN 是 MySQL 提供的分析查询筹划的命令，实行后会返回一张表格，展示 SQL 查询在实际实行时会采用的策略。通太过析这些列的数据，我们可以发现：

• 是否进行了全表扫描。
  
• 索引是否被精确使用。
  
• 哪些地方需要优化。
  

使用方法

1. EXPLAIN [SQL查询语句]

复制代码

例如：

1. EXPLAIN SELECT * FROM users WHERE id = 5;

复制代码

---

EXPLAIN 返回的各列详解

1. id 列

id 表现查询实行的次序和嵌套条理。

• 当查询中有子查询或联合查询时，不同的查询部分会分配不同的 id。
  
• 数值越大，优先级越高，意味着该部分的查询会优先实行。
  
  
  
  • 假如id序号相同，从上往下实行。
    
  • 假如两种都存在，先实行序号大，在同级从上往下实行。
    
  • 假如表现NULL，最后实行。表现效果集，并且不需要使用它来进行查询。
    
  
  

常见值含义：

• id = 1：表现最外层查询。
  
• id = 2 或更大：表现子查询或更深条理的查询。
  

示例：简单查询

1. EXPLAIN SELECT * FROM users;

复制代码

效果：
idselect_typetable…1SIMPLEusers… 示例：嵌套查询

1. 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：依赖外层查询效果的子查询。
  
  1. explain
  2. select orders.*,(select name from products where products.id = orders.user_id) from orders;

  复制代码
• DERIVED：派生表，FROM 子句中的子查询。（Mysql5.7似乎对衍生表归并优化了）
  
• UNION 和 UNION RESULT：UNION 查询的各部分。
  

示例：UNION 查询

1. EXPLAIN SELECT * FROM users UNION SELECT * FROM orders;

复制代码

效果：
idselect_typetable…1PRIMARYusers…2UNIONorders…3UNION RESULT…

---

3. table 列

table 表现查询涉及的表名或别名。假如查询中使用了临时表或派生表，这里会表现临时表的名称。
示例：

1. 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，但是只有一条数据；
    
  
  

示例：全表扫描

1. EXPLAIN SELECT * FROM users WHERE name = 'John';

复制代码

效果：
typeExtraALLUsing where 示例：索引查询

1. EXPLAIN SELECT * FROM users WHERE id = 1;

复制代码

效果：
typekeyconstPRIMARY

---

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：未使用索引排序。
  

---

综合示例：复杂查询的分析

示例：子查询与联合查询

1. EXPLAIN SELECT * FROM users u
2. WHERE u.id IN (SELECT user_id FROM orders WHERE total > 1000)
3. UNION
4. SELECT * FROM archived_users;

复制代码

效果：
idselect_typetabletypekeyrowsExtra1PRIMARYusersALLNULL1000Using where2DEPENDENT SUBQUERYordersrefidx50Using where; Using index3UNIONarchived_usersALLNULL500

---

总结

通过对 EXPLAIN 返回列的理解和应用，我们可以辨认查询的性能瓶颈。优化 SQL 的关键是：

• 使用合适的索引，克制全表扫描。
  
• 优化子查询，尽量减少嵌套层级。
  
• 使用 EXPLAIN 定位问题，逐步优化查询结构。
  

博客主页: 总是学不会.

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4