mysql auto

63次阅读

共计 3382 个字符，预计需要花费 9 分钟才能阅读完成。

这篇文章主要介绍“mysql auto_increment 锁带来的表锁举例分析”，在日常操作中，相信很多人在 mysql auto_increment 锁带来的表锁举例分析问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql auto_increment 锁带来的表锁举例分析”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

案例描述:
线上一张表有大概 2 亿条,50 个 G 左右大小的数据, 业务进行重新规划, 需要将绝大部分数据进行历史归档. 当时为了方便, 就新建一张相同结构的表, 然后快速的 rename 成线上表, 然后将备份表需要导入到线上表的数据进行 insert into select 操作. 结果, 线上表产生表锁, 业务全部堵住, 发现情况时, 已经没办法撤销 insert 操作 (因为已经插入很多了. 在回滚, 估计代价更高), 所以就只有坐等 insert 完毕了.
create table new_table like old_table; – 创建一个跟线上表结构一样的新表;
alter table new_table auto_increment=xxxx – 将新表的自增值设大一些, 目的是为了跟老表数据留下空间和区别;
rename table old_tale to old_table_bak;
renmae table new_table to online_table;
— 这两行一起执行, 减小切换表的时间, 尽量减小对线上数据的影响;
insert into online_table select * from old_table_bak where xxxxxxx; – 将历史表中需要的数据导入新表;

案例分析:
也算是自己麻痹大意了, 以为 innodb 的 insert 只会加行级锁, 没考虑到 auto_increment 的自增锁. 产生了表锁, 影响了整个业务.

下面分享下 auto_increment 自增锁的一些信息.
讲自增锁, 就讲一下 innodb_autoinc_lock_mode 参数:
在 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 等语句，推荐使用程序将这些语句分成多条语句，一一插入，减少单一时间的锁表时间。
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 mod，传统模式)。
这种方式就和 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 应该是够用了。