大數據場景下的查詢優化 vs 資料庫場景下的查詢優化?

請問各位大神，資料庫場景下的查詢優化器和大數據場景下【Mapreduce、Spark】的查詢優化器的優化手段上有什麼相同點和不同點？自己也看了一些論文，希望能一起討論討論。

具體場景：批處理，執行一批sql查詢

最主要的區別應該是在 並行/分散式（這二者在查詢優化的角度上並無本質的區別）場景下需要考慮 Partition 策略（可以參考 SCOPE 的幾篇論文），其他的部分應該是相同的。面向單機引擎的優化器可以說是並行版本的一個子集。

類似 TiDB 這種以 OLTP 為主的分散式資料庫為了低延時，目前會省略 exploration（其實是做起來很麻煩。。。），把 NP-H 問題化簡成多項式時間複雜度。

不了解查詢優化器，自己對兩個場景的一點感性認識是：

• OLAP 會使用 MPP 之類的手段儘可能多地利用集群資源來加速一條 adhoc 查詢的速度，認為 "我想臨時獨佔一下集群的計算資源，查完就釋放了，你們後面來的先排個隊"；此外為了支持 adhoc 查詢的靈活性，更喜歡列式存儲；
• 互聯網 OLTP 場景的在線查詢要支撐並發，不能允許一個 query 獨佔掉整個集群的資源，而要每個查詢都只佔用可預期的資源以保證所有查詢的實時性，為此重 IO 的查詢、CPU 密集的計數、排序類操作最好大家都悠著來，認為 "公路是大家的，不要橫衝直撞堵起來"；這時通過精細設計的索引和有限的查詢種類控制 IO 壓力是很嚴肅的事，吃不消滿天飛的 adhoc 查詢；

著作權歸作者所有。

商業轉載請聯繫作者獲得授權，非商業轉載請註明出處。

作者：opzoonzhuzhengke

鏈接：大數據量下的資料庫查詢與插入如何優化？ （整理）

來源：CSDN博客

資料庫經常要做一些查詢與插入，但是如果查詢和插入的數據量過大的時候就會引發資料庫性能問題，降低資料庫工作效率。因此性能調優是大家在工作中都能夠預見的問題，大到世界五百強的核心系統，小到超市的庫存系統，幾乎都會有要調優的時候。面對形形色色的系統，林林總總的需求，調優的手段也是豐富多彩。

1.盡量使語句符合查詢優化器的規則避免全表掃描而使用索引查詢

2.避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

3.盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

4.建立高效的索引

SQL語句的Select部分只寫必要的列；盡量將In子查詢重寫為Exists子查詢；

去除在謂詞列上編寫的任何數學運算；儘可能不用Distinct；

由於優化工具處理「或」邏輯可能有問題，所以盡量採用其他方式重寫；

確保所處理的表中數據分布和其他統計信息正確，並反映當前狀況；儘可能用UNION ALL取代UNION；

儘可能減少DB2的SQL請求；盡量將區間謂詞重寫為Between謂詞；不要只是為了排序而選擇某一列；

我目前所在的系統就是這麼一個有實時插入又需要大數據的查詢的一個系統。

採用了如下手段：

1，當天的記錄會放在一個獨立的表中.主要是針對實時的插入的記錄，記錄不要太多以免插入的時候維護索引的開銷穩定在一個範圍內。

2，歷史的記錄會按天分區的形式保存在歷史表中。這個表一天只會批量的插入一次數據(用的是分區交換的方法)。

3，分區的索引對我的業務性能不好，因為要跨天 查詢。歷史查詢最長時間段是一個月的時間，如果按照一個月一個分區的話，一個分區差不多是一個億的記錄，

就算是按月分區的話，再創建分區的本地索引，如果是時間段跨了月份的話估計分區的本地索引性能估計也不行。

4，後來採用一個方案，DB層上面再放了一個緩衝層，就是我最近在測試的Timesten關係型內存資料庫，按照時間的老化策略緩衝一個月的數據。具體不展開說了。涉及的內容很多。

