一次SQL分页的优化
今天优化了一个分页的SQL，以前虽然做了上千个SQL的优化，不过都是一些OLAP的，虽然也有OLTP的不过从来没做过分页优化，所以这里记录一下。
SQL和执行计划如下：
SQL> SELECT A.ROWNO,EMS_EVENT_VIEW.* FROM EMS_EVENT_VIEW,
2 (SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY first_occurrence_time DESC) AS ROWNO,EVENT_ID
3 FROM EMS_EVENT_VIEW
WHERE (first_occurrence_time>to_date('2012-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
4 5 and first_occurrence_time<to_date('2012-02-29 09:42:35','yyyy-mm-dd hh24:mi:ss'))
or (last_occurrence_time>to_date('2012-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')
and last_occurrence_time<to_date('2012-02-29 09:42:35','yyyy-mm-dd hh24:mi:ss')))
6 7 8 WHERE ROWNO>=0 AND ROWNO<=20) A
9 WHERE EMS_EVENT_VIEW.EVENT_ID=A.EVENT_ID;
Plan hash value: 2052413575
-------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:53.37 | 757K| | | |
|* 1 | HASH JOIN | | 1 | 81G| 20 |00:00:53.37 | 757K| 1179K| 1179K| 6598K (0)|
|* 2 | VIEW | | 1 | 2104K| 20 |00:00:30.83 | 101K| | | |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 2104K| 21 |00:00:30.83 | 101K| 4096 | 4096 | 4096 (0)|
|* 4 | FILTER | | 1 | | 1255K|00:00:30.10 | 101K| | | |
| 5 | VIEW | EMS_EVENT_VIEW | 1 | 2104K| 1255K|00:00:28.85 | 101K| | | |
| 6 | UNION-ALL | | 1 | | 1255K|00:00:28.85 | 101K| | | |
| 7 | CONCATENATION | | 1 | | 0 |00:00:00.01 | 335 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| EMS_EVENT | 1 | 1 | 0 |00:00:00.01 | 63 | | | |
|* 9 | INDEX RANGE SCAN | LAST_OCCURRENCE_TIME_INDEX | 1 | 1 | 0 |00:00:00.01 | 63 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| EMS_EVENT | 1 | 1 | 0 |00:00:00.01 | 272 | | | |
|* 11 | INDEX RANGE SCAN | FIRST_OCCURRENCE_INDEX | 1 | 1 | 0 |00:00:00.01 | 272 | | | |
|* 12 | VIEW | index_join_006 | 1 | 2104K| 1255K|00:00:28.84 | 100K| | | |
|* 13 | HASH JOIN | | 1 | | 3863K|00:00:26.50 | 100K| 195M| 9M| 248M (0)|
|* 14 | HASH JOIN | | 1 | | 3863K|00:00:13.87 | 63020 | 160M| 10M| 214M (0)|
| 15 | PARTITION RANGE ALL | | 1 | 2104K| 3863K|00:00:00.01 | 31419 | | | |
| 16 | INDEX FAST FULL SCAN | IDX_FIRSTTIME_201202 | 338 | 2104K| 3863K|00:00:00.07 | 31419 | | | |
| 17 | PARTITION RANGE ALL | | 1 | 2104K| 3863K|00:00:00.01 | 31601 | | | |
| 18 | INDEX FAST FULL SCAN | IDX_LASTOCCURRENCE_201202 | 338 | 2104K| 3863K|00:00:00.06 | 31601 | | | |
| 19 | INDEX FAST FULL SCAN | PK_EMS_EVENT_HISTORY_201202 | 1 | 2104K| 3863K|00:00:00.01 | 37894 | | | |
| 20 | VIEW | EMS_EVENT_VIEW | 1 | 3864K| 3867K|00:00:19.34 | 656K| | | |
| 21 | UNION-ALL | | 1 | | 3867K|00:00:15.47 | 656K| | | |
| 22 | TABLE ACCESS FULL | EMS_EVENT | 1 | 3867 | 3950 |00:00:00.02 | 2046 | | | |
| 23 | PARTITION RANGE ALL | | 1 | 3860K| 3863K|00:00:07.73 | 654K| | | |
| 24 | TABLE ACCESS FULL | EMS_EVENT_HISTORY | 338 | 3860K| 3863K|00:00:09.51 | 654K| | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMS_EVENT_VIEW"."EVENT_ID"="from_subquery_003"."EVENT_ID")
2 - filter(("ROWNO">=:SYS_B_8 AND "ROWNO"<=:SYS_B_9))
3 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("FIRST_OCCURRENCE_TIME") DESC )<=:SYS_B_9)
4 - filter(:SYS_B_8<=:SYS_B_9)
9 - access("A"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5) AND "A"."LAST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_6,:SYS_B_7))
10 - filter((LNNVL("A"."LAST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_6,:SYS_B_7)) OR LNNVL("A"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5))))
11 - access("A"."FIRST_OCCURRENCE_TIME">TO_DATE(:SYS_B_0,:SYS_B_1) AND "A"."FIRST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_2,:SYS_B_3))
12 - filter((("B"."FIRST_OCCURRENCE_TIME">TO_DATE(:SYS_B_0,:SYS_B_1) AND "B"."FIRST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_2,:SYS_B_3)) OR
("B"."LAST_OCCURRENCE_TIME">TO_DATE(:SYS_B_4,:SYS_B_5) AND "B"."LAST_OCCURRENCE_TIME"<TO_DATE(:SYS_B_6,:SYS_B_7))))
13 - access(ROWID=ROWID)
14 - access(ROWID=ROWID)
如果你看不清楚SQL，我在这里再贴一下：
SELECT A.ROWNO, EMS_EVENT_VIEW.*
FROM EMS_EVENT_VIEW,
(SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY first_occurrence_time DESC) AS ROWNO,
EVENT_ID
FROM EMS_EVENT_VIEW
WHERE (first_occurrence_time >
to_date('2012-02-22 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and
first_occurrence_time <
to_date('2012-02-29 09:42:35',
'yyyy-mm-dd hh24:mi:ss'))
))
WHERE ROWNO >= 0
AND ROWNO <= 20) A
WHERE EMS_EVENT_VIEW.EVENT_ID = A.EVENT_ID;
这个SQL其实就是一个分页SQL，利用 row_number over 做分页，EMS_EVENT_VIEW是一个视图。这个SQL确实写得很坑爹，它要扫描EMS_EVENT_VIEW两次，其实我们可以改写它，让它扫描一次，而不是自己和自己利用event_id 做自连接。
EMS_EVENT_VIEW的定义就不贴出来了，涉及保密。它的大概意思就是 select * from a union all select * from b; 无where 过滤条件。
因为这个SQL是朋友给我的，我无法连接到他的DB，所以我只有自己做测试了，测试代码如下：
create table a as select * from dba_objects;
create table b as select * from dba_objects;
create view test_view as select * from a
union all select * from b;
create index idx_a on a(created ,last_ddl_time);
create index idx_b on b(created ,last_ddl_time);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'B',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 4,
cascade => TRUE);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'B',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 4,
cascade => TRUE);
END;
/
要优化的SQL可以改写成如下代码，只访问一次视图：
select * from
(
select t.*,rownum rn from
(select /*+ INDEX(TEST_VIEW.A idx_a) INDEX(TEST_VIEW.b idx_b) */ *
from test_view
where created > to_date('2010-01-01', 'yyyy-mm-dd') or
LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
order by created desc
) t where rownum<=20
) where rn>=0;
现在来看它的执行计划
SQL> select * from
2 (
3 select t.*,rownum rn from
4 (select /*+ INDEX(TEST_VIEW.A idx_a) INDEX(TEST_VIEW.b idx_b) */ *
5 from test_view
6 where created > to_date('2010-01-01', 'yyyy-mm-dd') or
7 LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
8 order by created desc
9 ) t where rownum<=20
10 ) where rn>=0;
已选择20行。
已用时间: 00: 00: 00.10
执行计划
----------------------------------------------------------
Plan hash value: 1808710389
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 3800 | | 1898 (2)| 00:00:23 |
|* 1 | VIEW | | 20 | 3800 | | 1898 (2)| 00:00:23 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 70304 | 11M| | 1898 (2)| 00:00:23 |
|* 4 | SORT ORDER BY STOPKEY | | 70304 | 6659K| 17M| 1898 (2)| 00:00:23 |
| 5 | VIEW | TEST_VIEW | 70304 | 6659K| | 329 (5)| 00:00:04 |
| 6 | UNION-ALL PARTITION | | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| A | 1650 | 156K| | 238 (3)| 00:00:03 |
|* 8 | INDEX FULL SCAN | IDX_A | 1650 | | | 199 (4)| 00:00:03 |
| 9 | TABLE ACCESS BY INDEX ROWID| B | 1650 | 156K| | 238 (3)| 00:00:03 |
|* 10 | INDEX FULL SCAN | IDX_B | 1650 | | | 199 (4)| 00:00:03 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
8 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME"<TO_DATE('2007-0101','yyyy-mm-dd'))
10 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME"<TO_DATE('2007-0101','yyyy-mm-dd'))
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
566 consistent gets
9 physical reads
0 redo size
2621 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
逻辑读566，那么这样改写是不是最优化的呢？显然不是，因为索引 IDX_A,IDX_B 都是走的 index full scan,会扫描整个索引block，原始的SQL这个索引里面有3863K 条数据，性能肯定是很低的。 所以进一步的 改写SQL 如下：
select * from
(
select t.*,rownum rn from
(
select * from
(select * from
(
select /*+ index_desc(a) */ *
from a
where created > to_date('2010-01-01', 'yyyy-mm-dd') or
LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
order by created desc
) where rownum<=20
union all
select * from
(
select /*+ index_desc(b) */ *
from b
where created > to_date('2010-01-01', 'yyyy-mm-dd') or
LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
order by created desc
) where rownum<=20
) order by created desc
) t where rownum<=20
) where rn>=0
执行计划和逻辑读如下：
SQL> select * from
2 (
3 select t.*,rownum rn from
4 (
5 select * from
6 (select * from
7 (
8 select /*+ index_desc(a) */ *
9 from a
10 where created > to_date('2010-01-01', 'yyyy-mm-dd') or
11 LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
12 order by created desc
13 ) where rownum<=20
14 union all
15 select * from
16 (
17 select /*+ index_desc(b) */ *
18 from b
19 where created > to_date('2010-01-01', 'yyyy-mm-dd') or
20 LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
21 order by created desc
22 ) where rownum<=20
23 ) order by created desc
24 ) t where rownum<=20
25 ) where rn>=0;
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 3460309830
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 3800 | 244 (4)| 00:00:03 |
|* 1 | VIEW | | 20 | 3800 | 244 (4)| 00:00:03 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 40 | 7080 | 244 (4)| 00:00:03 |
|* 4 | SORT ORDER BY STOPKEY | | 40 | 7080 | 244 (4)| 00:00:03 |
| 5 | VIEW | | 40 | 7080 | 243 (3)| 00:00:03 |
| 6 | UNION-ALL | | | | | |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | VIEW | | 1650 | 285K| 238 (3)| 00:00:03 |
| 9 | TABLE ACCESS BY INDEX ROWID| A | 1650 | 156K| 238 (3)| 00:00:03 |
|* 10 | INDEX FULL SCAN DESCENDING| IDX_A | 1650 | | 199 (4)| 00:00:03 |
|* 11 | COUNT STOPKEY | | | | | |
| 12 | VIEW | | 20 | 3540 | 5 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| B | 20 | 1940 | 5 (0)| 00:00:01 |
|* 14 | INDEX FULL SCAN DESCENDING| IDX_B | 1650 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
7 - filter(ROWNUM<=20)
10 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME"<TO_DATE('2007-0101','yyyy-mm-dd'))
11 - filter(ROWNUM<=20)
14 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME"<TO_DATE('2007-0101','yyyy-mm-dd'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2457 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
逻辑读整整下降了60倍。
现在根据这个案例来谈谈SQL分页的优化思路，SQL分页通常要进行排序，比如select xxxx from t where 条件 order by ......
优化分页SQL可以重点关注 order by 这个条件，写SQL的时候要让ORACLE 对 order by 列 上的索引进行有序的扫描，然后根据stopkey 停止，也就是不要把索引的block全都给扫描了，应该扫描一部分block就停止。
发表评论
相关推荐
11.3.3 一次 Merge Sort Join 的优化全过程 324 11.3.4 一次统计信息收集不准确引发的NL性能瓶颈 329 11.4 本章习题、总结与延伸 332 第12章 动手，经典等价改写让SQL飞 333 12.1 设法减少访问路径 333 12.1.1...
11.3.3 一次 Merge Sort Join 的优化全过程 324 11.3.4 一次统计信息收集不准确引发的NL性能瓶颈 329 11.4 本章习题、总结与延伸 332 第12章 动手，经典等价改写让SQL飞 333 12.1 设法减少访问路径 333 12.1.1...
SQL数据库分页：用存储过程+游标方式分页，具体实现原理不是很清楚，设想如果用一次查询就得到需要的结果，或者是id集，需要后续页时只要按照结果中的IDs读出相关记录。这样只要很小的空间保留本次查询的所有IDs....
实现原理为mybatis的拦截器，但是比网上目前流行的修行sql方式优化，只是第一次调用查询时需要处理，以后不需要再额外处理。 生成的sql为自动化的最优(基于数据绑定方式的sql)。 缺点： 扩展需要对mybatis源码...
当我们展示一个列表中的内容时，难免会遇到分页问题，因为列表中的内容数量可能很多，但是用户能一次看到的界面大小是有限的，不可能一个界面展示所有的内容，从后端一次性取太多的数据也会给后端造成额外的压力。...
数据库SQL优化是老生常谈的问题，在面对百万级数据量的分页查询，又有什么好的优化建议呢？下面将列举了一些常用的方法，供大家参考学习！ 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: ...
作为一本讲述T-SQL高级查询的专业图书，《Microsoft SQL Server 2008技术内幕：T-SQL查询》旨在结合实践中的各种常见问题，教读者如何用SQL作为语言工具来思考问题，揭示基于集合查询的强大威力。《Microsoft SQL ...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容，以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。对于数据库分页查询，也有很多种方法和优化的点。下面简单说一下我知道的一些...
数据库使用的MySQL，有一个日志表，需要进行分页查询，于是很容易就想到了limit [offset偏移量] [count数量]这个查询方式,当我们偏移量比较小时,似乎是没什么问题 SELECT * FROM t_log WHERE type = 1 LIMIT 5, 50 ...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容，以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
├─第一篇 DBA工作手记 │ 01.Eygle的DBA工作手记 │ 02.Yangtingkun的DBA工作手记 │ 03.老熊的DBA手记 │ 04.BanPing的DBA工作手记 │ ├─第二篇 诊断案例篇 │ 01.ASM案例分析与诊断 ...一次排序的调整与优化
本文主要通过一下几个方面介绍：使用SQL DMV查找慢速查询、通过APM解决方案查询报告、...SQLServer的一个重要功能是内置于其中的所有动态管理视图（DMV）。它们有数十种，可以提供有关各种主题的丰富信息。有几个DMV提
原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃. 方法2: 建立主键或唯一索引, 利用索引(假设每页10条)...
—原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)。Limit限制的是从结果集的M位置处取出N条输出,其余抛弃。 方法2: 建立主键或唯一索引, 利用索引(假设每页10...
统计所有表内信息,得出行数,以行数进行数据分页(目的:由于数据的日渐增多,而所有数据一次性显示在表格内,会影响系统的正常使用,分页则是将众多数据一点点的显示出来,达到资源优化的目的.) (5).开始根据分页内容给...
您只需通过计数器配置一次其行为，然后使用render方法通过所有导航链接创建导航。 轻松地使用咖啡并与您的数据库进行交互。 分页器是一种非常紧凑且易于使用的组件。 您只需由构建器配置一次您的行为，然后使用...
procedure sp_Page(p_PageSize int, --每页记录数 p_PageNo int, --当前页码,从 1 开始 ... --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn OPEN p_OutCursor FOR v_sql; end sp_Page;
这种好处当您的栏目下有上万篇文章时越是明显，因为每次生成时不需要将所有页面重新生成一次，只需要根据预设定的生成最新更新的记录即可！ 3、 系统由文章、图片、下载、分类信息、商城、求职招聘、影视、动漫...
20、终极列表内置24种分页效果，满足不同网站风格 21、集成文章系统、下载系统.使用导入模型功能，可增加多种系统功能 22、数据字典内置多种常用类别：*地区、个人爱好、评分等级等 23、站内链接功能 24、特殊...