01 preface

Hello, long time no update. Because recently in the interview. It took two weeks to prepare, and I got five offers within three days. Finally, I chose the offer from a unicorn in the Internet industry in Guangzhou, and I just started my job yesterday. These days, I have just sorted out the interesting questions I have been asked in the interview, and I would like to take this opportunity to share with you.

The interviewer for this company was interesting. On the one hand, he was a young man of his age, and we chatted for two hours (until my mouth was dry). He asked me a keng (b) question:

What should I do when I run out of autoid in the database?

In fact, this question can be divided into a primary key and no primary key two situations to answer.

International practice, let me show you a brain picture:

1.1 The past is wonderful

How do MySQL query statements execute?

MySQL index

MySQL logs

MySQL transactions with MVCC

MySQL locking mechanism

MySQL > create an index for a MySQL string

02 has a primary key

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

In MySQL, it is common to set the primary key to int. Int (-2147483648,2147483647); int (-2147483647,2147483647); int (-2147483647,2147483647,2147483647); The maximum value for the unsigned bit is 2^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;

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

If you continue with the following insert statement at this point:

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

The result is a primary key collision:

2.1 Solution

Although INT is 4 bytes, the maximum amount of data can store 2.1 billion. You might think that with so much capacity, you wouldn’t want to run out. But in the age of the Internet, which generates huge amounts of data every day, it is possible to do so.

So, our solution is to change the primary key type to BIGINT, which is 8 bytes. The maximum amount of data you can store is 2^64-1, which I lost count of. It should be enough for the rest of your life.

PS: The data volume of 2.1 billion in a single table is obviously unrealistic. Generally speaking, the data volume reaches 5 million.

03 no primary key

The other situation is when you create a table without setting a primary key. In this case, InnoDB will automatically create an invisible row_id with a length of 6 bytes. By default, it is unsigned, so the maximum length is 2^48-1.

In fact, InnoDB maintains a global dictsy. row_id, so all tables without a primary key are sharing the row_id, not exclusive to a single table. Each time you insert a piece of data, treat the global row_id as the primary key ID, and then increment the global row_id by 1.

What happens in this case when you run out of database self-incremented IDs?

Select * from table t where primary key is not set;

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

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 install

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

The image below is correct:

3, insert three data:

insert into t(age) values(1);
insert into t(age) values(2);
insert into t(age) values(3);

The database data at this time:

GDB change row_id to maximum value: 281474976710656

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

5. Insert three more data:

insert into t(age) values(4);
insert into t(age) values(5);
insert into t(age) values(6);

This matter database data:

Analysis:

  • Mysql > insert rows (1, 2, 3) where row_id = 1; That’s no problem.
  • Then set the ROW_ID to the maximum, followed by three inserts. The database result at this point is: 4, 5, 6, 3; You’ll notice that 1 and 2 are covered.
  • Select * from user where row_id = 0, row_id = 1, row_id = 2; Because the values row_id 1 and 2 already exist, the values 5 and 6 of the latter override the values where row_id 1 and 2 already exist.

Conclusion: When the row_id reaches the maximum value, it will start again from 0. The data inserted before will be overwritten by the data inserted later with no error.

04 summary

There are two situations when the database self-increment primary key is used up:

  • There is a primary key, report a primary key conflict
  • Without a primary key, InnDB automatically generates a global row_id. When it reaches its maximum value, it will start at 0. When it encounters the same row_id, the new data will overwrite the old data. So, let’s try to set the primary key for the table.

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

In addition to the above solutions, I also mentioned that in business development, we don’t wait until the day the primary key is used up to divide the library and the table. This is rarely the case.

At this time, the interviewer may ask you how to deal with the database and spreadsheet, if you don’t know, don’t take the initiative to mention, just click on it.

05 Reference Articles

  • 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 question & e-book

If you like this article, please help to have a look at it.

I don’t know what to send you when I first meet you. Just send hundreds of eBooks and the latest interview materials for 2021. WeChat search JavaFish reply ebook to send you 1000+ programming ebook; Send some interview questions in reply to the interview; 1024 sends you a complete set of Java video tutorials.

The interview questions are answered, and the details are as follows: If you need it, come and get it. It’s absolutely free.