一、SQL ：一種熟悉又陌生的程式語言

這裡有幾個關鍵詞；“熟悉”、“陌生”、“程式語言”。

說它“熟悉”，是因為它是DBA和廣大開發人員，運算元據庫的主要手段，幾乎每天都在使用。說它“陌生”，是很多人只是簡單的使用它，至於它是怎麼工作的？如何才能讓它更高效的工作？卻從來沒有考慮過。

這裡把SQL歸結為一種“程式語言”，可能跟很多人對它的認知不同。讓我們看看它的簡單定義（以下內容摘自百度百科）

結構化查詢語言(Structured Query Language)，簡稱SQL，是一種特殊目的的程式語言，是一種資料庫查詢和程式設計語言，用於存取資料以及查詢、更新和管理關聯式資料庫系統。結構化查詢語言是高階的非過程化程式語言，允許使用者在高層資料結構上工作。它不要求使用者指定對資料的存放方法，也不需要使用者瞭解具體的資料存放方式，所以具有完全不同底層結構的不同資料庫系統, 可以使用相同的結構化查詢語言作為資料輸入與管理的介面。結構化查詢語言語句可以巢狀，這使它具有極大的靈活性和強大的功能。

總結一句話，SQL是一種非過程化的的程式語言，可通過它去訪問關係型資料庫系統。

二、你真的瞭解“SQL”嗎？

下面我會通過一個小例子，看看大家是否真正瞭解SQL。

這是一個很簡單的示例，是關於SQL語句執行順序的。這裡將一個普通的SELECT語句，拆分為三個子句。那麼在實際的執行過程中，是按照什麼順序處理的呢？這裡有A-F六個選項，大家可以思考選擇一下…

最終的答案是D，即按照先執行FROM子句，然後WHERE子句，最後是SELECT部分。

針對上面的示例，讓我們真實構造一個場景，通過檢視執行計劃看看是否按照我們選擇的順序執行的。關於執行計劃的判讀，我後面會專門談到。這裡我先解釋一下整個執行過程。

• 第一步，是按照全表掃描的方式訪問了物件表(EMP)。對應於語句中的FROM部分。

• 第二步，是對提取出的結果集進行了過濾(filter部分)，即將滿足條件的記錄篩選出來。對應於語句中的WHERE部分。

• 第三步，是對滿足條件的記錄進行欄位投射，即將需要顯示的欄位提取出來。對應於語句中的SELECT部分。

這是一個詳細的SQL各部分執行順序的說明。

通過對執行順序的理解，可以為我們未來的優化工作帶來很大幫助。一個很淺顯的認識就是，優化動作越靠前越好。

三、SQL現在是否仍然重要？

這裡引入了一個新的問題，在現有階段SQL語言是否還重要？

之所以引入這一話題，是因為隨著NOSQL、NEWSQL、BIGDATA等技術逐步成熟推廣，“SQL語言在現階段已經變得不那麼重要”成為一些人的觀點。那實際情況又是如何呢？

讓我們先來看一張經典的圖。圖中描述了傳統SMP架構的關係型資料庫、MPP架構的NEWSQL、MPP架構的NoSQL不同方案的適用場景對比。

從上面的“資料價值密度、實時性”來看，傳統關係型資料庫適合於價值密度更高、實時性要求更高的場景（這也就不難理解類似賬戶、金額類資訊都是儲存在傳統關係型資料庫中）；MPP架構的NewSQL次之，MPP架構的NoSQL更適合於低價值、實時性要求不高的場景。

從下面的“資料規模”來看，傳統關係型資料庫適合儲存的大小限制在TB級別，而後兩者可在更大尺度上(PB、EB)級儲存資料。

從下面的“典型場景”來看，傳統關係型資料庫適合於OLTP線上交易系統；MPP架構的NewSQL適合於OLAP線上分析系統；而NoSQL的使用場景較多(利於KV型需求、資料探勘等均可以考慮)。

最後從“資料特徵”來看，前兩者適合於儲存結構化資料，後者更適合於半結構化、乃至非結構化資料的儲存。

歸納一下，不同技術有其各自特點，不存在誰代替誰的問題。傳統關係型資料庫有其自身鮮明特點，在某些場合依然是不二選擇。而作為其主要互動語言，SQL必然長期存在發展下去。

我們再來對比一下傳統資料庫與大資料技術。從資料量、增長型、多樣化、價值等維度對比兩種技術，各自有其適用場景。

對於大資料領域而言，各種技術層出不窮。但對於廣大使用者來說，往往會存在一定的使用門檻，因此現在的一種趨勢就是在大資料領域也引入“類SQL”，以類似SQL的方式訪問資料。這對於廣大使用者來說，無疑大大降低了使用門檻。

