索引
索引的出现是为了提高查询的效率,就像书的目录一样
实现索引的方式有很多种,可以用于提高读写效率的数据结构也很多
主要有三种模型
哈希表,有序数组,搜索树
哈希表:
哈希表是一种键值存储数据结构
思路:把值放在数据里面,用一个哈希函数把key缓存成一个确定的位置,然后把value放在数组的这个位置(这种数据结构适用于只有等值查询的场景,比如Memcached以及一些其他NoSQL引擎)
有序数组
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eeH1XGpK-1673864516517)(%E7%B4%A2%E5%BC%95%202b129ad46a3a4f95a601381bf9b946ce/Untitled.png)]
按序保存
查询非常方便,但是更新数据很麻烦
所以有序数组适合保存静态存储引擎
二叉搜索树
查询的时间复杂度为O(log(N))
大多数情况下,使用n叉数
在MySQL中,索引是存储引擎实现的,并没有统一的索引标准
InnoDB的索引类型
表 是根据主键顺序以索引的形式存放的
InnoDB使用B+树索引模型
每一个索引在InnoDb里面对应一个B+树
建表语句
creat table T{
id int primary key,
k int not null,
name varchar(16),
index(k)) engine=InnoDB;
字段k上有索引
两棵树的示例示意图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GjVj22dY-1673864516519)(%E7%B4%A2%E5%BC%95%202b129ad46a3a4f95a601381bf9b946ce/Untitled%201.png)]
主键索引:聚簇索引,叶子节点存储的是整行数据
非主键索引:二级索引,叶子节点存储主键的值
非主键索引会多扫面一棵索引树
索引维护
页分裂(会降低空间的整体利用率),页合并
自增主键是指自增列上定义的主键。不会涉及到挪动其他记录,也不会触发叶子节点的分裂
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VBvpoYOM-1673864516520)(%E7%B4%A2%E5%BC%95%202b129ad46a3a4f95a601381bf9b946ce/Untitled%202.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fPVyHhkC-1673864516521)(%E7%B4%A2%E5%BC%95%202b129ad46a3a4f95a601381bf9b946ce/Untitled%203.png)]
现在,我们一起来看看这条SQL查询语句的执行流程:
- 在k索引树上找到k=3的记录,取得 ID = 300;
- 再到ID索引树查到ID=300对应的R3;
- 在k索引树取下一个值k=5,取得ID=500;
- 再回到ID索引树查到ID=500对应的R4;
- 在k索引树取下一个值k=6,不满足条件,循环结束。
在这个过程中,回到主键索引树搜索的过程,我们称为回表。可以看到,这个查询过程读了k索引树的3条记录(步骤1、3和5),回表了两次(步骤2和4)。
覆盖索引
如果执行的语句是select ID from T where k between 3 and 5,这时只需要查ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引k已经“覆盖了”我们的查询需求,我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。
最左前缀原则
B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录。
索引项是按照索引定义里面出现的字段顺序排序的。
可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。
基于上面对最左前缀索引的说明,我们来讨论一个问题:在建立联合索引的时候,如何安排索引内的字段顺序。
这里我们的评估标准是,索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。