Mysql中INNODB自增主鍵的問題有哪些

小編給大家分享一下Mysql中INNODB自增主鍵的問題有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

背景：

      自增長是一個(gè)很常見的數(shù)據(jù)屬性，在MySQL中大家都很愿意讓自增長屬性的字段當(dāng)一個(gè)主鍵。特別是InnoDB，因?yàn)镮nnoDB的聚集索引的特性，使用自增長屬性的字段當(dāng)主鍵性能更好，這里要說明下自增主鍵需要注意的幾個(gè)事項(xiàng)。

問題一：表鎖

      在MySQL5.1.22之前，InnoDB自增值是通過其本身的自增長計(jì)數(shù)器來獲取值，該實(shí)現(xiàn)方式是通過表鎖機(jī)制來完成的（AUTO-INC LOCKING）。鎖不是在每次事務(wù)完成后釋放，而是在完成對自增長值插入的SQL語句后釋放，要等待其釋放才能進(jìn)行后續(xù)操作。比如說當(dāng)表里有一個(gè)auto_increment字段的時(shí)候，innoDB會(huì)在內(nèi)存里保存一個(gè)計(jì)數(shù)器用來記錄auto_increment的值，當(dāng)插入一個(gè)新行數(shù)據(jù)時(shí)，就會(huì)用一個(gè)表鎖來鎖住這個(gè)計(jì)數(shù)器，直到插入結(jié)束。如果大量的并發(fā)插入，表鎖會(huì)引起SQL堵塞。insert into.....select大量插入數(shù)據(jù)的性能也比較差

      在5.1.22之后，InnoDB為了解決自增主鍵鎖表的問題，引入了參數(shù)innodb_autoinc_lock_mode，該實(shí)現(xiàn)方式是通過輕量級互斥量的增長機(jī)制完成的。它是專門用來在使用auto_increment的情況下調(diào)整鎖策略的，目前有三種選擇：

插入類型說明：

INSERT-LIKE：指所有的插入語句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等
Simple inserts：指在插入前就能確定插入行數(shù)的語句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE這類語句。
Bulk inserts：指在插入前不能確定得到插入行的語句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.
Mixed-mode inserts:指其中一部分是自增長的，有一部分是確定的。

0：通過表鎖的方式進(jìn)行，也就是所有類型的insert都用AUTO-inc locking。

1：默認(rèn)值，對于simple insert 自增長值的產(chǎn)生使用互斥量對內(nèi)存中的計(jì)數(shù)器進(jìn)行累加操作，對于bulk insert 則還是使用表鎖的方式進(jìn)行。

2：對所有的insert-like 自增長值的產(chǎn)生使用互斥量機(jī)制完成，性能最高，并發(fā)插入可能導(dǎo)致自增值不連續(xù)，可能會(huì)導(dǎo)致Statement 的 Replication 出現(xiàn)不一致，使用該模式，需要用 Row Replication的模式。

      在mysql5.1.22之前，mysql的INSERT-LIKE語句會(huì)在執(zhí)行整個(gè)語句的過程中使用一個(gè)AUTO-INC鎖將表鎖住，直到整個(gè)語句結(jié)束（而不是事務(wù)結(jié)束）。因此在使用INSERT…SELECT、INSERT…values(…),values(…)時(shí)，LOAD DATA等耗費(fèi)時(shí)間較長的操作時(shí)，會(huì)將整個(gè)表鎖住，而阻塞其他的insert-like,update等語句。推薦使用程序?qū)⑦@些語句分成多條語句，一一插入，減少單一時(shí)間的鎖表時(shí)間。

解決：

通過參數(shù)innodb_autoinc_lock_mode =1/2解決，并用simple inserts 模式插入。

問題二：自增主鍵不連續(xù)

5.1.22后 默認(rèn)：innodb_autoinc_lock_mode = 1 
直接通過分析語句，獲得要插入的數(shù)量，然后一次性分配足夠的auto_increment id，只會(huì)將整個(gè)分配的過程鎖住。

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 *************************** 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=16 DEFAULT CHARSET=gbk 1 row in set (0.00 sec)

