innodb中的auto_increment

15.7.3. How an AUTO_INCREMENT Column Works in
InnoDB
If you specify an AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary
contains a special counter called the auto-increment counter that is used in assigning new
values for the column. The auto-increment counter is stored only in main memory, not on disk.
InnoDB uses the following algorithm to initialize the auto-increment counter for a table T that contains
an AUTO_INCREMENT column named ai_col: After a server startup, when a user first does
an insert to a table T, InnoDB executes the equivalent of this statement:
SELECT MAX(ai_col) FROM T FOR UPDATE;
The value retrieved by the statement is incremented by one and assigned to the column and the autoincrement
counter of the table. If the table is empty, the value 1 is assigned. If the auto-increment
counter is not initialized and the user invokes a SHOW TABLE STATUS statement that displays
output for the table T, the counter is initialized (but not incremented) and stored for use by later inserts.
Note that in this initialization we do a normal exclusive-locking read on the table and the lock
lasts to the end of the transaction.
InnoDB follows the same procedure for initializing the auto-increment counter for a freshly created
table.
Note that if the user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB
treats the row as if the value had not been specified and generates a new value for it.
After the auto-increment counter has been initialized, if a user inserts a row that explicitly specifies
the column value, and the value is bigger than the current counter value, the counter is set to the specified
column value. If the user does not explicitly specify a value, InnoDB increments the counter
by one and assigns the new value to the column.
When accessing the auto-increment counter, InnoDB uses a special table level AUTO-INC lock
that it keeps to the end of the current SQL statement, not to the end of the transaction. The special
lock release strategy was introduced to improve concurrency for inserts into a table containing an
AUTO_INCREMENT column. Two transactions cannot have the AUTO-INC lock on the same table
simultaneously.
Note that you may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if
you roll back transactions that have gotten numbers from the counter.
The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to
the column or if the value becomes bigger than the maximum integer that can be stored in the specified
integer type.
Beginning with MySQL 4.1.12 and MySQL 5.0.3, InnoDB supports the AUTO_INCREMENT = n
table option in CREATE TABLE and ALTER TABLE statements, to set the initial counter value or
alter the current counter value. The effect of this option is canceled by a server restart, for reasons
discussed earlier in this section.

 

 

其实相当于oracle中的sequence,而Oracle中的sequence可以cache,circle

并且无并发insert时在sequence上的锁争用问题; 而mysql中的auto_increment不具备这些好处

 

总结一下,手册中讲了几个关键点:

1, auto_increment列如何赋值

 

   给auto_increment列赋值是由一个计数器对像控制的,针对每个表的auto_increment列,都有一个单独的计数器(counter),它常驻内存,当mysqld退出时,这个对像也会退出;mysqld启动后,这个对像load到内存

 

2, auto_increment计数器初始化

  mysqld启动后,第一次向表中插入数据时;或者创建好空表后立即调用show table status语句查看表状态信息时,会触发mysql对counter进行初始化

  区别在于:

  (1)mysqld启动后,第一次insert时,按SELECT MAX(ai_col) FROM T FOR UPDATE;算法给得到的值+1,赋值给计数器及列值

    如果是向空表插入数据,则初始化为1;

  (2)创建好空表后立即调用show table status语句,这个时候初始化成1,不会赋值!

 

  其实很容易理解这两个触发时机: 由于counter作为一个对像需要驻留内存,当mysqld启动后,counter应该已经在内存中了,但只有当insert时才需要通过它get一个新值来赋予auto_increment列,所以这时需要'初始化'它;

  另外,当一个新表创建后,用show table status查看表状况时,是需要给出auto_increment属性的,这是mysql设计造成的(它也可以不用展示这个信息,这时就不需要初始化了 :-)

 

3, insert与auto_increment有关的几种特殊情况

  指定null/0时,Innodb认为没有指定,会产生一个新值

  指定为大于计数器当前值,计数器被设为指定值,并且赋值给列

  不指定任何值,Innodb给计数器加1后,赋值给列,行为同'指定null/0'

  指定负数时,行为同'不指定任何值'

  指定的值超出了字段类型的范围时,计数器被设为字段类型最大允许值,如果多次指定,则会报key duplicate错误

 

  最后一种情况启示我们:在设计时应该考虑好序列字段的数据类型,比如,tinyint unsingned最大为255

  如果表的记录会超出255条,就不能用tinyint类型

 

4, 关于auto_increment序列'间隙'

  由于mysql是在语句级获得auto increment计数器对像上的一种表锁(table lock),即AUTO-INC

  任何insert语句执行完毕,都会使counter发生变化,但并不意味着事务成功完成

  所以,当rollback掉一个事务时,事务中已经获得分配的序列将不能再次使用

  rollback之后的第一个insert会使用计数器当前值+1, 见第1,2点

 

