索引深入

1.普通索引和唯一索引的选择

对于表中的字段k，在不同业务下考虑添加什么类型的索引可以提高性能。

1.1查询读操作

考虑如下语句(假设k不作为主键)：

select id from T where k=5；

• 唯一索引：首先去k的二级索引树搜索，因为唯一索引保证了索引字段列数据的唯一性，因此找到(k=5,id=500)这一条记录后，直接进行回表。
• 普通索引：首先去k的二级索引树搜索，查询到(k=5,id=500)这一条记录后，会继续查找下一个k的记录，直到碰到第一个k不等于5的记录。最后进行回表。

普通索引因为没有唯一性，因此多了一个继续往后查找，直到不满足查询条件的操作。这里假设业务代码已经控制了k字段的唯一性，并且所查询记录出现在内存数据页的概率较低。

因此普通索引和唯一索引之间的在读操作上性能损耗几乎可以忽略不计，几乎相同。

1.2更新写操作

InnoDB无论是进行读还是写，读取记录时都会将该记录所在的数据页从磁盘读到内存，然后在该数据页上再通过二分法找到具体行的数据。

• change buffer介绍

change buffer本质上是一个更新操作专属的缓冲区，因为在进行更新操作时，并不需要将数据读取并返回客户，因此change buffer解决了更新操作导致的磁盘数据页频繁IO的问题，更新操作时不需要将数据页读到内存中，只需要记录在内存中change buffer(事务提交后通过后台持久化到磁盘)。

具体来说，更新时如果数据页已经在内存当中，那么直接更新。如果数据页不在内存中，更新时InnoDB只需要将更新操作记录在change buffer中，下一次查询操作到来时会触发merge操作，把数据页读入内存后执行change buffer中与这个页相关的操作，然后写redo log(数据页变更)，merge结束。而此时内存中的数据页和change buffer都属于脏页，后续需要刷盘。

因此往往希望merge之前，change buffer存的更改操作越多越好，最后一次性写入到数据页中。好处：①减少磁盘IO，提升语句执行速度②提高内存利用率，减少内存占用。

• change buffer使用条件

对于唯一索引而言，因为每次执行插入语句都需要判断是否破坏唯一性约束(并且马上将结果返回给调用方)，而这必须要将数据页读入内存中才能进行判断，因此就破坏了change buffer的延迟性。既然已经将数据写到内存了，那就没必要再用change buffer，直接修改内存的数据页即可。

因此只有普通索引能够使用change buffer,唯一索引不能使用。业务能够接受的场景下使用普通索引，比如对于线上数据库和归档库，归档库相当于历史数据，已经确保不存在唯一键冲突，因此可以将归档库索引改成普通索引，提高归档效率。

• change buffer更新操作与使用场景
  • 唯一索引
    • 数据页不在内存中：将数据页读入内存，判断是否存在字段冲突，没有冲突则插入数据，语句结束。
    • 数据页在内存中：判断是否存在字段冲突，若没有冲突则写入内存数据页，语句结束。
  • 普通索引(使用change buffer)
    • 数据页不在内存中：在change buffer中记录更新操作，语句结束。
    • 数据页在内存中：直接修改内存中的数据页，语句结束。

✨change buffer机制大大减少了普通索引下，更新操作带来的磁盘IO次数。因此对于写多读少的场景(日志，账单类)，使用change buffer的效果最好。而对于更新之后马上需要进行查询的场景效果比较差，此时多了change buffer的维护成本。

1.3redo log与change buffer下的更新事务流程

为了便于区分与描述，将内存数据持久化到磁盘的过程称之为“写”，而将磁盘的数据读入到内存的过程称为“读”。

redo log分为两部分一部分在内存(redo log buffer)，一部分在磁盘(redo log file)。它的作用主要分为两个：

• 保证数据一致性。通过redo log恢复数据库，进行备份。

• 将随机写转换成顺序写，减少随机写产生的磁盘IO性能消耗(组提交机制)。首先明白一点，无论是redo log还是数据页，在内存还是磁盘中都各有一份备份。以一个包含多个更新操作的事务提交过程为例，对于有无redo log可以分为两种情况：

  • 如果没有使用redo log，那么每做一次更新操作，就需要立刻将内存中的数据页写到磁盘中。而每个更新操作的数据页不一定是连续的，因此“随机写”产生的磁盘IO会非常耗时。

  • 如果使用redo log，那么整个更新事务会按顺序执行以下步骤：

    • 首先先更新内存中的数据页，根据是否使用change buffer，操作方式有所不同。

    • 然后将上述更新操作记录到内存中的redo log buffer。

    • 在内存中提交更新操作事务。

    • 写日志：将内存中的redo log buffer持久化到磁盘redo log file。其中将redo log从内存持久化到磁盘的这个过程就是"顺序IO写"。redo log只是按顺序记录了每一个操作记录，因此刷盘时只需要顺序在redo log file的尾部append添加操作记录。——prepare状态

    • 写数据页：MySQL后台任务将内存中的数据页异步同步刷到磁盘中，此处也分为两种情况：

      ①使用了change buffer，则不需要“真同步”②没有使用change buffer，则需要将数据页同步到磁盘。——commit状态

🔥综上，redo log性能提升的点在于，把数据页从内存随机写到磁盘的过程，转化为将redo log里的更新操作从内存顺序写到磁盘的过程，从而大大减少执行更新操作时的响应时间。最后在后台异步执行数据页的持久化，相当于将这个过程延后了。

