Why does MySQL primary key increase automatically?

The reasons for mysql primary key auto-increment: 1. The data record itself is stored on the leaf node of the primary index; 2. MySQL will insert it into the appropriate node and location based on its primary key; 3. The table uses auto-increment Primary key, each time a new record is inserted, the record will be added sequentially to the subsequent position of the current index node.

The reason why MySQL uses auto-incrementing primary keys is because InnoDB tables are very convenient to use with it and the efficiency is significantly improved.

Recommended courses: MySQL Tutorial.

Features of InnoDB engine table

1. InnoDB engine table is an index-organized table (IOT) based on B-tree

About B-tree

Characteristics of B tree:

All keywords appear in the linked list of leaf nodes (dense index), and the keywords in the linked list happen to be Sequential;

It is impossible to hit on non-leaf nodes;

Non-leaf nodes are equivalent to the index of leaf nodes (sparse index), and leaf nodes are equivalent to storage (keywords) Data layer of data;

2. If we define the primary key (PRIMARY KEY), then InnoDB will select the primary key as the clustered index. If the primary key is not explicitly defined, InnoDB will select the first one that does not contain NULL. The unique index of the value is used as the primary key index. If there is no such unique index, InnoDB will choose the built-in 6-byte long ROWID as the implicit clustered index (ROWID is incremented as the row record is written and the primary key is incremented. This ROWID is not like ORACLE's ROWID can be quoted, which is implicit).

3. The data record itself is stored on the leaf node of the main index (a B Tree). This requires that each data record in the same leaf node (the size of one memory page or disk page) is stored in primary key order, so whenever a new record is inserted, MySQL will insert it into the appropriate node based on its primary key. and position, if the page reaches the loading factor (InnoDB default is 15/16), a new page (node) will be opened

4. If the table uses an auto-increasing primary key, then every time a new record is inserted, the record It will be added sequentially to the subsequent position of the current index node. When a page is full, a new page will be automatically opened

5. If a non-auto-increasing primary key is used (if the ID number or student number, etc.) , since the value of the primary key inserted each time is approximately random, each new record must be inserted somewhere in the middle of the existing index page. At this time, MySQL has to move the data in order to insert the new record into the appropriate location, and even The target page may have been written back to the disk and cleared from the cache. At this time, it needs to be read back from the disk, which adds a lot of overhead. At the same time, frequent moving and paging operations cause a lot of fragments, resulting in a less compact image. The index structure requires OPTIMIZE TABLE to rebuild the table and optimize the filling page.

To sum up, if the data writing order of the InnoDB table can be consistent with the order of the leaf nodes of the B-tree index, the access efficiency will be the highest at this time, which is the access efficiency in the following situations. Highest:

Use an auto-incrementing column (INT/BIGINT type) as the primary key. At this time, the writing order is auto-incrementing, which is consistent with the splitting order of B number of leaf nodes;

This table does not specify The auto-increment column is used as the primary key, and there is no unique index that can be selected as the primary key (the above condition). At this time, InnoDB will choose the built-in ROWID as the primary key, and the writing order is consistent with the ROWID growth order;

In addition, if an InnoDB table does not display a primary key, there is a unique index that can be selected as the primary key, but the unique index may not be in an incremental relationship (such as string, UUID, multi-field joint unique index) , the access efficiency of the table will be relatively poor.

The above is the detailed content of Why does MySQL primary key increase automatically?. For more information, please follow other related articles on the PHP Chinese website!