MySQL中的Change Buffer是如何优化非唯一索引更新执行流程的？-mysql教程-PHP中文网

change buffer仅对非唯一二级索引生效，主键和唯一索引因需即时校验唯一性而绕过；其启用需满足索引页不在buffer pool等条件，且合并发生在页加载或后台线程触发时，而非select查询中。

mysql中的change buffer是如何优化非唯一索引更新执行流程的？

Change Buffer只对非唯一二级索引生效，主键和唯一索引绕过它

MySQL 的 Change Buffer 不是通用写缓存，它只在满足特定条件时介入：目标索引必须是非唯一二级索引（即没加 UNIQUE 约束），且对应的数据页当前不在 buffer pool 中。主键索引（聚簇索引）和任何带 UNIQUE 约束的二级索引，都会跳过 Change Buffer，因为它们必须立刻校验唯一性——这一步强制触发磁盘读取。

常见错误现象：给字段加了 INDEX 但性能没提升，甚至变慢，很可能是因为误建了 UNIQUE INDEX，或该字段实际有隐式唯一性（如业务 ID），导致 Change Buffer 根本没启用。

检查索引类型：SHOW CREATE TABLE t 看是否含 UNIQUE KEY 或 PRIMARY KEY
确认索引页是否常驻内存：SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME = 't' AND INDEX_NAME = 'your_idx'，若返回大量行，说明页已常驻，Change Buffer 几乎不触发
innodb_change_buffering 默认值为 all，但如果被设成 none 或 inserts，UPDATE/DELETE 就不会进缓冲区

INSERT/UPDATE/DELETE 影响非唯一索引时，才可能写入 Change Buffer

只有 DML 操作真正修改了非唯一二级索引结构，才会走 Change Buffer 路径。比如 UPDATE t SET c = c + 1 WHERE id = 123，如果字段 c 上有普通索引，且该索引页不在内存中，InnoDB 就把这次更新记录写进 Change Buffer，而不是去磁盘读页。

但注意：如果语句只改主键或没索引的列，即使有非唯一索引也不会触发；如果改的是唯一索引列，哪怕没加 UNIQUE 约束，只要 MySQL 能推断出逻辑唯一（例如 WHERE 条件命中唯一值），也可能跳过 Change Buffer。

INSERT 写新行时，若二级索引页未加载，直接记入 Change Buffer
UPDATE 修改索引列值，且新旧值都影响同一索引条目（如更新 status 字段，该字段有普通索引）
DELETE 标记删除（BTRDELMARKOP）和后续清理（BTRDELETEOP）都支持缓冲，但后者由 purge 线程异步执行

Change Buffer 合并不发生在 SELECT，而是在页加载或后台线程触发时

SELECT 查询本身从不主动触发 Change Buffer 合并。只有当某次查询或 DML 操作需要把那个“被缓冲过变更”的索引页加载进 buffer pool 时，InnoDB 才会在内存中做 merge——把 Change Buffer 里针对该页的所有操作一次性应用上去。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

这意味着：如果你刚 INSERT 一条带普通索引的记录，紧接着 SELECT ... WHERE indexed_col = ?，这次查询会阻塞，直到完成合并才能返回结果。延迟不可控，尤其在高并发小表场景下容易成为瓶颈。

后台线程定期合并（默认每秒一次），但频率低、不可预测
数据库关闭前会强制合并所有 pending 变更
可通过 SHOW ENGINE INNODB STATUS 查看 INSERT BUFFER AND ADAPTIVE HASH INDEX 段，确认当前 pending 条目数和 merge 频次
监控关键指标：Innodb_ibuf_size（当前占用大小）、Innodb_ibuf_merges_insert（累计 insert 合并次数）

调优前先砍掉冗余索引，再动 innodb_change_buffer_max_size

innodb_change_buffer_max_size 默认是 25（占 buffer pool 的 25%），但盲目调大反而有害：它会挤占真实热点数据页的空间，导致更频繁的 LRU 淘汰，整体缓存效率下降。

真正该优先做的，是清理不必要的非唯一二级索引——每个这样的索引都是 Change Buffer 的潜在入口点，也是 merge 开销的来源。日志类、流水类表适合保留普通索引，但用户中心表上十几个 INDEX(colX) 却几乎没人查，就纯属拖累。

用 performance_schema.table_io_waits_summary_by_index_usage 查哪些索引长期没被用于查询
观察 Innodb_ibuf_merges_total 和 Innodb_buffer_pool_read_requests 比值，若 merge 次数占比过高（>10%），说明缓冲区在反复刷脏页，可能索引设计过重
设 innodb_change_buffer_max_size = 15 是较安全的保守值，除非你确认写远大于读且索引页极冷

Change Buffer 的价值不在“开就快”，而在“写多读少+页冷+索引精简”这个组合成立时才真正兑现。漏掉任意一环，它就从加速器变成减速垫。