Index problem, we have talked about two articles ~ today to talk about an index push down problem, is also very interesting!

Index pushdown is an index condition pushdown feature introduced from MySQL5.6. Index pushdown improves database query efficiency by reducing the number of times the table is returned.

Some friends may have seen some concepts about ICP, but I think, the concept is relatively simple, it is easy to understand, but in practical application, a variety of situations are very many. So I want to share with you what ICP is all about through a few specific query analysis.

1. Index push down

To demonstrate index push-down, I have installed two MySQL databases using Docker. One is MySQL5.5.62 and the other is 5.7.26. Since index push-down is a new feature introduced in MySQL5.6, Therefore, these two versions can demonstrate the characteristics of index push-down (those who do not understand Docker can reply to Docker in the background of the public account, with the introductory tutorial written by Songgo).

1.1 Preparations

First of all, I have the following table:

CREATE TABLE `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `username` (`username`(191),`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

I executed the above SQL in MySQL5.5 and MySQL5.7 respectively to ensure that there is such a table in both MySQL. This table contains a composite index of username and age. The index name is username. In the rest of this article, I will refer to this composite index.

After the table is created successfully, add some simulation data, which I will not demonstrate, through the stored procedure or Java code can add simulation data, this friend can solve by themselves.

OK, that completes our preparations.

1.2 the MySQL 5.5

Let’s start with a MySQL5.5 query example.

For the sake of the following statement, I give each SQL entry a tag:

SQL (SQL1)

select * from user2 where username='1' and age=99;
Copy the code

Select * from ‘username’ where username = ‘age’; select * from ‘age’ where username = ‘age’;

A quick glance shows that this is indexed, but how it works, LET me tell you!

In MySQL5.5, since there is no index push down, the above SQL execution flow looks like this:

  1. MySQL > select * from storage where username=’1′;
  2. The storage engine finds the first record where username=’1′ and stores the primary key ID in the leaf node of B+Tree.
  3. If age=99, the server returns the record to the client. If age=99, the server returns the record to the client. =99, then the record is discarded.
  4. Because the username+age composite index is a normal index, it is not a unique index (if it is a unique index, then the query ends), so you need to continue to search for records that meet the criteria.

But notice that step 4 does not go directly to B+Tree. Select * from B+Tree; select * from B+Tree; select * from B+Tree; select * from B+Tree; Step 2 returns data with a next_record attribute, which points directly to the next record in the secondary index. After finding the next record, go back to the table to retrieve all data and return it to the server layer. Then repeat steps 3 and 4.

Let’s look at the execution plan above, which is consistent with our analysis:

  • The previous type is ref, indicating that data is searched through the index. In general, when equivalent matching occurs, the type is ref.
  • Finally, Extra uses where to indicate that data is filtered at the server layer.

SQL (SQL2)

select * from user2 where username like 'j%' and age=99;
Copy the code

MySQL > select * from ‘j%’; MySQL > select * from ‘j%’; MySQL > select * from ‘j%’; MySQL > select * from ‘j%’; MySQL > select * from ‘j%’; .

This SQL execution process is basically the same as the first SQL1 execution process. I don’t need to repeat it here. Let’s take a look at this SQL execution plan:

‘j%’ represents a scan interval. ‘j%’ represents a scan interval. ‘j%’ represents a scan interval.

Mysql > select * from SQL where username = ‘age’; mysql > select * from SQL where username = ‘age’; mysql > select * from SQL where username = ‘age’; mysql > select * from SQL where username = ‘age’; mysql > select * from SQL where username = ‘age’; At the server layer, check whether the age meets the condition. In this way, we can see that the query efficiency is low. When there is a value of age in the index, we do not compare the age in the index. Instead, we need to retrieve the whole row of the table, return it to the server layer, and then compare it with the age. If we can directly pass age into the storage engine, the storage engine directly to determine whether age meets the condition, and then go back to the table, does not meet the condition ends here, so that we can reduce the number of times back to the table, and improve the query efficiency.

The index push-down technique, introduced since MySQL5.6, does just that.

1.3 the MySQL 5.7

SQL > select * from MySQL5.7;

select * from user2 where username like 'j%' and age=99;
Copy the code

Let’s look at the query plan:

Using index condition (Extra); Using index condition (condition);

MySQL5.6 introduces an index to push down ICP.

  1. The server layer of MySQL first calls the storage engine to locate the first username starting with j.
  2. The storage engine does not rush back to the table. Instead, it continues to determine whether the age of the record is 99. If the age is 99, the storage engine returns to the table.
  3. The storage engine returns the read rows to the server layer, and if there are any other non-indexed query conditions, the server layer continues to filter. In our case, there are no other query conditions. Assuming that the server layer has another filter that filters out the record just found, the next record is read through the record’s next_record property, and the second step is repeated.

This is the index push down (index condition pushdown, ICP), effectively reduce the number of times back to the table, improve the query efficiency.

Sometimes when we look at older versions of MySQL, we are confused by the fact that index push down is a natural feature, but it didn’t exist at the time. Fortunately, everything will happen in the end.

For a special case, look at the following SQL (SQL4) :

select * from user2 where username='1' and age=99;
Copy the code

The query condition is now equivalent to the previous one, and the execution plan is as follows:

Username =’1′ and age=99; username=’1′ and age=99; Mysql > select age from age; mysql > select username from age;

2. Summary

MySQL5.5 = MySQL5.7 = MySQL5.5 = MySQL5.7 In fact, a word: in the search engine in advance to determine whether the corresponding search conditions meet, meet the table, by reducing the number of times to improve the query efficiency.