在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: java@favorITe66.cm2 (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: java@favorITe66.cm2 (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: java@favorITe51.cm1 (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字段，大量减少了回表访问数据,使系统性能得到提升。 |
