mysql 语句执行计划分析

avatar
@海尔优家智能科技(北京)有限公司

本文首发于 CSDN , 欢迎关注作者

前言

sql 语句的执行计划就是解释该条 sql 一步一步怎么执行,在执行中那些使用索引、那些进行全表扫描,以及执行顺序,然后可以针对性的对 sql 进行优化。废话少说,直接上货。

sql 语句 EXPLAIN 时,会展示如下几个重要的字段,可以根据字段进行分析。

一. 执行计划

1. ID(执行顺序,值越大,优先级越高)

  1. id 相同,从上到下依次执行

  1. id 不同,里面有子查询,id 的序号会递增,id越大,优先级越高,最先执行

  1. id 有相同和不相同的,id 越高,优先级越高。id 相同,从上到下依次执行。

2. select_type (查询类型)

SIMPLE:简单的select查询,查询中不包括子查询或者 union。

PRIMARY:主查询,即外层的查询。

UNION:若第二个 select 出现在 union 之后,则被标记为 union;若 union 包含在 from 子句的子查询中,外层 select 将被标记为 derived。

SUBQUERY:在 select 或 where 列表中包含了子查询。

DERIVED:在 from 列表中包含的子查询被标记为 derived(衍生),mysql 或递归执行这些子查询,把结果放在零时表里。

UNION RESUL:从 union 表获取结果的 select。

3. table (输出结果集的表(表别名))

4. type (从表中找出目标行的方式,访问类型)

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

好的 sql 查询至少达到 range 级别,最好能达到 ref

  1. system:表只有一行记录(等于系统表),这是 const 类型的特例,平时不会出现,可以忽略不计。
  2. const:表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只需匹配一行数据,所有很快。如果将主键置于 where 列表中,mysql 就能将该查询转换为一个 const。
  3. eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
  4. ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
  5. range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了那个索引。一般就是在 where 语句中出现了 bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不 用扫描全部索引。
  6. index:Full Index Scan,index 与 ALL 区别为index类型只遍历索引树。比 ALL 块,因为索引文件通常比数据文件小。( Index 与 ALL 虽然都是读全表,但index是从索引中读取,而 ALL 是从硬盘读取)。
  7. ALL:Full Table Scan,遍历全表以找到匹配的行。

5. possible_keys(表示查询可能使用的索引)

6. key: 实际使用的索引

7. key_len

表示索引中使用的字节数,查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。key_len 是根据表定义计算而得的,不是通过表内检索出的。

8. ref

使用哪个列或常数与 key 一起从表中选择行。

9. rows: 扫描行的数量

二. 执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息

测试数据

--订单表

CREATE TABLE `test_order` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) DEFAULT NULL,

  `order_id` int(11) DEFAULT NULL,

  `order_status` tinyint(4) DEFAULT NULL,

  `create_date` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)

) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8
--订单详情

CREATE TABLE `test_orderdetail` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `order_id` int(11) DEFAULT NULL,

  `product_name` varchar(100) DEFAULT NULL,

  `cnt` int(11) DEFAULT NULL,

  `create_date` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_orderid_productname` (`order_id`,`product_name`)

) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

Using index

(1)查询的列被索引覆盖,并且筛选条件的是索引的前导列,Extra 中为 Using index

EXPLAIN SELECT user_id,order_id,create_date FROM test_order WHERE user_id=1

Using where Using index

(1)查询的列被索引覆盖,并且 where 筛选条件是索引列之一但是不是索引的不是前导列,Extra 中为 Using where ; Using index, 意味着无法直接通过索引查找来查询到符合条件的数据。

EXPLAIN SELECT user_id,order_id,create_date FROM test_order WHERE order_id=1

(2)查询的列被索引覆盖,并且 where 筛选条件是索引列前导列的一个范围,同样意味着无法直接通过索引查找查询到符合条件的数据。

EXPLAIN SELECT user_id,order_id,create_date FROM test_order WHERE user_id>1 AND user_id <5

NULL(既没有Using index,也没有Using where Using index,也没有using where)

(1)查询的列未被索引覆盖,并且 where 筛选条件是索引的前导列,意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表”来实现,不是纯粹地用到了索引,也不是完全没用到索引,Extra 中为 NULL (没有信息)。

EXPLAIN SELECT user_id,order_id,create_date,order_status FROM test_order WHERE user_id=5

Using where

(1)查询的列未被索引覆盖,where筛选条件非索引的前导列,Extra 中为 Using where

EXPLAIN SELECT user_id,order_id,create_date,order_status FROM test_order WHERE order_id>5

(2)查询的列未被索引覆盖,where 筛选条件非索引列,Extra 中为 Using where

EXPLAIN SELECT user_id,order_id,create_date,order_status FROM test_order WHERE order_status=0

using where 意味着通过索引或者表扫描的方式进程 where 条件的过滤,反过来说,也就是没有可用的索引查找,当然这里也要考虑索引扫描+回表与表扫描的代价。这里的 type 都是 all,说明 MySQL 认为全表扫描是一种比较低的代价。

Using index condition

(1)查询的列不全在索引中,where 条件中是一个前导列的范围

EXPLAIN SELECT user_id,order_id,create_date,order_status FROM test_order WHERE user_id>0 AND user_id<5

(2)查询列不完全被索引覆盖,查询条件完全可以使用到索引(进行索引查找)

EXPLAIN SELECT  order_id,product_name,cnt FROM  `test_orderdetail`  WHERE order_id=1 AND product_name LIKE '%ss%'

参考:MySQL · 特性分析 · Index Condition Pushdown (ICP)

using index conditoin 意味着查询列的某一部分无法直接使用索引

上述 case1 中,如果禁用 ICP(set optimizer_switch='index_condition_pushdown=off'),执行计划是 using where,意味着全表扫描,如果启用 ICP,执行计划为 using index Condition,意味着在筛选的过程中实现过滤

上述 case1 中

第二个查询条件无法直接使用索引,隐含了一个查找+筛选的过程。两个 case 的共同点就是无法直接使用索引。

结论:

  1. Extra 中的为 Using index 的情况 ,where筛选列是索引的前导列 &&查询列被索引覆盖 && where 筛选条件是一个基于索引前导列的查询,意味着通过索引超找就能直接找到符合条件的数据,并且无须回表。
  2. Extra中的为空的情况,查询列存在未被索引覆盖&& where 筛选列是索引的前导列,意味着通过索引超找并且通过“回表”来找到未被索引覆盖的字段。
  3. Extra中的为 Using where Using index:出现 Using where Using index 意味着是通过索引扫描(或者表扫描)来实现 sql 语句执行的,即便是索引前导列的索引范围查找也有一点范围扫描的动作,不管是前非索引前导列引起的,还是非索引列查询引起的。

三. 团队介绍

三翼鸟数字化技术平台-智能营销交互平台」以用户行为数据为基础,利用推荐引擎为用户提供“千人千面”的个性化推荐服务,改善用户体验,持续提升核心业务指标。通过构建高效、智能的线上运营系统,全面整合数据资产,实现数据分析-人群圈选-用户触达-后效分析-策略优化的运营闭环,并提供可视化报表,一站式操作提升数字化运营效率。