Usage example of MySQL overlay index

Time:2022-5-7

What is an overlay index

Create an index that contains all the fields used in the query, which is called “overwrite index”.

Using the overlay index, MySQL can find and return the data required by the query only through the index, instead of going back to the table after using the index to process the data.

Overlay index can complete the query work at one time, effectively reduce IO and improve query efficiency.

Use example

Query statement:


SELECT col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

Create a single index column:


ALTER TABLE `test_table` ADD INDEX `idx_col1`(`col1`) USING BTREE;

Analysis query:


EXPLAIN SELECT SQL_NO_CACHE col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

Results: after indexing col1, the type is ref and IDX is used_ Col1 index.

Modify the index and establish a joint index according to the query statement:


ALTER TABLE `test_table` DROP INDEX `idx_col1`;
ALTER TABLE `test_table` ADD INDEX `idx_col1_col2_col3`(`col1`,`col2`,`col3`) USING BTREE;

Analysis query:


EXPLAIN SELECT SQL_NO_CACHE col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

Results: after establishing the joint index, the type was ref and IDX was used_ col1_ col2_ Col3 index, extra is using index, which indicates that the overlay index is used.

The difference between MySQL overlay index and federated index

The overlay index ABCD is a query column, which can be extracted directly through the index ABCD. For example, only query the columns of the primary key ABCD! Or query all columns of the joint index or some columns starting from the left (pay attention to the order)!

The joint index does not necessarily get all the data from the index, which depends on the column you query. For example, select * from table where ××××××; The way is unlikely to be to overwrite the index. Therefore, if the columns you query can use the joint index, and the columns you query can be obtained through the joint index, for example, you can only query the columns where the joint index is located or some columns starting on the left, which is equivalent to overwriting the index. Usually, in order to make the query use the overlay index, the multi column data to be queried is set as a joint index.

summary

This is the end of this article on the use of MySQL overlay index. For more information about MySQL overlay index, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!