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

Non-clustered index

The leaf page of an index node is like a leaf. The leaf head is the index key value.

Create a table first:

CREATE TABLE 'userINTnotNULLVARCHARnot  nullVARCHARnotnull);

For the MyISAM engine, if the ID is created and the name is indexed. For the following query:

Select *  from User where = 1

The index is used to quickly retrieve the ID in the index tree, but to get the ID corresponding to the row data, you must find where the row data is stored on the hard disk, so the index on the leaf page of the MyISAM engine not only stores the primary key ID but also stores the address information for the data store.

An index such as this is called a non-clustered index.

A two-level index of a nonclustered index is similar to a primary key index. If we add an index to name, then the index leaf of name will be the following structure:

Clustered index

For non-clustered indexes, each time the desired line number is retrieved through the index, the disk address on the leaf is used to take the data back to the disk (the return line) to consume time. To optimize this part of the return row fetch data time, the InnoDB engine uses the clustered index.
Clustered index, the data will be stored on the index leaf page. For the InnoDB engine, the leaf page no longer stores the corresponding address of the row, but rather the data is stored directly:

This avoids the time consuming of the back-row operation. Make InnoDB in some queries than MyISAM even faster!

PS. About query time, it is generally thought that MyISAM sacrificed the function for performance, query faster. But that is not necessarily the case. In most cases, MyISAM is indeed faster than InnoDB. However, the query time is affected by many factors. InnoDB queries are slow because they support transactions, rollback, and so on, so that the InnoDB leaf page actually contains the transaction ID (in other words, the version number) and the rollback pointer.

There is a big difference between InnoDB and MyISAM in the level two index.

InnoDB sets the clustered index on the primary key by default. If you do not specify a primary key, InnoDB replaces it with an index that has a unique and non-null value. If such an index does not exist, InnoDB defines a hidden primary key and then establishes a clustered index on it. In general, InnoDB stores the actual data in the form of a clustered index, which is the basis for other two-level indexes.

Assuming that the table name field is indexed on the InnoDB engine, the name Index leaf page will only store the primary key ID:

When retrieving, the primary index ID is found through the name Index tree, and the data is fetched by ID on the clustered Index leaf page of the primary index tree.

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