sql調優一例---索引排序hint
剛剛酒窖開發人員發了封郵件給我，說酒窖測試庫現在的一個查詢出奇的慢，一直在執行狀態，讓我這裡給看看，具體的sql語句如下：
SELECT b.*
FROM (SELECT a.*, ROWNUM num
FROM (SELECT /*+ index ( a IND_M_SNS_STATUS_IDX3)*/
A.*,
B.NICK_NAME,
(SELECT COUNT(MGI.GOODS_ID)
FROM M_GOODS_INFO MGI
WHERE a.GOODS_ID = MGI.GOODS_ID) AS GOODS_PJNUM,
NVL(FUN_SNS_WINE_SCORE(A.GOODS_ID), 0) AS Fraction,
B.HEAD_IMG_URL,
B.RANK_FLAG,
B.FAMOUS_FLAG,
ROUND(C.TOTAL_GOODS_RANK / C.RANK_TIMES) AS AVGRANK,
'' AS CITY_PROMOTION_NAME,
'' AS GROUP_NAME,
(SELECT GOODS_SUB_CLASS
FROM M_GOODS_INFO GI
WHERE GI.GOODS_ID = A.GOODS_ID) AS WINE_TYPE_ID
FROM M_SNS_STATUS A
LEFT JOIN M_SNS_USER B
ON A.USER_ID = B.ID
LEFT JOIN M_SNS_GOODS_RANK C
ON A.GOODS_ID = C.GOODS_ID
WHERE A.GOODS_ID IS NOT NULL
AND B.ID NOT IN (47220)
ORDER BY A.POST_TIME DESC) a
where ROWNUM <= 4) b
WHERE num > 0 ;
具體執行計劃和統計資料如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 3789311727
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 6452 | | 16682 (1)| 00:03:21 |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_M_GOODS_INFO2 | 1 | 6 | | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | M_GOODS_INFO | 1 | 9 | | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_M_GOODS_INFO2 | 1 | | | 1 (0)| 00:00:01 |
|* 5 | VIEW | | 4 | 6452 | | 16682 (1)| 00:03:21 |
|* 6 | COUNT STOPKEY | | | | | | |
| 7 | VIEW | | 15366 | 23M| | 16682 (1)| 00:03:21 |
|* 8 | SORT ORDER BY STOPKEY | | 15366 | 4756K| 5136K| 16682 (1)| 00:03:21 |
|* 9 | HASH JOIN | | 15366 | 4756K| | 15634 (1)| 00:03:08 |
|* 10 | TABLE ACCESS FULL | M_SNS_USER | 63904 | 1248K| | 368 (3)| 00:00:05 |
|* 11 | HASH JOIN RIGHT OUTER | | 15366 | 4456K| | 15264 (1)| 00:03:04 |
| 12 | TABLE ACCESS FULL | M_SNS_GOODS_RANK | 119 | 1071 | | 2 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| M_SNS_STATUS | 15366 | 4321K| | 15262 (1)| 00:03:04 |
|* 14 | INDEX FULL SCAN | IND_M_SNS_STATUS_IDX3 | 15366 | | | 36 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MGI"."GOODS_ID"=:B1)
4 - access("GI"."GOODS_ID"=:B1)
5 - filter("NUM">0)
6 - filter(ROWNUM<=4)
8 - filter(ROWNUM<=4)
9 - access("A"."USER_ID"="B"."ID")
10 - filter("B"."ID"<>47220)
11 - access("A"."GOODS_ID"="C"."GOODS_ID"(+))
13 - filter("A"."USER_ID"<>47220)
14 - filter("A"."GOODS_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
15388 recursive calls
0 db block gets
853019 consistent gets
27 physical reads
116 redo size
4775 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
從以上執行計劃不難看出，SORT ORDER BY STOPKEY排序操作導致一連串的執行計劃都發生了變化，有2張表走了全表掃描，強制索引雖然走了索引，但是效果很不理想，最終的結果就是邏輯讀非常高，達到了85w之多，為什麼會這樣？根本原因還在於查詢語句當中使用了排序，就是上面語句當中標註為綠色的部分，如何進行調整？以下是我調整後的語句：
SELECT b.*
FROM (SELECT a.*, ROWNUM num
FROM (SELECT /*+ index_desc( a M_SNS_STATUS_IDX7)*/
A.*,
B.NICK_NAME,
(SELECT COUNT(MGI.GOODS_ID)
FROM M_GOODS_INFO MGI
WHERE a.GOODS_ID = MGI.GOODS_ID) AS GOODS_PJNUM,
NVL(FUN_SNS_WINE_SCORE(A.GOODS_ID), 0) AS Fraction,
B.HEAD_IMG_URL,
B.RANK_FLAG,
B.FAMOUS_FLAG,
ROUND(C.TOTAL_GOODS_RANK / C.RANK_TIMES) AS AVGRANK,
'' AS CITY_PROMOTION_NAME,
'' AS GROUP_NAME,
(SELECT GOODS_SUB_CLASS
FROM M_GOODS_INFO GI
WHERE GI.GOODS_ID = A.GOODS_ID) AS WINE_TYPE_ID
FROM M_SNS_STATUS A
LEFT JOIN M_SNS_USER B
ON A.USER_ID = B.ID
LEFT JOIN M_SNS_GOODS_RANK C
ON A.GOODS_ID = C.GOODS_ID
WHERE A.GOODS_ID IS NOT NULL
AND B.ID NOT IN (47220)
ORDER BY A.POST_TIME DESC) a
where ROWNUM <= 4) b
WHERE num > 0 ;
其中強制走索引排序，該索引正是sql語句中排序列上的索引，其執行計劃如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 1327187650
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 6452 | 199 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX UNIQUE SCAN | PK_M_GOODS_INFO2 | 1 | 6 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | M_GOODS_INFO | 1 | 9 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_M_GOODS_INFO2 | 1 | | 1 (0)| 00:00:01 |
|* 5 | VIEW | | 4 | 6452 | 199 (0)| 00:00:03 |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | VIEW | | 5 | 8000 | 199 (0)| 00:00:03 |
| 8 | NESTED LOOPS | | 5 | 1585 | 199 (0)| 00:00:03 |
| 9 | NESTED LOOPS OUTER | | 6 | 1782 | 193 (0)| 00:00:03 |
|* 10 | TABLE ACCESS BY INDEX ROWID| M_SNS_STATUS | 15366 | 4321K| 192 (0)| 00:00:03 |
| 11 | INDEX FULL SCAN DESCENDING| M_SNS_STATUS_IDX7 | 605K| | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| M_SNS_GOODS_RANK | 1 | 9 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_M_SNS_GOODS_RANK | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | M_SNS_USER | 1 | 20 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PRI_M_SNS_USER_ID | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MGI"."GOODS_ID"=:B1)
4 - access("GI"."GOODS_ID"=:B1)
5 - filter("NUM">0)
6 - filter(ROWNUM<=4)
10 - filter("A"."GOODS_ID" IS NOT NULL AND "A"."USER_ID"<>47220)
13 - access("A"."GOODS_ID"="C"."GOODS_ID"(+))
15 - access("A"."USER_ID"="B"."ID")
filter("B"."ID"<>47220)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
1594 consistent gets
1 physical reads
0 redo size
4775 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
可以看出執行計劃完全變了，邏輯讀驚人的降到了1千多，實際執行時間不到1秒鐘，可見使用錯hint對sql語句的影響有多大。
記錄一下~~
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25618347/viewspace-722055/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用hint來調優sql語句SQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- MySQL索引和SQL調優MySql索引
- SQL優化之利用索引排序SQL優化索引排序
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大優化執行）SQLServer索引優化
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- 使用USE_HASH Hint調優一個SQL語句SQL
- MySQL 索引和 SQL 調優總結MySql索引
- 高效的SQL（函式索引優化VIEW一例）SQL函式索引優化View
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 又一個複合索引的SQL調優索引SQL
- oracle SQL調整一例OracleSQL
- pl/sql 氣泡排序一例SQL排序
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 複合索引與函式索引優化一例索引函式優化
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- mysql hint憂化一例MySql
- SQL調整優化與10053跟蹤分析一例SQL優化
- PL/SQL優化一例SQL優化
- MySQL調優之索引優化MySql索引優化
- SQL優化-索引SQL優化索引
- SQL調優SQL
- SQL調整優化與10053跟蹤分析一例(zt)SQL優化
- 通過hint push_subq優化sql優化SQL
- oracle優化一例之sql優化Oracle優化SQL
- SQL Server調優實戰 亂建聚集索引的後果SQLServer索引
- Oracle索引HINT的使用Oracle索引
- SQL調整：‘以空間換效能’調整一例SQL
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- oracle sql 排序優化OracleSQL排序優化
- 【SQL 學習】排序問題之order by與索引排序SQL排序索引
- sql調優1SQL
- oracle sql調優OracleSQL
- 【sql hint 提示】SQL