《MySQL45讲》读书笔记(四)：索引

此文为极客时间：MySQL实战45讲的 4、5、9、10、11、15、18节索引相关部分的总结

一、Innodb索引模型#

1.主键/非主键索引的区别#

每个索引在Innodb中都是一颗B+树，其中根据索引叶子节点的不同，分为主键索引和非主键索引。

我们可以看到：

• 主键索引将索引和整行的数据都放在了一起，所以又叫聚簇索引
• 非主键索引的叶子节点内容是主键的值。所以又叫二级索引

其中，如果非主键索引查询字段没有做到覆盖索引，就需要先从非主键索引树中找到对应的主键，然后再回到主键索引树找到对应的行数据，这个过程叫做回表。

而相应的，直接把全部的主键索引过一遍，然后每拿到一个主键索引，就把相应的数据拿出来，这个过程叫做全表扫描。

2.索引维护#

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。（同理，相邻的两个页如果删除了数据，也会执行一个合并的过程）

3.根据主键和非主键索引排序#

假设t有字段a，b，c，d，设定（a，b）为主键索引，c，（c，b）为非主键索引，当查询的时候情况如下：

• 使用（a，b），则默认排序为先按a排序，再按b排序
• 使用c，则实际为（c，a，b），先按c排序，再按a排序，接着按b排序
• 使用（c，b），这实际为（c，b，a），先按c排序，再按b排序，接着按a排序

我们可以认为，排序的时候innodb会默认去重并且在排序条件上加上主键

二 .为什么要使用自增主键#

1.索引有序#

当我们使用自增主键的时候，插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。这样的主键是默认有序的，不涉及到挪动其他记录，也不会触发叶子节点的分裂

2.节约空间#

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。一页加载出来的数据就越多

3.非自增主键的情况#

一些项目会使用雪花算法获取 id，主键是递增的，就并不会影响索引的有序性。

三、覆盖索引与最左前缀#

1.索引覆盖#

假如我们建立了一个覆盖字段id和B的联合索引，如果执行的语句是 select id，B from T where id between 3 and 5，由于要查询的字段id和B都已经在B索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引已经“覆盖了”我们的查询需求，我们称为覆盖索引。

2.最左前缀#

由于Innodb的索引结构是B+树，所以索引可以通过最左前缀原则让联合索引的“一部分”也能起作用。

我们以（name，age）联合索引举例：

当搜索条件是

select * from T where name = '张三'

或者

select * from T where name like '张%'

都能通过索引快速定位到第一个符合条件的记录，然后向后遍历获取数据。

可见，这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

这时候，我们要考虑的原则就是空间了。比如上面这个表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

3.索引下推#

根据上一个例子，我们有sql：

select * from T where name like '张%' where age = 10

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比age字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，先对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

简单的说，如果你的判断字段被联合索引覆盖了，但是又不符合最左前缀，那样数据库引擎会自动在非主键索引树阶段就做完判断，避免不必要的回表。

四、前缀索引#

1.前缀索引的优劣#

很多情况下，我们需要根据一个长字符串类型的字段去查找记录，比如身份证，邮箱，为了避免全表扫描，就需要为字符串字段添加索引。

由于Mysql支持前缀索引，所以我们可以选择将整个字段添加索引，或者只将前一部分的字符串加上索引：

#整个字段
alter table T add index index1(email);
#一部分字段
alter table T add index index2(email(6));

假设我们执行一条查询sql：

select id,name,email from SUser where email='zhangssxyz@xxx.com';

对于完整索引：

1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。

而对于前缀索引：

1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在 index2 上取到的值不是’zhangs’时，循环结束。

根据这个流程，我们不难发现前缀索引有以下问题：

• 索引覆盖失效：由于前缀索引在命中以后，必须再回主键索引树确定一次，所以索引覆盖对前缀索引来说是无效的。
• 回表次数多：使用前缀索引后，可能会导致查询语句读数据的次数变多。

2.如何选择合适的长度#

前缀索引需要有足够的区分度才能提高查找效率。比如有ABCC，ABDD，ABEE三条数据，选前两个个字符作为索引等于没加索引，选前三个字符作为索引就很合适。当然，实际情况肯定会更复杂，我们就需要更具体的分析。

• 首先，算出这个列上有多少个不同的值：

  select count(distinct email) as L from T;
  

