索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。可以将数据库索引和书的目录进行类比，通过书的目录我们可以快速查找到章节位置，如果没有目录就只能一页页翻书查找了。
索引数据结构
可以用于提升查询效率的索引结构很多，常见的有B树索引、哈希索引和B+树索引。接下来我们会对这些索引一一进行介绍，并说明InnoDB为什么采用B+树作为索引。
磁盘IO
文件是存储在硬盘上面的。当下硬盘的读取速度十分有限，所以在进行查询定位某个数据的时候，应该尽可能地减少磁盘I/O次数。
磁盘预读
由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
局部性原理：CPU访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
合理利用磁盘预读
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
如果我们能合理使用磁盘预读的特性，使每次磁盘IO读到的页中的数据都是有用的，就可以大大提升数据的查询效率。
B树索引
B树可以看作是对二叉查找树的一种扩展，B树允许每个节点有M-1个子节点，B树有以下特点：
- 根节点至少有两个子节点；
- 每个节点包含M-1条数据，节点中的数据安装索引递增顺序排序；
- 节点中有最多有M个指针指向下一层节点，这些指针位于节点的多个数据之间，下一层节点的所有数据值大于指针左侧的数据，小于指针右侧的数据；
- 每个节点至少包含M/2条数据；
接下来我们用下表示例的用户数据来构建B树，如表所示，用户数据包含姓名、性别、年龄三个字段，我们把用户年龄作为数据库主键(假设年龄具有唯一性)，那么构建出来的B树的结构如下图所示。
|||||||||||
|--|--|--|--|--|--|--|--|--|--|--|
|姓名|陈尔|张散|李思|王舞|赵流|孙期|周跋|吴酒|郑史|
|性别|男|男|女|女|男|男|男|女|男|
|年龄|5|10|20|28|35|56|25|80|90|
![B树索引]
相比较与常见的二叉树，B树的一个节点中存放了更多的数据，这样做可以有效的减少一次数据查找过程中的磁盘IO次数：
- 二叉树每个节点只存放一个数据，节点之间用指针关联，节点之间的空间是离散的，所以每个节点都对应一次磁盘IO，查找一次数据的IO次数为O($log_2$N)；
- B树的节点可以存放M-1个数据，如果这M-1个数据刚好可以放到一个页中，那么B树查找一次数据的IO次数为O($log_M$N)；
哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希表是一种以键-值(Key-Value)存储数据的结构，用户可以在O(1)时间复杂度内按照Key查找到对应的Value。
哈希表通常是一个数组，数据在数组中的位置可以按照索引的值安装哈希算法进行计算，如果两个数据的索引值计算出来的位置相同，那么通常可以采用链地址法解决冲突（其它解决地址冲突的方法还有开放定制法，链地址法，公共溢出区法，再散列法等）。
如下表数据所示，我们依旧按照用户的年龄为用户数据建立索引(假设用户年龄不会相同)，我们采用的哈希算法为 addr=age%10，我们可以建立长度为10的数组作为哈希表，按照哈希函数一一把用户放入哈希表，按照用户年龄查找用户时，可以直接计算出用户所在的位置，从而得到用户信息，最终得到的哈希表以及查询流程如下图所示。
| 姓名 | 陈尔 | 张散 | 李思 | 王舞 | 赵流 | 孙期 | 周跋 | 吴酒 | 郑史 |
| 性别 | 男 | 男 | 女 | 女 | 男 | 男 | 男 | 女 | 男 |
| 年龄 | 5 | 10 | 20 | 28 | 35 | 56 | 25 | 80 | 90 |
哈希索引有以下优点：
- 占用的额外空间小，为数据新建一个哈希索引需要的额外空间为O(N)，和索引字段长度无关；
- 查询速度极快，哈希函数合理的情况下，程序可以在O(1)的磁盘IO次数内查找到数据；
哈希索引有以下缺点：
- 无法进行范围查询，哈希过程中已经丢失了索引的顺序性；
- 无法对数据进行排序查找，比如查找年龄最大的用户；
- 无法使用部分索引查找，比如前缀查询等；
- 哈希函数不合理的情况下，会导致哈希冲突问题，造成查询效率变低；
B+树索引
InnoDB使用的索引的数据结构是B+树，数据库表定义中的每一个索引对应一颗B+树，默认的聚簇索引也是一颗B+树，B+树有以下特征：
- 所有节点关键字是按递增次序排列，并遵循左小右大原则；
- 非叶节点的子节点数在1到M之间(下图中M为3)，空树除外；
- 非叶节点的索引数目大于等于ceil(M/2)个且小于等于M个；
- 所有叶子节点均在同一层，叶子节点之间有从左到右的指针；
- 数据存储在叶子节点，非叶子节点只存储索引；
接下来我们用几条示例的用户数据来构建B+树，如表所示，用户数据包含姓名、性别、年龄三个字段，我们把用户年龄作为数据库主键(假设年龄具有唯一性)，那么构建出来的B+树的结构如下图所示。
| 姓名 | 陈尔 | 张散 | 李思 | 王舞 | 赵流 | 孙期 | 周跋 | 吴酒 | 郑史 |
| 性别 | 男 | 男 | 女 | 女 | 男 | 男 | 男 | 女 | 男 |
| 年龄 | 5 | 10 | 20 | 28 | 35 | 56 | 25 | 80 | 90 |
B+树索引数据结构有以下列出的几种优势：
- 查询性能稳定，查询一条数据需要的IO次数往往是树的高度次；
- 范围查询效率高，安装索引范围查询时，可以先查找的第一个满足要求的数据，然后向后遍历，直到第一个不满足条件的数据为止，中间的数据都符合要求；
- 查询效率高，往往一次数据查询只需要2~3次磁盘IO；
- 叶子节点存储所有数据，不需要去B+树之外找数据；
InnoDB为什么采用B+树
在InnoDB引擎中，我们为数据库创建的索引都是以B+树的形式存在，为什么InnoDB不采用哈希索引或者B树索引呢？主要是基于以下原因：
- 数据库查询经常会出现非等值查询，哈希索引在这种情况下无法工作；
- 相比于B树，B+树索引非叶子节点不存放数据，从而磁盘一次IO可以读取更多的索引数据，有效减少磁盘IO次数；
- 数据库查询经常会出现范围查询，B+树底层的叶子节点之间按照顺序排列，可以更有效的实现范围查询；
自增主键
通过上文我们知道，B+树需要维护索引的有序性。
- 当用户向B+树插入数据，如果插入点对应的节点有空余位置，那么只需要挪动节点中的数据，并把需要插入的数据放入B+树即可；
- 当用户向B+树插入数据，如果插入点对应的节点没有空余位置，那么就需要生成一个新的节点，并把一部分数据挪过去；这种情况不仅会影响插入效率，由于分裂出来的节点只有部分数据，所以会导致空间的利用率降低；
- 当用户删除B+树中的数据时，如果节点或相邻节点的数据量很少，那么只需要直接删除数据，并按挪动节点中的其它数据即可；
- 当用户删除B+树中的数据时，如果节点和相邻节点的数据量很少，那么在删除之后，可能需要把节点和相邻节点合并，从而提高空间利用率；
基于B+树需要维护索引有序性的特点，我们对索引字段提出以下建议：
- 对于数据插入比较多的场景，主键索引字段最好是递增的。递增的主键每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
- 主键索引的长度应当尽量小，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
在InnoDB中，我们应当尽量使用自增主键，自增主键有插入效率高、占用空间小等优势。
数据空洞与重建索引
数据空洞
当你对InnoDB进行修改操作时，例如删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行，但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。如果表结构中包含动态长度字段，那么这些空洞甚至可能不能被InnoDB重新用来存新的行，因为空间空间长度不足。
数据空洞带来的问题：
- 删除表中的数据后，表占用的空间不会变小，造成空间浪费；
- 会降低数据查询的速度，因为空洞会占用页空间；
我们可以通过以下SQL来查看数据库中的空洞大小，执行语句如下所示，返回结果中的DATA_FREE表示表中空闲数据块的大小。
select data_length,data_free from information_schema.tables where table_schema='test' and table_name='test';
重建索引
当一张表的索引中的数据空洞过多时，会影响SQL语句的执行效率，此时我们就需要清理这些数据空洞。
清理数据空洞比较好的办法是重建索引，因为重建索引的过程中，会按照索引的大小排序后建立索引，建立出来的索引比较紧凑。
有什么办法可以重建索引呢？我们比较直观的想法肯定是先删除索引，再重建索引。然而不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。
alter table user_info drop primary key;
alter table user_info add primary key(id);
InnoDB中可以通过以下转换数据引擎的语句来重建表的所有索引。这是因为在转换数据引擎（即使没有真正转换）的过程中，会读取表中所有的数据，再重新写入，这个过程中，会释放空洞。需要注意的是，通过这种方法重建索引耗时比较长。
alter table test engine=innodb
本文最先发布至微信公众号，版权所有，禁止转载！