79-不要看到有order by xxx desc就创建desc降序索引-文末有赠书福利

经常看到有客户的系统会创建一些降序索引,如:

create index idx_xxx on tab_xxx(col_name desc);

还有多字段:

create index idx_xxx on tab_xxx(col_name1 desc,col_name2 desc);

一般情况下, 这种各字段都是desc索引是不需要的, 正常情况只需要普通索引即可(去掉desc).先看一个我遇到的案例(下面是为了方便分析进行的模拟演示):

--创建一个object_id is null 有11条记录的t1表:

create table t1 as select * from dba_objects where object_id is not null;

alter table t1 modify object_name not null;

update t1 set object_id = null where rownum<=11;

commit;

--收集一下统计信息

exec dbms_stats.gather_table_stats(user,'t1');

--当前发现的TOP SQL如下:

这是一个很简单的分页查询SQL,没有order by,执行计划使用的是全表扫描,生产系统平均buffer gets接近9万(上面用来模拟的t1表相对较小,只有741个block,索引180个左右的block).

检查t1表上的索引情况,存在一个两字段做desc的联合索引(应该是为别的业务SQL所建):

create index idx_t1_desc on t1(object_id desc,object_name desc);

加hint强制使用idx_t1_desc 索引,看一下什么情况:

使用的是index full scan , 也要把索引遍历一遍(如果object_id is null的记录数超过20个,null值所在的索引块会先扫描, 因为有rownum<=20,不会遍历整个索引,buffer gets数会大大减少).

为了优化这个SQL,我又创建了下面索引(还是两字段联合, 去掉了desc):

create index idx_t1_normal on t1(object_id,object_name);

上面SQL,不用加hint,自动使用新建索引, 执行计划使用了index range scan,效率大幅提高,buffer gets只有5:

可能有人会说, 上面的desc索引,可能是为下面的SQL所建:

那我告诉你,根本没必要. 我建的普通索引也可以被这个SQL使用. 因为oracle的优化器对这样一个全是desc的order by, 可以使用普通索引的降序扫描(descending)的方式进行优化,完全不需要创建desc降序索引:

如果上面的order by改成order by a.object_id desc,a.object_name(一个是降序,一个是默认的升序),在这种情况下, 如果为了避免排序,才需要创建(object_id desc,object_name)这样的联合索引.

而且降序索引还有一些bug,下面是MOS里面检索descending index bug的结果(截图只是一部分):

本人公众号文章<系统迁移导致SQL性能下降，神马原因？>就是一个关于desc 索引的bug.

到这里, 引出我的结论:

类似上面的降序索引,可以被普通索引取代. 普通索引可能适用多个SQL,降序索引的适用范围比较窄,而且还有一些bug. 为了避免多余的索引和bug, 不建议创建全是desc 降序索引.

概括起来就是:

如果order by的一个或多个字段都是desc降序,那么是不需要创建desc 降序索引的;

如果order by的多个字段有升有降,这种情况才需要创建desc降序索引.

有时如果你需要得到索引降序扫描,而优化器没有选择,这时可以使用index_desc的hint来引导优化器,比如下面这种情况,如:

这个SQL如果不加hint, 默认使用的是全表扫描的执行计划. 使用的索引IDX_T1_OWNER_OBJECT_ID 是 owner+object_id 两字段联合索引.

下面再来分析几个书上介绍的优化案例:

我在一本书里看到有几个类似的优化案例,都是建议创建desc降序索引, 下面我们一起分析一下:

案例1:

这是一个根据IP地址区间查所属地区信息的典型SQL, 这个SQL我在公众号文章<区间检索SQL优化--续>中曾有过论述,主要是写法,然后再配合合适的索引.

上面这个SQL,有几个问题:

首先,ip地址直接用字符串比较是不合适的,应该按照一定的规则,转换成number类型来比较;

其次,这样的写法如果要建索引,建议创建ip_addr_end+ip_addr_start, 即结束地址在前的普通索引,而不是建开始地址的降序索引;

再次,这种写法不存在升序降序导致结果错误的问题;

最后, 这种写法还有个比较大的缺点,就是在找不到匹配的记录时,性能会比较差.

建议大家在遇到类似区间检索的业务需求,参考我公众号文章的做法,实践证明是最佳的.

案例2:

原作者的优化建议是:

虽然也能起到优化作用,但是就像我一开始的案例分析的那样, 这个SQL创建普通的索引即可.desc 索引实际上是创建了一个函数索引,这种索引,其他SQL可能用不上.

案例3:

原作者对这个SQL的优化建议是:

原作者把原来的top n写法改成4层的分页查询, 我个人认为有点多余:

如果原SQL是一个分页查询多次翻页的SQL,那么这个改写完全没有问题,在页数较大时,比普通分页的3层标准分页写法会有较大的性能提升;

但是原SQL只是取top n,不存在翻页, 改写后的SQL不会因为改写发生任何性能提升,这里有点生搬硬套的感觉.

再说说组合索引的建议,最佳的索引组合我认为是这样的:

如果doc_level<=101 的选择性比较好, 那么可以创建class_3 + doc_level 两字段组合索引 ;

如果doc_level<=101 的选择性不好, 那么可以创建 class_3 + doc_writedate + doc_level 3字段组合索引(虽然sql中有order by doc_writedate desc,但是也不需要加desc);

原作者的建议是 class_3 + doc_level + doc_writedate desc 3字段组合索引, 这个组合索引,最后的doc_writedate desc是没有任何意义的, 不能起到过滤作用, 也不能去掉排序(执行计划中的sort order by还在).

如果doc_level不是<=101,而是=101 ,那么可以创建class_3 + doc_level + doc_writedate,避免执行计划中的sort order by,也是不需要加desc的.

总结:

除非必要,不要创建desc降序索引. 索引,关系型数据库的一个非常重要的理论,还需要大家深入理解.