当前位置: 首页 > news >正文

MySQL 09 普通索引和唯一索引

唯一索引:字段值不能重复。

普通索引:字段值可以重复。

假设数据如下图,且字段k上的值都不重复:

接下来,从两种索引对查询语句和更新语句的性能影响来分析。

查询过程

假设查询语句为select id from T where k=5。那么首先会在字段k的索引树上找到k=5所在的数据页,然后在数据页内二分查找记录。对于普通索引和唯一索引,区别为:

  • 普通索引,查找到满足条件的第一个记录后,会继续查找,直到找到第一个不满足k=5的记录。

  • 唯一索引,找到第一个满足条件的记录后,就停止查找。

这个区别带来的性能差距其实是不大的。由于InnoDB的数据按数据页为单位进行读写,当找到k=5的那条记录时,它所在的数据页就都在内存里了,因此普通索引多进行的一次查找并没有多大开销。尽管有可能k=5刚好是数据页的最后一条记录,查找下一条记录需要新读取一页,但这种可能出现的概率很低,均摊到所有可能后成本可以忽略不计。

更新过程

在分析更新过程之前,先介绍change buffer。

当需要更新一个数据页,如果数据页在内存中,就直接更新;如果数据页在磁盘中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中。当下次查询需要访问该数据页,会将该数据页读入内存,执行change buffer中与该页有关的操作来保证数据逻辑的正确性。

change buffer在内存中,也会被持久化到磁盘上。

将change buffer中的操作应用到原数据页,得到最新结果的过程称为merge。除了访问这个数据页会触发merge,系统有后台线程会定期merge。在数据库正常关闭的过程中,也会执行merge操作。

可以看出,如果能将更新操作先记录在change buffer,减少读磁盘的次数,语句执行速度将会加快。而且,数据读入内存需要占用buffer pool,使用change buffer还能避免占用内存,提高内存利用率。

并不是所有条件下都可以使用change buffer。对于唯一索引,所有更新操作都必须判断该操作是否违反唯一性约束,比如要插入(4,400),就需要先判断表中是否已经有k=4的记录,因此每次更新都需要将数据页读入内存。而如果本身有读入内存的操作,那么直接更新内存即可,不需要再使用change buffer。而对普通索引,change buffer是可以使用的。

change buffer用的是buffer pool里的内存,不能无限增大。其大小可以通过innodb_change_buffer_max_size来动态设置,参数值表示占用buffer pool的百分比。

那么来看看,如果想要插入一条k=4的新纪录,InnoDB的处理流程:

  • 对于唯一索引,找到3和5之间的位置,判断到没有冲突,插入值;

  • 对于普通索引,找到3和5之间的位置,插入值。

看起来两者只相差一个判断,但若该记录要更新的目标页不在内存中,再对两者进行比较:

  • 对于唯一索引,需要将数据页读入内存,判断到没有冲突,插入值;

  • 对于普通索引,将更新记录在change buffer即可。

可以看到,这种情况下相差了一次随机磁盘IO,性能相差较大。

change buffer的使用场景

尽管change buffer能用于普通索引的场景,但并不是在所有情况下它都能起到加速作用。

这是由于merge是真正进行数据更新的时刻,而change buffer的主要目的就是将记录的变更动作缓存下来,所以在一个数据页merge前,change buffer记录的变更越多,收益越大。

对于写多读少的业务场景,页面写完后马上被访问的概率较小,意味着change buffer马上merge的概率较小,此时使用效果较好,场景业务模型如账单类、日志类系统。

而对于一个写入之后马上做查询的场景,merge的频率较高,这样随机访问IO的次数并不会减少,反而增加了change buffer的维护代价。因此,对于这种业务模式来说,change buffer反而起到了副作用。

索引选择和实践

回到之前的问题:如何选择普通索引和唯一索引。

经过上面的分析可以看到,两者在查询过程的能力几乎无差别,主要区别在更新过程。因此,建议尽量选择普通索引。如果是写入后马上查询的场景,建议关闭change buffer

普通索引 + change buffer,对于数据量大的表的更新优化是比较明显的。特别地,在使用机械硬盘时,change buffer机制收益非常显著,因此使用机械硬盘时考虑加大change buffer。

