Innodb 與自增長auto_increment

Innodb與Myisam儲存引擎對auto_crement的限制：

Innodb中自增長的列必須是索引，同時必須是索引的第一個列，否則會報如下錯誤：there can be only one column and it must be defined as a key；MyISAM沒有這個限制。

MyISAM採用表鎖設計，自增長不用考慮並發問題；

auto_increment在Innodb工作方式：

含有auto_increment列的表在Innodb數字字典中包含一個自增長的計數器，僅存在記憶體而不是磁碟上；

初始化auto_increment的值：

server 啟動後，對錶進行插入的時候，innodb擷取互斥鎖等價於：SELECT MAX(ai_col) FROM T FOR UPDATE;語句取回的值逐次加一，並被賦給列和自動成長計數器。自動成長計數器被初始化之後，如果插入一個明確指定的列值，而且該值大於當前計數器值，則計數器被設定為指定列值。如果沒有明確指定一個值，InnoDB給計數器增加一，並且賦新值給該列。

自增長不連續的原因：

當使用表鎖的方式進行auto_increment 值的插入時，為了提供插入效能，該鎖是在完成自增列的插入後釋放，並不是在事務完成後才釋放。所以自增列不連續一般出現了交易回復操作。

當使用互斥量的方式插入值的時候，由於並發的存在可能會造成值的不連續性；

自增長與鎖：

在5.1.22版本之前，採用一種特殊的表鎖機制- auto-inc Locking，鎖不是在一個事務完成後釋放，而是在完成對自增長值插入的SQL語句之後立即釋放；這個只是在一定程度上提高了並發插入效率，但對於insert...select的大資料量的插入會影響插入的效能；

在5.1.22之後的版本，innodb採用輕量級互斥量的自增長，對語句插入的行數，innodb提前分配好自增的值該為多少，後面的語句無需等待前面未執行完的語句即可執行因為對插入的行數已知，自增的值已提前通過互斥量分配好）；

舉例：某一insert語句1執行前，表的AUTO_INCREMENT=1，語句1的插入行數已知為3，innodb在語句1的實際插入操作執行前就預分配給該語句三個自增值，當有一個新的insert語句2要執行時，讀取的AUTO_INCREMENT=4，這樣雖然語句1可能還沒有執行完，語句2就可直接執行無需等待語句2。

注意：對於語句中如果明確設定一個自增值，同時插入多個值，並利用其自增的特性，可能會造成其主鍵衝突；舉例：如果當前的自增值為4，插入下面的語句將會造成主鍵衝突：INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 因為所有自增值的增長都是根據前一個語句的最大自增值來確定的；

鎖設計的參數：innodb_autoinc_lock_mode 參數值 0 1(預設值) 2

0:表示使用auto-inco 表鎖機制不推薦）,優點是在不發生復原的情況下，能保證自增值是連續的，但並發效率很慢；

1：使用互斥量mutex）對記憶體中的計數器進行累加，對於 “bulk insert”來說還是使用表鎖機制，如果使用auto-inc 產生自增長的值，此時再進行“simple inserts”操作時，仍舊使用 auto-inc的方式產生自增值；優點：就算binlog複製是在sbr的方式下，那也能保證資料的連續性；

“bulk insert” 是指在插入前不確定要插入的行數的語句，比如 insert...select ;replace...select;load data）

“simple insert”是指插入前就能確定插入行數的語句；比如insert ;replace;不包含：insert... on dupliate key update...這樣的語句）

2：對於所有的語句都使用互斥量獲得自增長值；優點：並發效率高；缺點：可能會造成自增值的不連續性；最好是基於RBR模式進行複製，保持主從資料的一致性；

本文出自 “技術成就夢想” 部落格，請務必保留此出處http://weipengfei.blog.51cto.com/1511707/1299792