作者：燕南飛Liam
引言
小A正在balabala寫代碼呢实束，DBA小B突然發(fā)來了一條消息，“快看看你的用戶特定信息表T遮斥，里面的主鍵，也就是自增id，都到16億了蹲姐，這才多久，在這樣下去過不了多久主鍵就要超出范圍了人柿，插入就會失敗柴墩，balabala......”
我記得沒有這么多，最多1k多萬凫岖，count了下江咳，果然是1100萬。原來運(yùn)維是通過 auto_increment那個值看的哥放，就是說歼指，表中有大量的刪除插入操作，但是我大部分情況都是更新的甥雕，怎么會這樣踩身？
問題排查
這張表是一個簡單的接口服務(wù)在使用，每天大數(shù)據(jù)會統(tǒng)計(jì)一大批信息社露，然后推送給小A挟阻，小A將信息更新到數(shù)據(jù)庫中，如果是新數(shù)據(jù)就插入峭弟，舊數(shù)據(jù)就更新之前的數(shù)據(jù)附鸽，對外接口就只有查詢了。
很快瞒瘸，小A就排查了一遍自己的代碼坷备，沒有刪除的地方，也沒有主動插入挨务、更新id的地方击你，怎么會這樣呢玉组？難道是小B的原因谎柄，也不太可能，DBA那邊兒管理很多表惯雳，有問題的話早爆出來了朝巫，但問題在我這里哪里也沒頭緒。
小A又仔細(xì)觀察了這1000多萬已有的數(shù)據(jù)石景，將插入時(shí)間劈猿、id作為主要觀察字段拙吉，很快，發(fā)現(xiàn)了個問題揪荣，每天第一條插入的數(shù)據(jù)總是比前一天多1000多萬筷黔，有時(shí)候遞增的多，有時(shí)候遞增的少仗颈，小A又將矛頭指向了DBA小B佛舱，將問題又給小B描述了一遍。
小B問了小A挨决，“你是是不是用了 REPLACE INTO...語句”请祖，這是怎么回事呢，原來 REPLACE INTO...會對主鍵有影響脖祈。
“REPLACE INTO ...”對主鍵的影響
假設(shè)有一張表 t1：
如果新建這張表肆捕，執(zhí)行下面的語句，最后的數(shù)據(jù)記錄如何呢盖高？
原來慎陵， REPLACE INTO...每次插入的時(shí)候如果唯一索引對應(yīng)的數(shù)據(jù)已經(jīng)存在，會刪除原數(shù)據(jù)或舞，然后重新插入新的數(shù)據(jù)荆姆，這也就導(dǎo)致id會增大，但實(shí)際預(yù)期可能是更新那條數(shù)據(jù)映凳。
小A說：“我知道replace是這樣胆筒，所有既沒有用它”，但還是又排查了一遍诈豌，確實(shí)不是自己的問題仆救，沒有使用 REPLACE INTO...。
小A又雙叒叕仔細(xì)的排查了一遍矫渔，還是沒發(fā)現(xiàn)問題彤蔽，就讓小B查下binlog日志，看看是不是有什么奇怪的地方庙洼，查了之后還是沒發(fā)現(xiàn)問題顿痪，確實(shí)存在跳躍的情況，但并沒有實(shí)質(zhì)性的問題油够。
下圖中 @1的值對應(yīng)的是自增主鍵 id蚁袭，用 (@2,@3)作為唯一索引：
后來過了很久，小B給小A指了個方向石咬，小A開始懷疑自己的插入更新語句
INSERT...ON DUPLICATE KEY UPDATE...了揩悄，查了許久，果然是這里除了問題鬼悠。
“INSERT ... ON DUPLICATE KEY UPDATE ...”對主鍵的影響
這個語句跟 REPLACE INTO...類似删性，不過他并不會變更該條記錄的主鍵亏娜，還是上面 t1這張表，我們執(zhí)行下面的語句蹬挺，執(zhí)行完結(jié)果是什么呢维贺？
沒錯，跟小A預(yù)想的一樣巴帮，主鍵并沒有增加幸缕，而且 name字段已經(jīng)更新為想要的了，但是執(zhí)行結(jié)果有條提示晰韵，引起了小A的注意：
No errors; 2 rows affected, taking 10.7ms
明明更新了一條數(shù)據(jù)发乔，為什么這里的影響記錄條數(shù)是2呢？小A雪猪，又看了下目前表中的 auto_increment：
竟然是5`栏尚，這里本應(yīng)該是4的。
也就是說只恨，上面的語句译仗，會跟 REPLACE INTO...類似的會將自增ID加1，但實(shí)際記錄沒有加官觅，這是為什么呢?
查了資料之后纵菌，小A得知，原來休涤，mysql主鍵自增有個參數(shù) innodb_autoinc_lock_mode咱圆，他有三種可能只 0, 1, 2，mysql5.1之后加入的功氨，默認(rèn)值是 1序苏，之前的版本可以看做都是 0。
可以使用下面的語句看當(dāng)前是哪種模式：
小A使用的數(shù)據(jù)庫默認(rèn)值也是1捷凄，當(dāng)做簡單插入（可以確定插入行數(shù)）的時(shí)候忱详，直接將auto_increment加1，而不會去鎖表跺涤，這也就提高了性能匈睁。當(dāng)插入的語句類似insert into select ...這種復(fù)雜語句的時(shí)候，提前不知道插入的行數(shù)桶错，這個時(shí)候就要要鎖表（一個名為AUTO_INC的特殊表鎖）了航唆，這樣auto_increment才是準(zhǔn)確的，等待語句結(jié)束的時(shí)候才釋放鎖牛曹。還有一種稱為Mixed-mode inserts的插入佛点，比如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')醇滥，其中一部分明確指定了自增主鍵值黎比，一部分未指定超营，還有我們這里討論的INSERT ... ON DUPLICATE KEY UPDATE ...也屬于這種，這個時(shí)候會分析語句阅虫，然后按盡可能多的情況去分配auto_incrementid演闭，這個要怎么理解呢，我看下面這個例子：
此時(shí)數(shù)據(jù)表下一個自增id是7：
此時(shí)數(shù)據(jù)表只剩1颓帝，5米碰，6了，自增id還是7：
這里的自增id是多少呢购城？
上面的例子執(zhí)行完之后表的下一個自增id是10吕座，你理解對了嗎，因?yàn)樽詈笠粭l執(zhí)行的是一個 Mixed-mode inserts語句瘪板，innoDB會分析語句吴趴，然后分配三個id，此時(shí)下一個id就是10了侮攀，但分配的三個id并不一定都使用锣枝。此處* @總是遲到[zongshichidao] * 多謝指出，看官方文檔理解錯了兰英。
模式 0的話就是不管什么情況都是加上表鎖撇叁，等語句執(zhí)行完成的時(shí)候在釋放，如果真的添加了記錄畦贸，將 auto_increment加1陨闹。
至于模式 2，什么情況都不加 AUTO_INC鎖薄坏，存在安全問題正林，當(dāng) binlog格式設(shè)置為 Statement模式的時(shí)候，從庫同步的時(shí)候颤殴，執(zhí)行結(jié)果可能跟主庫不一致觅廓，問題很大。因?yàn)榭赡苡幸粋€復(fù)雜插入涵但，還在執(zhí)行呢杈绸，另外一個插入就來了，恢復(fù)的時(shí)候是一條條來執(zhí)行的矮瘟，就不能重現(xiàn)這種并發(fā)問題瞳脓，導(dǎo)致記錄id可能對不上。
至此澈侠，id跳躍的問題算是分析完了劫侧，由于 innodb_autoinc_lock_mode值是1， INSERT...ON DUPLICATE KEY UPDATE...是簡單的語句，預(yù)先就可以計(jì)算出影響的行數(shù)烧栋，所以不管是否更新写妥，這里都將 auto_increment加1（多行的話大于1）。
如果將 innodb_autoinc_lock_mode值改為 0审姓，再次執(zhí)行 INSERT...ON DUPLICATE KEY UPDATE...的話珍特，你會發(fā)現(xiàn) auto_increment并沒有增加，因?yàn)檫@種模式直接加了 AUTO_INC鎖魔吐，執(zhí)行完語句的時(shí)候釋放扎筒，發(fā)現(xiàn)沒有增加行數(shù)的話，不會增加自增id的酬姆。
“INSERT ... ON DUPLICATE KEY UPDATE ...”影響的行數(shù)是1為什么返回2嗜桌？
為什么會這樣呢，按理說影響行數(shù)就是1啊辞色，看看官方文檔的說明：
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
官方明確說明了症脂，插入影響1行，更新影響2行淫僻，0的話就是存在且更新前后值一樣诱篷。是不是很不好理解？
其實(shí)雳灵，你要這樣想就好了棕所，這是為了區(qū)分到底是插入了還是更新了，返回1表示插入成功悯辙，2表示更新成功琳省。
解決方案
將 innodb_autoinc_lock_mode設(shè)置為0肯定可以解決問題，但這樣的話躲撰，插入的并發(fā)性可能會受很大影響针贬，因此小A自己想著DBA也不會同意。經(jīng)過考慮拢蛋，目前準(zhǔn)備了兩種較為可能的解決方案：
修改業(yè)務(wù)邏輯
修改業(yè)務(wù)邏輯桦他，將 INSERT...ON DUPLICATE KEY UPDATE...語句拆開，先去查詢谆棱，然后去更新快压，這樣就可以保證主鍵不會不受控制的增大，但增加了復(fù)雜性垃瞧，原來的一次請求可能變?yōu)閮纱文枇樱炔樵冇袥]有，然后去更新个从。
刪除表的自增主鍵
刪除自增主鍵脉幢，讓唯一索引來做主鍵歪沃，這樣子基本不用做什么變動丐巫，只要確定目前的自增主鍵沒有實(shí)際的用處即可捅伤，這樣的話缤骨，插入刪除的時(shí)候可能會影響效率裆甩，但對于查詢多的情況來說，小A比較兩種之后更愿意選擇后者剖效。
結(jié)語
其實(shí) INSERT...ON DUPLICATE KEY UPDATE...這個影響行數(shù)是2的，小A很早就發(fā)現(xiàn)了，只是沒有保持好奇心外驱，不以為然罷了，沒有深究其中的問題腻窒，這深究就起來會帶出來一大串新知識昵宇，挺好，看來小A還是要對外界保持好奇心儿子，保持敏感瓦哎，這樣才會有進(jìn)步。