总结：因此change buffer节省的是“更新操作”下读磁盘的IO消耗；而redo log节省的实际上是写磁盘的IO响应时间。

2.索引选错异常

MySQL在某些情况下受到更新语句和查询语句的影响，导致选错索引从而影响查询性能。

explain+SQL查询语句：查看当前语句使用的索引，以及执行这条SQL语句扫描的行数rows。

show index from t：查看当前表中每个索引的基数。

2.1优化器

查询语句时，影响优化器选择索引进行查询的因素有以下几个：

• 扫描行数rows

基数(cardinality)：一个索引字段上不同值的个数；基数越大，索引的区分度越好。

采样统计：计算N个数据页上的不同值的平均数。

修正命令：analyze table t;重新统计表的索引信息。

• 回表

当前索引的扫描行数虽然少，但是如果是二级索引还需要进行回表操作，代价更大。

• 排序

当前查询语句包含order by b字段，那么直接使用b作为索引则不需要后续的排序，因为索引b的B+树已经完成了。

2.2索引选择异常处理

如果线上出现某个查询速度变慢，索引选择异常导致性能降低，那么可以尝试以下几种方案：

• ✨采用force index在查询时手动指定采用哪个索引进行查询

select * from t force index(a) where a between 1 and 2000;

存在的问题在于变更的及时性。如果优化器选择的索引没有问题，那么需要重新修改语句。

• ✨修改SQL语句，引导优化器使用正确的索引

举例来说，如果只使用order by b进行查询并排序，此时会选择索引b。而在不影响最终查询结果的情况下，如果将查询语句的排序条件修改为order by b,a；也就是说将a和b索引在排序上的代价都控制为相同的，那么此时优化器就会选择a，因为它的行数更少。

另外增加limit的查询数量，也可以增加该字段的代价。

• ✨直接删除当前优化器误用的索引

方法简单暴力有效，在不影响该索引在其它业务查询性能的情况下，可以使用这种方式。

2.3事务版本与rows计算

假设有如下场景：

• 事务A当前开启了事务
• 事务B开启了事务
• 事务B删除某张表的所有数据，然后重新恢复生成所有数据。
• 事务B执行查询语句，并调用explain查看该条SQL语句执行情况。
• 事务B关闭，事务A关闭

此时explain后会发现优化器计算得到的当前扫描行数rows翻了一倍。具体原因在于，首先事务A没有提交，因此事务B的删除操作无效，也就是事务A那个版本的所有数据还存在原先索引a上。此时B再重新创建恢复数据，就会导致优化器认为索引a有两个版本的数据。只有事务A提交之后事务版本小于低水位，当前索引才不可见。

而如果对于主键而言，rows的计算值并不会因为存在多版本数据而受到影响。它会直接根据表的行数进行估计，使用show table status的值。

3.给字符串字段添加索引

对于email邮箱字符串类型的字段，可以在该字段上添加不同的索引，分析如下：

3.1前缀索引

在email字段上建立取前六个字节的前缀索引，如图所示：

和全字符串索引相比，前缀索引特点在于以下几个方面：

• 索引存储

在索引树上可以很明显的看出，前缀索引每个节点存储的数据更小，从而节省空间。

• 执行过程

SQL语句中包含email字段的查询语句，假设保证email是唯一字段。

全字符串索引：在email索引B+树中拿到主键后回表一次拿到对应的记录。然后回到email索引树查找下一个叶子节点，发现不满足查询条件，立即返回。

前缀索引：同样会在前缀索引树中找到主键，然后回表读取记录。唯一不同之处在于，判断每条记录是否满足查询条件，是在回表时的主键树下进行。换而言之因为它是前缀索引，MySQL并不清楚这个字段上的所有内容，因此每次只能在主键树的叶子节点读取该记录的字段值，直到不满足查询条件。

前缀索引在查询语句中，回表次数明显增加,增加扫描行数，影响性能。因此往往希望建立前缀索引时，能够将数据区分得更开一些，用最小的空间代价换取尽可能高的查询性能。然而不论如何，查询性能再怎么样也比不过全字符串索引。

• 覆盖索引

考虑如下SQL语句:

select id,email from SUser where email="xxxx";

全字符串索引：在email索引树下叶子节点包含主键，因此触发覆盖索引，不需要回表。

前缀索引：MySQL不能通过前缀索引获取该字段上的所有信息，因此select email需要回表在主键树上读取记录。即使通过修改前缀索引字段长度已经包含所有信息，但是InnoDB还是会到id索引查。因此前缀索引会导致覆盖索引优化失效。

3.2其它索引方式

以身份证号等值查询为例，介绍两种索引存储方式。注意它们都不适用于范围查询。

• 倒序存储

存储身份证号时，将身份证号倒着存储建立索引。以为对于身份证号而言，后几位不像前几位包含地址码这些信息，后六位就可以提供足够的区分度。等值查询时记得通过reverse('input')将输入的身份证号翻转，才能和数据库中的数据进行匹配。

本质上是解决前缀索引区分度不够的问题。

• 添加哈希字段

创建一个新的哈希字段并给该字段添加索引，这个哈希字段的值根据身份证来生成。因为可能存在哈希冲突，因此对身份证号的等值查询，除了对当前哈希字段查询以外(最左原则)，还需要包括身份证号字段。通过两个字段同时约束保证查询结果的精确性。

where hash='' and card=''