索引 索引 还是索引

很久以前,我曾经写过一篇文章:Oracle初学者入门指南-索引是干什么用的?. 使用索引实在是最最基础的知识.

可是昨天晚上又被害了一次.

症状表象就是数据库服务器CPU资源被耗尽,数据库响应缓慢.

1.登陆检查v$session_wait视图

发现大量latch free,db file scattered read的等待

SQL> select sid,event from v$session_wait;

       SID EVENT
---------- ----------------------------------------------------------------
        10 latch free
        12 latch free
        16 latch free
.....
         7 rdbms ipc message
         4 rdbms ipc message
        58 buffer busy waits
       191 buffer busy waits
       244 buffer busy waits
       240 buffer busy waits
       333 buffer busy waits
       143 buffer busy waits
       282 log file sync
        28 db file sequential read
....
        39 db file scattered read
        68 db file scattered read
        77 db file scattered read
       114 db file scattered read
        80 db file scattered read
        40 db file scattered read
....

278 rows selected. 

2.获取问题SQL

SQL> @getsql
Enter value for sid: 297
old   5: where b.sid='&sid'
new   5: where b.sid='297'

SQL_TEXT
----------------------------------------------------------------
select wapuser0_.userID as userID, wapuser0_.image as image14_,
wapuser0_.gender as gender14_, wapuser0_.admin as admin14_, wapu
ser0_.passwordHash as password6_14_, wapuser0_.username as usern
ame14_, wapuser0_.creationdate as creation8_14_, wapuser0_.delet
ed as deleted14_, wapuser0_.rewardpoints as rewardp10_14_, wapus
er0_.address_description as address11_14_, wapuser0_.zip as zip1
4_, wapuser0_.province as province14_, wapuser0_.residency as re
sidency14_, wapuser0_.age as age14_, wapuser0_.modificationdate
as modific16_14_, wapuser0_.nickname as nickname14_, wapuser0_.n
ickname2 as nickname18_14_, wapuser0_.ua as ua14_, wapuser0_.dev
iceid as deviceid14_, wapuser0_.mobile as mobile14_, wapuser0_.m

SQL_TEXT
----------------------------------------------------------------
onternetid as montern22_14_, wapuser0_.otherid as otherid14_, wa
puser0_.fromgprs as fromgprs14_ from HS_passport wapuser0_ where
 wapuser0_.subclass='WapUser' and ((wapuser0_.mobile=:1 ))

14 rows selected. 

3.检查索引状况

发现该表Mobile字段无索引,而该表是数据量极大的一张表

SQL> select index_name,column_name from user_ind_columns 
   2 where table_name='HS_PASSPORT';

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
PK_HS_PASSPORT                 USERID
 

4.创建索引之后问题解决

SQL> create index i_mobile on hs_passport(MOBILE);

Index created. 

总结:

索引很重要,开发要记牢.