MySQL中Explain详解

Explain 简介

MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）

当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimizer 时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 功能：表的读取顺序，数据读取操作的操作类型，哪些索引可以使用，哪些索引被实际使用，表之间的引用，每张表有多少行被优化器查询等内容。

使用方式为：Explain + SQL 语句

EXPLAIN 出来的信息有 12 列，分别是

id: 选择标识符
select_type: 表示查询的类型
table: 输出结果集的表
partitions: 匹配的分区
type: 表示表的连接类型
possible_keys: 表示查询时，可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
ref: 列与索引的比较
rows: 扫描出的行数(估算的行数)
filtered: 按表条件过滤的行百分比
Extra: 执行情况的描述和说明

一般来说，我们需要重点关注：type、key、rows、filtered、Extra

1、id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序，分为三种情况

id 相同，表示执行顺序是由上至下
id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
id 相同，不同，同时存在

结论:

id 如果相同，可以认为是一组，执行顺序从上往下顺序执行
在所有组中，id 值越大，优先级越高，越先执行
id 相同，不同，同时存在时，先执行 id 值大的，遇到相同 id 值的则从上往下执行
id 号每个号码，表示一趟独立的查询, 一个 sql 的查询趟数越少越好

2、select_type

SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION（常见）
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为 primary（常见）
SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询（常见）
DEPENDENT SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUREY：表示这个 subquery 的查询要受到外部表查询的影响
DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里
UNION：这是 UNION 语句其中的一个 SQL 元素
UNION RESULT：从 UNION 表获取结果的 SELECT

3、table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称

4、partitions

代表分区表中的命中情况，非分区表，该项为 null

5、type

对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。

常用的类型（从最好的类型到最差的排序）

system：数据库表仅有一行记录，这是 const 类型的特例，查询起来非常迅速，但是一般情况下是不会出现的
const：通过一次索引就能找到数据，const 一般用于 primary key （主键）或者 unique 索引 （唯一索引）, 因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
eq_ref：在一个查询中使用主键或唯一索引来连接两个表时，每个索引键值只有一条记录匹配，查询效率非常高。
ref：在一个查询中使用普通索引（可能会有多条匹配的记录与之关联）来连接两个表时
fulltext：使用FULLTEXT 索引执行时候，用于全文搜索的一种索引类型
ref_or_null：对于某个字段既需要关联条件，也需要 null 值的情况下。查询优化器会选择用 ref_or_null 连接查询
index_merge：在查询过程中需要合并多个索引的结果，通常出现在有 or 的关键字的 sql 中
unique_subquery：该联接类型类似于index_subquery，使用了子查询，在子查询中使用的是唯一索引
index_subquery：利用索引来执行关联操作，进行子查询，避免了全表扫描
range：常用于范围查询，比如 between...and 或者 in 等操作。只检索给定范围的行，使用一个索引来选择行，范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引
index：Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树，all 和 Index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的
ALL：Full Table Scan， MySQL 将遍历全表以找到匹配的行

eq_ref的例子
假设我们有两个表，一个是Users表，其中包含用户信息，另一个是Orders表，其中包含订单信息。每个用户在Users表中具有唯一的主键user_id，而在Orders表中，每个订单都与特定用户相关联，使用user_id进行引用。
1
2
3
SELECT *
FROM Users
JOIN Orders ON Users.user_id = Orders.user_id;
在这个例子中，我们使用Users表和Orders表之间的user_id字段进行连接。由于user_id在Users表中是主键或唯一索引，对于Users表中的每个user_id值，只会有一条匹配的记录与之关联。