解答一些疑問：

NoSQL、NewSQL已經超越了傳統資料庫，SQL沒有了用武之地！

各種技術有著各自適合的不同場景，不能一概而論。SQL語言作為關係型資料庫的主要訪問方式，依然有其用武之地。

以後都是雲時代了，誰還用關係型資料庫！

對於價值密度高，嚴格一致性的場景，仍然適合採用關係型資料庫作為解決方案。

我程式設計都是用OR Mapping工具，從不需要寫SQL！

的確，引入OR Mapping工具大大提高了生產效率，但是它的副作用也很明顯，那就是對語句的執行效率失去了控制。很多低效的語句，往往是通過工具直接生成的。這也是為什麼有的Mapping工具還提供了原始的SQL介面，用來保證關鍵語句的執行效率。

大資料時代，我們都用Hadoop、Spark了，不用寫SQL啦！

無論是使用Hadoop、Spark都是可以通過編寫程式完成資料分析的，但其生產效率往往很低。這也是為什麼產生了Hive 、Spark SQL等“類SQL”的解決方案來提高生產效率。

資料庫處理能力很強，不用太在意SQL效能！

的確，隨著多核CPU、大記憶體、快閃記憶體等硬體技術的發展，資料庫的處理能力較以前有了很大的增強。但是SQL的效能依然很重要。後面我們可以看到，一個簡單SQL語句就可以輕易地搞垮一個資料庫。

SQL優化，找DBA就行了，我就不用學了!

SQL優化是DBA的職責範疇，但對於開發人員來講，更應該對自己的程式碼負責。如果能在開發階段就注重SQL質量，會避免很多低階問題。

我只是個運維DBA，SQL優化我不行！

DBA的發展可分為“運維DBA->開發DBA->資料架構師…”。如果只能完成資料庫的運維類工作，無疑是技能的欠缺，也是對各人未來發展不利。況且，隨著Paas雲的逐步推廣，對於資料庫的運維需求越來越少，對於優化、設計、架構的要求越來越多。因此，SQL優化是每個DBA必須掌握的技能。

現在優化有工具了，很簡單的！

的確現在有些工具可以為我們減少些優化分析工作，會自動給出一些優化建議。但是，作為DBA來講，不僅要知其然，還要知其所以然。況且，資料庫優化器本身就是一個非常複雜的元件，很難做到完全無誤的優化，這就需要人工的介入，分析。

優化不就是加索引嘛，這有啥！

的確，加索引是一個非常常用的優化手段，但其不是唯一的。且很多情況下，加了索引可能導致效能更差。後面，會有一個案例說明。

四、SQL仍然很重要！

我們通過一個示例，說明一下理解SQL執行原理仍然很重要。

這是我在生產環境碰到的一個真實案例。Oracle資料庫環境，兩個表做關聯。執行計劃觸目驚心，優化器評估返回的資料量為3505T條記錄，計劃返回量127P位元組，總成本9890G，返回時間999:59:59。

從執行計劃中可見，兩表關聯使用了笛卡爾積的關聯方式。我們知道笛卡爾連線是指在兩表連線沒有任何連線條件的情況。一般情況下應儘量避免笛卡爾積，除非某些特殊場合。否則再強大的資料庫，也無法處理。這是一個典型的多表關聯缺乏連線條件，導致笛卡爾積，引發效能問題的案例。

從案例本身來講，並沒有什麼特別之處，不過是開發人員疏忽，導致了一條質量很差的SQL。但從更深層次來講，這個案例可以給我們帶來如下啟示：

• 開發人員的一個疏忽，造成了嚴重的後果，原來資料庫竟是如此的脆弱。需要對資料庫保持一種"敬畏"之心。

• 電腦不是人腦，它不知道你的需求是什麼，只能用寫好的邏輯進行處理。

• 不要去責怪開發人員，誰都會犯錯誤，關鍵是如何從制度上保證不再發生類似的問題。

五、SQL優化法則

下面我們來看看常見的優化法則。這裡所說的優化法則，其實是指可以從那些角度去考慮SQL優化的問題。可以有很多種方式去看待它。下面列舉一二。

這裡來自阿里-葉正盛的一篇部落格裡的一張圖，相信很多人都看過。這裡提出了經典的漏斗優化法則，高度是指我們投入的資源，寬度是指可能實現的收益。從圖中可見，“減少資料訪問”是投入資源最少，而收益較多的方式；“增加硬體資源”是相對投入資源最多，而收益較少的一種方式。受時間所限，這裡不展開說明了。

