2018-06-16 16:03:37

icp(index condition pushdown)

icp(acronym of index condition pushdown), which used when index(part of a multicolumn index) can be use to filter out some(enormous maybe) on storage level

  • the output of EXPLAIN cound display some information to indicate user, suck as key_len and key, especially in Extra column which will dislay using index condition
  • I think that only multicolumn index could make use of this method
  • even there exist only one where condition, the icp method would happened, becuase it did
  • need satisfy leftmost prefix
  • For InnoDB tables, ICP is used only for secondary indexes, The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
  • Conditions that refer to subqueries cannot be pushed down.
  • it depends on optimizer and analyse according to statistic to decide where use icp on specific query

some example

  • multicolumn index
mysql> show index from smalla;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| smalla |          0 | PRIMARY        |            1 | id          | A         |        2000 |     NULL | NULL   |      | BTREE      |         |               |
| smalla |          1 | idx_uid_char25 |            1 | uid         | A         |        2000 |     NULL | NULL   |      | BTREE      |         |               |
| smalla |          1 | idx_uid_char25 |            2 | char25      | A         |        2000 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from smalla where uid=496840401 and char25 like 'hello%';
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | smalla | NULL       | range | idx_uid_char25 | idx_uid_char25 | 31      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

mysql> explain select * from smalla where uid>2071948332 and char25 like 'hello%';
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | smalla | NULL       | range | idx_uid_char25 | idx_uid_char25 | 4       | NULL |   69 |    11.11 | Using index condition |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
  • seperate indexes
mysql> show index from smallb;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| smallb |          0 | PRIMARY    |            1 | id          | A         |        2000 |     NULL | NULL   |      | BTREE      |         |               |
| smallb |          1 | idx_uid    |            1 | uid         | A         |        2000 |     NULL | NULL   |      | BTREE      |         |               |
| smallb |          1 | idx_char25 |            1 | char25      | A         |        2000 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> explain select * from smalla where uid=496840401 and char25 like 'hello%';
+----+-------------+--------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys      | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | smalla | NULL       | ref  | idx_uid,idx_char25 | idx_uid | 4       | const |    1 |     5.00 | Using where |
+----+-------------+--------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+

Permanent link of this article:http://nulls.cc/post/icp_of_mysql

-- EOF --