2.3.4    复合索引

1.  复合索引设计

在工作中,我们时常会被问及一个问题:“我将如何选择索引列来建立复合索引呢?”举一个例子来说说看吧。

现在有一张表的三个列:A、B、C,在日常的业务应用中,会用过两两组合的查询,即:A and B,B and C,A and C,将如何建复合索引呢?这是一个很不明确的需求,我们很难以此为据来作出判断,需要进一步跟需求方沟通。

经过数据量和数据分布等情况的分析,确定是需要建索引的,问题将集中在如何去建,建什么样的索引。

架构师:“这三种场景发生的概率大致如何呢?”

开发者:“各占三分之一吧。”

架构师:“除此之外,还有这三个列上其他查询吗?”

开发者:“可能还有少量的B列和C列的单列查询吧,但是真的比较少。”

架构师:“那这三个列的数据区分度是怎么样的呢?”

开发者:“A列区分度比较低,B列和C列将比较高。”

架构师:“这真的是一个比较棘手的问题。……问题主要集中在这三个列的区分度上,如果A列很低,B和C很高的的话,我建议可以考虑不建复合索引,只建B列和C列的单列索引。”

开发者:“这是你的第一个建议吗?但是我并不是很确定A列的区分度是否真的像预期的那么小,或者说B列和C列的区分度未必像预期的那么大。”

架构师:“是的,如果用单列索引就能解决问题,当然是最好不过的了。但是,从你的描述中,我对使用单列索引还是有所担心的,我建议可以考虑建(B, C)复合索引和C列单列索引。这样,(B, C)复合索引可以覆盖B and C查询和A and B查询,C列单列索引可以覆盖A and C查询,因为A列区分度不高,可以不考虑其走索引。”

开发者:“那为什么不干脆建(B, C),(B, A),(C, A)三个复合索引呢?”

架构师:“这个当然很好了,但是索引维护开销太大,未必是好事。”

开发者:“那能不能使用INDEX SKIP SCAN的特性呢?”

架构师:“非必要时不要用,尽可能选择区分度比较高的列作为复合索引的前导列,这样前导列单列查询的时候也能很好地使用复合索引。”

开发者:“那能不能建三个列的复合索引呢?”

架构师:“能是能,但是不建议这么干。你这个表数据量比较大的话,你可以相像一下这个索引结构会多复杂,后期维护也是很痛苦的。”

架构师:“好了,我们现在一共有三个方案。第一,B列和C列的单列索引;第二,(B, C)复合索引和C列单列索引;第三,(B, C),(B, A),(C, A)三个复合索引。其中,第二个是最优推荐的。”

开发者:“好的,那就按照第二个来建吧。”

架构师:“还不行。我们需要请开发DBA做一次影响分析后,再确定是否可以建。”

开发者:“影响分析?怎么做的呢?”

架构师:“就是对比索引建立前后该表上所有SQL语句的执行计划的变化情况。如果变好了,自然没有问题,如果变差了,就不能创建了。如果必须创建,就需要使用OUTLINE或者SPM固定有可能变差的执行计划后,再行创建。”

开发者:“那不是很麻烦吗?不这么干行不行呢?”

架构师:“是很麻烦,但是为了保证系统的稳定运行,这是必不可少的。宁愿把难度做在设计阶段,也不要把难度留给运维阶段。”

通过上述的一段工作对话,我们大致可以了解到一些复合索引设计和使用的小技巧了吧。其实,上面提到的影响分析,不只是在建复合索引的时候需要做,在建单列索引和收集统计信息等有可能造成执行计划变化的DBA操作都需要做。

2.  使用实例

复合索引的设计很大程度上可以参考单列索引来进行,其比较大的一个特点就是前导列和后置列的选择问题。通过下面一个例子,我们来对比看一下吧。具体步骤和SQL语句如下所示:

步骤1  创建一下相关的表和索引:

SQL> create table alex_t04 (
2  id number,
3  a  number,
4  b  number,
5  c  number,
6  d  number,
7  name varchar2(100)
8  );

SQL> alter table alex_t04 add constraint pk_alex_t04
2  primary key (id) using index;
SQL> create index idx_alex_t04_ab on alex_t04 (a, b);
SQL> create index idx_alex_t04_cd on alex_t04 (c, d);

