一条简单update语句的优化

今天上午10点钟，经过数据库巡检，发现一条很简单的updatesql语句：
UPDATE card cc SET     cc.card_password =fn_md5('001122') WHERE   cc.card_no=2525000120001590；
此sql存在很严重的全表扫描情况，然而card_no字段却存在索引。根据分析，得到card_no存在隐式转换，导致索引失效。card_no字段为varchar型。代码中却定为int型。找到开发，程序中改正，sql运行ok了。
优化前：
SQL> select cc.card_password from card cc where cc.card_no=2525000120001590;

Execution Plan
----------------------------------------------------------
Plan hash value: 3078775325

--------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows   | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1     |    50   | 76929   (1)    | 00:15:24 |
|*  1 |  TABLE ACCESS FULL | CARD |     1     |    50   | 76929   (1)    | 00:15:24 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("CC"."CARD_NO")=2525000120001590)


Statistics
----------------------------------------------------------
      1          recursive calls
      0          db block gets
     305904  consistent gets
     200033  physical reads
      0           redo size
    562          bytes sent via SQL*Net to client
    520          bytes received via SQL*Net from client
      2            SQL*Net roundtrips to/from client
      0            sorts (memory)
      0            sorts (disk)
      1             rows processed

从执行计划得知，此语句存在严重的table access full.并且cpu+io的消耗达到 76929 次之多，并且存在305904 次逻辑读和200033 次物理读。
单条update更新需要运行15秒之久。在高并发情况下，此sql是很容易产生类似双11那天出现的锁等待现象。
优化后：
SQL> select cc.card_password from card cc where cc.card_no='2525000120001590';

Execution Plan
----------------------------------------------------------
Plan hash value: 1939090742

-------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                           |    1      |    50  |    4   (0)         | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CARD                |    1      |    50  |    4   (0)          | 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                  | IDX_CARD_NO |    1       |         |    3   (0)          | 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CC"."CARD_NO"='2525000120001590')


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      5  consistent gets
      0  physical reads
      0  redo size
    562  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

优化后的sql从执行计划上来看，table access full不见了，取而代之的是 INDEX UNIQUE SCAN唯一索引扫描。cost性能将为了4，从7692降到了4。逻辑读降到了5，物理读降到了0.同比降低倍数很巨大。
单条运行0.01秒即可。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28912313/viewspace-1847066/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28912313/viewspace-1847066/