引言

小A正在balabala寫(xiě)代碼呢，DBA小B突然發(fā)來(lái)了一條消息，“快看看你的用戶(hù)特定信息表T，里面的主鍵，也就是自增id，都到16億了，這才多久，在這樣下去過(guò)不了多久主鍵就要超出范圍了，插入就會(huì)失敗，balabala......”

我記得沒(méi)有這么多，最多1k多萬(wàn)，count了下，果然是1100萬(wàn)。原來(lái)運(yùn)維是通過(guò)auto_increment那個(gè)值看的，就是說(shuō)，表中有大量的刪除插入操作，但是我大部分情況都是更新的，怎么會(huì)這樣？

下面話(huà)不多說(shuō)了，來(lái)一起看看詳細(xì)的介紹吧

問(wèn)題排查

這張表是一個(gè)簡(jiǎn)單的接口服務(wù)在使用，每天大數(shù)據(jù)會(huì)統(tǒng)計(jì)一大批信息，然后推送給小A，小A將信息更新到數(shù)據(jù)庫(kù)中，如果是新數(shù)據(jù)就插入，舊數(shù)據(jù)就更新之前的數(shù)據(jù)，對(duì)外接口就只有查詢(xún)了。

很快，小A就排查了一遍自己的代碼，沒(méi)有刪除的地方，也沒(méi)有主動(dòng)插入、更新id的地方，怎么會(huì)這樣呢？難道是小B的原因，也不太可能，DBA那邊兒管理很多表，有問(wèn)題的話(huà)早爆出來(lái)了，但問(wèn)題在我這里哪里也沒(méi)頭緒。

小A又仔細(xì)觀察了這1000多萬(wàn)已有的數(shù)據(jù)，將插入時(shí)間、id作為主要觀察字段，很快，發(fā)現(xiàn)了個(gè)問(wèn)題，每天第一條插入的數(shù)據(jù)總是比前一天多1000多萬(wàn)，有時(shí)候遞增的多，有時(shí)候遞增的少，小A又將矛頭指向了DBA小B，將問(wèn)題又給小B描述了一遍。

小B問(wèn)了小A，“你是是不是用了REPLACE INTO ...語(yǔ)句”，這是怎么回事呢，原來(lái)REPLACE INTO ...會(huì)對(duì)主鍵有影響。

REPLACE INTO ...對(duì)主鍵的影響

假設(shè)有一張表t1:

CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID，自增',
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用戶(hù)uid',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '用戶(hù)昵稱(chēng)',
PRIMARY KEY (`id`),
UNIQUE KEY `u_idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測(cè)試replace into';

如果新建這張表，執(zhí)行下面的語(yǔ)句，最后的數(shù)據(jù)記錄如何呢？

insert into t1 values(NULL, 100, "test1"),(NULL, 101, "test2");
replace into t1 values(NULL, 100, "test3");

原來(lái)，REPLACE INTO ...每次插入的時(shí)候如果唯一索引對(duì)應(yīng)的數(shù)據(jù)已經(jīng)存在，會(huì)刪除原數(shù)據(jù)，然后重新插入新的數(shù)據(jù)，這也就導(dǎo)致id會(huì)增大，但實(shí)際預(yù)期可能是更新那條數(shù)據(jù)。

小A說(shuō)：“我知道replace是這樣，所有既沒(méi)有用它”，但還是又排查了一遍，確實(shí)不是自己的問(wèn)題，沒(méi)有使用REPLACE INTO ...，

小A又雙叒叕仔細(xì)的排查了一遍，還是沒(méi)發(fā)現(xiàn)問(wèn)題，就讓小B查下binlog日志，看看是不是有什么奇怪的地方，查了之后還是沒(méi)發(fā)現(xiàn)問(wèn)題，確實(shí)存在跳躍的情況，但并沒(méi)有實(shí)質(zhì)性的問(wèn)題。

下圖中@1的值對(duì)應(yīng)的是自增主鍵id，用(@2, @3)作為唯一索引

后來(lái)過(guò)了很久，小B給小A指了個(gè)方向，小A開(kāi)始懷疑自己的插入更新語(yǔ)句INSERT ... ON DUPLICATE KEY UPDATE ...了，查了許久，果然是這里除了問(wèn)題。

INSERT ... ON DUPLICATE KEY UPDATE ...對(duì)主鍵的影響

這個(gè)語(yǔ)句跟REPLACE INTO ...類(lèi)似，不過(guò)他并不會(huì)變更該條記錄的主鍵，還是上面t1這張表，我們執(zhí)行下面的語(yǔ)句，執(zhí)行完結(jié)果是什么呢？

insert into t1 values(NULL, 100, "test4") on duplicate key update name = values(name);

沒(méi)錯(cuò)，跟小A預(yù)想的一樣，主鍵并沒(méi)有增加，而且name字段已經(jīng)更新為想要的了，但是執(zhí)行結(jié)果有條提示，引起了小A的注意

No errors; 2 rows affected, taking 10.7ms

明明更新了一條數(shù)據(jù)，為什么這里的影響記錄條數(shù)是2呢？小A，又看了下目前表中的auto_increment

CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID，自增',
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用戶(hù)uid',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '用戶(hù)昵稱(chēng)',
PRIMARY KEY (`id`),
UNIQUE KEY `u_idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='測(cè)試replace into';