只是對於這個系統，我總結一下有以下需要注意的地方:

1,對於一個系統來說，如果查詢性能反應不好的話，第一個調整的地方是思考業務的需求是否是合理的？

一個查詢既要分頁獲取前面一頁或者幾頁的數據，又要根據條件獲取總的記錄數，如果符合的記錄總數是上億條的話，感覺就是一個不合理的要求。

2，市場需求調研人員業務水平根本不合格。

3，前台開發人員寫的SQL差，根本沒有調優的基本概念,術業有專攻啊。

4，如果業務需求合理，SQL的調整無非是從執行計劃開始，如果是ORACLE10g,開了cbo，可以用 SQL優化器 (SQL Tuning Advisor :STA)

分析你的sql。

5，最近的nosq和海量分散式的資料庫概念很熱。公司也在考慮HBASE了。

分區，

讀寫分離。

細節的優化手段大家都說了很多，我說些幾個粗略的方面：

1.使用分區表

2.並行查詢

3.定期的數據信息採集

4.可以考慮使用sql hint（生產庫上我個人認為還是少指定 HINT，可以考慮用 SQL_PROFILE固定執行計劃）

對於大數據量的處理，我通常採用如下方法：

一、對於大數據存儲的處理（以下是假設硬體指標合格的情況）

（1）對大表進行分區，根據不同的業務以及數據特徵，採用不同的分區方法。比如，銷售，可以考慮採用間隔分區技術，多分公司或是多部門，可以考慮採用列表分區或是上述的組合分區等。

（2）如果硬體具備，對於大表進行分離存儲，從而減少磁碟爭用。

（3）對歷史數據進行定期的歸檔處理。比如，銷售的區間分區，可以採用表與分區交換的技術去處理。對於含有複雜的業務的數據表的歸檔，採用PL/SQL腳本與後台作業定時完成。

二、對於查詢的處理（以下是基於成本優化器的情況）

對於查詢，優化的方法通常會多一些，但是也是最頻繁調整的一塊內容。通常，我從如下這幾個方面來進行考慮並調優.

（1）對於SQL本身的編寫是否合理。比如基礎寫法與高級寫法之間的配合，在滿足業務要求的基礎上，做到盡量減少表的訪問。

（2）索引的創建是否合理，優化器是否選擇了較正確的索引。在不同的業務場景下，B*樹索引與點陣圖索引的相互配合是否合理等。

（3）監控系統中產生的爭用，根據產生的不同的閂或鎖，對SQL或是業務處理邏輯進行調整。

（4）如果有必要，根據當前系統的負載與硬體本身的支持，對PGA，SGA進行一個分配，使之更為合理。

（5）如果有必要，在優化器參數進行一個調整。

（6）採用並行處理。

三、對數據插入的處理

這是考慮問題當中最薄弱的一塊內容，一般不作必要的優化處理，但有如下一些技巧：

（1）對於大數據量的表與表之間的插入，可以採用並行，直接路徑、最小化日誌。

（2）如果必要，對錶本身參數進行一個修改，如freelist。

（3）如果是同數據源多目標的插入，可以採用多表插入技術。

（4）如果可以，盡量使用INSERT INTO SELECT,CREATE TABLE AS SELECT方法。

如果在前期需求調研，設計階段就做好了工作。那麼後面的性能優化問題麻煩就可以少很多。

當然通常由於各種原因，往往前期做不到那麼好。

那麼找到問題，發現問題，解決問題，通常

1. 通過工具來定位問題，例如選擇用10046 TRACE 工具包來實現

2. 找到問題所在以後去理解需求，探索是否能少做事完成需求（選擇用索引來替代全表掃描，從而減少訪問路徑）;

3. 去思考需求背後的真正需求，例如是否可以用UNION ALL取代UNION ，避免不必要的排序引起資源消耗。

