聊一聊MySQL的索引，MySQL索引为什么要选择B+树

我们先了解一下什么是索引：

索引本质上就是一个数据结构，可以帮助我们更快的进行数据的查询。就像一本书前的目录一样，可以快速帮我们定位到我们要找的那一页。比如我们常见的散列表，在不发生哈希冲突的前提下，可以在O(n)的情况下完成数据的访问，插入和删除。还有一种比较常见的就是我们的二叉树，它可以在O(logN)的时间复杂度下完成数据的查找，但是在某些情况下二叉树会退化成链表，导致时间复杂度变为O(N)；

我们再来了解一下使用MySQL进行数据读取的过程：

以 select * from user where id>5 为例，id为主键索引，那么搜索过程为：

存储引擎InnoDB --》 访问kernel --》进行磁盘IO(读取索引文件) --》 将数据加载到内存中 --》用索引的数据结构构建B+树加速搜索。

聚簇索引与非聚簇索引

---

接下来我们来聊一聊MySQL的聚簇索引和非聚簇索引。

在MySQL中，我们常用的存储引擎为MyISAM和InnoDB，它们的底层索引都是由B+树进行构建的，但是他们数据存储的方式却有所不同。

在InnoDB引擎下，数据和索引在磁盘中是存放在一起的，全部存放在xxx.idb文件下，而MyISAM的数据和索引是分开存储的，分别存储在xxx.myi和xxx.myd文件下，这也是为什么InnoDB要求表一定要有主键的原因，数据和索引是存储在一起的，没有主键索引就无法生成主键索引树。

因为索引和数据存放的位置不同，导致生成的索引树节点存放的数据不同。在InnoDB主键索引树的节点中key存放的就是主键，value就是主键对应的这一行数据本身。而在MyISAM中，因为索引和数据是分开存放的，所以key存放的是主键，value存放的是数据在内存中的地址。

像InnoDB这种key和数据本身存放在一起的称之为聚簇索引，像MyISAM这种value存放的不是数据本身的称之为非聚簇索引。

自适应hash索引

---

我们在上文提到过，使用哈希表(散列表)构成索引是非常鸡肋的，因为他会频繁的造成hash冲突，导致我们的搜索效率下降。在Java中的HashMap是使用数组+链表+红黑树来解决hash冲突的。

其实除了hash冲突之外，还有很多我们不使用hash索引的原因：

1. 根据key使用hash算法生成的hashCode是随机的，也就是我们在存储数据时是没有任何顺序可言的，这也就导致我们无法使用where id > 5 这种范围查询和like模糊查询。

2. 数据是存放在硬盘中的，每次读取数据都会进程磁盘IO，哈希表无法有效减少磁盘IO次数。

但这并不代表在MySQL中没有hash索引。memory存储引擎就是hash索引，InnoDB也有着自适应哈希索引。

当我们使用二级索引树(下文会提到)进行搜索时，如果我们频繁的使用上面的索引进行查找，那么MySQL就会自动为我们以二级索引树的key生成一个hash索引，用以加快等值查询时的速度。但这并不代表自适应hash索引效率是较高的。因为维护自适应hash索引也需要耗费性能。

3. 自适应哈希索引会占用InnoDB的buffer pool

4. RW-latch等待的线程数量，InnoDB默认设置了8个分区，可能会导致等待的线程数量过多，且当自适应hash索引命中略小于二级索引树时，我们就应该关闭自适应hash索引。

索引树

---

MySQL的索引树是选择使用B+树进行实现的。索引树分为两种，一种是主键索引树，顾名思义，是以主键作为key进行构建的索引树；一种是辅助(二级)索引树，是以非主键作为key进行构建的索引树。

我们给出一张表的字段：

id，userAccount，userPassowrd 三个字段，id为主键，userAccount为普通索引。

在构建主键索引树时，树节点的key为主键id，value为这一行数据本身。

在构建辅助索引树时，树的节点key为普通索引userAccount，value为主键id。当我们执行select * from user where userAccount = 'xxxxx' 时，MySQL就会构建辅助索引树，在辅助索引树上进行搜索。但是我们搜索的是select *，而在辅助索引树上是没有userPassowrd的，所以MySQL Server会拿着在辅助索引树上查到的key对应的主键id再去到主键索引树上进行查询，这种情况我们称之为回表。

