对于不同基数的列的复合索引,顺序重要吗? [英] For a composite index of columns of different cardinality, does order matter?
问题描述
对于使用复合b树索引的所有列的查询
SELECT * from customers where gender = 'M' AND date_of_birth < '2000-01-01'
For a query that uses ALL columns of a composite b-tree index
SELECT * from customers where gender = 'M' AND date_of_birth < '2000-01-01'
之间有区别吗
CREATE INDEX low_then_high ON customer (gender, date_of_birth);
CREATE INDEX high_then_low ON customer (date_of_birth, gender);
Is there a difference between
CREATE INDEX low_then_high ON customer (gender, date_of_birth);
CREATE INDEX high_then_low ON customer (date_of_birth, gender);
类似的问题是如何配对低/高基数列作为综合索引?,但是被接受的答案并不能解释该建议背后的原因.
A similar question is How to pair low/high cardinality columns as composite indexes? but the accepted answer did not explain the reason behind the recommendation.
我问的是MySQL,但我猜答案可能适用于任何b树索引.
I am asking about MySQL but I'm guessing that the answer would apply to any b-tree index.
推荐答案
否.多列INDEXes
中的基数无关紧要.但是,列的使用确实很重要.
No. Cardinality in multi-column INDEXes
does not matter. However, the usage of the columns does matter.
索引中的第一列需要使用=
进行测试.之后,您会在范围"(例如<
或BETWEEN
)上出现一条裂缝. IN
处于灰色区域,有时像=
一样优化,有时像范围一样.
The first column(s) in the index needs to be tested with =
. After that, you get one crack at a "range", such as <
or BETWEEN
. IN
is in a gray area where it sometimes optimized like =
, sometimes like a range.
更具体地说,对于where gender = 'M' AND date_of_birth < '2000-01-01'
,基数无关紧要.
(gender, date_of_birth) -- will use both columns.
(date_of_birth, gender) -- will ignore `gender` and not be as efficient.
类似地,请注意,由于=
与<
的关系,您提供的链接与您的情况不相同.
Similarly, note that the link you provided is not the same as your case because of =
versus <
.
我在 食谱 中进一步讨论了这些问题.
I discuss these issues further in my Cookbook .
这篇关于对于不同基数的列的复合索引,顺序重要吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!