一條"簡單"的sql語句和小兔子買面包的故事

有時候開發人員寫sql語句的時候,接觸的性能問題越多,可能對sql語句的結構，性能考慮會多一些，這也是一件好事，不過如果考慮不當，本來原本想做的的一些優化卻使得問題變得更加嚴重。

在生産環境中我們對指定的用戶端都有一定的監控，在下午的時候發現一個sql語句執行的時間太長了，抓到語句，一看倒不複雜，是一個開發人員運作的。

從他的sql語句可以看出他在嘗試自連接配接account表（account表示百萬級别的）,l9_id不是account的主鍵，ban這個字段是主鍵字段。

select *

  from account a

 where l9_id = 'XX'

   and l9_id is not null

   and exists (select 1

          from account

         where l9_id = a.l9_id

           and ban a.ban

           and rownum = 1)

 order by a.l9_id

他要做的查詢從結構上來看類似下面的形式，比如表account裡的資料如下

 ban     l9_id

1         1001

2         1001

3         1002

4         1001

5         1002

每個account對應一個l9_id字段，是不唯一的，比如根據account ban=1得到l9_id=1001,現在要查的是除了ban=1之外的l9_id為1001的ban清單。

從sql結構中可以看到，開發人員還專門使用了exists rownum，看起來好像還不錯。

來看看oracle生成的執行計劃。

你會看到cost已經到1886M，時間是999:59:59,從計劃來看,oracle都不知道什麼時候能執行完成。

的确從生成環境來看，這條語句執行很慢，用了2個小時。

SQL> @plan

Plan hash value: 2418382151

--------------------------------------------------------------------------------------------

| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT     |             |     1 |   204 |       |  1886M  (1)|999:59:59 |

|   1 |  SORT ORDER BY       |             |     1 |   204 |   537M|  1886M  (1)|999:59:59 |

|*  2 |   FILTER             |             |       |       |       |            |          |

|*  3 |    TABLE ACCESS FULL |     ACCOUNT |  1651K|   321M|       | 33945   (1)| 00:06:48 |

|*  4 |    COUNT STOPKEY     |             |       |       |       |            |          |

|*  5 |     TABLE ACCESS FULL|     ACCOUNT |     2 |    26 |       |  1146   (1)| 00:00:14 |

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM "ACCOUNT" "ACCOUNT" WHERE

              ROWNUM=1 AND "L9_ID"=:B1 AND "BAN":B2))

   3 - filter("L9_COMPANY_CODE"='XX' AND "L9_ID" IS NOT NULL)

   4 - filter(ROWNUM=1)

   5 - filter("L9_ID"=:B1 AND "BAN":B2)

可以舉個笑話來說明一下，這個邏輯的問題，

有一天，小白兔到面包房買面包，問老闆，“老闆啊，你有100個面包嗎？",老闆說沒有啊，小白兔第二天又去買面包，又問老闆有沒有100個面包啊。老闆還是說沒有，第三天的時候老闆忙了很久終于做好了100個面包，小白兔又來買面包了，問老闆你又100個面包嗎，老闆高興的說有啊，小白兔也高興的說，給我買一個！

如果仔細分析邏輯，就會發現那個rownum是畫蛇添足，本來可以順利得到ban的清單，但是反複循環，循環幾百萬次，每次都是一個全表掃描，還不一定能夠查到對應的ban值。

對于這個語句，其實不用那麼複雜。寫成下面的形式就好。基本沒有什麼特别的地方。

 select t1.ban from account t1,account t2

 where t1.id = 'TD' 

 and  t1.id is not null and t2.l9_id is not null

 and t1.l9_id=t2.l9_lid

 and t1.ban!=t2.ban

執行計劃來看确實是一個可以達到目标的計劃。

Plan hash value: 1286362100

------------------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT   |             |    48M|  1353M|       | 78038   (2)| 00:15:37 |

|*  1 |  HASH JOIN         |             |    48M|  1353M|    44M| 78038   (2)| 00:15:37 |

|*  2 |   TABLE ACCESS FULL|     ACCOUNT |  1651K|    25M|       | 33924   (1)| 00:06:48 |

|*  3 |   TABLE ACCESS FULL|     ACCOUNT |  6605K|    81M|       | 33915   (1)| 00:06:47 |

是以,有些東西還是大道至簡的道理，其實oracle内部也做了很多的優化，對于exists和in已經沒有那麼明顯的差别了。而且它會從資料的分布角度進行計劃的解析。從第一個執行計劃來看，資料庫分析的還是很合理的。