This is my first day of the genwen challenge.

source

It started when I touched the fish at the boiling point and saw this problem and thought it was interesting. Although I have been doing development for several years, I have never thought about this problem. On second thought, it seems to be excusable.

Bigint is 8 bytes in mysql, that is to say, its maximum limit is 2^64. With this order of magnitude, it has already exceeded the processing capacity of mysql single table.

Normally, when the amount of data in mysql reaches tens of millions, the performance will start to decline, and it is necessary to start optimizing the library table, among which the biggest performance bottleneck should be SQL query.

Even after a series of optimizations, when the amount of data reaches the billion level, it is basically the limit of mysql. At this time, without separate tables and libraries, business operations are not sustainable. So even if you use an incremented ID of type int, 4 bytes, it has a limit of over 2.1 billion. When the data reaches this level, we have already begun to consider doing table and library, but we all know that table and library, must not be able to use the increment ID, because at this time, the use of the increment ID can not guarantee the uniqueness of the increment ID. Typically we would consider using something like a snowflake algorithm to generate system ids

Having said all that, we can conclude that the interviewer has too much time on his hands! (Manual dog head)

What happens when you run out of autoincrement ids and continue to insert data?

In fact, this can also be divided into two situations to analyze:

  1. The auto-increment primary key ID is set

In this case, the primary key duplicate error is reported after the increment id runs out

Duplicate entry '*****' for key 'PRIMARY'

Note that another error is reported after mysql5.7

SQL Error (167): Out of range value for column 'id'.

Therefore, in this case, it is possible to report data out of bounds error, or increment id conflict error.

  1. No auto-increment primary key ID is set

InnoDB automatically creates a 6-byte row_id for you if the primary key is not set. Since row_id is unsigned, the maximum length is 2^48-1.

In this case, the data overwrites the data starting at subscript 0.

You can set your own row_id to verify this.

Thanks for reading.