这段时间做一个大数据量查询优化时偶然发现一个和分区有关的MySQL 优化特性ICP的例子，回来翻阅一下MySQL的参考手册，找了些数据测试了一下，对这个case有了进一步的了解，在这里分享一下。先介绍一下MySQL的ICP (Index Condition Pushdown，索引条件下推)优化特性。
1 Index Condition Pushdown，索引条件下推（ICP）
分布式数据库里有一项存算分离技术，这项技术的一个重要的特性计算下推，将部分计算下推到存储引擎里执行。Oracle 一体机Exadata一个重要的特性也是数据密集处理查询offload到存储节点的智能软件进行。实际上，MySQL也是一个存算分离的结构，它的引擎和计算引擎也是分离的。而且MySQL的存储引擎也执行了大量的计算操作。ICP就是一种优化计算下推的技术，它的核心思想是将WHERE条件中索引相关的过滤操作"下推"到存储引擎层执行。传统方式中，存储引擎先通过索引定位记录，然后将完整记录返回给服务器层进行过滤；而ICP允许存储引擎在读取索引时就应用部分WHERE条件进行过滤。当查询使用二级索引且WHERE条件包含索引列和非索引列时，ICP可以将索引列的过滤条件下推到存储引擎。这样存储引擎在扫描索引时就能提前过滤掉不符合条件的记录，减少回表操作和服务器层处理的数据量，显著提升查询性能，特别是对于组合索引和范围查询场景效果明显。ICP通过减少I/O和CPU开销来优化查询效率。
2 测试数据
mysql> show create table sales;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sales | CREATE TABLE `sales` (
`id` int NOT NULL AUTO_INCREMENT,
`product_id` int NOT NULL,
`customer_id` int NOT NULL,
`sale_date` date NOT NULL,
`amount` decimal(10,2) NOT NULL,
`region` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`,`sale_date`),
KEY `idx_sales_cust_id` (`customer_id`),
KEY `idx_p_c_r` (`product_id`,`customer_id`,`region`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`sale_date`))
(PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)表里的数据
mysql> select * from sales limit 5;
+----+------------+-------------+------------+--------+-----------+
| id | product_id | customer_id | sale_date | amount | region |
+----+------------+-------------+------------+--------+-----------+
| 7 | 254 | 1955 | 2018-03-16 | 895.48 | North |
| 28 | 298 | 3207 | 2018-11-03 | 970.72 | South |
| 56 | 352 | 4006 | 2018-01-20 | 139.03 | Southwest |
| 57 | 168 | 1631 | 2018-09-17 | 895.66 | South |
| 61 | 335 | 1238 | 2018-06-27 | 959.53 | West |
+----+------------+-------------+------------+--------+-----------+
5 rows in set (0.00 sec)3 ICP应用的普通场景
ICP只能应用于使用辅助索引访问表的场景，不能用于主键访问表的场景。其典型的场景是表上创建了联合索引，但却不能充分利用联合索引的情况下。以sales表的联合索引idx_sales_cust_id为例，查询where条件包含product_id列，但没有customer_id列，或者有customer_id，但region列是like ‘%th’。这样语句只能根据product_id列查询索引后回到MySQL层面回表后应用其它查询条件。ICP优化的目的是将customer_id和region的条件下推到存储引擎在索引查找时执行，减少回表次数。比如，现在要查询的时id为254的产品在North区域的销售信息，sql语句如下
select * from sales where product_id=254 and region=‘North’；
在开启和未开启ICP时语句的实际执行计划（MySQL 8.0 可以用explain analyze获得）对比如下：
--ICP开启（默认情况下）
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on sales using idx_p_c_r (product_id=254), with index condition: (sales.region = 'North') (cost=73.8 rows=284) (actual time=0.0631..0.254 rows=33 loops=1)|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--关闭ICP
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (sales.region = 'North') (cost=73.8 rows=28.4) (actual time=0.25..1.41 rows=33 loops=1)
-> Index lookup on sales using idx_p_c_r (product_id=254) (cost=73.8 rows=284) (actual time=0.242..1.34 rows=284 loops=1)|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+首先注意到的是不论ICP是否开启，MySQL优化器对语句评估的成本相同（cost=73.8），对返回行数的评估也都是不准确的，这可能意味着如果有其它可行执行计划，评估成本低于73.8，优化器会选择其它的执行计划，社区内有一篇文章技术分享 | SQL 优化：ICP 的缺陷对此有详细的说明，即ICP不会影响执行计划的选择，只会影响已被选择的执行计划的执行。ICP开启时（这个特性MySQL数据库默认是开启的），语句执行了ICP，使用索引idx_p_c_r，ICP的条件是sales.region = ‘North’，这个语句实际执行时返回了33行，执行时间0.06秒；当关闭ICP时，语句执行的ref操作，分为两个步骤，首先查询索引idx_p_c_r，索引的查询条件是product_id=254，这个操作优化器的评估和实际执行都是准确的，评估和实际返回的行数都是284行，实际的执行时间也较长，达到了0.24秒以上，然后查询sales表，根据条件对索引返回的行进行过滤），索引返回了284行，这一步左就要对根据索引返回的主键对sales表进行284此查询并过滤。执行完这两部后，实际返回33行，消耗时间0.25秒。从这两个执行计划来看，ICP的执行效率回表次数更少，效率更高，在where条件选择性较高的情况下效率接近覆盖索引扫描。
4 分区场景下ICP的应用
在这次sql优化中，偶然发现ICP不但可以用于联合索引的场景，有时也可以用于单列索引的场景，这次是一个分区的场景。还是用之前的数据，运行下面的SQL
select * from sales where sale_date=‘2018-03-16’ and customer_id=1631;
在ICP开启的情况下语句实际的执行计划如下：
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on sales using idx_sales_cust_id (customer_id=1631), with index condition: (sales.sale_date = DATE'2018-03-16') (cost=1.82 rows=7) (actual time=0.0159..0.0159 rows=0 loops=1)|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+这个执行计划明显优于ICP关闭时的执行计划
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (sales.sale_date = DATE'2018-03-16') (cost=1.82 rows=0.7) (actual time=0.192..0.192 rows=0 loops=1)
-> Index lookup on sales using idx_sales_cust_id (customer_id=1631) (cost=1.82 rows=7) (actual time=0.0636..0.104 rows=7 loops=1)|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+idx_sales_cust_id是创建在cust_id列上的单列索引，为什么这里ICP也会生效呢？理解了MySQL索引原理后，略加思索便会对个中道理了然于胸了。idx_sales_cust_id存储的值是sales表的主键，sales表的主键是联合主键（id,sale_date），包含sale_date列，这样，索引idx_sales_cust_id里也包含了sale_date列的信息，ICP也就可以根据这个信息进行过滤了。明白了这个道理，将其推广一下，对于联合主键的，查询条件中只要包含主键列，就可以利用单列索引进行ICP优化了。下面是一个简单的例子
测试数据
mysql> show indexes from weatherdata;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| weatherdata | 0 | PRIMARY | 1 | log_date | A | 4017 | NULL | NULL | | BTREE | | | YES | NULL |
| weatherdata | 0 | PRIMARY | 2 | time | A | 1155084 | NULL | NULL | | BTREE | | | YES | NULL |
| weatherdata | 0 | PRIMARY | 3 | time | A | 4623086 | NULL | NULL | | BTREE | | | YES | NULL |
| weatherdata | 1 | idx_wd | 1 | weather | A | 9 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)表weatherdata除了联合主键（log_date，time，weather）外还有一个weather列上的辅助索引idx_wd，下面是一条利用这个辅助索引查询数据的例子
mysql> explain analyze select * from sales where sale_date='2018-03-16' and customer_id=4799;
mysql> explain analyze select * from weatherdata where weather='Nebel-Regen' and station =1;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on weatherdata using idx_wd (weather='Nebel-Regen'), with index condition: (weatherdata.station = 1) (cost=12910 rows=255176) (actual time=12.5..85.6 rows=32455 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)语句的执行计划依然执行了ICP。