change buffer和redo log

理解了change buffer的原理,可能会发现这和redo log有一些相似。

让我们回顾redo log的WAL机制:MySQL的更新用到了WAL(Write-Ahead Logging)技术,关键点就是先写日志,再写磁盘。具体来说,当有一条记录需要更新时,InnoDB引擎先将记录写到redo log并更新内存,这时更新就可以算完成了。之后,InnoDB会在适当的时候将这个操作记录更新到磁盘里。

接下来,使用一个例子来区分这两个概念。假设要在表上执行下面这个插入语句:

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

假设在k索引树找到位置后,k1所在的数据页在内存中,而k2所在的数据页不在内存中。下图是带change buffer的更新状态图:

分析这条更新语句:

  • 发现Page 1在内存中,直接更新内存;

  • 发现Page 2没有在内存中,就在内存的change buffer区域,记录“我要往Page 2插入一行”这个信息;

  • 将上述两个动作记入redo log。

做完上述动作,事务就完成了。可以发现,对于该次更新,只是写了两次内存+一次磁盘。图中的虚线箭头是后台操作,不会影响更新的响应时间。

那么此次更新之后的查询请求,会怎么处理呢?

比如要执行select * from t where k in (k1,k2),其读请求的流程图如下:

分析这条查询语句:

  • 读Page 1时,直接从内存返回。

  • 读Page 2时,需要先将Page 2从磁盘读入内存,然后应用change buffer,生成一个正确版本并返回结果。

因此,如果简单对比change buffer和redo log在提升更新性能上的收益的话:

  • redo log主要节省了随机写磁盘的IO消耗,将随机写转为了顺序写;

  • change buffer节省了随机读磁盘的IO消耗。

最后,做一个思考:如果某次写入使用了change buffer,如果之后主机异常重启,是否会丢失change buffer和数据?

答案是不会,因为在写完change buffer后,会把该动作也记录到redo log后,之后崩溃恢复也可以通过redo log找回。

http://www.lryc.cn/news/583481.html

相关文章:

  • 汽车功能安全-软件单元验证 (Software Unit Verification)【用例导出方法、输出物】8
  • 装配式建筑4.0:当房子像汽车一样被“智造”
  • 解锁DevOps潜力:如何选择合适的CI/CD工作流工具
  • 北京-4年功能测试2年空窗-报培训班学测开-第四十六天
  • Spring AI Alibaba Graph使用案例多节点并行执行
  • Webpack、Vite配置技巧与CI/CD流程搭建全解析
  • CentOS7系统部署Node.js LTS V18.16.0
  • 【自动驾驶】经典LSS算法解析——深度估计
  • 佰力博科技与您浅谈低温介电材料特性及应用分析
  • 科技对生态保育的影响?
  • Oracle存储过程导出数据到Excel:全面实现方案详解
  • 专题一_双指针_三数之和
  • 【基础算法】贪心 (四) :区间问题
  • WIFI协议全解析04:从芯片角度看WiFi协议:ESP32/8266 支持了哪些?
  • SQL 视图与事务知识点详解及练习题
  • ARM汇编编程(AArch64架构)课程 - 第7章:SIMD与浮点运算
  • STIDGCN(时空交互动态图卷积网络)的原理,包括其核心模块的设计思路和工作机制 交通预测是智能交通系统中的一个重要任务
  • python+vue的企业产品订单管理系统
  • Redis:分组与设备在 Redis 中缓存存储设计
  • Redis-哨兵机制doctor环境搭建
  • CSS基础选择器、文本属性、引入方式及Chorme调试工具
  • Linux 测开:日志分析 + 定位 Bug
  • 【图像处理基石】如何检测到画面中的ppt并对其进行增强?
  • deepseek实战教程-第十篇deepseek对MCP协议支持
  • 计算机网络实验——网线的制作和测试
  • 7、整合前几篇插件列表
  • 云成本优化的核心原则与框架
  • Spring AI 本地 RAG 实战:用Redis、Chroma搭建离线知识问答系统
  • 【解决方法】ollama在powershell或者cmd运行时乱码报错
  • 网络安全之RCE分析与利用详情