Auto-Inc gaps - Id value explodes We have a big issue in production environment, many tables were created with Int Auto-Inc Primary Key.

Adding to the fact that our insert mechanizes includes using Insert Ignore/Insert.. On Duplicate Update we have reached the Int.MaxValue In a few tables.

I am considering my options and would like to consult:


Switching to innodb_autoinc_lock_mode = 0
Alerting to BigInt in all tables auto-inc column
Switching all our application inserts to  Select + Insert/Update


I am considering option 1 as this is an On-Premise DB and the migration work here is very costly.

As far as I understand from my researching of using innodb_autoinc_lock_mode=0 is that it as somewhat of a performance downgrade when handling concurrent inserts. I am planning a benchmark to understand the performance difference, what do you guys think about this option?
I call this "burning ids".  I think it happens with all flavors of INSERT, including INSERT IGNORE, IODKU, REPLACE, etc.

Here is a workaround:  http://mysql.rjweb.org/doc.php/staging_table#normalization

That describes how to use 2 SQLs to "normalize" a batch of names and get their ids in an efficient way, and without burning ids.  If you have only a single name to insert/lookup, it is still 2 sqls.

INSERT allocates as many ids as it might need, then burns the ones it did not need.


I think the autoinc_mode is global, and hurts performance.
Sure, BIGINT will work, but I hate to use 8 bytes.
And, yeah, my recommendation is similar to your #3.  But look closely; there may be some useful differences, especially if you can batch things.