MySQL 优化器 Index Condition Pushdown下推（ICP）

ICP 测试

准备数据

CREATE TABLE `icp` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) DEFAULT NULL,
  `email` varchar(25) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
);

insert  into `icp`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`) values (100,'Steven','K_ing','SKING','515.123.4567'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568'),(102,'Lex','De Haan','LDEHAAN','515.123.4569'),(103,'Mary','Hunold','AHUNOLD','590.423.4567'),(104,'Mary','Ernst','BERNST','590.423.4568'),(105,'Mary','Austin','DAUSTIN','590.423.4569'),(106,'Valli','Pataballa','VPATABAL','590.423.4560'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569'),(109,'Daniel','Faviet','DFAVIET','515.124.4169'),(110,'Mary','Chen','JCHEN','515.124.4269'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469'),(113,'Mary','Popp','LPOPP','515.124.4567'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561'),(115,'Alexander','Khoo','AKHOO','515.127.4562'),(116,'Mary','Lichtman','SBAIDA','515.127.4563'),(117,'Mary','Tobias','STOBIAS','515.127.4564'),(118,'Mary','Oberman','GHIMURO','515.127.4565'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566'),(120,'Matthew','Weiss','MWEISS','650.123.1234'),(121,'Adam','Fripp','AFRIPP','650.123.2234'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234'),(123,'Mary','Vollman','SVOLLMAN','650.123.4234'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234'),(125,'Julia','Nayer','JNAYER','650.124.1214'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224'),(127,'Mary','Landry','JLANDRY','650.124.1334'),(128,'Mary','Markle','SMARKLE','650.124.1434'),(129,'Mary','Bissot','LBISSOT','650.124.5234'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234'),(131,'Mary','Marlow','JAMRLOW','650.124.7234'),(132,'TJ','Olson','TJOLSON','650.124.8234'),(133,'Mary','Mallin','JMALLIN','650.127.1934'),(134,'Michael','Rogers','MROGERS','650.127.1834'),(135,'Ki','Gee','KGEE','650.127.1734'),(136,'Mary','Philtanker','HPHILTAN','650.127.1634'),(137,'Renske','Ladwig','RLADWIG','650.121.1234'),(138,'Stephen','Stiles','SSTILES','650.121.2034'),(139,'Mary','Seo','JSEO','650.121.2019'),(140,'Mary','Hofman','JPATEL','650.121.1834'),(141,'Trenna','Rajs','TRAJS','650.121.8009'),(142,'Curtis','Davies','CDAVIES','650.121.2994'),(143,'Mary','Matos','RMATOS','650.121.2874'),(144,'Mary','Vargas','PVARGAS','650.121.2004'),(145,'John','Russell','JRUSSEL','011.44.1344.429268'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268'),(147,'Mary','Errazuriz','AERRAZUR','011.44.1344.429278'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018'),(150,'Mary','Weedman','PTUCKER','011.44.1344.129268'),(151,'Mary','Bernstein','DBERNSTE','011.44.1344.345268'),(152,'Peter','Hall','PHALL','011.44.1344.478968'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718'),(154,'Mary','Cambrault','NCAMBRAU','011.44.1344.987668'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508'),(156,'Mary','K_ing','JKING','011.44.1345.429268'),(157,'Mary','Sully','PSULLY','011.44.1345.929268'),(158,'Mary','Dymetman','AMCEWEN','011.44.1345.829268'),(159,'Mary','Smith','LSMITH','011.44.1345.729268'),(160,'Mary','Doran','LDORAN','011.44.1345.629268'),(161,'Mary','Sewall','SSEWALL','011.44.1345.529268'),(162,'Mary','Vishney','CVISHNEY','011.44.1346.129268'),(163,'Mary','Greene','DGREENE','011.44.1346.229268'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268'),(165,'David','Lee','DLEE','011.44.1346.529268'),(166,'Mary','Ande','SANDE','011.44.1346.629268'),(167,'Mary','Banda','ABANDA','011.44.1346.729268'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268'),(169,'Mary','Bloom','HBLOOM','011.44.1343.829268'),(170,'Tayler','Fox','TFOX','011.44.1343.729268'),(171,'Mary','Mary','WSMITH','011.44.1343.629268'),(172,'Mary','Bates','EBATES','011.44.1343.529268'),(173,'Mary','Kumar','SKUMAR','011.44.1343.329268'),(174,'Ellen','Abel','EABEL','011.44.1644.429267'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264'),(178,'Mary','Grant','KGRANT','011.44.1644.429263'),(179,'Mary','Johnson','CJOHNSON','011.44.1644.429262'),(180,'Mary','Taylor','WTAYLOR','650.507.9876'),(181,'Mary','Fleaur','JFLEAUR','650.507.9877'),(182,'Mary','Sullivan','MSULLIVA','650.507.9878'),(183,'Girard','Geoni','GGEONI','650.507.9879'),(184,'Mary','Sarchand','NSARCHAN','650.509.1876'),(185,'Mary','Bull','ABULL','650.509.2876'),(186,'Mary','Botman','JDELLING','650.509.3876'),(187,'Mary','Cabrio','ACABRIO','650.509.4876'),(188,'Kelly','Chung','KCHUNG','650.505.1876'),(189,'Jennifer','Dilly','JDILLY','650.505.2876'),(190,'Mary','Gates','TGATES','650.505.3876'),(191,'Randall','Perkins','RPERKINS','650.505.4876'),(192,'Sarah','Bell','SBELL','650.501.1876'),(193,'Mary','Everett','BEVERETT','650.501.2876'),(194,'Mary','McCain','SMCCAIN','650.501.3876'),(195,'Vance','Jones','VJONES','650.501.4876'),(196,'Alana','Walsh','AWALSH','650.507.9811'),(197,'Mary','Feeney','KFEENEY','650.507.9822'),(198,'Mary','OConnell','DOCONNEL','650.507.9833'),(199,'Mary','Grant','DGRANT','650.507.9844'),(200,'Mary','Whalen','JWHALEN','515.123.4444'),(201,'Mary','Hartstein','MHARTSTE','515.123.5555'),(202,'Pat','Fay','PFAY','603.123.6666'),(203,'Susan','Mavris','SMAVRIS','515.123.7777'),(204,'Mary','Baer','HBAER','515.123.8888'),(205,'Mary','Higgins','SHIGGINS','515.123.8080'),(206,'William','Gietz','WGIETZ','515.123.8181');

mysql> desc employees;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| employee_id  | int         | NO   | PRI | NULL    | auto_increment |
| first_name   | varchar(20) | YES  | MUL | NULL    |                |
| last_name    | varchar(25) | YES  |     | NULL    |                |
| email        | varchar(25) | YES  |     | NULL    |                |
| phone_number | varchar(20) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

ICP 只能作用于二级索引，所以需要建立一个二级索引。执行下述命令建立 first_name 和 last_name 的联合索引：

mysql> alter table employees add index first_name_last_name (first_name, last_name);

打开 ICP 的性能测试

为了明确看到查询性能，我们启用 profiling：

mysql> set profiling = 1;
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                     |
+----------+------------+---------------------------------------------------------------------------+
|        1 | 0.00165975 | select * from employees where first_name='Mary' and last_name like '%man' |
|        2 | 0.00162125 | select * from employees where first_name='Mary' and last_name like '%man' |
|        3 | 0.00164050 | select * from employees where first_name='Mary' and last_name like '%man' |
+----------+------------+---------------------------------------------------------------------------+

注意：mysql 默认开启 ICP 机制

PS：MySQL 有一个叫 profile 的东东，可以用来监视 SQL 语句在各个阶段的执行情况，咱们可以使用这个工具来观察 SQL 语句在各个阶段的运行情况，关于 profile 的详细说明可以参考官方文档。

上述执行中查询语句中只有 first_name 采用索引，last_name 由于使用了模糊查询，没法使用索引进行匹配。

关闭 ICP 性能测试

mysql> set optimizer_switch='index_condition_pushdown=off';
mysql> set profiling = 1;
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> select * from employees where first_name='Mary' and last_name like '%man';
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                     |
+----------+------------+---------------------------------------------------------------------------+
|        1 | 0.00317075 | select * from employees where first_name='Mary' and last_name like '%man' |
|        2 | 0.00316175 | select * from employees where first_name='Mary' and last_name like '%man' |
|        3 | 0.00316075 | select * from employees where first_name='Mary' and last_name like '%man' |
+----------+------------+---------------------------------------------------------------------------+

结果对比

从上述结果可以看出，打开 ICP 机制时，随机执行三次查询，花费时间都在 0.0016...

而关闭 ICP 机制后，随机执行三次查询，花费时间都在 0.0031...，约是打开 ICP 机制时的 2 倍

此外，通过 explain 命令也可查看 ICP 是否打开，执行如下命令：

mysql> explain select * from employees where first_name='Mary' and last_name like '%man';

情况 1

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | first_name_last_name | first_name_last_name | 83      | const |   56 |    11.11 | Using index condition |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------+

情况 2

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | first_name_last_name | first_name_last_name | 83      | const |   56 |    11.11 | Using where |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+

情况 1 中的 Extra 列显示 Using index condition 代表 ICP 机制已打开；

情况 2 中的 Extra 列显示 Using where 代表 ICP 机制已关闭；

ICP原理

5.6 之前，在 SQL 语句的执行过程中，server 层通过 engine 的 api 获取数据，然后再进行 where_cond 的判断（具体判断逻辑在: evaluate_join_record），每一条数据都需要从 engine 层返回 server 层做判断。5.6 之后，在利用索引扫描的过程中，如果发现 where_cond 中含有这个 index 相关的条件，则将此条件记录在 handler 接口中，在索引扫描的过程中，只有满足索引与 handler 接口的条件时，才会返回到 server 层做进一步的处理，在前缀索引区分度不够，其它字段区分度高的情况下可以有效的减少 server & engine之间的开销，提升查询性能。

举例说明：

mysql> select * from employees where first_name='Mary';
+-------------+------------+------------+----------+--------------------+
| employee_id | first_name | last_name  | email    | phone_number       |
+-------------+------------+------------+----------+--------------------+
|         166 | Mary       | Ande       | SANDE    | 011.44.1346.629268 |
|         105 | Mary       | Austin     | DAUSTIN  | 590.423.4569       |
|         204 | Mary       | Baer       | HBAER    | 515.123.8888       |
|         167 | Mary       | Banda      | ABANDA   | 011.44.1346.729268 |
|         172 | Mary       | Bates      | EBATES   | 011.44.1343.529268 |
|         151 | Mary       | Bernstein  | DBERNSTE | 011.44.1344.345268 |
|         129 | Mary       | Bissot     | LBISSOT  | 650.124.5234       |
|         169 | Mary       | Bloom      | HBLOOM   | 011.44.1343.829268 |
|         186 | Mary       | Botman     | JDELLING | 650.509.3876       |
|         185 | Mary       | Bull       | ABULL    | 650.509.2876       |
|         187 | Mary       | Cabrio     | ACABRIO  | 650.509.4876       |
|         154 | Mary       | Cambrault  | NCAMBRAU | 011.44.1344.987668 |
|         110 | Mary       | Chen       | JCHEN    | 515.124.4269       |
|         160 | Mary       | Doran      | LDORAN   | 011.44.1345.629268 |
|         158 | Mary       | Dymetman   | AMCEWEN  | 011.44.1345.829268 |
|         104 | Mary       | Ernst      | BERNST   | 590.423.4568       |
|         147 | Mary       | Errazuriz  | AERRAZUR | 011.44.1344.429278 |
|         193 | Mary       | Everett    | BEVERETT | 650.501.2876       |
|         197 | Mary       | Feeney     | KFEENEY  | 650.507.9822       |
|         181 | Mary       | Fleaur     | JFLEAUR  | 650.507.9877       |
|         190 | Mary       | Gates      | TGATES   | 650.505.3876       |
|         178 | Mary       | Grant      | KGRANT   | 011.44.1644.429263 |
|         199 | Mary       | Grant      | DGRANT   | 650.507.9844       |
|         163 | Mary       | Greene     | DGREENE  | 011.44.1346.229268 |
|         201 | Mary       | Hartstein  | MHARTSTE | 515.123.5555       |
|         205 | Mary       | Higgins    | SHIGGINS | 515.123.8080       |
|         140 | Mary       | Hofman     | JPATEL   | 650.121.1834       |
|         103 | Mary       | Hunold     | AHUNOLD  | 590.423.4567       |
|         179 | Mary       | Johnson    | CJOHNSON | 011.44.1644.429262 |
|         173 | Mary       | Kumar      | SKUMAR   | 011.44.1343.329268 |
|         156 | Mary       | K_ing      | JKING    | 011.44.1345.429268 |
|         127 | Mary       | Landry     | JLANDRY  | 650.124.1334       |
|         116 | Mary       | Lichtman   | SBAIDA   | 515.127.4563       |
|         133 | Mary       | Mallin     | JMALLIN  | 650.127.1934       |
|         128 | Mary       | Markle     | SMARKLE  | 650.124.1434       |
|         131 | Mary       | Marlow     | JAMRLOW  | 650.124.7234       |
|         171 | Mary       | Mary       | WSMITH   | 011.44.1343.629268 |
|         143 | Mary       | Matos      | RMATOS   | 650.121.2874       |
|         194 | Mary       | McCain     | SMCCAIN  | 650.501.3876       |
|         118 | Mary       | Oberman    | GHIMURO  | 515.127.4565       |
|         198 | Mary       | OConnell   | DOCONNEL | 650.507.9833       |
|         136 | Mary       | Philtanker | HPHILTAN | 650.127.1634       |
|         113 | Mary       | Popp       | LPOPP    | 515.124.4567       |
|         184 | Mary       | Sarchand   | NSARCHAN | 650.509.1876       |
|         139 | Mary       | Seo        | JSEO     | 650.121.2019       |
|         161 | Mary       | Sewall     | SSEWALL  | 011.44.1345.529268 |
|         159 | Mary       | Smith      | LSMITH   | 011.44.1345.729268 |
|         182 | Mary       | Sullivan   | MSULLIVA | 650.507.9878       |
|         157 | Mary       | Sully      | PSULLY   | 011.44.1345.929268 |
|         180 | Mary       | Taylor     | WTAYLOR  | 650.507.9876       |
|         117 | Mary       | Tobias     | STOBIAS  | 515.127.4564       |
|         144 | Mary       | Vargas     | PVARGAS  | 650.121.2004       |
|         162 | Mary       | Vishney    | CVISHNEY | 011.44.1346.129268 |
|         123 | Mary       | Vollman    | SVOLLMAN | 650.123.4234       |
|         150 | Mary       | Weedman    | PTUCKER  | 011.44.1344.129268 |
|         200 | Mary       | Whalen     | JWHALEN  | 515.123.4444       |
+-------------+------------+------------+----------+--------------------+
56 rows in set (0.01 sec)

ICP的原理简单说来就是将可以利用索引筛选的 where 条件在存储引擎一侧进行筛选，而不是将所有 index access 的结果取出放在 server 端进行 where 筛选。

以上面的查询为例，在没有 ICP 时，首先通过索引前缀从存储引擎中读出 56 条 first_name 为 Mary 的记录，然后在 server 端用 where 筛选 last_name 的 like 条件（回表操作）；而启用 ICP 后，会继续在 56 条数据上继续使用索引对 last_name 进行匹配，筛选掉不符合 where 条件的记录，这个过程不需要读出整条记录，同时只返回给 server 筛选后的 6 条记录，因此提高了查询性能。

ICP 使用场景

上述是关于 Index Condition Pushdown（ICP）优化的适用条件的说明。让我们逐一解释每个场景，并提供相应的示例：

1）ICP 适用于range, ref, eq_ref, and ref_or_null 访问方法，当需要访问完整的表行时。

示例：考虑一个包含 "users" 表的数据库，其中有一个名为 "age" 的索引。我们想要查询年龄大于等于 18 岁且性别为男性的用户的信息。

SELECT * FROM users WHERE age >= 18 AND gender = 'Male';

在这种情况下，ICP 可以应用于 "age" 索引和 "gender" 索引，以减少访问完整表行的次数。

2.）ICP 可以用于 InnoDB and MyISAM 表，包括分区的 InnoDB 和 MyISAM 表。

示例：对于 InnoDB 表和 MyISAM 表，ICP 都可以应用。例如，在具有 InnoDB 存储引擎的数据库中，ICP 可以用于减少对辅助索引的完整行读取次数。

3）对于 InnoDB 表，ICP 仅适用于辅助索引。ICP 的目标是减少完整行读取的次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经被读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。

示例：考虑一个包含 "orders" 表的数据库，其中有一个聚集索引（主键索引）和一个辅助索引。我们想要查询购买了特定产品的订单信息。

SELECT * FROM orders WHERE product_id = '123';

在这种情况下，ICP 可以应用于辅助索引，以减少对完整行的读取次数。

4）ICP 不支持在虚拟生成列上创建的辅助索引。InnoDB 支持对虚拟生成列创建辅助索引。

示例：考虑一个包含 "users" 表的数据库，其中有一个名为 "full_name" 的虚拟生成列。我们想要查询具有特定全名的用户信息。

SELECT * FROM users WHERE full_name = 'John Doe';

在这种情况下，ICP 无法应用于虚拟生成列上的辅助索引。

5）无法下推涉及子查询的条件。

示例：考虑一个包含 "users" 和 "orders" 两个表的数据库。我们想要查询购买了特定产品的用户的信息。

SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE product_id = '123');

在这种情况下，涉及子查询的条件无法被下推。

6）无法下推涉及存储函数的条件。存储引擎无法调用存储函数。

示例：考虑一个包含 "users" 表的数据库，其中有一个名为 "get_age()" 的存储函数。我们想要查询年龄大于等于 18 岁的用户的信息。

SELECT * FROM users WHERE get_age() >= 18;

在这种情况下，涉及存储函数的条件无法被下推。

7）无法下推触发器中的条件。

示例：考虑一个包含触发器的数据库，当插入或更新 "users" 表时触发。我们想要查询满足特定条件的用户的信息。

SELECT * FROM users WHERE condition = 'some_condition';

在这种情况下，触发器中的条件无法被下推。

8）（适用于 MySQL 8.0.30 及更高版本）无法将条件下推到包含对系统变量的引用的派生表。

示例：考虑一个包含派生表的查询，其中引用了系统变量。

SELECT * FROM (SELECT * FROM users WHERE age >= @min_age) AS derived_table;

在这种情况下，条件无法被下推到引用了系统变量的派生表。

总结：ICP 是一种针对 MySQL 的查询优化技术，具有一些适用条件和限制。它可以减少对完整表行的访问次数，从而提升查询性能。然而，需要注意的是，在特定的场景和条件下，ICP 可能无法应用。