MySQL Index summary (2)-mysql clustered index and non-clustered index

A clustered index is an algorithm that re-organizes actual data on disk to sort by the values of a specific one or more columns

The characteristic is that the order of storing data is consistent with the index order in general, the primary key will generate a clustered index by default and a table with only one clustered index

The differences between clustered and non-clustered indexes are:

The leaf node of the clustered index is the data node, but not the clustered index. The leaf node is still an index file, only the index file contains pointers to the corresponding data blocks.

Different data storage engines in MySQL have different support for clustered indexes

Non-clustered index used by MyISAM

Raw data

Storage mode

The leaf node that organizes the index by column value and row number is actually a pointer to the block that holds the data

You can also see the index file of the MyISAM from the physical file. Myi and data files. MyD is separate storage is relatively independent

For the InnoDB engine, the data is stored in the form of a clustered index

Its leaf nodes for each clustered index contain primary key values, transaction IDs, rollback pointers (for transactions and MVCC), and the remaining columns.

From the physical file, you can also see that the data file for InnoDB is only a structure file. frm and data files. IDB where the. IDB holds data and index information stored together

InnoDB's Level Two index and primary key index also have a large different two-level index that holds the primary key value instead of the row pointer, which reduces the overhead of maintaining a two-level index when moving data or splitting, because the row pointers that do not need to update the index

Comparison of level Two indexes of MyISAM and InnoDB

It can be seen that the leaf node of the InnoDB level two index holds the key field + primary key value, so the primary key value is first found through a two-level index, and the corresponding data file is found in the Zhu Jian index based on the primary key value.

MyISAM's two-level index holds a combination of column values and row numbers. The leaf node holds a pointer to the physical data, so there is no difference between its primary index and the structure of the two-level index, except that the index value of the primary key index is unique and non-null, and the MyISAM engine can not set the primary key.

InnoDB engine is required to set the primary key, need to rely on the primary key to generate the clustered index, so when the primary key is not specified, the InnoDB engine will default to look for a column that uniquely identifies each row of data as the primary key, when such a column does not exist, will default to generate a 6-byte integer hidden column as the primary key

MySQL Index summary (2)-mysql clustered index and non-clustered index