Index Condition Pushdown ICP is a new feature in Mysql5.6. The main focus is to put the filtering process in the storage engine layer instead of the Server layer as before.

Although this is a relatively simple concept, many careless students may have a small misunderstanding of index push down, as for what it is, please see below.

What is index push down

First, we create a user table, create a joint index of AGe_name, and insert three test data.

SELECT * from user where age >10 and name = ‘a’, SELECT * from user where age >10 and name = ‘a’ This indicates that the index has been pushed down.

Yeah, index push-down for this query scenario, so what exactly is index push-down?

According to our scenario above, there are actually two index trees, one is the primary key index, which stores the specific data information, and the other is the age_name joint index, which holds the primary key ID.

In the absence of an ICP index push down, the flow of the query should look like this (skipping extraneous details) :

  1. The Mysql Server layer invokes apis to query storage engine data
  2. The storage engine first finds all data with age>10 by condition based on the federated index
  3. Each piece of data found is queried back to the table against the primary key index until unqualified results are found
  4. Data is returned to the Server layer, and the Server filters the results based on the conditions. The process ends

With ICP, the process goes like this:

  1. The Mysql Server layer invokes apis to query storage engine data
  2. The storage engine first finds all data with age>10 based on conditions that already exist in the federated indexnameData is filtered to find the data that meets the requirements
  3. According to the data found to meet the conditions, back to the table query
  4. Return data to the Server layer and the process ends

Comparison of these two processes will obviously found that using ICP after we just simply by joint index already some data directly filtered out, don’t need to find a pile of useless data to the Server layer filter, so that reduces the number of back to the table and return data, IO number reduced, to have a good performance.

According to the official documentation, ICP actually also has certain use restrictions, only say the key, chaotic not to say.

  1. First, ICP is suitable for range, REF, Eq_REF, and REF_OR_NULL scenarios
  2. InnoDB and MyISAM both support ICP, Mysql partition table can also be used
  3. For InndoDB, ICP only supports secondary index, because the primary key index is not used, isn’t it?
  4. Subqueries are not supported

By default, ICP is enabled. SET optimizer_switch = ‘index_condition_pushdown=off’; .

A little mistake

Mysql > alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure alter table structure

Explain SELECT * from user where age >10 and name like ‘a%’

You’re going to find, holy shit, there’s no index push down? This is not scientific right, it seems that there is no explanation, can this index be pushed down to find the age index and then pushed down to the name index? This is completely unreasonable ah.

Using index condition does not necessarily mean that index push-down is used, but it does mean that index push-down is available, but not necessarily used…

This one is a little tricky, and it may mislead our judgment.

If you go to a lot of people search on the net for example that indexed, and then tell you this is an index pushdown, you can enjoy the spray him, we say that an index pushdown must be in the case of a joint index, according to the joint index itself has some data directly for a filter, and don’t have to perform multiple useless to filter back to the table to Server layer, You need to be very clear about that.

Well, that’s the end of today’s topic, I’m Ai Xiaoxian, and we’ll see you next time.

(ORIGINALLY I wanted to draw two more diagrams, but IT seemed to me that the concept was so simple that it was meaningless to draw a few diagrams to explain to you. It was not necessary, was it not because I was lazy?)

This article is published by OpenWrite, a blogging tool platform