We recommend that you use the auto-increment ID unrelated to the business as the primary key for Innodb without special requirements.

We recommend that you use a business-independent auto-increment ID as the primary key for Innodb without special requirements.

We recommend that you use the auto-increment ID that is not related to the business as the primary key for Innodb without special requirements.

We recommend that you use auto-incrementing IDs that are not related to the business as the primary key (for innodb) without special requirements)

The InnoDB engine uses clustered indexes, and data records are stored on the leaf nodes of the primary index (a B + Tree. This requires that each data record in the same leaf node (the size is a memory page or disk page) be stored in the primary key order. Therefore, when a new record is inserted, mySQL inserts an appropriate node and location based on its primary key. if the page reaches the load factor (InnoDB defaults to 15/16), a new page (node) is created ).

If the table uses an auto-incrementing primary key, the records are added to the subsequent positions of the current index node each time a new record is inserted. when a page is full, A new page is automatically created. As shown in:

In this way, a compact index structure will be formed, and the approximate order will be filled up. Because existing data does not need to be moved during each insertion, the efficiency is very high and there will be no additional overhead for maintaining indexes.

If a non-auto-incrementing primary key is used (if the ID card number or student ID number is used), because the value of the primary key inserted each time is similar to a random value, each new record is inserted to a certain position in the middle of the existing index page:

At this time, MySQL had 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 the disk and cleared from the cache. at this time, it had to read back from the disk, this adds a lot of overhead, and frequent movement and paging operations cause a lot of fragmentation, resulting in a compact index structure. later, we had to use optimize table to recreate the TABLE and OPTIMIZE the page filling.

When using the InnoDB storage engine, if you do not have a special need, always use an auto-increment field that is not related to the business as the primary key.

Mysql is frequently updated and deleted, and fragments are generated. For tables with large fragments, the query efficiency is reduced. Optimize the table to make the query more efficient.

BitsCN.com