where条件顺序和复合索引字段顺序

Question

最近在优化线上库的时候经常会遇到如下一个问题：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

Answer 1

贴完整sql  也可能建立(c,b,a)更好

Answer 2

create index xxx on table1 (a,b,c,d)

Answer 3

你也认为复合索引的字段顺序要和where条件的顺序一样吗？

Answer 4

(c,b,a)没有更好
我比较奇怪，复合索引的字段顺序和where条件的顺序需要一致吗？

Answer 5

你也认为复合索引的字段顺序要和where条件的顺序一样吗？不需要。 但至少 d 必须在最后。 (a,b,c) 按查询的频率，或者不同值分布来决定以提高效率。

Answer 6

你也认为复合索引的字段顺序要和where条件的顺序一样吗？不需要。 但至少 d 必须在最后。 (a,b,c) 按查询的频率，或者不同值分布来决定以提高效率。
假设有这么一条query：
select * from t where a and b; 其中，a的cardinality比b的小很多，
此时建立的索引idx_1 (a,b) 比idx_2(b,a)好非常多。能帮忙解释下吗？

Answer 7

你也认为复合索引的字段顺序要和where条件的顺序一样吗？不需要。 但至少 d 必须在最后。 (a,b,c) 按查询的频率，或者不同值分布来决定以提高效率。
假设有这么一条query：
select * from t where a and b; 其中，a的cardinality比b的小很多，
此时建立的索引idx_1 (a,b) 比idx_2(b,a)好非常多。能帮忙解释下吗？
这个要具体的分析，很难直接下结论。比如 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% 或者某个阈值，则 直接进行全部结点的遍历。
关于索引，没有直接的一统的解决方案，需要非常具体的情况参数才能决定。

Answer 8

写出具体的语句，给贴出执行计划出来，或者 a ,b,,c,d 的具体语句。

Answer 9

优化器可以自主调整where顺序，所以where的顺序其实不会影响性能。

Answer 10

idx_1(d,c,b,a) 这个不对，从selectivity是a<b<c<d来看，应该是(c,b,a,d)合理