复合索引 顺序 复合索引(a,b,c)

前言举例

数据库的复合索引不是用到哪列，就在哪列上创建。遇到以下这种情况就不能创建复合索引。

举个例子

数据库表

这张表有什么特点？
第一、经a、b和c都经常要被查询，并且有经常a、b、c组合查询的可能性。这就给了我们建索引的理由。
第二、a列区分度不大，b和c都有区分度。请问这种场景如何建索引？

下面看我们思考的逻辑：

1. 很直观的想法，分别在a、b和c上分别建单列索引，即我们最终有三个索引，分别是(a)、(b)、(c)。这当然可以。但还有没有更好的？
2. 比如在(a、b)、(b、c)、(a、c)上建复合索引。这样当然也可以。但维护起来开销比较大，还有没有更好的？
3. 在(a、b、c)上建复合索引。这样维护起来开销也很大。并且a作为先导列，区分度不大，不是一个很好的选择。
4. 最好的，应该是在(b、c)上建立复合索引，在c上建单列索引，这样(b、c)索引可以覆盖到a and b和b and c查询，c单列索引可以覆盖到a和b查询。当然口说无凭，最终的效果还要看oracle的执行计划。这里不再贴执行计划了，感兴趣可以自己尝试以下。

执行计划

首先创建表

CREATE TABLE sunyang_test(         id number,         a number,  b number,  c number )tablespace ebiz; alter table sunyang_test add constraint pk_sunyang_test primary key (id) using index tablespace ebiz;

插入十万条数据

begin         for i in 1 .. 100000 loop                 insert into sunyang_test values(i,mod(i,2),mod(i,20000),mod(i,20000));         end loop;         commit; end;

创建索引

create index idx_sunyang_test_bc on lingmou_test(b,c) tablespace ebiz; create index idx_sunyang_test_c on lingmou_test(c) tablespace ebiz;

执行下面的SQL来进行测试

1、

select * from sunyang_test where b=5000;

执行计划-1

2、

select * from sunyang_test where c=5000;

执行计划：

执行计划-2

3、

select * from sunyang_test where a=1 and b=5000;

执行计划：

执行计划-3

4、

select * from sunyang_test where a=1 and c=5000;

执行计划：

执行计划-4

5、

select * from sunyang_test where b=5000 and c=5000;

执行计划：

执行计划-5

6、

select * from sunyang_test where a=1 and b=5000 and c=5000;

执行计划：

执行计划-6

可以看到，在a列区分度不大，且需要a、b、c单列查询或组合查询时，这样建索引将所有查询情况都覆盖到了，并且索引不复杂。

结束语

复合索引的设计，对于前导列的选择(也就是复合索引中的第一个列)非常重要，至少不应该在这里选择a作为一个复合索引的前导列，因为a的区分度太低了。有人说，不是有INDEX SKIP SCAN吗？这个确实要看a的实际值，如果a的取值很少，比如这里的两个，那么可以走INDEX SKIP SCAN，否则开销是很大的，往往Oracle直接去FULL TABLE SCAN了，也不会去INDEX SKIP SCAN，因为其涉及到索引分裂。