服务器之家
您的位置： 首页 数据库 mysql

MySQL特性：深入理解ICP

100人浏览   2025-02-25 00:11:07

什么是ICP

ICP全称Index Condition Pushdown ，是MySQL用索引去表里取数据的一种优化。原来的数据读取逻辑是，MySQL Server层根据索引通过引擎层在基表中寻找数据行，取到数据后再去为这些数据行进行WHERE其他条件的过滤。在ICP优化后，如果部分WHERE条件能使用索引中的字段，MySQL Server 会把这部分条件下推到引擎层。引擎层按索引取出数据后会根据下推的条件进行一次过滤。ICP能减少引擎层访问基表的次数和MySQL Server 访问引擎层的次数。

实例演示

准备一个表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `uname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `intro` varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`uname`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

往表里插100万条数据

BEGIN 

DECLARE i INT DEFAULT 1;

WHILE i<=1000000
DO 
insert into `user` values (i, MOD(i-1,100) + 1, CONCAT('name', i), REPEAT(CONCAT('intro', i), 40)); 
SET i=i+1; 
END WHILE ; 
commit; 

END

从表里查找数据

SELECT * FROM `user` WHERE age = 99 AND uname LIKE "%999"

在不使用ICP的情况下查看执行计划


运行这条sql


开启ICP


对比没有开启的执行计划，我们可以看到extra的内容变成了Using index condition，表示使用了索引下推。


执行以后，Sending data从1秒多变成了0.005秒，可见，在多个where条件并有部分条件被索引覆盖的情况下，通过ICP特性可极大的提高数据读取效率。

ICP 适用的场景

  • ICP 用于访问方法是 range/ref/eq_ref/ref_or_null，并且查询的数据列中存在索引无法覆盖的列。如果刚才的示例中，把“SELECT *”改成“SELECT id, age, uname”，则不会使用ICP，因为需要查询的数据列被索引“idx_age_name”覆盖。
  • ICP适用于 InnoDB 和 MyISAM 的表，包括分区的表。
  • 对于 InnoDB 表，ICP只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。对于 InnoDB 的聚簇索引，完整的记录已经读进 InnoDB 的缓存，使用 ICP 不能减少 I/O 。

相关推荐