mysql 壓力測試之批量插入自增欄位不連續問題

標籤：

•         Gaps in auto-increment values for “bulk inserts”

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

With innodb_autoinc_lock_mode set to 2 (“interleaved”), there may be gaps in the auto-increment values generated by “bulk inserts,” but only if there are concurrently executing “INSERT-like” statements.

For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.

https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

 

資料庫機器上配置：

[email protected]:/etc/mysql$ grep -rn "innodb_autoinc_lock_mode" my.cnf

28:innodb_autoinc_lock_mode = 2

mysql 壓力測試之批量插入自增欄位不連續問題