MySQL tips – index push down

Time:2022-5-11

preface

Index push down (ICP) is an optimization for MySQL to retrieve data rows from tables using indexes.

  • When there is no index push down, MySQL locates the data rows in the table by traversing the index through the storage engine and returns them to the MySQL server. The server then judges the where condition to confirm whether to add the data rows to the result set.
  • Turn on index push down, and the where condition part can only be evaluated by using the columns in the index. At this time, the MySQL server will push this part of the where condition to the storage engine, and then the storage engine will evaluate the pushed index condition by using the index entry, and read it from the table only when the condition is met.

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

text

After reading the explanation of the above official document, are you like this now

MySQL tips - index push down

Yes, that’s right. There’s no doubt that it’s very difficult to understand the above paragraph, but please don’t lose heart. I’ll take you to understand index push down in the most accessible language.

Before that, let me introduce two friends to you

  • Leftmost prefix principle
  • Back to table

Leftmost prefix principle

MySQL will follow the leftmost prefix principle when establishing a federated index. For example, now the user table has established a federated index (ID, name, age). According to the leftmost prefix principle, this federated index can only be used when the conditional part of SQL hits (ID), (ID, name) or (ID, name, age).

The index can be used as follows:

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 as follows:

SELECT * FROM USER WHERE name = ‘zhangsan’

SELECT * FROM USER WHERE age = 18

SELECT * FROM USER WHERE name = ‘zhangsan’ and age = 18

For the joint index, MySQL will match to the right until it meets the range query (>, <, between, like).

Back to table

MySQL supports two indexes under the InnoDB engine

Clustered index: data rows (real data) are stored in the index (on the leaf node of B + tree)

Ordinary index: the primary key is stored in the index (on the leaf node of B + tree)

Here we focus on the clustered index. The official document has the following description

  • In a table with a primary key, InnoDB uses the primary key as a clustered index
  • For tables without primary keys, 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 get all the data at one time through the ordinary index, obtain the primary key value through the ordinary index, and then locate the record in the clustered index through the primary key value. This process is called back to the table. You can reduce the return to the table by creating an overlay index. For example, if you want to query a name by ID number, you can create a joint index (ID, name) of ID number and name. When querying, you can get the value of name directly through this index. You no longer need to search in the clustered index. This is the overlay index.

Index push down

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 to this table
 (`name`,`age`)

Insert data into 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 data inserted by query is as followsMySQL tips - index push down

Next, explain the following SQL

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

MySQL tips - index push down

You can see that the extra field is displayed as using index condition, which indicates that the SQL uses index push down. Let’s analyze the above SQL statement:

On mysql5 Before 6, you can only find the qualified rows from the name field and startBack to table, find the data row on the clustered index, compare the age field, and add the qualified data to the result set.

On mysql5 6. Index push down optimization is introduced. In the process of index traversal, the fields contained in the index are judged first. Here, the age field is judged. Directly exclude the data rows that do not meet the age field, so as toReduce back to tableNumber of times.

Q & A 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, if you encounter like, the matching of subsequent elements will be interrupted, but only the name field can be used. MySQL will match to the right until you encounter range queries (>, <, between, like). The range column can use the index, but the column after the range column cannot use the index. That is, the index can be used for one range column at most. Therefore, if there are two range columns in the query criteria, the index cannot be used completely.

  • Question 2 can index push down only exist in the joint index?

Yes, non federated indexes cannot be pushed down using indexes.

  • Question 3 under what circumstances can index push down not be used?

Push down condition encountered subquery

Push down condition encountered function

Non InnoDB and MyISAM tables

  • Question 4 how to turn index push down on and off?
//Index push down is on by default
set optimizer_ switch='index_ condition_ pushdown=off'; //  close
set optimizer_ switch='index_ condition_ pushdown=on'; //  open

summary

The optimization of index push down on the non primary key index can effectively reduce the number of table returns and greatly improve the efficiency of query. In normal work, index push down can be used by optimizing the index according to the business situation to improve the business throughput.

❤️ Thank you for your support

The above is all the content of this sharing. I hope it will help you^_^

If you like it, don’t forget to share, like and collect three times ~.

Welcome to pay attention to the official account programmer bus, an interesting, stylish and warm programmer bus, which covers the contents of large factories, programmers’ lives, practical tutorials, technological frontiers, etc. pay attention to me and make friends.