第一章 技術人生系列 · 我和資料中心的故事（第九期）SQL優化之基於SQL特徵的改寫

中亦安圖 | 2016-03-21 22:04

前言

今天老K繼續與大家分享第九期。

週末老K宅在家觀戰了兩局精彩的“人狗”大戰。老K既算不上科技迷，也算不上圍棋迷，不過對此頗有感觸：阿爾法狗不過是通過左右互博的方式不斷學習圍棋，然而依賴其最優的學習演算法（學習方法）卻能再短短的數月之內達到人類圍棋水平的最頂端；而李世石在卻是依賴其已有的經驗結合人類特有的靈感下出“神之一手”，人類終究還是可以戰勝擁有超強計算能力的阿爾法狗。這些不禁讓老K想起了自己在工作過程中的最有藝術性的部分---“SQL tunning”，一方面要不斷學習積累運用不同的優化方法，同時在必要時多一分想象力和靈感，這樣面對不同的SQL問題，我們才能下出自己的“神之一手”。

好了，今天老K與大家分享的案例是SQL調優的案例，但老K更希望大家能從中體會到SQL tunning過程中的優化方法和思維方式，真正做到它山之石，可以攻玉。同時，大家如果覺得老K的方法還不錯，不妨輕輕的轉發一下，分享給身邊更多的ORACLE技術愛好者。

今天分析的問題是客戶DBA給過來的一條SQL語句，已經困擾其一段時間了，希望老K一起來分析解決。解決這個問題對老K來說並不是特別難，不過在這個問題的分析過程中，老K給出了幾種優化的方向，最終選擇了不論是對整個系統還是對該條SQL都可謂最佳的一種方式，最後在測試環境執行效果非常不錯。

Part 1

擺問題、列資訊

對於SQL tunning，老K上手最先關注的是SQL文字、執行計劃和執行統計資訊，當然也不要忘了關注一下系統/資料庫版本。

1.1 環境介紹

作業系統 AIX 6.1

資料庫 ORACLE 11.2.0.3 兩節點RAC

1.2 SQL文字

1.3 執行計劃

1.4 執行統計資訊

資訊都在這了，我們要關注些什麼呢？老K的經驗是，先找特徵，再根據不同的特徵來進一步提取自己需要的資訊。

Part 2

找特徵、補資訊

2.1 SQL文字特徵

>> exists子句 (part1)和update set部分(part2)的sql程式碼基本相同，如下圖；

>> part1部分中，標量子查詢的結果作為set列的目標值，說明從業務邏輯上能保證該部查詢返回記錄數最多為1；

2.2 執行計劃的特徵

>> 該執行計劃各過程均使用filter

>> 結合sql文字及predicate information可以看到，對目標表TARGET_BIG_TABLE經過濾條件POST_DATE=:V1後，返回記錄數預估為623K條。

2.3 補充資訊收集之表統計資訊

>> TARGET_BIG_TABLE大約2G大小，SOURCE_SMALL_TABLE大約3M 大小；

>> TARGET_BIG_TABLE表中記錄數約250W左右，統計資訊估算POST_DATE過濾後返回623K條記錄，注意：這是預估值，實際值會隨著傳入的變數V1而變化。

>> SOURCE_SMALL_TABLE表中記錄數約12W左右，ad02_acct_no列的選擇度比較高；

2.4 補充資訊收集之執行計劃解讀

注：TARGET_BIG_TABLE簡稱為T表 SOURCE_SMALL_TABLE 簡稱為S表

另注：解讀關鍵----理解執行計劃中的filter

>> 執行計劃分開成兩部分來看，其中ID2-7步表示對應SQL文字的part2部分，ID8-12步對應SQL文字的part1部分；

>> part2部分的過程：使用POST_DATE過濾T表，將過濾後的記錄迭代入EXISTS子查詢（T表的結果集此時作為變數傳入子查詢），在子查詢執行的過程中，如果前面的關聯條件符合，再次迭代入第二層子查詢（select max()部分）進行匹配；

>> part1部分的過程：針對ID2-7步過濾出的結果集，逐條update，而update的目標值，同樣是通過類似2-7步過程中的逐步迭代查詢而來；

>> 在各步驟單表訪問方式均為全表掃描；

