Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you what are the problems of INNODB self-increasing primary key in Mysql, I believe most people still do not know how, so share this article for everyone's reference, I hope you have a lot of gains after reading this article, let's go and understand it together!

Background:

Self-growth is a very common data attribute. In MySQL, everyone likes to make the field of self-growth attribute a primary key. Especially InnoDB, because of the characteristics of InnoDB's clustered index, the performance of using self-increasing attribute fields as primary keys is better. Here are a few things to note about self-increasing primary keys.

Question 1: Watch lock

Prior to MySQL 5.1.22, InnoDB self-incrementing was achieved through its own self-incrementing counter, which was implemented through the table locking mechanism (AUTO-INC LOCKING). Locks are not released at the completion of each transaction, but rather at the completion of SQL statements inserted into self-increasing values, waiting for their release before proceeding further. For example, when there is an auto_increment field in the table, innoDB stores a counter in memory to record the value of auto_increment, and when a new row of data is inserted, it locks the counter with a table lock until the end of the insertion. If there are a lot of concurrent inserts, table locks can cause SQL congestion. insert into..... Select also performs poorly with large amounts of inserted data

After 5.1.22, InnoDB introduced the parameter innodb_autoinc_lock_mode in order to solve the problem of self-increasing primary key lock table, which was implemented through the growth mechanism of lightweight mutex. It is specifically designed to adjust locking policy when auto_increment is used, and currently there are three options:

Insert Type Description:

INSERT-LIKE： Simple inserts refer to statements that can determine the number of inserted rows before insertion, including INSERT, REPLACE, INSERT…SELECT, REPLACE…SELECT,LOAD DATA, etc., excluding statements such as INSERT…ON DUPLICATE KEY UPDATE. Bulk inserts: Statements that cannot be sure to get inserted lines before insertion. For example INSERT…SELECT,REPLACE…SELECT,LOAD DATA.Mixed-mode inserts: means that some of them are self-increasing and some of them are deterministic.

0: Through table locking, that is, all types of inserts are AUTO-inc locking.

1: Default value, for simple insert self-increment value generation, use mutex to accumulate counters in memory, for bulk insert, use table lock.

2: Use mutex mechanism to generate all insert-like self-growth values, with the highest performance. Concurrent insertion may lead to discontinuous self-growth values, which may lead to inconsistent Replication of Statements. To use this mode, Row Replication mode is required.

Prior to mysql 5.1.22, mysql's INSERT_LIKE statement locked the table with an AUTO-INC lock throughout the statement's execution until the statement ended (not the transaction). Therefore, when using INSERT…SELECT, INSERT…values(…),values(…), LOAD DATA and other time-consuming operations, the entire table will be locked, and other insert-like,update and other statements will be blocked. It is recommended to use a program to break these statements into multiple statements and insert them one by one to reduce the lock table time for a single time.

Solution:

Resolved by parameter innodb_autoinc_lock_mode =1/2 and inserted with simple inserts mode.

Question 2: Self-increasing PK is discontinuous

5.1.22 Default: innodb_autoinc_lock_mode = 1

Directly parsing the statement to get the number to insert, and then allocating enough auto_increment ids at once will only lock the entire allocation process.

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)

Insert 10 records, but the AUTO_INCREMENT of the table =16. When inserting another record, the self-increasing id of the table is already discontinuous.

Reason:

When parameter innodb_autoinc_lock_mode = 1, redundant ids (handler.cc:compute_next_insert_id) will be pre-applied every time, and after insert execution is completed, these reserved ids will be specially vacated, that is, the current maximum id after pre-application will be written back to the table (dict0dict.c:dict_table_autoinc_update_if_greater).

The reserved strategy is "apply for more when not enough," and the actual implementation is to apply step by step. As for the number of applications, it was decided by "several pieces of data N have been inserted" at that time. When auto_increment_offset=1, the number of pre-applications is N-1.

So you will find that when you insert only 1 line, you don't see this phenomenon and don't pre-apply. If N>1 is required. The number of multiple applications is N-1, so the self-increment after execution is: 1+N+(N-1). If there are 10 lines in the test, then: 1+10+9 =20, and 16 are inconsistent? The reason is: when inserting row 8, the AUTO_INCREMENT of the table is already 16, so when inserting row 10, the id has been reserved in row 8, so it is directly used, and the self-increment is still 16. So when inserting 8 lines, 7 more ids are requested, namely: 9, 10, 11, 12, 13, 14, 15. Insert rows 8 to 15 as in the example, and the AUTO_INCREMENT of the table is always 16

In order to discover the rule, here I did an experiment, not very accurate, insert the number of rows and the corresponding autocommit are 2> 4 3> 4 4-7> 8 8-15> 16 16-31> 32), can only say AUTO_INCREMENT may be 2n(specific when is 2n has not been found), the range should be between [n,2n]

Verification:

Insert 16 lines: Guess id of pre-request: 1+16+(16-1)= 32, i.e. 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) ---First insertion as 2*16

And guess the same, since the ID increased to 32. So when you insert 16 lines, you get 17, 18, 19... 31 。

Therefore, the reason for ID discontinuity is that when innodb_autoinc_lock_mode = 1, more IDs will be applied. The advantage is that allocating enough auto_increment ids at once only locks the entire allocation process.

5.1.22 Previous default: 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: 0root@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)

Insert 10 records, but the AUTO_INCREMENT of the table =11. When inserting another record, the self-increasing id of the table is still continuous.

innodb_autoinc_lock_mode = 2 and innodb_autoinc_lock_mode = 1. However, in this mode, one is allocated one by one, and the table will not be locked. Only the process of allocating id will be locked. The difference between 1 and 1 is that multiple will not be pre-allocated. This mode has the highest concurrency. But there is a problem in replication when binlog_format is statement-based

Solution:

Try to make the primary key ID meaningless or insert it using simple inserts pattern.

Conclusion:

When innodb_autoinc_lock_mode is 0, self-increment id will continue, but table lock will occur. To solve this problem, innodb_autoinc_lock_mode can be set to 1 or even 2. Performance is improved, but self-increasing id discontinuity is caused under certain conditions.

The above is "Mysql INNODB self-increasing primary key problems what" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.