读懂IDS10的sqexplain.out

最新推荐文章于 2022-03-21 16:42:30 发布

shenzheng79

最新推荐文章于 2022-03-21 16:42:30 发布

阅读量1k

点赞数 1

分类专栏： informix

本文链接：https://blog.csdn.net/shenzheng79/article/details/8884545

版权

informix 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

简单介绍下IDS10的sqexplain.out，IDS11类似，只是增加了统计项而已

QUERY:
------
select * frommsp_optrace
where money=10000


Estimated Cost:181337
Estimated # of RowsReturned: 174915


  1) informix.msp_optrace: SEQUENTIAL SCAN
        Filters: informix.msp_optrace.money =10000

这是最简单的，单表顺序扫描，加一个过滤

EstimatedCost: 代表数据库估算的查询代价，一般情况下是越小越好，有时还需要实际执行根据结果返回的时间长短确定

Estimated# of Rows Returned: 数据库估算出的结果返回行数

QUERY:
------
select * from msp_optrace
where   reqtime>'20130401000000' and   reqtime<'20130401999999'


Estimated Cost: 31303
Estimated # of Rows Returned: 189918

  1) informix.msp_optrace: INDEX PATH

    (1) Index Keys: reqtime   (Serial, fragments: ALL)
        Lower Index Filter: informix.msp_optrace.reqtime > '20130401000000'
        Upper Index Filter: informix.msp_optrace.reqtime < '20130401999999'

可以看到这是索引扫描

Lower Index Filter: 从这里开始找

Upper Index Filter: 找到这里结束

QUERY:
------
select reqtime from msp_optrace
where   reqtime>'20130401000000' and   reqtime<'20130401999999'

Estimated Cost: 7528
Estimated # of Rows Returned: 189918

  1) informix.msp_optrace: INDEX PATH

    (1) Index Keys: reqtime   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: informix.msp_optrace.reqtime > '20130401000000'
        Upper Index Filter: informix.msp_optrace.reqtime < '20130401999999'

还是索引扫描，由于需要的字段在索引键里都存在，因此只需进行“Key-Only”，即不需要使用数据表内数据（也就是不用使用rowid去找数据本身了）

QUERY:
------
select reqtime from msp_optrace
where   reqtime>'20130401000000' and   reqtime<'20130401999999'
and opid=400000


Estimated Cost: 31303
Estimated # of Rows Returned: 179

  1) informix.msp_optrace: INDEX PATH

        Filters: informix.msp_optrace.opid = 400000

    (1) Index Keys: reqtime   (Serial, fragments: ALL)
        Lower Index Filter: informix.msp_optrace.reqtime > '20130401000000'
        Upper Index Filter: informix.msp_optrace.reqtime < '20130401999999'

由于还需要进行opid过滤，因此索引使用的不是“Key Only”，即使你最后要得结果只有索引键本身（select reqtime）

QUERY:
------
select a.*,b.opid,b.state as opstate,ota_user.accountID,ota_user.parentAcc
from msp_pay_msg a,msp_optrace b,ota_user
where a.traceid=b.traceid and b.opid=ota_user.useraccount
and b.reqtime>'20130401000000' and b.reqtime<'20130401999999'
and a.msgtype=1 order by b.reqtime desc


Estimated Cost: 172298
Estimated # of Rows Returned: 11316

  1) informix.b: INDEX PATH

    (1) Index Keys: reqtime   (Serial, fragments: ALL)
        Lower Index Filter: informix.b.reqtime < '20130401999999'
        Upper Index Filter: informix.b.reqtime > '20130401000000'

  2) informix.ota_user: INDEX PATH

    (1) Index Keys: useraccount   (Serial, fragments: ALL)
        Lower Index Filter: informix.b.opid = informix.ota_user.useraccount
NESTED LOOP JOIN

  3) informix.a: INDEX PATH

        Filters: informix.a.msgtype = 1

    (1) Index Keys: traceid   (Serial, fragments: ALL)
        Lower Index Filter: informix.a.traceid = informix.b.traceid
NESTED LOOP JOIN

这个比较复杂，过程如下：

先使用b表的索引键进行过滤，条件为b.reqtime>'20130401000000' andb.reqtime<'20130401999999'。原因是只有这个条件会减少数据量

然后使用过滤出来的结果和ota_user表的Index Keys: useraccount 做嵌套查询，找出informix.b.opid =informix.ota_user.useraccount的记录

最后，将查询出来的结果和a表的Index Keys: traceid 做嵌套查询，得出的结果进行“Filters: informix.a.msgtype = 1”得到最终结果

QUERY:
------
select money from msp_optrace
where reqtime>'20130401000000' and   reqtime<'20130401999999'
order by money

    
Estimated Cost: 134058
Estimated # of Rows Returned: 189918
Temporary Files Required For: Order By

  1) informix.msp_optrace: INDEX PATH
    
    (1) Index Keys: reqtime   (Serial, fragments: ALL)
        Lower Index Filter: informix.msp_optrace.reqtime > '20130401000000'
        Upper Index Filter: informix.msp_optrace.reqtime < '20130401999999'

