How MySQL auto_increment step size is determined

Question

I have a web page table with a primary key as a URL hash and an auto_increment id column, which is also a unique key.

Which confuses me a bit, why successive inserts do not increase the identifier field by 1. When I first created the table and made one insert, the first identifier was 1. The second insert and identifier were 5, and the third was 8.

I have a trigger in a table that on the insert calculates a hash of the URL of a webpage. Not sure if this is important or not.

It is not a problem to have spaces, but I would like to understand why sequential inserts do not generate identifiers with a step size of 1.

thanks!

Answer 1

A few tips on why this might happen:

See auto_increment_increment . This controls the growth every time a new value is requested during INSERT.

Also, if you use InnoDB tables in MySQL 5.1, they optimized the auto-inc distribution to lock the table for a shorter duration. This is useful for concurrency, but it can also “lose” auto-inc values ​​if the INSERT of a row conflicts with another constraint, such as a secondary UNIQUE column or foreign key. In these cases, the highlighted auto-inc value does not return to the queue, since we assume that another parallel thread has already assigned the next auto-inc value.

Of course, rollbacks are also performed, in which case the auto-inc value can be allocated, but discarded.

Answer 2

This may be due to transactions that eventually roll back. For instance,

• Insert google.com id = 5
• Insert mysql.com id = 6
• Insert file stackoverflow.com id = 7
• rollback insert google.com
• mysql.com rollback tab

Then stackoverflow.com is inserted with id = 7 and 5 and 6 remain empty.

Answer 3

Automatic addition is always performed in steps of 1, but after deleting a row, the identifier is not freed.

The following auto-increment identifier will be stored in the information schema database in your MySQL environment. This is always +1, and when a line is deleted, the identifier between them will be absent. So, if the first is 1 and the second (in your opinion) is 5, then 2,3,4 were deleted in the process.

Run this query to find out and replace the last 2 values ​​in :;

SELECT AUTO_INCREMENT from `information_schema`.`TABLES` WHERE TABLE_NAME = '<<YOUR TABLE NAME HERE>>' AND TABLE_SCHEMA = '<< YOUR DATABASE NAME HERE >>' 

Answer 4

mysql.cnf / ini can set the change:

 auto-increment-increment = 2 auto-increment-offset = 1 

In some configuration (for example, for master / master replication) a_i can skip numbers using these variables.