本文介绍 MySQL
的更新缓存 Change Buffer
,以及唯一索引和普通索引如何选择。
唯一索引和普通索引的选择
查询过程
在唯一索引下,查询索引树,找到第一个匹配的行并返回
[En]
Under the unique index, query the index tree, find the first matching row and return
在普通索引下,查询索引树,找到第一个匹配的行,然后继续遍历直到第一个不匹配的行,然后返回。
[En]
Under a normal index, query the index tree, find the first matching row, and then continue to traverse until the first mismatched row, and then return.
即使匹配的行跨了数据页,但一个数据页默认 16KB
,每行只存储一个 key
且是整数,可以存储近千个。那么普通索引下,最多也是多加载一次数据页。所以唯一索引和普通索引的查询效率基本相同。
更新过程
首先介绍下 MySQL
的更新缓存 Change Buffer
,它使用的是 MySQL Buffer Pool
的存储空间,可以设置 Change Buffer
在 Buffer Pool
中的占比, MySQL5.6
默认为 25%
,最多可以开到 50%
。
顾名思义, Change Buffer
就是用来存储更新操作的,使得更新操作不需要加载对应的数据页,直接更新到内存。当对 Change Buffer
中的行进行查询时,就会将原始数据页加载到内存,并将缓存应用到原始数据页,这个就是 merge
过程。 Change Buffer
的 merge
触发时机:
- 当原始数据页加载到
Buffer Pool
时 - 后台线程定期
merge
- 数据库正常关闭时
Change Buffer
的刷盘触发时机:
- 数据库空闲时,会定期持久化。
[En]
when the database is idle, it will be persisted regularly.*
- 数据库缓冲不够用时
- 数据库正常关闭之前
redo log
写满时
你或许会疑问”万一在刷盘之前数据库宕机了,那之前的更新不就丢失了么?”
答案是否。简单分析是,因为将更新操作写到 Change Buffer
后,还会将更新操作写到 redo log
并持久化到磁盘,数据库宕机重启之后,会将之前的更新缓存数据恢复到内存。
具体分析如下:
(1) change buffer
写入, redo log
虽然做了 fsync
但未 commit
, binlog
未 fsync
到磁盘,这部分数据丢失
(2) change buffer
写入, redo log
写入但没有 commit
, binlog
以及 fsync
到磁盘,先从 binlog
恢复 redo log
,再从 redo log
恢复 change buffer
(3) change buffer
写入, redo log
和 binlog
都已经 fsync
.那么直接从 redo log
里恢复。
接下来,分析这两个索引下的更新操作:
[En]
Next, analyze the update operations under the two indexes:
在唯一索引下,查询索引树,将对应的数据页加载到内存中,判断是否违反一致性约束,然后更新
[En]
Under the unique index, query the index tree, load the corresponding data page into memory, determine whether the consistency constraint is violated, and then update
普通索引,查询索引树,直接更新内存中的 Change Buffer
。
因为唯一索引需要判断更新操作是否违反一致性约束,所以必须加载数据页,也就用不到 Change Buffer
,即 Change Buffer
只用于普通索引。
从上面的分析可见,在更新多于读取操作的情况下,普通索引的更新操作效率要高于唯一索引。但如果是更新之后就有查询的场景,那么 Change Buffer
不但没有起到提效作用,反而占用的缓冲空间。所以,这种情况下,一般会关闭 Change Buffer
来避免它的副作用。
总结来说,如果业务需要数据库来对数据进行唯一性约束,那么优先还是考虑唯一索引;否则,如果是更新远多于读取操作的业务场景,比如归档,日志等,考虑用普通索引代替唯一索引,可以提高内存命中率和提高更新效率。但如果是更新之后就有查询的场景,则建议关闭 Change Buffer
,来避免它的副作用。
Original: https://www.cnblogs.com/flowers-bloom/p/mysql45-change-buffer.html
Author: flowers-bloom
Title: MySQL45讲之更新缓存
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/508173/
转载文章受原作者版权保护。转载请注明原作者出处!