ORACLE 9i資料庫優化案例(2) --- 單列索引變複合索引的優化
方案實施過程及分析來啦：
越看越激動是吧，修改前後，從數倍，數十倍直到數百倍的效能差距，這究竟是怎麼實現的呢，要回答這個問題，我們還是要從源頭說起~~
我個人非常認同這樣的觀點：大多數的效能優化方案，最終都是要落實到SQL優化的層面來！！這裡，我們進行的調優操作，也是要從優化SQL語句著手！
我曾經提到過一種觀點：所謂調優，就是儘可能少讀並且儘可能少寫！！能否少寫很多時候DBA不能直接掌控，但讓它少讀，就本次案例來說，還是有辦法的。
從TOP等待事件來看，主要等待也是磁碟檔案讀，如果我們能夠讓它少讀一點(嚴重強調，"一點"也很重要，一條SQL語句每次如果能少產生幾百次IO，那該語句執行個幾萬次後，算下來也相當於節省了近千萬次IO呢)，對於系統整體負載就能起到重要的作用。
A>. 單列索引變複合索引
從之前的ORACLE報表中可以看到，佔用資源最多的其實就那麼四五條SQL語句，執行了很多次，佔用了大量的CPU資源，產生了巨量的邏輯讀/物理讀，其中最顯著的是這兩條：
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
450,247,504 1,974 228,088.9 32.1 2445.17 2405.51 1551938908
select count(1) as num from t_wiki_doc_topics where doc_id_encry
pt='BBwReRlFZV0RRXlkG' and topic_state=1
446,599,884 1,959 227,973.4 31.8 2450.06 2406.25 1250588569
select b.* from (select rownum as r,a.* from (select t.user_id,t
.last_post_user_id,t.doc_title,t.topic_title,t.click_count+1 as
click_count,t.posts_count-1 as posts_count,t.user_nick,to_char(t
.last_post_time,'yy-mm-dd hh24:mi:ss') last_post_time,t.last_pos
t_user_nick,t.last_post_user_id_encrypt,t.user_id_encrypt,t.onto提示：
第二條語句由於report指令碼的限制，沒有完全顯示，可以通過查詢v$sql獲取完整SQL語句，以協助分析。這兩語句佔用了一半以上的系統資源，訪問的物件相同都是t_wiki_doc_topics表，該表的查詢列之一doc_id_encrypt上建有索引，單條語句執行效率亦可接受，基本都是在s即可得到結果，但，由於查詢涉及資料量和查詢次數，仍然產生了大量的邏輯IO和CPU資源的佔用。
首先檢視第1條語句的執行計劃：
SQL> explain plan for
2 select count(1) as num from t_wiki_doc_topics where doc_id_encrypt='BBwReRlFZV0RRXlkG' and topic_state=1;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | B
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T_WIKI_DOC_TOPICS | 1 |
|* 3 | INDEX RANGE SCAN | IDX_DOC_TOPIC_DOC_ID_ENCRYPT | 1 |
--------------------------------------------ex------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_WIKI_DOC_TOPICS"."TOPIC_STATE"=1)
3 - access("T_WIKI_DOC_TOPICS"."DOC_ID_ENCRYPT"='BBwReRlFZV0RRXlkG')
Note: cpu costing is off
17 rows selected
從上述計劃可以看出，雖然有索引，由於還需要返回表中讀取資料，以過濾topic_state，因此一旦執行次數頻繁，就會迭代產生更多IO。接下來我們再看看第2條語句：
SQL> explain plan for
2
2 select b.*
3 from (select rownum as r, a.*
4 from (select t.user_id,
5 t.last_post_user_id,
6 t.doc_title,
7 t.topic_title,
8 t.click_count + 1 as click_count,
9 t.posts_count - 1 as posts_count,
10 t.user_nick,
11 to_char(t.last_post_time, 'yy-mm-dd hh24:mi:ss') last_post_time,
12 t.last_post_user_nick,
13 t.last_post_user_id_encrypt,
14 t.user_id_encrypt,
15 t.ontop_sort,
16 t.is_valuable,
17 t.doc_topics_id_encrypt,
18 t.is_ontop,
19 t.user_ip,
20 t.last_post_user_ip,
21 t.topic_type
22 from t_wiki_doc_topics t
23 where t.doc_id_encrypt = 'BBwReRlFZV0RRXlkG'
24 and t.topic_state = 1
25 order by t.is_ontop desc,
26 t.ontop_sort desc,
27 t.last_post_time desc) a
28 where rownum <= 40) b
29 where b.r >= 1
30 /
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
|* 1 | VIEW | | 1
|* 2 | COUNT STOPKEY | |
| 3 | VIEW | | 1
|* 4 | SORT ORDER BY STOPKEY | | 1
|* 5 | TABLE ACCESS BY INDEX ROWID| T_WIKI_DOC_TOPICS | 1
|* 6 | INDEX RANGE SCAN | IDX_DOC_TOPIC_DOC_ID_ENCRYPT | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."R">=1)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
5 - filter("T"."TOPIC_STATE"=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - access("T"."DOC_ID_ENCRYPT"='BBwReRlFZV0RRXlkG')
Note: cpu costing is off
23 rows selected
經過分析可以看到基本過濾條件與第一條相同：from t_wiki_doc_topics t where t.doc_id_encrypt = 'BBwReRlFZV0RRXlkG' and t.topic_state = 1。
對於這種型別的語句，我個人認為最簡單的辦法，就是刪除原doc_id_encrypt的舊索引，並建立新的複合索引(doc_id_encrypt+topic_state)即可。
執行建立指令碼如下：
SQL> drop index IDX_DOC_TOPIC_DOC_ID_ENCRYPT;
Index dropped
SQL> create index ind_t_wiki_doc_topics_id_stat on t_wiki_doc_topics (doc_id_encrypt,topic_state);
Index created建立完新索引之後，重新檢視執行計劃：
SQL> explain plan for
2 select count(1) as num from t_wiki_doc_topics where doc_id_encrypt='BBwReRlFZV0RRXlkG' and topic_state=1;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 |
| 1 | SORT AGGREGATE | | 1 | 19 |
|* 2 | INDEX RANGE SCAN | IND_T_WIKI_DOC_TOPICS_ID_STAT | 1 | 19 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_WIKI_DOC_TOPICS"."DOC_ID_ENCRYPT"='BBwReRlFZV0RRXlkG' AND
"T_WIKI_DOC_TOPICS"."TOPIC_STATE"=1)
Note: cpu costing is off
16 rows selected
SQL> explain plan for
2
2 select b.*
3 from (select rownum as r, a.*
4 from (select t.user_id,
5 t.last_post_user_id,
6 t.doc_title,
7 t.topic_title,
8 t.click_count + 1 as click_count,
9 t.posts_count - 1 as posts_count,
10 t.user_nick,
11 to_char(t.last_post_time, 'yy-mm-dd hh24:mi:ss') last_post_time,
12 t.last_post_user_nick,
13 t.last_post_user_id_encrypt,
14 t.user_id_encrypt,
15 t.ontop_sort,
16 t.is_valuable,
17 t.doc_topics_id_encrypt,
18 t.is_ontop,
19 t.user_ip,
20 t.last_post_user_ip,
21 t.topic_type
22 from t_wiki_doc_topics t
23 where t.doc_id_encrypt = 'BBwReRlFZV0RRXlkG'
24 and t.topic_state = 1
25 order by t.is_ontop desc,
26 t.ontop_sort desc,
27 t.last_post_time desc) a
28 where rownum <= 40) b
29 where b.r >= 1
30 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
|* 1 | VIEW | | 1
|* 2 | COUNT STOPKEY | |
| 3 | VIEW | | 1
|* 4 | SORT ORDER BY STOPKEY | | 1
| 5 | TABLE ACCESS BY INDEX ROWID| T_WIKI_DOC_TOPICS | 1
|* 6 | INDEX RANGE SCAN | IND_T_WIKI_DOC_TOPICS_ID_STAT | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."R">=1)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
6 - access("T"."DOC_ID_ENCRYPT"='BBwReRlFZV0RRXlkG' AND "T"."TOPIC_STATE"=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note: cpu costing is off
22 rows selected
對於第1條語句只需要通過索引，即可以得到資料，避免了TABLE ACCESS BY INDEX ROWID的操作；對於第2條語句，雖然索引不能提供所有要訪問的列，TABLE ACCESS BY INDEX ROWID不可避免，但是我們要想到，索引本身也是過濾，應用複合索引後得到的資料就是符合條件的記錄，這時只需要返回基表獲得其它列的資訊即可，而無須再做資料的filter，再加上COUNT STOPKEY的作用，仍然可以實現只需要很少的讀即可實現需求。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-680625/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE 9i資料庫優化案例(4) --- 索引改善UPDATEOracle資料庫優化索引
- 一個複合索引的優化案例索引優化
- ORACLE 9i資料庫優化案例(3) --- 低相異值的列也可以建立索引Oracle資料庫優化索引
- SUM優化（複合索引）優化索引
- SQLT 最佳化SQL 用複合索引代替單列索引的案例SQL索引
- 增加複合索引優化SQL的簡單過程索引優化SQL
- 複合索引與函式索引優化一例索引函式優化
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- Oracle 索引的優化Oracle索引優化
- 資料庫效能優化-索引與sql相關優化資料庫優化索引SQL
- Oracle優化案例-分割槽索引之無字首索引（六）Oracle優化索引
- Oracle優化案例-正確的使用索引（二）Oracle優化索引
- 資料庫引擎優化顧問與索引優化的差別資料庫優化索引
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- 理解索引：索引優化索引優化
- Oracle優化案例-自定義函式索引（五）Oracle優化函式索引
- 查詢中讓優化器使用複合索引優化索引
- Oracle對索引分析的優化Oracle索引優化
- sql優化之多列索引的使用SQL優化索引
- MSSQL優化之索引優化SQL優化索引
- 效能調優-Mysql索引資料結構詳解與索引優化MySql索引資料結構優化
- SQL優化（二）（聯合索引的使用）SQL優化索引
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- 理解 MySQL（2）：索引與優化MySql索引優化
- 【oracle 效能優化】組合索引之index_ssOracle優化索引Index
- SQLServer索引優化（3）：對於建組合索引的要求SQLServer索引優化
- Elasitcsearch索引優化索引優化
- MySQL索引優化MySql索引優化
- mysql優化索引MySql優化索引
- SQL優化-索引SQL優化索引
- MySQL 效能優化之索引優化MySql優化索引
- SQLServer索引優化（2）：對於索引中include的理解SQLServer索引優化
- SQL優化案例-正確的使用索引（二）SQL優化索引
- 資料庫系列：MySQL索引優化總結（綜合版）資料庫MySql索引優化
- 比較SQL Server 2008資料庫引擎優化和索引優化SQLServer資料庫優化索引
- Mysql索引優化之索引的分類MySql索引優化
- Oracle union all 不走索引的優化Oracle索引優化
- 索引回表操作，ORACLE所作的優化索引Oracle優化