[Index]组合索引(Composite Index)中多个字段的顺序

最新推荐文章于 2024-06-19 16:24:34 发布

最新推荐文章于 2024-06-19 16:24:34 发布 · 650 阅读

文章标签：

#数据库

本文探讨了在数据库中创建组合索引时，字段顺序选择的重要性。重点指出，应将越离散的字段置于索引的前列，以优化查询效率。尽管在使用等号条件时字段顺序对效率影响较小，但合理布局仍能显著提高性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一个表中有三个字段，XX,YY,ZZ，如果要建立给这三个字段建组合索引(Composite Index)，组合索引中字段的顺序应该遵循怎样一个原则。

一般的原则：越离散的字段越靠前。哪个列可以降低索引的扫描成本就放在前面。

比如：下位三个字段的离散情况
XX:2
YY:1000
ZZ:50000

那么建立索引的顺序应该为：ZZ,YY,XX

CREATE INDEX t_idx ON t (zz,yy,xx);

但是如果where条件中，三个字段的条件都是通过"="号连接的，那么组合索引中字段的顺序就是无所谓了。

Refer：https://forums.oracle.com/forums/thread.jspa?threadID=2425684

Because you are using equality conditions in your predicate, then the order should be XX, YY, ZZ for maximum compression. Others are suggesting ZZ should be first because it is more selective (probably). But if you are using equals (=) for all 3, then that really does not matter.

或者It’s Less Efficient To Have Low Cardinality Leading Columns In An Index (Right) ? （轻功需好）

In actual fact, there’s no real difference in navigating to the specific leaf block of interest for an index on (ID, CODE) compared to an index based on (CODE, ID), providing both indexed columns are known.

再或者 http://www.oraclemagician.com/white_papers/index_order.pdf

Consider the following indexes:
INDEX1: (ZIP_CODE, GENDER)
INDEX2: (GENDER, ZIP_CODE)
Assume we are looking for the combination of Zip_Code = 94568 and Gender = ‘Male.’ Before Oracle traverses the index, it combines the two conditions. The composite value, something like 94568_Male has the same discriminatory value as Male_94568.