SQL优化实战：让查询沿着索引扫描以提升性能

SQL优化实战：让查询沿着索引扫描以提升性能

引用

CSDN

1.

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

在处理大量数据集排序并取前N条记录的场景中，SQL优化是一个常见的挑战。本文将通过具体的SQL语句和执行计划对比，深入探讨如何让查询沿着索引扫描，以显著提升性能。

问题背景

在数据库查询中，经常需要对大量数据集进行排序，然后从排序后的集合中取前部分结果。按照常规思路，系统会先读取所有符合条件的数据，然后进行排序，最后取出极少量结果。这个过程中，大量数据的扫描读取、过滤、排序会消耗掉大量的系统资源，导致SQL性能问题。实践中，几分钟乃至几个小时不出结果的情况很常见。

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

问题重现

我们创建一个简单的表t1并为其添加索引：

create table t1(c1 int,c2 char(10));
create index idx1_t1 on t1(c1);

然后尝试以下SQL查询：

select * from (
select * from t1 order by c1
) where rownum<6;

这个查询的执行计划如下：

从计划中可以看出，系统会先读取表，再进行排序，然后取前5条记录。

尝试优化

如果我们尝试使用hint来强制让查询沿着索引扫描：

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

但是，执行计划显示并没有按照hint的指示执行：

这说明Oracle优化器认为在这种情况下走索引比全表扫描效率更低，因此即使使用hint也会被忽略。

解决方案

为了解决这个问题，我们可以在where条件中添加一个对索引列的条件，即使这个条件看起来是多余的：

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

修改后的执行计划如下：

从计划中可以看出，这次查询确实按照索引扫描的方式执行了。

总结

通过这个案例，我们可以总结出优化类似场景的两个关键点：

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

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