对于不同基数的列的复合索引，顺序重要吗? [英] 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屋！