在MySQL數據庫表設計中有個屬性，數字類型的自增列(auto incremnet)，從設計原初是為了滿足遞增規則解決計數，或無主鍵下采用主鍵(由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂，因此索引更緊湊）。

在數據庫個需要分清Sequence 和 自增鍵的區別。像mariadb，oracle 都有這個功能。但mysql 目前暫無提供。自增和Sequence 都是數字類型的，自增是單存的向上累加 無法保證等間隔，序列(Sequence)可以完美的保證連貫性可以為表中的行自動生成序列號，產生一組等間隔的數值。

但自增鍵也有一些不為關注的點，雖然數字類型，設計初如果可能的話，使用UNSIGNED屬性來允許更大的范圍。例如，如果使用Tinyint，最大允許的序號是127，對于tinyint 使用UNSIGNED屬性，最大值為255。需要了解所有整型類型的范圍。

自增鎖和模式：

自增對于數據來說，就是插入的時候的特殊處理，對于插入情況，在自增字段下可分為3種模式：

自增鎖機制：

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(“連續”)

簡單地說，這種鎖模式顯著提高了可伸縮性，同時對于基于語句的復制來說是安全的，并為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)

如這兩個變量（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