萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL AUTO_INCREMENT 深度解析:步长、偏移、锁模式与主主复制实战

核心变量

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
  • auto_increment_increment:步长,默认 1。
  • auto_increment_offset:起始偏移,默认 1。

InnoDB 自增锁模式

mysql> SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
模式机制特点
0表锁串行安全,并发差,无空洞
1 (默认)互斥量 + 预分配批量预分配 ID,可能出现不连续,性能好
2轻量级互斥量不连续,性能最好,语句复制不安全

模式 1 原理

每次 INSERT 预分配一批 ID(compute_next_insert_id),执行完成后将“当前最大 ID”回写表字典。若 INSERT ... ON DUPLICATE KEY UPDATE 触发更新,预分配的 ID 被废弃,形成空洞。

主主复制配置防冲突

双主互为主从,需保证自增 ID 不重叠:

-- Server A
auto_increment_increment = 2
auto_increment_offset = 1   -- 生成 1,3,5,7...

-- Server B
auto_increment_increment = 2
auto_increment_offset = 2   -- 生成 2,4,6,8...

配合 log_slave_updates=ON、sync_binlog=1、innodb_flush_log_at_trx_commit=1 保证数据一致性。

最佳实践清单

  1. 主键使用 BIGINT UNSIGNED AUTO_INCREMENT,规避 21 亿上限。
  2. 业务不依赖 ID 连续性(订单号另设字段)。
  3. 批量导入前设置 SET @@auto_increment_increment=100 减少锁竞争,导入后恢复。
  4. 监控 SHOW TABLE STATUS LIKE 'tbl'\G 中 Auto_increment 值,预防耗尽。
copyright © 萬盛學電腦網 all rights reserved