Mysql/innodb processing auto_increment_1

Http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html

Mysql/innodb processing auto_increment_1

auto_increment Handling in InnoDB

The table that is used below

CREATE TABLE people (person_id BIGINT not NULL auto_increment, first_name varchar, last_name varchar (20), PRIMARY KEY (person_id));

Traditional InnoDB auto-increment LockingThe initialization of Auto-increment counter:

Auto-increment counter is initialized according to my analysis in three nodes:

• Before the first insert of the table after the database server starts

• The status information of the table is queried by the Show Table Status command, which is initialized before the show table status if Auto-increment counter is not initialized.

• After the table is new, initialize the auto-increment counter.

Here is a description of the official MySQL document: http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-traditional.html

If you specify a auto_increment column for a InnoDB table, the table handle in the InnoDB data dictionary contains? ? ? ? a special counter called the auto-increment counter that's used in assigning new values for the column. This counter was stored only in main memory and not on disk. ????

InnoDB uses the following algorithm to initialize the auto-increment counter for a table t this contains an auto_ INCREMENT column named Ai_col: After a server startup (when the server starts, the value of counter disappears because it exists in memory), for the first insert into A table T (for the first insertion), InnoDB executes the equivalent (equivalent, equivalent) of this statement:

SELECT MAX (Ai_col) from the T for UPDATE;

InnoDB increments the value retrieved (retrieve, restore) by the statement and assigns it to the column and to the Auto-increment C Ounter for the table. By default, the value was incremented by one (the default value will be increased by a). This default can is overridden by the auto_increment_increment configuration setting.

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

If a SHOW table STATUS statement examines (investigation) the TABLE T before the Auto-increment co unter 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 in the table and the lock lasts to the end of the Transactio N. ?

as shown below, By initializing a table with truncate, you can see that auto_increment is 1 through Show table status, which is only stored in counter for the next insertion ... This initialization process locks the table with an exclusive lock that lasts until the end of the transaction ....

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: 16384max_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:&nbsp 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)

InnoDB follows the same procedure (same process) for initializing the auto-increment counter for a freshly created table (initializes the newly created table auto-increment counter).

Accessing the Auto-increment counter

One of the most important concepts:table-level auto-inc lock, the effect is that when an INSERT statement is executed, the lock will lock the table, knowing that the INSERT statement is done, and then the table is unlocked instead of waiting for the transaction to end.

When accessing the auto-increment counter, InnoDB uses a special table-level auto-inc lock then it keeps to t He end of the current SQL statement, the "not" to the end of the transaction. The special lock release strategy is introduced to improve concurrency for inserts into a table containing an Auto_increm ENT column. Nevertheless (however, despite this), the transactions cannot has the auto-inc lock on the same table simultaneously (at the same time, together), whic H can have a performance (performance) impact (impact) If the Auto-inc lock is held for a long time. That might is the case for a statement such as INSERT into T1 ... SELECT ... From T2 This inserts all rows from one table to another.

Auto_increment_increment&&auto_increment_offset

Auto_increment_increment: Self-increment from increments

Auto_increment_offset: Offset from self-increment

After setting two values, the self-increment field value of the server is limited to:

The value of Auto_increment_offset + auto_increment_increment*n , where n>=0, but the upper limit is the type limit of the defined field.

Like what:

Auto_increment_offset=1

auto_increment_increment=2

Then the ID is all the odd [1,3,5,7, ...]

If:

Auto_increment_offset=5

auto_increment_increment=10

Then the ID is all the odd [5,15,25,35, ...]

============end============

Mysql/innodb processing auto_increment_1