请教一个 MYSQL 联合索引字段顺序的问题

请教一个 MySQL 联合索引方案
假设有一个表
table a
column x,y,.....

现在有一个 sql ，select * from a where x = ? and y in (?,?,?,...)

x 的区分度很低，y 的区分度很高。

联合索引是建（ x,y ）还是（ y,x ）。

考虑到区分度高索引列在前面的原则似乎是因该是（ y,x ）。但是考虑到索引下推，似乎又应该是（ x,y ）?

索引

的区

MySQL

分度

21 条回复 • 2023-08-30 18:45:40 +08:00

wetalk

2023-03-03 11:09:20 +08:00

in 查询，条件很多吗

yxjn

2023-03-03 11:20:33 +08:00

@wetalk 有可能会很多

LeegoYih

2023-03-03 11:32:32 +08:00

分别建 2 个索引，然后打开优化器看看执行计划，经验之谈不如手动试一试

optional

2023-03-03 11:34:37 +08:00 via iPhone

单独 y 就行

wjfz

2023-03-03 11:37:14 +08:00

导两张表分别 explain 一下得到的答案最确定。

tairan2006

2023-03-03 11:47:55 +08:00 via Android

y,x 吧

如果 in 后面很多的话，即使区分度高数据集可能也很大…explain 的结果跟数据和查询条件都有关，又不是不变的…

iffi

2023-03-03 12:08:15 +08:00

# 一、多列索引字段顺序问题
1 、尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。
2 、选择区分度小的字段在前面，例如 idx_sex_city(sex,city)
# 二、减小 where in() 条件数据量，需要注意控制 IN 的中值的个数，建议 in 数量小于 200 ，(参数 eq_range_index_dive_limit)

yxjn

2023-03-03 12:16:25 +08:00

@iffi 为什么是区分度小的放前面，不应该是区分度高的放前面么

lovelylain

2023-03-03 12:18:17 +08:00 via Android

为什么没人选(x,y)，这样不是可以先筛掉非 x 的（虽然数量不多），再按 y 筛选吗，单看这条语句，不至于比只用 y 有副作用吧？

wangyzj

2023-03-03 12:49:01 +08:00

x.y 把
explain 一下
先重建一个新的

wetalk

2023-03-03 13:10:31 +08:00

@yxjn 我这样写，不知道你能不能看懂，单独为 y 建索引
select a.* from a join
(
SELECT ?1 as y
union all
SELECT ?2 as y
union all
SELECT ?3 as y ...
) as t on a.y = t.y and a.x = ?

liuxu

2023-03-03 13:16:44 +08:00

普通情况，简单的话就(y,x)

其他情况，你可能需要看下“基于成本的优化”、“复合索引”和“IN 子查询”相关内容

https://www.liuquanhao.com/posts/mysql%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E5%99%A8%E4%BD%BF%E7%94%A8%E6%8C%87%E5%8D%97/

除非 sql 很复杂，否则楼上将 in 手动重写成 join 其实没必要，mysql 优化器可能会自动重写

james2013

2023-03-03 13:45:37 +08:00

单独建 y 就可以了
区分度很低放到索引里是多余的

vagusss

2023-03-03 14:26:12 +08:00

为啥非得联合索引

ooToo

2023-03-03 14:31:43 +08:00

select * 用不到索引下推.
对于你的 sql （ x,y ）还是（ y,x ）区别不大, 甚至(y)也可以

imokkkk

2023-03-03 14:50:22 +08:00

三星索引来看，y 建索引属于第 3 颗星，比重更大，效果更好

yxjn

2023-03-03 16:28:44 +08:00

@ooToo 为什么 select *用不到索引下推，又不是回表

yxjn

2023-03-03 16:29:30 +08:00

@jobmailcn 我和你一个想法，但是没有理论支撑。explain 也是能命中 x,y 索引的。

UN2758

2023-03-03 18:08:12 +08:00

@yxjn 我也感觉很不理解，select *还是具体 column 只会影响覆盖索引，x ，y 前后顺序不影响索引下推发挥作用吧。有关系的是优化器在选择先 x 后 y 还是 yx

liuxu

2023-03-03 18:51:42 +08:00

select * 会被 mysql 优化器重写成所有列，没什么问题

之所以不用(x,y)是因为 x 区分度很低，扫 x 时索引页占用更多内存，而且 x 区分度低到一个层次的话，优化器可能直接走全表扫描了
这里有一份和区分度基数有关的数据
https://www.liuquanhao.com/posts/mysql%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E5%99%A8%E4%BD%BF%E7%94%A8%E6%8C%87%E5%8D%97/#%E4%B8%B4%E6%97%B6%E8%AE%A1%E5%88%92

54qyc

2023-08-30 18:45:40 +08:00

@yxjn
@ooToo
select * 可以用到索引下推

@yxjn OP 最终用的啥，这里(x,y)用到索引下推肯定比单一索引好吧？