Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the self-adding id is, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

When using MySQL to build a table, we usually create a self-incrementing field (AUTO_INCREMENT) and use it as the primary key.

Note: what is discussed in this article is based on the Innodb storage engine.

Why does 1.MySQL recommend setting the self-incrementing id as the primary key?

If we define a primary key (PRIMARY KEY), then InnoDB will select the primary key as the clustered index, if there is no explicit definition of the primary key, InnoDB will select the first unique index that does not contain null values as the primary key index, and if there is no such unique index, InnoDB will choose the built-in 6-byte ROWID as the implicit clustered index (ROWID increments as the row records are written, this ROWID is not as referable as ORACLE's ROWID Is implied).

The data record itself is stored on the leaf node of the main index (a B+Tree). This requires that the data records within the same leaf node (the size is a memory page or a disk page) are stored in primary key order, so every time a new record is inserted, MySQL will insert it into the appropriate node and location according to its primary key, and if the page reaches the load factor (InnoDB default is 15top 16), a new page (node) will be opened.

If the table uses a self-incrementing primary key, each time a new record is inserted, the record will be sequentially added to the subsequent position of the current index node, and when a page is full, a new page will be automatically opened

If you use a non-self-increasing primary key (such as ID number or student number, etc.), because the value of each inserted primary key is approximately random, each new record will be inserted somewhere in the middle of the existing index page, and MySQL will have to move the data in order to insert the new record to the appropriate location, and even the target page may have been written back to disk and cleared from the cache, and then read back from the disk. This increases a lot of overhead, while frequent movement and paging operations cause a large number of fragments, resulting in an index structure that is not compact enough. Later, we have to use OPTIMIZE TABLE to rebuild the table and optimize the fill page.

To sum up: access efficiency is highest when we use self-incrementing columns as primary keys.

two。 Does the self-increasing column id have to be continuous?

Self-increasing id is not necessarily continuous.

Let's first take a look at MySQL's self-increment preservation strategy:

In fact, the self-increment of the InnoDB engine is stored in memory, and it is not until MySQL version 8.0 that it has the ability of "self-increment persistence", that is, "if a restart occurs, the self-increment of the table can be restored to the value before MySQL restart". The details are as follows:

In MySQL version 5. 7 and earlier, self-increment is kept in memory and not persisted. After each restart, when the table is opened for the first time, it will find the maximum self-added value max (id), and then take max (id) + 1 as the current self-increment of the table. Phellodendron mandshurica (Thunb.)

For example, if the largest id in the current data row of a table is 10. At this point, we delete the line of id=10, and AUTO_INCREMENT is still 11. However, if you restart the instance immediately, the AUTO_INCREMENT of the table will become 10 after the restart. Phellodendron mandshurica (Thunb.)

That is, a MySQL restart may change the value of a table's AUTO_INCREMENT.

In MySQL version 8.0, self-increment changes are recorded in redo log, and the restart relies on redo log to restore the pre-restart values.

The discontinuity of self-increasing id may be caused by:

1. Unique key conflict

two。 Transaction rollback

3.insert... Select statement batch application self-increment id

3. Is there an upper limit for self-increasing id?

Self-increment id is an integer field. We often use the int type to define the growth id, while the int type has an upper limit, that is, the growth id also has an upper limit.

The following table lists the ranges of int and bigint field types:

Type size range (signed) range (unsigned) int4 bytes (- 2147483648instruction 2147483647) (0re4294967295) bigint8 bytes (- 9223372036854775808) 9223372036854775807) (01844674407709551615)

As can be seen from the above table: when the self-increasing field uses the int signed type, the maximum can reach 2147483647 or more than 2.1 billion; when using the int unsigned type, the maximum can reach 4294967295 or more than 4.2 billion. Of course, bigint can represent a wider range.

Let's test what happens when you insert data again when the self-increasing id reaches its maximum:

Create table t (id int unsigned auto_increment primary key) auto_increment=4294967295;insert into t values (null); / / successfully insert a line of 4294967295show create table tTrachap * CREATE TABLE `t` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=4294967295;*/insert into t values (null)); / / Duplicate entry '4294967295' for key 'PRIMARY'

It can be seen from the experiment that when the self-increment id reaches the maximum, it cannot be expanded. After the first insert statement inserts data successfully, the AUTO_INCREMENT of the table does not change (still 4294967295), which causes the second insert statement to get the same self-increment id value, and then try to execute the insert statement, reporting the primary key conflict error.

4. About self-addition, how do we maintain it?

The following two suggestions are mainly provided in terms of maintenance:

1. Field type selection: it is recommended to use int unsigned type. If you can predict that the amount of data in this table will be very large, you can use bigint unsigned type instead.

two。 Pay more attention to the self-appreciation of large tables to prevent primary key overflow.

About how the id is shared here, I hope the above content can help you to some extent, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.