MySQL 8.0 自增主键与热点写优化：AUTO_INCREMENT 锁模式、Change Buffer 与并发插入可验证指南

本文面向高并发写入场景的 MySQL 8.0 使用者，目标是通过“可验证”的方法优化自增主键的热点写入与插入吞吐。

## 环境前提与校验

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
SHOW VARIABLES LIKE 'innodb_change_buffering';

• `innodb_autoinc_lock_mode`：0=traditional，1=consecutive，2=interleaved（建议在批量插入与并发场景使用 2）。
• `innodb_change_buffering`：仅作用于二级索引的变更缓冲，不影响主键（聚簇索引）写入路径。

## 可复现实验：并发插入与锁模式对比

准备表与数据：

CREATE TABLE t_auto (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  k BIGINT NOT NULL,
  v VARCHAR(64) NOT NULL,
  KEY idx_k (k)
) ENGINE=InnoDB;

sysbench 压测（示例）：

sysbench oltp_insert --threads=32 --tables=1 --table-size=0 \
  --mysql-db=test --mysql-user=root --mysql-password=xxx run

切换锁模式并对比：

SET GLOBAL innodb_autoinc_lock_mode = 0; -- traditional
-- 运行压测，记录 TPS 与 p95

SET GLOBAL innodb_autoinc_lock_mode = 1; -- consecutive
-- 运行压测，记录 TPS 与 p95

SET GLOBAL innodb_autoinc_lock_mode = 2; -- interleaved
-- 运行压测，记录 TPS 与 p95

观察：模式 2 在多线程并发插入下通常获得更高吞吐，且自增 ID 连续性按语句级保障（可能存在间隙，但不影响唯一性）。

## Change Buffer 的适用边界（澄清）

• 仅针对二级索引的插入/删除进行延迟应用以减少随机 I/O；不作用于主键（聚簇索引）。
• 对唯一二级索引、`INSERT ... ON DUPLICATE KEY` 等场景，变更缓冲会受限或禁用。
• 验证：在上表中对 `idx_k` 进行批量插入，观察 `SHOW STATUS LIKE 'Innodb_ibuf%'` 的变化。

## 主键热点与分布策略

• 若写入集中在少量分片键导致行锁/页锁争用，考虑调整分片键或引入“雪花算法”分布式序列以降低热点（注意业务排序需求）。
• 批量插入：使用多值 `INSERT` 与合适的事务边界，减少锁竞争与日志刷写频次。

## 回归与监控

• 指标：记录 `TPS`、`p95/p99`、`rows_inserted`、`buffer pool` 命中率与 `redo` 写入速率。
• 基线：在固定硬件与参数下，建立并发 16/32/64 的曲线，作为后续发布的回归参照。

## 注意事项

• 切换 `innodb_autoinc_lock_mode` 为全局生效，生产变更需在维护窗口并回归。
• 大事务会放大锁持有时间与日志压力，建议分批提交。

## 结语

通过锁模式的可验证对比与二级索引缓冲的边界澄清，能够把自增主键写入从经验调优转为数据驱动的工程路径，在保持正确性的同时提升并发吞吐。