INNODB自增主键的一些问题 vs mysql获得自增字段下一个值

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

验证：

插入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)

和猜测的一样，自增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)

INNODB自增主键的一些问题 vs mysql获得自增字段下一个值

标签：mysql   int   configure   sel   limit   section   not   互斥量   好处