MySQL的索引结构

目录

索引是什么？

索引的作用

为什么索引的数据结构使用B+Tree？

平衡二叉查找树 AVL-Tree

多路平衡查找树 B-Tree

多路平衡查找树加强版 B+Tree 

B+Tree的优势

MySQL中B+Tree的表现形式

MyISAM引擎（非聚簇索引）

InnoDB引擎（聚簇索引 + 非聚簇索引）

联合索引

最左前缀原则

覆盖索引

---

索引是什么？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

 

索引的作用

索引能极大的减少存储引擎需要扫描的数据量。

索引可以把随机IO变成顺序IO。

索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。

 

为什么索引的数据结构使用B+Tree？

平衡二叉查找树 AVL-Tree

缺点：

太高：树的高（深）度决定着它的IO操作次数。显然，当数据量逐渐增加时，平衡二叉树的深度也会显著增加。

太小：每一个节点保存的数据量太小了。没有很好的利用操作磁盘IO的数据交换特性，也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作。

 

多路平衡查找树 B-Tree

查询的效率取决于与磁盘IO的次数，减少磁盘IO的次数就必须要压缩树的高度，B-Tree 很好的解决了平衡二叉树的小与高的问题，让瘦高的树尽量变成矮胖的树。

 

多路平衡查找树加强版 B+Tree 

 

B+Tree的优势

• 磁盘读写能力强。B+Tree非叶节点不保存数据相关信息，只保存关键字和子节点的引用，所以一个节点可以保存更多关键字，一次磁盘加载的关键字更多。
• 扫表能力更强。B+Tree关键字对应的数据都保存在叶子节点上， 进行全表扫描时，只需要遍历叶子节点，无需遍历整棵树。
• 遍历、排序、范围搜索的能力更强。B+Tree叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系，所以对于这种依赖顺序或范围的操作更有优势。
• 检索效率更加稳定。B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的。

 

MySQL中B+Tree的表现形式

MyISAM引擎（非聚簇索引）

MyISAM的索引都属于B+Tree结构的非聚簇索引，索引与数据是分开的，B+Tree的叶子节点上存放的是数据的地址。主键索引和辅助索引没有区别，只是主键索引必须是唯一的。

 

InnoDB引擎（聚簇索引 + 非聚簇索引）

InnoDB的主键是B+Tree结构的聚簇索引，将主键组织到一棵B+Tree中，而行数据就储存在叶子节点上。聚簇索引是按大小排列的，因此对于范围查询的效率很高。

InnoDB中除了主键索引其他辅助索引都是非聚簇索引，叶子节点则存储的是主键的值，所以通过辅助索引查询其实需要两个过程，先确定数据的主键，再通过主键进行查询。

 

联合索引

由多个列组成的索引称为联合索引。

善于利用联合索引有以下好处：

减少开销：建一个联合索引(a,b,c)，实际相当于建了(a),(a,b),(a,b,c)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的节省开销。

覆盖索引：对联合索引(a,b,c)，如果有如下的sql: select a,b,c from test where a=1 and b=2；那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作，提升性能。

效率高：索引列越多（不超过5个），通过索引筛选出的数据越少，减少回表次数。

 

最左前缀原则

对索引中关键字进行匹配时，一定是从左往右依次进行，不能跳过。如果最左边是模糊的，那么将无法命中索引。

例如对于单列索引：a like'%123'；联合索引 (a,b,c)：where b=2 and c=3; 都无法命中索引。

理解了索引结构 B+Tree 的特点之后，就不难理解最左前缀原则了。

 

覆盖索引

如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。

能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。

例如我们有 (a,b,c) 三列组成的联合索引：

SELECT  c  FROM TABLE WHERE a = 'aaa'  AND  b = 'bbbb';

这样就用到了覆盖索引。

覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能。