What to do if the auto-increment primary key in MySQL is used up

In the interview, you should have experienced the following scenarios

Interviewer: "You have used MySQL before, right? Do you use auto-increment primary keys or UUIDs?"

You: "I used an auto-increment primary key"

Interviewer: "Why is it an auto-increment primary key?"

You: "Because the auto-increment primary key is used, the data is stored sequentially in the physical structure, and the performance is the best, blabla..."

Interviewer: "What if the auto-increment primary key reaches the maximum value and is used up?"

You: "What? I didn't review!!" (Then, you can go back and wait for notification!)

This question was asked to me by a fan, and I think意(KENG)思(B)!
So, today we are going to talk about what to do when this auto-increment primary key is used up!

text

Simple version

Let's first understand that in MySQL, the range of Int integers is as follows

Let’s take an unsigned integer as an example. The storage range is 0 to 4294967295, which is about 4.3 billion! Let's first say that once the auto-increment id reaches the maximum value, if data continues to be inserted, a primary key conflict exception will be reported as follows

//Duplicate entry '4294967295' for key 'PRIMARY'

The solution is also very simple, change the Int type to BigInt type, the range of BigInt is as follows

Even if you have 10,000 data items per second and run it for 100 years, the data in a single table will only be
10000*24*3600*365*100=31536000000000
This number is still far from the upper limit of BigInt, so if you set the auto-increment ID to BigInt type, you don’t have to worry about the problem of the auto-increment ID reaching the maximum value!
However, if your answer in the interview is

You: "It's easy. Just change the type of the auto-increment primary key to BigInt!"

Next, the interviewer can ask you a more tricky question!

Interviewer: "How do you modify the data type of a column online?"

You: "What! I'll just wait for notification!"

How to change

At present, there are three solutions for online modification of table structure in the industry. As far as I know, there are generally the following three

Method 1: Use the online modification function provided by MySQL 5.6+

The so-called functions provided by MySQL itself are MySQL's own native statements. For example, if we want to modify the original field name and type.

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

Well, before MySQL 5.5, this was achieved by copying temporary tables. After executing the ALTER statement, a temporary table with a new structure will be created, all the data in the original table will be copied to the temporary table, and then Rename will be performed to complete the creation operation. In this process, the original table is readable but not writable.
Starting from 5.6+, MySQL supports online modification of database tables. During the process of modifying the table, for most operations , the original table can be read and written.
So, for operations like changing the data type of a column, can the original table still be written? Come on, I went to the official website to find a picture of the MySQL 8.0 version

As shown in the figure, concurrent DML operations are not supported for operations such as modifying data types! In other words, if you directly use statements such as ALTER to modify the table data structure online, this table will become unable to perform update operations ( DELETE , UPDATE , DELETE ).
Therefore, direct ALTER is not acceptable!

Then we can only use method 2 or method 3

Method 2: Using third-party tools

There are some third-party tools in the industry that can support online modification of table structure. Using these third-party tools, the table will not be blocked when you perform ALTER operations! There are two more famous ones

• pt-online-schema-change , pt-osc for short
• GitHub officially announced the release of the tool in an open source format, called gh-ost

Taking pt-osc as an example, its principle is as follows

1. Create a new table with the modified data table structure, which is used to import data from the source data table into the new table.

2. Create a trigger to record the operations of continuing to modify the data on the source data table after the data is copied. After the data is copied, execute these operations to ensure that the data is not lost.

3. Copy data from the source data table to the new table.

4. Rename the source data table to the old table, rename the new table to the source table name, and delete the old table.

5. Delete the trigger.

However, these two意(KENG)思(B) tools actually. . . Surprisingly. . . well! If your table has triggers and foreign keys, these two tools will not work!
If you really encounter triggers and foreign keys in the database, you can only use force. Please see method 3.
Method 3: Change the slave table structure and then switch between master and slave <br /> This method is extremely troublesome and requires professional players to operate. Because our MySQL architecture is generally a read-write separation architecture, the slave is used for reading. We modify the table structure directly on the slave database without blocking the read operation of the slave database. After the modification, you can switch between master and slave. The only thing you need to pay attention to is that data may be lost during the master-slave switching process!

Advanced version

In fact, after answering the above questions, this article is almost finished. But remember what I said at the beginning. This is a very意(KENG)思(B) , why?
Suppose that the auto-increment field in your table is of signed Int type, that is, your field range is -2147483648 to 2147483648.
Everything happens just right. Your auto-increment ID starts from 0, which means that now your available range is 0 to 2147483648.
Let's be clear that there will definitely be some surprises in the real data IDs in the table, and the IDs are not necessarily continuous. For example, the following situation occurs

CREATE TABLE `t` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`),
) EN

Execute the following SQL

insert into t values(null);
// The row inserted is (1)
begin;
insert into t values(null);
rolllack;
insert into t values(null);
// The row inserted is (3)

Therefore, the real ID in the table will inevitably be discontinuous.
OK, now the data range of your auto-increment primary key id is 0 to 2147483648, which means 2.1 billion records in a single table! Taking into account the discontinuity of IDs, the actual data is probably at most 1.8 billion.
Bro, there are already 1.8 billion records in a single table, why don't you split it into different databases and tables? Once you have split the database and tables, you can no longer rely on the auto-increment ID of each table to globally uniquely identify the data. At this point, we need to provide a globally unique ID number generation strategy to support the environment of sharding libraries and tables.

Therefore, in practice, you can never wait until the auto-increment primary key is used up! Therefore, the professional answer is as follows:

Interviewer: "What if the auto-increment primary key reaches the maximum value and is used up?"

You: "I have never encountered this problem, because we use int type for the auto-increment primary key, which usually cannot reach the maximum value, so we split the database and table, so I have never encountered it!"

This is the end of this article about what to do when the auto-increment primary key in MySQL is used up. For more information about running out of MySQL auto-increment primary keys, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!