[内功修神]MySQL 索引原理

MySQL 索引原理

现在互联网应用中对数据库的使用多数都是读较多，比例可以达到 10:1。并且数据库在做查询时 IO 消耗较大，所以如果能把一次查询的 IO 次数控制在常量级那对数据库的性能提升将是非常明显的，因此基于 B+ Tree 的索引结构出现了。

B+ Tree 的索引

如图所示是 B+ Tree 的数据结构。是由一个一个的磁盘块组成的树形结构，每个磁盘块由数据项和指针组成。

所有的数据都是存放在叶子节点，非叶子节点不存放数据。

查找过程

• 以磁盘块1为例，指针 P1 表示小于 17 的磁盘块，P2 表示在 17~35 之间的磁盘块，P3 则表示大于 35 的磁盘块。

• 比如要查找数据项 99 ，首先将磁盘块1 load 到内存中，发生 1 次 IO。接着通过二分查找发现 99 大于 35，所以找到了 P3 指针。

• 通过 P3 指针发生第二次 IO 将磁盘块4加载到内存。再通过二分查找发现大于87。

• 通过 P3指针发生了第三次 IO 将磁盘块11 加载到内存。最后再通过一次二分查找找到了数据项99。

由此可见，如果一个几百万的数据查询只需要进行三次 IO 即可找到数据，那么整个效率将是非常高的。

观察树的结构，发现查询需要经历几次 IO 是由树的高度来决定的，而树的高度又由磁盘块，数据项的大小决定的。

磁盘块越大，数据项越小那么树的高度就越低。这也就是为什么索引字段要尽可能小的原因。

聚集索引

InnoDB 存储引擎表是索引组织表，即表中的数据按照主键顺序存放。

聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点存放的即为整张表的行记录数据，也将叶子节点称为数据页，每个数据页通过一个双向链表来进行连接。

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

非聚集索引

叶子节点不包含行记录的全部数据。叶子节点除了包含键值外，每个叶子节点中的索引行还包含相应行数据的聚集索引键。

非聚集索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个非聚集索引。

当通过非聚集索引来查询数据时，InnoDB存储引擎会遍历非聚集索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

如在一颗高度为3的非聚集索引树中查找数据，那需要对这棵树比遍历3次找到指定的主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问得到最终的一个数据页。

对于这种从非聚集索引到聚集索引的查找过程称为回表，如何避免回表使用覆盖索引。

索引的劣势

• 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句。

哪些情况需要创建索引

1. 主键自动建立唯一索引。
2. 频繁作为查询的条件的字段应该创建索引。
3. 查询中与其他表关联的字段，外键关系建立索引。
4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
5. 查询中统计或者分组字段

哪些情况不要创建索引

1. 表记录太少。
2. 经常增删改的表 。
3. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
4. Where条件里用不到的字段不创建索引。
5. 频繁更新的字段不适合创建索引。因为每次更新不单单是更新了记录还会更新索引，加重IO负担。

Mysql 为什么选择B+Tree

哈希索引

通过哈希算法计算出字段值所对应的数组下标，出现哈希冲突，采用链表来解决。

哈希表的特点就是可以快速的精确查询，但是不支持范围查询，会导致全表扫描。

哈希表适合的场景是等值查询的场景，就只有KV（Key，Value）的情况，例如Redis、Memcached等这些NoSQL的中间件。

有序数组

无序的哈希表不支持范围查询，那么在等值查询的和范围查询的时候都很好了；

但是有序数组的适合静态数据，因为如果我们新增、删除、修改数据的时候就会改变他的结构，移动数据成本很高。

有序数组的使用场景是可以用来做静态存储引擎，用来保存静态数据，例如你2019年的支付宝账单，2019年的淘宝购物记录等等都是很合适的，都是不会变动的历史数据。

二叉树和平衡二叉树

二叉树是有序的，所以是支持范围查询的。

但是他的时间复杂度是O(log(N))，为了维持这个时间复杂度，更新的时间复杂度也得是O(log(N))，那就得保持这棵树是完全平衡二叉树。

二叉树的缺点是如果数据很多，树就会很高，查询的成本就会随着树高的增加而增加。

B Tree

B Tree的表示要比完全平衡二叉树要“矮”，原因在于B Tree中的一个节点可以存储多个元素。

B+ Tree

为什么不是B Tree呢，而使用B+ Tree，可以发现同样的元素，B+ Tree的表示要比B Tree要“胖”，原因在于B+ Tree中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

B Tree一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。

而B+ Tree是B Tree的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

提高了的原因是会有指针指向下一个节点的叶子节点。

B+ Tree 一个节点大小为一页或页的倍数最为合适

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。

如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。

所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

Mysql 的基本存储结构是页(记录都存在页里边)：

• 各个数据页可以组成一个双向链表
• 而每个数据页中的记录又可以组成一个单向链表
• 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽， 然后再遍历该槽对应分组中的记录即可快速找到指定的记录
• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

参考

• 《MySQL技术内幕：InnoDB存储引擎》