The difference between mysql clustered index and non-clustered index

The difference between mysql clustered index and non-clustered index is: for clustered index, table data is stored in order, that is, the index order is consistent with the physical storage order of table records; for non-clustered index, table data is stored The order has nothing to do with index order.

Introduction to the difference between mysql clustered index and non-clustered index:

(Recommended tutorial: mysql tutorial)

Clustered index:

• Table data is stored in order, that is, the index order is consistent with the physical storage order of table records.

• Clustered index leaf nodes store data rows and B-Tree indexes.

• There can only be one clustered index in a table, because there can only be one real physical storage order.

• The clustered index is a sparse index, and the upper-level index storage of the data page is a page pointer, not a row pointer.

Non-clustered index:

• The storage order of table data has nothing to do with the index order.

• For a non-clustered index, the leaf node contains the row primary key value and the primary key value as a "pointer" to the row, through the secondary index lookup, two B-Tree lookups InnoDB Adaptive hash indexes can reduce this duplication of work.

• Non-clustered index is a dense index, which stores a record for each data row on the upper-level index page of the data page.

The above is the detailed content of The difference between mysql clustered index and non-clustered index. For more information, please follow other related articles on the PHP Chinese website!