个人博客
公司的项目要上线了，比较忙，隔了挺久没写了，码字不易，还请关注点赞；

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访问得到最终的一个数据页。

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

哪些情况需要创建索引

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

哪些情况不要创建索引

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