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

This article mainly shows you "Mysql self-increasing id example analysis", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "Mysql self-increasing id example analysis" this article bar.

Introduction:

When creating tables with MySQL, we usually create an auto-increment field (AUTO_INCREMENT) and use this field as the primary key. This article will tell you everything about self-increasing id in the form of questions and answers.

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

Why does MySQL recommend setting self-adding column id as primary key?

If we define the primary key (PRIMARY KEY), then InnoDB will select the primary key as the clustered index. If there is no explicit primary key definition, InnoDB will select the first unique index that does not contain NULL values as the primary key index. If there is no such unique index, InnoDB will select the built-in 6-byte ROWID as the implicit clustered index (ROWID increases with the writing of row records, and this ROWID is not as referenceable as ORACLE ROWID.).

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

If the table uses a self-increasing primary key, each time a new record is inserted, the record is sequentially added to the subsequent position of the current inode, and when a page is full, a new page is automatically opened.

If you use a non-self-increasing primary key (If ID number or student number, etc.), because the value of each inserted primary key is approximately random, each time a new record is inserted into a certain position in the middle of the existing index page, MySQL has to move the data in order to insert the new record into the appropriate position, and even the target page may have been written back to disk and cleared from the cache. At this time, it has to be read back from disk, which increases a lot of overhead. At the same time, frequent movement and paging operations cause a lot of fragmentation. The resulting index structure was not compact enough, and subsequently had to be optimized by OPTIMIZE TABLE to rebuild the table and optimize the fill page.

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

2. Is self-increasing column id necessarily continuous?

Self-increasing id is not necessarily continuous growth.

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

The self-increment of InnoDB engine is actually stored in memory, and only after MySQL version 8.0 does it have 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 specific situation is as follows:

In MySQL 5.7 and earlier, self-increment values are stored in memory and are not persisted. After each restart, the first time the table is opened, it will find the maximum value of the self-increment max(id), and then max(id)+1 will be the current self-increment of this table.

For example, if the largest id in the current row of a table is 10, AUTO_INCREMENT=11. At this point, we delete the row with id=10, AUTO_INCREMENT or 11. But if you restart the instance right away, the AUTO_INCREMENT of this table will become 10 after restart.

That is, a MySQL restart might modify the AUTO_INCREMENT value of a table.

In MySQL version 8.0, self-increment changes are recorded in the redo log, and the redo log is used to restore the values before the restart.

Examples of self-increasing id discontinuity include:

1. unique key conflict

2. Transaction Rollback

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

3. Is there an upper limit to self-increasing ID?

Self-increasing id is an integer field, we often use int type to define the growth id, and int type has an upper limit, that is, the growth id also has an upper limit.

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

Type Size Range (Signed) Range (Unsigned) int4 bytes (-2147483648,2147483647)(0,4294967295)bigint8 bytes (-9223372036854775808,922337203685475807)(0,1844674073709551615)

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

Let's test what happens when the autoincrement id reaches its maximum and inserts data again:

create table t (id int unsigned auto_increase primary key) auto_increase =4294967295;insert into t values(null);//insert a line successfully 4294967295show create table t;/* 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-increasing id reaches the maximum, it cannot be extended. After the first insert statement inserts data successfully, the AUTO_INCREMENT of this table does not change (or 4294967295), which leads to the second insert statement getting the same self-increasing id value again. Then try to execute the insert statement and report a primary key conflict error.

4. How do we maintain self-addition?

The maintenance mainly provides the following two suggestions:

1. Field type selection: It is recommended to use int unsigned type. If it can be predicted that the data volume of the table will be very large, it can be changed to bigint unsigned type.

2. Pay more attention to the self-increment of large tables to prevent primary key overflow.

The above is "Mysql self-increasing id sample analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.