写有效的web分页sql语句

 

说起statspack，想到年前一个跟web分页有关的故障，某天服务器负载狂增加，load由平时的小于10飚升到30以上，数据库报警不断。经过检查我发现大部分进程都在执行同样的sql，statspack报表显示这些语句的逻辑读、物理读都排在最前面，奇怪，之前报表未见到过这些sql，大过年的谁在胡搞？

Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- ------------
 217,146,067   16,789    12,933.8   25.9  2209.51   3174.15  539991006
Module: java@promotion2.cm2 (TNS V1-V3)
  select count(*) from test_dream t

217,126,051   16,785   12,935.7  25.9 ########  23573.04 4126436409
Module: java@promotion2.cm2 (TNS V1-V3)
  select * from    (select r.*,rownum linenum from     (select t.uID,   t.NAME,   t.XX_NAME,    t.XX_URL ,   t.STATUS,   t.GMT_CREATE,   t.GMT_MODIFIED, t.FO_MODIFIED   from test_dream t   order by t.GMT_CREATE desc)r     WHERE rownum <= :1)   WHERE linenum >= :

很明显，这两个语句是用来web页面分页展示的。第一个sql统计分页的总页数，第二个sql提取需要展示的具体数据。搞清楚开发是要实现web分页功能，以order by time desc排序来显示，这是很常见的需求，我们来考虑怎么进行优化？

从上面的逻辑读来看，这个表数据量肯定不小:

SQL> select count(*)from test_dream t;

  COUNT(*)
----------
   1774011

1 row selected.

如果以这个值做分页且假设每页显示100条数据的话，至少需要分17万页，对于web页面展示来说，人们关注的只是前面几页，分这么多页没有什么意义。
第一条sql，让开发更改只显示前20000行数据：

select count(*)from test_dream t where rownum<20000;

第二条sql,web页面上展示最新生成的数据，查看其执行计划：

SQL> explain plan for
  2  select *
  3    from (select r.*, rownum linenum
  4            from (select t.ID,
  5                         ....
 12                    from test_dream t
 13                   order by t.GMT_CREATE desc) r
 14           WHERE rownum <= :1)
 15   WHERE linenum >= :2 ;

SQL> @?/rdbms/admin/utlxpls
---------------------------------------------------------------------
Id  | Operation           |  Name            | Rows  | Bytes | Cost |
---------------------------------------------------------------------
0 | SELECT STATEMENT         |                |  1053 |   255K|  31 |
1 |  VIEW                    |                |  1053 |   255K|  31 |
2 |   COUNT STOPKEY          |                |       |       |     |
3 |    VIEW                  |                |  1053 |   241K|  31 |
4 |     SORT ORDER BY STOPKEY|                |  1053 | 50544 |  31 |
5 |      TABLE ACCESS FULL   | test_dream     |1053   |50544  | 7 |
---------------------------------------------------------------------

执行计划走的是全表扫描，而全表扫描代价显然是很高的，尝试在GMT_CREATE建立索引，引导sql走时间索引。

SQL> create index ind_sai_dream_time on test_dream(GMT_CREATE) online compute statistics;
.........省略explain语句
SQL> @?/rdbms/admin/utlxpls
---------------------------------------------------------------------
Id  | Operation           |  Name            | Rows  | Bytes | Cost |
---------------------------------------------------------------------
0 | SELECT STATEMENT         |                |  1053 |   255K|  31 |
1 |  VIEW                    |                |  1053 |   255K|  31 |
2 |   COUNT STOPKEY          |                |       |       |     |
3 |    VIEW                  |                |  1053 |   241K|  31 |
4 |     SORT ORDER BY STOPKEY|                |  1053 | 50544 |  31 |
5 |      TABLE ACCESS FULL   | test_dream     |1053   |50544  | 7 |
---------------------------------------------------------------------

还是走的全表扫描，建了索引也分析过了，执行计划还是没有走索引，这是为什么呢?
简单细小的问题，关键时刻要立刻反应出来，这应该算是高级dba的能力一部分，我还不是，下面分析是经拖雷提醒我才想起来的。
分析：Btree索引不存储NULL值，而在order by desc中，NULL值总是最大的，sql语句通过索引无法判断表是否存在NULL，执行计划还是走全表扫描。
设置GMT_CREATE不为空:

SQL> alter table test_dream modify GMT_CREATE not null;
SQL> explain plan for select *
  2    from (select r.*, rownum linenum
  3            from (select t.ID,
  4                       ....
 11                    from test_dream t
 12                   order by t.GMT_CREATE desc) r
 13           WHERE rownum <= :1)
 14   WHERE linenum >= :2;

13:42:13 SQL> @?/rdbms/admin/utlxpls
-----------------------------------------------------------------
Id  | Operation          |  Name           | Rows  | Bytes | Cost
------------------------------------------------------------------
| SELECT STATEMENT               |             |1062  |257K |15 |
|  VIEW                          |             |1062  |257K |15 |
|   COUNT STOPKEY                |             |      |     |   |
|    VIEW                        |             |1062  |243K | 15|
|     TABLE ACCESS BY INDEX ROWID| test_dream  |1062  |50976| 15|
|      INDEX FULL SCAN DESCENDING| IND_DREAM_TIME|1062 |    |  4|
------------------------------------------------------------------

搞完这两步后，服务器负载立刻降下来在10之内。web分页其实有很多种实现方式，这个例子是最普通的写法，更多的例子在piner书里面有详细的介绍.http://www.ixdba.com/html/y2008/m01/195-book-information.html
通常来说，任何一条部署到生产库上的sql语句都必须通过dba的审核，这两个sql语句开发赶着发布，没有提交到dba这边来，差点就把数据库服务器整垮了。这明显是不遵守流程引起的故障，看来sql审核真的很需要，遵守流程真的很重要，