步骤2  初始化数据,制造a列超低区分度,b、c、d三列较高区分度:

SQL> declare
2  begin
3    for i in 1 .. 100000 loop
4      insert into alex_t04
5      values
6        (i, mod(i, 2), mod(i, 20000), mod(i, 20000),
7        mod(i, 20000), 'alex');
8    end loop;
9    commit;
10  end;
11  /

步骤3  同样收集一下表和索引的统计信息:

SQL> exec dbms_stats.gather_table_stats('alex','alex_t04')
SQL> exec dbms_stats.gather_index_stats('alex','pk_alex_t04')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t04_ab')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t04_cd')

对于查询条件覆盖了所有复合索引列,不论是低区分度的前导列还是高区分度的前导列,同样走了INDEX RANGE SCAN,达到较好的预期效果。示例如下所示:

SQL> select * from alex_t04 where a=1 and b=600;
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     3 |    75 |     6   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T04        |     3 |    75 |     6   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T04_AB |     5 |       |     1   (0)|
------------------------------------------------------------------------------------

SQL> select * from alex_t04 where c=1 and d=600;
------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    25 |     6   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T04        |     1 |    25 |     6   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T04_CD |     5 |       |     1   (0)|
------------------------------------------------------------------------------------

在复合索引的使用中,查询条件覆盖所有复合索引列的情况是最优的。如果在建索引的时候,能够充分考虑到这一点自然是最好的。就像前一节的场景对话,如果能创建(B, C),(B, A),(C, A)三个复合索引,完全覆盖查询场景自然是最优的选择,但是也不得不考虑其运维代价。说白了,就是要用更少的,结构更简单的索引来达成更多的场景需求,实现索引的高效使用。

对于复合索引来说,其高效在哪里呢?索引列全覆盖自然不用说,不论如何都是高效的。问题应该集中在前导列和后置列的使用上。众所周之,复合索引的前导列是可以覆盖其单列查询的,而后置列则未必,除非满足INDEX SKIP SCAN的条件。如下面的例子所示。

对于较低区分度前导列的复合索引idx_alex_t04_ab来说,当发生前导列单列查询是无法使用到索引的,或者说不使用索引效率更高。

SQL> select * from alex_t04 where a=1;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 50000 |  1220K|   115   (5)|
|*  1 |  TABLE ACCESS FULL| ALEX_T04 | 50000 |  1220K|   115   (5)|
-------------------------------------------------------------------

在后置列查询中,我们看到INDEX SKIP SCAN的效率还是挺高的。那是因为我们选择的前导列a有极低的区分度,否则的话,其效率不会太高,或者不使用索引扫描而选择全表扫描。

SQL> select * from alex_t04 where b=600;
------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     5 |   125 |     8   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T04        |     5 |   125 |     8   (0)|
|*  2 |   INDEX SKIP SCAN           | IDX_ALEX_T04_AB |     5 |       |     3   (0)|
------------------------------------------------------------------------------------

可以这么说,一个前导列区分度不高的复合索引,满足不了前导列单列查询的需要,也未必能较好的满足后置列的单列查询需要。这样的复合索引的使用效率就显得不高了。

反观前导列区分度较高的复合索引idx_alex_t04_cd,前导列的单列查询毋庸置疑地满足了高效索引扫描。如下所示:

SQL> select * from alex_t04 where c=1;
------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     5 |   125 |     7   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T04        |     5 |   125 |     7   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T04_CD |     5 |       |     2   (0)|
------------------------------------------------------------------------------------

对于后置列的单列查询来说,就直接说不了,简单干脆,便于把握,避免了“未必”这种模棱两可,难以把握的情况,也减少了后期出现问题和偏差的几率。如下所示:

SQL> select * from alex_t04 where d=600;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     5 |   125 |   115   (5)|
|*  1 |  TABLE ACCESS FULL| ALEX_T04 |     5 |   125 |   115   (5)|
-------------------------------------------------------------------

小结一下复合索引的设计和使用吧:

q  在复合索引的应用中,查询条件覆盖所有的索引列,查询效果最优;

q  尽可能地用更少的,结构更简单的索引来达成更多的场景需求;

q  一般来说,在复合索引创建的时候,前导列都建议选择区分度较高的,故INDEX SKIP SCAN往往就是被优化对象。

Trackback

no comment untill now

Add your comment now

切换到手机版