MySQL第4讲 索引 三种索引模型和联合索引详解

MySQL剖析 第四讲 索引 三种索引模型和联合索引详解

1. 索引

三种常见的索引模型：哈希表，有序数组，搜索树

1.1 哈希表

• 特点：哈希表的存储形式是键值对的形式，如果某个字段key被建立索引后，哈希表就会建立key和数据行的对应关系，哈希表会将这个字段key经过哈希函数计算后确定一个存储位置，这个位置存储的就是表对应的value的值。也就是说只要确定了key，那么一般可以很快找到对应的value。但是哈希表还是避免不了哈希冲突，所以存在哈希冲突时，一个key对应的记录就会形成一个链表。
• 适用场景：哈希表的索引排列并不是递增的，所以往表中增加新的数据时速度会很快，只需要往后追加，但是这意味着区间查询的速度很慢。比如此时有一个学生表，需要根据身份证号找到学生名字时，如果要找的是身份证号在【m，n】区间的学生，由于此时身份证号并未按顺序排列，所以要找到这个区间的身份证号就得全表扫描。所以哈希表结构的索引适用于等值查询。

1.2 有序数组

• 如上述例子，存储直接按照身份证号递增顺序保存，要找到某一条数据，可以使用二分法快速找到，而查找某个区间的数据也是类似，只要找到最小和最大的数据即可。但是不适用于频繁更新数据的表，如果要往中间插入一个记录，那就得挪动后面所有的数据。所以有序数组适用于静态存储，比如类似于2017年某个城市的所有人口信息，这类不会再修改的数据。

1.3 二叉搜索树

• 二叉搜索树在查询和更新操作是一个折中的方案，但是二叉树节点所能存放的索引太少了，查找一个值会不断访问磁盘取出索引。那么为了让查询过程访问尽量少的数据块，应该使树的节点上可以存放更多的索引，所以将二叉树经过改良优化后，InnoDB采用了B+树索引模型。

4. InnoDB的索引模型——B+树

4.1 主键索引和非主键索引

在InnoDB中，表都是根据主键顺序以索引的形式存放的，数据都存放在主键索引（也叫聚簇索引）的叶子节点上，索引的存放都是B+数结构的。那非主键索引（也叫辅助索引）的叶子节点又是存放的什么呢？存放的是主键的值，也就是说如果某个查询是基于非主键索引的，那么这个SQL会先走一遍非主索引，找到主键的值，再走一遍主索引，然后才能在主索引的叶子节点上找到真正的数据。

这也是为什么我们应该尽量使用主键作为查询条件，如果使用非主索引进行查询，那就需要多扫描一次索引树。另外，建议主键长度不宜过长，因为非主索引的叶子节点存放的是主键的值，主键占用的存储空间过大会使得非主索引搜索树占用空间大。

4.2 自增主键

为什么建议使用自增主键呢？因为自增主键可以很方便地将主键索引继续追加到原来的B+树上，不需要挪动其它记录，一般也不会触发叶子节点的分裂。如果使用表中某个有业务逻辑的字段作为主键，比如身份证号，那么新插入的值是无序的，这会导致B+树的频繁分裂和重建，性能影响较大。而且自增主键往往占用空间会更小。

5. 联合索引

5.1 覆盖索引

前面说到如果某个查询是基于非主索引的，那么这个查询过程会先经过辅助索引搜索树，再走一遍主索引。这个回到主键索引搜索树的过程叫做回表。如果在覆盖索引的条件下，就不用再回表了。

例1：在表T中，主键是ID，K字段是普通索引。那如果执行的语句类似于这样select ID from T where k between 3 and 5，相当于根据K查找ID，ID是主键，所以ID的值已经在K索引树上了，因此不需要经过回表就可以得到查询结果。

例2：在某些建立联合索引的情况下，也不一定会经过回表。如在市民信息表上建立了（身份证号、姓名）的联合索引，那么如果要根据市民的身份证号查询姓名，那也可以用到覆盖索引，不需要再回表。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

5.2 最左前缀原则

如果一个表建立了联合索引（a，b），那么索引树中是先按照左边的a排序，a相同时再按照b排序。所以某个查询如果是基于索引a的，那么这个联合索引也是可以用到的。

• 问1：那么在建立联合索引的时候，如何安排索引内的字段顺序呢？也就是说是用（a，b)还是（b，a）更好呢？

  这个时候应该考虑索引的复用能力。也就是说如果已经建立了（a，b）这个索引，那么相当于已经有a这个索引了，如果你又恰好需要a这个索引，那么此时当然是建立（a，b）更好。换句话说如果能够通过调整联合所以内的字段顺序，可以少维护一个索引，那么这个顺序可以被优先考虑。

• 问2：那如果既需要联合索引，又需要两个索引a和b的单独索引呢？

  这个时候可以从空间占用考虑了，如果a的字段比b占用空间小，那就建立（b，a）和a，而不是（a，b）和b，反之亦然，这样做可以使得索引占用空间更小。

5.3 索引下推

问：以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的：mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

在MySQL5.6之前，这个语句在搜索索引树时，先找到“张”，接着找出对应位置的主键，再回表找出数据行，然后对比字段值。这样是不是太麻烦了呢，因为联合索引还包含一个字段age，能不能先找出“张”开头的，接着再判断age是不是10，如果不是那就不用回表再找了，取出下一个name和age继续判断。在MySQL5.6引入的索引下推优化，就是这样做的。在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

Ps：如果某个查询不是基于主索引的，那么在该索引字段相同时，MySQL会自动按照主键大小排列。
例：表T的主键是（a, b)这个联合索引，表T还包含一个字段c，那么此表中还暗含了哪些索引呢？当使用索引c来查询时，当c的值相同时，数据会按照主键大小排列，也就是相当于建立了c-a-b这个联合索引，如果有使用（c，a）联合索引的需求，也不需要再建立一次索引了，因为表中其实已经有了。