5, AUTO_INC

  虽然AUTO_INC是在语句级,即insert完毕,计数器对像上的这个特殊表锁马上释放;但mysql的实现是

  两个事务不能同时占用计数器上的AUTO_INC锁, 这意味着在有auto_increment列的表上大量并发insert(不同的事务)会很容易引起AUTO_INC锁争用, 严重影响并发性能;

  如果有这种需要,最好不要采用auto_increment,改成用一个序列表来实现.

 

 

 

 

附测试记录

 

--创建一个空表,counter未初始化

CREATE TABLE `test` (
  `Fid` int(10) unsigned NOT NULL auto_increment,
  `Ftime` datetime NOT NULL default '0000-00-00 00:00:00',
  `Ftype` int(10) unsigned NOT NULL default '0',
  `Fdesc` varchar(255) default NULL,
  primary key `i_fid` (`Fid`)
) ENGINE=innodb DEFAULT CHARSET=latin1;

 

--空表show table status,counter初始化为1,列未被赋值; 独占读锁,事务有效;

mysql> show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1   ==>需要显示表上auto_increment信息,故需初始化

    Create_time: 2008-04-23 19:19:51
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 32201728 kB
1 row in set (0.00 sec)

 

--表原来为空,insert中定义为null==>初始化counter及id列值为1

