MySQL's efficient coverage index

A very efficient and useful index---the overlay index in MySQL.

Overlay index in layman's words, it is only when the select is used to fetch the data, without having to make two select related tables. The leaf nodes of such indexes also contain the data of their index.

SELECT * FROM table_name;

Select Id,name from table_name;

In most cases, we should only query the columns we need to know, so that the packets sent between the networks are small, the network traffic is reduced, and the speed of your query is naturally improved.

Select a from table_name where B , such a query, all know that the index should be added on top B,

Process of query:

first go to index B to find its corresponding index,

The correct data rows are then retrieved based on the index area.

So a go is two searches, can you get the data through a single retrieval?

If you want to get the data through a single retrieval, then the index should contain the data relative to its index, so is it possible?

Read this, of course know possible.

ALTER TABLE_NAME ADD index (B,A);

Adding an index like this can be done,

See if the overwrite index is used;

Explain select;

·······

·······

······

Extra:use Index

If a red font portion appears, it indicates that an overlay index is used.

The InnoDB engine is further above the overlay index:

All InnoDB engines have a primary key ID, a transaction ID, a rollback pointer, a non-primary key ID,

His query would be either a non-primary key ID or overwrite it to get the primary key ID.

MySQL's efficient coverage index