下面小编就为大家带来一篇浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
前提条件,percona 5.6版本,事务隔离级别为RR
mysql> show create table test_autoinc_lock\G *************************** 1. row *************************** Table: test_autoinc_lock Create Table: CREATE TABLE `test_autoinc_lock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 8 rows in set (0.00 sec)
条件1 innodb_autoinc_lock_mode设置为0
session1 begin;delete from test_autoinc_lock where a>7;//这时未提交 session2 mysql> insert into test_autoinc_lock(a) values(100);//gap锁的存在,这时处于锁等待 session3 mysql> insert into test_autoinc_lock(a) values(2);//这时同样处于等待状态,理论上这个不是gap锁的锁定范围,那么它是在等什么呢 session4 mysql> select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 2317 trx_state: LOCK WAIT trx_started: 2016-10-31 19:28:05 trx_requested_lock_id: 2317:20 trx_wait_started: 2016-10-31 19:28:05 trx_weight: 1 trx_mysql_thread_id: 9 trx_query: insert into test_autoinc_lock(a) values(2) trx_operation_state: setting auto-inc lock 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: REPEATABLE READ 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是等待自增锁,一直处于setting auto-inc lock状态
session2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这时session3锁等待超时退出
session3
这时再看session3可以发现insert完成。
mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 9 rows in set (0.00 sec)//注意看这时的最大自增值是13,也就是之前自增最大值上+1,也就是说session2后来释放了预计生成的自增id,将13留给了session3,自增id值的申请完全是串行顺序的。
结论:innodb_autoinc_lock_mode为0时的,也就是官方说的traditional
级别,该自增锁是表锁级别,且必须等待当前SQL执行完成后或者回滚掉才会释放,这样在高并发的情况下可想而知自增锁竞争是比较大的。
条件2 innodb_autoinc_lock_mode设置为1
session1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test_autoinc_lock where a>7; Query OK, 2 rows affected (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 9 rows in set (0.00 sec)//注意看这时的最大自增值是13 session2 mysql> insert into test_autoinc_lock(a) values(100);//同样gap锁的存在,这时处于锁等待 session3 mysql> insert into test_autoinc_lock(a) values(5); Query OK, 1 row affected (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 15 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 10 rows in set (0.00 sec)//session3直接完成了,并且注意观察插入的自增id值是15,也就是跳过了预计分配给session2的14,可以看到自增id值立马就分配给了session3,而不必等session2执行完成
结论:innodb_autoinc_lock_mode为1时的,也就是官方说的consecutive
级别,这时如果是单一的insert SQL,可以立即获得该锁,并立即释放,而不必等待当前SQL执行完成(除非在其他事务中已经有session获取了自增锁)。另外当SQL是一些批量insert sql时,比如insert into ...select ...,load data,replace ..select..时,这时还是表级锁,可以理解成退化为必须等待当前SQL执行完才释放。
可以认为,该值为1时是相对比较轻量的锁,也不会对复制产生影响,唯一的缺陷是产生的自增值不一定是完全连续的(不过个人认为这个往往不是很重要,也没必要根据自增id值来统计行数之类)
条件3 innodb_autoinc_lock_mode设置为2
先说结论:当innodb_autoinc_lock_mode设置为2时,所有insert种类的SQL都可以立马获得锁并释放,这时的效率最高。但是会引入一个新的问题:当binlog_format为statement时,这时的复制没法保证安全,因为批量的insert,比如insert ..select..语句在这个情况下,也可以立马获取到一大批的自增id值,不必锁整个表,slave在回放这个sql时必然会产生错乱。我们做个测试验证复制不是安全的。
master session1 mysql> show variables like '%binlog_for%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.00 sec) mysql> insert into test_autoinc_lock(a) select * from test_auto; Query OK, 8388608 rows affected, 1 warning (29.85 sec) Records: 8388608 Duplicates: 0 Warnings: 1 master session2(注意session2在session1执行完成之前执行) mysql> insert into test_autoinc_lock(a) values(2); Query OK, 1 row affected (0.01 sec) mysql> select * from test_autoinc_lock where a=2; +---------+------+ | id | a | +---------+------+ | 1376236 | 2 | +---------+------+ 1 row in set (0.00 sec) slave session1(这时可看到1376236主键冲突) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event 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 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto' Skip_Counter: 0 Exec_Master_Log_Pos: 75822971
我们这时解析下主库的binlog不难发现问题原因,第一条批量insert还没执行完时,第二条简单insert执行时获得了自增id值为1376236的锁,这时在主库写入是没有问题的,但是反应到从库时,因为是基于statement的复制,必然出现主键冲突。
SET INSERT_ID=1376236/*!*/; #161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c Query thread_id=20 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1477921471/*!*/; insert into test_autoinc_lock(a) values(2) /*!*/; # at 75822940 #161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d Xid = 274 COMMIT/*!*/; # at 75822971 #161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b Query thread_id=57 exec_time=30 error_code=0 SET TIMESTAMP=1477921466/*!*/; BEGIN /*!*/; # at 75823050 # at 75823082 #161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1 Intvar SET INSERT_ID=1/*!*/; #161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba Query thread_id=57 exec_time=30 error_code=0 SET TIMESTAMP=1477921466/*!*/; insert into test_autoinc_lock(a) select * from test_auto
总结:
1 innodb row复制时,可将innodb_autoinc_lock_mode设置为2,这时可在所有insert情况下表获得最大并发度
2 innodb statement复制时,可将innodb_autoinc_lock_mode设置为1,保证复制安全的同时,获得简单insert语句的最大并发度
3 myisam引擎情况下,无论什么样自增id锁都是表级锁,设置innodb_autoinc_lock_mode参数无效(测试略)
4 实际上提问者说到的在innodb引擎下自增id值作为主键的情况下,相比uuid或者自定义的主键,是可以提到插入速度的,因为innodb是主键聚集索引,实际的主键值必须按照主键顺序存取,那么自增id本身就是升序的,那么在插入数据时,底层就不必再做额外的排序操作,也减少了索引页分裂的次数,从而大大增加insert速度(除非其他方案也能保证主键完全自增)
【相关推荐】
1. Mysql免费视频教程
以上是实例详解mysql中innodb_autoinc_lock_mode的详细内容。更多信息请关注PHP中文网其他相关文章!

MySQL和SQLite的主要区别在于设计理念和使用场景:1.MySQL适用于大型应用和企业级解决方案,支持高性能和高并发;2.SQLite适合移动应用和桌面软件,轻量级且易于嵌入。

MySQL中的索引是数据库表中一列或多列的有序结构,用于加速数据检索。1)索引通过减少扫描数据量提升查询速度。2)B-Tree索引利用平衡树结构,适合范围查询和排序。3)创建索引使用CREATEINDEX语句,如CREATEINDEXidx_customer_idONorders(customer_id)。4)复合索引可优化多列查询,如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。5)使用EXPLAIN分析查询计划,避

在MySQL中使用事务可以确保数据一致性。1)通过STARTTRANSACTION开始事务,执行SQL操作后用COMMIT提交或ROLLBACK回滚。2)使用SAVEPOINT可以设置保存点,允许部分回滚。3)性能优化建议包括缩短事务时间、避免大规模查询和合理使用隔离级别。

选择PostgreSQL而非MySQL的场景包括:1)需要复杂查询和高级SQL功能,2)要求严格的数据完整性和ACID遵从性,3)需要高级空间功能,4)处理大数据集时需要高性能。PostgreSQL在这些方面表现出色,适合需要复杂数据处理和高数据完整性的项目。

MySQL数据库的安全可以通过以下措施实现:1.用户权限管理:通过CREATEUSER和GRANT命令严格控制访问权限。2.加密传输:配置SSL/TLS确保数据传输安全。3.数据库备份和恢复:使用mysqldump或mysqlpump定期备份数据。4.高级安全策略:使用防火墙限制访问,并启用审计日志记录操作。5.性能优化与最佳实践:通过索引和查询优化以及定期维护兼顾安全和性能。

如何有效监控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异,选择时需考虑项目规模和未来扩展性。

在需要高可用性、高级安全性和良好集成性的企业级应用场景下,应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能,如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色,支持内存优化表和列存储索引。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

VSCode Windows 64位 下载
微软推出的免费、功能强大的一款IDE编辑器

Atom编辑器mac版下载
最流行的的开源编辑器

SublimeText3汉化版
中文版,非常好用

Dreamweaver Mac版
视觉化网页开发工具

禅工作室 13.0.1
功能强大的PHP集成开发环境