mysql 组合索引在范围查询和 order 下到达能不能用到索引

CREATE TABLE `info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `multi` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=999

select * from info where a = 1 and c = 200

这条 sql 到底能不能用到 c 索引？？？如果是索引下推的话，到底能不能推到 c 索引，然后减少回表次数

select * from info where a > 1 and b = 200
select * from info where a >= 1 and b = 200

上面两个 sql 都能用到 b 索引么？？我觉得是可以的，虽然在 a 在不等值下 b 是无序的，但是索引下推到 b ，能减少回表次数

select * from info where a > 1 and b < 200
select * from info where a >= 1 and b <= 200

我也觉得是能用到 b 索引，理由同上条

select * from info where a > 1 order by b
select * from info where a >= 1 order by b

这个 order by 是回表排序的么

9 条回复 • 2024-05-28 10:56:55 +08:00

sagaxu

195 天前

要看索引稀疏程度，也有可能以上查询全部不走索引，直接扫全表

keakon

195 天前

ICP is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows.
不等于是不能用 ICP 的。

3a10IgjVYjvsH93b

195 天前

此视频是对 DB 存储的数据结构 B Tree 和 B+ Tree 的讲解。

确定了数据的存储结构，上述问题就可以回答了。

3a10IgjVYjvsH93b

195 天前

@einvcz [

](DB 存储的数据结构)

3a10IgjVYjvsH93b

195 天前

@einvcz 咋把视频链接自动解析了。 \[

]

3a10IgjVYjvsH93b

195 天前

绝了。 https://ww w.youtube.com/watch? v=aZjYr87r1b8 。
手动去一下空格吧

wenxueywx

195 天前

1 、select * from info where a = 1 and c = 200
multi 索引总长度为 15 字节，可以用到 multi 索引的 a 字段（ 5 字节），不能用到 c 字段（不满足最左前缀匹配原则）；
icp 开启的情况下，可以把 c=200 的条件下推到引擎层过滤，可以减少回表次数
2 、select * from info where a > 1 and b < 200
首先，即使使用索引+icp ，也是和场景 1 相同，只能使用 multi 中 a 字段的索引，b<200 作为条件下推。
实际上，根据索引中 a 字段的基数来判断，只有在使用索引能大量减少扫描的行时才会使用索引+下推；假设表中有 10W 行数据，a 字段的取值范围是 1-100 ，在数据分布均匀的情况下，a 字段的基数为 1000 ，当你使用 a>1 and b<200 时，使用 multi 索引查询基本是全索引扫描+icp ，然后回表；全索引扫描代价与全表扫描差别不大；而当你使用 a>95 and b<200 时，通过 multi 索引的 a 字段可以过滤 95%的行，代价肯定比全表低。

main1234

195 天前

@keakon 其实不太明白为啥不等于不能使用下推

LiaoMatt

188 天前

select * from info where a > 1 and b = 200
select * from info where a >= 1 and b = 20
这两句 SQL 是用不到联合索引的 b 的, 因为联合索引是先按照 a 从小往大排序, 再基于 a 的顺序从小往大排 b, 这就意味着, 如果 a 不是等值, 那么 b 的顺序就没有办法保证, 只能一行一行扫描