MySQL AUTO_INCREMENT 自增屬性深度解析：各引擎差異、修改策略與避坑指南

前言

AUTO_INCREMENT 是生成代理主鍵的核心機制，但不同存儲引擎（MyISAM、InnoDB、Memory、BDB）在初始值設置、最大值重用、復合索引序列、持久化語義上存在顯著差異。本文系統梳理各引擎行為，並給出生產環境修改自增值、防重用、替代方案的最佳實踐。

一、各引擎自增行為對比表

二、InnoDB 自增核心機制（5.7/8.0）

• 鎖模式：innodb_autoinc_lock_mode（0=傳統表鎖、1=連續鎖默認、2=交錯鎖高併發）
• 持久化：8.0 將最大自增值寫入 Redo Log 和數據字典表 mysql.innodb_table_stats，重啟不再重掃 B+Tree
• 不可重用：即使刪除最大 ID，下次插入仍遞增，避免主鍵衝突與複製衝突
• 修改起值：5.7.9+ 支持 ALTER TABLE t AUTO_INCREMENT = 1000（需 > 當前最大值）

三、定義自增列的五個硬性約束

1. 整數類型（TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT）
2. UNSIGNED 推薦，翻倍正數範圍
3. NOT NULL 必須
4. 唯一索引（主鍵或 UNIQUE KEY）
5. 單表僅一個自增列

四、修改自增值的正確姿勢

-- 5.7.9+ / 8.0 直接修改（需大於當前最大值）
ALTER TABLE t AUTO_INCREMENT = 100000;

-- 老版本或需縮小值：重建表
CREATE TABLE t_new LIKE t;
ALTER TABLE t_new AUTO_INCREMENT = 1000;
INSERT INTO t_new SELECT * FROM t;
RENAME TABLE t TO t_old, t_new TO t;

五、特殊場景與替代方案

5.1 防止自增值耗盡

• 使用 BIGINT UNSIGNED（1.84e19），單表百年不枯竭
• 監控 SHOW TABLE STATUS LIKE 't'\G 中 Auto_increment 接近上限報警

5.2 全表刪除保留自增值

-- TRUNCATE / DELETE 無 WHERE 會重置自增計數器
-- 保留計數器的刪除寫法（逐行評估，慢但保留）
DELETE FROM t WHERE 1;  -- 或 WHERE id > 0

5.3 極高併發自增熱點優化

• innodb_autoinc_lock_mode=2（交錯鎖，Statement-based 複制不安全）
• 應用層批量預取：SELECT LAST_INSERT_ID() 配合 INSERT ... VALUES (),(),()
• 分布式 ID：雪花算法、美團 Leaf、百度 UID Generator，完全繞開自增鎖

5.4 不用自增列生成序列（通用計數器模式）

CREATE TABLE seq (id INT UNSIGNED NOT NULL);
INSERT INTO seq VALUES (0);

-- 獲取下一值（步長可控）
UPDATE seq SET id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();  -- 返回新值

-- 步長 10
UPDATE seq SET id = LAST_INSERT_ID(id + 10);
SELECT LAST_INSERT_ID();

優點：無表鎖、無事務、可定製步長、多計數器共存。

六、常見誤區辨析