Hadoop平臺中SQL優化的四個思路
要正確的優化SQL，必須能快速定位性能瓶頸點，或者說快速找到SQL主要的開銷所在。最慢的設備通常是瓶頸點的成因，如文件下載時的瓶頸點可能是網絡速度，本地文件復制時的瓶頸點可能在于硬盤性能。
為了快速找到SQL的性能瓶頸點，首先需要讀者對各種設備的性能數據有一些基本的認識，如千兆網絡帶寬是1000Mbps，硬盤轉速為每分鐘7200/10000轉等。
下圖數據給出了一些當前主流的計算機性能指標。
圖1 I/O各層次硬件性能匯總
如上圖所示，每種設備基本上都有兩個重要指標：
- 延時(響應時間)：反映硬件的突發處理能力。
- 帶寬(吞吐量)：反映硬件持續處理能力。
通過比較這兩種指標，可以發現計算機各系統硬件性能從高到低依次為：CPU→Cache(L1-L2-L3)→內存→SSD硬盤→網絡→硬盤。
比較性能之后，我們再看一下每種硬件在Hadoop系統進行SQL運算時負責的主要工作：
CPU及內存：緩存數據訪問、比較、排序、事務檢測、SQL解析、函數或邏輯運算、JOIN、數據加解密、加解壓等;
網絡：結果或者Shuffle數據的傳輸、SQL請求、遠程數據訪問等;
硬盤：數據訪問、數據寫入、日志記錄、外排序、Shuffle等。
將以上陳列的各硬件性能指標及其工作內容結合考慮，在Hadoop集群中提升SQL的執行性能就是要盡量做到以下四點：
- 減少數據訪問(減少磁盤訪問)
- 減少中間結果量(減少網絡傳輸或磁盤訪問)
- 減少交互次數(減少網絡傳輸、減少調度開銷)
- 改進算法，減少服務器CPU開銷(減少CPU及內存開銷)
注：實際優化時，除了以上四點還應注意任務分配要均勻且大小適中。
總而言之，優化的基本思想就是反復迭代，合理利用資源，綜合平衡各種開銷，以求達到***效果。下面將簡單介紹這四種優化思路，以及分別可采用的方法。
1. 減少數據訪問
傳統關系型數據庫例如MySQL、Oracle等，通常通過提供索引來實現減少數據訪問、提升訪問速度，但是由于Hadoop不維護鍵(Key)的特性，因而SQL on Hadoop引擎一般不提供對傳統索引的支持，或者功能不像傳統索引一樣完備。
為了達到和索引相似的優化目的，即加快過濾掃描，SQL on Hadoop產品通常提供其他功能用以彌補。以星環科技的Inceptor為例，其本身并沒有可用于控制的傳統意義上的索引，但是提供了分區、分桶，以及MinMaxFilter、BloomFilter以及RowFilter等用于批量過濾數據的過濾器。這些功能的原理通常是通過把相似、相關或者相等的數據進行歸類以減少查詢搜索的范圍，或者建立基于列式存儲的掃描方式盡可能的減少無關數據的讀取。使用者需要結合實際語句，把這些功能進行高效組合，合理運用在刀刃上。
2. 返回更少的數據
返回更少的數據就是要求在構造SQL語句時，只SELECT需要的列。因為每個字段的提取都是一個復雜的解析過程，且占用內存，所以為了減少不必要的查詢時間，請讀者***僅返回需要的字段。比如減少“SELECT *”的使用，因為大多數情況是不需要所有字段的數據的。
【例1】如果某用戶提交了這樣的語句，但是實際需要的只有id、name兩個字段：
- SELECT * FROM product WHERE company_id = 456723
- LIMIT 100;
為了加快執行速度，建議將語句寫為：
- SELECT id, name FROM product
- WHERE company_id = 456723
- LIMIT 10;
另外若SELECT的結果是用于判斷某些條件是否成立，例如EXISTS操作，就更加沒必要返回所有數據：
【例2】某個包含關聯的語句，在優化調整前，EXISTS內部返回了滿足條件的所有字段值：
- SELECT … FROM table_name_2 WHERE
- … EXISTS (
- SELECT * FROM table_name_1
- WHERE table_name_1.col1 = table_name_2.col1
- );
但是EXISTS的返回僅用于判斷滿足條件的記錄存在與否，所以EXISTS內部無需返回所有字段。因此可以將EXISTS子句中的“SELECT *”優化為“SELECT 1”：
- SELECT … FROM table_name_2 WHERE
- … EXISTS (
- SELECT 1 FROM table_name_1
- WHERE table_name_1.col1 = table_name_2.col1
- );
3. 減少交互次數
減少交互次數就是減少網絡通信的交互次數。這里分享與此相關的三種優化情況。
Batch DML
批量方式處理DML可以大幅度減少和服務器的交互次數。Inceptor數據庫訪問框架提供了批量提交的接口以服務于大量插入數據。當用戶一次性往一個表中插入1000萬條數據時，試想如果采用普通的Insert，將和服務器發生1000萬次交互，按每秒鐘向數據庫服務器提交10000次估算，完成所有工作需要消耗1000秒。但是如果采用批量提交模式，每1000條提交一次，和服務器的交互次數就減少至1萬次，交互次數大大減少，耗時縮短為原來的千分之一。
采用Batch操作雖然不會大量減少數據庫服務器的物理I/O，但是會大幅減少客戶端與服務端的交互次數，從而降低多次發起的網絡延時開銷，以及數據庫的CPU開銷。
In List
進行數據掃描時，有時會遇到這樣的情況：到手多個ID，需要查詢與這些ID相關的記錄。有兩種方式實現：單條提交或者批量提交。
單條處理就是采用一個ID發一個請求的方式傳送給數據庫：
- for: var in ids[] do begin
- SELECT * FROM table_name WHERE id=:var;
- end;
這種方法會增加與服務器的交互次數，顯然和減少交互次數的思想背道而馳，固然是不推薦的。建議用ID InList的方式批量提交，可以把多次交互壓縮在一次訪問中完成，加速查詢：
- SELECT * FROM table_name
- WHERE id IN ids[];
使用存儲過程
Inceptor支持存儲過程，合理的利用存儲過程有助于提高系統性能。存儲過程是由SQL語句組成的完成特定功能的代碼塊。每個代碼塊在創建時都需要命名，用戶通過訪問對應名稱調用它們。存儲過程中的代碼都是已經編譯過的，所以調用的時候可以跳過編譯階段直接執行，而且由于其直接存儲在數據庫中，可以避免SQL語句的重復傳輸。
總體而言使用存儲過程有以下兩方面的好處：
減少編譯次數提高了執行效率。
在網絡交互中代替了大量的SQL語句，使用者只需傳遞一些必要參數，幫助減少網絡通信量，提升通信效率。
4. 減少數據庫服務器
CPU運算SQL中會包含各種各樣的操作和計算要求CPU參與運算，其中有一些計算并非必須，可以人為避免。例如，進行對比運算時，對于不匹配的類型，系統要對操作數進行類型轉換，導致加重CPU負擔。所以，對于數字和日期類型，建議用戶在執行計算前先進行類型轉換，使各操作數的類型匹配，或者建表時盡可能的把字段規劃成相同的數據類型。
另外，對于SQL中的邏輯運算符，Inceptor通常對普通比較運算符(如等于、不等)有較好的表現，但是對于服務器CPU需求量很高的操作，需要用戶保持警惕。如LIKE操作，該模糊查詢對CPU的要求一般較高，特別是檢查的記錄有上萬條及以上時，系統表現比較糟糕。建議用戶根據業務語義盡量用In-List實現LIKE，在In-List中包含LIKE所有可能的匹配選項。
【例3】如下所示模糊查詢語句：
- SELECT * FROM table_name
- WHERE column_name LIKE ‘%abc%’;
若已知該列字段值僅有三種取值‘cabc’、‘abce’、‘cabe’，上面的語句可以等價為這樣的表達方式：
- SELECT * FROM table_name
- WHERE column_name IN (‘cabc’, ‘abce’, ‘cabe’);
【例4】如果In-List數據可用一條SELECT語句查詢得到，***讓一張中間小表作為In列表內部數據，然后采用內外查詢關聯的方式進行檢索：
- SELECT * FROM table_name
- WHERE column_name IN (
- SELECT col_name FROM tbl WHERE gender = ‘f’
- );
總結本文分享了四種在Hadoop平臺中常用的SQL優化思路，實際上每種思路在具體應用時都可以引申出很多不同的方法，介紹這些思路的目的在于為用戶在選擇SQL優化手段時提供一些明確方向。
***大致總結一下這些優化思路的適用場合：
- 在過濾掃描階段考慮如何減少數據訪問;
- 構造SELECT子句時應思考應該如何減少返回數據;
- 當執行涉及向服務器發起交互請求的操作時，應當選擇減少交互次數的合適方法;
- 必要時進行人工處理以減少不必要的CPU計算。
如果用戶能夠考慮并兼顧這四個方面，相信由此構造的SQL語句會在Hadoop平臺中有更好的執行性能。
