MySQL clustered indexes and nonclustered indexes

Indexes are clustered and nonclustered, and the underlying implementations of the different storage engines in MySQL may be different for the index, focusing only on MySQL's default storage engine InnoDB.
Use the following command to view the default storage engine

show variables like ‘%storage_engine%‘;

Clustered index:

The logical order of the key values in the index determines the physical order of the corresponding rows in the table (the physical location of the data in the index is consistent with the order of the indexes), so it can be understood that the storage position of the data on the storage medium is contiguous as long as the index is contiguous.
For example: To find a word in the dictionary, we can find the word according to the pinyin in front of the dictionary, and note that the pinyin is arranged in order.

To make an analogy: when we want to find the word "ah" and then want to find the word "no", according to Pinyin, "B" must be in the back of "a".

Clustered index As we look at the dictionary according to the Order of pinyin, we can greatly improve the efficiency. When a range of values is searched frequently, the first data is found by index, and the characteristics are stored continuously according to the physical address, and then the adjacent data is retrieved until the condition is reached.

Nonclustered indexes

The logical order of the indexes is different from the physical storage order on disk . The key values of a nonclustered index are logically contiguous, but the physical order of the data in the table on the storage medium is inconsistent, i.e. the logical order of the records is not associated with the physical order of the actual storage. The indexed record node has a data pointer pointing to the true data storage location.

Nonclustered indexes like a dictionary based on the Radicals, the directories in front of the dictionary are logically contiguous, but the words in the dictionary are very unlikely to be next to each other when you look up two of the characters in the directory.

Here is a description of the index in the MySQL Documentation: Document description

Each InnoDB table has a special index called the data store of the row where the clustered index resides. Typically, a clustered index is a synonym for a primary key. From queries, insert performance best, and other database operations, it is important to understand that InnoDB uses a clustered index to optimize the most common query and DML operations for each table.
When you define the primary key of your table, InnoDB uses it as a clustered index. Define a primary key for each table that you create. If there is no logical unique and non-empty column or column set, add a new AutoIncrement column, and its value is automatically populated.
If you are unsure of your table's primary key, unique index, MySQL location first all key columns are not empty, InnoDB uses it as a clustered index.
If the table does not have a primary key or a unique index InnoDB internally, generate a hidden clustered index for the composite column that contains the row ID value gen_clust_index. The rows of the Id,innodb are assigned a sort of row in such a table. The row ID is a monotonically increasing of a 6-byte field when inserting a new row. Therefore, the lines of the row ID command are physically inserted in the order.

Summarized as follows:

• If a primary key is defined, then the primary key is a clustered index
• If no primary key is defined, then the first unique non-empty index of the table is used as a clustered index
• If there is no primary key and no suitable unique index, then InnoDB generates a hidden primary key as a clustered index, the hidden primary key is a 6-byte column, and the value of the column is increased as the data is inserted.

The InnoDB engine adds a clustered index to each table, and the data that the clustered index points to is stored in the physical disk order, and the self-increasing primary key automatically inserts the data backwards, avoiding the clustered index ordering problem during the insert process. If the clustered index is sorted, this can result in a very large disk IO performance loss.

MySQL clustered indexes and nonclustered indexes