High performance MySQL (5) Indexing Strategy-Overlay index and index sorting

One, covering index

Indexing is an efficient way to find data, but MySQL can also use indexes to get data directly from columns so that you no longer need to read rows of data. If the leaf node of the index already contains the data to query, what else is necessary to return to the table query?

If an index contains or overwrites the values of all fields that need to be queried, we call it "overlay index."

Benefits of overriding indexes:

1, index entries are usually far less than the data row size, so if only need to read the index, greatly reduce the amount of data traffic. This is especially true for MyISAM, because MyISAM can compress indexes to become smaller.

2. Because indexes are stored sequentially, the I/O-intensive range query is much less than the I/O that randomly reads each row of data from disk.

3, because of the InnoDB clustered index, the overlay index is particularly useful for InnoDB. The two-level index of the INNODB holds the primary key value of the row in the leaf node, so if the two primary key can overwrite the query, you can avoid the two queries on the primary key index.

Overriding an index must store the value of the indexed column, and the hash Index, spatial index, and Full-text index cannot store the column's value, so MySQL can only use the B-tree index to overwrite the index.

When an index overlay query is initiated, the Usingindex information is visible in the extra column of explain. For example:

If the index overwrites the fields in the Where condition, but not the fields involved in the entire query, see what happens

No index can overwrite this query because the query selects all columns from the table, and none of the indexes overwrite all columns. But the index is still in use.

Next you can compare the difference between the InnoDB and MyISAM that can use the clustered index to overwrite the index.

First look at the MyISAM table, the table structure is as follows