第一章  細緻入微 | 讓 SQL 優化再多飛一會兒

雲和恩墨 | 2016-04-12 20:54

懷曉明

雲和恩墨效能優化專家

本文來自於本週四雲和恩墨大講堂懷曉明老師的分享。

內容：作為 DevOps 的最佳落地方式之一的 SQL 稽核，如何才能做好？這是一件很有挑戰性的事情，他將通過兩個具體案例，來展現 SQL 稽核工作如何才能做得更好，更有價值。簡言之就是八個字——“細緻入微，方顯價值 ”。

我們都知道，細緻認真，可以將一件事情做得儘可能完美，在 SQL 稽核與優化中，同樣需要細緻認真，才能做好這項工作。細緻入微，透過現象看本質，讓 SQL 優化走的更深一些。

DevOps 理念逐漸為大眾認可，SQL 稽核作為其中協同開發和運營工作的一項服務，或說一種工作手段，也顯得異常重要。因為 SQL 稽核的價值已經深入人心——SQL 稽核可以提前消除資料庫隱患，為未來系統更穩定的執行打下良好的基礎。

今天給大家分享兩個有點點特別的 SQL 優化案例，說是特別，是因為它們的優化並不僅僅是表面上的 SQL 問題，還能夠發掘出更深入的問題根源，讓我們的 SQL 優化旅程開始飛吧......

不僅僅是 NULL 的問題

1問題發現

在某系統中，通過 Z3，我們發現一條違反稽核規則的語句：

（注：Z3 是雲和恩墨自主研發的 SQL 稽核工具，試用資訊見文末。）

delete from publckrec where RecKey = null

該語句非常簡單，違反的是“索引全掃”這一條規則。

2問題分析與處理

首先，首先我們看一下執行計劃：

我們需要知道的是，Oracle 在執行原 SQL 的時候，並不會真正去做全索引掃描。

為什麼呢？

因為執行計劃中的第二步的 filter，其斷言是 NULL IS NOT NULL，這是永遠不會成立的條件，所以 Oracle 是不會去執行全索引掃描。

回到這個 SQL 的書寫上，如果你對 Oracle 的 NULL 有一定的瞭解，那麼你一定能知道，該語句的正確的寫法應該如下：

delete from publckrec where RecKey IS null

這是因為在 Oracle 中，對 NULL 的比較必須使用 IS NULL 或者 IS NOT NULL.

有關 NULL 的更多有趣的事兒和更深入的解析，推薦大家閱讀楊廷琨老師的文章：

Oracle 中的 NULL 值解析

甚至還有人利用 NULL 來鑽漏洞：

據說他姓氏改成 Null 之後，酒店/租車/購物各種不花錢了（http://www.cnbeta.com/articles/488673.htm）

對於普通工程師而言，事情到這裡就結束了。

然而，作為一名合格的 SQL 稽核工程師，我們應當能發現執行計劃第三步用到的索引的名字並不是使用者自定義的名字，而是系統自動生成的約束的名字。

然而，作為一名合格的 SQL 稽核工程師，我們應當能發現執行計劃第三步用到的索引的名字並不是使用者自定義的名字，而是系統自動生成的約束的名字。

然而，作為一名合格的 SQL 稽核工程師，我們應當能發現執行計劃第三步用到的索引的名字並不是使用者自定義的名字，而是系統自動生成的約束的名字。

以上重複的都是重點！

而 Oracle 只會自動為主鍵和唯一鍵這兩種約束新增同名的索引，如果這是唯一鍵約束，那麼改寫就是最終的方案，如果是主鍵約束，那該語句就是一個無用的語句。

通過 Z3，我們發現，這是一個主鍵約束：

因為該表的 RecKey 欄位為主鍵，不會存在NULL值，所以從資料庫的角度看，此 SQL 總是刪除0條記錄，實質上是一為無用語句。

然而，事情至此並未結束，我們還要問，既然無用，為什麼還會有此語句被稽核到？這連單元測試都通不過，開發不是應該在開發階段就消滅掉這種錯誤的麼？

當問題反饋給開發後，得到開發的反饋是前臺 JavaScript 有 bug，導致傳遞的鍵值變成了 null，所以出現瞭如上問題。

所以最終的解決方案並不是不執行該 SQL，而是修正前臺 JavaScript 的 bug，並採用繫結變數的方式編碼。

意想不到的優化方式

1問題發現

在某電信客戶，通過 Top SQL 抓取，發現一消耗資源過高的問題語句如下：

SELECT *

FROM

