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

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 应该是符合索引下推的条件的，但是实际并没有使用,有大佬可以解答一下吗？

10 replies • 2024-02-27 09:15:57 +08:00

bigfei

Feb 23, 2024

以下 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

Feb 23, 2024 via Android

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

cxshun

Feb 23, 2024

你这里的查询语句直接使用了覆盖索引，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

Feb 23, 2024

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

elza

Feb 23, 2024

你这个使用的是覆盖索引

q1450718943

Feb 23, 2024

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

q1450718943

Feb 23, 2024

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

cxshun

Feb 26, 2024

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

q1450718943

Feb 26, 2024

@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

Feb 27, 2024

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

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