MySQL overwrite index covering or index coverage

Combined index

Referring to the combined index, we all know the "leftmost prefix" principle. For example, creating an index Idx_name_age (name,age), typically where age=50 or where age>50, is not used to idx_a_b. Is there any special situation?
Suppose the table is:
CREATE TABLE users (
idInt (ten) unsigned not NULL auto_increment,
namevarchar (255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
emailvarchar (255) COLLATE Utf8mb4_unicode_ci not NULL,
passwordvarchar (255) COLLATE Utf8mb4_unicode_ci not NULL,
remember_tokenvarchar (COLLATE) utf8mb4_unicode_ci DEFAULT NULL,
created_atTimestamp null DEFAULT NULL,
updated_atTimestamp null DEFAULT NULL,
ageInt (one) DEFAULT NULL,
PRIMARY KEY ( id ),
UNIQUE KEY users_email_unique ( email ),
KEY idx_name_age ( name , age )
) Engine=innodb auto_increment=1 DEFAULT charset=utf8mb4 collate=utf8mb4_unicode_ci;

Overwrite Index

The InnoDB storage engine supports the overwrite index (covering index), or index coverage (index coverage), which is the record that can be traced from the secondary index, rather than querying the records in the clustered index.
The benefit of using an overlay index is that the secondary index does not contain all the information for the entire row of records, so its size is much smaller than the clustered index, so it can reduce the number of IO operations.

Explain of several SQL statements

• Explain SELECT name from Test.users where age>50
  

Possible_keys is null, which means that there is really no index available.
The key is idx_name_age, which means the optimizer is out of the picture, and it chooses the idx_name_age level two index.
Note that the Select field is name and can be found in the Idx_name_age two-level index and the field of the select is given the name

• Explain SELECT name,age from Test.users where age>50
  

Ditto

• Explain SELECT Name,age,id from Test.users where age>50
  

Ditto (note that the level two index contains the primary key [sample Table primary key field is ID] so that the primary key goes to the clustered index to find other fields. But obviously the above SQL statements are not needed because the fields to select are in Idx_name_age. ）

• Explain SELECT Name,age,id,email from Test.users where age>50
  

This time did not use to idx_name_age, because to select the field contains the email, in idx_name_age there is no

• Explain SELECT count (1) from Test.users where age>50
  

For a federated index such as (A, a), the query criteria for column B is counted, and if the index is overwritten, the optimizer will also select the federated Index.

MySQL overwrite index covering or index coverage