在MySQL5.0版本，只要使用了auto_increment字段，那么innodb的表锁问题就无法回避，如果你必须面对大量并发插入的情况，要么听天由命，要么便只能抛弃auto_increment，转而使用自定义主键的方式，但必须小心选择，否则可能会陷入到另一个陷阱当中，比如说，有的人会通过使用UUID主键的方式来回避auto_increment表锁的问题，但是这可能会让应用陷入更严重的IO瓶颈问题之中去，原因可以参考我以前写的 文章：
还有一个方法就是使用MySQL5.1，在这个版本里，出现了一个新的配置选项： innodb_autoinc_lock_mode，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：
innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
不过MySQL还没有原生的uuid支持，在和innodb表类型配合时，可能会出现一些问题：
首先，innodb会对主键进行物理排序，这对auto_increment_int是个好消息，因为后一次插入的主键位置总是在最后。但是对uuid来说，这却是个坏消息，因为uuid是杂乱无章的，每次插入的主键位置是不确定的，可能在开头，也可能在中间，在进行主键物理排序的时候， 势必会造成大量的IO操作影响效率。
幸运的是， CakePHP的uuid算法最开始那部分的字符串是基于时间戳的，所以单就CakePHP的uuid而言，不存在这个问题，如果是其他的uuid算法，这个问题一定要仔细考虑。
其次，因为其他的索引要和主键关联，当主键是uuid时，和int相比必然会占用更大的空间，在较大的空间上检索肯定比在较小的空间上检索耗时。
这个问题解决起来办法不多，比较常见的方式是主键仍然用auto_increment_int来做，而另加一个uuid做唯一索引，表外键关联什么的，还用uuid来做，也就是说auto_increment_int只是一个形式上的主键，而uuid才是事实上的主键，这样，一方面int主键不会浪费太多空间，另一方面，还可以继续使用uuid。
还有一个问题是在MySQL里使用uuid，一般是用char(36)来声明字段，如果列编码是gbk/utf8这样的复杂的编码，会拖累主键的效率，这时候，我们那字段编码转换成ascii/latin1这样的简单编码会好一些。
################################################
官方文档已经给出了很好的描述，就不多说了。需要提醒的是MySQL5.1现在还没有Stable，要谨慎使用。
innodb_autoinc_lock_mode 下自增id不连续的原因
一、问题复现
文件/tmp/data.sql中两列，每列一个数字1;
输入
|
CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; load data infile '/tmp/data.sql' into table t(k); show create table t; |
结果：
|
CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
二、原因分析
我们知道在5.1.22在之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode。这个值为0时，每次申请自增主键时需要锁表。
这个参数的默认值是1，设为此值时，每次会“预申请”多余的id(handler.cc: compute_next_insert_id)，而insert执行完成后，会特别将这些预留的id空出，动作就是特意将预申请后的当前最大id回写到表中(dict0dict.c:dict_table_autoinc_update_if_greater)。
三、简单计算预留
注意这个预留的策略是“不够时多申请几个”， 实际执行中是分步申请。至于申请几个，是由当时“已经插入了几条数据N”决定的。当auto_increment_offset＝1时，预申请的个数是 N-1。
所以会发现，当data.sql中只有一行时，你看不到这个现象，并不预申请。
而当有两行时（如文章开头的例子），则需要。多申请的数目为1，因此执行后的自增值为4 (1+2+1)。
若data.sql中有三行呢？由于执行第三行的id已经在执行第二行时预留了，所以直接使用，结果的自增值仍为4。
后续的就类推了，可自行分析下。
实际insert行 |
自增id增加值 |
2、3 |
3 |
4、5、6、7 |
7 |
8～15 |
15 |
################################################################
########################################
在mysql5.1.22之前，mysql的“INSERT-like”语句（包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住，直到整个语句结束（而不是事务结束）。因此在使用INSERT…SELECT、INSERT…values(…),values(…)时，LOAD DATA等耗费时间较长的操作时，会将整个表锁住，而阻塞其他的“INSERT-like”、Update等语句，推荐使用程序将这些语句分成多条语句，一一插入，减少单一时间的锁表时间，荣嬷嬷的superdump就不错。
mysql5.1.22之后mysql进行了改进，引入了参数 innodb_autoinc_lock_mode，通过这个参数控制mysql的锁表逻辑，在介绍这个之前先引入几个术语，方便说明 innodb_autoinc_lock_mode。
1.“INSERT-like”：
INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()
2.“Simple inserts”
就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()
3.“Bulk inserts”
就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA
4.“Mixed-mode inserts”
下面两种，不确定是否需要分配auto_increment id
INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
INSERT … ON DUPLICATE KEY UPDATE
一、innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
这种方式就和mysql5.1.22以前一样，为了向后兼容而保留了这种模式，如同前面介绍的一样，这种方式的特点就是“表级锁定”，并发性较差
二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
这种方式是新版本中的默认方式，推荐使用，并发性相对较高，特点是“consecutive”，即保证同一条insert语句中新插入的auto_increment id都是连续的。
这种模式下：
“Simple inserts”：直接通过分析语句，获得要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。
“Bulk inserts”：因为不能确定插入的数量，因此使用和以前的模式相同的表级锁定。
“Mixed-mode inserts”：直接分析语句，获得最坏情况下需要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。需要注意的是，这种方式下，会分配过多的id，而导致”浪费“。比如INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');会一次性的分配5个id，而不管用户是否指定了部分id；INSERT … ON DUPLICATE KEY UPDATE一次性分配，而不管将来插入过程中是否会因为duplicate key而仅仅执行update操作。
注意：当master mysql版本<5.1.22，slave mysql版本>=5.1.22时，slave需要将innodb_autoinc_lock_mode设置为0，因为默认的innodb_autoinc_lock_mode为1，对于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的执行结果不同，现实环境一般会使用INSERT … ON DUPLICATE KEY UPDATE。
三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
这种模式是来一个分配一个，而不会锁表，只会锁住分配id的过程，和innodb_autoinc_lock_mode = 1的区别在于，不会预分配多个，这种方式并发性最高。但是在replication中当binlog_format为statement-based时（简称SBR statement-based replication）存在问题，因为是来一个分配一个，这样当并发执行时，“Bulk inserts”在分配的时会同时向其他的INSERT分配，会出现主从不一致（从库执行结果和主库执行结果不一样），因为binlog只会记录开始的insert id。
测试SBR，执行begin;insert values(),();insert values(),();commit;会在binlog中每条insert values(),();前增加SET INSERT_ID=18/*!*/;。
但是row-based replication RBR时不会存在问题。
另外RBR的主要缺点是日志数量在包括语句中包含大量的update delete（update多条语句，delete多条语句）时，日志会比SBR大很多；假如实际语句中这样语句不是很多的时候（现实中存在很多这样的情况），推荐使用RBR配合innodb_autoinc_lock_mode，不过话说回来，现实生产中“Bulk inserts”本来就很少，因此innodb_autoinc_lock_mode = 1应该是够用了。
下面是官方文档举得几个例子，这里就不翻译的
For example, assume c1 is an AUTO_INCREMENT column of table t1, and that the most recent automatically generated sequence number is 100. Consider the following “mixed-mode insert” statement:
INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
With innodb_autoinc_lock_mode set to 0 (“traditional”), the four new rows will be:
+—–+——+
| c1 | c2 |
+—–+——+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+—–+——+
The next available auto-increment value will be 103 because the auto-increment values are allocated one at a time, not all at once at the beginning of statement execution. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).
With innodb_autoinc_lock_mode set to 1 (“consecutive”), the four new rows will also be:
+—–+——+
| c1 | c2 |
+—–+——+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+—–+——+
However, in this case, the next available auto-increment value will be 105, not 103 because four auto-increment values are allocated at the time the statement is processed, but only two are used. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).
With innodb_autoinc_lock_mode set to mode 2 (“interleaved”), the four new rows will be:
+—–+——+
| c1 | c2 |
+—–+——+
| 1 | a |
| x | b |
| 5 | c |
| y | d |
+—–+——+
The values of x and y will be unique and larger than any previously generated rows. However, the specific values of x and y will depend on the number of auto-increment values generated by concurrently executing statements.
Finally, consider the following statement, issued when the most-recently generated sequence number was the value 4:
INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
With any innodb_autoinc_lock_mode setting, this statement will generate a duplicate-key error 23000 (Can’t write; duplicate key in table) because 5 will be allocated for the row (NULL, ‘b’) and insertion of the row (5, ‘c’) will fail.