mysql语句执行计划分析

木鸡呀

已于 2022-11-10 17:42:43 修改

阅读量2.8k

点赞数 5

分类专栏： sql 文章标签： sql 数据库 mysql

于 2020-03-23 21:40:08 首次发布

本文链接：https://blog.csdn.net/junge1545/article/details/104995621

版权

sql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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

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

一.ID（执行顺序，值越大，优先级越高）

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

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

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

二.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。

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

四.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，遍历全表以找到匹配的行。

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

六.key: 实际使用的索引

七.key_len

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

八.ref

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

九.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语句执行的，即便是索引前导列的索引范围查找也有一点范围扫描的动作，不管是前非索引前导列引起的，还是非索引列查询引起的。

相关文章：回表查询？索引覆盖？

sql语句执行顺序及流程

参考：https://www.jianshu.com/p/514aa0b139d8 MySQL——通过EXPLAIN分析SQL的执行计划 - CoderFocus - 博客园