SQL优化实战：让查询沿着索引跑的技巧

SQL优化实战：让查询沿着索引跑的技巧

引用

CSDN

1.

https://blog.csdn.net/weixin_40345397/article/details/140650885

在进行SQL优化时，我们经常会遇到需要对大量数据集进行排序，然后从排序后的集合中取前部分结果的需求。在这种情况下，如果按照常规思路去写SQL，系统会先读取过滤获得所有集合，然后进行排序，再从排序结果中取出极少量结果。这个过程中，大量数据的扫描读取、过滤、排序会消耗掉大量的系统资源，导致SQL性能存在很大问题。实践中，几分钟乃至几个小时不出结果的情况很常见。

为了优化这种场景的SQL，我们经常会让查询顺序扫描建在排序列上的索引，以避开大量的数据读取和排序。但实践中发现，当索引列不在条件中出现时，Oracle不会产生扫描索引的计划，即使使用hint也不能让查询沿着目的索引扫描。

例如：

create table t1(c1 int,c2 char(10));  
create index idx1_t1 on t1(c1);  
select * from (  
select * from t1 order by c1)  
where rownum<6;  

很明显，这种写法会导致先读取表，再进行排序，然后取前5条记录。其执行计划如下：

如果我们这么写，语义是一样的，但会省去了大量的读取和排序代价：

select /*+ index(t1,idx1_t1)*/* from t1 where rownum<6;  

但是，该SQL并没有按照hint指示，顺序扫描idx1_t1索引。个人猜测，可能是Oracle优化器认为过滤条件内没c1列，走索引比走FTS效率更低，所以，干脆就不考虑走索引这种计划，即使使用hint也要忽略，这点感觉有点不尽人意。

那么，我们想什么办法才能让优化器选择扫描idx1_t1的计划呢？我们只需要在where中加个c1列上的条件就可以了，例如：

select /*+ index(t1,idx1_t1)*/* from t1 where rownum<6 and c1<>-1;  

修改后的执行计划如下：

由此可见，我们优化类似场景时，只需满足两点：

1. 排序列上存在索引；
2. where条件中有该索引列上的条件；

如果能实现按照索引扫描，性能有成千上万倍的提升也是非常可能的，这点在实践中得到了验证。