mysql> insert into test values(null,now(),1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
|   1 | 2008-04-23 19:31:00 |     1 | a     |
+-----+---------------------+-------+-------+

 

--指定为0,自增为1到2

mysql> insert into test values(0,now(),2,'b');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
|   1 | 2008-04-23 19:32:22 |     1 | a     |
|   2 | 2008-04-23 19:32:41 |     2 | b     |
+-----+---------------------+-------+-------+
2 rows in set (0.00 sec)

 

--不指定值时,自增2到3

mysql> insert into test(ftime,ftype,fdesc) values(now(),3,'c');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
|   1 | 2008-04-23 19:32:22 |     1 | a     |
|   2 | 2008-04-23 19:32:41 |     2 | b     |
|   3 | 2008-04-23 19:33:27 |     3 | c     |
+-----+---------------------+-------+-------+
3 rows in set (0.00 sec)

 

--大于当前计数器值时,设为指定值100

mysql> insert into test values(100,now(),4,'d');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
|   1 | 2008-04-23 19:32:22 |     1 | a     |
|   2 | 2008-04-23 19:32:41 |     2 | b     |
|   3 | 2008-04-23 19:33:27 |     3 | c     |
| 100 | 2008-04-23 19:34:01 |     4 | d     |
+-----+---------------------+-------+-------+

 

--指定负数或大于精度的值(int(10) unsigned精度为0--4294967295

 

mysql> insert into test values(-1,now(),5,'aa');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
|   1 | 2008-04-23 19:32:22 |     1 | a     |
|   2 | 2008-04-23 19:32:41 |     2 | b     |
|   3 | 2008-04-23 19:33:27 |     3 | c     |
| 100 | 2008-04-23 19:34:01 |     4 | d     |
| 101 | 2008-04-23 19:35:40 |     5 | aa    | ==>负数时相当于没有指定,自增1
+-----+---------------------+-------+-------+
5 rows in set (0.00 sec)

mysql> insert into test values(4294967295+1,now(),5,'aa');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+---------------------+-------+-------+
| Fid        | Ftime               | Ftype | Fdesc |
+------------+---------------------+-------+-------+
|          1 | 2008-04-23 19:32:22 |     1 | a     |
|          2 | 2008-04-23 19:32:41 |     2 | b     |
|          3 | 2008-04-23 19:33:27 |     3 | c     |
|        100 | 2008-04-23 19:34:01 |     4 | d     |
|        101 | 2008-04-23 19:35:40 |     5 | aa    |
| 4294967295 | 2008-04-23 19:36:03 |     5 | aa    | ==>最大值
+------------+---------------------+-------+-------+
6 rows in set (0.00 sec)

mysql> insert into test(ftime,ftype,fdesc) values(now(),3,'c');==>自增1时,发现已经超过最大值,赋值为最大值,发现id列是pk,前面已经有一个最大值,键重复!
ERROR 1062 (23000): Duplicate entry '4294967295' for key 1
mysql>

 

--gap演示

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `test` (
    ->   `Fid` int(10) unsigned NOT NULL auto_increment,
    ->   `Ftime` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   `Ftype` int(10) unsigned NOT NULL default '0',
    ->   `Fdesc` varchar(255) default NULL,
    ->   primary key `i_fid` (`Fid`)
    -> ) ENGINE=innodb DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(100,now(),4,'d');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
| 100 | 2008-04-23 19:39:04 |     4 | d     |
+-----+---------------------+-------+-------+
1 row in set (0.00 sec)

mysql> start transaction;  ==>开始一个新事务,临时禁止了autocommit=1,相当于0,显示commit事务才生效!
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(ftime,ftype,fdesc) values(now(),5,'aa'),(now(),5,'aa'),(now(),5,'aa'),(now(),5,'aa');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
| 100 | 2008-04-23 19:39:04 |     4 | d     |
| 101 | 2008-04-23 19:39:15 |     5 | aa    |
| 102 | 2008-04-23 19:39:15 |     5 | aa    |
| 103 | 2008-04-23 19:39:15 |     5 | aa    |
| 104 | 2008-04-23 19:39:15 |     5 | aa    |
+-----+---------------------+-------+-------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(ftime,ftype,fdesc) values(now(),5,'aa');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
| 100 | 2008-04-23 19:39:04 |     4 | d     |
| 105 | 2008-04-23 19:39:35 |     5 | aa    | ==>没有从101开始,形成gap
+-----+---------------------+-------+-------+
2 rows in set (0.00 sec)

 

--create table & alter table中的AUTO_INCREMENT = n选项

mysql> CREATE TABLE `test2` (
    ->   `Fid` int(10) unsigned  not null auto_increment ,
    ->   `Ftime` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   `Ftype` int(10) unsigned NOT NULL default '0',
    ->   `Fdesc` varchar(255) default NULL,
    ->   primary key `i_fid` (`Fid`)
    -> ) ENGINE=innodb DEFAULT CHARSET=latin1 AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into test2(ftime,ftype,fdesc) values(now(),5,'aa'),(now(),5,'aa'),(now(),5,'aa'),(now(),5,'aa');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+-----+---------------------+-------+-------+
| Fid | Ftime               | Ftype | Fdesc |
+-----+---------------------+-------+-------+
| 100 | 2008-04-23 19:41:51 |     5 | aa    | ==>从100开始
| 101 | 2008-04-23 19:41:51 |     5 | aa    |
| 102 | 2008-04-23 19:41:51 |     5 | aa    |
| 103 | 2008-04-23 19:41:51 |     5 | aa    |
+-----+---------------------+-------+-------+
4 rows in set (0.00 sec)

 

 

mysql> alter table test2 AUTO_INCREMENT = 1000;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into test2(ftime,ftype,fdesc) values(now(),5,'aa');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+------+---------------------+-------+-------+
| Fid  | Ftime               | Ftype | Fdesc |
+------+---------------------+-------+-------+
|  100 | 2008-04-23 19:41:51 |     5 | aa    |
|  101 | 2008-04-23 19:41:51 |     5 | aa    |
|  102 | 2008-04-23 19:41:51 |     5 | aa    |
|  103 | 2008-04-23 19:41:51 |     5 | aa    |
| 1000 | 2008-04-23 19:42:48 |     5 | aa    |
+------+---------------------+-------+-------+

 

 

注意:default与auto_increment不可同时使用,auto_increment列必须为pk,一个表中只能有一个auto_increment列

 

 

几个疑问:

 1) 手册上讲计数器的初始化算法是由mysql执行一个类似语句来做的

 SELECT MAX(ai_col) FROM T FOR UPDATE;

 在my.cnf中指定log=query_log(记录下所有sql)重启mysql后,对一个空表执行insert,并没有发现类似语句

 

 2) 手册提到在第2-(2)情况下初始化计数器时,会进行'独占性锁定读'直到事务结束

    Note that in this initialization we do a normal exclusive-locking read on the table and the lock
lasts to the end of the transaction.

 

   insert需要获得计数器上的AUTO_INC锁

 

  怎样才能模拟这两个场景,查看到锁定了?

 

 3) auto_increment counter是否是一个变量?

   当被初始化(initialize)后,后面就是引用(refer)它,而不再需要初始化,除非mysqld死掉后重启,这个变量被重置(由于常驻内存没有放磁盘文件中),这时又需要初始化(initialize)?

  只有在初始化时,才用SELECT MAX(ai_col) FROM T FOR UPDATE;算法

  初始化过后,遇到insert时就是直接拿计counter+1赋值给counter及列值,而不再是从表中取最大值+1

  否则,第4点关于'空隙'问题就解释不了 ---用select max()就不会存在间隙,除非人为设定!