sql优化1：分页查询

 sql优化1：分页查询

测试环境：
192.168.0.209上的wzgltest数据库
工具：
db2命令行

样表：PROJECT_MATERIAL

D:/>db2 select count(*) from PROJECT_MATERIAL with ur
1
-----------
    1148372
  1 条记录已选择。

场景：
    分页显示该表的所有内容，每页显示10行记录。

===================================================

来看一个普通查询：

select *
  from (
    select row_number() over () rn,t.*
      from PROJECT_MATERIAL t) a
  where rn between ? and ?
  with ur;

执行时间 Time(h:m:s.ms): 0:0:35.968

其执行路径：

Estimated Cost = 396069.062500
Estimated Cardinality = 114837.203125
Table Constructor
|  1-Row(s)
Residual Predicate(s)
|  #Predicates = 1
Nested Loop Join
|  Piped Inner
|  Access Table Name = WZGLADM.PROJECT_MATERIAL  ID = 4,21
|  |  #Columns = 42
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Isolation Level: Uncommitted Read
|  |  Lock Intents
|  |  |  Table: Intent None
|  |  |  Row  : None
|  Residual Predicate(s)
|  |  #Predicates = 2
Return Data to Application
|  #Columns = 43

看出没有使用到索引，执行的是全表扫描！更糟糕的是每翻一页都是这么慢，显然客户是受不了这种效果的。


=====================================================

优化思路：
    考虑到每页只有10条，如果每次都全表扫描，显然不行；
    缓冲整个表，然后翻页时从内存里取数据，理论上可以这么做，但开销太大；
    用索引，是可行的，关键是怎么让优化器选择使用索引！索引的使用条件有两方面，一方面是where条件里要用到索引字段；其次就是数据范围方面，如果索引的取值范围超过7%（大概这个数字），则优化器就忽略索引而选择全部扫描。所以，我想到修改sql，使查询条件里含有索引字段，其次就是用返回记录范围数来显式告知优化器数据范围。

优化后的语句：

select * from PROJECT_MATERIAL 
where id in (select id 
  from (
    select row_number() over () rn,t.id
      from PROJECT_MATERIAL t) a
  where rn between ? and ? 
  fetch first 10 rows only
  )
  with ur

执行时间 Time(h:m:s.ms): 0:0:0.078     

这样每次翻页执行性能是一样快，一个字，爽！

再看一下执行路径：

Estimated Cost = 3698.492920
Estimated Cardinality = 10.000000
Table Constructor
|  1-Row(s)
Residual Predicate(s)
|  #Predicates = 1
Nested Loop Join
|  Piped Inner
|  Access Table Name = WZGLADM.PROJECT_MATERIAL  ID = 4,21
|  |  Index Scan:  Name = WZGLADM.PM_I3  ID = 4
|  |  |  Regular Index (Not Clustered)
|  |  |  Index Columns:
|  |  |  |  1: ID (Ascending)
|  |  |  |  2: STATE (Ascending)
|  |  #Columns = 1
|  |  #Key Columns = 0
|  |  |  Start Key: Beginning of Index
|  |  |  Stop Key: End of Index
|  |  Index-Only Access
|  |  Index Prefetch: None
|  |  Isolation Level: Uncommitted Read
|  |  Lock Intents
|  |  |  Table: Intent None
|  |  |  Row  : None
|  Residual Predicate(s)
|  |  #Predicates = 2
Nested Loop Join
|  Access Table Name = WZGLADM.PROJECT_MATERIAL  ID = 4,21
|  |  Index Scan:  Name = WZGLADM.PM_I3  ID = 4
|  |  |  Regular Index (Not Clustered)
|  |  |  Index Columns:
|  |  |  |  1: ID (Ascending)
|  |  |  |  2: STATE (Ascending)
|  |  #Columns = 42
|  |  Single Record
|  |  #Key Columns = 1
|  |  |  Start Key: Inclusive Value
|  |  |  |  |  1: ?
|  |  |  Stop Key: Inclusive Value
|  |  |  |  |  1: ?
|  |  Data Prefetch: None
|  |  Index Prefetch: None
|  |  Isolation Level: Uncommitted Read
|  |  Lock Intents
|  |  |  Table: Intent None
|  |  |  Row  : None
Return Data to Application
|  #Columns = 42

看得出是先全索引扫描，再用索引取数，最后join



今天，为监视192.168.0.214:50016/cwgl数据库中的sql执行情况，开启了sql监视器，内容记入表stmt_sql_bak中，该表迅速扩大，为分析语句恰好运用上述优化思想，执行效果对比如下：

D:/>db2 select count(*) from stmt_sql_bak with ur
1
-----------
    2213502
  1 条记录已选择。


select *
  from (
    select row_number() over (order by stop_time-start_time desc) rn,t.*
      from stmt_sql_bak t) a
  where rn between ? and ?
  with ur

执行时间：Time(h:m:s.ms): 0:2:41.282

优化后的sql：

select *
  from stmt_sql_bak
  where id in (
      select id
        from (
          select row_number() over (order by stop_time-start_time desc) rn,id
            from stmt_sql_bak) t
        where rn between ? and ?
        fetch first 10 rows only )
  with ur

执行时间：Time(h:m:s.ms): 0:0:4.062

其中，主键索引做了修改，使用include选项，避免了表扫描：

D:/>db2 alter table stmt_sql_bak drop primary key
DB20000I  SQL 命令成功完成。
D:/>db2 create unique index stmt_i1 on stmt_sql_bak(id) include (start_time,stop_time)
DB20000I  SQL 命令成功完成。
D:/>db2 alter table stmt_sql_bak add constraint stmt_p primary key(id)
SQL0598W  现有的索引 "CWGLADM.STMT_I1" 被用作主键或唯一键的索引。
SQLSTATE=01550