乐趣区

关于数据库:Mysql索引覆盖

作者:京东批发 孙涛

1. 什么是笼罩索引

通常状况下,咱们创立索引的时候只关注 where 条件,不过这只是索引优化的一个方向。优良的索引设计应该纵观整个查问,而不仅仅是 where 条件局部,还应该关注查问所蕴含的列。索引的确是一种高效的查找数据形式,然而 mysql 也能够从索引中间接获取数据,这样就不在须要读数据行了。笼罩索引(covering index) 指一个查问语句的执行只须要从辅助索引中就能够失去查问记录,而不须要回表,去查问汇集索引中的记录。能够称之为实现了索引笼罩。在 mysql 数据库中,如何看出一个 sql 是否实现了索引笼罩呢?





从执行打算看,Extra 的信息为using index,即用到了索引笼罩。

2. 笼罩索引为什么快

innodb 存储引擎底层实现包含 B + 树索引和哈希索引,innodb 存储引擎默认的索引模型 / 构造是 B + 树,所以大部分时候咱们应用的都是 B + 树索引,因为它良好的性能和个性更适宜于构建高并发零碎。依据索引的存储形式来划分,索引能够分为 聚簇索引和非聚簇索引。聚簇索引的特点是叶子节点蕴含了残缺的记录行,而非聚簇索引的叶子节点只有索引字段和主键 ID。非聚簇索引中因为不含有残缺的数据信息,查找残缺的数据记录须要回表,所以一次查问操作实际上要做两次索引查问。而如果所有的索引查问都要通过两次能力查到,那么必定会引起效率降落,毕竟能少查一次就少查一次。

笼罩索引就实现了从非聚簇索引中间接获取数据,所以效率会晋升。





3.SQL 优化场景

(1)无 where 条件

请看上面的 sql









执行打算中,type 为 ALL,代表进行了全表扫描,扫描行数达到了 26274308,所以执行工夫为 9.25 秒, 也是失常的。

那么如何优化?优化措施很简略,就是对查问列建设索引。如下,

alter table instance_space_history add index idx_org1(org1); 看增加索引后的执行打算





Possible_keys 为 null,阐明没有 where 条件时优化器无奈通过索引检索数据;

然而看 extra 的信息 Using index,即从索引中获取数据,缩小了读取的数据块的数量。





在看理论优化成果,扫描行数没变,然而应用了笼罩索引,查问工夫从 9.25 秒缩短到 5.67 秒。思考: 无 where 条件的查问,能够通过索引来实现索引笼罩查问。但前提条件是,查问返回的字段数足够少,更不用说 select * 之类的了。毕竟,建设 key length 过长的索引,始终不是一件好事件。

(2)where 条件区分度低

应用区分度极低的字段作为 where 条件的查问 SQL,对于 dba 或者研发人员优化始终是比拟头疼的问题,这里介绍一种思路,就是通过索引笼罩来优化。t_material_image 是一张 8 亿多数据的大表,where 条件的 material_type 字段区分度很低,上面是没加任何索引的执行打算和查问工夫(7.35 秒)。









最容易想到的优化形式,就是给 where 条件的字段加索引,增加索引语句如下:alter table t_material_image add index idx_material_type (material_type);

再来看执行打算





通过执行打算和测试后果看,确实是有成果的,然而走索引后的查问效率仍然不能满足咱们冀望。而后试着给 material_type,material_id 增加联结索引。alter table t_material_image add index idx_material_id_type (material_type,material_id);









从这个 sql 的执行打算看,呈现 Using index,实现了索引笼罩;再看执行工夫,性能失去了微小的晋升,竟然曾经能够跑到 0.85s 左右了。

思考:

当 where 条件字段区分度低(过滤性差),且 where 条件与查问字段总数较少的状况下,应用索引笼罩优化,是个不错的抉择。

(3)查问仅抉择主键

对于 Innodb 的辅助索引,它的叶子节点存储的是索引值和指向主键索引的地位,而后须要通过主键在查问表的字段值,所以辅助索引存储了主键的值。如果查问所抉择的列只有主键,应该思考通过索引笼罩优化。看上面的两个 sql,字段 pin 和 completion_time有联结索引,where 条件差异只有comment_voucher_status = 0,然而执行工夫差距微小(第一个 sql0.58s, 第二个 sql0.2s),为什么呢?是不是很困惑

















咱们来看执行打算,次要差异体现在 extra,第一个 sql 用到 Using index condition,而第二个 sql 用到Using index,因为 pin 和 completion_time 有联结索引,而且查问后果只抉择了主键 id,所以第二个 sql 笼罩了所有的 where 条件字段和查问后果抉择字段,故实现了索引笼罩。 思考:

当查问字段只有主键时,更容易实现索引笼罩,因为索引只有笼罩 where 条件,就能够实现索引笼罩。

4. 总结与倡议

索引的核心作用:(1)通过索引检索仅须要数据(2)从索引中间接获取查问后果
索引笼罩的条件:(1)Select 查问的返回列蕴含在索引列中(2)有 where 条件时,where 条件中要蕴含索引列或复合索引的前导列(3)查问后果的总字段长度能够承受

退出移动版