在MySQL數據庫表設計中有個屬性,數字類型的自增列(auto incremnet),從設計原初是為了滿足遞增規則解決計數,或無主鍵下采用主鍵(由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入,避免了頁分裂,因此索引更緊湊)。
在數據庫個需要分清Sequence 和 自增鍵的區別。像mariadb,oracle 都有這個功能。但mysql 目前暫無提供。自增和Sequence 都是數字類型的,自增是單存的向上累加 無法保證等間隔,序列(Sequence)可以完美的保證連貫性可以為表中的行自動生成序列號,產生一組等間隔的數值。
但自增鍵也有一些不為關注的點,雖然數字類型,設計初如果可能的話,使用UNSIGNED屬性來允許更大的范圍。例如,如果使用Tinyint,最大允許的序號是127,對于tinyint 使用UNSIGNED屬性,最大值為255。需要了解所有整型類型的范圍。

自增鎖和模式:
自增對于數據來說,就是插入的時候的特殊處理,對于插入情況,在自增字段下可分為3種模式:
| 插入模式 | 說明 | 例子 |
|---|---|---|
| Simple inserts | 可以預先確定要插入的行數的語句(在語句最初處理時),不包含INSERT … ON DUPLICATE KEY UPDATE. | INSERT INTO Table1(field1) values(‘test’) |
| Bulk inserts | 對于要插入的行數(以及所需的自動遞增值的數量),事先不知道的語句。nnoDB在處理每一行時為AUTO_INCREMENT列一次賦一個新值。比如:INSERT … SELECT, REPLACE … SELECT, LOAD DATA | INSERT INTO Table2(field1,field2,…) select value1,value2,… from Table1 |
| Mixed-mode inserts | 為一些(但不是所有)新行指定自動增加的值。另一種INSERT…ON DUPLICATE KEY UPDATE,在最壞的情況下,實際上是一個INSERT,后面跟著一個UPDATE,其中為AUTO_INCREMENT列分配的值在更新階段可能使用,也可能不使用。 | INSERT INTO Table2(field1,field2) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’); |
自增鎖機制:
InnoDB對自增字段提供了一個可配置的3種鎖機制配合插入時保證唯一性和連續性:
- 傳統:“traditional”,
- 連續:“consecutive”,
- 交錯:“interleaved”
注意:自增鎖是在語句級而不是事務級的,一個事務可能包涵多個語句。
當兩個Tx1,Tx2事務中那個語句先執行,就先獲取自增遞歸值,跟最后提交commit無關。
1)innodb_autoinc_lock_mode = 0 (“傳統”)
- 傳統的鎖模式選項提供向后兼容性的,
- 語句都獲得一個特殊的表級AUTO-INC鎖,表級鎖一直保持到語句結束。
- 以確保對給定的INSERT語句序列:可預測,可重復,增值連續的。
- 當多個事務同時執行插入語句時,這些表級鎖限制了并發性和可伸縮性。
在這種鎖模式下,所有“類insert”語句都獲得一個特殊的表級AUTO-INC鎖,用于插入具有AUTO_INCREMENT列的表。這個鎖通常持有的最后結束(事務),以確保自動遞增值被分配在一個可預見的和可重復的訂單對于一個給定的INSERT語句序列,并確保連續自動遞增值。
2)innodb_autoinc_lock_mode = 1(“連續”)
| 插入模式 | 說明 |
|---|---|
| Simple inserts | 提前已知要插入的行數,避免表級鎖AUTO-INC |
| Bulk inserts | 使用特殊的AUTO-INC表級鎖,并將其保持到語句結束 |
| Mixed-mode inserts | InnoDB會分配比要插入的行數更多的自動增量值。但是,所有自動分配的值都是連續生成的。下一個單獨的可能是跳過的。 |
簡單地說,這種鎖模式顯著提高了可伸縮性,同時對于基于語句的復制來說是安全的,并為statement-based復制操作是安全
3)Innodb_autoinc_lock_mode = 2(“交叉”)
沒有語句使用表級AUTO-INC鎖,多個語句可以同時執行。
自動遞增值保證在所有并發執行的“類insert”語句中是唯一的,并且是單調遞增的。但是,由于多條語句可以同時生成數字,數字的分配是在語句之間交叉進行的,因此任何給定語句插入的行所生成的值可能不是連續的。
4)不同版本模式不同
在MySQL 8.0中,交錯鎖模式(innodb_autoinc_lock_mode=2)是默認設置。
在MySQL 8.0前,連續鎖模式是默認的(innodb_autoinc_lock_mode=1)。
5)索引必不可少
為了在InnoDB表中使用AUTO_INCREMENT機制,必須將AUTO_INCREMENT列定義為某個索引,這樣就可以在表上執行等價的SELECT MAX(ai_col)查找來獲得最大的列值。索引類型不需要是PRIMARY KEY或UNIQUE,但是為了避免AUTO_INCREMENT列中的重復值,建議使用這些索引類型。普通索引可以指定插入重復值的。
table definition; there can be only one auto column and it must be defined as a key
DROP TABLE IF EXISTS t_user;
CREATE TABLE `t_user` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
KEY `idx_id` (`id`)
) ENGINE=InnoDB ;
6)參數
mysql > SHOW VARIABLES WHERE variable_name LIKE 'auto_incre%' OR variable_name LIKE 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| innodb_autoinc_lock_mode | 2 |
+--------------------------+-------+
3 rows in set (0.00 sec)
| 參數 | 范圍 | 說明 |
|---|---|---|
| innodb_autoinc_lock_mode | 全局變量 | 0,1,2 三種鎖模式 |
| auto_increment_offset | 動態變量 | 表示自增長字段從那個數開始,取值范圍是1…65535 |
| auto_increment_increment | 動態變量 | 表示自增長字段每次遞增的量,其默認值是1,取值范圍是1…65535 |
如這兩個變量(auto_increment_offset,auto_increment_increment)的值設置為大于65,535 整數將導致其值被設置為65,535 ,如小于0的將導致其值被設置為 1;
7)復制中自增
-
基于STATEMENT語句的復制時,副本點沒有人為寫入的情況下innodb_autoinc_lock_mode設置為0或1,能確保生成的自增是一致的。如使用innodb_autoinc_lock_mode = 2,則不能確保副本上的自動增量值與源上的相同。
-
如果使用基于ROW或MIXED格式的復制,那么所有自動增量鎖模式都是安全的,因為基于行的復制對SQL語句的執行順序不敏感(混合格式對任何對基于語句的復制不安全的語句使用基于行的復制)。
mysql> INSERT INTO t_user01(name) SELECT name FROM t_user;
#binlog解析:
BEGIN
/*!*/;
# at 309
#211212 17:10:30 server id 129 end_log_pos 385 CRC32 0x387058ca Rows_query
# insert into t_user01(name) select name from t_user
# at 385
#211212 17:10:30 server id 129 end_log_pos 444 CRC32 0x671a7ed0 Table_map: `db8`.`t_user01` mapped to number 107
# at 444
#211212 17:10:30 server id 129 end_log_pos 493 CRC32 0xddc7d069 Write_rows: table id 107 flags: STMT_END_F
### INSERT INTO `db8`.`t_user01`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='A' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### INSERT INTO `db8`.`t_user01`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='C' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
# at 493
#211212 17:10:30 server id 129 end_log_pos 524 CRC32 0xee8d4eb5 Xid = 12
COMMIT/*!*/;
8)"Lost"自動遞增連續性
在所有鎖模式(0、1和2)中,如果生成自動增值值的事務回滾,那么這些自動增值值將"丟失"。一旦為自動增量列生成了一個值,無論"類insert"語句是否完成,以及包含的事務是否回滾,都不能回滾該值。這些丟失的值不會被重用。

