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'
对于使用来自Customers ='M'且date_of_birth <'2000-01-01'的客户的复合b树索引SELECT *的所有列的查询
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);
CREATE INDEX low_then_high ON customer(性别,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.
类似的问题是如何将低/高基数列配对为复合索引?但接受的答案没有解释推荐背后的原因。
I am asking about MySQL but I'm guessing that the answer would apply to any b-tree index.
我问的是MySQL,但我猜这个答案适用于任何b树索引。
1 个解决方案
#1
2
No. Cardinality in multi-column INDEXes does not matter. However, the usage of the columns does matter.
不。多列INDEX中的基数无关紧要。但是,列的使用确实很重要。
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.
索引中的第一列需要使用=进行测试。之后,您会在“范围”中获得一个裂缝,例如 <或between。 in位于灰色区域,有时优化为=",有时像范围。
More specifically, for where gender = 'M' AND date_of_birth < '2000-01-01', cardinality does not matter.
更具体地说,对于性别='M'和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 .
我在Cookbook中进一步讨论了这些问题。
#1
2
No. Cardinality in multi-column INDEXes does not matter. However, the usage of the columns does matter.
不。多列INDEX中的基数无关紧要。但是,列的使用确实很重要。
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.
索引中的第一列需要使用=进行测试。之后,您会在“范围”中获得一个裂缝,例如 <或between。 in位于灰色区域,有时优化为=",有时像范围。
More specifically, for where gender = 'M' AND date_of_birth < '2000-01-01', cardinality does not matter.
更具体地说,对于性别='M'和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 .
我在Cookbook中进一步讨论了这些问题。