插入10條記錄，但表的AUTO_INCREMENT=16，再插入一條的時(shí)候，表的自增id已經(jīng)是不連續(xù)了。

原因：

      參數(shù)innodb_autoinc_lock_mode = 1時(shí)，每次會(huì)“預(yù)申請”多余的id(handler.cc:compute_next_insert_id)，而insert執(zhí)行完成后，會(huì)特別將這些預(yù)留的id空出，就是特意將預(yù)申請后的當(dāng)前最大id回寫到表中(dict0dict.c:dict_table_autoinc_update_if_greater)。

      這個(gè)預(yù)留的策略是“不夠時(shí)多申請幾個(gè)”， 實(shí)際執(zhí)行中是分步申請。至于申請幾個(gè)，是由當(dāng)時(shí)“已經(jīng)插入了幾條數(shù)據(jù)N”決定的。當(dāng)auto_increment_offset＝1時(shí)，預(yù)申請的個(gè)數(shù)是 N-1。

      所以會(huì)發(fā)現(xiàn)：插入只有1行時(shí)，你看不到這個(gè)現(xiàn)象，并不預(yù)申請。而當(dāng)有N>1行時(shí)，則需要。多申請的數(shù)目為N-1，因此執(zhí)行后的自增值為：1+N+(N-1)。測試中為10行，則：1+10+9 =20，和 16不一致？原因是：當(dāng)插入8行的時(shí)候，表的AUTO_INCREMENT已經(jīng)是16了，所以插入10行時(shí)，id已經(jīng)在第8行時(shí)預(yù)留了，所以直接使用，自增值仍為16。所以當(dāng)插入8行的時(shí)候，多申請了7個(gè)id，即：9，10，11，12，13，14，15。按照例子中的方法插入8~15行，表的AUTO_INCREMENT始終是16
為了發(fā)現(xiàn)規(guī)律，這兒我做了實(shí)驗(yàn)，不是很準(zhǔn)確，插入行數(shù)與對應(yīng)的autocommit分別是 2》4   3》4   4-7》8  8-15》16 16-31》32），只能說AUTO_INCREMENT有可能是2n(具體什么時(shí)候是2n還沒發(fā)現(xiàn)規(guī)律)，范圍應(yīng)該是[n,2n]之間

驗(yàn)證：

插入16行：猜測 預(yù)申請的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。所以當(dāng)插入16行的時(shí)候，多申請了17,18,19...,31 。

所以導(dǎo)致ID不連續(xù)的原因是因?yàn)閕nnodb_autoinc_lock_mode = 1時(shí)，會(huì)多申請id。好處是：一次性分配足夠的auto_increment id，只會(huì)將整個(gè)分配的過程鎖住。

5.1.22前 默認(rèn)：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: 0

root@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，再插入一條的時(shí)候，表的自增id還是連續(xù)的。

innodb_autoinc_lock_mode = 2 和 innodb_autoinc_lock_mode = 1 的測試情況一樣。但該模式下是來一個(gè)分配一個(gè)，而不會(huì)鎖表，只會(huì)鎖住分配id的過程，和1的區(qū)別在于，不會(huì)預(yù)分配多個(gè)，這種方式并發(fā)性最高。但是在replication中當(dāng)binlog_format為statement-based時(shí)存在問題

解決：

盡量讓主鍵ID沒有業(yè)務(wù)意義，或則使用simple inserts模式插入。

結(jié)論：

當(dāng)innodb_autoinc_lock_mode為0時(shí)候， 自增id都會(huì)連續(xù)，但是會(huì)出現(xiàn)表鎖的情況，解決該問題可以把innodb_autoinc_lock_mode 設(shè)置為1，甚至是2。會(huì)提高性能，但是會(huì)在一定的條件下導(dǎo)致自增id不連續(xù)。

以上是“Mysql中INNODB自增主鍵的問題有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！