前言

主要從事網(wǎng)頁設計、PC網(wǎng)站建設（電腦版網(wǎng)站建設）、wap網(wǎng)站建設（手機版網(wǎng)站建設）、響應式網(wǎng)站建設、程序開發(fā)、微網(wǎng)站、小程序開發(fā)等，憑借多年來在互聯(lián)網(wǎng)的打拼，我們在互聯(lián)網(wǎng)網(wǎng)站建設行業(yè)積累了豐富的網(wǎng)站設計制作、網(wǎng)站制作、網(wǎng)絡營銷經驗，集策劃、開發(fā)、設計、營銷、管理等多方位專業(yè)化運作于一體，具備承接不同規(guī)模與類型的建設項目的能力。

今天老K繼續(xù)與大家分享第九期。

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

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

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

Part 1

擺問題、列信息

對于SQL tunning，老K上手最先關注的是SQL文本、執(zhí)行計劃和執(zhí)行統(tǒng)計信息，當然也不要忘了關注一下系統(tǒng)/數(shù)據(jù)庫版本。

1.1 環(huán)境介紹

操作系統(tǒng) AIX 6.1

數(shù)據(jù)庫 ORACLE 11.2.0.3 兩節(jié)點RAC

1.2 SQL文本

1.3 執(zhí)行計劃

1.4 執(zhí)行統(tǒng)計信息

信息都在這了，我們要關注些什么呢？老K的經驗是，先找特征，再根據(jù)不同的特征來進一步提取自己需要的信息。

Part 2

找特征、補信息

2.1 SQL文本特征

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

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

2.2 執(zhí)行計劃的特征

>> 該執(zhí)行計劃各過程均使用filter

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

2.3 補充信息收集之表統(tǒng)計信息

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

>> TARGET_BIG_TABLE表中記錄數(shù)約250W左右，統(tǒng)計信息估算POST_DATE過濾后返回623K條記錄，注意：這是預估值，實際值會隨著傳入的變量V1而變化。

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

2.4 補充信息收集之執(zhí)行計劃解讀

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

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

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

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

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

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

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

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

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

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

總的訪問過程=過濾過程的表訪問次數(shù) +修改過程的表訪問

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

2.5 執(zhí)行統(tǒng)計信息特征

>> SQL單次執(zhí)行平均邏輯讀為355,245,774（block數(shù)）

>> SQL單次執(zhí)行平均時間約2000秒

>> SQL單次平均修改記錄數(shù)約為0條

Part 3

思考吧DBA

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

3.1 老K的例行思考

>> 這個執(zhí)行計劃是否為當前SQL語句下最優(yōu)的執(zhí)行計劃？（選擇優(yōu)化目標）

>> 我們想要的執(zhí)行計劃是什么樣的？（確定優(yōu)化目標）

>> 我們怎么來讓SQL跑出我們想要的執(zhí)行計劃？（實現(xiàn)優(yōu)化目標）

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

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

3.2 老K的答案----不是最優(yōu)的計劃

老K先查看過該SQL的歷史執(zhí)行計劃，只有這一個，但這并不意味著著就是該SQL的最優(yōu)執(zhí)行計劃；

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

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

那么，新建索引，將S表的全掃都變?yōu)樗饕龗呙瑁@就是老K想要的執(zhí)行計劃嗎？

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

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

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

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

要回答這個問題，我們首先要思考為什么SQL當前沒有跑出我們想要的執(zhí)行計劃，是因為統(tǒng)計信息不準？索引設計不合理？還是列類型不匹配？

都不是！

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

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

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

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

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

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

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

Part 4

改寫吧DBA

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

4.1 改寫的花絮

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

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

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

>> 基于part1和part2基本相同，使用了nvl函數(shù)代替了原來的exists子句

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

>> 如果select部分不能查到記錄，則用原記錄自身進行更新（set chq_pay_name=chq_pay_name），更新前后該記錄的數(shù)據(jù)不變

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

我們再看改寫后的SQL執(zhí)行計劃：

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

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

4.2 這樣改寫真的好嗎？

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

總的訪問過程=過濾過程的表訪問次數(shù) +修改過程的表訪問

那么，在這個執(zhí)行計劃下，因為去掉了冗余的一部分，公式就變成了：

總的訪問過程=過濾過程的表訪問次數(shù)

實際上就可以理解為，SQL在修改數(shù)據(jù)的過程中可以重用過濾過程中生成的數(shù)據(jù)；

不過針對這個語句，我們從執(zhí)行統(tǒng)計信息里知道，每次語句執(zhí)行最終修改的數(shù)據(jù)量都非常少，也就是說這樣改寫所減少的“修改過程的表訪問”對整體執(zhí)行效率影響并不大。

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

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

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

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

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

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

4.3 繼續(xù)改寫

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

改寫前信息補充：

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

4.4 增加冗余

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

>> 增加d表和a表的關聯(lián)關系，其中jrnl_no列和acct_no列組合為T表的主鍵，其他冗余列的關聯(lián)主要為下一步繼續(xù)的改寫作鋪墊；

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

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

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

4.5 關鍵角色轉變：

基于第一步冗余等價關系，將exists子句中的所有a與b、c的關聯(lián)關系替換為d與b、c的關聯(lián)關系。

4.6 減少冗余：

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

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

>> 執(zhí)行計劃中b、d、c表使用hash join進行關聯(lián)

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

>> 最后VW_SQ_2和外層的T表使用NL的方式進行join，關聯(lián)字段為主鍵字段

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

總的訪問過程 = S表全掃 + T表全掃 + S表全掃 + VW_SQ_2記錄數(shù) *（1個T表主鍵索引塊 + 1個T表數(shù)據(jù)塊）

4.7 別忘了”set“

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

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

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

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

這里改寫后的執(zhí)行計劃與前面的update語句類似，老K也就不單獨列出分析了。

Part 5

最后的總覽

最后我們再來看看我們改寫后的語句及其執(zhí)行計劃：

語句如下：

最終的執(zhí)行計劃：

最終測試效果：

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

寫在最后

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

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

>> 針對CASE中的SQL如何通過添加索引來改善其執(zhí)行效率

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

>> 怎樣通過添加冗余關聯(lián)來引導數(shù)據(jù)庫生成我們想要的執(zhí)行計劃

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

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

編外：老K后來通過與應用開發(fā)團隊溝通了解文中SQL的業(yè)務特征后，再次結合其業(yè)務特征改寫了SQL，執(zhí)行效率再次得到了極大的提升，可見，在SQLtunning的過程中，了解業(yè)務確實是非常重要的一環(huán)。

新聞標題：SQL優(yōu)化之基于SQL特征的改寫
本文URL：http://www.chinadenli.net/article26/piepcg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導航、品牌網(wǎng)站制作、App開發(fā)、網(wǎng)站策劃、網(wǎng)站建設、網(wǎng)站內鏈

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)