本文详细分析了一个SQL查询语句的问题所在，并通过正确使用索引来优化执行计划，使逻辑读操作从85万次减少到1千多次，实际执行时间缩短至不到1秒。通过调整查询语句中的排序方式，使得执行计划发生了根本性的改变，进而提高了查询性能。
刚刚酒窖开发人员发了封邮件给我，说酒窖测试库现在的一个查询出奇的慢，一直在执行状态，让我这里给看看，具体的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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-722055/
扫一扫
683
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
