MySQLIndexConditionPushdown (ICP) performance optimization method instance _ MySQL

This article mainly introduces the example of MySQLIndexConditionPushdown (ICP) performance optimization method. This article describes the concepts, principles, practice cases, case analysis, and ICP restrictions, for more information, see 1. Introduction

Index Condition Pushdown (ICP) is a new feature in MySQL 5.6. it is an optimization method for filtering data using indexes at the storage engine layer.

A. When you disable ICP, index is only an access method for data access. The data retrieved by the storage engine from the index return Table is transmitted to the MySQL Server layer for where condition filtering.

B. When an ICP is enabled, if some where conditions can use fields in the index, MySQL Server pushes this part down to the engine layer, you can use the where condition of index filtering to filter data at the storage engine layer, instead of passing all results through index access to the MySQL server layer for where filtering.

Optimized results: the ICP can reduce the number of accesses to the base table at the engine layer and the number of accesses to the storage engine at the MySQL Server, reduce the number of I/O operations, and improve the query statement performance.

Principle 2

Index Condition Pushdown is not used:

1 Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
2 Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.
Index Condition Pushdown is used
1 Get the next row s index tuple (but not the full table row ).
2 Test the part of the WHERE condition that applies to this table and can be checked using only index columns.
If the condition is not satisfied, proceed to the index tuple for the next row.
3 If the condition is satisfied, use the index tuple to locate and read the full table row.
4 est the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

3. case studies

Prepare environment
Database version 5.6.16
Disable Cache

The code is as follows:

Set query_cache_size = 0;
Set query_cache_type = OFF;

Test data
B. When ICP is enabled

The code is as follows:

Mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Mysql> select * from employees where first_name = 'anneke 'and last_name like' % sig ';
+ -------- + ------------ + ----------- + -------- + ------------ +
| Emp_no | birth_date | first_name | last_name | gender | hire_date |
+ -------- + ------------ + ----------- + -------- + ------------ +
| 10006 | Anneke | Preusig | F |
+ -------- + ------------ + ----------- + -------- + ------------ +
1 row in set (0.00 sec)
Mysql> show profiles;
+ ---------- + ------------ + -------------------------------------------------------------------------------- +
| Query_ID | Duration | Query |
+ ---------- + ------------ + -------------------------------------------------------------------------------- +
| 1 | 0.00060275 | select * from employees where first_name = 'anneke 'and last_name like' % sig '|
+ ---------- + ------------ + -------------------------------------------------------------------------------- +
3 rows in set, 1 warning (0.00 sec)

In this case, according to the leftmost prefix principle of MySQL, first_name can use indexes. last_name adopts like fuzzy queries and cannot use indexes.
C. disable ICP

The code is as follows:

Mysql> set optimizer_switch = 'index _ condition_pushdown = off ';
Query OK, 0 rows affected (0.00 sec)
Mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Mysql> select * from employees where first_name = 'anneke 'and last_name like' % sig ';
+ -------- + ------------ + ----------- + -------- + ------------ +
| Emp_no | birth_date | first_name | last_name | gender | hire_date |
+ -------- + ------------ + ----------- + -------- + ------------ +
| 10006 | Anneke | Preusig | F |
+ -------- + ------------ + ----------- + -------- + ------------ +
1 row in set (0.00 sec)
Mysql> SET profiling = 0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Mysql> show profiles;
+ ---------- + ------------ + -------------------------------------------------------------------------------- +
| Query_ID | Duration | Query |
+ ---------- + ------------ + -------------------------------------------------------------------------------- +
| 2 | 0.00097000 | select * from employees where first_name = 'anneke 'and last_name like' % sig '|
+ ---------- + ------------ + -------------------------------------------------------------------------------- +
6 rows in set, 1 warning (0.00 sec)

When you enable the ICP service, the time consumed for querying sending data is 0.000189 s.

The code is as follows:

Mysql> show profile cpu, block io for query 1;
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
| Starting | 0.000094 | 0.000000 | 0.000000 | 0 | 0 |
| Checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| Init | 0.000044 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| Optimizing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |
| Statistics | 0.000093 | 0.000000 | 0.000000 | 0 | 0 |
| Preparing | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| Executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000189 | 0.000000 | 0.000000 | 0 | 0 |
| End | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| Query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| Closing tables | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| Freeing items | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| Cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
15 rows in set, 1 warning (0.00 sec)