(SELECT *

FROM

(SELECTj.order_id,

j.jms_xml

FROMjms_cent j,

om_order oo

WHEREoo.service_id IN (355, 597)

ANDj.order_id = oo.id

ANDj.state = '-1'

ORDER BYj.in_time ASC

)

WHERErownum <= (10 -

(SELECTCOUNT(1)

FROM

(SELECT j.order_id,

j.jms_xml

FROMjms_cent j,

om_order oo

WHEREoo.service_id NOT IN (355, 597)

ANDj.order_id = oo.id

ANDj.state = '-1'

ANDrownum <= 10

ORDERBY j.in_time ASC

)

))

)

UNION

SELECT *

FROM

(SELECT *

FROM

(SELECTj.order_id,

j.jms_xml

FROMjms_cent j,

om_order oo

WHEREoo.service_id NOT IN (355, 597)

ANDj.order_id = oo.id

ANDj.state = '-1'

ORDER BYj.in_time ASC

)

WHERErownum <=

(SELECTCOUNT(1)

FROM

(SELECT j.order_id,

j.jms_xml

FROMjms_cent j,

om_order oo

WHEREoo.service_id NOT IN (355, 597)

ANDj.order_id = oo.id

ANDj.state = '-1'

ANDrownum <= 10

ORDERBY j.in_time ASC

)

)

)

在生產環境上，該 SQL 平均每次消耗10萬邏輯讀：

其執行計劃如下圖（測試環境，生產環境的執行計劃與之類似）：

2問題分析與處理

如果你有一定的開發經驗，當看到該 SQL 及其執行計劃，你的第一反應，應該是該 SQL 存在寫法不當的問題（ORDER BY 與 rownum 用法不當），並且可以通過 WITH 來改寫 SQL，改寫後的 SQL 邏輯讀，能降到原先的1/4左右。

但云和恩墨為這個客戶做了一段時間的 SQL 稽核與優化，我們已經熟悉了開發商的開發風格。

該系統的繫結變數編碼其實一直做得不錯，為什麼出現了 oo.service_id NOT IN(355, 597)和 j.state = '-1' 這樣沒有用繫結變數方式編碼的程式碼呢？

實際上，jms_cent.state 上的資料分佈非常不均勻：

經過與開發溝通，確認 state 為'-1'的資料的確總是很少數，並且根據業務，該語句總是要查 state 為'-1'的值。然而該列上並沒有索引，於是我們在測試環境上我們新建一個索引：

create index idx_jms_state_new on jms_cent(state);

建立索引後，再看此時 SQL 的執行計劃：

可見建立索引後 cost 大幅下降。

如果我們沒有深入分析，而是直接就上手改寫 SQL，cost 大約會在10000左右，和現在加索引後的22相比，還是天壤之別的差距。

所以，在 SQL 稽核與優化工作中，找對改進或優化的方向是更為重要的事。

而我更想說的是，事情到此其實才剛開了一個頭，精彩在後面……

同時我們注意到，該表上建立了好幾個索引，其中一個索引為：

索引列只有一列，但命名是 SYS_NC00007$，這意味著什麼呢？

答案是：'SYS_NC' 的字首暗示著這很可能是一個函式索引。

使用 DBMS_METADATA.GET_DDL 獲得該索引的建立語句核心部分為：

CREATE INDEX IND_JMS_CENT ON JSM_CENT('STATE');

此刻，真相大白了，原來並不是該列上沒有索引，而是當初建立索引時由於一個疏忽，將一個普通索引建立成了一個函式索引！！！

所以我們的優化改進方案，即實際應執行的指令碼為：

DROP INDEX IND_JMS_CENT;

CREATE INDEX IND_JMS_CENT ON JSM_CENT(STATE);

3問題延伸處理

案例2至上一節就完滿結束了。

但作為一家專業的 SQL 稽核與優化服務提供商，我們還會繼續做一項檢查——查詢庫內是否還有類似的將普通索引錯建成函式索引的情況。

通過對庫內所有索引進行檢查，並未發現其他索引有類似問題存在，所以最終在正式庫上執行的優化改進指令碼就是上述指令碼。

總結

今天，通過兩個很有意思的案例，來和大家分享一下 SQL 稽核工作要如何才能做得更好。

要做好這項工作，細緻認真是必不可少的，然而工具也是極其重要的，所謂“工欲善其事，必先利其器”，有了 Z3 的幫助，大大提高了我們做 SQL 稽核與優化服務的效率。

SQL 稽核工作如果需要做到完美，那並不是一項簡單的事。我們需要“由點及面”，找出問題的真正原因，才能真正將這項工作做好。