竟然是5`，這里本應(yīng)該是4的。

也就是說(shuō)，上面的語(yǔ)句，會(huì)跟REPLACE INTO ...類(lèi)似的會(huì)將自增ID加1，但實(shí)際記錄沒(méi)有加，這是為什么呢?

查了資料之后，小A得知，原來(lái)，mysql主鍵自增有個(gè)參數(shù)innodb_autoinc_lock_mode，他有三種可能只0,1,2，mysql5.1之后加入的，默認(rèn)值是1，之前的版本可以看做都是0。

可以使用下面的語(yǔ)句看當(dāng)前是哪種模式

select @@innodb_autoinc_lock_mode;

小A使用的數(shù)據(jù)庫(kù)默認(rèn)值也是1，當(dāng)做簡(jiǎn)單插入（可以確定插入行數(shù)）的時(shí)候，直接將auto_increment加1，而不會(huì)去鎖表，這也就提高了性能。當(dāng)插入的語(yǔ)句類(lèi)似insert into select ...這種復(fù)雜語(yǔ)句的時(shí)候，提前不知道插入的行數(shù)，這個(gè)時(shí)候就要要鎖表（一個(gè)名為AUTO_INC的特殊表鎖）了，這樣auto_increment才是準(zhǔn)確的，等待語(yǔ)句結(jié)束的時(shí)候才釋放鎖。還有一種稱(chēng)為Mixed-mode inserts的插入，比如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')，其中一部分明確指定了自增主鍵值，一部分未指定，還有我們這里討論的INSERT ... ON DUPLICATE KEY UPDATE ...也屬于這種，這個(gè)時(shí)候會(huì)分析語(yǔ)句，然后按盡可能多的情況去分配auto_incrementid，這個(gè)要怎么理解呢，我看下面這個(gè)例子:

truncate table t1;
insert into t1 values(NULL, 100, "test1"),(NULL, 101, "test2"),(NULL, 102, "test2"),(NULL, 103, "test2"),(NULL, 104, "test2"),(NULL, 105, "test2");

-- 此時(shí)數(shù)據(jù)表下一個(gè)自增id是7

delete from t1 where id in (2,3,4);

-- 此時(shí)數(shù)據(jù)表只剩1，5，6了，自增id還是7

insert into t1 values(2, 106, "test1"),(NULL, 107, "test2"),(3, 108, "test2");

-- 這里的自增id是多少呢？

上面的例子執(zhí)行完之后表的下一個(gè)自增id是10，你理解對(duì)了嗎，因?yàn)樽詈笠粭l執(zhí)行的是一個(gè)Mixed-mode inserts語(yǔ)句，innoDB會(huì)分析語(yǔ)句，然后分配三個(gè)id，此時(shí)下一個(gè)id就是10了，但分配的三個(gè)id并不一定都使用。此處 @總是遲到 多謝指出，看官方文檔理解錯(cuò)了

模式0的話(huà)就是不管什么情況都是加上表鎖，等語(yǔ)句執(zhí)行完成的時(shí)候在釋放，如果真的添加了記錄，將auto_increment加1。

至于模式2，什么情況都不加AUTO_INC鎖，存在安全問(wèn)題，當(dāng)binlog格式設(shè)置為Statement模式的時(shí)候，從庫(kù)同步的時(shí)候，執(zhí)行結(jié)果可能跟主庫(kù)不一致，問(wèn)題很大。因?yàn)榭赡苡幸粋€(gè)復(fù)雜插入，還在執(zhí)行呢，另外一個(gè)插入就來(lái)了，恢復(fù)的時(shí)候是一條條來(lái)執(zhí)行的，就不能重現(xiàn)這種并發(fā)問(wèn)題，導(dǎo)致記錄id可能對(duì)不上。

至此，id跳躍的問(wèn)題算是分析完了，由于innodb_autoinc_lock_mode值是1，INSERT ... ON DUPLICATE KEY UPDATE ...是簡(jiǎn)單的語(yǔ)句，預(yù)先就可以計(jì)算出影響的行數(shù)，所以不管是否更新，這里都將auto_increment加1（多行的話(huà)大于1）。

如果將innodb_autoinc_lock_mode值改為0，再次執(zhí)行INSERT ... ON DUPLICATE KEY UPDATE ...的話(huà)，你會(huì)發(fā)現(xiàn)auto_increment并沒(méi)有增加，因?yàn)檫@種模式直接加了AUTO_INC鎖，執(zhí)行完語(yǔ)句的時(shí)候釋放，發(fā)現(xiàn)沒(méi)有增加行數(shù)的話(huà)，不會(huì)增加自增id的。

INSERT ... ON DUPLICATE KEY UPDATE ...影響的行數(shù)是1為什么返回2？

為什么會(huì)這樣呢，按理說(shuō)影響行數(shù)就是1啊，看看官方文檔的說(shuō)明

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values

官方明確說(shuō)明了，插入影響1行，更新影響2行，0的話(huà)就是存在且更新前后值一樣。是不是很不好理解？

其實(shí)，你要這樣想就好了，這是為了區(qū)分到底是插入了還是更新了，返回1表示插入成功，2表示更新成功。

解決方案

將innodb_autoinc_lock_mode設(shè)置為0肯定可以解決問(wèn)題，但這樣的話(huà)，插入的并發(fā)性可能會(huì)受很大影響，因此小A自己想著DBA也不會(huì)同意。經(jīng)過(guò)考慮，目前準(zhǔn)備了兩種較為可能的解決方案：

修改業(yè)務(wù)邏輯

修改業(yè)務(wù)邏輯，將INSERT ... ON DUPLICATE KEY UPDATE ...語(yǔ)句拆開(kāi)，先去查詢(xún)，然后去更新，這樣就可以保證主鍵不會(huì)不受控制的增大，但增加了復(fù)雜性，原來(lái)的一次請(qǐng)求可能變?yōu)閮纱?，先查?xún)有沒(méi)有，然后去更新。

刪除表的自增主鍵

刪除自增主鍵，讓唯一索引來(lái)做主鍵，這樣子基本不用做什么變動(dòng)，只要確定目前的自增主鍵沒(méi)有實(shí)際的用處即可，這樣的話(huà)，插入刪除的時(shí)候可能會(huì)影響效率，但對(duì)于查詢(xún)多的情況來(lái)說(shuō)，小A比較兩種之后更愿意選擇后者。

結(jié)語(yǔ)

其實(shí)INSERT ... ON DUPLICATE KEY UPDATE ...這個(gè)影響行數(shù)是2的，小A很早就發(fā)現(xiàn)了，只是沒(méi)有保持好奇心，不以為然罷了，沒(méi)有深究其中的問(wèn)題，這深究就起來(lái)會(huì)帶出來(lái)一大串新知識(shí)，挺好，看來(lái)小A還是要對(duì)外界保持好奇心，保持敏感，這樣才會(huì)有進(jìn)步。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。