Primary Key Index of MySQL index,

Primary Key Index of MySQL index,

In MySQL, what are the meanings and differences between primary key indexes and secondary indexes?
In the previous sharing, we introduced the differences between clustered indexes and non-clustered indexes. This time we will continue to introduce the differences between primary key indexes and secondary indexes.

1. Primary Key Index

A primary key index (referred to as a primary key) is a primary key consisting of one or more columns used to uniquely identify a record in a data table. A table can have no primary key, but can have only one primary key at most, and the primary key value cannot contain NULL.

In MySQL, the primary key design of the InnoDB data table follows several principles:

1. Use an auto-increment attribute column that has no business purpose as the primary key;
2. The primary key field values are always not updated. There are only two operations: add or delete;
3. Do not select the type that will be dynamically updated, such as the current timestamp.

The benefits of doing so are as follows:

1. When new data is added, the probability of innodb page split is reduced because the primary key value increases sequentially; you can refer to the previous "[MySQL FAQ] series-why is it recommended to use an auto-incrementing column as the primary key in the InnoDB table ";
2. When the business data changes, the primary key value is not modified. The probability of physical storage location changes is reduced, and the probability of fragmentation in innodb page is also reduced.
Because the MyISAM table is a heap organization table, you don't need to pay attention to the primary key type design.

2. Secondary Index

SECONDARY indexes are the indexes we normally refer to. The original text is the secondary key. Secondary indexes can be further divided into unique indexes rather than unique indexes.

A unique index is actually called a uniqueness constraint. Its function is to avoid repeated values in one or more columns. It is a binding index.

3. Differences between primary key indexes and secondary Indexes

In the MyISAM engine, except for the NULL key value, the unique index has no essential difference with the primary key index. That is to say, in MyISAM engine, unique indexes with NULL values are not allowed, which is essentially the same as primary key indexes.

In the InnoDB engine, primary key indexes differ greatly from secondary indexes. Primary key indexes will be selected as clustered indexes, and the unique indexes and common secondary indexes have no essential difference in storage except uniqueness constraints.

In terms of query performance, the query performance of the primary key index and the unique index that does not allow NULL in the MyISAM table is equivalent, in an InnoDB table, you need to perform one more conversion from the secondary index to the primary key index. InnoDB tables have a higher cost of searching based on common indexes, because each retrieval result requires at least one more retrieval to confirm whether there are more matching results, this is not required for primary key indexes and unique indexes.

After testing, we performed random search (integer type) on MyISAM of 1 million rows of data. The efficiency of the primary key is basically the same as that of the unique index, while that of the general index is more than 30% slower. For an InnoDB table, the unique index is 9% less efficient than the primary key index, and the general index is more than 50% slower than the primary key index.

If you want to know about all aspects of MySQL, you can leave a message and reply to them. I will select some hot topics to share with you. At the same time, I hope you can forward more data. Reading more is a great help for Lao Ye's continued efforts to share it. Thank you :)