十七、SQL 执行计划——Explain

一、Explain的作用

  explain 命令主要用于查看 SQL 语句的执行计划,该命令可以模拟优化器执行 SQL 查询语句,可以帮助我们编写和优化 SQL。那么 explain 具体可以提供哪些信息,帮助我们如何去优化 SQl 的呢?

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

二、如何使用

使用方式: 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耗时。
posted @   维维尼~  阅读(919)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· DeepSeek “源神”启动!「GitHub 热点速览」
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示