【MySQL数据库】InnoDB索引原理

本文主要从整体上把INNODB的索引涉及到的知识点进行梳理，让读者从整体把握索引的原理，具体内容还需要读者自行查看MySQL技术内幕一书，因为网上大多数文章基本都是拷贝这本书的内容，并且有些文章会误导读者，具体的内容还是耐心点看书吧！

1.索引是什么？

索引就像是一本书的目录，假设我们想要在书中找到某一小节的内容，如果没有目录，我们是不是要从头到尾顺序找一遍，这非常浪费时间，但有了目录，我们就可以快速定位到该小节的页码，并找到该小结的内容。索引的作用就是这样，可以帮助我们快速找到指定的内容。

2.MySQL  InnoDB索引的存储结构

InnoDB使用的是B+tree数据结构，所有的记录都在叶子节点上，并且是按照顺序存放的，根节点和分支节点中不保存数据，只用于索引。具体可以看这篇文章：B+tree数据结构

3.MySql的系统架构图

从图中可以看出，mysql底层是使用文件系统来存储数据库。对于文件系统，大家需要区了解一下扇区、磁盘块等相关内容，这部分是mysql索引物理实现的基础。

4.InnoDB的逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有的数据都被逻辑地存放在一个空间中，称之为表空间。表空间又由段（segment）、区（extent）、页（page）组成。逻辑存储结构如下图所示：

 表空间：在默认情况下InnoDB引擎有一个共享表孔甲ibdata1，即所有的数据都存放在这个表空间中，如果用户启用了参数innodb_file_per_table，则每张表内的数据可以单独存放到一个表空间内。此时，你会发现每一个表都有一个 表名.frm文件和 表名.ibd文件。注意：这些单独的表空间文件只存储该表的数据、索引和插入缓冲BITMAP等信息。其余信息还是存放在默认的表空间。

区：区是由连续页组成的空间，每个区的大小位1MB.为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区。默认情况下，存储引擎页的大小为16KB，即一个区中一共有64个连续的页。

页：页是InnoDB磁盘管理的最小单位，默认大小是16KB.常见的页类型有：数据页 undo页 系统页 插入缓冲位图页等。页的数据结构如下图所示：

记录行：InnoDB存储引擎是面向行的，也就是说数据是按行存入.ibd文件的，而行是有格式的，就像TCP协议一样，每一个二进制都代表了各自的含义，无规矩不成方圆。InnoDB行格式有四种，Compact、Redundant、Compressed和Dynamic。只要了解一种就可以了，大同小异，大体意思就是我这一行的长度是多少，下一条记录的位置在哪里等。数据结构如下图所示：

以上数据结构都是从逻辑层面进行的抽象，那么物理结构是怎样的呢？

我们现在在本地数据库添加一个student表，并向其中添加两条记录。如下图所示：

我们使用 UltraEdit软件打开School.ibd文件，如下图所示：

 数据页是从0x0000c0000(16K*3=0xc000)处开始，这也就验证之前说的页的大小是16KB，保证了同一页的数据在连续的磁盘块上，提高了查询效率。具体的二进制文件分析请看MySQL技术内幕。

5.InnoDB和MyISAM索引的区别

MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。MYISAM使用的是非聚簇索引，非聚簇索引的两颗B+树看上去没有任何不同，节点的结构完全一致只是存储的内容不一样。主键索引的B+树存储的是主键，而辅助索引B+树存储的是辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点保存了数据存储的真实物理地址。

InnoDB 只聚集在同一个页面中的记录。通常向下读取一个节点的动作可能会是一次磁盘IO操作，不过非叶节点通常会在初始阶段载入内存以加快访问速度。聚集索引保存的是数据的复制，所以控制的是逻辑顺序，即使物理地址发生改变，行号变化，都不会影响聚集索引。InnoDB主键索引的叶节点存储的是主键值和剩余的列值，也就是存储了真实的记录，而辅助键索引存储的是索引值和主键值，这样设计的好处是，当记录由于行移动或者数据页分裂合并等操作造成记录的位置发生改变时，只需要更新主键索引，其他的辅助键索引完全不用变。而MyISAM叶节点存储的都是真实物理地址，所以当记录的地址发生改变时，所有的索引都需要更新。

如下图所示：

6. 使用自增ID做主键的理由

聚簇索引的物理存放顺序和主键索引的顺序是相同的，只要索引是相邻的，那么对应的数据也一定相邻的存放在磁盘上。而如果主键不是自增ID，可以想象，聚簇索引需要不断的进行分页，调整记录的物理地址，这样是非常耗费时间的。使用自增主键可以让索引结构变得紧凑，磁盘碎片少，效率高。

7.聚簇索引的列的选择

• 主键列,该列在where子句中使用并且插入是随机的。
• 按范围存取的列，如pri_order > 100 and pri_order < 200。
• 在group by或order by中使用的列。
• 不经常修改的列。
• 在连接操作中使用的列。

总结：InnoDB索引巧妙的利用文件系统中磁盘块的概念，使用页来进行作为B+tree树的节点，这样可以在从磁盘读取每一个页的时候，保证同一页的数据相邻存放，提高了读取的效率。

思考题：我们知道聚簇索引适合范围存取，假设我们对列  A添加辅助索引 ，然后以A列作为查询条件：

select * from student where A>10 and A<100 这个查询是怎么使用索引的？如何提高查询效率？

答案：Multi_Range Read优化  随机访问转化为顺序访问。

参考文献

MySQL技术内幕：InnoDB存储引擎

1.MySQL索引背后的数据结构及算法原理

2.浅谈算法和数据结构: 十 平衡查找树之B树

3.聚簇索引与非聚簇索引