這是我總結的一個優化法則，簡稱為“DoDo”法則。

第一條，“Do Less or not do!”翻譯過來，就是儘量讓資料庫少做工作、甚至不做工作。

怎麼樣來理解少做工作呢？比如建立索引往往可以提高訪問效率，其原理就是將原來的表掃描轉換為索引掃描，通過一個有序的結構，只需要少量的IO訪問就可以得到相應的資料，因此效率才比較高。這就可以歸納為少做工作。

怎麼樣來理解不做工作呢？比如在系統設計中常見的快取設計，很多是將原來需要訪問資料庫的情況，改為訪問快取即可。這樣既提高了訪問效率，又減少了資料庫的壓力。從資料庫角度來說，這就是典型的不做工作。

第二條，“If must do,do it fast!”翻譯過來，如果資料庫必須做這件事件，那麼請儘快做完它。

怎麼樣來理解這句話呢？比如資料庫裡常見的並行操作，就是通過引入多程式來加速原來的執行過程。加速處理過程，可以少佔用相關資源，提高系統整體吞吐量。

六、SQL 執行過程

SQL的執行過程比較複雜，不同資料庫有一定差異。下面介紹以兩種主流的資料庫（Oracle、MySQL）介紹一下。

• 使用者提交了一條SQL語句

• 資料庫按照SQL語句的字面值計算出一個HASH值

• 根據HASH值，判斷一下在資料庫緩衝區中是否存在此SQL的執行計劃。

• 如果不存在，則需要生成一個執行計劃(硬解析過程)，然後將結果存入緩衝區。

• 如果存在的話，判斷是否為相同SQL（同樣HASH值的語句，可能字元不相同；即使完全相同，也可能代表不同的語句。這塊不展開說了）

• 確認是同一條SQL語句，則從緩衝區中取出執行計劃。

• 將執行計劃，交給執行器執行。

• 結果返回給客戶端。

• 客戶提交一條語句

• 現在查詢快取檢視是否存在對應的快取資料，如有則直接返回(一般有的可能性極小，因此一般建議關閉查詢快取)。

• 交給解析器處理，解析器會將提交的語句生成一個解析樹。

• 前處理器會處理解析樹，形成新的解析樹。這一階段存在一些SQL改寫的過程。

• 改寫後的解析樹提交給查詢優化器。查詢優化器生成執行計劃。

• 執行計劃交由執行引擎呼叫儲存引擎介面，完成執行過程。這裡要注意，MySQL的Server層和Engine層是分離的。

• 最終的結果有執行引擎返回給客戶端，如果開啟查詢快取的話，則會快取。

七、SQL優化器

在上面的執行過程描述中，多次提高了優化器。它也是資料庫中最核心的元件。下面我們來介紹一下優化器。

上面是我對優化器的一些認識。優化器是資料庫的精華所在，值得DBA去認真研究。但是遺憾的是，資料庫對這方面的開放程度並不夠。（相對來說，Oracle還是做的不錯的）

這裡我們看到的MySQL的優化器的工作過程，大致經歷瞭如下處理：

• 詞法分析、語法分析、語義檢查
• 預處理階段(查詢改寫等)
• 查詢優化階段(可詳細劃分為邏輯優化、物理優化兩部分)
• 查詢優化器優化依據，來自於代價估算器估算結果(它會呼叫統計資訊作為計算依據)
• 交由執行器執行

此圖是DBAplus社群MySQL原創專家李海翔對比不同資料庫優化器技術所總結的。從這裡可以看出：

• 不同資料庫的實現層次不同，有些支援、有些不支援

• 即使支援，其實現原理也差異很大

• 這只是列出了一小部分優化技術

• 以上對比，也可以解釋不同資料庫對同樣語句的行為不同。下面會有一個示例說明

八、SQL 執行計劃

看懂執行計劃是DBA優化的前提之一，它為我們開啟一扇通往資料庫內部的視窗。但是很遺憾，從沒有一本書叫做“如何看懂執行計劃”，這裡的情況非常複雜，很多是需要DBA常年積累而成。

這是Oracle執行計劃簡單的示例，說明了執行計劃的大致內容。

九、案例分享

前面講了很多理論內容，下面通過幾個案例說明一下。方便大家對前面內容的理解。

案例1：資料庫對比

第一個例子，是一個優化器行為的對比案例。示例對比了三種資料庫（四種版本）對於同樣語句的行為。通過這個例子，大家可以瞭解，不同資料庫（乃至不同版本）優化器的行為不同。對於資料庫選型、資料庫升級等工作，要做到充分的評估測試，也正是出於此目的。

