take MySQL's InnoDB storage engine as an example, which is explained as follows:

each index contains different fields. A clustered index contains all fields, while a nonclustered index contains only index fields + primary key fields, so if you need to use other fields (including in the where condition or in the select clause) after using a nonclustered index, you need to go back to the clustered index to get other fields through the primary key index back to the table . If a nonclustered index satisfies all the fields of the SQL statement, it is called fully covered index , and there is no overhead of returning to the table.

returning a table is a process of re-querying a clustered index through the primary key field, so if a large number of records need to return to the table, the query cost will be higher than the cost of range scanning directly on the clustered index. So in some cases, it is more efficient not to use nonclustered indexes.

Why is it limited to the InnoDB storage engine? Because the MyISAM storage engine data file and index file are separate, there is no concept of clustered index.

for more details and principles, please refer to my recently published article: Database Index Integration