Simple understanding and examples of MySQL index pushdown (ICP)

Preface

Index Condition Pushdown (ICP) is a new feature of MySQL 5.6. It can reduce the number of table queries and improve retrieval efficiency.

MySQL Architecture

To understand index pushdown, you must first understand the architecture of MySQL:

The above picture is from the official MySQL documentation.

MySQL is usually divided into the following layers from top to bottom:

• MySQL service layer: includes NoSQL and SQL interfaces, query parser, optimizer, cache, buffer and other components.
• Storage engine layer: Various plug-in table storage engines that implement various storage engine-related features such as transactions and indexes.
• File system layer: read and write physical files.
  

The MySQL service layer is responsible for SQL syntax parsing, triggers, views, built-in functions, binlog, generating execution plans, etc., and calls the storage engine layer to perform data storage and retrieval. The "down" in "index push down" actually means that some of the tasks that are the responsibility of the upper layer (service layer) are handed over to the lower layer (storage engine) for processing.

Index pushdown example

Assume that the user table data and structure are as follows:

Create a joint index (age, birthday) and query users whose age is > 20 and whose birthday is 03-01:

select * from user where age>20 and birthday="03-01"

Because the age field uses a range query, according to the leftmost prefix principle, in this case only the age field can be used for range query, and the birthday field in the index cannot be used. Use explain to view the execution plan:

+------+-------------+-------+-------+---------------+--------------+--------+------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+--------------+--------+------+------+------+-----------------------+
| 1 | SIMPLE | user | range | age_birthday | age_birthday | 4 | NULL | 3 | Using index condition |
+------+-------------+-------+-------+---------------+--------------+--------+------+------+------+-----------------------+

It can be seen that although the age_birthday index is used, the index length key_len is only 4, which means that only the age field of the joint index is effective (because the age field is of int type and occupies 4 bytes). Finally, the Using index condition in the Extra column indicates that this query uses index push-down optimization.

To perform the following steps without index pushdown:

• The storage engine finds the user IDs with age>20 according to the index, which are: 4, 5, 7
• The storage engine retrieves the three records with id in (4,5,7) from the table and returns them to the service layer.
• The service layer filters out the records that do not meet the birthday="03-01" condition, and finally returns a row of records with id=4 as the query result.
  

If index pushdown optimization is enabled, the execution steps are as follows:

1. The storage engine finds the user IDs whose age>20 according to the index, and uses the birthday field in the index to filter out records that do not meet the birthday="03-01" condition, and finally obtains id=4;
2. The storage engine retrieves a record with id=4 from the table and returns it to the service layer.
3. The service layer filters out the records that do not meet the birthday="03-01" condition, and finally returns a row of records with id=4 as the query result.
   

After enabling index pushdown, the where condition is moved from the MySQL service layer to the storage engine layer for execution. The benefit is that the storage engine reads data from the table less often based on the ID. In the above example, when there is no index pushdown, the table needs to be queried twice more. In addition, table query is likely to be discrete IO, which can significantly improve database performance in some cases.

Summarize

This concludes this article on the simple understanding and examples of MySQL Index Pushdown (ICP). For more relevant MySQL Index Pushdown (ICP) content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!