MySQL学习笔记3——索引

比较常见的索引模型有三种,哈希表,有序数组,搜索树。

哈希表

为了处理多个key经过哈希函数映射到同一个数组位置的问题,解决方法一般是拉出一个链表
在这里插入图片描述

如上图,维护一个身份证信息-姓名的表,根据身份证号查找名字。
可以看见2和4两个虽然key值映射到的都是N,因此由此拉出一个链表,按顺序遍历这个链表,直到找到对应值

问题在于,key值并不是递增的,只是单纯的往链表后追加,非有序带来的问题就是查询速度慢
所以,哈希表适用于只有等值查询(筛选出一个字段等于特定值的所有记录)的场景

有序数组

假设是有序排列的,那么查找自然很方便,用二分法即可查询
(见我们之前说的kafka消息查询方式添加链接描述

但是问题在于,插入与删除的代价太大,只适用于做静态存储
比如保存2020年某个城市的数据,这个数据相较于2022年来说,已经不会再变化了,没有增删的需求。

N叉搜索树

在这里插入图片描述
我们按照左小右大的特点,存放数据。查询某个节点的时间复杂度是log(n),且我们需要保持二叉搜索树是二叉平衡树。不断调整位置的时间复杂度同上。

数据量大了之后,肯定不能用二叉树,而是用N叉树。

以InnoDB的一个整数字段索引为例,这个N差不多是1200。这棵树高是4的时候,就可以存1200的3次方个值,这已经17亿了。考虑到树根的数据块总是在内存中的,一个10亿行的表上一个整数字段的索引,查找一个值最多只需要访问3次磁盘。其实,树的第二层也有很大概率在内存中,那么访问磁盘的平均次数就更少了。

在MySQL中,索引是在存储引擎层实现的,所以并没有统一的索引标准,即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。

InnoDB索引模型——B+树

在该模型中,表根据主键顺序以索引形式存放,存储方式为索引组织表。
而数据存放在B+树中。**每一个索引在InnoDB中对应一颗B+树,B+树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数。
**

假设,我们有一个主键列为ID的表,表中有字段k,并且在k上有索引。表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6),两棵树的示例示意图如下。
在这里插入图片描述
根据叶子节点的内容,索引类型分为主键索引和非主键索引
主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

这两种索引查询有什么区别?

  • 如果是select * from T where ID=100;是主键查询,只搜索ID这颗B+树即可。
  • 而如果是select * from T where k=1;则是二级索引查询,需要先查K索引树,找到对应的主键为100,再去进行主键查询ID,这个过程就叫做回表

也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

B+树索引的维护

B+树为了维护索引有序性,在插入新值的时候需要做必要的维护。以上面这个图为例,如果插入新的行ID值为700,则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。

而更糟的情况是,如果R5所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。

当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

自增主键的使用
自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的: NOTNULL PRIMARY KEY AUTO_INCREMENT

插入新记录的时候可以不指定ID的值,系统会获取当前ID最大值加1作为下一条记录的ID值

也就是说,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂

假设刚才的身份证信息表,是否应该用身份证号做主键?
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约20个字节,而如果用整型做主键,则只要4个字节,如果是长整型(bigint)则是8个字节。

主键长度越小,普通索引(二级索引)叶子节点(主键的值)就越小,则普通索引占用空间越小
所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。

什么情况适合直接用业务字段做主键? KV场景。

案例——如何避免回表过程

执行 select *fromTwhere k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?
在这里插入图片描述

  1. 在k索引上得先找到k=3的记录,获取其主键300.
  2. 回到ID索引找到主键ID=300对应R3。
  3. 同上找到k=5,500,R4.至此找到两个端点
  4. 回到K索引找下一个值k=6,不在3 5之间,直接返回

在这个例子中,由于查询结果所需要的数据只在主键索引上有,所以不得不回表。那么,有没有可能经过索引优化,避免回表过程呢?

覆盖索引

上面的案例要找*
索引我们找到ID后要回表去找Rx。
但是如果我们select的是ID。此时直接找索引K,即可找到ID,不需要回表,该索引已经覆盖了我们的查询要求,称为覆盖索引。 ·

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