9)自增列值重復利用問題
在MySQL 5.7及更早版本中,自動增量計數器存儲在內存中,而不是磁盤上。在服務器重啟后初始化一個自動增量計數器,InnoDB會在第一次插入包含AUTO_INCREMENT列的表時執行如下語句。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
在MySQL 8.0中,這種行為被改變成,當前最大的自動增量計數器值被寫入重做日志,每次它改變并保存到每個檢查點的數據字典。這些更改使當前最大自動增量計數器值在服務器重新啟動時保持不變。
在崩潰恢復的服務器重啟過程中,InnoDB使用存儲在數據字典中的當前最大自動增量值初始化內存中的自動增量計數器,并掃描重做日志中自上次檢查點以來寫入的自動增量計數器值。如果重做記錄的值大于內存中計數器的值,則應用重做記錄的值。
注意:如出現redo丟失 或損壞,問題可能還會存在。
10)常見問題
-
TRUNCAT命令會把初始化自增值,從最初的值開始,DELETE則不會
-
不管復制集群之間怎樣操作,步長是按照之前設置進行增長,除非人為更改。但也可能存在bug情況(https://bugs.mysql.com/bug.php?id=105871)
-
補齊寬度值ZEROFILL,用處不大
CREATE TABLE `t_user` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENTS=1;
-
自增主鍵是否適合業務主鍵
無主鍵需求的時候,比如日志數據,為了提高復制,運維效率,需要添加自曾主鍵。
有的業務設計依賴于自增主鍵的連續性,或自增鍵作為業務主鍵 是堅決不建議使用的。本身不存在業務意義。如出現bug問題,關聯表會存在數據沖突問題。 -
當AUTO_INCREMENT整數列的值用完時,后續的INSERT操作將返回一個復制鍵錯誤(主鍵唯一索引下),普通索引會持續的插入最大值。
-
日常統計自增字段使用情況:
SELECT infotb.TABLE_SCHEMA , infotb.TABLE_NAME, infotb.AUTO_INCREMENT,infocl.COLUMN_TYPE
FROM information_schema.TABLES as infotb INNER JOIN information_schema.COLUMNS infocl
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA
AND infotb.TABLE_NAME = infocl.TABLE_NAME
AND infocl.EXTRA='auto_increment';
總結:
首先三種innodb_autoinc_lock_mode模式合理選擇:
- 0:traditonal (每次都會產生表鎖)
- 1:consecutive (會產生一個輕量鎖,simple insert會獲得批量的鎖,保證連續插入)
- 2:interleaved (不會鎖表,來一個處理一個,并發最高)
建議可將innodb_autoinc_lock_mode設置為2,這時可在所有insert情況下表獲得最大并發度。
除此之外日常運維中需要關注的問題:
- 自增用完的情況。
- 多節點寫入中設置不同的步長(邏輯復制,延遲導致沖突)。
- 自增鍵選擇主鍵問題。
持續關注看是否能把Sequence結合進來,進一步完善機制。
ddl bug:
https://bugs.mysql.com/bug.php?id=92949
https://bugs.mysql.com/bug.php?id=108834




