十七、SQL 执行计划——Explain
一、Explain的作用
explain 命令主要用于查看 SQL 语句的执行计划,该命令可以模拟优化器执行 SQL 查询语句,可以帮助我们编写和优化 SQL。那么 explain 具体可以提供哪些信息,帮助我们如何去优化 SQl 的呢?
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
二、如何使用
使用方式: explain + 待执行的sql
explain 会返回一个待执行 SQL 的执行计划列表,列表包含了 12 个字段,字段共同描述了 SQL 在执行计划中将会采取何种方式执行。以下列表详细描述了执行计划表的字段含义:
字段名称
|
描述
|
---|---|
id
|
执行 select 语句查询的序列号,决定表的读取顺序
|
select_type
|
查询的类型,也就是数据读取操作的操作类型
|
table
|
查询的表名
|
partitions
|
表分区
|
type
|
访问类型
|
possible_keys
|
可使用的索引。
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用到。
如果这个字段为 null 但是字段 key 不为 null,这种情况就是在查找时没有可以使用的二级索引树,
但是二级索引中包含了需要查询的字段,于是就不再查找聚簇索引(聚簇索引比较大),
转而扫描这个二级索引树(二级索引树比较小),
并且此时一般访问类型 type 为 index,及扫描整棵索引树。
|
key
|
实际扫描使用的索引。
如果为 null,则没有使用索引;
查询中若使用了覆盖索引,则该索引仅出现在key列表中;
|
key_len
|
索引中使用的字节数。
可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好;
key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,
即 key_len 是根据表定义计算而得,不是通过表内检索出的;
|
ref
|
显示索引的哪一列被使用了。
如果可能的话,是一个常数,哪些列或常量别用于查找索引列上的值;
|
rows
|
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数;
|
filtered
|
搜索条件过滤后剩余数据的百分比。
|
Extra
|
包含不适合在其它列中显示但十分重要的额外信息
|
三、主要的字段分析结果
1. id
执行 select 语句查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序,它有三种情况:
类型名称
|
描述
|
---|---|
id相同
|
执行顺序由上至下
|
id不同
|
如果是子查询,id 的序号会递增,id 值越大优先级越高,越先被执行
|
id相同不同,同时存在
|
如果 id 相同,可以认为是一组,从上往下顺序执行,在所有组中,id值越大,优先级越高,越先执行
|
2. select_type
就是数据读取操作的操作类型,他一共有以下几种:
类型名称
|
描述
|
---|---|
simple
|
简单的 select 查询,查询中不包含子查询或者 union;
|
primary
|
查询中若包含任何复杂的子查询,最外层查询则被标记;
|
subquery
|
在 select 或者 where 列表中包含了子查询;
|
dependent subquery
|
子查询中的第一个 SELECT, 取决于外面的查询。 即子查询依赖于外层查询的结果。
|
derived
|
在 from 列表中包含的子查询被标记为 DERIVED(衍生表),mysql 会递归执行这些子查询,把结果放临时表中;
|
union
|
若第二个 select 出现在 union 之后,则被标记为 union,若 union 包含在 from 子句的子查询中,外层 select 将被标记为 DERIVED;
|
union result
|
从 union 表(即 union 合并的结果集)中获取 select 查询的结果;
|
meterialized
|
物化表,子查询关联查询时,子查询结果存储在物化临时表,然后根据临时表中的数据去主表匹配。
|
dependent union
|
UNION 中的第二个或后面的查询语句,取决于外面的查询
|
3. type
显示查询使用了何种类型,性能从最好到最差依次是:system > const > eq_ref > ref > range > index > all 使用以下DDL语句给 vehicle 表添加索引。
ALTER table vehicle ADD index index_org_id_delete_state_vehicle_no (org_id, is_delete, state, vehicle_no) COMMENT '机构ID-删除状态-启用状态-索引'; ALTER table vehicle ADD index index_vin (vin) COMMENT '车架号索引';
解释各个字段:
org_id: organization 表的主键ID,关联多个车辆 vehicle,int 类型,自增。
is_delete:vehicle 表标记记录是否删除,int 类型,值为 0、1,不可为 null。
state:vehicle 表标记车辆是否启用,int 类型,值为 0、1,不可为 null。
vehicle_no:vehicle 表车牌号,varchar(255) 类型,值为定长字符串,可以为 null。
vin:vehicle 表车架号,varchar(255) 类型,值为不定长字符串,可以为 null。
system
表只有一行记录(等于系统表),这是 const 类型的特例,平时不会出现,这个也可忽略不计;
const
表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行记录,所以很快。 如果将主键置于 where 列表中,mysql 就能将该查询转换成一个常量;
举例:索引为 id 主键,SQL为
SELECT * FROM vehicle WHERE id = 1
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
const
|
PRIMARY
|
PRIMARY
|
4
|
const
|
1
|
100.00
|
eq_ref
唯一性索引扫描,对于每一个索引键,表中只有一条记录与之匹配,常用于主键或唯一索引扫描;此类型通常出现在多表的 join 等值查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果,查询效率较高。
举例:索引为 id 主键和一个复合索引(实际未使用),SQL 为
SELECT * FROM vehicle, organization WHERE vehicle.org_id = organization.id
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
ALL
|
index_org_id_delete_state_vehicle_no
|
const
|
195
|
100.00
|
||||
1
|
SIMPLE
|
organization
|
eq_ref
|
PRIMARY
|
PRIMARY
|
4
|
jhphe-service-device.vehicle.org_id
|
1
|
100.00
|
ref_or_null
二级索引等值比较同时限定 is null 。
举例:索引为可为 null 的字段 vin ,SQL 为
SELECT * FROM vehicle WHERE vin = 'VIN123456' or vin IS NULL
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
ref_or_null
|
index_vin
|
index_vin
|
768
|
const
|
35
|
100.00
|
ref
非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体;
举例:索引为 org_id ,SQL为
SELECT * FROM vehicle WHERE vehicle.org_id = 1
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
ref
|
index_org_id_delete_state_vehicle_no
|
index_org_id_delete_state_vehicle_no
|
768
|
const
|
35
|
100.00
|
range
只检索给定范围的行,使用一个索引来选择行,key列显示使用哪个索引,一般就是在你的 where 语句中出现了 between、<、>、in 等的查询;这种范围索引扫描比全表扫描要好,因为它只需要开始于索引的某一个点,结束于另一个点,不用扫描全部索引;
举例:索引为主键 id,SQL 为
SELECT * FROM vehicle WHERE id between 1 and 20
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
range
|
PRIMARY
|
PRIMARY
|
4
|
20
|
100.00
|
Using where
|
index
index 和 all 区别为 index 类型只遍历索引树,这通常比 all 快,因为索引文件通常比数据文件小;也就是说虽然 all 和 index 都是读写表,但 index 是从索引中读取的,而 all 是从硬盘中读的;
举例:索引包含字段 vehicle_no ,SQL为
SELECT vehicle_no FROM vehicle WHERE is_delete = 0
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
index
|
index_org_id_delete_state_vehicle_no
|
index_org_id_delete_state_vehicle_no
|
780
|
195
|
10.00
|
Using where; Using index
|
all
也就是全表扫描;
举例:SQL为
SELECT * FROM vehicle WHERE is_delete = 0
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
ALL
|
195
|
100.00
|
Using where
|
一般来说,得保证查询至少达到 range 级别,最好能达到 ref;
4. key_len
key_len 表示该列计算查询中使用的索引的长度。例如:SELECT * FROM table where age = 1 and name like 'xx',假设 age 是 int 类型且不可为 null;name 是 varchar(20) 类型且可以为 null,编码为 utf8。若以这两个字段为索引查询,那么 key_len 的值为 4 + 3 * 20 + 2 + 1 = 67。具体计算规则如下表所示:
值类型
|
值名称
|
描述
|
---|---|---|
字符串
|
CHAR(n)
|
n 字节长度
|
VARCHAR(n)
|
如果是 utf8 编码,则是 3 n + 2字节;;如果是 utf8mb4 编码,则是 4 n + 2 字节。
|
|
数值类型
|
TINYINT
|
1字节
|
SMALLINT
|
2字节
|
|
MEDIUMINT
|
3字节
|
|
INT
|
4字节
|
|
BIGINT
|
8字节
|
|
时间类型
|
DATE
|
3字节
|
TIMESTAMP
|
4字节
|
|
DATETIME
|
8字节
|
|
字段属性
|
NULL 属性 占用一个字节。如果一个字段是 NOT NULL 的, 则不占用。
|
5. Extra
包含不适合在其它列中显示但十分重要
using filesort
说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql 中无法利用索引完成的排序操作称为”文件排序“;
比如:查询未删除的车辆车牌号(vehicle_no)和所属机构ID(org_id)并按车辆创建时间排序。那么 SQL 为
SELECT org_id, vehicle_no FROM vehicle WHERE is_delete = 0 ORDER BY create_time
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
ALL
|
195
|
10.00
|
Using where; Using filesort
|
如果按照车辆所属机构ID排序,那么 SQL 为
SELECT org_id, vehicle_no FROM vehicle WHERE is_delete = 0 ORDER BY org_id
分析结果为
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
---|---|---|---|---|---|---|---|---|---|---|---|
1
|
SIMPLE
|
vehicle
|
index
|
index_org_id_delete_state_vehicle_no
|
index_org_id_delete_state_vehicle_no
|
780
|
195
|
10.00
|
Using where; Using index
|
using temporary
使用了临时表保存中间结果,Mysql 在对查询结果排序时使用临时表,常见于排序 order by 和分组查询 group by
using index
表示相应的 select 操作中使用了覆盖索引(Covering Index),避免了访问了表的数据行,效率不错
using where
表明索引被用来执行索引键值的查找;如果出现 using index 而没有 using where 表明索引用来读取数据而非执行查找操作;
Using index condition
会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行;
using join buffer
Block Nested Loop/Block Nested-Loop Join,连接查询优化。
Block Nested-Loop Join 算法:把外部表的每一行数据作为内部表查询的条件,内部表存在索引其扫描成本为 O(Rn) ,不存在索引其扫描成本为 O(Rn * Sn);
Block Nested Loop 算法:把外部表结果存入 join buffer,内存循环每行与 join buffer 匹配,相当于外部表每次可以同时匹配多行数据,其扫描次数为 (R * S) / join_buffer_size + 1,其扫描次数随 join_buffer_size 增大而减少。
Join Buffer:
1. 默认大小为 256k,在 MySQL 配置文件中通过 join_buffer_size 参数配置。
2. 在 join 类型为 all、index、range 时可以使用 join buffer
3. 每个 join 都会分配一个 buffer
4. join 之前会分配 join buffer,在 query 执行完毕释放。
5. join buffer 只会保存参与 join 的列。
6. 优化器管理参数 optimizer_switch 中的 block_nested_loop 参数控制 BNL 算法是否应用,默认开启,设置为 off 会选择 NLJ 算法。
impossible where
where 子句的值总是 false,不能用来获取任何元素;
select tables optimized away
在没有 group by 子句的情况下,基于索引优化 MIN、MAX操作或者对于 MyISAM 存储引擎优化 count(*) 操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化;
distinct
去除重复行
四、其它
使用 show warnings; 命令在执行 explan 后查看优化器优化后的SQL语句,也就是执行计划执行的SQL。
使用 MySQL profile 工具分析 SQL 语句执行的瓶颈。
- 使用命令 show variables like 'profiling'; 查看 profiling 是否开启。
- 使用命令 set profiling = 1; 开启 profiling。
- 在执行 SQL 后,使用命令 SHOW profiles; 查看执行的 SQL 语句的 Query_ID
- 使用命令 SHOW PROFILE cpu, block io for QUERY {Query_ID}; 查看 SQL 语句执行各个阶段的CPU、IO耗时。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· DeepSeek “源神”启动!「GitHub 热点速览」
· 上周热点回顾(2.17-2.23)