MySQL 5.7.33升级至8.0.31性能骤降：为何停用ICP优化？

问题背景 #

数据表结构（简化后）：

CREATE TABLE UserData (
  id bigint NOT NULL AUTO_INCREMENT,
  userId bigint NOT NULL DEFAULT '0',
  c6 int NOT NULL DEFAULT '0',
  hidden int NOT NULL DEFAULT '0',
  c22 int NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  KEY userId_hidden_c6_c22_idx (userId,hidden,c6,c22)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 

在MySQL 5.7中执行以下查询性能良好：

select * from UserData use index (userId_hidden_c6_c22_idx) where (userId = 123 AND hidden = 0) order by id DESC limit 10 offset 0;

10 rows in set (0.03 sec)

升级至MySQL 8.0后，同查询性能暴跌：

select * from UserData use index (userId_hidden_c6_c22_idx) where (userId = 123 AND hidden = 0) order by id DESC limit 10 offset 0;

10 rows in set (1.56 sec)

通过EXPLAIN对比执行计划：

• MySQL 5.7的Extra字段显示Using index condition; Using filesort（启用了索引条件下推ICP）
• MySQL 8.0的Extra字段仅显示Using filesort（未启用ICP）

数据表共约1.5亿行，目标用户userId=123对应约7.5k条记录。

性能下降原因 #

MySQL 8.0对优化器的启发式规则和成本计算模型做了调整：

1. ICP收益评估逻辑变化：当优化器估算通过索引过滤后需要回表的数据量较大时，会认为ICP带来的过滤收益不足以抵消其额外开销，从而放弃启用ICP。
2. 统计信息与成本估算差异：8.0的成本模型相比5.7更复杂，对索引匹配行数、回表成本的评估逻辑有改动，导致优化器选择了不使用ICP的执行计划。

解决方法 #

1. 强制触发ICP并验证 #

虽然index_condition_pushdown参数默认是开启的，但可以通过FORCE INDEX强化索引使用提示，同时结合参数调整确保ICP生效：

-- 确认会话级ICP开启（默认已开启）
SET SESSION optimizer_switch='index_condition_pushdown=on';
-- 使用FORCE INDEX强制指定索引，提升优化器启用ICP的概率
select * from UserData FORCE INDEX (userId_hidden_c6_c22_idx) where (userId = 123 AND hidden = 0) order by id DESC limit 10 offset 0;

2. 优化索引结构（推荐） #

针对该查询的业务逻辑，创建更贴合的索引可以彻底解决性能问题：

-- 创建包含查询条件和排序字段的索引，避免filesort与无效回表
CREATE INDEX idx_userid_hidden_id_desc ON UserData(userId, hidden, id DESC);

使用该索引后，查询可直接通过索引定位到符合条件的前10条数据，无需回表后再排序，性能会大幅提升。

3. 更新统计信息 #

执行统计信息更新，让优化器获得更准确的数据分布，可能重新选择启用ICP的计划：

ANALYZE TABLE UserData;

4. 调整优化器成本参数（谨慎使用） #

降低行评估成本，让优化器更倾向于使用ICP：

-- 会话级调整，仅影响当前会话
SET SESSION optimizer_costs.row_evaluate_cost = 0.25;

注意：该参数调整可能影响其他查询的执行计划，需在测试环境验证后再应用到生产。

内容的提问来源于stack exchange，提问作者Jxtps