还是最上面身份证的问题,我们是否有必要建立一个 身份证号-姓名 的联合索引?
我们已经直到身份证号可以唯一确定一个市民,看起来已经够了。如果有一个高频请求,根据身份证号查姓名,该联合索引才有意义,因为这样可以用覆盖索引来避免回表,减少语句执行时间

最左前缀

B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录
我们用(name,age)这个联合索引来分析。
在这里插入图片描述
索引项按照索引定义里出现的字段顺序排序,因此可以查找到某个姓张的人ID是多少,一旦定位到张三,往后数一定能找到。

只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符

在建立联合索引的时候,如何安排索引内的字段顺序?

原则1:索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

原则2:空间
比如查询条件里面只有b的语句,是无法使用(a,b)这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护(a,b)、(b) 这两个索引。所以,如果有name,age的需求,name字段显然大于age,如果需要单独查age,则需要(name,age)和(age)两个索引

索引下推

上一段我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。这时,你可能要问,那些不符合最左前缀的部分,会怎么样呢?

我们还是以市民表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是10岁的所有男孩”。那么,SQL语句是这么写的:

select * from tuser where name like '张%' and age=10 and ismale=1;

根据最左前缀的规则,我们先找到了第一个姓张的人,ID3。
然后我们需要判度其他条件是否满足
MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数

我们来看假如没有索引下推:
在这里插入图片描述
我们查到这些姓张的人之后,获取其ID,再回表到主键索引中查他的age。可以看见需要回表4次。

在这里插入图片描述

而有了索引下推,只需要回表2次。在(name,age)联合索引内部就判度age是否符合,不符合直接跳过,就不用回表

如何选择普通索引和唯一索引

对于一个普通索引,查找到第一个满足条件的记录后,需要继续往后找下一个记录,直到其不满足条件
而唯一索引只要找到就是唯一的。
但是两者性能差距微乎其微

为什么?
InnoDB读数据并不是按行读的,而是按页为单位读。将一页整体读入内存,默认页大小是16KB。
所以普通索引多出的往下一次找,只是指针移动一次,所以性能上并没有差别。

change buffer:
如果更新操作更新的这一页恰好在内存中,那么InnoDB可以直接更新,如果不在内存中,就缓存这些操作到change buffer中,直到下次从磁盘把这一页读到内存中再执行操作(这个操作叫Merge)。
需要说明的是,这个缓存是可持久化的数据,会被拷贝到磁盘上

什么条件使用change buffer:

  • 对于唯一索引,既然要保证唯一,必须要判度插入的内容是否唯一,那就要把数据页读进内存,既然已经读进内存了,没必要使用change buffer。
  • 只有普通索引才能用change buffer

所以对于一个插入操作

  • 如果要更新的数据在内存中,唯一索引只多了判断索引是否冲突这一步,性能没有影响。
  • 而如果不在,唯一索引需要从磁盘中读数据页,这个过程IO成本高,而普通索引同上面情况都只需要记在change buffer里,没有磁盘IO

总结:
写多读少,用change buffer效果好,如账单,日志
更新多,更新完需要立即查询,change buffer会起到副作用,因为需要额外merge
在实际使用中,你会发现,普通索引和change buffer的配合使用,对于数据量大的表的更新优化还是很明显的

区别change buffer 和redo log

对于一个操作:

insert into t(id,k) values(id1,k1),(id2,k2)

加入k1在内存中(即在buffer pool),k2不在。
在这里插入图片描述

  1. page1 在内存中,直接更新内存
  2. page2不在,在内存的change buffer区域记下操作
  3. 而后把上述两个操作都记录到redo log中

如果在更新完不久就有了读操作,
在这里插入图片描述

  • 此时如果读page1直接从内存返回,所以WAL(write ahead log)并不一定要读盘 等redo log更新了数据才能返回,虽然磁盘中的数据还没有被更新,但是此时内存里的数据已经是最新的了。
  • 如果读page2 ,需要从磁盘里读进内存,然后执行change buffer里对page 2的操作,生成最后正确的版本返回。这里可以体会到我们之前说的直到下一次读取该页,才把该页从磁盘中读入内存,否则只要记在change buffer里就行

redo log 和change buffer的区别:
redo log主要节省随机写磁盘的IO消耗(有了redo log就可以按log顺序写)
change buffer主要节省随机读磁盘的IO消耗

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值