MySQL index coverage example analysis

This article describes MySQL index coverage with examples. Share with you for your reference, the details are as follows:

Index Covering

If the queried column happens to be part of the index, then the query only needs to be performed on the index file, and there is no need to go back to the disk to find the data. This type of query is very fast and is called "index covering".

Suppose there is a t15 table, in which a joint index is created: cp (cat_id, price)

When we use the following SQL statement, index coverage will occur. If you don't believe it, we can check it out. The Extra here shows Using index, which means that this SQL statement just uses index coverage.

select price from t15 where cat_id = 1;

Let's look at a question. Create a t11 table with an index on the email column.

Suppose we use the following query:

select eamil from t11 where right(email,4)='.com'

Query analysis:

First, let’s take a look at Extra. There is Using index here, which means that index coverage is used here. The reason why possible_keys is NULL is because the function in MySQL is used, so the email index is not used during the query, but the key is email, which means that the index is used for sorting. If you don’t believe me, I will print the data and have a look.

The data here is sorted. The original data is like this.

Index covering problem

create table A (
id varchar(64) primary key,
ver int,
…
)

The table has several very long fields varbinary(3000), with joint indexes on id and ver, totaling 10,000 records

Why is select id from A order by id so slow?
And select id from A order by id,ver is very fast

Question: id and (id,ver) both have indexes, and select id should produce the effect of "index coverage". Why is the former slow and the latter fast?

Idea: The difference between InnoDB clustered index and MyISAM index, consider the index coverage from these two perspectives

(1) Assuming that this table uses a MyISAM index, then neither of these two SQL statements needs to go back row to search for data, so their speeds should be similar.

(2) Assuming that this table uses the InnoDB index, then the SQL statement select id from A order by id uses the primary key index. This is because each primary key of InnoDB carries the data of each row, and there are several particularly large fields in this question, so the search for the id will be relatively slow. The SQL statement select id from A order by id,ver uses the id,ver joint index. In the InnoDB storage engine, the secondary index stores the application of the primary key index, so the secondary index does not carry the data of the row. In this case, searching for the id in the (id,ver) index will be faster. When the corresponding node tree is found, you only need to find the location of the primary key index again to get the data of the row. This is faster.

infer:

(1) If the table is using the MyISAM engine, there will be no significant difference in speed between the two statements.
(2) Because of the clustered index, the ID index of the InnoDB table needs to span multiple blocks on the disk, resulting in slow speed.
(3) Even with the InnoDB engine, if there are no long varbinay columns, there will be no significant difference in the speed of the two statements.

Table t12, storage engine MyISAM, has primary key index and (id,ver) index, and several large variable-length fields. Test inference 1

Table t13, with InnoDB storage engine, has primary key index and (id,ver) index, and several large variable-length fields.

Table t14, storage engine is InnoDB, has primary key index and (id,ver) index, no large variable length fields Corollary 3

Each table of t12, t13, and t14 contains 10,000 pieces of data, and then a test is performed. The test results are as follows, and our inference is correct.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.