MySQL 索引底层

MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。索引的本质：数据结构

数据库的查询是数据库最主要的功能之一，要想查询速度尽可能快，就得从查询算法上进行优化。最基本的查询算法就是顺序查找了，时间复杂度为O(n)，可见效率很低。还有一些其他的常用查找算法，比如二分查找，二叉树查找，但这些算法都对应特定的结构来使用，二分查找要求数据是顺序的，二叉树查找是基于二叉查找树结构的，但是数据库本身的数据不可能完全满足于各种数据结构。所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这种数据结构以某种方式引用数据，这样就可以在这些数据上实现高级查找算法。这种数据结构就是索引。

举个例子来理解 索引 这种数据结构：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含关键字和一个指向对应数据记录物理地址的指针（关键字可以看作是索引的具体值），这样就可以运用二叉查找在O(logn)的复杂度内获取到相应数据。

上图只是展示了索引的一种可能的方式，但实际数据库中没有使用二叉查找树或其进化版本红黑树来实现索引结构的。

二叉查找树

在二叉查找树中，左节点都比父节点小，右节点都比父节点大。

二叉查找树组织数据，用于查找时效率比较高，因为每次经过一个节点，最多可以减少一半的可能，不过极端情况会出现所有节点都位于同一侧，直观上看就是一条直线，那么这种查询的效率就比较低了，因此需要对二叉树左右子树的高度进行平衡化处理，于是就有了平衡二叉树（Balenced Binary Tree）。

平衡二叉树：每个节点的左右子树高度差最多为1，如果不满足，就通过左旋和右旋来满足这个条件。这样就不会出现一条支路特别长的情况。于是，在这样的平衡树中进行查找时，总共比较节点的次数不超过树的高度，这就确保了查询的效率（时间复杂度为O(logn)）。

B树

B树是平衡树的一种，是一种平衡的多叉查找树。

（B树关于节点，深度以及分裂组合的特性不多赘述）

B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，位于同一个磁盘块中的数据会被一次性读取出来）把节点大小限制在磁盘块大小范围；树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度。

B树进行查找时存在的缺点：

搜索有可能在非叶子节点结束，查找效率不均衡。

范围查找时需要中序遍历。

每个节点都带有数据，每次读取都意味着一次I/O，如果寻址遍历的次数多，就意味着更多的I/O，浪费性能。

B+树：

与B树的不同：

所有的关键字全部存储在叶节点上，且叶子节点本身根据关键字自小而大顺序连接。

非叶结点仅具有索引作用，数据均存放在叶结点中。

 innodb存储引擎中有页（Page）的概念，页是数据库管理磁盘的最小单位，innodb存储引擎中默认每个页的大小为16kb，每次读取磁盘时都将页载入内存中。

系统一个磁盘块的大小空间往往没有16kb这么大，因此innodb每次io操作时都会将若干个地址连续的磁盘块的数据读入内存，从而实现整页读入内存。

B+ 树中各个页之间是通过双向链表连接的（一个叶节点为一页），叶子节点中的数据是通过单向链表连接的。范围查找时可以直接通过链表查找相邻数据。

B+树中间节点没有Data数据，所以同样大小的 页 可以容纳更多的键值。

所以数据量比较大并且数据量相同的情况下，B+树比B树更加“矮胖“，因此使用的IO查询次数更少。（数据量比较小的情况下，B+树未必比B树高度矮）

MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

聚集索引和非聚集索引：

着重介绍 InnoDB 中的聚集索引和非聚集索引：（以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键）。

InnoDB存储引擎表是索引组织表，即表中数据按照主键的顺序存储。聚集索引就是按照表的主键构建一棵B+树，叶节点中存放的是行记录。

每张表只有一个聚集索引，即为主键索引，其他索引为非聚集索引。

InnoDB中，辅助索引与主索引的区别在于辅助索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去主索引中进行查找，这个过程称为“回表”。

MyISAM：主索引和辅助索引都为非聚集索引。

MyISAM索引的叶子节点存储指针，指向行数据。

利用聚集索引查找数据：

select * from user where id>=18 and id <40

①一般根节点都是常驻内存的，也就是说页 1 已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。

从内存中读取到页 1，要查找这个 id>=18 and id <40 或者范围值，首先需要找到 id=18 的键值。

从页 1 中可以找到键值 18，此时我们需要根据指针 p2，定位到页 3。

②要从页 3 中查找数据，就需要拿着 p2 指针去磁盘中进行读取页 3。

从磁盘中读取页 3 后将页 3 放入内存中，然后进行查找，我们可以找到键值 18，然后再拿到页 3 中的指针 p1，定位到页 8。

③同样的页 8 页不在内存中，需要再去磁盘中将页 8 读取到内存中。

将页 8 读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值 18。

此时因为已经到数据页了，此时已经找到一条满足条件的数据了，就是键值 18 对应的数据。

因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。

我们可以一直找到键值为 22 的数据，然后页 8 中就没有数据了，此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。

④因为页 9 不在内存中，就又会加载页 9 到内存中，并通过和页 8 中一样的方式进行数据的查找，直到将页 12 加载到内存中，发现 41 大于 40，此时不满足条件。那么查找到此终止。

最终我们找到满足条件的所有数据，总共 12 条记录：

(18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt)