MySQL AUTO_INCREMENT 深度解析：MyISAM 與 InnoDB 自增行為差異實測

MySQL 自增主鍵機制核心差異揭秘

AUTO_INCREMENT 是 MySQL 最常用的主鍵生成策略，但 MyISAM 與 InnoDB 在自增值持久化與重啟後行為上存在關鍵差異。本文透過實測案例，深度剖析兩大引擎的自增機制差異與背後原理。

實測環境與測試邏輯

• 建立含 AUTO_INCREMENT PRIMARY KEY 的測試表
• 插入資料 → 刪除最大 ID 記錄 → 重新插入觀察新 ID
• 對 InnoDB 額外測試：重啟 MySQL 服務後再插入觀察

MyISAM 引擎測試過程與結果

1. 建表與插入初始資料

mysql> CREATE TABLE test_myisam (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(5)
) ENGINE=MyISAM;

mysql> INSERT INTO test_myisam (name) VALUES ('a'), ('b'), ('c');
mysql> INSERT INTO test_myisam (name) SELECT name FROM test_myisam;  -- 複製插入

mysql> SELECT * FROM test_myisam;
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
| 3  | c    |
| 4  | a    |
| 5  | b    |
| 6  | c    |
+----+------+

2. 刪除最大 ID (6) 後重新插入

mysql> DELETE FROM test_myisam WHERE id=6;
mysql> INSERT INTO test_myisam (name) VALUES ('d');
mysql> SELECT * FROM test_myisam;
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
| 3  | c    |
| 4  | a    |
| 5  | b    |
| 7  | d    |  <-- 新 ID 為 7 (原最大值 6 + 1)
+----+------+

3. 結論：MyISAM 永久記錄最大值

即使刪除最大 ID，**下一次插入仍使用「歷史最大值 + 1」**。此值寫入資料檔案，重啟服務不影響。

InnoDB 引擎測試過程與結果

1. 建表與插入初始資料

mysql> CREATE TABLE test_innodb (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(5)
) ENGINE=InnoDB;

mysql> INSERT INTO test_innodb (name) VALUES ('a'), ('b'), ('c');
mysql> SELECT * FROM test_innodb;
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
| 3  | c    |
+----+------+

2. 未重啟情況：刪除最大 ID (3) 後插入

mysql> DELETE FROM test_innodb WHERE id=3;
mysql> INSERT INTO test_innodb (name) VALUES ('d');
mysql> SELECT * FROM test_innodb;
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
| 4  | d    |  <-- 新 ID 為 4 (記憶體中最大值 3 + 1)
+----+------+

3. 關鍵測試：重啟 MySQL 服務後再插入

mysql> DELETE FROM test_innodb WHERE id=4;
mysql> EXIT;
$ sudo service mysql restart
mysql> INSERT INTO test_innodb (name) VALUES ('z');
mysql> SELECT * FROM test_innodb;
+----+------+
| id | name |
+----+------+
| 1  | a    |
| 2  | b    |
| 3  | z    |  <-- 新 ID 為 3 (當前表中最大值 2 + 1)
+----+------+

核心差異總結表

底層原理深度解析

MyISAM：資料檔案持久化

• 自增計數器寫入 .MYD 資料檔案
• 每次插入更新檔案中的計數器值
• 崩潰恢復時直接讀取檔案值，保證單調遞增

InnoDB：記憶體計數器 + 重啟掃描

• MySQL 5.7 以前：計數器僅存在記憶體，重啟後掃描表尋找 MAX(id) 重建
• MySQL 8.0+：引入 redo log 持久化自增值，重啟後從 redo log 恢復，行為更接近 MyISAM
• 本文測試環境為 MySQL 5.5，呈現舊版行為

實務影響與應對策略

潛在問題

1. ID 重複風險：InnoDB 重啟後可能重用已刪除的 ID，若應用依賴 ID 唯一性（如外部系統引用）會出錯
2. 主鍵衝突：軟刪除 + 重啟 + 重新插入可能導致主鍵衝突
3. 審計追蹤斷點：ID 不連續或重用會干擾審計日誌分析

最佳實踐建議

1. 業務層面勿依賴 ID 連續性，ID 僅為技術主鍵
2. 重要業務實體使用 UUID 或雪花演算法 作為業務主鍵
3. MySQL 8.0+ 環境：自增持久化問題已大幅改善
4. 若需嚴格單調遞增：可用序列表模擬或升級至 8.0+

驗證當前版本行為的快速腳本

-- 建立測試表
CREATE TABLE t_auto (id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t_auto VALUES (), (), ();  -- ID 1,2,3
DELETE FROM t_auto WHERE id=3;
-- 重啟 MySQL 服務
-- 再次插入觀察新 ID
INSERT INTO t_auto VALUES ();
SELECT * FROM t_auto;

理解引擎層面的自增機制差異，能幫助你在架構設計、遷移升級、故障排查時做出正確決策，避免隱性 Bug 影響業務穩定性。