InnoDB 索引

目录

1.索引的常见模型

1.1 InnoDB的索引模型

1.2 索引维护

2.回表

2.1 覆盖索引

2.2最左匹配原则

2.3 索引下推

3.普通索引和唯一索引

3.1 查询过程

3.2 更新过程

3.2.1 change buffer

3.2.2 change buffer使用场景

4.字符串字段添加索引

4.1 使用前缀索引

4.2 前缀索引对覆盖索引的影响

4.3 其他方式

5.小结

---

数据库的索引其实就是为了提高数据查询的效率，就像书的目录一样。

1.索引的常见模型

索引实现比较常见的数据结构有哈希表、有序数组和搜索树三种。

• 哈希表在做添加的时候，速度会非常快；但缺点是，因为不是有序的，所以哈希表做区间查询的时候速度会非常慢。所以哈希表只适用于等值查询的场景。
• 有序数组在等值查询和范围查询场景中的性能都非常优秀。但是，在需要更新数据的时候，比如往中间插入一条记录就必须挪动后面所有的记录，成本很高。所以，有序数组索引适用于一般不需要再修改数据的情况。
• 平衡二叉树的搜索效率非常高，但是实际上索引不止存在内存中，还要写到磁盘。如果一棵100万节点的平衡二叉树，树高20，一次查询可能需要20个数据块，硬盘寻址会非常耗时。为了让一个查询尽量少地读磁盘，就必须让查询过程尽量少的数据块，此时我们就需要使用N叉树。

不管是哈希还是有序数组，或者N叉树，它们都是不断迭代、不断优化的产物。数据库发展到今天，跳表、LSM树等数据结构也被用于引擎设计中。

1.1 InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，每个索引都对应一个B+树。比如我们有如下的表结构：

