关于索引下推，请教下大佬们

[q1450718943]

表结构如下

CREATE TABLE `user_cp` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cp_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'cpId 下划线分割',
  `intimacy` int(11) NOT NULL COMMENT '加了 add_exp 后的当前等级下的经验',
   `attenuation_utime` int(11) NOT NULL DEFAULT '0' COMMENT '扣减时间',
  `utime` int(11) NOT NULL COMMENT '更新时间',
  `ctime` int(11) NOT NULL COMMENT '时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cp_id_unique` (`cp_id`),
  KEY `idx_attenuationutime_utime_intimacy_cpid` (`attenuation_utime`,`utime`,`intimacy`,`cp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31988443 DEFAULT CHARSET=utf8

sql 语句

explain SELECT cp_id
FROM user_cp
WHERE utime < 1707912100
 AND attenuation_utime < 1708430500
 AND intimacy > 0

extra: Using where; Using index

MySQL 版本:5.7.32

按照我的理解，这个 SQL 应该是符合索引下推的条件的，但是实际并没有使用,有大佬可以解答一下吗？

[bigfei]

以下 GPT4 回答：

MySQL 的索引下推优化（ Index Condition Pushdown, ICP ）是 MySQL 5.6 版本引入的一种查询优化方式。它允许 MySQL 在索引扫描时就进行部分 WHERE 条件的过滤，而不是在全部数据检索后再进行过滤，从而提高查询效率。

然而，ICP 并不是对所有查询都有效，它对某些查询的优化效果更为明显，例如当数据表的行数非常大，且需要过滤的数据占比较小的情况下。

你的查询语句的 EXPLAIN 结果显示"Using where; Using index"，这表明 MySQL 在进行索引扫描时，已经使用了你定义的复合索引`idx_attenuationutime_utime_intimacy_cpid`，并且在扫描索引时就已经应用了 WHERE 条件进行了部分过滤。

但是，这并不意味着 MySQL 一定使用了 ICP 。ICP 的使用是由优化器决定的，优化器会根据表的统计信息以及查询条件来决定是否使用 ICP 。在某些情况下，即使查询符合 ICP 的使用条件，优化器也可能决定不使用 ICP ，例如当表的行数较小，或者需要过滤的数据占比较大的情况下。

在你的情况下，如果你确定你的查询符合 ICP 的使用条件，但 EXPLAIN 的结果显示没有使用 ICP ，那么可能的原因包括但不限于以下几点：

1. 表的统计信息不准确，导致优化器做出了错误的决策。你可以尝试使用`ANALYZE TABLE`语句更新表的统计信息，然后再查看 EXPLAIN 的结果。

2. MySQL 的配置参数没有正确设置。你可以检查`optimizer_switch`参数的设置，确保`index_condition_pushdown`选项被设置为了`on`。

3. 查询的过滤条件不满足 ICP 的使用条件。ICP 只对某些类型的查询有效，例如比较运算符（如<、>、=等）和某些函数（如 IS NULL 和 IS NOT NULL ）。如果查询的过滤条件包含其他类型的运算或函数，那么 ICP 可能不会被使用。

总的来说，ICP 的使用是由 MySQL 的优化器决定的，它会根据多种因素来决定是否使用 ICP 。如果你想了解更多关于 ICP 的信息，你可以查阅 MySQL 的官方文档。

[lbprivateacc]

看了下官方文档，貌似要回表查的时候才会使用

[cxshun]

你这里的查询语句直接使用了覆盖索引，Using Index ，有可能优化器觉得用了覆盖索引已经是比较优化的结果了，所以就不会使用索引下推。
按覆盖索引的作用来说，它是为了减少回表的次数，而覆盖索引了，实际上根本就不需要回表，所以就不会使用了。
按官方文档是这样的：
> ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

综上：我的理解是因为这里的覆盖索引，所以导致不会用索引下推来做优化。

[rockyliang]

试了一下，确实如上面回答说的，要回表的时候才会使用索引下推，将 SELECT cp_id 改为 SELECT * 就会使用索引下推

[elza]

你这个使用的是覆盖索引

[q1450718943]

@lbprivateacc
@cxshun
@elza
@rockyliang
是的，SELECT * 的时候才会使用索引下推,感谢回答。

[q1450718943]

@cxshun 本地测试了一下，虽然 extra 没有输出 using index condition ，但是 key len 是 115 。可惜线上 key len 是 4 ，rows 也很大，主要是这个问题。

[cxshun]

@q1450718943 感觉有点奇怪，你的 key len 最大也只是 11+11+11+25 = 58 ，这个 115 是不是命中了其他的 key 。
你可以提供 explain 的大概信息么？

[q1450718943]

@cxshun
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,user_cp,,index,idx_attenuationutime_utime_intimacy_cpid,idx_attenuationutime_utime_intimacy_cpid,115,,1,100,Using where; Using index

应该是 4 + 4 + 4 + 25*4+2+1 ，按字节算的。

[cxshun]

@q1450718943 对，不好意思，一下子弄混了，是按字节来算的。
key_len 是 115 应该是表示它使用了完整的索引，Using index 表示它使用了覆盖索引，因为这里用不了最左匹配，就相当于只能全索引扫描了，所以这里看起来没啥毛病。

线上的 key len 是 4 ，这个我暂时解释不了，看看有没有大佬能解释一下哈。