做好 SQL 稽核，可以讓技術和運營團隊形成更緊密的協作關係，有助於提高應用系統的穩定和效率，保障業務順暢進行，而這也是 DevOps 的落地的最佳方式之一。

附錄

1分享人簡介

懷曉明，ID 是 lastwinner，全國 SQL 大賽的評委。目前主要負責 SQL 稽核與優化相關的業務。

2Z3 簡介

【雲和恩墨業務介紹】之 SQL 稽核服務

Z3 試用申請：

marketing@enmotech.com

010-59003186

3課堂提問

1. 加個引號有什麼作用？（指的是函式索引）

答：引號加上後，常規的一個列就變成了一個字串常量，相應的索引就是一個常量索引。而對 Oracle來說，常量索引是函式索引的一種，所以你在 Oracle 裡看到該索引就是一個函式索引。

2. 類似問題：

? 問題：請問建索引的時候把state欄位加上單引號的效果是什麼，相當於所有欄位的指都to_char了嗎？

? 問題：請問建索引的時候把state欄位加上單引號的效果是什麼，相當於所有欄位的指都 to_char 了嗎？

答：不是，相當於是索引了一個常量字串——'STATE'，可認為是一個常量索引，其實並沒有什麼實際作用。

3. 第二個 sql 能否採用臨時表，因為大部分查詢都是重複的?

答：用 with 生成的臨時表，是可以的，但是我在分享中也提到了，用了索引，是最有效的手段。如果再加上改寫，效率還會再提高，但提高不會太多了。

4. 問題：

? Z3 內建的規則是通用的嗎，區分 oltp 和 olap 嗎？

? 在 sql 稽核方面，基於 oltp 和 olap 我們需要注意些什麼呢？

? 測試環境與生產環境資料量不是一個數量級的時候，Z3 的內建規則稽核的問題 sql 有差異嗎？

? 在 olap 中經常用到並行，對於在 olap 中我們使用並行需要注意些什麼呢？

答：

? Z3 內建的規則是可以修改閾值的，OLAP 和 OLTP 可以分別選取不同的規則並設定不同的閾值進行稽核

? 在 SQL 稽核層面，OLAP 和 OLTP 的差異不是很大

? 因為 SQL 稽核的規則的閾值可以配置，比方說全表掃描，在測試環境上我可以設定只要大於 8M 的表上發生了全表掃描，就認為觸發了規則，而在正式環境上，我會將該值設定為 80M 甚至 200M（根據需要）

? OLAP 中的並行一般問題不大，最重要的是要避免併發的並行

5. jms_cent.state 上的資料分佈非常不均勻，直方圖不就是解決資料分佈不均勻的情況嗎？此處為什麼還是新建索引呢？

答：光有直方圖沒有索引是沒用的。

6.（追問）直方圖的使用是建立在有索引的基礎上的，可以這樣理解吧？

答：資料分佈不均勻的列上若建立了索引，當列上存在直方圖時，Oracle 可以更精準的根據條件值來判斷到底該不該走這個索引。

7. 表是否必須建主鍵？建與不建有何深層次影響？

答：大部分情況下，表是應當建主鍵的。當然，也有情況可以允許不建主鍵，比如多表關聯查詢的結果暫存在一張中間表中，這樣的情況就可以不為這張中間表建主鍵。不建主鍵就可能導致表內資料重複，一旦出現重複就可能導致業務出錯。

8. 第二個案例中的 SQL 是要實現什麼目的呢？UNION 的前面當滿足條件的記錄小於等於5條時，就返回這5條，若大於5條，則返回 10-N 條。比如有8條記錄時，就只返回前兩條。而 UNION 的後面則返回最前面的N條，（N 小於等10）。由於 UNION 有去重的作用，所以，最後返回的最終結果似乎是：只返回滿足條件的前10條？

答：這個 SQL 比較繞，一兩句話描述不清楚。看這個 SQL，你需要注意條件裡有 in 和 not in ，還有這個 SQL 平均每次返回不到3條記錄。

9. 'state' 列加單引號就會成函式索引嗎？

答：你的問題應該是“ state 列加單引號就會成函式索引嗎？”，回答是，是的。

10. 如果產品上線或者要上新功能，有大量的 sql 需要 review，如果沒有像恩墨 Z3 這樣的神器，我們 dba 應該怎麼操作，難道只能艱難的看開發的 xml 檔案嗎？

