共計 5610 個字符，預計需要花費 15 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 Mysql 中 INNODB 自增主鍵的問題有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
背景：
自增長是一個很常見的數據屬性，在 MySQL 中大家都很愿意讓自增長屬性的字段當一個主鍵。特別是 InnoDB，因為 InnoDB 的聚集索引的特性，使用自增長屬性的字段當主鍵性能更好，這里要說明下自增主鍵需要注意的幾個事項。
問題一：表鎖
在 MySQL5.1.22 之前，InnoDB 自增值是通過其本身的自增長計數器來獲取值，該實現方式是通過表鎖機制來完成的（AUTO-INC LOCKING）。鎖不是在每次事務完成后釋放，而是在完成對自增長值插入的 SQL 語句后釋放，要等待其釋放才能進行后續操作。比如說當表里有一個 auto_increment 字段的時候，innoDB 會在內存里保存一個計數器用來記錄 auto_increment 的值，當插入一個新行數據時，就會用一個表鎖來鎖住這個計數器，直到插入結束。如果大量的并發插入，表鎖會引起 SQL 堵塞。insert into…..select 大量插入數據的性能也比較差
在 5.1.22 之后，InnoDB 為了解決自增主鍵鎖表的問題，引入了參數 innodb_autoinc_lock_mode，該實現方式是通過輕量級互斥量的增長機制完成的。它是專門用來在使用 auto_increment 的情況下調整鎖策略的，目前有三種選擇：
插入類型說明：
INSERT-LIKE：指所有的插入語句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA 等
Simple inserts：指在插入前就能確定插入行數的語句，包括 INSERT、REPLACE，不包含 INSERT…ON DUPLICATE KEY UPDATE 這類語句。Bulk inserts：指在插入前不能確定得到插入行的語句。如 INSERT…SELECT,REPLACE…SELECT,LOAD DATA.
Mixed-mode inserts: 指其中一部分是自增長的，有一部分是確定的。0：通過表鎖的方式進行，也就是所有類型的 insert 都用 AUTO-inc locking。
1：默認值，對于 simple insert 自增長值的產生使用互斥量對內存中的計數器進行累加操作，對于 bulk insert 則還是使用表鎖的方式進行。
2：對所有的 insert-like 自增長值的產生使用互斥量機制完成，性能最高，并發插入可能導致自增值不連續，可能會導致 Statement 的 Replication 出現不一致，使用該模式，需要用 Row Replication 的模式。
在 mysql5.1.22 之前，mysql 的 INSERT-LIKE 語句會在執行整個語句的過程中使用一個 AUTO-INC 鎖將表鎖住，直到整個語句結束（而不是事務結束）。因此在使用 INSERT…SELECT、INSERT…values(…),values(…)時，LOAD DATA 等耗費時間較長的操作時，會將整個表鎖住，而阻塞其他的 insert-like,update 等語句。推薦使用程序將這些語句分成多條語句，一一插入，減少單一時間的鎖表時間。
解決：
通過參數 innodb_autoinc_lock_mode =1/ 2 解決，并用 simple inserts 模式插入。
問題二：自增主鍵不連續
5.1.22 后 默認：innodb_autoinc_lock_mode = 1
直接通過分析語句，獲得要插入的數量，然后一次性分配足夠的 auto_increment id，只會將整個分配的過程鎖住。
root@localhost : test 04:23:28 show variables like innodb_autoinc_lock_mode +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 1 | +--------------------------+-------+ 1 row in set (0.00 sec)
root@localhost : test 04:23:31 create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.16 sec)
root@localhost : test 04:23:35 insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0 root@localhost : test 04:23:39 show create table tmp_auto_inc\G; *************************** 1. row ***************************插入 10 條記錄，但表的 AUTO_INCREMENT=16，再插入一條的時候，表的自增 id 已經是不連續了。
原因：
參數 innodb_autoinc_lock_mode = 1 時，每次會“預申請”多余的 id(handler.cc:compute_next_insert_id)，而 insert 執行完成后，會特別將這些預留的 id 空出，就是特意將預申請后的當前最大 id 回寫到表中(dict0dict.c:dict_table_autoinc_update_if_greater)。
這個預留的策略是“不夠時多申請幾個”，實際執行中是分步申請。至于申請幾個，是由當時“已經插入了幾條數據 N”決定的。當 auto_increment_offset=1 時，預申請的個數是 N-1。
所以會發現：插入只有 1 行時，你看不到這個現象，并不預申請。而當有 N 1 行時，則需要。多申請的數目為 N -1，因此執行后的自增值為：1+N+(N-1)。測試中為 10 行，則：1+10+9 =20，和 16 不一致？原因是：當插入 8 行的時候，表的 AUTO_INCREMENT 已經是 16 了，所以插入 10 行時，id 已經在第 8 行時預留了，所以直接使用，自增值仍為 16。所以當插入 8 行的時候，多申請了 7 個 id，即：9，10，11，12，13，14，15。按照例子中的方法插入 8~15 行，表的 AUTO_INCREMENT 始終是 16
為了發現規律，這兒我做了實驗，不是很準確，插入行數與對應的 autocommit 分別是 2》4 3》4 4-7》8 8-15》16 16-31》32），只能說 AUTO_INCREMENT 有可能是 2n(具體什么時候是 2n 還沒發現規律)，范圍應該是 [n,2n] 之間
驗證：
插入 16 行：猜測 預申請的 id：1+16+（16-1）= 32，即：AUTO_INCREMENT=32
root@localhost : test 04:55:45 create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.17 sec)
root@localhost : test 04:55:48 insert into tmp_auto_inc(talkid) select talkId from sns_talk_dialog limit 16;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0 root@localhost :test 04:55:50 show create table tmp_auto_inc\G;*************************** 1. row ***************************Table: tmp_auto_inc Create Table: CREATE TABLE `tmp_auto_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`talkid` int(11) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=gbk 1 row in set (0.00 sec) --- 第一次插入所以為 2 *16和猜測的一樣，自增 id 到了 32。所以當插入 16 行的時候，多申請了 17,18,19…,31。
所以導致 ID 不連續的原因是因為 innodb_autoinc_lock_mode = 1 時，會多申請 id。好處是：一次性分配足夠的 auto_increment id，只會將整個分配的過程鎖住。
5.1.22 前 默認：innodb_autoinc_lock_mode = 0
root@localhost : test 04:25:12 show variables like innodb_autoinc_lock_mode+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 0 | +--------------------------+-------+1 row in set (0.00 sec)
root@localhost : test 04:25:15 create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.17 sec)
root@localhost : test 04:25:17 insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0root@localhost : test 04:25:21 show create table tmp_auto_inc\G;*************************** 1. row ***************************Table: tmp_auto_inc Create Table: CREATE TABLE `tmp_auto_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT,
`talkid` int(11) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk 1 row in set (0.00 sec)插入 10 條記錄，但表的 AUTO_INCREMENT=11，再插入一條的時候，表的自增 id 還是連續的。
innodb_autoinc_lock_mode = 2 和 innodb_autoinc_lock_mode = 1 的測試情況一樣。但該模式下是來一個分配一個，而不會鎖表，只會鎖住分配 id 的過程，和 1 的區別在于，不會預分配多個，這種方式并發性最高。但是在 replication 中當 binlog_format 為 statement-based 時存在問題
解決：
盡量讓主鍵 ID 沒有業務意義，或則使用 simple inserts 模式插入。
結論：
當 innodb_autoinc_lock_mode 為 0 時候，自增 id 都會連續，但是會出現表鎖的情況，解決該問題可以把 innodb_autoinc_lock_mode 設置為 1，甚至是 2。會提高性能，但是會在一定的條件下導致自增 id 不連續。
以上是“Mysql 中 INNODB 自增主鍵的問題有哪些”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
