Analysis of Index condition pushdown

Original link: http://blog.csdn.net/zbszhangbosen/article/details/7463382

Another article spoke MRR and BKA.

What is indexcondition pushdown (ICP)?

Pushdown in the database indicates that some operations are "pushed down", that is, some operations are executed ahead of schedule, and some actions push down when the execution plan is generated (why push down, because the optimizer is called the execution plan tree in the generated plan, the operation starts at the leaf node to the root), Pushing down means doing it ahead of time. For the simplest example, some projection operations push down can greatly reduce the amount of data in the execution process, and the index condition pushdown here is also a similar push operation. That is, we will take advantage of this step of index judgment, and let us take a look at an example:

Table Orders

CREATE TABLE Orders (

order_id INT not NULL PRIMARY KEY,

customer_id INT,

Value INT,

Order_date date,

KEY Idx_custid_value (customer_id, value)

)

Query:select * fromorders where customer_id<4 and value=290;

it does this before the ICP is done.

1. Remove the next customer_id<4 record from the index Idx_custid_value index, and then read the entire row with the primary key field

2. Then use the value=290 for this complete line to determine if it meets the criteria.

3. Repeat this process starting from 1

That's what happens when you have ICP.

1. Remove the next customer_id<4 record from the index Idx_custid_value index, and then use the other field conditions of the index to determine if the condition is set, perform step 2nd, or 3rd step

2. The filter in the previous step to meet the criteria will use order_id to go to the primary key index to find this complete row.

3. Repeat this process starting from 1

This means that the judgment condition is advanced (or called a push), which has a lot to do with the customer_id on the line, and the selectivity of the query on value is a big improvement.

Here's a look at the example above:

mysql5.5 (no index conditionpushdown implemented)

Mysql> Explain select * from Orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key |key_len | Ref | Rows | Extra |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+

| 1| Simple | Orders | Range |idx_custid_value | Idx_custid_value |5 |    NULL | 1 | Using where |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+

mysql5.6 (Implementation of the index Conditionpushdown)

Mysql> Explain select * from Orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------- ----+

| ID | Select_type | Table | Type | Possible_keys | Key |key_len | Ref | Rows | Extra |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------- ----+

| 1| Simple | Orders | Range | Idx_custid_value | Idx_custid_value | 5 |    NULL | 1 | Using Indexcondition |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------- ----+

I think the core of ICP is that it does not affect which index the optimizer chooses, but how it uses the index. The above two execution plans see whether or not ICP two is a customer_id column (key_len=5, customer_id int, 4 bytes, and 1 bytes to determine whether or not null) using the Idx_custid_value index. The indexes used are exactly the same, but they are handled in a different way.

Because there is only one index on the top of the customer_id, it is very likely that this is the only option on the customer_id, so create a separate index for customer_id, which is exactly what the top combination index uses

Mysql> CREATE INDEX Idx_custid onorders (customer_id);

Query OK, 0 rows affected (0.09 sec)

And then execute it again in MySQL 5.5 and MySQL 5.6, respectively.

mysql5.5

Mysql> Explain select * from Orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+--------- ----+

| ID | Select_type | Table | Type | Possible_keys |key | Key_len | Ref | Rows | Extra |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+--------- ----+

| 1| Simple | Orders | Range |idx_custid_value,idx_custid | Idx_custid_value | 5 |    NULL | 1 | Using where |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+--------- ----+

1 row in Set (0.00 sec)

mysql5.6

Mysql> Explain select * from Orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+--------- --------------+

| ID | Select_type | Table | Type | Possible_keys |key | Key_len | Ref | Rows | Extra |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+--------- --------------+

| 1| Simple | Orders | Range |idx_custid_value,idx_custid | Idx_custid_value | 5 |    NULL | 1 | Using Indexcondition |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+--------- --------------+

1 row in Set (0.00 sec)

As you can see from the above, even though there is a separate index on the customer_id, MySQL 5.5 uses the Idx_custid_value combination index. However, in the MySQL 5.5 obviously not using the characteristics of the ICP (that is, not using the Idx_custid_value index of the Value column part), why not choose Idx_custid this index. This may be a reflection of the MySQL optimizer's imperfections.

Summary:

Queries that require index condition pushdown typically have indexed fields that appear within the WHERE clause. Like what:

SELECT * from TB where Tb.key_part1 < x and tb.key_part2 = y

SELECT * from TB where tb.key_part1 = x andtb.key_part2 like '%yyyy% '

SELECT * from TB where Tb.key_part1 > x and Tb.key_part1 < Y and Tb.key_part1 > XX and Tb.key_part2 < yy

However, it should be noted that:

1. If the query for the first field of the index is without boundaries such as Key_part1 like '%xxx% ', then do not say ICP, even the index will not be able to use.

2. If the Select field is all within the index, then it is the direct index scan, no need for what ICP

3. Did not do performance comparison test, because now the MySQL 5.6 are still only development version, and so on GA version is not too late

reference materials :

1.http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

2. http://www.mysqlperformanceblog.com/2012/03/12/ index-condition-pushdown-in-mysql-5-6-and-mariadb-5-5-and-its-performance-impact/

3.http://jorgenloland.blogspot.com/2011/08/mysql-range-access-method-explained.html