InnoDB provides an optimization that significantly improves scalability and
performance of SQL statements that insert rows into tables with
columns. To use the
AUTO_INCREMENT mechanism with an
InnoDB table, an
ai_col must be defined as part of an index such that it is
possible to perform the equivalent of an indexed
lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the
first column of some table index.
This section provides background information on the original ("traditional")
implementation of auto-increment locking in
InnoDB, explains the configurable
locking mechanism, documents the parameter for configuring the mechanism, and describes its behavior and
interaction with replication.