这个查询使用reqtime进行过滤，然后对结果依照money字段排序，需要产生临时排序表，通过“Temporary Files Required For: Order By” 可以看到。

由于需要产生临时表，tempdbs会被占用，同时会产生脏页。

[informix@mdw ids]$ onstat -R

IBM Informix Dynamic Server Version 10.00.FC6     -- On-Line -- Up 03:44:08 -- 3011276 Kbytes
。。。。。。
10 dirty, 1000000 queued, 1000000 total, 1048576 hash buckets, 2048 buffer size
start clean at   0.050% (of pair total) dirty, or 63 buffs dirty, stop at
   0.000%

[informix@mdw ids]$ onstat -D

IBM Informix Dynamic Server Version 10.00.FC6     -- On-Line -- Up 03:44:13 -- 3011276 Kbytes

Dbspaces
address          number   flags      fchunk   nchunks  pgsize   flags    owner    name
ca0c2e78         1        0x60001    1        1        2048     N  B     informix rootdbs
ca0c3988         2        0x60001    2        2        2048     N  B     informix userdbs
ca0c3b20         3        0x42001    3        1        2048     N TB     informix tempdbs
ca0c3cb8         4        0x60001    4        1        2048     N  B     informix logdbs
 4 active, 2047 maximum

Chunks
address          chunk/dbs  offset     page Rd  page Wr  pathname
ca0c3028         1     1    0          0        0        /ids/dbfiles/rootdbs1
ca0c34a8         2     2    0          0        0        /ids/dbfiles/userdbs1
ca0c3648         3     3    0          728      1036     /ids/dbfiles/tempdbs1
ca0c37e8         4     4    0          0        0        /ids/dbfiles/logdbs1
cb689958         5     2    0          0        0        /ids/dbfiles/userdbs2
 5 active, 32766 maximum

NOTE: The values in the "page Rd" and "page Wr" columns for DBspace chunks 
      are displayed in terms of system base  page size.

Expanded chunk capacity mode: always

[informix@mdw ids]$ 


QUERY:  
------
select * from msp_optrace
where reqtime>'20130401000000' and   reqtime<'20130401999999'
order by reqtime
    
    
Estimated Cost: 31303 
Estimated # of Rows Returned: 189918

  1) informix.msp_optrace: INDEX PATH
    
    (1) Index Keys: reqtime   (Serial, fragments: ALL)
        Lower Index Filter: informix.msp_optrace.reqtime > '20130401000000'
        Upper Index Filter: informix.msp_optrace.reqtime < '20130401999999'

看这个查询，虽然也进行排序操作，但由于是按索引键排序，而索引本身就是有顺序的，查询速度就会快很多。

原则总结如下: 如果order by字句中的所有列都按所要求的顺序出现在单个索引中,那么数据库可以使用索引来按行既定的顺序读取他们,从而避免了排序.

最后，针对Estimated Cost在多说两句：

看下面的查询，语句本身和上面的一个查询是一样的，只不过上面的查询没有建立联合索引，而是使用的“Index Keys: reqtime ”的单独索引，这个查询中使用的是“Index Keys: opid reqtime ” 联合索引，因此数据库产生了不同的执行计划：

QUERY:
QUERY:
------
select a.*,b.opid,b.state as opstate,ota_user.accountID,ota_user.parentAcc
from msp_pay_msg a,msp_optrace b,ota_user
where a.traceid=b.traceid and b.opid=ota_user.useraccount
and b.reqtime>'20130401000000' and b.reqtime<'20130401999999'
and a.msgtype=1 order by b.reqtime desc


Estimated Cost: 91301
Estimated # of Rows Returned: 11316
Temporary Files Required For: Order By

  1) informix.ota_user: SEQUENTIAL SCAN

  2) informix.b: INDEX PATH

    (1) Index Keys: opid reqtime   (Serial, fragments: ALL)
        Lower Index Filter: (informix.b.opid = informix.ota_user.useraccount AND informix.b.reqtime > '20130401000000' )
        Upper Index Filter: informix.b.reqtime < '20130401999999'
NESTED LOOP JOIN

  3) informix.a: INDEX PATH

        Filters: informix.a.msgtype = 1

    (1) Index Keys: traceid   (Serial, fragments: ALL)
        Lower Index Filter: informix.a.traceid = informix.b.traceid
NESTED LOOP JOIN

表面看，这个语句的EstimatedCost值很小，但由于这样的索引导致了针对reqtime的排序无法使用索引，而需要产生临时表“Temporary Files Required For: Order By”，执行时间反到长很多。实际测试上面的语句是15s（reqtime单独索引），这个语句是2m30s（opid，reqtime联合索引），因此Estimated Cost也不能全信。

强调一点：Index Keys:opid reqtime 的reqtime在第二个字段，针对这个字段的排序效果就没有了。