Innodb与Myisam存储引擎对auto_crement的限制：
Innodb中自增长的列必须是索引，同时必须是索引的第一个列，否则会报如下错误：there can be only one column and it must be defined as a key；MyISAM没有这个限制。
MyISAM采用表锁设计，自增长不用考虑并发问题；
auto_increment在Innodb工作方式：
含有auto_increment列的表在Innodb数字字典中包含一个自增长的计数器，仅存在内存而不是磁盘上；
初始化auto_increment的值：
server 启动后，对表进行插入的时候，innodb获取互斥锁等价于：SELECT MAX(ai_col) FROM T FOR UPDATE;语句取回的值逐次加一，并被赋给列和自动增长计数器。自动增长计数器被初始化之后，如果插入一个明确指定的列值，而且该值大于当前计数器值，则计数器被设置为指定列值。如果没有明确指定一个值，InnoDB给计数器增加一，并且赋新值给该列。
自增长不连续的原因：
当使用表锁的方式进行auto_increment 值的插入时，为了提供插入性能，该锁是在完成自增列的插入后释放，并不是在事务完成后才释放。所以自增列不连续一般出现了事务回滚操作。
当使用互斥量的方式插入值的时候，由于并发的存在可能会造成值的不连续性；
自增长与锁：
在5.1.22版本之前，采用一种特殊的表锁机制- auto-inc Locking，锁不是在一个事务完成后释放，而是在完成对自增长值插入的SQL语句之后立即释放；这个只是在一定程度上提高了并发插入效率，但对于insert...select的大数据量的插入会影响插入的性能；
在5.1.22之后的版本，innodb采用轻量级互斥量的自增长，对语句插入的行数，innodb提前分配好自增的值该为多少，后面的语句无需等待前面未执行完的语句即可执行（因为对插入的行数已知，自增的值已提前通过互斥量分配好）；
举例：某一insert语句1执行前，表的AUTO_INCREMENT=1，语句1的插入行数已知为3，innodb在语句1的实际插入操作执行前就预分配给该语句三个自增值，当有一个新的insert语句2要执行时，读取的AUTO_INCREMENT=4，这样虽然语句1可能还没有执行完，语句2就可直接执行无需等待语句2。
注意：对于语句中如果明确设定一个自增值，同时插入多个值，并利用其自增的特性，可能会造成其主键冲突；举例：如果当前的自增值为4，插入下面的语句将会造成主键冲突：INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 因为所有自增值的增长都是根据前一个语句的最大自增值来确定的；
锁设计的参数：innodb_autoinc_lock_mode 参数值 0 1(默认值) 2
0:表示使用auto-inco 表锁机制（不推荐）,优点是在不发生回滚的情况下，能保证自增值是连续的，但并发效率很慢；
1：使用互斥量（mutex）对内存中的计数器进行累加，对于 “bulk insert”来说还是使用表锁机制，如果使用auto-inc 产生自增长的值，此时再进行“simple inserts”操作时，仍旧使用 auto-inc的方式产生自增值；优点：就算binlog复制是在sbr的方式下，那也能保证数据的连续性；
（“bulk insert” 是指在插入前不确定要插入的行数的语句，比如 insert...select ;replace...select;load data）
（“simple insert”是指插入前就能确定插入行数的语句；比如insert ;replace;不包含：insert... on dupliate key update...这样的语句）
2：对于所有的语句都使用互斥量获得自增长值；优点：并发效率高；缺点：可能会造成自增值的不连续性；最好是基于RBR模式进行复制，保持主从数据的一致性；
本文转自 位鹏飞 51CTO博客，原文链接：http://blog.51cto.com/weipengfei/1299792，如需转载请自行联系原作者
