Geek Answers Handbook

Automatically increase primary residual gaps in counting

I have a table with a primary key with automatic addition. This table is for storing millions of records, and now I do not need to delete anything. The problem is that when inserting new rows due to some error, the auto-increment key leaves some spaces in the auto-increment identifiers. For example, after 5, the next identifier is 8, leaving a space of 6 and 7. The result of this is when I count the lines, the result is 28000, but the maximum id is 58000. What could be the reason? I do not delete anything. And how can I fix this problem.

PS I use insert ignore when inserting records so that it does not throw an error when I try to insert a repeating record in a unique column.

+9
mysql primary-key
source share
4 answers

It is by design and always will be.

Why?

Take 2 overlapping transactions that do INSERT

  • Transaction 1 does an INSERT, gets the value (say 42), does more work
  • Transaction 2 does INSERT, gets the value 43, does more work

Then

  • Transaction Failure 1. Returned. 42 remains unused
  • Transaction 2 ends at 43

If consistent values ​​were guaranteed, each transaction should occur one after another. Not very scalable.

Also see Insert records always take adjacent identification values (SQL Server, but the same principle applies)

+12
source share

You can create a trigger to handle auto-increment as:

CREATE DEFINER=`root`@`localhost` TRIGGER `mytable_before_insert` BEFORE INSERT ON `mytable` FOR EACH ROW BEGIN SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM mytable);; END
+3
source share

This is a problem in InnoDB, the MySQL storage engine.

This is really not a problem, since when checking documents for " AUTO_INCREMENT Handling in InnoDB " it is mainly said that InnoDB uses a special table to automatically increase at startup

And the query he uses is something like

 SELECT MAX(ai_col) FROM t FOR UPDATE;

This improves concurrency without affecting your data.

To not use this, use MyISAM instead of InnoDB as the storage engine

+2
source share

Perhaps (I have not tested this) the solution should set innodb_autoinc_lock_mode to 0 . According to http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html , this can make things a little slower (if you are inserting multiple rows in a single query), but should delete spaces.

+1
source share

More articles:


All Articles