Shulou(Shulou.com)06/03 Report--

This article mainly explains "MySQL how to achieve self-increasing primary key", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL how to achieve self-increasing primary key" bar!

Catalogue

First, where is the self-value-added storage?

II. Self-value-added modification mechanism

III. Timing of self-value-added modification

IV. Optimization of self-increasing lock

The self-adding primary key has been used up.

First, where is the self-value-added storage?

Different engines have different preservation strategies for self-increment.

The self-increment of the 1.MyISAM engine is saved in the data file

The self-increment of the 2.InnoDB engine, in MySQL5.7 and previous versions, is stored in memory and is not persisted. After each restart, when the table is opened for the first time, it will find the maximum value of self-increment max (id), and then use the max (id) + step as the current self-increment of the table.

Select max (ai_col) from table_name for update

In the MySQL8.0 version, self-increment changes are recorded in redo log, and the restart depends on redo log to restore the value before restart.

II. Self-value-added modification mechanism

If the field id is defined as AUTO_INCREMENT, the self-increment behavior is as follows when inserting a row of data:

1. If the id field is specified as 0, null, or no value when inserting data, fill in the current AUTO_INCREMENT value of the table to the self-increment field

two。 If the id field specifies a specific value when inserting data, use the value specified in the statement directly

Suppose that the value of a minor insert is X, and the current self-increment is Y

1. If you are Xeroy, you need to modify the current self-increment to a new self-increment

The new self-increment generation algorithm is: starting from auto_increment_offset (initial value) and taking auto_increment_increment (step size) as the step size, continue to stack until the first value greater than X is found as a new self-increment.

III. Timing of self-value-added modification

Create a table t, where id is the self-increasing primary key field and c is the only index. The table statement is as follows:

CREATE TABLE `t` (`id` int (11) NOT NULL AUTO_INCREMENT, `c` int (11) DEFAULT NULL, `d` int (11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c`) ENGINE=InnoDB

Suppose that there is already a record in table t, and then execute another insert data command:

Insert into t values (null, 1,1)

The execution process is as follows:

1. The executor calls the InnoDB engine interface to write a line, and the value passed in for this line is (0mem1p1).

2.InnoDB found a value for which no self-increment id was specified to get the current self-increment 2 of table t

3. Change the value of the passed-in row to (2 # 1)

4. Change the self-increment of the watch to 3.

5. Continue the insert data operation, because the record of clock1 already exists, so report Duplicate key error (unique key conflict), and the statement returns

The corresponding execution flow chart is as follows:

After that, when you insert a new data row, you get a self-incrementing id of 3. There is a case of self-increasing primary key discontinuity.

Unique key conflicts and transaction rollback will result in self-increasing primary key id discontiguity

IV. Optimization of self-increasing lock

The self-added id lock is not a transaction lock, but is released immediately after each application, so as to allow other transactions to reapply.

In the MySQL5.0 version, however, the scope of self-incrementing locks is at the statement level. That is, if a statement applies for a table self-incrementing lock, the lock will not be released until the end of the statement execution

The MySQL5.1.22 version introduces a new policy with a new parameter innodb_autoinc_lock_mode. The default value is 1.

1. This parameter is set to 0, which means that the policy of the previous version of MySQL5.0 is adopted, that is, the lock is not released until the end of the statement execution.

two。 This parameter is set to 1

An ordinary insert statement that is released immediately after the lock is added to the application

Similar to insert... For bulk insert data statements such as select, the lock will not be released until the end of the statement.

3. This parameter is set to 2, and all applications release the lock after the action of adding the primary key.

For data consistency, the default setting is 1

If sessionB applies to release the self-increment lock as soon as it is added, this may occur:

SessionB first inserts two rows of data (1), (2) and (2)

SessionA came to apply since adding id to get id=3, inserted (3jing5jue 5)

After that, sessionB continues to execute, inserting two records (4, 4, 3, 3) and (5, 4, 4)

When binlog_format=statement, two session execute insert data command at the same time, so there are only two cases of update log facing table T2 in binlog: either remember sessionA first, or record sessionB first. No matter which binlog is used to execute from the library, or to restore temporary instances, the statement sessionB is executed in the repository and temporary instances, and the id is continuous in the results. At this point, there is a data inconsistency in the library.

The way to solve this problem:

1) Let the original library insert data statements in batches to generate continuous id values. Therefore, it is to achieve this goal that the lock is not released until the end of the statement execution.

2) the operations of inserting data are truthfully recorded in binlog, and when the standby database is executed, it is no longer dependent on the self-increasing primary key to generate. That is, innodb_autoinc_lock_mode is set to 2 and binlog_format is set to row

If there is bulk insert data (insert … Select, replace... Select and load data), from the point of view of concurrent insert data performance, it is recommended to set innodb_autoinc_lock_mode to 2 and binlog_format to row, which can achieve concurrency without the problem of data consistency.

For statements that insert data in bulk, MySQL has a strategy to apply for self-incrementing id in bulk:

1. During the execution of the statement, the first time you apply for a self-increment id, a

After 2.1s are used up, this statement applies for a self-increasing id for the second time, and two will be allocated.

After 3.2s are used up, the same sentence will be assigned 4 when applying for the third application to add id.

4. By analogy, the same statement applies for self-increasing id, and the number of self-increasing id applied for each time is twice that of the previous one.

Insert into t values (null, 1jue 1); insert into t values (null, 2pje 2); insert into t values (null, 3pje 3); insert into t values (null, 4pr 4); create table T2 like tten insert into T2 (cmeme d) select from tten insert into T2 values (null, 5je 5)

Insert... Select, which actually inserts four rows of data into table T2. However, these four lines of data are self-increasing id in three applications, the first application is id=1, the second is assigned id=2 and id=3, and the third is assigned to id=4 to id=7

Since only four id are actually used in this statement, id=5 to id=7 is wasted. After that, you execute insert into T2 values (null, 5jue 5), and the data you actually insert is (8meme 5pen5).

This is the third reason for the self-increasing id discontinuity of primary key id.

The self-adding primary key has been used up.

If a row of records is inserted after the primary key field reaches the defined type limit, the error of primary key conflict will be reported.

Take an unsigned integer (4 bytes with an upper limit of 232 − 1 ^ {32}-1 232 − 1) as an example to verify it through the following sequence of statements:

CREATE TABLE t (id INT UNSIGNED auto_increment PRIMARY KEY) auto_increment = 4294967295 / insert INTO t VALUES (NULL); INSERT INTO t VALUES (NULL)

After the first insert statement successfully inserts the data, the AUTO_INCREMENT of the table does not change (still 4294967295), which causes the second insert statement to get the same self-increment id value, and then attempts to execute the insert statement, reporting a primary key conflict error.

Thank you for your reading, the above is the content of "MySQL how to achieve self-increasing primary key". After the study of this article, I believe you have a deeper understanding of how to achieve self-increasing primary key in MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.