MySQL index pushdown details

Preface:

Index Pushdown (ICP) is an optimization for situations where MySQL uses indexes to retrieve rows from a table.

• In the absence of index pushdown, MySQL uses the storage engine to traverse the index to locate the data rows in the table and return them to the MySQL server. The server then determines the WHERE condition to confirm whether to add the data row to the result set.
• When index pushdown is enabled and part of the WHERE condition can be evaluated using only columns in the index, the MySQL server pushes this part of the WHERE condition to the storage engine, which then uses the index entries to evaluate the pushed index condition and reads from the table only when the condition is met.
  

Index pushdown can reduce the number of times the storage engine accesses the data table and the number of times the MySQL server accesses the storage engine.

Are you still a little confused? That's right. There is no doubt that the above paragraph is quite difficult to understand, but please don't be discouraged. I will use the most easy-to-understand language to show you how to understand index pushdown.

To summarize:

• Leftmost prefix principle
• Back to table

1. Leftmost prefix principle

MySQL follows the leftmost prefix principle when establishing a joint index. For example, a joint index (id, name, age) is now established for the User table. According to the leftmost prefix principle, this joint index can only be used when the condition part of the SQL hits (id), (id, name) or ( id , name , age ).

The index can be used in the following situations:

SELECT * FROM USER WHERE id = 1

SELECT * FROM USER WHERE id = 1 and name = 'zhangsan'

SELECT * FROM USER WHERE id = 1 and name = 'zhangsan' and age = 18

The index cannot be used in the following situations:

SELECT * FROM USER WHERE name = 'zhangsan'

SELECT * FROM USER WHERE age = 18

SELECT * FROM USER WHERE name = 'zhangsan' and age = 18

For a joint index, mysql will keep matching to the right until it encounters a range query (>, <, between , like ) and stops matching.

2. Return to table

MySQL supports two types of indexes under the InnoDB engine:

• Clustered index: The index (on the leaf nodes of the B+ tree) stores data rows (real data)
• Ordinary index: The primary key is stored in the index (on the leaf node of the B+ tree)

Here we focus on clustered indexes. The official documentation has the following description

• InnoDB uses the primary key as the clustered index for tables with primary keys.
• For tables without a primary key, InnoDB uses the first unique index as the clustered index.
• When there is no primary key or unique index, MySQL will generate a hidden 6-byte row ID field as the clustered index.
  

When MySQL cannot retrieve all the data at once through ordinary indexes, it obtains the primary key value through ordinary indexes, and then locates the record in the clustered index through the primary key value. This process is called table retrieval. You can reduce the number of table returns by creating a covering index. For example, if you want to look up a name by an ID number, you can create a joint index of the ID number and name ( id , name ). When you query, you can directly get the value of the name through this index, and there is no need to search in the clustered index. This is a covering index.

3. Index pushdown

First create a user table

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT 0,
  `class` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_two` (`name`,`age`)
)ENGINE=InnoDB;

//Add a composite index (`name`,`age`) to this table

Insert data into the table

INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 21, '1');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 22, '2');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 23, '3');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 24, '4');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 25, '5');

The query inserted data is as follows

Next explain the following SQL

explain select * from student where name like 'peng%' and age = 23;

You can see that the Extra field is displayed as USING INDEX CONDITION, which indicates that this SQL uses index pushdown. Let's analyze the above SQL statement:

Before MySQL 5.6, you can only find the rows that meet the conditions from the name field and then start to go back to the table, find the data rows on the clustered index, and then compare the age field and add the qualified data to the result set.

Index push-down optimization was introduced in MySQL 5.6. During the index traversal process, the fields included in the index are first judged. Here, the age field is judged. Directly exclude the data rows that do not meet the age field requirements, thereby reducing the number of table returns.

Question and Answer Area

Question 1: When the composite index column is (name, age, address), can the following SQL use the index?

select * from student where name like 'peng%' and age = 23;

Yes, encountering like will interrupt the matching of subsequent elements, but only the name field can be used. MySQL will keep matching to the right until it encounters a range query (>, <, between, like) and stops matching. The range column can use the index, but the columns following the range column cannot use the index. That is, the index is used for at most one range column, so if there are two range columns in the query condition, the index cannot be fully used.

Question 2: Can index pushdown only exist in joint indexes?

Yes, index pushdown is not possible with non-joined indexes.

Question 3: In what situations can index pushdown not be used?

Pushdown condition encountered subquery

Pushing down condition encounter function

Non-InnoDB Tables and MyISAM Tables

Question 4 : How to turn on and off index pushdown?

// Index pushdown is enabled by default set optimizer_switch='index_condition_pushdown=off'; // Disable set optimizer_switch='index_condition_pushdown=on'; // Enable

Summarize

The optimization of index pushdown on non-primary key indexes can effectively reduce the number of table returns and greatly improve the efficiency of queries. In daily work, you can use index pushdown to improve business throughput by optimizing indexes according to business conditions.

This is the end of this detailed article about MySQL index pushdown. For more relevant MySQL index pushdown content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!