http://dev.mysql.com/tech-resources/articles/mysql-5.1-recap.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
摘要：
以前每次INSERT是，使用一个全局计数器和表级锁来保证自增，但对于在大量批量INSERT的情况，则会带来严重的性能问题。
新的自增特性就是解决或者说优化这个问题。
New InnoDB Auto-Increment Functionality (MySQL 5.1.22)
Traditionally, that is to say, up until MySQL 5.1.22, InnoDB handled newly inserted records into an InnoDB table with an AUTO_INCREMENT column by using a global counter which held the last value for the auto-incrementing column. A lock would be placed on this counter for the duration of the SQL statement which did the inserting. After the statement completed, the lock would be released and other statements which inserted new rows into the table could acquire a lock on the counter.
This "traditional mode" worked well for many scenarios, particularly situations involving short, single-row inserts, as the time the lock was held for the statement was minimized. However, some performance issues would come into play when statements such as INSERT INTO my_innodb_table SELECT * FROM some_other_table were executed, as the lock on the auto-increment global counter would be held for an extended period of time, causing other activity to wait. To be sure, there were reasons for this strategy having to do with the way MySQL replication works -- InnoDB needed to ensure that the replaying of statements on a slave server would be identical to the master server.
Heikki Tuuri and the Innobase/Oracle team spent considerable resources to develop a solution to the above scalability problem. The new solution involves a configurable setting which can be changed without a server restart in MySQL 5.1.22. The new server variable, innodb_autoinc_lock_mode controls how InnoDB treats statements which insert rows into an InnoDB table with an AUTO_INCREMENT column. Depending on your environment -- specifically, whether you are using the binlog for replication or recovery purposes and whether you are executing "batched insert" statements-- you can set this variable to 0, 1, or 2. 0 corresponds to the traditional mode, and is not recommended except for very specific scenarios (see the doc link above). 1 represents "consecutive mode" and is the default. In this mode, only statements where InnoDB cannot determine the number of rows to be inserted will use the global auto-increment lock. All other "simple insert" statements (even those inserting multiple records in batch mode) will use a faster, lighter locking mechanism, which results in significant scalability increases. The final setting, 2, represents an "interleaved" mode and has even greater scalability improvements, but cannot be used in scenarios where the binary log is being used for recovery or statement-based replication.
Again, this is a large and important feature chance in MySQL 5.1.22, and I encourage anyone using InnoDB tables in a production environment to read up on the new documentation.
--EOF--
Leave a comment