Introduction to a concept
The index Condition pushdown (ICP) is a new feature in the MySQL 5.6 release and is an optimized way to use index filtering data at the storage engine level.
A when the ICP is turned off, index is just one way of accessing data access, and the storage engine's retrieved from the index back table is passed to the MySQL Server layer for where condition filtering.
b When the ICP is turned on, if some of the where conditions can use the fields in the index, the MySQL Server pushes this part down to the engine layer, using the Where condition of the index filter to filter data at the storage engine layer, rather than all through the index The results of access are passed to the MySQL server layer for where filtering.
Optimization effect: ICP can reduce the number of engine layer access to the base table and MySQL Server access to the storage engine, reduce IO times, improve query statement performance.
Two principles
Index Condition pushdown is not used:
1 Get the next row, the index tuple of the reading, and then by using the "index tuple to locate and read" full table Row.
2 Test The part of the "WHERE condition" 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 isn't the full table row).
2 Test the part of the WHERE condition so applies to this table and can is checked using only index columns.
If the condition is isn't 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" applies to this table. Accept or reject the row based on the test result.
Three cases of practice
A environment preparation
Database version 5.6.16
Turn off caching
Copy Code code as follows:
Set query_cache_size=0;
Set Query_cache_type=off;
Test data Download Address
b When the ICP is opened
Copy Code code 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 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
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 MySQL's leftmost prefix principle, first_name can use the index, last_name used like fuzzy query, can not use the index.
C off ICP
Copy Code code 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 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
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 the ICP is opened, the query in the sending data link time consumption is 0.000189s
Copy Code code as follows:
Mysql> Show Profiles 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 |
+----------------------+----------+----------+------------+--------------+---------------+
Rows in Set, 1 Warning (0.00 sec)
When the ICP is off, the query in the sending data link time consumption is 0.000735s
Copy Code code as follows:
Mysql> Show Profiles 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 |
+----------------------+----------+----------+------------+--------------+---------------+
Rows in Set, 1 Warning (0.00 sec)
From the above profile can be seen when the ICP opened the entire SQL execution time is not open 2/3,sending data link time consumption of the former is only 1/4 of the latter.
The ICP opening execution plan contains the using index condition indicator, which indicates that the optimizer uses ICP to optimize data access.
Copy Code code 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 | Idx_emp_fnln | 44 | Const | 224 | Using Index Condition |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+---------------- -------+
1 row in Set (0.00 sec)
The execution plan at the time of ICP shutdown shows the use where.
Copy Code code 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 | Idx_emp_fnln | 44 | Const | 224 | Using where |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
1 row in Set (0.00 sec)
Case analysis
As an example of the above query to close ICP, the storage Engine prefix index first_name access to the table 225 first_name for Anneke data, and in the MySQL server layer according to last_name like '%sig ' filter
When the ICP is opened, the last_name's like '%sig ' condition can be filtered through the index field last_name, which filters out the data that does not meet the condition through the comparison with the where condition inside the storage engine. The process does not return tables, accesses only 1 records that qualify and returns to MySQL Server, effectively reducing IO access and interaction between tiers.
When ICP closes, only the index is used as a way to access the data.
When ICP is turned on, MySQL will filter the data at the storage engine layer using the index to reduce unnecessary return tables, and note that the using where of the dashed line means that if the Where condition contains fields that are not indexed, the MySQL Server layer is filtered.
Use limitation of ICP
1 when SQL requires full table access, the ICP optimization strategy can be used for range, ref, EQ_REF, Ref_or_null type of access data methods.
2 supports InnoDB and MyISAM tables.
3 ICP can only be used for level two indexes and 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 indexed column, or you want to read the entire table's records to the server side to do where filtering.
5 The acceleration effect of ICP depends on the proportion of data filtered through ICP in the storage engine.
6 5.6 Version does not support the ICP function of the table, 5.7 version of the beginning support.
7 The ICP optimization method is not supported when SQL uses overlay indexing.
Copy Code code 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 | 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 | Idx_emp_fnln | 94 | Const,const | 1 | The Using where; Using Index |
+----+-------------+-----------+------+---------------+--------------+---------+-------------+------+---------- ----------------+
1 row in Set (0.00 sec)
