Mysql索引原理

索引基本介绍

索引是帮助 Mysql 高效获取数据的排好序的数据结构。

二叉树、红黑树、Hash表、B树等都可以作为索引。

• 二叉树高度不可控，查询性能差；
• 红黑树高度比二叉树小，但是也不可控，而且插入是有自旋问题，数据量大时性能比较差；
• Hash表性能是最强的，但是无法支持区间范围查找，排序等功能；

Mysql 的索引使用B树的变种B+树来实现，并且对B+树做了点扩展。

B树

二叉树、红黑树这种数据结构，在大数据量的时候的时候，高度不可控，从而导致搜索慢。红黑树还有插入时的自旋问题，数据量大时插入慢。

我们希望在大数据量的时候，依然只需要访问很少次数就能查找到想要的元素。

对红黑树进行横向扩容，就形成了B树。

特点

• 每个节点存储更多的元素，组成一个大节点，这样可以很大程度上减小树的高度。一个大节点是一个磁盘块
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
• 非叶子节点，前后会有一个指针，存储子节点的地址信息
• 叶子节点具有相同的深度，叶子节点的指针为空

缺点：每个节点都会存储data数据，而一个节点的data数据量可能很大，innodb引擎，data里存储的是其他列的数据，占用的存储空间可能就比较大，Mysql推荐一个大节点总容量固定是16KB（通过参数 Innodo_page_size 设置，默认16KB），在大节点总容量固定的情况下，如果data比较大，那么这个大节点所能容纳的节点数量就会少，高度也就会增加。所以Mysql不会直接使用B树。

B+树

B+树是B树的一个变种。

特点

• 非叶子节点不存储data，只存储索引，所以可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用单向指针连接，提高区间访问的性能

Mysql对B+树又进行了一些扩展，以支持更多的功能

• 高度固定为3层
• 每个大节点的存储空间限制是16KB，不管是叶子节点还是非叶子节点
• 叶子节点的指针是双向的，而且最后的大节点还会跟第一个连接，以支持区间正序/倒序排序等功能。

下图来源于网络！

为什么大节点存储空间限制16KB？

以 InnoDB 主键索引为例：

主键 bigint 8B，下一个节点的指针 6B，一个大节点约包含 16KB / (8+6)B 约等于 1170 个索引元素，所以第三级的有 1170 * 1170 个大节点。

第三级的每个大节点也是每个16KB，假设每个data占1KB，那个一个大节点有16个元素，最终节点数量就是 1170 * 1170 * 16，约2000多万。

所以，千万级别的表，查找表里任意元素，也只要经过3次磁盘I/O。

InnoDB & MyISAM 引擎

最大区别：

• MyISAM引擎中，data存储的是索引所在行的磁盘文件指针
• InnoDB引擎中，data存储的是索引所在行的其他所有字段数据

InnoDB索引实现

主键索引

表数据文件本身就是按B+树组织的一个索引结构文件

聚集索引(也叫聚簇索引)：叶子节点包含完整的数据记录(就比如InnoDB的主键索引)，索引跟表的数据放在一个文件里

InnoDB表必须有主键，如果没有，mysql找不到唯一的列，它会自动生成一个列

为什么主键最好是整形且自增？

整形：查找的时候会有大量的索引元素的比较，整形比较很快。UUID比较起来就远远慢于整形比较。而且UUIT占用的存储空间也比整形大。

自增：自增的情况下，新的节点都是往后面加，基本上不会出现元素分裂、再平衡的问题。如果不是自增，可能一个大节点16KB存储空间已经满了，想再往里面插入时，会导致一个节点分成两个节点，并且第二层会进行再平衡，效率会大大降低。

非主键索引

data存储的不是所有所在行的其他所有字段，而是索引所在行的主键！

需要遍历两棵树

为什么要这么设计？

• 一致性：不需要所有索引全部建立好之后才能把数据才插入
• 节省存储空间：完整的数据只保存一份

MyISAM索引实现

非聚集索引(也叫稀疏索引)：索引和索引所在行的数据，在不同的文件中。索引在MYI文件中，数据在MYD文件中，分开存储。

下图来源于网络！

联合索引底层结构

联合索引的基本结构与主键索引是一样的，只不过它的索引键值为联合索引中所有列的值。

索引中的每个叶子节点，按照建立索引时列的顺序来递增排列的。例如上图中所用中有三列：(name，age，postion)，那首先比较 name，name 小的排在前面。当name相同时，age小的排在前面，依次类推！

这个联合索引的结构，也决定了 Mysql 查询的时的一个规则：最左前缀原则。

SELECT * FROM employees WHERE age = 26 AND position ='java开发';  // 不使用索引
SELECT * FROM employees WHERE position = 'java开发工程师';              // 不使用索引
SELECT * FROM employees WHERE name = 'lili';                                       // 能使用索引

联合索引的结构决定了，查询的时候按照建立索引字段的顺序逐个比较，所以如果没有第一列，那么无法定位数据，也就无法使用索引了。同理，如果只有第一列跟第三列，那么只能根据第一列来查找，第三列无法生效！