簡單構造了兩張測試表，主要注意的是前一個欄位是包含空值的。

第一種情況，是對於IN子查詢的處理。對於Oracle來說，10g、11g行為相同，這裡就列了一個。

對於這樣的一個例子，不同資料庫已經表現出不同的差異。Oracle和PG的行為類似，MySQL由於不支援雜湊連線，因此採用了其他處理方式。具體的技術細節，這裡不展開說明了。

第二種情況，是對於NOT IN子查詢的處理。這種情況下，Oracle的不同版本、PG和MySQL表現出不同的行為。從上面例子可以看出，11g的優化器在處理此種情況是更加智慧一些。

案例2：解決“ERP匯單慢”問題

這裡我構造了類似的結構，模擬了上線的情況。

示例是一個關聯子查詢，其核心部分是轉化為一個表關聯，並使用了巢狀迴圈的一個變體-Filter實現關聯方式。顯然，如果外層表過大或內層探查效率過低，其執行效率可想而知。通常來說，兩表關聯，巢狀迴圈是最後的一種選擇，如果能使用其他方式（例如HASH JOIN、SORT MERGE）可能會帶來更好的效果。

這裡優化器沒有選擇更優的計劃，是優化器的Bug？還是功能所限？可通過人工手段干預，看看是否能達到意向不到的效果。

引入了一個Hint-unnest，主動實現子查詢的解巢狀。將子查詢部分提前，讓優化器有了更多的選擇。從執行計劃來看，優化器生成了一個內聯檢視，然後跟外部表實現了一個雜湊連線，整體效率大大提高。

這個示例說明，優化器的功能還是有所侷限。在某些場合，可以人工干預語句的執行，提升整體執行效率。

案例3：處理“ERP清理資料”問題

下面這個示例，是因為結構設計不良導致的問題。

在日常的優化中，我們往往遵循著“語句級、物件級、架構級、業務級”的順序考慮優化策略。但在專案需求、設計階段，是按照反向的順序進行。後者的影響力要遠遠大於前者。一個糟糕的物件結構設計，可能會帶來一系列SQL的問題。示例中，就是這樣的一個問題。

這是某公司後臺的ERP系統，系統已經上線執行了10多年。隨著時間的推移，累積的資料量越來越大。公司計劃針對部分大表進行資料清理。在DBA對某個大表進行清理中，出現了問題。這個表本身有數百G，按照指定的清理規則只需要根據主鍵欄位範圍(>=)選擇出一定比例(不超過10%)的資料進行清理即可。但在實際使用中發現，該SQL的是全表掃描，執行時間大大超出預期時間。DBA嘗試使用強制指定索引方式清理資料，依然無效。

這套ERP系統歷史很久遠，相關資訊已經找不到了。只能從純資料庫的角度進行分析，這是一個普通表（非分割槽表）按照主鍵欄位的範圍查詢一批記錄進行清理。按照正常理解，執行索引範圍掃描應該是效率較高的一種處理方式，但實際情況確實全表掃描。進一步分析發現，該表的主鍵是沒有業務含義的，僅僅是自增長的資料，其來源是一個序列。但奇怪的是，這個主鍵欄位的型別是變長文字型別，而不是通常的數字型別。現在已經無從考證，當初定義該欄位型別的依據，但實驗表明正是這個欄位的型別“異常”，導致了錯誤的執行路徑。

下面構造了一個測試環境。

可以很好的復現案例的問題。選擇少範圍資料，文字方式依然走的全表掃描，數字方式走的索引掃描。效率高低，顯而易見。

大家頭腦中可以構想出一棵索引樹結構，對於字串來說，這個有序的結構該如何存放？是與你預期一樣的嗎？

知道了問題所在，該如何處理呢？修改結構無疑成本太高，不具備可操作性。這裡所採取的策略是“區域性有序”。利用修改語句中條件的範圍，由開放區間變為封閉區間，影響基數的選擇。（關於這部分，大家有興趣可多看看《基於成本的Oracle優化》一書）

如仍然不起作用，可考慮進一步細化分段或乾脆採用“逐條提取+批繫結”的方式解決。

一個小小的資料型別設定不當，會為我們後面的工作帶來的多大的麻煩。

案例4： “抽絲剝繭”找出問題所在

這裡會描述一次完整的優化過程，看看DBA是如何“抽絲剝繭”，發現問題本質的。