4. 去分析資源如何合理應用 （在系統空閑時使用並行技術）。

5.診斷索引方面的問題。例如是否由於錯誤的優化器統計信息導致執行了不正確的執行計劃。

資料庫表進行插入、查詢操作當數據達到百萬甚至千萬條級別的時候， 這一切似乎變得相當困難。幾經折騰，總算完成了任務。在此做些簡單的小結，不足之處，還望高手們幫忙補充補充！

1、避免使用Hibernate框架

Hibernate用起來雖然方便，但對於海量數據的操作顯得力不從心。

關於插入：

試過用Hibernate一次性進行5萬條左右數據的插入，若ID使用sequence方式生成，Hibernate將分5萬次從資料庫取得 5萬個sequence，構造成相應對象後，再分五萬次將數據保存到資料庫。花了我十分鐘時間。主要的時間不是花在插入上，而是花在5萬次從資料庫取 sequence上，弄得我相當鬱悶。雖然後來把ID生成方式改成increase解決了問題，但還是對那十分鐘的等待心有餘悸。

關於查詢：

Hibernate對資料庫查詢的主要思想還是面向對象的，這將使許多我們不需要查詢的數據佔用了大量的系統資源（包括資料庫資源和本地資 源）。由於對Hibernate的偏愛，本著不拋棄、不放棄的作風，做了包括配SQL，改進SQL等等的相當多的嘗試，可都以失敗告終，不得不忍痛割愛 了。

2、寫查詢語句時，要把查詢的欄位一一列出

查詢時不要使用類似select * from x_table的語句，要盡量使用select id,name from x_table，以避免查詢出不需要的數據浪費資源。對於海量數據而言，一個欄位所佔用的資源和查詢時間是相當可觀的。

3、減少不必要的查詢條件

當我們在做查詢時，常常是前台提交一個查詢表單到後台，後台解析這個表 單，而後進行查詢操作。在我們解析表單時，為了方便起見，常常喜歡將一些不需要查詢的條件用永真的條件來代替（如：select count(id) from x_table where name like 『%』），其實這樣的SQL對資源的浪費是相當可怕的。我試過對於同樣的近一千萬條記錄的查詢來說，使用select count(id) from x_table 進行表查詢需要11秒，而使用select count(id) from x_table where name like 『%』卻花了33秒。

4、避免在查詢時使用表連接

在做海量數據查詢時，應盡量避免表連接（特別是左、右連接），萬不得已要進行表連接時，被連接的另一張表數據量一定不能太大，若連接的另一張表也是數萬條的話，那估計可以考慮重新設計庫表了，因為那需要等待的時間決不是正常用戶所能忍受的。

5、嵌套查詢時，儘可能地在第一次select就把查詢範圍縮到最小

在有多個select嵌套查詢的時候，應盡量在最內層就把所要查詢的範圍縮到最小，能分頁的先分頁。很多時候，就是這樣簡單地把分頁放到內層查詢里，對查詢效率來說能形成質的變化。

特別是銀行系統的，數量級是億級別的，所以更要考慮下面的方法。

1，怎樣造Java對象。有句話說得好：儘可能的少造對象。別說千萬級，就是上萬級都不要考慮造對象了。因為幾個請求一併發，喀嚓，系統肯定完蛋。

2，合理擺正系統設計的位置。大量數據操作，和少量數據操作一定是分開的。大量的數據操作，肯定不是ORM框架搞定的。絕對不能ORM，因為1，要少造對象；2，資料庫資源合理利用。就像博主的例子：id分配就是一個好例子。

3，合理利用資料庫的分區、索引技術。

4，有的時候可以考慮臨時表之類的，尤其是大數據量。

5，有人說非常大的數據量，一定要用存儲過程：jdbc，效果非常好

6，控制好內存，讓數據流起來，而不是全部讀到內存再處理，而是邊讀取邊處理；

7，合理利用內存，有的數據要緩存

瀉藥。