Mysql中INNODB自增主键的问题有哪些

29次阅读

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

丸趣 TV 小编给大家分享一下 Mysql 中 INNODB 自增主键的问题有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

背景：

自增长是一个很常见的数据属性，在 MySQL 中大家都很愿意让自增长属性的字段当一个主键。特别是 InnoDB，因为 InnoDB 的聚集索引的特性，使用自增长属性的字段当主键性能更好，这里要说明下自增主键需要注意的几个事项。

问题一：表锁

在 MySQL5.1.22 之前，InnoDB 自增值是通过其本身的自增长计数器来获取值，该实现方式是通过表锁机制来完成的（AUTO-INC LOCKING）。锁不是在每次事务完成后释放，而是在完成对自增长值插入的 SQL 语句后释放，要等待其释放才能进行后续操作。比如说当表里有一个 auto_increment 字段的时候，innoDB 会在内存里保存一个计数器用来记录 auto_increment 的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果大量的并发插入，表锁会引起 SQL 堵塞。insert into…..select 大量插入数据的性能也比较差

在 5.1.22 之后，InnoDB 为了解决自增主键锁表的问题，引入了参数 innodb_autoinc_lock_mode，该实现方式是通过轻量级互斥量的增长机制完成的。它是专门用来在使用 auto_increment 的情况下调整锁策略的，目前有三种选择：

插入类型说明：

INSERT-LIKE：指所有的插入语句，比如  INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA 等
Simple inserts：指在插入前就能确定插入行数的语句，包括 INSERT、REPLACE，不包含 INSERT…ON DUPLICATE KEY UPDATE 这类语句。Bulk inserts：指在插入前不能确定得到插入行的语句。如 INSERT…SELECT,REPLACE…SELECT,LOAD DATA.
Mixed-mode inserts: 指其中一部分是自增长的，有一部分是确定的。

0：通过表锁的方式进行，也就是所有类型的 insert 都用 AUTO-inc locking。

1：默认值，对于 simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于 bulk insert 则还是使用表锁的方式进行。

2：对所有的 insert-like 自增长值的产生使用互斥量机制完成，性能最高，并发插入可能导致自增值不连续，可能会导致 Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication 的模式。

在 mysql5.1.22 之前，mysql 的 INSERT-LIKE 语句会在执行整个语句的过程中使用一个 AUTO-INC 锁将表锁住，直到整个语句结束（而不是事务结束）。因此在使用 INSERT…SELECT、INSERT…values(…),values(…)时，LOAD DATA 等耗费时间较长的操作时，会将整个表锁住，而阻塞其他的 insert-like,update 等语句。推荐使用程序将这些语句分成多条语句，一一插入，减少单一时间的锁表时间。

解决：

通过参数 innodb_autoinc_lock_mode =1/ 2 解决，并用 simple inserts 模式插入。

问题二：自增主键不连续

5.1.22 后默认：innodb_autoinc_lock_mode = 1
直接通过分析语句，获得要插入的数量，然后一次性分配足够的 auto_increment id，只会将整个分配的过程锁住。

root@localhost : test 04:23:28 show variables like  innodb_autoinc_lock_mode  +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 1 | +--------------------------+-------+ 1 row in set (0.00 sec)
root@localhost : test 04:23:31 create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.16 sec)
root@localhost : test 04:23:35 insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0 root@localhost : test 04:23:39 show create table tmp_auto_inc\G; *************************** 1. row *************************** Table: tmp_auto_inc Create Table: CREATE TABLE `tmp_auto_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `talkid` int(11) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk 1 row in set (0.00 sec)

插入 10 条记录，但表的 AUTO_INCREMENT=16，再插入一条的时候，表的自增 id 已经是不连续了。

原因：

参数 innodb_autoinc_lock_mode = 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。

所以会发现：插入只有 1 行时，你看不到这个现象，并不预申请。而当有 N 1 行时，则需要。多申请的数目为 N -1，因此执行后的自增值为：1+N+(N-1)。测试中为 10 行，则：1+10+9 =20，和 16 不一致？原因是：当插入 8 行的时候，表的 AUTO_INCREMENT 已经是 16 了，所以插入 10 行时，id 已经在第 8 行时预留了，所以直接使用，自增值仍为 16。所以当插入 8 行的时候，多申请了 7 个 id，即：9，10，11，12，13，14，15。按照例子中的方法插入 8~15 行，表的 AUTO_INCREMENT 始终是 16
为了发现规律，这儿我做了实验，不是很准确，插入行数与对应的 autocommit 分别是 2》4 3》4 4-7》8 8-15》16 16-31》32），只能说 AUTO_INCREMENT 有可能是 2n(具体什么时候是 2n 还没发现规律)，范围应该是 [n,2n] 之间

验证：

插入 16 行：猜测预申请的 id：1+16+（16-1）= 32，即：AUTO_INCREMENT=32

root@localhost : test 04:55:45 create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.17 sec)
root@localhost : test 04:55:48 insert into tmp_auto_inc(talkid) select talkId from sns_talk_dialog limit 16;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0 root@localhost :

test 04:55:50 show create table tmp_auto_inc\G;

*************************** 1. row ***************************

Table: tmp_auto_inc Create Table: CREATE TABLE `tmp_auto_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `talkid` int(11) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=gbk 1 row in set (0.00 sec) --- 第一次插入所以为 2 *16

和猜测的一样，自增 id 到了 32。所以当插入 16 行的时候，多申请了 17,18,19…,31。

所以导致 ID 不连续的原因是因为 innodb_autoinc_lock_mode = 1 时，会多申请 id。好处是：一次性分配足够的 auto_increment id，只会将整个分配的过程锁住。

5.1.22 前默认：innodb_autoinc_lock_mode = 0

root@localhost : test 04:25:12 show variables like  innodb_autoinc_lock_mode

+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 0 | +--------------------------+-------+

1 row in set (0.00 sec)
root@localhost : test 04:25:15 create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;
Query OK, 0 rows affected (0.17 sec)
root@localhost : test 04:25:17 insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

root@localhost : test 04:25:21 show create table tmp_auto_inc\G;

*************************** 1. row ***************************

Table: tmp_auto_inc Create Table: CREATE TABLE `tmp_auto_inc` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `talkid` int(11) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk 1 row in set (0.00 sec)

插入 10 条记录，但表的 AUTO_INCREMENT=11，再插入一条的时候，表的自增 id 还是连续的。

innodb_autoinc_lock_mode = 2 和 innodb_autoinc_lock_mode = 1 的测试情况一样。但该模式下是来一个分配一个，而不会锁表，只会锁住分配 id 的过程，和 1 的区别在于，不会预分配多个，这种方式并发性最高。但是在 replication 中当 binlog_format 为 statement-based 时存在问题

解决：

尽量让主键 ID 没有业务意义，或则使用 simple inserts 模式插入。

结论：

当 innodb_autoinc_lock_mode 为 0 时候，自增 id 都会连续，但是会出现表锁的情况，解决该问题可以把 innodb_autoinc_lock_mode 设置为 1，甚至是 2。会提高性能，但是会在一定的条件下导致自增 id 不连续。

以上是“Mysql 中 INNODB 自增主键的问题有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

正文完