性能优化,你是否忽视了这一点

 

在OLTP的应用环境下，我们对一个数据库系统进行优化。通常来讲，可以从两方面着手。第一个方面，优化应用实现逻辑；第二个方面，优化数据库中的SQL语句。优化数据库中的SQL语句，可能大家通常会想到，为SQL创建合适的索引，让SQL走正确的执行计划。但最近优化了好几个这样的案例，情况却不是这样的。另外一点，我们要学会利用statpack这个工具，这可是个发现有性能问题的SQL的利器。 
 
优化前，在statpack中发现有一条语句,此语句物理读排名第二,逻辑读也消耗不少： 
逻辑读如下： 
 
                                                     CPU      Elapsd 
 
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value 
--------------- ------------ -------------- ------ -------- --------- ------ 
     13,575,220      464,573           29.2   13.8   798.17   9601.89  379611895 
 
Module: [e-mail]2 (TNS V1-V3) 
  select collect_info_id as id, user_id as userid, USER_NICK as 
userNick, collect_item_id as collectitemid,      isshared, note, 
 status, collect_time as collecttime,tag as tag   from collect_i 
nfo   where status > -1    and collect_item_id = :1   and user_i 
d = :2 
 
 
  
物理读如下: 
 
                                                     CPU      Elapsd 
 
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value 
--------------- ------------ -------------- ------ -------- --------- -------- 
        987,848      464,573            2.1    8.8   798.17   9601.89  379611895 
 
Module: [e-mail]2 (TNS V1-V3) 
  select collect_info_id as id, user_id as userid, USER_NICK as 
userNick, collect_item_id as collectitemid,      isshared, note, 
 status, collect_time as collecttime,tag as tag   from collect_i 
nfo   where status > -1    and collect_item_id = :1   and user_i 
d = :2 
 
上面这条语句的执行计划如下： 
 
---------------------------------------------------------------------------- 
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost | 
---------------------------------------------------------------------------- 
|SELECT STATEMENT                |----- 379611895 -----|       |      |      2 | 
|TABLE ACCESS BY INDEX ROWID     |COLLECT_INFO         |     1 |   61 |      2 | 
| INDEX RANGE SCAN               |IDX_COLLECT_INFO_ITE |     1 |      |      4 | 
---------------------------------------------------------------------------- 
 
 
上面这条语句的执行计划是正确的，但凭着对业务的熟悉，对应用select如此多的字段产生了怀疑，在与开发工程师了解后，确认了实际上只需要collect_info_id字段.因此，对上面这条SQL语句的优化策略是，去掉多余的select字段，只保留collect_info_id字段，条件字段不变。 
 
优化发布后： 
逻辑读，已经有大量下降，从单次29.2的逻辑读下降到7.4个: 
 
                                                     CPU      Elapsd 
 
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value 
--------------- ------------ -------------- ------ -------- --------- ------- 
      3,273,782      439,679            7.4    3.7    11.35    791.89 1405865088 
Module: [e-mail]1 (TNS V1-V3) 
  select/*+ index(i,IDX_COLLECT_INFO_USERID) */ collect_info_id 
as id   from collect_info i   where status > -1    and collect_i 
tem_id = :1   and user_id = :2 
 
物理读部份statpack已经看不到这个语句了 
 
当前这个语句的执行计划如下，很明显，这个SQL不用回表了，另外一个索引包括了所有需要访问的字段。 
 
----------------------------------------------------------------------------- 
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost | 
----------------------------------------------------------------------------- 
|SELECT STATEMENT                |----- 1405865088 ----|       |      |      2 | 
|INDEX RANGE SCAN                |IDX_COLLECT_INFO_USE |     1 |   16 |      4 | 
----------------------------------------------------------------------------- 
 
 
这个优化发布后，整个系统性能提高了不少,物理读平均第秒钟少400个IO ,逻辑读平均每秒钟少3000个,达到了优化预期。而优化的途径却是通过去掉一大堆应用程序不用的select字段，大量减少了回表访问数据,使系统性能得到提升。