mysql 压力测试之批量插入自增字段不连续问题

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

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

Withinnodb_autoinc_lock_modeset 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.

数据库机器上配置：

localadmin@TEST-MS01:/etc/mysql$ grep -rn “innodb_autoinc_lock_mode” my.cnf

28:innodb_autoinc_lock_mode = 2