我的知识海洋

What are you following

  • 首页
  • 标签
  • 分类目录
  • 文章归档
  • 行路万里
  • 读书万卷
  • About Me

  • 搜索
面经 解决方案 操作系统 Java源码 开源 GSoC 哲学 中间件 回溯 链表 书 top 数据库 分布式 滑动窗口 配置 动态规划 前缀树 并查集 Redis 总结 年终总结 面试 算法基础

【MySQL 45讲-9】第9讲普通索引和唯一索引

发表于 2022-02-27 | 分类于 学习 | 阅读次数 710
# 数据库
【MySQL 45讲-8】第8讲 事务隔离性及快照的实现
【MySQL 45讲-10】第10讲MySQL如何选择索引

请添加图片描述

09 | 普通索引和唯一索引,应该怎么选择?

查询过程

  • 举个栗子
    select id from T where k=5

    • 查找过程:从B+树的树根开始,按层搜索到叶节点(数据页),然后根据二分法在数据页内部来定位。

    • 建立普通索引还是唯一索引?

      • 普通索引:查到第一个满足的记录后,需要查找下一个记录,直到碰到不满足k=5,结束

      • 唯一索引:因为有唯一性,所以找到了第一个满足的记录后就结束了

      • 性能差距:微乎其微

        • InnoDB按数据页为单位读写
        • 所以都是读取一页,只是怕普通索引要多做一次查找和判断下一次记录(一次指针寻找和计算)

更新过程

  • change buffer

    • 使用场景

      • 只限于用在普通索引的场景下,不使用于唯一索引

        • 因为对于唯一索引,所有的更新操作都要先判断这个操作是否违反唯一性约束,而这需要将数据页读入内存才能判断,既然已经读到内存了,直接更新内存会更快,没必要change buffer
      • 但是并不是所有普通索引都适用

        • 写多读少的业务,页面在写完以后马上被访问到的概率小,如账单类、日志类系统,所以用change buffer的效果好

        • 写入之后马上会做查询的业务不适用

          • 因为马上要访问这个数据页,会立即触发merge过程,这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。
    • 在内存中有拷贝,也会被写入到磁盘上

    • merge

      • 将change buffer中的操作应用到原数据页,得到最新结果的过程。

      • 触发条件

        • 访问这个数据页
        • 系统后台线程会定期merge
        • 数据库正常关闭也会merge
    • 主要是减少读磁盘,提升运行速度

  • 更新流程

    • 如果数据页在内存中

      • 唯一索引

        • 判断有没有冲突再更新
      • 普通索引

        • 直接更新
    • 数据页不在内存中

      • InnoDB会将这些更新操作缓存在change buffer中,下次查询需要访问这个数据页时,将数据页读入内存,然后执行change buffer中与这个页有关的操作。

      • 唯一索引

        • 将数据页读入内存,判断是否有冲突再更新
      • 普通索引

        • 直接将记录更新在change buffer中

索引选择和实践

尽量选择普通索引

如果所有更新后都马上伴随着查询,则应该关闭changebuffer

普通索引和change buffer配合使用对于数据量大的表的更新优化明显

change buffer和redo log

  • 表上插入一条语句
    insert into t(id, k) values(id1, k1), (id2,k2)

    • k索引树,k1所在数据页在内存中,k2所在数据页不在内存中
      1. page1在内存中,直接更新内存
      1. page2不在内存中,就在change buffer区域下,记录“我要往page2插入一行”这个信息
      1. 将上述两个动作记入redo log中
  • 插入后马上进行查询
    select * from t where k in(k1,k2)

      1. 读page1时, 直接从内存中返回
      1. 读page2时,需要把page2从磁盘读入内存,然后应用change buffer里的操作日志,生成并返回结果
  • 综上,redo log主要节省随机写磁盘的IO消耗,
    而change buffer主要节省随机读磁盘的IO消耗。

举个栗子

  • change buffer写入内存了,断电重启了怎么办?会丢失吗

    • 不会丢失,在事务提交的时候,会把change buffer的操作页记录到redo log里。
# 数据库
【MySQL 45讲-8】第8讲 事务隔离性及快照的实现
【MySQL 45讲-10】第10讲MySQL如何选择索引

  • 文章目录
  • 站点概览
erdengk

erdengk

91 日志
5 分类
24 标签
RSS
Github E-mail
Creative Commons
友链
  • 星球球友
  • Joey
  • 北松山(itwaix)-TP在职
  • JooKS' Blog-GSoC 2022 Mentor
  • Chever-John-Shein在职
  • 一堆网页小游戏
  • 飞鸟记
0%
© 2019 — 2025 erdengk
由 Halo 强力驱动
陕ICP备2021015348号-1
川公网安备 51011202000481号
轻点广告,请我喝水,非常感谢 (。・ω・。)ノ(*/ω\*)