• 依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句：

  select 
    count(distinct left(email,4)）as L4,
    count(distinct left(email,5)）as L5,
    count(distinct left(email,6)）as L6,
    count(distinct left(email,7)）as L7,
  from T;
  

• 使用前缀索引必然会损失一部分区分度，所以我们需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，然后选择最短的长度。

3.其他优化方式#

对于邮箱，前缀索引效果还比较明显，因为@之前的字符串一般不会有太多的相似度，但是对于比如像身份证这样，同一个县市里的市民只有后几位才会有较大区别的长字符串，可能就需要设置一个非常长的前缀索引了，这显然不是我们乐意见到的。

• 倒序存储

  我们可以借助reverse()函数实现倒序存储。比如身份证存入的时候我们可以倒序存储，查找的时候也先反转在查找。这样加索引以后只需要选择前几位辨识度高的即可。

• Hash字段

  我们借助crc32/64()函数去获取长字符串的校验码，在表上另外开一个字段用于存储对应的校验码，以长度较短的校验码作为索引。不过由于crc32仍然会出现值重复的情况，所以查询的时候还需要判断拿到的记录是否与条件字段完全一致。

他们的异同如下：

• 都不支持范围查找
• 占用空间：倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
• 额外消耗：序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
• 查询效率：使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32() 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

当然，还有一种折中的方法，就是拆分字段：

对于像邮箱这样的字段，有时候@后面的字段往往都是固定的几种，可以单独拆分出来作为一个字段，@前的作为单独的字段直接加全字段索引，这样减少的字段长度，并且保证也了范围查找的性能。

4.小结#

要给字符串类型字段的加索引，我们有以下几种方式：

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

五、唯一索引和 change buffer#

1.对查找的影响#

对于普通索引，当执行定值查找的时候，会先按索引找到对应的叶子节点，即数据页，然后通过二分法查找到第一条符合条件的数据，然后继续查找直到遇到第一个不符合条件的数据。

而对于唯一索引，当找到第一条符合条件的数据即返回，因为已经能确定是唯一的了。

由于mysql加载数据是根据页来加载的，当已有的页里找不到对应的数据的时候，不会从磁盘单独读取一条数据，而是接着加载下一页然后再在内存里查找，因此，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。由于一个数据页可以放很多的数据，大多数情况下相邻的数据都在同一页，相对于现在强大的cpu性能，节省的那些查找时间可以忽略不计。

也就是说，对于查找，唯一索引和普通索引差别的不大。

2.对更新的影响#

首先我们需要了解一个新东西：change buffer，也就是写缓冲。

change buffer 和 log buffer 一样，也是 buffer pool 的一部分，他会占用 buffer pool 的容量。

我们知道，mysql按页去将磁盘中的数据读取到内存中（一页的大小通常是16k），当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。一般在三种情况下会进行merge：

• 这个数据页被访问
• 在数据库正常关闭的过程中
• 后台线程会定时执行

而唯一索引在插入或者更新时必须先获取对应记录以保证唯一性，也就是说当更新的时候必然要访问数据页，所以唯一索引无法使用 change buffer 。所以，如果要更新一条数据，而该数据所在页又不在内存中，就要先把数据页读入内存，这一过程随机的磁盘IO，是消耗非常大的操作。

所以，一般情况下，不推荐使用唯一索引。除非业务需要保证字段的唯一性。

3.写缓冲的使用场景#

值得一提的是，并不是所有情况下使用 change buffer 都会带来收益，因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多，收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种立刻写立刻读的业务模式来说，change buffer 反而起到了副作用。这种情况就需要关闭写缓冲。

六、索引失效的情况#

一般来说，如果查询很慢，应该优先考虑一下是不是没加索引，或是因为 sql 的写法而导致查询未能走索引。针对以下例子，我们讨论日常可能出现的“索引失效”的情况。

这里我们需要针对“索引失效”的情况做一下区分：

• 全表扫描：即真正意义上的索引失效，指的是不走索引回表而是直接进行全表扫描，把数据一行一行的拿出来对比字段；
• 索引扫描：指的是通过索树快速定位了一部分数据，然后再根据索引树上的主键id会主键索引树把对应的数据拿出来；
• 全索引扫描：指的是介于两者中间的状态：使用了索引，但是把全部的索引都走了一遍

这里的情况大多数是指全索引扫描。

1.对条件字段的函数操作#

假如我们执行了这么一条SQL：

# 统计7月的总记录条数
select count(*) from tradelog where month(t_modified)=7;

其中原本 t_modified 是有索引的，但是使用了 month() 函数之后走了全索引扫描，影响了查询速度。

上图是索引的树结构，我们不难看出，由于索引同层节点间是有序的，如果使用日期去查询的话，可以很快的定位到存在目标数据的下一层对应的父节点，也就是绿色箭头的路线。但是使用了 month() 函数后，由于索引的节点并不直接包含 month() 计算得到的树值，所以是无序的，如上图的二级节点所示，所以只能选择直接从叶子结点把所有的节点都过一遍，也就是说，如果有十万条数据，他就得把十万个索引节点都走一遍。

当然，虽然不能快速定位，但是查询依然通过遍历索引树的方式走了查询，没有直接回表全表查询，也就是说，其实还是走了索引，但是没有用到 B+ 树快速定位的性质，查询是速度还是有所下降的。

总结一下，就是：由于加了函数操作，MySQL 无法再使用索引快速定位功能，而只能使用全索引扫描。

值得一提的是，虽然不是所有的函数操作都会破坏索引树的有序性，但是优化器仍然会选择不使用索引：

# 全索引扫描
select * from tradelog where id + 1 = 10000
# 快速定位
select * from tradelog where id = 10000 - 1

2.隐式类型转换#

假如要执行以下的sql：

# 全索引扫描
select * from tradelog where tradeid = 110717;

其中，由于 tradeid 是 varchar 类型，但是查询的条件却是 int 类型，这导致了隐式的类型转换，也就是使用了函数操作。

而在 mysql 中，字符串和数字比较，是转换字符串为数字，也就说，上面的 sql 实际上等同于：

select * from tradelog where CAST(tradid AS signed int)  = 110717;

这里的原理和上文提到了对条件字段的函数操作是一样的，因为对条件字段的操作破坏了索引树的有序性，导致只能全索引扫描。换而言之，如果不破坏有序性，函数操作就不会影响索引树的快速定位：

# 快速定位
select * from tradelog where id = '110717';
# 上面的sql等同于下面
select * from tradelog where  id = CAST('110717' AS signed int);

如上，id 是 int 类型的字段，那么不会导致索引的快速定位失效。

也就是说：要将函数操作的对象从条件字段变成条件字段的参数

3.隐式字符编码转换#

假如要执行以下的 sql：

select d.* from tradelog l
left join trade_detail d on d.tradeid = l.tradeid
where l.id=2

其中，trade_detail 的 tradeid 字段是有索引的，但是 explain 后却显示查询 trade_detail 仍然全表扫描。

也就是说，我们希望 tradelog 拿到了 tradeid 以后，能够直接在 trade_detail 的 tradeid 索引树上找到对应的记录，然后直接回表取出对应的数据，但是他却没通过索引，而是直接把 trade_detail 扫了一遍，把 tradeid 符合的数据拿出来了。

原因在于，tradelog 的字符集是 utf8，trade_detail 的字符集是 utf8mb4。而

utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

所以对于 trade_detail，他的 sql 实际上是这样的：

# 将trade_detail的tradeid转成utf8mb4
select d.* from trade_detail d where CONVERT(d.tradeid USING utf8mb4) = l.tradeid

可见，这又是一个对条件字段使用了函数操作的情况。

根据上面两种情况的，我们有两种方法来优化这个 sql：

• 将 trade_detail 表转为 utf8mb4 的编码格式

• 将函数操作的对象从条件字段变成条件字段的参数

  select d.* from trade_detail d where d.tradeid = CONVERT(l.tradeid USING utf8mb4) 
  

七、总结#

1. 尽量做到索引覆盖，减少回表次数；
2. 排序总是会在按排序条件排完后，再根据主键排序，所以联合索引的最后不必包含主键字段；
3. 索引需要尽可能的保证有序，并且尽可能的小；
4. 条件字段需要尽可能的使用索引覆盖，以便索引下推，减少回表；
5. 条件字段需要尽可能的按照联合索引的字段顺序排序，以便最左前缀原则生效；
6. 长字符串索引使用索引：
   • 完整索引。这样可能比较占用空间；
   • 前缀索引。节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
   • 倒序存储。再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
   • 创建 hash 字段索引。查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
7. 类型转换和字符集转换的实质都是实用的函数，而函数操作会引起的索引失效和全索引扫描问题，解决方式是将要将函数操作的对象从条件字段变成条件字段的参数