公众号:pythonislover
说到分页,大家都用过,但是你知道你的分页SQL写的对吗?下面我们看看在写分页的SQL时候,要注意哪些,怎么去优化分页。开头我们要说说分页的类型,单表分页和多表分页。
单表分页
先看看一般的单表分页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是不对的。先看看执行计划
![5a788b8467df29ec4cbfd1450180c9d4.png](https://i-blog.csdnimg.cn/blog_migrate/b8b083e35019be5b05a7da3291965f19.jpeg)
从执行计划看到SQL里面的表走的是全表扫描,而且是先SORT ORDER BY 之后再FILTER的,换句人话就是先把表排个序,然后取出20条。想想这样的方式傻吗? 假如表2000W甚至几个亿怎么办?所以这样的方式不能取,换句话说这里面的表应该是走索引。
现在我们试试加个索引看看
SQL> create index ix1 on test(object_id,0); #注意建索引要加个常数,具体为什么是因为索引不能储存NULL值,或者你把object_id设置成IS NOT NULL.
Index created.
![90f19138ecd08ade10f7bcd021532135.png](https://i-blog.csdnimg.cn/blog_migrate/9edf1a7c3ae7963d79dc3f75ef2ca727.jpeg)
上面执行计划,表虽然是走了索引,但是走的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;
看下执行计划
![2a5cf10fa69458c71429ad525c492ecf.png](https://i-blog.csdnimg.cn/blog_migrate/699582d445aa7e94b1a40d76307a9cb0.jpeg)
执行计划显示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;
![956460f9797427f15154cb64dd5a12f9.png](https://i-blog.csdnimg.cn/blog_migrate/3999c1a20ded1b6360c7d0e1d0f5e471.jpeg)
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;
看下执行计划
![59e237f97815f8641c4f40d0bfa505c5.png](https://i-blog.csdnimg.cn/blog_migrate/c359920d6c56d395fccc6691aafb0084.jpeg)
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;
![c2d9d6978cf6f1a8dd6e152729cc5d27.png](https://i-blog.csdnimg.cn/blog_migrate/386975450afbac9b492c7abc51863d5c.jpeg)
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;
![03472cda863f5cbc16d71b42e8dd27ae.png](https://i-blog.csdnimg.cn/blog_migrate/d17d4c7b7048ef5f808649b5a191575d.jpeg)
下面总结下:
1.单表分页,要让表走索引
2.要出现COUNT STOPKEY 而不能出现 SORT ORDER BY,就是让表扫到10就停止扫描
3.注意上面各种情况下的索引创建方式和列的顺序
下期我们说说多表分页的优化。
今天就说到这,个人见解,望指正