>> 從執行計劃中可以看到，在第3步對錶T表進行過濾之後結果集估算為623K（rows列），其後對S表過濾後均為1；

>> 由此可以估算執行過程中表訪問的情況應為：（老K建議在本分享中記住下面的公式，暫且稱之為 “ 訪問公式 ” 吧）

過濾過程的表訪問=（T表全掃+ 623K 次 ×（S表全掃 +（0或者 1次）×（S表全掃）））

修改過程的表訪問=（需要修改的記錄數 ×（S表全掃 + （0或者 1次）×（S表全掃）））

總的訪問過程=過濾過程的表訪問次數 +修改過程的表訪問

注意：此處的（0或者 1次）×（S表全掃）表示的是第二層子查詢的情況，如果在第一層子查詢過程中關聯條件就不符合，則不再需要迭代入第二層，即0次S表全掃，否則即是1次S表全掃；所以過濾過程對S表最少需要做623K次全掃，最多需要做1246K次全掃；修改過程同理。

2.5 執行統計資訊特徵

>> SQL單次執行平均邏輯讀為355,245,774（block數）

>> SQL單次執行平均時間約2000秒

>> SQL單次平均修改記錄數約為0條

Part 3

思考吧DBA

好了，資訊收集完成了，進入老K的既定思考軌道，其實對於任何一個SQL tunning的問題，老K都會提出下面的三個問題，這個也不用例外；

3.1 老K的例行思考

>> 這個執行計劃是否為當前SQL語句下最優的執行計劃？（選擇優化目標）

>> 我們想要的執行計劃是什麼樣的？（確定優化目標）

>> 我們怎麼來讓SQL跑出我們想要的執行計劃？（實現優化目標）

如果可以，正在閱讀此文的你，也許也可以思考一下上面的三個問題，或者回憶一下當你面對SQL tunning的問題時你有沒有思考過這三個問題，亦或者你會思考/思考過什麼呢。

綜合前期的分析思考片刻之後，老K鄭重地給出了自己的答案：

3.2 老K的答案----不是最優的計劃

老K先檢視過該SQL的歷史執行計劃，只有這一個，但這並不意味著著就是該SQL的最優執行計劃；

在執行計劃解讀部分，老K給出了這個執行計劃的“訪問公式”，從公式中可以知道其實S表雖小，但其實際上是整個執行計劃的關鍵，整個過程中最多可能需要對S表進行1246K×2次訪問呢，那我們可不可以提高對S表的訪問效率呢？當然可以，從執行計劃中的估算可以知道對S表的訪問大約返回1-2條記錄（這裡老K還單獨驗證過），說明整體選擇度比較高，我們只有建立合適的索引，就可以就可以大大將提高S表的訪問效率。

我們簡單來估算一下使用索引的情況下的執行效率是怎樣的。原來對S表全掃所需的邏輯讀數為3M（表大小）÷8192=375次，使用索引後預估對S表一次訪問最多所需邏輯讀數為：（2次索引塊訪問 + 2次資料塊訪問）=4次；所以說，使用索引的邏輯讀約為使用全掃的的1%，估算建立索引後該語句單次執行平均邏輯讀約在350w左右。

那麼，新建索引，將S表的全掃都變為索引掃描，這就是老K想要的執行計劃嗎？

顯然不是，這樣的執行計劃只是原執行計劃的一個升級版而已，其過程還是一個迭代的過程，這樣執行的時間/消耗的時間基本都會隨著原計劃中第3步返回的資料量（還記得623K這個值嗎，就是它！它是可變的，可能隨著傳入的）變化而線性變化；所以這個執行計劃雖然較原執行計劃預計會有非常大的改善，但仍然不是老K想要的執行計劃。

3.3 老K的答案----想要的計劃

SQL文字告訴我們，其實SQL做的就是使用exists方式將T表和S表進行關聯更新，老K想要的執行計劃應該是使用NL或者hash join的方式來連線兩表，而不是使用filter迭代的方式，這樣就能保證SQL執行過程中只需要對T表和S表進行極少的一次或幾次掃描，從而降低SQL執行的邏輯讀。

3.4 老K的答案----如何生成漂亮的執行計劃

要回答這個問題，我們首先要思考為什麼SQL當前沒有跑出我們想要的執行計劃，是因為統計資訊不準？索引設計不合理？還是列型別不匹配？

都不是！

