oracle 建分区索引_你真的会创建数据库复合索引吗?附具体执行计划

前言举例

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

举个例子

c6e2378eb6228ccd833c2faad885d597.png

数据库表

这张表有什么特点?
第一、经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; 
18843b7d46f933d68ca832f80fec06dc.png

执行计划-1

2、

select * from sunyang_test where c=5000; 

执行计划:

26211f4de953ff1601e01cb3f66210fa.png

执行计划-2

3、

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

执行计划:

03eceedb52d91b76d15e8d318b6aaabc.png

执行计划-3

4、

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

执行计划:

9c8610876d8d6f364912b3a085aa5586.png

执行计划-4

5、

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

执行计划:

9051df47ba0900261e639769e1d8a2ed.png

执行计划-5

6、

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

执行计划:

39f7fa53f059fa2eb34a9ad0d2a4d8c4.png

执行计划-6

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

结束语

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

谢谢大家的关注!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值