這個案例本身不是為了說明某種技術，而是展現了DBA在分析處理問題時的一種處理方式。其採用的方法往往是根據自己掌握的知識，分析判斷某種可能性，然後再驗證確認是否是這個原因。在不斷的丟擲疑問，不斷的驗證糾錯中，逐步接近問題的本質。

也想通過這個示例，告知廣大開發人員，DBA優化語句的不容易。

這是某資料倉儲系統，有一個作業在某天出現較大延遲。為了不影響明天的業務系統，必須在今天解決這個問題。經和開發人員的溝通，該業務的SQL語句沒有修改，相關的資料結構也沒有變更相類似的其他業務（SQL語句相似的）也都正常執行，資料庫系統本身也沒有異常。

修改後執行計劃，跟其他類似SQL相同了。整個計劃可概述為”HASH JOIN” + “FULL TABLE SCAN”。經測試，速度略有提升，但是整個執行時間仍然超過2個小時。

開始了第一次嘗試，開始想到的方法很簡單，既然類似的SQL執行效率沒問題，而這個SQL由於其他SQL執行計劃偏差較大，我可以手工採取固化執行計劃的方法。這裡使用了抽取OUTLINE的方式。經測試，對速度提升不大，不知問題主因。

第二次嘗試，從等待事件角度入手。首先考慮的是和快取有關的問題。

不要盲目相信別人的話，優化之前先按照故有流程檢驗一遍，做到心中有數。對於此例來說，可以大大加快問題的解決。

Q&A

Q1：ANSI 的SQL標準，會一直推出新版本嗎？ 後續版本是否會加入新的語法和特性呢？

A1：這個問題沒有仔細考慮過，ANSI-SQL的標準一直在變化，不同的資料庫根據自身情況實現了它的子集。從我個人角度來看，未來ANSI-SQL可能會對大資料、資料探勘方向有所考慮，加入部分新語法或特性。畢竟SQL介面作為人們最為熟悉的資料訪問介面，未來在大資料等方向大有可為。

Q2：優化SQL最終的目的是不是改變SQL執行計劃?

A2：第一目的，是理解現有優化器選擇的行為，並考慮是否是最佳選擇。第二目的，是在優化器功能有所侷限的情況下，通過人工介入的方式，讓資料庫以更優的方式執行SQL。畢竟人要比電腦更理解資料。

Q3：能不能介紹一下開發中，資料型別的選擇對資料庫的影響？

A3：資料型別在優化層面，主要可從以下角度考慮：

選擇“合適”的型別儲存資料。注意，這裡使用的詞是“合適”，要確保精度、夠用、不浪費的原則。

資料型別在資料庫自身儲存、計算上的特性，不同型別的效率是不同的。

型別間要做到相容，保證關聯欄位的型別一致性。

Q4：能不能介紹下oracle資料遷移的常用方式和利弊？

A4：這個有很多，取決於遷移的需求，比如常用的：

1.備份、恢復；2.邏輯匯入、匯出(含傳輸表空間等)；3.DATAGUARD；4.LOG SYNC（例如OGG等）；5.程式同步……利弊，主要取決於成本、代價了，每種方案都有自身的適用場景。

Q5：請問必須全表掃描的語句有什麼優化思路？

A5：必須用全表掃描的情況，就適用於分享中的“DoDo”原則第二條，儘量讓其更快的完成。可考慮的策略有：

• DIRECT PATH READ

• 加大MULTI BLOCK READ COUNT

• 啟用PARALLEL

• 更好的IO

Q6：對於group by語句如何優化?

A6：對於分組來說，Oracle 11g以後的版本提供了HASH GROUP BY的實現。HASH是個重記憶體消耗操作，可從記憶體使用角度基於優化考慮。

Q7：訪問路徑是會快取起來的，怎麼判斷回收沒用的快取中的訪問路徑呢？

A7：一般不需要考慮回收問題，如果非要做可從記憶體資訊中瞭解此執行計劃是否最近被使用，使用DBMS包清除即可。

Q8：oracle發現在雲機上安裝之後，在併發性方面不行，這是為什麼？

A8：不同雲的實現策略不同。併發性方面，可考慮從vCPU使用、IO等方面著手。這方面經驗不多，抱歉！

Q9：全表掃描想辦法修改為索引全表掃描是否合適？使用with子句來優化sql，這個手段如何?

A9：將全表掃描修改為索引全掃描，根本原則是能夠縮小訪問量，即讓資料庫幹更少的活。

WITH子句，定義查詢塊，一個目的是減少多次引用，但也有可能出現不允許執行查詢語句變形的情況，要具體分情況分析。

作者：韓鋒  

DBAplus社群分享

內容來源：宜信技術學院

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