MySQL/InnoDB处理AUTO_INCREMENT（一）

AUTO_INCREMENT Handling in InnoDB

下面所使用的表

CREATE TABLE people  (
    person_id BIGINT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    PRIMARY KEY (person_id)
);

Traditional InnoDB Auto-Increment Locking

the initialization of auto-increment counter

auto-increment counter的初始化据我的分析存在于三个节点：

数据库服务器启动后表的第一次插入前

通过show table status命令查询表的状态信息，如果auto-increment counter没有初始化，就要在show table status之前初始化。

表新建后，要初始化auto-increment counter。

下面是mysql官方文档的描述：http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-traditional.html

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains ????a special counter called the auto-increment counter that is used in assigning new values for the column. This 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（在服务器启动后，counter的值由于存在内存中，会消失）, for the first insert into a table t（对于第一次插入）, InnoDB executes the equivalent（等价的，等效的） of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB increments the value retrieved（取回，恢复） by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by one（默认的这个值会增加one）. This default can be overridden by the auto_increment_increment configuration setting.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

If a SHOW TABLE STATUS statement examines（调查） the table t before the auto-increment counter is initialized, InnoDB initializes but does not increment the value and stores it for use by later inserts. ?This initialization uses a normal exclusive-locking read on the table and the lock lasts to the end of the transaction.?

如下所示，通过truncate将一张表初始化，通过show table status可以看到Auto_increment为1，这只是存储到counter中以供下次插入使用的。这个初始化过程会加一个排它锁把表锁住，这个锁会一直持续到事务结束。

mysql> truncate people;
Query OK, 0 rows affected (0.54 sec)

mysql> show table status from local_database like 'people'\G
*************************** 1. row ***************************
           Name: people
         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
    Create_time: 2014-11-04 15:36:51
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select max(person_id) from people;
+----------------+
| max(person_id) |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

auto-increment counter

accessing the auto-increment counter

最重要的一个概念：table-level AUTO-INC lock，作用就是当执行一个insert语句时，这个锁会把表锁住，知道这个insert语句执行完成，然后表解锁，而不是等到这个事务结束。

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. Nevertheless（然而，尽管如此）, two transactions cannot have the AUTO-INC lock on the same table simultaneously（同时，一起，一齐）, which can have a performance（性能） impact（影响） if the AUTO-INC lock is held for a long time. That might be the case for a statement such as INSERT INTO t1 ... SELECT ... FROM t2 that inserts all rows from one table into another.

auto_increment_increment&&auto_increment_offset

auto_increment_increment：自增值的自增量

auto_increment_offset： 自增值的偏移量

设置了两个值之后，改服务器的自增字段值限定为：

auto_increment_offset + auto_increment_increment*N 的值，其中N>=0，但是上限还是要受定义字段的类型限制。

比如：

auto_increment_offset=1

auto_increment_increment=2

那么ID则是所有的奇数[1,3,5,7，.....]

如果：

auto_increment_offset=5

auto_increment_increment=10

那么ID则是所有的奇数[5,15,25,35，.....]