面试中的 MySQL 大坑：为什么UPDATE 这么慢？

运维路书 2024-10-09

有粉丝留言说，面试时被问到

“为什么UPDATE 这么慢？ Change Buffer 如何优化 UPDATE ?”

今天就来讨论一下这个问题。

核心要点

Change Buffer：加速写请求，避免每次写入都进行磁盘IO

这个问题主要从解释change buffer的用途的角度来回答

概念解释

MySQL官方的change buffer结构图

https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html

MySQL官方手册中对于change buffer的描述：

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

意思是：

当非唯一普通索引页不在缓冲池中时，对页进行了写操作(增，删，改)，不会立即将磁盘中的页加载到buffer pool中，仅仅记录写缓冲的变更（buffered changes），将来要读取数据时，再将数据合并恢复到buffer pool中。

写操作如何减少IO

这里分为两种情况：

数据页已经在buffer pool中
数据页不在buffer pool中

1

数据页已经在buffer pool中

当需要修改的数据页已经在buffer pool 中，

MySQL不会直接更新磁盘上的数据，而是经过一下两个步骤：

直接修改Buffer Pool 中的数据页，一次内存操作；
顺序写入redo log , 一次磁盘顺序写操作；

这样的效率最高

顺序写redo log

每秒几万次，问题不大。

是否会产生数据一致性问题？

读取数据，会命中buffer pool 的数据页（已经修改过）
buffer pool 的LRU淘汰机制，会将脏页刷回磁盘
数据库崩溃，redo log 可以恢复数据

因此写入的数据页在buffer pool 中的情况，不会产生数据不一致。

2

数据页不在buffer pool中

当需要修改的数据页不在buffer pool 中，或者干脆是一条插入语句。这种情况下有change buffer 和没有change buffer是不一样的。

没有change buffer

第一步：先从磁盘读取所需数据页加载到buffer pool 中，一次磁盘随机读操作；

第二步：修改buffer pool 中的数据页，一次内存操作；

第三步：将修改后的数据页写入redo log, 一次磁盘顺序写操作；

相比于数据页在buffer pool 中的情况，多了一次磁盘的IO，

磁盘IO与内存操作相比要慢的多，并发下性能会急剧下降。

有change buffer

第一步：在 change buffer 中记录这个修改操作，一次内存操作；

第二步：将修改操作顺序写入redo log, 一次磁盘顺序写操作；

由此可以看出

有change buffer与记录已经在buffer pool中

效率差不多，

仅仅是第一步的操作位置不一样，但都是内存操作。

是否会产生数据一致性问题？

读取数据，会将change buffer 中的数据合并到buffer pool 中
如果没有读取，后台会定期刷盘
数据库崩溃，redo log 可以恢复数据

因此写入的数据页不在buffer pool 中的情况，不会产生数据不一致。

其他

只用于非唯一普通索引

对于唯一索引的修改操作，需要先判断当前操作是否违反唯一性约束，而这个操作会将索引页读取到内存中，进行判断。此时既然已经读取到内存了，就可以直接修改了，不需要再用change buffer 了。

Change Buffer merge时机

change buffer 中记录的修改操作，什么时候会被merge到buffer pool 中？

读取change buffer 中记录的数据页时，会将change buffer 合并到 buffer pool 中，然后刷新到磁盘
当系统空闲或者正常 shut down 时，后台线程会发起merge
change buffer 的空间满了，后台线程会发起merge

Change Buffer 配置参数

change buffer 有两个与之相关的重要参数：

innodb_change_buffer_max_size
innodb_change_buffering

配置change buffer的大小

从第一张官方的架构图中，可以看出，change buffer是包含在 buffer pool中的。相当于是buffer pool 分出一块内存空间给change buffer使用。

innodb_change_buffer_max_size 表示允许change buffer 占 buffer pool 总大小的百分比，默认值为25%，最大可以设置为50%

系统如果是写多读少的业务场景，可以调大参数值，以提高系统的写入性能。
系统如果是读多写少的场景，可以保持默认配置，或适当减少参数值，以减少buffer pool中数据页的淘汰率，提高系统的读取性能。

配置change buffer的类型

change buffer 在 MySQL5.5以后可以支持插入、更新、删除的写入操作，能够降低磁盘IO，提升数据库的写入性能。对于一些特定的场景可以通过修改innodb_change_buffering 来变更change buffer 支持的类型

all: 默认值，缓冲插入，删除标记操作，清除

none: 不缓冲任何操作

inserts: 缓冲插入操作

deletes: 缓冲删除标记操作

changes: 缓冲插入和删除标记操作，相当于update

purges: 后台发生的物理删除操作

总结

Change Buffer 是降低磁盘IO，提升数据库写性能的一种机制
Change Buffer 只作用于非唯一的普通索引
非唯一普通索引页不在 buffer pool 中，修改数据页，不会立即将磁盘中的页加载到 buffer pool 中，仅仅记录变更到Change Buffer 中，将来要读取数据时，再将数据合并恢复到 buffer pool 中。

【以上仅为个人观点，如有不同意见，欢迎留言讨论！】