01 preface

Hello, it’s been a while. Because I’ve been interviewing. It took me two weeks to prepare, and I took 5 offers within 3 days. Finally, I chose a unicorn offer in an Internet industry in Guangzhou. I just entered the company yesterday. These days I just sorted out the interesting questions I was asked in the interview, and take this opportunity to share with you.

The interviewer of this company was a very interesting guy of the same age. We talked for two hours (until my mouth was dry). He asked me a keng question:

What should I do if I run out of autoincrement ids in the database?

This question can actually be answered with a primary key versus no primary key.

International practice. Here’s a brain map:

1.1 Past highlights

How do MySQL queries execute?

MySQL index

MySQL logs

MySQL transactions with MVCC

Lock mechanism of MySQL

How to design index of MySQL string?

02 has a primary key

If your table has a primary key, set the primary key to increment.

In MySQL, we usually set the primary key to int. In MySQL, int takes 4 bytes. As a sign bit, the range is [-2^31, 1,2^31-1]. The unsigned bit is 2 to the 32 minus 1, which is 4294967295.

Create a table with signed bits:

CREATE TABLE IF NOT EXISTS `t`(
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `url` VARCHAR(64) NOT NULL.PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Insert a value with the maximum id 2147483647, as shown below:

If you continue with the following insert statement:

INSERT INTO t (url) VALUES ('wwww.javafish.top/article/erwt/spring')
Copy the code

The result is a primary key conflict:

2.1 Solutions

Although int is 4 bytes, the maximum amount of data can be stored 2.1 billion. You might think that with all that capacity, you wouldn’t run out. But in the Internet age, which generates vast amounts of data every day, it is quite possible.

So, our solution is to change the primary key type to Bigint, which is 8 bytes. The maximum amount of data that can be stored is 2^64-1, which I can’t even count. It should be enough for the rest of your life, anyway.

PS: The data volume of 2.1 billion in a single table is obviously unrealistic. Generally speaking, when the data volume reaches 5 million, it should be divided into tables.

03 no primary key

The other case is when the table is created without a primary key. In this case, InnoDB will automatically create an invisible row_id of 6 bytes in length, which is unsigned by default, so the maximum size is 2^48-1.

In fact, InnoDB maintains a global dictsys.row_id, so tables with undefined primary keys share this row_id, not a single table. For each insert, the global row_id is used as the primary key ID, and the global row_id is incremented by 1.

What happens in this case when we run out of database increment ids?

Select * from table t where primary key is set;

CREATE TABLE IF NOT EXISTS `t`(
   `age` int(4) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

2, through the ps – ef | grep mysql command to get mysql process ID, and then execute the command, by GDB row_id first changed to 1. PS: No GDB, Baidu installation under

sudo gdb -p 16111 -ex 'p dict_sys->row_id=1' -batch
Copy the code

The following is correct:

3. Insert three pieces of data:

insert into t(age) values(1);
insert into t(age) values(2);
insert into t(age) values(3);
Copy the code

Database data at this point:

4. GDB changes row_id to maximum value: 281474976710656

sudo gdb -p 16111 -ex 'p dict_sys->row_id=281474976710656' -batch
Copy the code

5, insert three more data:

insert into t(age) values(4);
insert into t(age) values(5);
insert into t(age) values(6);
Copy the code

Database data on this matter:

Analysis:

  • Row_id = 1; row_id = 1; row_id = 1; row_id = 1; That’s ok.

  • Next, set row_id to the maximum value, followed by inserting three pieces of data. The database result is: 4, 5, 6, 3; You’ll see that 1 and 2 are covered.

  • The row_id of 4, 5, and 6 are 0, 1, and 2 respectively. Since row_id 1 and row_id 2 already exist, the latter values 5 and 6 will override row_id 1 and row_id 2.

Conclusion: If row_id reaches its maximum value, start from 0. The data inserted before is overwritten by the data inserted after, and no error is reported.

04 summary

When the auto-increment primary key is used up, there are two cases:

  • Primary key conflict was reported. Procedure
  • Without a primary key, InnDB automatically generates a global row_ID. When the row_id is the same, the new data overrides the old data. So, let’s try to set the primary key for the table.

Why do I say this is a keng (b) problem?

In addition to the above solutions, I also mentioned that in business development, we do not wait until the primary key is used up and then we have to divide the database table. This is rarely the case.

At this point, the interviewer may ask you what to do with the score sheet. If you don’t know it, don’t ask.

05 Reference article

  • blog.csdn.net/weixin_39640090/article/details/113227742
  • blog.csdn.net/qq_35393693/article/details/100059966
  • time.geekbang.org/column/article/69862

06 big factory interview questions & e-books

If you read here and liked this article, please do something nice.

I don’t know what to get you for the first time. Just send hundreds of e-books and the latest interview materials for 2021. Wechat search JavaFish reply ebook send you 1000+ this programming ebook; Send some interview questions; Reply 1024 send you a complete set of Java video tutorials.

Interview questions have answers, detailed below: need to take it, absolutely free, no routine access.