Index 组合索引 Composite Index 中多个字段的顺序

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.youkuaiyun.com/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

               

一个表中有三个字段，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.

           

给我老师的人工智能教程打call！http://blog.youkuaiyun.com/jiangjunshow