MySQL核心月報 2014.09-MySQL· 捉蟲動态·auto_increment

<b>背景：</b>

　　innodb引擎使用b_tree結構儲存表資料，這樣就需要一個唯一鍵表示每一行記錄(比如二級索引記錄引用)。

　　innodb表定義中處理主鍵的邏輯是：

　　1.如果表定義了主鍵，就使用主鍵唯一定位一條記錄

　　2.如果沒有定義主鍵，innodb就生成一個全局唯一的rowid來定位一條記錄

<b>auto_increment的由來:</b>

　　1.innodb強烈推薦在設計表中自定義一個主鍵，因為rowid是全局唯一的，是以如果有很多表沒有定義主鍵，就會在生成rowid上産生争用。

　　row_id由mutex保護，并在每次checkpoint的時候，寫入到資料字典的檔案頭。

　　2.當使用者自定義了主鍵後，由于大部分實際應用部署的分布式，是以主鍵值的生成上，采用集中式的方式，更容易實作唯一性，是以auto_increment非常合适。

　　auto_increment也帶來兩個好處：

　　1. auto_increment的值是表級别的，不會在db級别上産生争用

　　2. 由于auto_increment的順序性，減少了随機讀的可能，保證了寫入的page的緩沖命中。（不可否認，寫入的并發足夠大時，會産生熱點塊的争用）

<b>auto_increment引起的bug：</b>

　　環境：mysql 5.6.16版本, binlog_format=row

　　case複現：

　　當進行主備切換後，導緻主鍵沖突，slave恢複異常。

　　同樣insert on duplication update 語句同樣存在這樣的問題。

<b>aliyun rds分支bug修複</b>

　　<b>問題的原因：</b>innodb對于auto_increment的處理，當語句是insert時，會進行遞增，而update，delete語句則不更新。

　　當replace語句在主庫的執行時：

　　1. 先按照insert語句執行，發現uk沖突。

　　2. 演變成update語句進行更新。

　　這樣在主庫，雖然insert失敗，但auto_increment也遞增上去了。但到備庫，row格式下，隻産生了一個update row event，

　　備庫無法知道主庫是一個replace語句，而且insert還失敗了， 是以auto_increment在備庫沒有遞增。

　　<b>修複方式：</b>在備庫，對于update進行auto_increment遞增，可能會産生副作用，即auto_increment的浪費，但不會産生主鍵沖突。

<b>那些年經曆的auto_increment坑：</b>

　　1. 執行個體重新開機，主鍵沖突：

　　記憶體中的autoinc值，在系統重新開機後，使用select max(id) from table來初始化。是以，如果你設計的業務表，存在delete操作，那麼一旦你的執行個體crash過，重新開機後，可能會複用以前使用過的id值。如果你需要持續對這個表進行邏輯備份，那麼就可能會碰到主鍵沖突的問題。

　　2. load file阻塞：

　　在設定innodb_autoinc_lock_mode=1的時候，mysql為了維護單個statement語句的id連續性，當不确定插入條數的時候，會在語句整個執行過程中

　　持有lock_auto_inc, /* locks the auto-inc counter of a table in an exclusive mode */

　　這個鎖是表級别的，使用互斥模式。

　　是以，在繁忙的表上，如果要導入資料，小心可能阻塞正常的業務寫入，并發寫入在這個時候也會阻塞的。