为了避免回表现象，我们可以在只要id和userAccount时使用 select id, userAccount from user 这样的SQL语句进行查询，避免使用select * 出现回表。

在需要userAccount和userPassword时，也可以为userAccount和userPassword建立联合索引(多列)，这样辅助索引树上的key就为userAccount和userPassword，就不会发生回表了。

为什么选择B+树

---

选择B+树肯定是经过对比发现B+树更为合适的，那么我们下面就对比这说一下B+树的优点，以及为什么不使用别的数据结构。

1. 为什么不适用AVL树

AVL树是二叉平衡树，它确实不会和二叉树一样退化为链表，但是它会有频繁的旋转。当插入一个新节点时旋转的复杂度还好，最多只需要一次旋转即可(单旋或者双旋)；但是当删除数据导致树失衡时，需要维护从被删除节点到根节点这条路径上所有节点的平衡，旋转的量级为O(lgN)。

维护AVL树所带来的代价可能比它带来的好处更高。

2. 为什么不使用红黑树

红黑树是AVL树的变种，它并不要求严格的平衡，而是大致平衡。它给树的节点增加了颜色，红色或黑色。

红黑树舍弃了部分平衡性增强了在树失衡时旋转的效率，红黑树在删除或者新增节点时可以以O(1)的旋转次数保证树的平衡。但是因为不严格的平衡且一个节点只能保存一组key，value导致树会变得很高。我们知道MySQL的数据是存放在磁盘中的，所以它不能有效的解决磁盘IO效率问题。

但是在数据保存在内存中，红黑树是非常适合的，比如Java的HashMap就是使用数组+链表+红黑树进行构建的。

3. 为什么不使用B树

B树是为了磁盘而生的。

B树又称为多阶平衡查找树，它的非叶子节点可以有m个子树，m通常为300-500。这样的话树就会变矮许多，可以增强磁盘IO效率。

B树的优势除了树比较矮壮，还有对访问局部性原理的利用。所谓局部性原理，是指当一个数据被使用时其附近的数据有较大概率在短时间内被使用。B 树将键相近的数据存储在同一个节点，当访问其中某人数据时，数据库会将该整个节点读到缓存中，当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO;换句话说，B 树的缓存命中率更高。

但是B树也是存在问题的：

1. 搜索效率不确定，搜索普通节点和叶子节点的查找效率不稳定。

2. 不适合范围查询，做范围查询时需要遍历整棵树，导致效率下降。

3. 在节点中存放key和value，value会占用节点较多的空间，导致存放的key变少

为了解决上述问题，我们引进了B树的变种B+树。

4. 为什么选择B+树

我们先来说一下B+树的特点：

1. 非叶子节点中只存放key，而不存放value

2. value全部存放在叶子节点上，并且叶子节点之间有双向链表可以互相指向。

3. 在叶子节点上保存的数据也是有顺序的

根据上述特点B+树比B树更适合作为MySQL的索引结构。

B+树的有点：

4. 更少的磁盘IO次数，树更加矮状，因为非叶子节点中不需要存放value，可以存放更多的key。

5. 支持范围查询和like模糊查询，因为叶子节点有序且可以互相指向。

6. 更稳定的查找效率，因为所有的数据都存放在叶子节点中。

建立索引时需要考虑什么

---

1. 首先肯定是为我们经常用来作为查询条件的字段设置索引

2. 对经常需要排序，分组和联合操作的字段设置索引，防止在排序时出现file sort。

3. 建立索引时按照最左匹配原则进行添加，不然有可能会使索引失效。

4. 不要一味的增加过多索引，因为索引本身也会占存储空间，需要维护。

5. 增加索引时要考虑索引的长度，不要为长度太长的字段添加索引，不然会降低索引的效率。

索引失效

---

1. 第一肯定是没有遵守最左匹配原则。

2. 在where后使用or，或在like模糊查询时用%xx开头。使用xx%结尾不会使索引失效。

3. 如果索引使字符串类型，一定要用' '引起来，不然会MySQL会将字符串隐式转换为整形，导致索引失效。

4. 如果全盘扫描的速度与索引扫描相差无几，就没必要添加索引，比如对一些枚举值添加索引。MySQL Server会自动使用全盘扫描。