3分钟搞定单表分页语句SQL的优化

关注我的微信公众号：pythonislover,领取python,大数据，SQL优化相关视频资料！~

Python大数据与SQL优化笔 QQ群：771686295

 

说到分页，大家都用过，但是你知道你的分页SQL写的对吗？下面我们看看在写分页的SQL时候，要注意哪些，怎么去优化分页。开头我们要说说分页的类型，单表分页和多表分页。

 

1. 单表分页

 

先看看一般的单表分页SQL怎么写

SQL> create table test as select * from dba_objects;
Table created.
SQL> select * from   (select t.*,rownum rn   from (select * from test order by object_id) t )   where rn>=1 and rn<=20;

 

不知道大家有没有这样写过，应该有的，但是我要说这样的分页SQL是不对的。先看看执行计划

从执行计划看到SQL里面的表走的是全表扫描，而且是先SORT ORDER BY 之后再FILTER的，换句人话就是先把表排个序，然后取出20条。想想这样的方式傻吗？ 假如表2000W甚至几个亿怎么办？所以这样的方式不能取，换句话说这里面的表应该是走索引。

 

现在我们试试加个索引看看

 

SQL> create index ix1 on test(object_id,0); #注意建索引要加个常数，具体为什么是因为索引不能储存NULL值，或者你把object_id设置成IS NOT NULL.

 

Index created.

 

 

上面执行计划，表虽然是走了索引，但是走的INDEX FULL SCAN还是扫描了所有的叶子块。

 

所有上面的写法怎么弄都不行，下面看看正确的写法。

select  * from    (select * from (  select t.*,rownum rn   from (select * from test order by object_id) t)  where rownum <=10)   where rn>=1;

 

看下执行计划

 

 

执行计划显示A-Rows只有10条了，说明表只扫描了10条数据就停止了，这是为什么呢？ 为什么扫描10条就能找到我们需要的数据呢？ 那是因为索引本身是有序的，所以可以实现只扫描10条就能得到我们要的数据，这里显示的COUNT STOPKEY，也说明了当扫描到指定行数之后就停止扫描了

 

下面我们来总结下单表分页优化的重点

1. 表要走索引

2. 执行计划中要出现COUNT STOPKEY，不能出现SORT ORDER BY

 

下面我们看看索引到底怎么建，我们分情况讨论：

 

1. SQL中有等值条件和ORDER BY

如： select * from test where owner ='SYS' order by object_id;

 

SQL>  create index ix2 on test(owner,object_id,0);
Index created.
SQL> select  /*+ gather_plan_statistics*/ * from   (select * from (  select t.*,rownum rn  from (select * from test where owner ='SYS' order by object_id) t)  where rownum <=10)  where rn>=1;

 

 

 

2. SQL中有等值条件,非等值条件和ORDER BY

如：

select * from test where owner ='SYS' and object_id >100 order by object_name

 

 create index ix3 on test(owner,object_name,object_id);  #注意索引的列顺序，先等值，order by条件，在非等值条件  select  /*+ gather_plan_statistics*/ * from    (select * from (  select t.*,rownum rn   from (select/*+index(test ix3)*/ * from test where owner ='SYS' and object_id >100 order by object_name) t)  where rownum <=10)   where rn>=1;

看下执行计划

 

 

3. SQL中有多个等值条件,非等值条件和ORDER BY

结论如图2

 

4.SQL中有等值条件,多个非等值条件和ORDER BY

select * from test where owner ='SYS' and object_id >100 DATA_OBJECT_ID <1000 order by object_name

 create index ix4 on test(owner,object_name,object_id,DATA_OBJECT_ID);  select  /*+ gather_plan_statistics*/ * from    (select * from (  select t.*,rownum rn   from (select/*+index(test ix4)*/ * from test where owner ='SYS' and object_id >100 and DATA_OBJECT_ID <1000 order by object_name) t)  where rownum <=10)   where rn>=1;

 

 

 

5. order by 里面按倒序排序

如：

create index ix5 on test(owner,object_name,object_id desc,0);#注意索引d额列顺序， 按order by列顺序来定
select  /*+ gather_plan_statistics*/ * from    (select * from (  select t.*,rownum rn   from (select/*+index(test ix5)*/ * from test where owner ='SYS' and object_id >100  order by object_name,object_id desc) t)  where rownum <=10)   where rn>=1;

 

 

 

下面总结下：

1. 单表分页，要让表走索引

2. 要出现COUNT STOPKEY 而不能出现 SORT ORDER BY，就是让表扫到10就停止扫描

3. 注意上面各种情况下的索引创建方式和列的顺序

 

下期我们说说多表分页的优化。

 

今天就说到这，个人见解，望指正