InnoDB索引演变

引言

对于一个java开发工程师而言，能够写出一手高端大气上档次、低调奢华有内涵的sql是多么任性的一件事，出于提高自身编程技术的原因，以及对工作负责的态度，最近通过各方面的学习和研究，对InnoDB索引有了更深一步的认知，故写下文章以加深巩固：

文章目录

1. 建表规范
2. 索引概述
3. 索引应用

建表规范

建立数据库表，首先要知道建表的规范，这样才能针对业务设计出合适的表分布及表结构。
建表要满足三范式：
第一范式：表列具有原子性，即单个字段值不能再次被拆分，关系型数据库自动满足第一范式
第二范式：表中数据信息具有单一性，通过主键确保数据单一性
第三范式：表中数据不能冗余，即能通过表中数据推导出来数据，不应该单独建立字段单独保存（第三范式可根据实际情况而定，适当冗余字段能够避免表关联查询，提高查询效率，像一对多的情况就可以适当字段冗余）

当数据表设计并创建成功之后就要考虑是否要对表中的某些字段添加索引，若果需要添加，该添加什么类型的索引？诸如此类的问题不胜枚举，如果要用需知道为什么用，那么首先我们分析一下为什么要是用索引

索引概述

为什么使用索引

在不使用索引的时候，查询数据会遍历全表，直到找到想要的数据，这样就会进行多次的IO操作，如果数据量非常大，会导致IO操作次数增多，严重影响sql执行效率，这时候索引作用就会得到体现。

索引就像是一本字典，在字典中需要查找一个字，可是我们不知道这个字具体在那一页，此时就需要手动一页一页的翻，一页一页的找，直到找到这个字位置，如果命不好的话这个字没准在最后一页。如果使用所用呢？如果使用索引的话，在字典中查找一个字，可以现在目录中找到这个字的位置，具体在哪一页，然后直接根据页码翻到对应的页，就能找到需要的字了，回过头来说索引，索引是怎样的通过目录从海量数据中快速定位到指定数据的呢？

索引树

说到索引树，目前mysql使用的是B+tree形式，那为什么使用的B+tree呢？
首先来看一下B+tree的历史演变：

二叉树

下图为二叉树示例

如图所示“6”为根节点，小于根节点的值为左子树，大于根节点的值为右子树，根节点可以任意设置，如果数据大多数都小于或大于根节点，树会失去平衡，树的高度越高对于越底层的数据而言，查询的效率就会相对越低。
为解决失衡问题，故以此演变出平衡二叉树。

平衡二叉树

下图为平衡二叉树示例

平衡二叉树是在二叉树的基础上演变而来，当二叉树失衡的情况下，会通过自身旋转重新定义根节点，使其左右两个子树高度差值不会大于1，从而达到平衡的状态，但仍未解决因树的高度不断增加而影响查询效率的问题。
若想在保证大数据量的情况下还要保证查询效率就要考虑适当降低树的高度来提高查询效率，因此通过平衡二叉树演变出平衡多路树。

平衡多路树（b-tree）

平衡多路树包括根节点、非叶子节点和叶子结点三部分组成，每个节点存储着关键字，指向下一个节点的指针（叶子结点指向 null），以及对应的数据表数据。
例：m阶平衡树

1. 根节点有m个指向下一节点的指针；（指针个数等于子节点个数）
2. 叶子结点有拥有的指针数 p >= 2 && p <= m；
3. 根节点有m - 1个关键字；
4. 除根节点外每个节点有n个关键字，n >= Ceil(m/2 - 1) && n <= m - 1；
5. 所有节点关键字自左向右依次递增
   b-tree是用于存储在磁盘等外存储设备上的一种平衡查找树，在磁盘中以磁盘块形式存储，每个磁盘块代表一个节点，系统从磁盘中读取数据到内存是以磁盘块为单位进行读取，获取一个数据会将该数据所在磁盘块读取到内存（记为一次IO操作）。
   Mysql使用的存储引擎InnoDB是以页为单位从磁盘中读取数据，页有通用的页头和页尾，页中为根据page类型变化的内容，头部信息中存储着两个指针，一个指向前一个page，一个指向后一个page，另有一个32位的int值作为页的唯一标识，默认每页大小为16k，而单个磁盘块大小无法达到16k，所以InnoDB每次读取到的磁盘数据都是若干连续的磁盘块。
   如图所示的三阶平衡树，每个节点存储着指针信息，关键字信息，以及完整的data数据，这样大大消耗了磁盘空间，因为每个磁盘块的空间有限，如果除叶子结点外，每个节点只存储指针信息和关键字信息，而叶子结点存储data信息，就会使得每个磁盘块存储的数据增多，进而避免出现因为数据量的增多而导致树的高度增加的情况，因此有b-tree演变而出b+tree结构。

