mysql优化中的explain的使用

explain中的关键字段的分析理解

---

前言

在自己写sql的时候怎么去判断自己的sql是不是能优化以及是否达到自己心里所预期的性能效果时，sql中有一个关键字explain可以供我们查看自己写的sql的性能。explain虽然返回的结果项很多，但是这里我们只关注三种，分别是type，key，rows。其中key表明的是这次查找中所用到的索引，rows是指这次查找数据所扫描的行数（这里可以先这样理解，但实际上是内循环的次数）。而type则是本文要详细记录的连接类型。

---

type – 连接类型

mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。这里直接使用工作中的一张表来测试

CREATE TABLE `rcc_rule_d`  (
  `RID` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '数据唯一记录号',
  `RULE_ID` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规则ID',
  `RULE_CODE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规则代码',
  `RULE_NAME` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规则名称'
  PRIMARY KEY (`RULE_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

一、all

这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
为什么这么说呢？因为all是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。

explain SELECT * FROM `rcc_rule_d` where RULE_CODE = 'IA00000001010010000'

这是因为RULE_CODE列既不是主键也不是索引，因此只能采用全表扫描来查找目标RULE_CODE。

二、index

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。
如果一定要比效率，只需要获取这个表的数据并且排序便可以看出来谁比谁效率高了：

explain SELECT * FROM `rcc_rule_d` order by rule_code

explain SELECT * FROM `rcc_rule_d` order by rule_id

上面可以看出，根据rule_code列排序的连接类型是all型的，但是注意extra列是用到了排序（Using filesort），
而根据rule_id列排序的连接类型是index，而且得到的结果自然是有序的，不许额外的排序。可能正是因为这个缘故，index的效率比all高，但注意这需要相同的条件才成立（既需要排序）。

如果连接类型为index，而且extra列中的值为‘Using index’，那么称这种情况为 索引覆盖；
索引覆盖意味着什么呢？想象这样一种场景，如果说一本新华字典是一张表，当然前面的索引部分（假设按照部首的索引）是这张表的索引，那么索引覆盖就相当于根据部首索引获取第一个字到最后一个字（新华字典的所有字）。我们获得了字典中所有的字，然而我们并没有查一次表，因为我们想要的都在索引中，即索引覆盖。

explain SELECT rule_id FROM `rcc_rule_d` 

上例获取的rule_id刚好为索引列，因此无需回表取数据。

三、range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。

四、ref

出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）

alter table rcc_rule_d add key my_rule_name(`rule_name`); 
explain SELECT rule_name FROM `rcc_rule_d` where rule_name = '测试';

可以看到，在rcc_rule_d表中根据rule_name查找数据的时候，mysql优化器便选择了ref的连接类型。

五、ref_eq

ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个！什么情况下结果集只有一个呢？那便是使用了主键或者唯一性索引进行查找的情况。在没有查找rule_id前我们就知道结果一定只有一个，所以当我们首次查找到这个rule_id，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。

CREATE TABLE `rcc_rule_kng_cfg_a`  (
  `RID` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '数据唯一记录号',
  `RULE_KNG_ID` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规则知识ID',
  `RULE_ID` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规则ID',
  `KNG_CODE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '知识代码',
  `KNG_NAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '知识名称',
  PRIMARY KEY (`RULE_KNG_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

explain SELECT * FROM `rcc_rule_d` a,rcc_rule_kng_cfg_a b where a.rule_id = b.rule_id

上面就可以看到rcc_rule_kng_cfg_a 表是全表扫描的类型，rows=3047代表外层表循环的次数（因为有能关联的数据条数就是3047行），但是rcc_rule_d表的rows怎么是1，怎么可能？刚开始也是很疑惑，这与mysql的查询原理息息相关，rows实际反映的是查询的内循环数，针对外层的每一条数据匹配，rcc_rule_d的确一次就可以命中，因此rows为1。

六、const

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

总结

explain 就像一面镜子，有事没事写完sql记得explain一下。
同时，有很多东西和细节，想要明白清楚，也是没有那么简单的，需要对操作系统以及数据库的底层查询和运行原理要有一个清楚的理解。同时type的几种类型几乎都是基于索引之上的，因此需要对索引有个深入的了解，而且explain的结果可以指导我们什么时候应该加索引，什么时候可以不加索引，什么时候应该使用我们预期的索引。从而让我们更好的使用索引。