Hello everyone, I am the third, today share a small knowledge point – index push down.

If you hear the words “MySQL5.6” or “index optimization” in an interview, you’ll get it right away. This is “index push down.”

What is index push down

Index Condition Pushdown (ICP) is a new feature in MySQL5.6, which can reduce The Times of query back to the table and improve query efficiency.

Principles of index push-down optimization

Let’s take a look at the general architecture of MySQL:

The MySQL service layer is responsible for PARSING SQL, generating execution plans, etc., and calling the storage engine layer to perform data storage and retrieval.

Push-down of indexes actually means that part of the responsibility of the upper layer (the service layer) is transferred to the lower layer (the engine layer).

MySQL > select * from MySQL where ICP is not used;

  • The storage engine reads the index record;
  • Locate and read complete row records based on primary key values in the index;
  • The storage engine gives the recordServerLayer to check whether the record is satisfiedWHEREConditions.

In the case of ICP, the query procedure:

  • The storage engine reads the index record (not the full row record);
  • judgeWHEREWhether the condition part can be checked by the column in the index, if the condition is not met, the next row of index records will be processed;
  • If yes, use the primary key in the index to locate and read the full row record (so-called back table);
  • The storage engine gives the recordServerLayer,ServerLayer detects whether the record is satisfiedWHEREThe rest of the condition.

Specific practice of index push-down

The theory is abstract, so let’s do a practice.

Use a user table, tuser, in which the federated index (name, age) is created.

If you now have a request: retrieve all users whose names start with zhang and are 10 years old. So, the SQL statement looks like this:

select * from tuser where name like 'a %' and age=10;
Copy the code

If you know the index leftmost matching rule, then you know that this statement can only search the index tree with zhang, and the first record that meets the condition is ID 1.

So what’s the next step?

No ICP is used

Before MySQL 5.6, storage engine found primary key ID (1, 4) of Name Likelike ‘Zhang %’ through joint index, conducted back-table scan one by one, clustering index was removed to find complete row records, and server layer screened data according to age=10.

Let’s take a look at the schematic:

As you can see, we need to go back to the table twice, wasting age, another field in our federated index.

Using ICP

However, after MySQL 5.6, storage engine found Name likelike ‘Zhang %’ according to the joint index (name, age), as the joint index included age column, storage engine was directly filtered in the joint index according to age=10. Scan the table one by one based on the filtered data.

Let’s take a look at the schematic:

You can see that it only returns to the table once.

In addition, we can also look at the execution plan and see the Using index condition column in Extra, which is Using index push-down.

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+---------- -------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+---------- -------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+---------- -------------+
Copy the code

Index push-down conditions of use

  • Can only be used forrange,ref,eq_ref,ref_or_nullAccess method;
  • Can only be used forInnoDBandMyISAMStorage engine and its partition table;
  • rightInnoDBFor storage engines, index push-downs apply only to secondary indexes (also known as secondary indexes);

The purpose of index push-down is to reduce the number of times the table is returned, that is, to reduce I/O operations. For InnoDB’s clustered index, data and index are together, there is no table-back.

  • Conditions that reference subqueries cannot be pushed down;
  • A condition that references a storage function cannot be pushed down because the storage engine cannot call the storage function.

Related system parameters

Push under index conditions is enabled by default. You can use the system parameter optimizer_switch to determine whether the controller is enabled.

To view the default status:

mysql> select @@optimizer_switch\G; *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,i ndex_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semij oin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,con dition_fanout_filter=on,derived_merge=on 1 rowin set (0.00 sec)

Copy the code

Switching state:

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";
Copy the code


Reference:

[1]. InnoDB Storage Engine

[2] MySQL MySQL

[3]. MySQL index push down (ICP

[4]. MySQL index push down (ICP)