Detailed explanation of MySQL clustered index and non-clustered index

1. Clustered Index

Table data is stored in the order of the index, that is, the order of the index items is consistent with the physical order of the records in the table. For a clustered index, the leaf nodes store the actual data rows , and there are no separate data pages.

You can create at most one clustered index on a table because there can only be one physical order for the actual data.

It can also be seen from the physical files that the data files of InnoDB (clustered index) only have the data structure file .frm and the data file .idb. The data and index information are stored together in .idb.

2. Non-clustered index

The order in which table data is stored has nothing to do with the order of indexes. For a non-clustered index, the leaf node contains the index field value and a logical pointer to the data page data row , and the number of rows is consistent with the amount of data in the data table.

It can also be seen from the physical file that the MyISAM (non-clustered index) index file.MYI and the data file.MYD are stored separately and are relatively independent.

Summarize:

The difference between clustered index and non-clustered index is:

The leaf nodes of the clustered index (innoDB) are data nodes;

The leaf nodes of non-clustered indexes (MyISAM) are still index files, but the index files contain pointers to the corresponding data blocks.

For a non-clustered index, each time the required row number is retrieved through the index, it is necessary to fetch data from the disk (return the row) through the disk address on the leaf, which consumes time. In order to optimize the time for fetching data, the InnoDB engine uses a clustered index.
Clustered index, that is, storing data on the index leaf pages. For the InnoDB engine, the leaf page no longer stores the address corresponding to the row, but directly stores the data.

This avoids the time consumption caused by the return operation. This makes InnoDB even faster than MyISAM on some queries!

ps. Regarding query time, it is generally believed that MyISAM sacrifices functionality for performance, and queries are faster. But this is not necessarily the case. In most cases, MyISAM is indeed faster than InnoDB. However, query time is affected by many factors. The reason why InnoDB queries slow down is because it supports transactions, rollbacks, etc., so that the leaf pages of InnoDB actually contain the transaction id (in other words, the version number) and the rollback pointer.

In short, clustered indexes are not suitable for frequently updated columns, frequently modified indexed columns, and small numbers of distinct values.

The above is a detailed explanation of MySQL clustered index and non-clustered index. For more information about MySQL clustered index and non-clustered index, please pay attention to other related articles on 123WORDPRESS.COM!