An in-depth analysis of how to solve the problem of MySQL running out of auto-incremented IDs

This article brings you relevant knowledge about mysql, which mainly introduces the related issues about self-increasing IDs. IDs have an upper limit. Since there is an upper limit, they will always be used. When finished, what should I do if I run out of IDs? Let’s take a look at it. I hope it will be helpful to everyone.

Recommended study: mysql video tutorial

I recently saw such an interview question. MySQL has run out of self-increasing IDs, what should I do? The following is the solution to this interview question.

If you have used or understood MySQL, then you must know about auto-incrementing primary keys. Each auto-incrementing ID has an initial value defined, and then increases according to the specified step size (the default step size is 1). Although there is no upper limit for natural numbers, when we design the table structure, we usually specify the field length. Then, there is an upper limit for id. Since there is an upper limit, there will always be a time when it is used up. What if the ID is used up? Let’s learn together today.

Auto-increment id

Speaking of auto-increment id, I believe your first reaction must be to customize an auto-increment id field when designing the table structure. Then there is a problem. When inserting data, there may be unique primary key collision, SQL transaction rollback, batch insertion, batch application for auto-increment value, etc., which causes the auto-increment ID to be discontinuous.

The logic of the auto-increment value defined in the table after it goes online is: when applying for the next ID, the same value (maximum value) will be obtained. You can insert sql to set the id to the maximum value, and then insert a statement that does not actively set the id to verify this conclusion. If you insert again at this time, a primary key conflict will be reported~

Here is a reminder: 232-1 (4294967295) is not a particularly large number. For a table that frequently inserts and deletes data, it may be used Finished. Therefore, when building a table, you need to check whether your table is likely to reach this upper limit. If possible, you should create it as an 8-byte bigint unsigned.

InnoDB system automatically increments row_id

If the InnoDB table you create does not specify a primary key, InnoDB will create an invisible row_id with a length of 6 bytes for you. InnoDB maintains a global dict_sys.row_id value. For all InnoDB tables without primary keys, every time a row of data is inserted, the current dict_sys.row_id value is used as the row_id of the data to be inserted, and then the value of dict_sys.row_id is increased by 1.

Actually, when the code is implemented, row_id is an unsigned long integer (bigint unsigned) with a length of 8 bytes. However, when InnoDB was designed, only 6 bytes of length were left for row_id. In this way, only the last 6 bytes were put when writing to the data table. Therefore, there are two row_id values ​​that can be written to the data table. Features:

The value range of row_id written into the table is from 0 to 248-1;

When dict_sys.row_id=2^48, If there is another act of inserting data, you need to apply for row_id. After getting it, the last 6 bytes will be 0.

Although the number 2^48 is already very large, everyone should know that a system can run for a long time, so it is still possible to reach the upper limit. At this time, applying again will overwrite the original record. . Therefore, try not to choose this option!

Xid

When redo log and binlog in MySQL are combined, they have a common field called Xid. It is used to correspond to transactions in MySQL.

MySQL maintains a global variable global_query_id internally. Each time a statement is executed, it is assigned to Query_id, and then 1 is added to this variable. If the current statement is the first statement executed by this transaction, MySQL will also assign Query_id to the Xid of this transaction. Global_query_id is a pure memory variable and will be cleared after restarting. Therefore, in the same database instance, the XID of different transactions may be the same.

Innodb trx_id

InnoDB maintains a max_trx_id global variable internally. Every time a new trx_id needs to be applied for, the current value of max_trx_id is obtained, and then max_trx_id is increased by 1.

The core idea of ​​InnoDB data visibility is: each row of data records the trx_id that updates it. When a transaction reads a row of data, the way to determine whether the data is visible is through transaction consistency. The view is compared with the trx_id of this row of data. However, there are dirty reads in this process, so the ID will not be atomic and there is a possibility of duplication.

thread_id

In fact, thread id is the most common self-increasing id in MySQL. Usually when we check various scenes, the first column in show processlist is thread_id.

The logic of thread_id is easy to understand: the system saves a global variable thread_id_counter. Every time a new connection is created, thread_id_counter is assigned to the thread variable of this new connection.

The defined size of thread_id_counter is 4 bytes, so once it reaches 232-1 it resets to 0 and continues to increase. The result is the same as row_id, and the original record will be overwritten.

The above introduces some of MySQL's own auto-incrementing IDs. In fact, in actual application, we may also choose external auto-incrementing primary keys and then persist them to the database to replace the database's own auto-incrementing IDs. Increase id. Let’s talk about it below.

Redis self-increasing primary key

In fact, there are many ways to generate external self-increasing primary keys. Why should I introduce redis? Because I have found many advantages of it in practical applications.

Redis itself is atomic, so high concurrency is also thread-safe. Assuming the length of the primary key field is 20, we use the time as an auto-incrementing number to form the primary key, for example: an 8-digit date with a 12-digit auto-incrementing number. Then, according to the nature of the business, the time can be determined to be the year, month, day, or millisecond level. Then the probability of repeating the auto-increment number between milliseconds is extremely small, and it can be applied to basic businesses.

Summary

Several self-increasing IDs are introduced above. Each self-increasing ID has its own application scenarios, and its performance after reaching the upper limit is also different:

1, After the table's auto-increment id reaches the upper limit, its value will not change when applying again, which will lead to the error of primary key conflict when continuing to insert data
2, row_id reaches After reaching the upper limit, it will return to 0 and then increment again. If the same row_id appears, the data written later will overwrite the previous data
3, Xid only needs to not have duplicate values ​​in the same binlog file. Can. Although duplicate values ​​will theoretically appear, the probability is extremely small and can be ignored
4, InnoDB’s max_trx_id increment value will be saved every time MySQL is restarted, so the dirty read mentioned in our article The example is a bug that must appear. Fortunately, we still have plenty of time.
5. thread_id is the most common one we use, and it is also the best-handled auto-increment id logic.
6, redis external self-increment, millisecond level, theoretically there will be repeated values, but the probability is extremely small and can be ignored
7, In fact, each Self-increasing IDs have their own applicable scenarios, and you can choose according to specific scenarios in daily use. But be prepared for a rainy day, because the system running time and data storage must be taken into consideration. Comprehensive considerations, choose one that will not be repeated immediately during the system operation. Have you learned it?

Recommended learning: mysql video tutorial

The above is the detailed content of An in-depth analysis of how to solve the problem of MySQL running out of auto-incremented IDs. For more information, please follow other related articles on the PHP Chinese website!