This issue reference from: www.zhihu.com/question/43… The solution is personally original

Key TakeAways

  • There are three types of AutoIncrement in InnoDB engineThe lock mode:
    • Innodb_autoinc_lock_mode =0 (traditional lock mode) : obtain table lock, release after statement execution
    • Innodb_autoinc_lock_mode =1 (default until MySQL 8.0) : for statements with an unspecified number of inserts (e.gINSERT ... SELECT.REPLACE ... SELECTandLOAD DATAAs with innodb_autoinc_lock_mode=0, other fixed number of statements are executed firstBatch Obtaining ids.Then execute the statement.
    • Innodb_autoinc_lock_mode =2 (interleaved lock mode, MySQL 8.0+ default) : use optimistic lock, CAS update counter acquisition.
  • Before MySQL 8.0, the AutoIncrement counter is stored in memory, and after MySQL 8.0, it is stored in disk persistently. Write a Redo Log with each update and record it in the InnoDB engine table at checkpoints.
  • The AutoIncrement ID enables new data to be aggregated together, which is beneficial for most OLTP services (access frequency is active in the last day, week, or months, while data over a period of time is rarely accessed). For such services, you are advised to use the autoincrement primary key and use the service primary key (UUID) as the unique index of the secondary level.
  • If you are concerned about distributed performance and avoiding lock performance issues caused by AutoIncrement, consider using the ID generator to generate: globally trending primary keys

Why is Auto Increment not UUID used as primary key

MySQL InnoDB engine default primary key index is B+ tree index, also called clustered index.

If InnoDB is used as the storage engine, the data in the table will have a primary key. Even if you do not create a primary key, the system will create an implicit primary key for you. This is because InnoDB stores data in a B+ tree whose key values are the primary keys. The leaves of the TREE store all the data in the table. This kind of B+ tree index constructed with primary key as the key value of B+ tree index is called clustered index.

In storage, the clustered index data will be clustered and stored together according to the index value:

MySQL reads data from disk page by page. If a certain item of data is on a certain page, but the other data on the page is not cared about, the performance will decrease dramatically, similar to CPU false sharing:

In accordance with the principle of B+ tree, the AutoIncrement ID can ensure that the latest data is read in one page and reduce the B+ tree split flipping. Because the UUID is out of order, the B+ tree is constantly flipped when inserted, and the latest data may not be on the same page. It’s very likely that the most recent data is on the same page as the data from years ago.

In most OLTP-like businesses, such as orders for shopping and payment transactions, and raffles for holiday promotions, there is a usage scenario where the frequency of access is active in the last day, week, or months, and the data over a period of time is rarely accessed. For such services, you are advised to use the autoincrement primary key and use the service primary key (UUID) as the unique index of the secondary level. For distributed performance and to avoid lock performance issues with AutoIncrement, consider using an ID generator to generate globally trending primary keys, such as Twitter’s Snowflake algorithm, which generates primary key ids with time stamps in front. Or is similar to the “time + + on the business” (for example 20210105105811233 ord0000001) string, as the primary key id, that actually can approximate thermal data gathered is stored in together, namely the MySQL read from page to page can hit more to read the data processing

AutoIncrement principle

We only care about the InnoDB engine here.

A maximum AutoIncrement

The maximum value of AutoIncrement, depending on the column type. The maximum column type that can be set to UNSIGNED BIGINT is 18446744073709551615. If you continue generating beyond this value, it will still be 18446744073709551615. It’s not going to increase.

AutoIncrement lock mode

To obtain the latest value of AutoIncrement, a lock is required. There are currently three lock modes, corresponding to innodb_autoinc_lock_mode values, 0, 1, 2. After MySQL 8.0, the default is 2, and before that, the default is 1

  • Innodb_autoinc_lock_mode =0 (traditional lock mode) Innodb_AUTOINC_LOCK_mode =0 (traditional lock mode) Innodb_AUTOINC_LOCK_mode =0 (traditional lock mode) At the end of the statement, the auto_INCREMENT value is released, and the allocated values are consecutively allocated. Normally, there is no gap (of course, if the transaction rollback auto_increment value is wasted, resulting in a gap).

  • Innodb_autoinc_lock_mode =1 (avG lock mode) Innodb_autoinc_lock_mode =1 (avG lock mode) SELECT, REPLACE … The auto-Inc lock is used for SELECT and LOAD DATA, and a new lightweight mutex is used to allocate values of auto_INCREMENT columns for a known number of normal inserts. This lock only lasts until a certain number of ids are obtained and does not wait for the end of the statement to be released. That is, take a lightweight lock, allocate the required number of ids in advance, release the lock, and then execute the statement. Of course, Simple Inserts need to wait if other transactions already hold auto-Inc locks. Of course, in this case, there may be more gaps.

  • Innodb_autoinc_lock_mode =2 (Interleaved Lock mode) Inserts of any type in this mode perform best without an auto-inc lock, but generate an auto_increment value gap inside the same statement. All statements update compare-and-set to the same value, similar to optimistic locking. This lock mode has problems with statement-based replication’s master/slave synchronization. Because statements, not row values, are transferred synchronously, differences after statement execution result in inconsistent primary keys between primary and secondary.

AutoIncrement storage

Before MySQL 8.0, the AutoIncrement counter was stored in memory and initialized with the following statement at startup:

SELECT MAX(ai_col) FROM table_name FOR UPDATE;
Copy the code

After MySQL 8.0, persistent storage to disk. Write a Redo Log with each update and record it in the InnoDB engine table at checkpoints.

So, before MySQL 8.0, if rollback caused some values not to be used, they would still be used after a restart. But not after MySQL 8.0.

Daily brush, easy to improve skills, get a variety of offers: