MySQL, C ++ - programmatically, how does MySQL auto-increment work?

Question

From the latest source code (not sure about C or C ++) MySQL, how does it do auto-increment? I mean, is it effective that it stores as a metadata resource in the table where it is last used, or should it scan the table to find the largest identifier used in the table? Also do you see any negative aspects of using auto-increment when you look at how it is implemented, for example, PostgreSQL?

Answer 1

It depends on which engine the database is using. InnoDB stores the largest value in memory, not on disk. Very effective. I would suggest that most engines will do something similar, but cannot guarantee this.

Intensive growth InnoDB Will run the following query once when the DB loads and store the variable in memory:

SELECT MAX(ai_col) FROM t FOR UPDATE; 

Comparing this with PostgreSQL , the complete lack of auto_increment depends on how you implement the field yourself. (At least that wasn't enough the last time I used it. Maybe they have changed). Most created SEQUENCE . It seems to be stored in a pseudo table. I would ask InnoDB to become an easier way. I would suggest that InnoDB would be more efficient if they are not equal.