我們再次回到SQL語句本身，來看看SQL語句的特別之處。

在這裡，我們看到了問題的關鍵，正是因為最外層的T表與兩層子查詢均有關聯關係，導致ORACLE無法自動改寫SQL，最終生成執行計劃時無法使用T表與S表進行JOIN，只能生成使用filter方式的執行計劃。

所以，最終思考的結果已經出來：

>> 因為兩層子查詢的原因導致ORACLE無法使用JOIN的方式關聯T表和S表

>> 要想生成較好的執行計劃必須改寫語句

>> 改寫後的語句不應該存在類似的最外層表涉及第二層子查詢的情況

其中最後一點，指出了我們改寫的關鍵點。

Part 4

改寫吧DBA

依據老K的經驗，SQL語句的改寫通常要求改寫者對SQL涉及業務非常瞭解，通過業務特徵重構出合理的SQL語句，才能更好的做到既不改變SQL的業務邏輯，又有效提高SQL效能；不過針對這個SQL，我們已經知道了導致其執行計劃不優的根本原因，老K相信可以在不考慮業務特性的情況，利用資料庫的特性來進行有效的改寫。

4.1 改寫的花絮

基於SQL特性中，part1和part2基本相同的特性，老K先隨性的對SQL做了如下改寫（當然沒有針對前面提到的改寫關鍵點）；

這一改寫方式的幾個關鍵點：

>> 先把post_date欄位的過濾條件直接提取出來，與原邏輯一致

>> 基於part1和part2基本相同，使用了nvl函式代替了原來的exists子句

>> 如果select部分能查到記錄（類似原來的exists子句成立），則用查詢出的結果更新chq_pay_name欄位

>> 如果select部分不能查到記錄，則用原記錄自身進行更新（set chq_pay_name=chq_pay_name），更新前後該記錄的資料不變

以上幾點保證了改寫後的SQL與原SQL邏輯一致，不過有一點不一樣的非常值得注意，原SQL只修改極少的幾條記錄，新SQL卻修改了623K條記錄，只是其中絕大多數是冗餘的修改。

我們再看改寫後的SQL執行計劃：

與原SQL執行計劃類似，不過少了原執行計劃的part1部分。

新的執行計劃，老K又問了自己一句：

4.2 這樣改寫真的好嗎？

大家是否還記得原執行計劃解析過程中老K給出的“訪問公式”：

總的訪問過程=過濾過程的表訪問次數 +修改過程的表訪問

那麼，在這個執行計劃下，因為去掉了冗餘的一部分，公式就變成了：

總的訪問過程=過濾過程的表訪問次數

實際上就可以理解為，SQL在修改資料的過程中可以重用過濾過程中生成的資料；

不過針對這個語句，我們從執行統計資訊裡知道，每次語句執行最終修改的資料量都非常少，也就是說這樣改寫所減少的“修改過程的表訪問”對整體執行效率影響並不大。

這樣改寫會帶來什麼壞處嗎？

會！根本原因就在於上面提到的新SQL實際修改的記錄數是623K條：

>> 持有行鎖範圍變大，可能大量導致其他對該表進行DML操作的會話被阻塞

>> 如果修改列上有索引，索引維護的時間將大大增加，導致新SQL執行效率更低

綜上，針對這條SQL語句，這種改寫方式並不合適。

不過，如果原SQL在執行過程中修改的資料量接近623K條，那麼這種改寫方式的收益就要高非常多，而其帶來的壞處也就不復存在了，這種改寫方式只是不適合這種業務環境下（每次只修改極少幾條記錄），然而卻有一定的普遍性，所以老K也把這部分分享給大家，最重要的是解決問題過程中的思路和方法。

4.3 繼續改寫

前面我們已經分析出改寫的關鍵點：改寫後的語句不應該存在類似的最外層表涉及第二層子查詢的情況；下面我們就朝著這個目標去改寫我們的SQL語句。

改寫前資訊補充：

改寫思路在老K腦中醞釀好後，老K又補查了T表的資訊，確認T表存在主鍵約束，主鍵列為ACCT_NO和JRNL_NO;

4.4 增加冗餘

>> 在exists子句中增加一個冗餘的T表，別名為d

>> 增加d表和a表的關聯關係，其中jrnl_no列和acct_no列組合為T表的主鍵，其他冗餘列的關聯主要為下一步繼續的改寫作鋪墊；

