top n 查询之优化

提问：

应该不少人都遇到过这样的问题吧, 在数据库中选取 位于min行和max行 之间的所有数据,
通常DQL 书写如下.

SELECT * FROM (
SELECT a,b,c,...,rownum rowcount FROM
where ... and rownum < :max )
WHERE rowcount  > :min;

当数据达到数百万或者上千万后, 这个嵌套查询 的内部select的速度明显会随着 max的增大而变慢,
从而导致 取10-30 行  和 取  1000000 - 1000020 行的速度有差异,
做软件客户端的时候,让 客户觉得分页查询越往后越慢,
请问 这个尴尬的问题, 大虾们 有什么好的解决方案.

回答：

一般来说‘选取位于min行和max行之间的所有数据’都会是在某几个字段排序后再选取的，那样调起来有一定的难度。但从你的语句看来，并没有order by的要求，所以简单一些。

由于你使用rownum，而rownum的值是在查询时生成的，所以在取1000000 - 1000020 行时必然会造成从1 - 1000020的index scan或table scan,这是查询减慢的主因。

如果将该语句的核心大表加一unique字段，而该字段值用sequence（increase by 1)来生成，并在其上建索引。那么语句可以改为

SELECT a,b,c,...,rownum rowcount FROM
where ... and 新字段 < :max and 新字段  > :min)


如此即使在查询1000000 - 1000020 行时，也不会full scan, 最好可以进行index unique scan，最差也只是index range scan. 结果会好很多．