56,681
社区成员
发帖
与我相关
我的任务
分享where条件顺序和复合索引字段顺序
最近在优化线上库的时候经常会遇到如下一个问题：
where a AND b AND c group by d，其中，selectivity是a<b<c<d
如果按复合索引建立规则，也就是，选择性高的置前，那应该建立 idx_1(d,c,b,a)
但事实上，idx_1的效果非常差。反而是，建立idx_1(a,b,c,d)效果出奇的好
MySQL的版本是 5.1 && 5.5
where a AND b AND c group by d，其中，selectivity是a<b<c<d
如果按复合索引建立规则，也就是，选择性高的置前，那应该建立 idx_1(d,c,b,a)
但事实上，idx_1的效果非常差。反而是，建立idx_1(a,b,c,d)效果出奇的好
MySQL的版本是 5.1 && 5.5
...全文
请发表友善的回复…
发表回复
fcy_n 2014-03-25
- 打赏
- 举报
idx_1(d,c,b,a) 这个不对，从selectivity是a<b<c<d来看，应该是(c,b,a,d)合理
linwaterbin 2014-03-24
- 打赏
- 举报
优化器可以自主调整where顺序，所以where的顺序其实不会影响性能。
ACMAIN_CHM 2014-03-23
- 打赏
- 举报
你也认为复合索引的字段顺序要和where条件的顺序一样吗？[/quote]不需要。 但至少 d 必须在最后。 (a,b,c) 按查询的频率，或者不同值分布来决定以提高效率。[/quote]
假设有这么一条query：
select * from t where a and b; 其中，a的cardinality比b的小很多，
此时建立的索引idx_1 (a,b) 比idx_2(b,a)好非常多。
能帮忙解释下吗？[/quote]
这个要具体的分析，很难直接下结论。
比如 a.cardinality = 1 ，A中仅有一种值。 这种情况下显然 idx_2(b,a) 的效率高。 MYSQL会按 B=x 的值在BTREE上找到 b=x 的结点，然后因为 a.cardinality = 1 MYSQL就直接全部节点的输出。
或者 a.cardinality = 2, A中仅有两种值,比如(男，女) , 依然是 idx_2(b,a) 的效率高, 因为 a.cardinality = 2 ，预测符合条件 a='M' 的记录大于 30% 或者某个阈值，则 直接进行全部结点的遍历。
关于索引，没有直接的一统的解决方案，需要非常具体的情况参数才能决定。
卖水果的net 2014-03-23
- 打赏
- 举报
写出具体的语句，给贴出执行计划出来，或者 a ,b,,c,d 的具体语句。
linwaterbin 2014-03-22
- 打赏
- 举报
嗯。这确实是个很有效的经验法则，如果按复合索引的选择性来建立，感觉效果很差。不知道为什么。
linwaterbin 2014-03-22
- 打赏
- 举报
你也认为复合索引的字段顺序要和where条件的顺序一样吗？[/quote]不需要。 但至少 d 必须在最后。 (a,b,c) 按查询的频率，或者不同值分布来决定以提高效率。[/quote]
假设有这么一条query：
select * from t where a and b; 其中，a的cardinality比b的小很多，
此时建立的索引idx_1 (a,b) 比idx_2(b,a)好非常多。
能帮忙解释下吗？
ACMAIN_CHM 2014-03-22
- 打赏
- 举报
你也认为复合索引的字段顺序要和where条件的顺序一样吗？[/quote]不需要。 但至少 d 必须在最后。 (a,b,c) 按查询的频率，或者不同值分布来决定以提高效率。
WWWWA 2014-03-22
- 打赏
- 举报
一般来讲，是按照条件来建立索引的
linwaterbin 2014-03-22
- 打赏
- 举报
(c,b,a)没有更好
我比较奇怪，复合索引的字段顺序和where条件的顺序需要一致吗？
linwaterbin 2014-03-22
- 打赏
- 举报
你也认为复合索引的字段顺序要和where条件的顺序一样吗？
ACMAIN_CHM 2014-03-22
- 打赏
- 举报
create index xxx on table1 (a,b,c,d)
rucypli 2014-03-22
- 打赏
- 举报
贴完整sql 也可能建立(c,b,a)更好
benluobo 2014-03-22
- 打赏
- 举报
能否贴出两种情况的执行计划
