oracle 高效分页查询SQL

     因为一个功能需要读取一个大表的所有数据做业务处理，那这样肯定不能一次性查出所有数据，需要程序分页查询处理，模拟测试一个200万数据量的表发现耗时很久，并不是业务处理耗时，而且分页查询耗时了。oracle的分页查询可能大家都知道利用rownum,而且大部分公司这种分页都是底层封装好的了，所有平时大家使用的时候也没注意（这次之后特意留意了一下，我们公司就是用了错误的）

这两条查询语句看着区别不大，但是性能却差很多。经过测试第一种性能最好，而且随着数量的增大，几乎不受影响。第二种随着数据量的增大，查询速度也越来越慢。表200W条数据的情况下，第一种查询耗时基本是03s,第二种基本在13s以上。一个查询足足差了一秒。别小看这1秒。200W条数据每次查询1000条，查询完也差了2000s=33分钟

分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 20这句上。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。

对于正确有order by语句，第二层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了

对于错误有order by 语句，由于查询条件where browno >= 11 and browno <= 20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道browno代表什么）。因此对于这个语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

limit 第一个参数是查询的 开始位置，第二个是查询的行数，跟数值大小没关系，如果你的查询慢，因该检查 表是否有索引，而且 like 查询 在大数据中很影响性能，一般like语句会造成全表扫描

1 对order by使用复合索引

order by和limit一起使用，避免引起全表扫描和数据排序是非常重要的，因此借助合适的索引提高查询效率。

使用联合索引

联合索引又叫复合索引，是由表中的几个列联合组成的索引。联合索引生效需满足最左前缀原则，即如果联合索引列为a,b,c三列，a,b,c 、a,b 、a生效，b,c、a,c、b、c等不生效（此处的顺序不是where条件后面的先后顺序，而是where条件中是否存在这些列，如果where中只存在a,c列，则不生效）。

索引生效，与where条件的顺序无关：

索引失效，与where条件的列是否存在有关：

带IN条件的联合索引失效

in的参数个数为1个，联合索引生效，大于1个，索引失效。所以使用了强制索引使联合索引生效。

原因分析：

第一、取决于B树的数据结构，单参数的IN只会得到一颗基于model子树，该子树的code本身是有序的，所以索引生效，查询效率高；多参数的IN会得到多颗基于model的子树，每颗子树的code字段是有序的，但是总体上可能不是有序的，所以索引失效，查询效率低。

第二、使用强制索引后，理论上无法保证order by的顺序，但是如果数据本身的特性，比如时间递增的这类数据，总体上还是有序的，笔者试过多中途径想要迫使强制索引得到错误的结果，结果都对了。强制索引需进一步研究。

2 大数据量limit慎用

limit常用于分页中，有两种用法，三种写法：

偏移量offset较大的优化

limit偏移量较小时性能优秀，分页越到后面，偏移量递增，limit的性能会逐渐下降。

此时，通过子查询优化limit，效果如下：

以上数据来自一张超过2000万的MySQL单表，仅供参考，能够说明子查询明显能够提升效率，笔者开始尝试把子查询的order by去掉，发现查询效率又提升2倍，但是对比发现数据不正确，explain后发现查询优化器给出的子查询索引并不是id（此表建有多个索引，id是主键，区分度最高），这一点比较困惑。

ps：在sql语句中，limt关键字是最后才用到的。以下条件的出现顺序一般是：where->group by->having-order by->limit

mysql中查询第几行到第几行的记录

1、查询前n行

       查询第一行

2、查询第n行到第m行

        查询第4行到 第6行

3、查询后n行

查询最后一行

4、查询一条记录的下一条记录

查询一条记录的上一条记录

以上就是关于oracle 高效分页查询SQL全部的内容，包括:oracle 高效分页查询SQL、优化sql mysql 中分页 LIMIT offset,length 当offset过大时如何优化。代码如下、MySQL优化：order by和limit等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！