Recently with MySQL, use the add columns, when performing bulk insert found discontinuous phenomenon, there is no delete, not delete data from empty,
Later found the hole size and batch size has something to do, it depends on the generate new auto_increament_value MySQL,
The new value will be a 2 n Numbers, the biggest since the number is greater than the bulk insert data referred to in the minimum value of the NTH power of 2,
Don't know if this behavior is my specific MySQL version, my version is 5.5.47,
Consult a great god, whether have new version or patch has been released, or what set can change this behavior, thanks first,

I tried the following some batch size found the rule, please see the table below:
A batch of insert the amount of data, for the new data from the value-added
==============================
1, 2,
2, 4
3, 4
4, 8
5, 8
6, 8
7, 8
August 16th
September 16th
October 16th
November 16th
12 dec
13 dec
Dec 14
15 and 16th
16, 32

The following script to interested friend reference, used to produce the discontinuous since value-added script:
Drop table if the exists TTT;
Drop table if the exists tsource;

The create table if not exists TTT (id int auto_increment primary key, name varchar (50));
The create table if not exists tsource (name varchar (50));
Insert into tsource values
(" aaa "),
(' BBB '),
(" CCC "),
(" DDD ");

Insert into TTT (name)
Select the name from tsource;

Insert into TTT (name) values (' eee);

Select * from TTT.

CodePudding user response:

5.7.13 test in the jump

CodePudding user response:

May be inserted before the set AUTO_INCREMENT=Max (id) + 1, because the official didn't announce this bug, do not know will not affect storage after change,

CodePudding user response:

The insert for the select statement on the column will be pre-allocated double if enough
So you insert four statements are distributed on the column to 8 insert 10 statement must be assigned to 16 article 20 statements are distributed to 32

CodePudding user response:

reference rucypli reply: 3/f

insert for the select statement on the column will be pre-allocated double if enough
So you insert four statements are distributed on the column to 8 insert 10 statement must be assigned to 16 article 20 statements are distributed to 32

Yes, this behavior can be understood as "just such a design", after all, since the concept and design principles of increase does not contain consecutive, to ensure continuous friend can only another think method, generate continuous id there are quite a few online, some still in view of the high concurrency scheme can refer to,

CodePudding user response:

refer to the second floor wangjian0228 response:

perhaps can be inserted before the set AUTO_INCREMENT=Max (id) + 1, since the official did not announce this bug, do not know will not affect storage after change,

Personally, I think not necessarily affect the inch, but this solution when considering concurrent need to lock table in advance, otherwise may cause duplicate id,

CodePudding user response:

reference 1st floor ZJCXC response:

5.7.13 test of jumping

This test results further point to one conclusion: MySQL team are designed in this way, guarantee continuous is not in the column on the design considerations,

CodePudding user response:

Behind http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization, mentioned on the list, but still can't imagine why such design, your example after the first four data already AUTO_INCREMENT=8, think impassability think impassability

CodePudding user response:

refer to 7th floor wangjian0228 response:

http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization, mentioned on the list, but I still can't imagine why such design, your example after the first four data already AUTO_INCREMENT=8, think impassability ah think impassability

Actually this is just a demo on the column when doing bulk insert sample code of behavior, I didn't design, what if the MySQL design since the behavior of the columns, why is it so I can only guess, probably MySQL the bulk insert on the column behavior is for performance reasons, no request in batch of each insert increased since the increment operation, after all, in order to ensure the safety it is need to synchronize concurrent processing overhead, if in advance to set aside part of that within the bulk insert can simply in the thread-local + 1, leave Spaces for other threads will use the new global count,

CodePudding user response:

Actually otherwise, MYISAM is fine

CodePudding user response:

Here involves the step problem, the id to get, is the id number of the step length,
Never write data to the this list, then step length is 1, otherwise the step length is the last article number, insert data
N1 is the biggest id now, for example, to list in the N2 of the data, so next time you write data, is from the beginning of the N1 + N2,

CodePudding user response:

https://blog.csdn.net/weixin_39004901/article/details/84871909

On the key hole is caused by the characteristics of the MySQL, specifically related to binlog and innodb_autoinc_lock_mode, illustrate the above address,