When the ICP service is disabled, the time consumed for querying sending data is 0.000735 s.

The code is as follows:

Mysql> show profile cpu, block io for query 2;
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
| Starting | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| Checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| Init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| Optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| Statistics | 0.000049 | 0.000000 | 0.000000 | 0 | 0 |
| Preparing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| Executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000735 | 0.001000 | 0.000000 | 0 | 0 |
| End | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| Query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| Closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| Freeing items | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
| Cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
15 rows in set, 1 warning (0.00 sec)

From the profile above, we can see that the entire SQL execution time is 2/3 of the time not enabled when the ICP is enabled, and the time consumed by the sending data link is only 1/4 of the time consumed by the former.
The execution plan when the ICP is enabled contains the Using index condition mark, indicating that the optimizer uses the ICP to optimize data access.

The code is as follows:

Mysql> explain select * from employees where first_name = 'anneke 'and last_name like' % nta ';
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------- + ------ + ------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------- + ------ + ------------------------- +
| 1 | SIMPLE | employees | ref | idx_emp_fnln | 44 | const | 224 | Using index condition |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------- + ------ + ------------------------- +
1 row in set (0.00 sec)

Use where is displayed for the execution plan when the ICP service is disabled.

The code is as follows:

Mysql> explain select * from employees where first_name = 'anneke 'and last_name like' % nta ';
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------- + ------ + ------------- +
| 1 | SIMPLE | employees | ref | idx_emp_fnln | 44 | const | 224 | Using where |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------- + ------ + ------------- +
1 row in set (0.00 sec)

Case analysis

When the preceding query is used as an example to disable ICP, the storage engine accesses 225 pieces of data whose first_name is Anneke through the index first_name prefix, and filters data based on last_name like '% sig' at the MySQL server layer.
When you enable ICP, the like '% sig' condition of last_name can be filtered by the index field last_name. in the storage engine, the data that does not meet the condition is filtered out by comparing with the where condition. This process does not return to the table. it only accesses one qualified record and returns it to the MySQL Server, effectively reducing the interaction between io access and each layer.

When the ICP service is disabled, only indexes are used to access data.

When the ICP is enabled, MySQL uses indexes to filter data at the storage engine layer to reduce unnecessary back-to-table data. Note that the dotted using where indicates that if the where condition contains fields not indexed, it still needs to be filtered by the MySQL Server layer.

IV. ICP restrictions

1 When SQL requires full table access, the ICP optimization policy can be used for data access methods of the range, ref, eq_ref, and ref_or_null types.
2. InnoDB and MyISAM tables are supported.
3. ICP can only be used for secondary indexes, but not for primary indexes.
4. not all where conditions can be filtered by ICP.
If the field of the where condition is not in the index column, you still need to read the full table record to the server for where filtering.
5. the acceleration effect of ICP is determined by the proportion of data filtered by ICP in the storage engine.
6. analyticdb 5.6 does not support the table sharding ICP function, but starts with analyticdb 5.7.
7. When an SQL statement overwrites an index, the ICP optimization method is not supported.

The code is as follows:

Mysql> explain select * from employees where first_name = 'anneke 'and last_name = 'porenta ';
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------------- + ------ + --------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------------- + ------ + --------------------- +
| 1 | SIMPLE | employees | ref | idx_emp_fnln | 94 | const, const | 1 | Using index condition |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------------- + ------ + --------------------- +
1 row in set (0.00 sec)
Mysql> explain select first_name, last_name from employees where first_name = 'anneke 'and last_name = 'porenta ';
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------------- + ------ + ------------------------ +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------------- + ------ + ------------------------ +
| 1 | SIMPLE | employees | ref | idx_emp_fnln | 94 | const, const | 1 | Using where; Using index |
+ ---- + ------------- + ----------- + ------ + --------------- + -------------- + --------- + ------------- + ------ + ------------------------ +
1 row in set (0.00 sec)