本篇内容包括:MySQL 索引的概述 、索引的数据结构、索引的分类、索引的失效以及索引的设计原则
在关系数据库中,索引是一种数据结构,为存储引擎提高访问速度的数据结构,它一般是以包含索引键值和一个指向索引键值对应数据记录物理地址的指针的节点的集合的清单的形式存在。
索引的作用:数据是存储在磁盘上的,查询数据时,如果没有索引,会加载所有的数据到内存,依次进行检索,读取磁盘次数较多。有了索引,就不需要加载所有数据,因为B+树的高度一般在 2-4 层,最多只需要读取 2-4 次磁盘,查询速度大大提升。
索引的适用场景:
索引的不适用场景:
MySQL 索引的实现采用的是 B+ 树,B+ 树是 B- 树的变体,也是一棵多路搜索树。
B+ 树相较于 B- 树最主要的特点是:
在 B+ Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的key值数量,降低 B+ 树的高度。
B+ 树的叶子节点上有指针进行相连,因此在做数据遍历的时候,只需要对叶子节点进行遍历即可,这个特性使得 B+ 树非常适合做范围查询。
二叉树索引:左子节点小于父节点,右子节点大等于父节点;索引会受插入顺序影响,增加复杂度,比如插入的是已排序数据时,导致二叉树变成了链表;,查找复杂度为 N;
红黑树索引:自平衡二叉查找树,每次新增数据,都会进行大量的平衡判断,数据量特别大的时候,红黑树的深度也会很大,再进行搜索时会比较耗时;
HASH 索引:基于Hash表,在确定值的情况下,对于读写定位都很快;区间范围查找困难,比如大于、小于、between,order by 和 group by也很困难;
B-Tree 索引:一个节点包含多个索引,这样树的高度可控。我们知道 B+ 树就是 B- 树的变体,之所以不采用 B- 树,是因为B树每个节点中不仅包含数据的 key 值,还有 data 值。而每一个页的存储空间是有限的,如果 data 数据较大时将会导致每个节点能存储的 key 的数量很小,要保存同样多的 key,就需要增加树的高度。树的高度每增加一层,查询时的磁盘 I/O次 数就增加一次,进而影响查询效率。
MySQL 的索引分类有多种方式,其中按存储方式来区分的话,就包括聚簇索引(clustered index)和非聚簇索引(secondary index,也称辅助索引或普通索引)
此外 MySQL 索引的按照不同类型还可以分为:主键索引与辅助索引、联合索引、前缀索引、覆盖索引、全文索引之类的
主键索引:叶子节点保存数据
辅助索引:叶子节点保存主键值
前缀索引也叫局部索引,比如给身份证的前 10 位添加索引,类似这种给某列部分信息添加索引的方式叫做前缀索引。
联合索引:MySQL可以使用多个字段同时建立一个索引,叫做联合索引;
最左前缀匹配原则:在 MySQL 建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。比如我们配置了一个 A、B、C 三个字段的联合索引,我们用 A、AB、ABC 的方式都是可以走到联合索引的,但如果是 AC、BC、C 的这种情况则不会使用索引。
覆盖索引就是一种特殊的联合索引
我们如果直接用主键查找,用的是聚集索引,能找到全部的数据。如果我们是用非聚集索引查找,如果索引里不包含全部要查找的字段,则需要根据索引叶子节点存的主键值,再到聚集索引里查找需要的字段,这个过程也叫做回表
覆盖索引指的是在⼀次查询中,如果⼀个索引包含或者说覆盖所有需要查询的字段的值,我们就称之为覆盖索引,⽽不再需要回表查询。
全文索引与普通的索引不是一回事,在查找上方面其效率是普通模糊(like)查询和 N 倍,是 MySQL专门提供用作搜索引擎的。
自适应哈希索引是Innodb引擎的一个特殊功能,当它注意到某些索引值被使用的非常频繁时,会在内存中基于B-Tree所有之上再创建一个哈希索引,这就让B-Tree索引也具有哈希索引的一些优点,比如快速哈希查找。这是一个完全自动的内部行为,用户无法控制或配置,但如果有必要刻意关闭该功能。
常见的索引失效情况:
id=c_id
,这种情况会被认为还不如走全表扫描;select * from sunyang where id='123';
索引设计不合理或者缺少索引都会对数据库和应用程序的性能差生障碍,高效的索引对对获得良好的性能非常重要,设计索引是要考虑下下准则: