Skip to main content

索引的代价

索引可以非常有效地提升查询效率，既然这么好，我给每个字段都创建一个索引行不行？ ----答案是不行，过度使用索引，我们在空间和时间上都会付出相应的代价。

3.1空间代价​

索引就是一棵B+数，每创建一个索引都需要创建一棵B+树，每一棵B+树的节点都是一个数据页，每一个数据页默认会占用16KB的磁盘空间，每一棵B+树又会包含许许多多的数据页。所以，大量创建索引，你的磁盘空间会被迅速消耗。

3.2时间代价​

空间上的代价你可以使用“**钞能力”**来解决，但时间上的代价我们可能就束手无策了。

链表的维护

我以主键索引为例举个例子，主键索引的B+树的每一个节点内的记录都是按照主键值由小到大的顺序，采用单向链表的方式进行连接的。如下图所示：

img

如果我现在要删除主键id为1的记录，会破坏3个数据页内的记录排序，需要对这3个数据页内的记录进行重排列，插入和修改操作也是同理。

注：这里给大家提一嘴，其实删除操作并不会立即进行数据页内记录的重排列，而是会给被删除的记录打上一个删除的标识，等到合适的时候，再把记录从链表中移除，但是总归需要涉及到排序的维护，势必要消耗性能。

假如这张表有12个字段，我们为这张表的12个字段都设置了索引，我们删除1条记录，需要涉及到12棵B+树的N个数据页内记录的排序维护。

更糟糕的是，你增删改记录的时候，还可能会触发数据页的回收和分裂。还是以上图为例，假如我删除了id为13的记录，那么数据页124就没有存在的必要了，会被InnoDB存储引擎回收；我插入一条id为12的记录，如果数据页32的空间不足以存储该记录，InnoDB又需要进行页面分裂。我们不需要知道页面回收和页面分裂的细节，但是能够想象到这个操作会有多复杂。

如果每个字段都创建索引，所有这些索引的维护操作带来的性能损耗，

查询计划

执行查询语句之前，MySQL查询优化器会基于cost成本对一条查询语句进行优化，并生成一个执行计划。如果创建的索引太多，优化器会计算每个索引的搜索成本，导致在分析过程中耗时太多，最终影响查询语句的执行效率。

3.3回表的代价​

1、额外磁盘IO：如果回表的目标数据页恰好在内存中的话效果倒也不会太差，但如果不在，则需要额外的磁盘IO操作，增加响应时间，特别是大型表上；

2、增加CPU开销：回表过程需要消耗额外的 CPU 资源，因为数据库引擎需要执行额外的操作来定位并返回所需的数据行。

3、增加网络传输开销：如果数据库引擎和数据存储不在同一台服务器上，回表可能导致额外的网络传输开销，因为需要将数据从存储服务器传输到查询引擎所在的服务器。

4、增加锁的竞争：回表操作可能导致额外的锁的竞争，尤其是在高并发的环境中。如果多个查询需要回表，并试图访问相同的数据行，可能会引发锁的争夺，影响系统的并发性能。

所以：

  1. 能不回表就不回；
  2. 必须回表就减少回表的次数。

优化回表策略：

3.3.1索引覆盖​

想一下，如果非聚簇索引的叶子节点上有你想要的所有数据，是不是就不需要回表了呢？比如我为 name 和 phone 字段创建了一个联合索引，如果我们恰好只想搜索 name、phone、主键字段：

select id,name,phone from t_user where name='张三';

可以直接从叶子节点获取所有数据，根本不需要回表操作。

我们把索引中已经包含了所有需要读取的列数据的查询方式称为覆盖索引（或索引覆盖）。

重要概念：

3.3.2索引下推​

还是拿name和phone的联合索引为例，我们要查询所有name为「蝉沐风」，并且手机尾号为6606的记录，查询SQL如下：

SELECT * FROM user_innodb WHERE name = "蝉沐风" AND phone LIKE "%6606";

由于联合索引的叶子节点的记录是先按照name字段排序，name字段相同的情况下再按照phone字段排序，因此把%加在phone字段前面的时候，是无法利用索引的顺序性来进行快速比较的，也就是说这条查询语句中只有name字段可以使用索引进行快速比较和过滤。正常情况下查询过程是这个样子的：

  1. InnoDB使用联合索引查出所有name为蝉沐风的二级索引数据，得到3个主键值：3485，78921，423476；
  2. 拿到主键索引进行回表，到聚簇索引中拿到这三条完整的用户记录；
  3. InnoDB把这3条完整的用户记录返回给MySQL的Server层，在Server层过滤出尾号为6606的用户。

如下面两幅图所示，第一幅图表示InnoDB通过3次回表拿到3条完整的用户记录，交给Server层；第二幅图表示Server层经过phone LIKE "%6606"条件的过滤之后找到符合搜索条件的记录，返给客户端。

img

img

值得我们关注的是，索引的使用是在存储引擎中进行的，而数据记录的比较是在Server层中进行的。

现在我们把上述搜索考虑地极端一点，假如数据表中10万条记录都符合name='蝉沐风'的条件，而只有1条符合phone LIKE "%6606"条件，这就意味着，InnoDB需要将99999条无效的记录传输给Server层让其自己筛选，更严重的是，这99999条数据都是通过回表搜索出来的啊！关于回表的代价你已经知道了。

现在引入索引下推。准确来说，应该叫做索引条件下推（Index Condition Pushdown，ICP），就是过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。ICP是在MySQL5.6之后完善的功能。

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

再回顾一下，我们第一步已经通过name = "蝉沐风"在联合索引的叶子节点中找到了符合条件的3条记录，而且phone字段也恰好在联合索引的叶子节点的记录中。这个时候可以直接在联合索引的叶子节点中进行遍历，筛选出尾号为6606的记录，找到主键值为78921的记录，最后只需要进行1次回表操作即可找到符合全部条件的1条记录，返回给Server层。

很明显，使用ICP的方式能有效减少回表的次数。

另外，ICP是默认开启的，对于二级索引，只要能把条件甩给下面的存储引擎，存储引擎就会进行过滤，不需要我们干预。

正确使用索引的一些建议​

选择合适的字段创建索引​

  • 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

被频繁更新的字段应该慎重建立索引​

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

限制每张表上的索引数量​

索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

尽可能的考虑建立联合索引而不是单列索引​

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

注意避免冗余索引​

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

字符串类型的字段使用前缀索引代替普通索引​

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

避免索引失效​

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

  • 使用 SELECT * 进行查询; SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
  • 创建了组合索引，但查询条件未遵守最左匹配原则;
  • 在索引列上进行计算、函数、类型转换等操作;
  • 以 % 开头的 LIKE 查询比如 LIKE '%abc';;
  • 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
  • IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
  • 发生隐式转换;
  • ……

推荐阅读这篇文章：美团暑期实习一面：MySQl 索引失效的场景有哪些？。

删除长期未使用的索引​

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

Last updated on