CREATE TABLE `T` (
  `id` int(11) NOT NULL,
  `k` int(11) NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中R1~R5的(ID,k)值分别为(100,1)，(200,2)，(300,3)，(500,5)和(600,6)，两棵树的示意图如下：

从图中可以看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

• 主键索引的叶子节点存的是整行数据。在InnoDB中，主键索引也被称为聚簇索引。
• 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引。

基于主键索引和普通索引的查询有什么区别呢？

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树。
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到id的值为500，然后到ID索引树搜索一次。这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

1.2 索引维护

B+树为了维护索引的有序性，在插入新值的时候需要做必要的维护。以上图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果插入的ID值为400，则需要挪动后面的数据，空出位置。极端的情况下，R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。当然有分裂就有合并，当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

了解了索引维护的过程，我们思考一下，为什么在建表语句中推荐使用自增主键？

自增主键的插入模式，正符合了我们前面提到的递增插入的场景。每次插入一个新记录，都是追加操作，都不涉及到挪动其他记录。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。除了考虑性能以外，从存储空间的角度来看，由于每个非主键索引的叶子节点上都是主键的值。如果用占用空间比较大的字段，比如身份证号，那么每个非主键索引的叶子节点占用空间也会变大。所以，从性能和存储空间的角度考量，自增主键往往是更合理的选择。

思考一下，对于上面例子中的InnoDB表T，如果要重建索引k，SQL可以这么写：

alter table T drop index k;
alter table T add index(k);

如果要重建主键索引，可以这么写：

alter table T drop primary key;
alter table T add primary key(id);

我的问题是，对于上面这两个重建索引的做法，是否有不合适的地方，为什么？

其实，重建索引k的做法是合理的，可以达到省空间的目的。但是重建主键索引是不合理的，不论是删除主键还是创建主键，都会将整个表重建。

2.回表

在下面的这个表T中，如果执行select * from T where k between 3 and 5，需要执行几次树的搜索凑在哦，会扫描多少行？

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

现在，我们一起看一下这条SQL语句的执行过程：

1. 在k索引树上找到k=3的记录，取得ID=300
2. 再到ID索引树查到ID=300对应的R3
3. 在k索引树取下一个值5，取得ID=500
4. 再到ID索引树查到ID=500对应的R4
5. 在k索引树取下一个值k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了k索引树的3条记录，回表了两次。由于查询结果所需要的数据只能在主键索引上有，所以不得不回表。那么有没有可能经过索引优化，避免回表过程呢？

2.1 覆盖索引

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经覆盖了我们的查询需求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提高性能，所以覆盖索引是一个常用的性能优化手段。

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和姓名建立联合索引。假设市民表的定义如下：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要做一下权衡。

2.2最左匹配原则

B+树这种索引结构，可以利用索引的“最左前缀”来定位记录。假如我们有（name,age）这个联合索引：

可以看到，索引项是按照索引定义里面出现的字段顺序排序的。按照最左匹配原则，当你的逻辑需求是查到所有名字是“张三”的人或者是查到所有名字第一个字是“张”的人，都可以用上这个索引。这个最左匹配既可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。那在建立联合索引的时候，如何安排索引内的字段顺序呢？

• 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑的。
• 第二要考虑的就是空间原则。比如我们的市民表中，name字段是比age字段大的，因此创建一个(name,age)的联合索引和一个(age)的单字段索引，要优于(age,name)的联合索引和一个(name)的单字段索引。

2.3 索引下推

前面我们说到，最左前缀匹配可以在索引中定位记录。这时，那些不符合最左前缀的部分，会怎么样呢？

还是以市民表的联合索引（name,age）为例，如果现在有个请求是检索出表中，名字第一个字是张，而且年龄是10岁的所有男孩。那么SQL语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

此时，这个语句在搜索索引树的时候，只能用到“张”，找到第一个满足条件的记录ID3，然后是判断其他条件是否满足。这个判断过程在MySQL5.6之前和之后是完全不同的：

• 5.6之前，只能从ID3开始一个一个回表，到主键索引上找出数据行，在对比字段值。
• 5.6开始，引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

这两个过程的执行流程图如下图所示：

在无索引下推执行过程图中，InnoDB并不会去看age的值，只是按顺序把name第一个字是“张”的记录一条条取出来回表。因此，需要回表4次。在索引下推执行过程图中，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。此时只需要回表2次。

3.普通索引和唯一索引

唯一索引相较于普通索引，能够保证数据库表中每一行数据的唯一性。那我们在不同的业务场景下，应该选择普通索引还是唯一索引？假设我们要从市民信息表中根据身份证号查询姓名，而身份证号（id_crad）是唯一的，由于身份证号字段比较大，不建议把身份证号当做主键，那么此时要么给id_card字段创建唯一索引，要么创建一个普通索引。如果此时业务代码保证了不会写入重复的身份证号，这两个选择逻辑上都是正确的。那从性能的角度考虑，你选择唯一索引还是普通索引呢？接下来，我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。我们还是沿用前面的表T：

3.1 查询过程

假设，执行查询的语句是select id from T where k=5。

• 对于普通索引来说，查找到满足条件的第一个记录(5,500）后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

其实，这个不同带来的性能差距是微乎其微的。InnoDB的数据是按数据页为单位来读写的，也就是说当需要读一条记录的时候，并不是将这条记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。所以当k=5的记录被找到后，它所在的数据页就都在内存中了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和计算了。极端情况下，k=5这个记录是数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。但是，对于整型字段，一个数据页可以存放近千个key，因此出现这种情况的概率是很低的。所以，总体来看，这两种操作成本是差不多的。

3.2 更新过程

为了说清楚，普通索引和唯一索引对更新语句性能的影响需要先介绍一下change buffer。

3.2.1 change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读取这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个也有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭的过程中，也会执行merge操作。显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。并且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。需要注意的是，change buffer也是使用的buffer pool里面的内存，因此不能无限增大。change buffer的大小可以通过参数innodb_change_buffer_max_size来动态设置。这个参数如果设置为50，表示change buffer的大小最大占用buffer pool的50%。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，因此必须要将数据页读入内存才能判断。所以，唯一索引的更新是不会使用change buffer的。

现在，我们理解了change buffer的机制，那么我们再一起来看看如果要在这张表中插入一个新纪录(4,400)的话，InnoDB的处理流程是怎样的？

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下：

• 对于唯一索引，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束。
• 对于普通索引，找到3和5之间的位置，插入这个值，语句执行结束。

这种情况下，普通索引和唯一索引对更新语句性能影响的差别只是一个判断，性能损耗微乎其微。

第二种情况是，这个记录要更新的目标页不在内存中。此时，InnoDB的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句结束。
• 对于普通索引来说，直接将更新记录在change buffer，语句执行结束。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所以对更新性能的提升是非常明显的。

3.2.2 change buffer使用场景

通过上面的分析，你已经清楚了使用change_buffer对更新过程的加速作用，也清楚了change_buffer只限于用在普通索引的场景下，而不适用于唯一索引。那么，现在有一个问题就是：普通索引的所有场景，使用change buffer都可以起到加速作用吗？

对于写多读少的业务来说，页面在写完之后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见于账单类，日志类的系统。反之，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以对于这种业务模式来说，change buffer反而起到了副作用。

回到前面的问题，普通索引和唯一索引应该怎么选择呢？

其实，这两类索引在查询性能上几乎是没有差别的，主要考虑的是对更新性能的影响。所以，尽量选择普通索引。当然，如果所有的更新后面，都马上伴随着对这个记录的查询，那么我们应该关闭change buffer。而其他情况下，change buffer都能提升更新性能。

4.字符串字段添加索引

假设，现在维护一个支持邮箱登录的系统，用户表是这样定义的：

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb; 

由于要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select f1, f2 from SUser where email='xxx';

为了加快查询速度，我们一般需要给email字段添加索引。那应该如何定义这个索引呢？

4.1 使用前缀索引

由于MySQL是支持前缀索引的，可以定义字符串的一部分作为前缀索引，也可以使用整个字符串作为索引。定义语句如下：

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第一个语句创建的index1索引里面，包含了每个记录的整个字符串，而第二个语句创建的index2索引里面，对于每个记录都是只取前6个字节。这两种不同的定义的索引示意图如下：

从图中可以看出，由于email(6)这个索引结构中每个邮箱字段都只取前6个字节，所以占用的空间会更小，这就是使用前缀索引的优势，但这同时可能会增加额外的记录扫描次数。比如，我们执行下面的这条：

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

使用index1，执行顺序如下：

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

这个过程，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用index2，执行顺序如下：

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

在这个过程中，要回主键索引取4次数据，也就是扫描了4行。因此，使用前缀索引后，可能导致查询语句读数据的次数变多。但是，对于这个查询语句来说，如果你定义的index2不是email(6)而是email(7)，也就是说取email字段的前7个字节来构建索引的话，即满足前缀"zhangss"的记录只有一个，也能够直接查到ID2，只扫描一行就结束了。也就是说使用前缀索引，定义好长度，就可以做到即节省空间，又不用额外增加太多的查询成本。

那应该如何选择最佳长度呢？实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少，可以用如下SQL语句来判断出区分度：

mysql> 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 SUser;

查询结果count统计值越高，代表的区分度越好，当然占用的空间可能越大。我们可以预先设定一个可以接受的损失区分度的比例，比如5%。然后，在返回的L4~L7中，找出不小于L*95%的值，然后取出长度最短的前缀。假设这里L6和L7都满足，可以选择前缀长度为6。

4.2 前缀索引对覆盖索引的影响

使用前缀索引可能会增加扫描行数，这会影响到性能。除此之外，前缀索引还会影响到覆盖索引，我们看如下的SQL语句：

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

该语句只要求返回id和email字段，所以如果使用index1的话，可以利用覆盖索引，从index1查到结果后直接返回了，不需要再到ID索引查一次。如果使用index2（即email(6)索引结构）的话，就不得不再回到ID索引去判断email字段的值，因为系统并不确定前缀索引是否截断了完整的信息，需要到ID索引再查一次进行确认。也就是说，使用前缀索引就无法使用覆盖索引对查询性能的优化了，这也是我们在使用前缀索引时需要考虑的因素。

4.3 其他方式

对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时，应该怎么办呢？比如，身份号对于一个市的人来说前几位都是相同的，如果要满足区分度的要求可能需要创建长度为12以上的前缀索引，才能够满足区分度要求。但是索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会降低。那有没有既可以占用更小的空间，也能达到相同的查询效率呢？

第一种方式是使用倒序存储。如果我们存储的身份证号是倒过来存的，每次查询的时候，可以使用SQL：select field_list from t where id_card = reverse('input_id_card_string');由于身份证号的最后6位通常没有太多的重复，所以最后这6位就可能提供了足够的区分度。

第二种是使用hash字段。可以在表上再创建一个冗余的整数字段，来保存身份证的校验码，同时在这个字段上创建索引：alter table t add id_card_crc int unsigned, add index(id_card_crc);然后每次插入新纪录的时候，都同时用crc32()这个函数得到校验码存到这个新字段。由于两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断身份证号是否精确相同。
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'。这样索引的长度就变成了4个字节，比原来小了很多。

这两者之间的区别如下：

• 倒序方式相比hash字段，无需消耗额外的存储空间。
• CPU消耗方面，reverse函数相比crc32()函数额外消耗的CPU资源会更小一些。
• 从查询效率看，hash字段方式的查询性能会更加稳定一些。因为crc32算出来的值虽然有冲突的概率，但是概率非常小，每次查询的平均扫描行数接近1.

在工作中，我们可以根据自己的场景，灵活的使用这两种方式。

5.小结

本文讨论了InnoDB的索引模型以及索引的概念，包括覆盖索引，前缀索引和索引下推，还介绍了普通索引和唯一索引的使用，以及字符串索引。在满足语句需求的情况下，要尽量少地访问资源是数据库设计的重要原则之一。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标。