>> 整個SQL語句中沒有使用d表與其他表進行關聯；

>> 由於d表和a表使用的是主鍵進行關聯，所以能確保對a表的每條記錄，都能從d中找到且只能找到一條記錄符合語句中的關聯關係；

綜上，可以知道上述增加冗餘完全不改變SQL的邏輯關係。

4.5 關鍵角色轉變：

基於第一步冗餘等價關係，將exists子句中的所有a與b、c的關聯關係替換為d與b、c的關聯關係。

4.6 減少冗餘：

因為主鍵a、d的主鍵列值相等，即可保證a、d的其他列值必然相等，所以a、d的關聯欄位只需要保留主鍵欄位即可（保留也是可以的，去掉顯得更簡潔）

以上一步一步的改寫保證了邏輯的一致性，同時實現了最外層的T表不再涉及第二層子查詢的關聯，我們可以推斷執行計劃應該與老K預期的相差不遠了：

>> 執行計劃中b、d、c表使用hash join進行關聯

>> join完成後通過一系列SORT/FILTER後形成結果集VW_SQ_2，其中這裡的filter部分為結果集內部的比較（即同一條記錄的不同列的比較），效率非常高

>> 最後VW_SQ_2和外層的T表使用NL的方式進行join，關聯欄位為主鍵欄位

執行計劃出來以後，我們來估算一下這個SQL在執行過程中的“訪問公式”：

總的訪問過程 = S表全掃 + T表全掃 + S表全掃 + VW_SQ_2記錄數 *（1個T表主鍵索引塊 + 1個T表資料塊）

4.7 別忘了”set“

原語句的part2部分修改的跟老K預期的差不多，原語句part1部分與part2部分一致，那麼我們簡單的修改part1部分成part2部分就可以了嗎？顯然不是！通常，使用merge into語句能很方便的改寫update語句，這裡我們更能利用原語句part1和part2一致的特性，改寫如下：

>> 將語句改寫為merge into的方式；

>> Merge的源與上一步改寫的exists子句中的內容一致，只是把與a的關聯關係提取到merge語句的on 部分；

>> 這樣改寫後SQL執行過程中也會鎖定需要修改的極少記錄。

這裡改寫後的執行計劃與前面的update語句類似，老K也就不單獨列出分析了。

Part 5

最後的總覽

最後我們再來看看我們改寫後的語句及其執行計劃：

語句如下：

最終的執行計劃：

最終測試效果：

在測試環境，改寫後的語句執行了兩次，每次平均修改7.5條記錄，耗時4s，邏輯讀3.4w；細心的讀者可能能從最終的執行計劃中看到，對T表的全表掃描也許可以避免等，由於篇幅原因以及測試環境的原因，老K沒有再在這裡深究，畢竟老K分享的是SQL tuning的方法，而如何避免全表掃描以及如何分析避免了全表掃描後對SQL執行效率提升的預估，相信讀者你一定已經學到了，不妨自己做一個估算。

寫在最後

讀到了最後，老K分享了什麼，我們不妨來仔細回憶一番。

>> SQL分析過程中如何通過執行計劃推算SQL執行的邏輯讀

>> 針對CASE中的SQL如何通過新增索引來改善其執行效率

>> 針對CASE中的SQL通過使用NVL的方式進行改寫，它在什麼場景下是合適的，什麼情況下是不合適的。

>> 怎樣通過新增冗餘關聯來引導資料庫生成我們想要的執行計劃

>> 怎樣使用merge語法來改寫update語句

最後，老K再一次強調，在SQLtunning的過程中最重要的是優化的思路和對問題的思考方式，希望聰明的讀者已從這次分享中得到啟示。

編外：老K後來通過與應用開發團隊溝通了解文中SQL的業務特徵後，再次結合其業務特徵改寫了SQL，執行效率再次得到了極大的提升，可見，在SQLtunning的過程中，瞭解業務確實是非常重要的一環。

 

About Me

....................................................................................................................................................

本文來自於微信公眾號轉載文章，若有侵權，請聯絡小麥苗及時刪除

ITPUB BLOG：http://blog.itpub.net/26736162

QQ：642808185 若加QQ請註明您所正在讀的文章標題

【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

....................................................................................................................................................

 

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