浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法
前提条件，percona5.6版本，事务隔离级别为RR
mysql>showcreatetabletest_autoinc_lock\G ***************************1.row*************************** Table:test_autoinc_lock CreateTable:CREATETABLE`test_autoinc_lock`( `id`int(11)NOTNULLAUTO_INCREMENT, `a`int(11)DEFAULTNULL, PRIMARYKEY(`id`), KEY`idx_a`(`a`) )ENGINE=InnoDBAUTO_INCREMENT=14DEFAULTCHARSET=utf8 1rowinset(0.00sec) mysql>select*fromtest_autoinc_lock; +----+------+ |id|a| +----+------+ |1|1| |12|2| |2|3| |3|5| |4|7| |5|7| |6|9| |7|10| +----+------+ 8rowsinset(0.00sec)
条件1innodb_autoinc_lock_mode设置为0
session1 begin;deletefromtest_autoinc_lockwherea>7;//这时未提交 session2 mysql>insertintotest_autoinc_lock(a)values(100);//gap锁的存在，这时处于锁等待 session3 mysql>insertintotest_autoinc_lock(a)values(2);//这时同样处于等待状态，理论上这个不是gap锁的锁定范围，那么它是在等什么呢 session4 mysql>select*frominformation_schema.innodb_trx\G ***************************1.row*************************** trx_id:2317 trx_state:LOCKWAIT trx_started:2016-10-3119:28:05 trx_requested_lock_id:2317:20 trx_wait_started:2016-10-3119:28:05 trx_weight:1 trx_mysql_thread_id:9 trx_query:insertintotest_autoinc_lock(a)values(2) trx_operation_state:settingauto-inclock trx_tables_in_use:1 trx_tables_locked:1 trx_lock_structs:1 trx_lock_memory_bytes:360 trx_rows_locked:0 trx_rows_modified:0 trx_concurrency_tickets:0 trx_isolation_level:REPEATABLEREAD trx_unique_checks:1 trx_foreign_key_checks:1 trx_last_foreign_key_error:NULL trx_adaptive_hash_latched:0 trx_adaptive_hash_timeout:10000 trx_is_read_only:0 trx_autocommit_non_locking:0
这时查看session3是等待自增锁，一直处于settingauto-inclock状态
session2
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
这时session3锁等待超时退出
session3
这时再看session3可以发现insert完成。
mysql>select*fromtest_autoinc_lock; +----+------+ |id|a| +----+------+ |1|1| |12|2| |13|2| |2|3| |3|5| |4|7| |5|7| |6|9| |7|10| +----+------+ 9rowsinset(0.00sec)//注意看这时的最大自增值是13，也就是之前自增最大值上+1，也就是说session2后来释放了预计生成的自增id，将13留给了session3，自增id值的申请完全是串行顺序的。
结论：innodb_autoinc_lock_mode为0时的，也就是官方说的traditional
级别，该自增锁是表锁级别，且必须等待当前SQL执行完成后或者回滚掉才会释放，这样在高并发的情况下可想而知自增锁竞争是比较大的。
条件2innodb_autoinc_lock_mode设置为1
session1 mysql>begin; QueryOK,0rowsaffected(0.00sec) mysql>deletefromtest_autoinc_lockwherea>7; QueryOK,2rowsaffected(0.00sec) mysql>select*fromtest_autoinc_lock; +----+------+ |id|a| +----+------+ |1|1| |12|2| |13|2| |2|3| |3|5| |4|7| |5|7| |6|9| |7|10| +----+------+ 9rowsinset(0.00sec)//注意看这时的最大自增值是13 session2 mysql>insertintotest_autoinc_lock(a)values(100);//同样gap锁的存在，这时处于锁等待 session3 mysql>insertintotest_autoinc_lock(a)values(5); QueryOK,1rowaffected(0.00sec) mysql>select*fromtest_autoinc_lock; +----+------+ |id|a| +----+------+ |1|1| |12|2| |13|2| |2|3| |3|5| |15|5| |4|7| |5|7| |6|9| |7|10| +----+------+ 10rowsinset(0.00sec)//session3直接完成了，并且注意观察插入的自增id值是15，也就是跳过了预计分配给session2的14，可以看到自增id值立马就分配给了session3，而不必等session2执行完成
结论：innodb_autoinc_lock_mode为1时的，也就是官方说的consecutive
级别，这时如果是单一的insertSQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其他事务中已经有session获取了自增锁）。另外当SQL是一些批量insertsql时，比如insertinto...select...,loaddata,replace..select..时，这时还是表级锁，可以理解成退化为必须等待当前SQL执行完才释放。
可以认为，该值为1时是相对比较轻量的锁，也不会对复制产生影响，唯一的缺陷是产生的自增值不一定是完全连续的（不过个人认为这个往往不是很重要，也没必要根据自增id值来统计行数之类）
条件3innodb_autoinc_lock_mode设置为2
先说结论：当innodb_autoinc_lock_mode设置为2时，所有insert种类的SQL都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当binlog_format为statement时，这时的复制没法保证安全，因为批量的insert，比如insert..select..语句在这个情况下，也可以立马获取到一大批的自增id值，不必锁整个表，slave在回放这个sql时必然会产生错乱。我们做个测试验证复制不是安全的。
mastersession1 mysql>showvariableslike'%binlog_for%'; +---------------+-----------+ |Variable_name|Value| +---------------+-----------+ |binlog_format|STATEMENT| +---------------+-----------+ 1rowinset(0.00sec) mysql>insertintotest_autoinc_lock(a)select*fromtest_auto; QueryOK,8388608rowsaffected,1warning(29.85sec) Records:8388608Duplicates:0Warnings:1 mastersession2（注意session2在session1执行完成之前执行） mysql>insertintotest_autoinc_lock(a)values(2); QueryOK,1rowaffected(0.01sec) mysql>select*fromtest_autoinc_lockwherea=2; +---------+------+ |id|a| +---------+------+ |1376236|2| +---------+------+ 1rowinset(0.00sec) slavesession1（这时可看到1376236主键冲突） mysql>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:10.9.73.139 Master_User:ucloudbackup Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000006 Read_Master_Log_Pos:75823243 Relay_Log_File:mysql-relay.000002 Relay_Log_Pos:541 Relay_Master_Log_File:mysql-bin.000006 Slave_IO_Running:Yes Slave_SQL_Running:No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno:1062 Last_Error:Error'Duplicateentry'1376236'forkey'PRIMARY''onquery.Defaultdatabase:'test'.Query:'insertintotest_autoinc_lock(a)select*fromtest_auto' Skip_Counter:0 Exec_Master_Log_Pos:75822971
我们这时解析下主库的binlog不难发现问题原因，第一条批量insert还没执行完时，第二条简单insert执行时获得了自增id值为1376236的锁，这时在主库写入是没有问题的，但是反应到从库时，因为是基于statement的复制，必然出现主键冲突。
SETINSERT_ID=1376236/*!*/; #16103121:44:31serverid168380811end_log_pos75822940CRC320x65797f1cQuerythread_id=20exec_time=0error_code=0 use`test`/*!*/; SETTIMESTAMP=1477921471/*!*/; insertintotest_autoinc_lock(a)values(2) /*!*/; #at75822940 #16103121:44:31serverid168380811end_log_pos75822971CRC320xbb91449dXid=274 COMMIT/*!*/; #at75822971 #16103121:44:26serverid168380811end_log_pos75823050CRC320xa297b57bQuerythread_id=57exec_time=30error_code=0 SETTIMESTAMP=1477921466/*!*/; BEGIN /*!*/; #at75823050 #at75823082 #16103121:44:26serverid168380811end_log_pos75823082CRC320xa5aa31a1Intvar SETINSERT_ID=1/*!*/; #16103121:44:26serverid168380811end_log_pos75823212CRC320x470282baQuerythread_id=57exec_time=30error_code=0 SETTIMESTAMP=1477921466/*!*/; insertintotest_autoinc_lock(a)select*fromtest_auto
总结：
1innodbrow复制时，可将innodb_autoinc_lock_mode设置为2，这时可在所有insert情况下表获得最大并发度
2innodbstatement复制时，可将innodb_autoinc_lock_mode设置为1，保证复制安全的同时，获得简单insert语句的最大并发度
3myisam引擎情况下，无论什么样自增id锁都是表级锁，设置innodb_autoinc_lock_mode参数无效（测试略）
4实际上提问者说到的在innodb引擎下自增id值作为主键的情况下，相比uuid或者自定义的主键，是可以提到插入速度的，因为innodb是主键聚集索引，实际的主键值必须按照主键顺序存取，那么自增id本身就是升序的，那么在插入数据时，底层就不必再做额外的排序操作，也减少了索引页分裂的次数，从而大大增加insert速度（除非其他方案也能保证主键完全自增）
以上这篇浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。