答：如果你們沒有使用 Z3 這樣的產品，那麼 SQL 稽核的工作會非常辛苦，作為一名 DBA，你大部分精力就會消耗在這些可用工具幫你做的事情上，而你的智慧和精力實際應該用在更有價值的地方，比如根據 Z3 審查出的疑似問題的地方，依據你對業務的瞭解進行確認。

另外需要提醒的是，XML 的配置有很多種，有的是直接寫 SQL，有的是將一個 SQL 分成可拼接的幾部分，如果遇到後者，那會很麻煩的。

11. 'SYS_NC' 的字首暗示著這很可能是一個函式索引。這個字首有幾種？

答：這個字首有幾種我沒細研究過，抱歉現在無法給你一個滿意的答覆。

12. 案例二隻查狀態為-1的情況麼？

答：是的，經過與開發確認，這個 SQL 就總是查 state 為-1的資料，這是業務決定的。

13. 這個 SQL 稽核軟體 Z3 可以共享一下用嗎？

答：試用需要先和我公司聯絡。不過，我們很快就會放出一版雲上的 Z3，供大家體驗。

Z3 試用申請：

marketing@enmotech.com

010-59003186

14. 類似這樣巨大的執行計劃應該怎麼去分析，是按規則一行一行的看？還是隻看關鍵的位置？

答：這需要就事論事，有時候，優化 SQL 不僅僅是要執行計劃，而有時候，優化 SQL 又根本不需要看執行計劃。這類案例我以後會和大家分享。

對於複雜巨大的執行計劃，首先要打好基本功，知道各種執行計劃是什麼含義，在什麼場景下適用於什麼執行計劃，其次是需要多加鍛鍊，經驗了多了之後，面對一個執行計劃你能很快就看出問題所在。

15. 想問一下建索引時加一個 desc 是什麼作用，我上網查說是降序索引，能不能講講這種索引的應用場合呢？

答：適用於 order by ... desc 的場景。

16. 避免併發的並行-例如一個排程裡面包含了100 條 sql，假如每條 sql 裡面都使用了並行，這就是併發的並行對吧，如果這些併發的 sql 裡面的並行度之和沒有超過我的並行 server 最大值呢，也不建議用嗎，再如果我的併發 sql 失敗了造成大量的並行事物回滾，如何避免這種情況呢，設定序列回滾貌似也是有代價的。

答：如果是在一個排程裡，在沒有開啟多執行緒的情況下，應該是序列的而不是並行的。

17. 做好審計，需要關注哪幾方面內容？

答：做好 SQL 稽核，需要在技術、工具、業務等多方面都要做到位，才能做好 SQL 稽核。

18. 是否可以這樣理解，對於一個嚴重分佈不均勻的列（類似例子中的 state 列），如果某個或某幾個值非常少，而 SQL 中又經常對這些稀少值進行查詢，也是可以建索引來提高查詢效能的。

答：是的，這樣理解完全沒問題。

19. 如果表的列上建了複合索引，又建了普通單列索引，是優先使用複合索引嗎？這種一個列上建了多種索引會有問題嗎？

答：這個問題不好具體回答，Oracle 的規則是選擇代價小的索引，而代價大小，是要先確定查詢條件才能定的。一個列有可能被多索引所選用，這是很正常的現象，設計得當是不會有問題的。

20. 建索引的時候寫法（"status"）和（'status'）是不是一樣會有函式轉換？都轉成字元型別？

答：不是轉換，至於單引號和雙引號的區別，你自己試驗一下就知道了，還是挺有意思的，我這裡就不點破了。

21. 請問函式索引是如何提高函式的執行速度的，是什麼原理？和普通列上的索引機制一樣麼？

答：原理是一樣的，都是 B-Tree 索引。

22. 一個十萬和百萬級別的表關聯查詢，關聯欄位都見了索引，檢視執行計劃只走了百萬級別表的索引，這是不是已經是最優的執行計劃了。

答：沒有具體的 SQL、執行計劃和索引情況，那是不好說的。

23.（追問）select * from a，b where a.id=b.ida 表十萬級，b表百萬級，兩個表的 ID 都是 B-tree 索引，檢視計劃只走了百萬的b表的 ID 索引，就是這種簡單場景。

答：那有可能，因為你要查表裡的所有欄位，走完索引再回表代價可能會太大，所以還不如將小表全表掃一遍。

2016 ACOUG Asia Tour 4月份合肥、南京、濟南和上海站火熱報名中，掃描下方二維碼填寫報名資訊，或者複製連結（https://jinshuju.net/f/Lqq7H4）到瀏覽器開啟。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2084979/，如需轉載，請註明出處，否則將追究法律責任。