b+tree

由图可知，b+tree存储结构除叶子结点外，每个节点只存储指针信息及关键字信息，使得每个节点能存储更多的数据，而叶子结点存储关键字及完整的data数据，叶子节点间使用双向链表连接，为什么要是用双向链表呢？
在非范围查询时，索引树通过二分查找可找到对应数据，这一点b-tree和b+tree没什么区别，只不过b+tree每次都会索引到叶子节点；但是在范围查询的时候双向链表的作用就体现出来了，首先是b-tree每次查询都会进行二分查找，查询的范围越大，查找的次数就越多，而使用b+tree则会使用第一次二分查找获取到的叶子节点数据通过链表指针在相邻的叶子结点中横向查找数据，效率自然要高于b-tree，这大概就是InnoDB选择b+tree作为索引结构的原因吧。

索引应用

聚簇索引：主键索引
非聚簇索引：普通索引

聚簇索引的索引树如上一节当中介绍的实例，data数据储在叶子节点中，data为数据表中的完整数据；
非聚簇索引的索引树叶子节点存储的不是完整的data数据，而是主键值，
所以在使用非聚簇索引查询数据的时候，如果使用的索引，会先查询非聚簇索引树，根据查询到的主键值（关键字）再去聚簇索引中查询对应的数据，这个过程叫做：“回表”

那么是否所有使用非聚簇索引的情况都需要回表呢？
答案很显然，不是
普通索引叶子结点中存储索引字段的数据值和主键值，当sql中查询的数据包含在普通索引树的叶子结点中时，可以直接获取到数据，不需要进行回表操作，这个过程使用的是覆盖索引，覆盖索引不是由我们主动操作的，而是根据sql内容被动执行。

索引方法

hash索引顾名思义，是以map形式存储数据，检索数据可以一次性定位到数据，不用像btree索引那样，通过一次次的IO操作从根节点检索到叶子节点取得数据，因此检索数据效率要比btree索引高，那为什么不使用hash索引呢？

1. hash索引的存储形式导致其使用存在一定的局限性，在等值操作中，hash索引的效率很高，但不适用于范围查询操作，因为存储的时候是通过hash运算值进行存储，不能保证与运算前一致；
2. hash索引不能进行排序操作，存储方式无序；
3. hash索引在操作组合索引时，是将组合索引键合并后进行的hash运算，所以不完全适用组合索引；
4. hash索引不能避免表扫描，不同的键可能存在相同的hash值，hash冲突之后的存储是以链表形式存储，所以需要进行数据表比较获得最终数据结果，降低性能
5. 综合考虑还是使用btree索引方法更加适合大多数业务逻辑

使用原则

索引不是说创建了就一定会使用到，在sql执行过程中，sql优化器都会对此进行优化。

1. 如果sql的where中使用了索引字段，但是通过执行计划发现并没有使用到索引，原因和可能是sql优化器认为不使用索引比使用索引查询 的效率更高；
2. 使用不等号往往也不会走索引；
3. 模糊查询中，字段前侧使用百分号不走索引；
4. 对字段使用函数不会走索引；
5. 对字段进行运算不会走索引；
6. 复合索引中未遵循最左前缀原则，不会走索引；
7. 复合索引最左使用范围查询，后面的字段不会使用到索引；
8. is null和is not null不会使用索引；
9. 类型隐士转换不会走索引；
   10.or不会走索引

小结

本文主要针对InnoDB索引结构及索引使用原则进行了介绍，希望能对大家有所帮助，有任何问题欢迎指正