InnoDB PRIMARY KEY Design

InnoDB is Clustered-index table, so for InnoDB, the primary key has a special meaning. You can navigate directly to the physical location of the corresponding data row record by the primary key, the primary key index points to the corresponding row record, and the other indexes to the primary key index; So, so to speak, InnoDB is actually a B-tree index, the B-Tree index is the primary key, and its value is the corresponding row record.
In the InnoDB data sheet design, we need to pay attention to several points:

• 1. Explicitly define a primary key for an INT type self-increment field that can be used only for the master key and not for other purposes
• 2. If you do not explicitly define a primary key, you may cause InnoDB to sort new rows of data each time, severely compromising performance
• 3. Try to ensure that the primary key field is not updated to prevent changes in the primary key fields, causing data storage fragmentation and reducing IO performance
• 4. If you need to update the primary key field, turn the field into a unique index constraint field and create a self-increment field with no other business meaning.
• 5. Primary key field type as small as possible, can use smallint without int, can use int without bigint
• 6. Primary key fields are placed in the first order of the data table

InnoDB PRIMARY KEY Design