sql優化案例

A. 22 六種常見sql場景及其在TDH中的優化策略

//
Transwarp - 新聞詳情
http://www.transwarp.io/news/detail?id=161

在《 Hadoop平台中SQL優化的四個思路 》一文中，我們對Hadoop平台中的SQL優化思路做了簡單介紹，為的是讓讀者能對SQL優化有一個宏觀掌握。
本文將針對Transwarp Data Hub（TDH）中的常見SQL的場景，繼續深入討論如何對不同類型SQL選擇不同的優化策略。根據SQL特性以及數據特性，本文把TDH中涉及的SQL場景分為以下六類，分別對它們的優化方法進行分析。

分析數據。獲取每個表的各行各列的特性，比較分析是否存在記錄行數很大的表、表與表的特點差異和記錄行數量級的差距。

分析執行計劃。明確是否應該用MapJoin，是否應調整JOIN順序，是否需要謂詞下推。

分析過濾率。計算過濾率，核對JOIN順序並做出調整，先JOIN數據量少過濾率高的表。

對SQL分類，選擇典型，重點分析。從最影響性能且容易改善的部分開始，不斷優化、迭代，直至得到滿意效果。

Step5：最終把選用的優化手段按類型應用到其他語句上。
對SQL優化進行實踐的過程中，需要讀者不僅僅只是理解這些優化思想，更重要的是在足夠多的案例中去累積經驗，多嘗試多比較，提高對特徵SQL的敏銳度，將充裕的理論轉化為價值，將了解的事物變成屬於自己的東西。

B. 如何用SQL分析電商用戶行為數據（案例）

        

本文以「淘寶用戶行為數據集」的分析全過程為例，展示數據分析的全過程

——使用工具：MySQL，Excel，Navicat，PowerBI

——分析類型：描述分析，診斷分析

——分析方法：漏斗分析，用戶路徑分析，RFM用戶價值分析，活躍/存留分析，帕累托分析，假設驗證分析。

（考慮到閱讀體驗文章中只放了SQL截圖，如需PDF版本，再公眾號後台回復「用戶行為分析」領取）

（目錄如下）

       

1.分析流程和方法

當沒有清晰的數據看板時我們需要先清洗雜亂的數據，基於分析模型做可視化，搭建描述性的數據看板。

然後基於描述性的數據挖掘問題，提出假設做優化，或者基於用戶特徵數據進行預測分析找規律，基於規律設計策略。簡單來說：

——描述性分析就是：「畫地圖」

——診斷性分析就是：「找問題」

——預測性分析就是 ：「找規律」

在數據分析中有兩個典型的場景：

一種是有數據，沒有問題：需要先整體分析數據，然後再根據初步的描述分析，挖掘問題做診斷性分析，提出假設，設計策略解決問題。

 

另一種是已經發現了問題，或者已經有了假設，這種做數據分析更偏向於驗證假設。

 

2.淘寶用戶行為分析

本次是對「淘寶用戶行為數據集」進行分析，在分析之前我們並不知道有什麼問題，所以需要先進行描述性分析，分析數據挖掘問題。

我們首先來看下這個數據集的元數據：

       

根據以上數據欄位我們可以拿用戶行為為主軸從縱深方向提出一些問題，然後再從數據中找答案

       

縱向：

——這個數據集中用戶的日活躍和周活躍時間有什麼規律嗎？

——在當日活躍的用戶次日，三日，四日……還有多少活躍？

深向：

——用戶從瀏覽到購買的整體轉化率怎麼樣？

——用戶從瀏覽到購買的路徑是怎麼樣子的？ 

——平台主要會給用戶推送什麼商品？

——用戶喜歡什麼類目？喜歡什麼商品？ 

——怎麼判斷哪些是高價值用戶 ？ 

 

 

下面是叮當整理的常用分析方法：      

我們可以給前面的問題匹配一下分析方法，便於後面的分析：

為了便於後面的數據分析，在分析之前我們需要先對做一下清洗

看元數據（欄位解釋，數據來源，數據類型，數據量……）初步發現問題為之後的處理做准備。

       

確定缺失值范圍，去除不需要欄位，填充缺失內容    

根據元數據格式和後續分析需要的格式對數據進行處理

  

去除重復值，異常值

——去除重復值：並把用戶ID,商品ID，時間戳設置為主鍵

——異常值處理：查詢並刪除2017年11月25日至2017年12月3日之外的數據

     

查詢並刪除小於2017-11-25的

——驗證數據：      

——分析思路：

——SQL提數：

       

       

——Excel可視化：

       

活躍曲線整體為上升狀態，同為周六日，12月2號，3號相比11月25日，26日活躍度更高。

用戶在周六周日相比其他時間更活躍（周六周日為休息日，用戶有更多時間）

      

一天內用戶活躍的最高峰期為21點（用戶在這個時間段空閑較多）

 

——分析思路：

——SQL提數：

列出每用戶每天及當天後面又活躍的日期，並創建「活躍時間間隔表」用於後面求次日存留，三日存留……

       

對「活躍時間間隔表視圖」引用進行分組統計，計算每日存留人數並創建視圖

對存留人數表進行計算，統計活躍用戶留存率

——Excel可視化：

       

——分析思路：

——SQL提數：

-把各種用戶行為分離出來並創建視圖方便後續查詢用戶行為數據

查詢整體數據漏斗

——Excel可視化：

       

用戶從瀏覽到購買整體轉化率2.3%，具體主要在哪個環節流失還需要再細分用戶路徑分析

 

——分析思路：

       

——SQL提數：

——PowerBI可視化：

       

用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越底

路徑1：瀏覽→購買：轉化率1.45%

路徑2：瀏覽→加購物車→購買：轉化率0.33

路徑3：瀏覽→收藏→購買：轉化率0.11%

路徑4：瀏覽→收藏→加購物車→購買：轉化率0.03%

——分析思路：

——SQL提數：

——Excel可視化：

       

——描述性分析：

瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠後商品瀏覽量階梯之間的落差相對越小，同階梯內的商品越多。

瀏覽量TOP100的商品所屬類目中，4756105，3607361，4357323三個類目瀏覽量遠超其他類目。

——分析思路：

——SQL提數：

查詢計算商品轉化率，升序排列，取前100個

       

——Excel可視化：

       

——描述性分析：

從商品看：有17款商品轉化率超過了1。

從類目看：這些商品所屬類目分布均勻，除965809，4801426，2735466，2640118，5063620，4789432，2945933這7個類目之外，其他類目都只有一個商品在轉化率TOP100的商品中。

——分析思路：

用戶價值分析常用的分析方式是RFM模型

       

本次分析中的R,F,M具體定義（僅用於演示分析方法，無實際業務參考價值）：

 

——SQL取數與分析：

1）建立打分標准：先計算R,F的值，並排序，根據R,F值最大值和最小值得區間設計本次得打分標准

-查詢並計算R，F值創建視圖

       

-引用RF數值表，分別查詢R,F的最大值和最小值

       

       

-結合人工瀏覽的建立打分標准      

2）給R，F按價值打分

3）計算價值的平均值

       

4）用平均值和用戶分類規則表比較得出用戶分類   

     

——Excel可視化      

 

通過描述性分析得到可視化的數據後我們一般會先看一下是否符合業務常識

如果符合常識接下來我們會通過與行業平均數據和本產品的同比環比對比看是否正常，如果不正常就要找原因，設計解決方案，如果正常那就看是否有可以優化的地方。

       

我們首先來看一下這些描述性分析是否符合業務常識和指標是否正常：

       

1.活躍曲線整體為上升狀態，同為周六日，12月2號，3號相比11月25日，26日活躍度更高。

2.用戶在周六周日相比其他時間更活躍

3.一天內用戶活躍的最高峰期為21點

4.從2017年11月15日致2017年12月3日，活躍用戶新增38%

5.從2017年11月15日致2017年12月3日，活躍用戶次日留存增長18.67%，當日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。

6.用戶從瀏覽到購買整體轉化率2.3%

7.用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越低。

8.瀏覽量top100的商品瀏覽量呈階梯分布，越靠前的階梯之間的落差相對越大在這個階梯中的商品越少，越靠後商品瀏覽量階梯之間的落差相對越小，同階梯內的商品越多。

9.瀏覽量TOP100的商品所屬類目中，4756105，3607361，4357323三個類目瀏覽量遠超其他類目。

10.從商品看：有17款商品轉化率超過了1。

11.從類目看：這些商品所屬類目分布均勻，除965809，4801426，2735466，2640118，5063620，4789432，2945933這7個類目之外，其他類目都只有一個商品在轉化率TOP100的商品中。

根據以上診斷分析我們梳理出了以下假設，做假設驗證。

       

 

假設1：這些商品中有高轉化率的爆款商品

       

 

對比瀏覽量TOP5的商品，發現這些商品轉化率在同一類目下並不高，假設不成立

 

假設2：4756105，3607361，4357323三個類目屬於高頻剛需類目

-創建類目購買頻次表

       

-計算類目購買頻次平均值

       

-查詢4756105，3607361，4357323三個類目的購買頻次       

4756105，3607361，4357323三個類目的用戶購買頻次明顯高於平均值，假設成立

 

假設3：有部分用戶是未點擊商詳直接從收藏和購物車購買的。

       

用戶不是直接從收藏和購物車購買的，只是後續復購未點擊商詳，假設不成立

 

假設4：淘寶推薦的商品主要是「同一類目下的高轉化商品」

       

用Excel對瀏覽量TOP100的商品ID和轉化率TOP100的商品ID進行去重，結果無重復值，假設不成立

3.結論：

1）用戶活躍：用戶活躍曲線整體呈上升趨勢，在一周中周六，周日活躍度比平時更高，在一天中用戶活躍曲線從凌晨4點開始往上升，在中午12點和下午5~6點有兩個小低谷（吃飯），到晚上9點時活躍度達到頂峰。

 

2）用戶留存：從2017年11月15日致2017年12月3日的用戶留存數據來看，淘寶的用戶留存數據較好，活躍用戶次日留存增長18.67%，當日的活躍用戶留存也在快速增長，第七日留存比次日留存高18.56%。

 

3）用戶轉化：整體轉化2.3%，用戶從瀏覽到購買的路徑主要有4條，路徑越長轉化率越低。

4）平台推薦與用戶偏好：從數據集中的數據來看，排除用戶興趣偏好標簽，淘寶給用戶用戶推送的商品主要是高頻剛需的類目，促使用戶復購，流量迴流平台。

 

以上結論受數據量和數據類型的影響，並不一定準確，僅用來練習數據分析方法。

（考慮到閱讀體驗文章中只放了SQL截圖，如需PDF版本，再公眾號後台回復「用戶行為分析」領取）

C. 如何使OQWT定位和分析SQL性能問題

封面報道
如何使用 IBM Optim Query Workload Tuner 定位和分析 SQL 性能問題 作者：秦瑋, 劉銘, 趙國斌
IBM Optim Query Workload Tuner ( 簡稱 OQWT)， 是面向資料庫管理人員和應用開發人員的新一代 SQL 性能調優產品，它可以對單個或者一組 SQL 查詢語句進行性能分析和調優，包括 SQL 查詢語句的定位、圖形化的性能分析、多種的優化建議、驗證優化方案等等。它提供了豐富的功能可以快速的從各種常用源找到有潛在性能問題的 SQL 語句。下面詳細介紹如何使用 OQWT 快速定位問題 SQL 語句。

社區討論精彩回顧：您如何看待使用資料庫工具對DBA技術成長的影響?
本次討論主要就以下幾個問題進行了探討：1、您如何看待使用資料庫圖形工具對DBA以及開發人員技術成長的影響? 2、您認為DBA們最常用的資料庫工具是什麼？ 3、您認為DBA們應該如何使用資料庫工具才能高效的成長？

安裝配置
AIX DB2 V8升級到DB2 V9.5的步驟 作者：DB2開發與應用社區 lijiantang
本文分操作系統升級與環境確認、停止相關系統應用、安裝DB9資料庫程序、備份相關配置文件等十個步驟向大家詳細介紹了DB2資料庫升級步驟。

性能調優
「三方演義」與性能優化 作者：DB2開發與應用社區 王飛鵬
本文作者自己實際的體會探討了性能優化為什麼這么難？並重點分析了某銀行性能優化的真實案例。

故障診斷
對 DB2 遠程 SQL 復制應用中的常見錯誤分析及處理 作者：黃湘平,張蕾
本文深入分析和刨析 SQL 復制的運行機制，列舉出常見錯誤的處理方法，使 DB2 開發和管理人員能縮短處理問題的時間，提高他們在系統中應用 SQL 復制的效率。

博客精選
淺談影響DB2高可用性的各種因素 作者：DB2開發與應用社區 sunyangnj
DB2資料庫本身的設計和操作對高可用影響非常大。所以本文著重從DB2本身出發談談做好高可用應該注意的方面。

教程推薦
本欄目提供了DB2資料庫的實用教程。

D. 慢sql治理經典案例分享

作者 | 如期
來源 | 阿里技術公眾號

菜鳥供應鏈金融慢sql治理已經有一段時間，自己負責的應用持續很長時間沒有慢sql告警，現階段在推進組內其他成員治理應用慢sql。這里把治理過程中的一些實踐拿出來分享下。

在分頁查詢治理的文章里已經介紹過我們系統舊的分頁查詢邏輯，上面的查詢sql明顯就是分頁查詢獲取總記錄數，通過XXX_rules表的分頁查詢介面溯源，找到發起調用的頁面是我們小二後台的一個操作商家准入的頁面，頁面打開後直接調用分頁查詢介面，除了分頁參數，不傳入其他任何查詢參數，導致掃描全表。

靈魂拷問：為什麼要掃描全表？全表數據展示到頁面，花里胡哨的數據有用嗎？

調研：和經常使用這個頁面的運營聊後了解到，打開頁面查詢出的全表數據對運營是沒有用的，他們根本不看這些數據。運營的操作習慣是拿到商家id，在頁面查詢框中輸入商家id，查到商家數據後進行操作。

由此優化方案就很明朗了：打開頁面時不直接查詢全量數據，等運營輸入商家id後，將商家id作為參數進行查詢。XXX_rules表中，商家id這一常用查詢條件設置為索引，再結合分頁查詢優化，全表掃描慢sql得以解決。

優化後的小二後台頁面如下：

打開頁面時未查詢任何數據，查詢條件商家賬戶為必填項。

優化後的sql為:

執行EXPLAIN得到結果如下：

可以看到命中了索引，掃描行數為3，查詢速度明顯提高。

掃描全表治理簡單來說就是加入查詢條件，命中索引，去除全表掃描查詢，雖然有些粗暴，但並不是沒有道理。實際業務場景中，很少有要掃描全表獲取全部數據的情況，限制調用上游必須傳入查詢條件，且該查詢條件能命中索引，能很大程度上避免慢sql。

另外，再引申下，XXX_rules初始的用意是准入表，記錄金融貨主維度的准入情況，最多也就幾千條數據，但是很多同事將這張表理解為規則表，寫入很多業務相關規則，導致這個表膨脹到一百多萬條數據，表不clean了。這就涉及到數據表的設計使用，明確表的使用規范，不亂寫入數據，能給後期維護帶來很大的便利。

除了時間、操作人欄位，XXX_rules表就rule_name、rule_value、status、proct_code四個欄位，表的索引對這四個欄位做各種排列組合。存在如下問題：

1、rule_name離散度不高，放在索引首位不合適；

2、前三個索引重合度很高；

顯然是對索引的命中規則不夠了解。XXX_rules表很多業務有定時任務對其寫入刪除，索引多、混亂，對性能有很大的影響。

高性能的索引有哪些，再來回顧下：

1、獨立的列：索引列不能是表達式的一部分；

2、選擇區分度高的列作為索引；

3、選擇合適的索引列順序：將選擇性高的索引列放在最前列；

4、覆蓋索引：查詢的列均在索引中，不需要回查聚簇索引；

5、使用索引掃描來做排序;

6、在遵守最左前綴的原則下，盡量擴展索引，而不是創建索引。

但凡記得第3和6規則，也不至於把索引建成這樣。

對索引進行整合如下：

系統中有很多任務拉取整個產品下的准入記錄，然後進行處理，所以將區分度較高的proct_code放在索引首位，然後添加rule_name、status欄位到索引里，進一步過濾數據，減少掃描行數，避免慢sql。針對常用的rule_value查詢條件，可以命中UK，因此不用單獨建立索引。

很多業務邏輯中，需要拉取滿足某個條件的記錄列表，查詢的sql語句帶有order by，記錄比較多的情況，排序代價往往很大，但是查詢出來的記錄是否有序對業務邏輯沒有影響，比如分頁治理里討論的count語句，只需要統計條數，order by對條數沒有影響，再比如查出記錄列表後，不依賴記錄的順序遍歷列表處理數據，這時候order by多此一舉。

查詢sql無limit語句，且業務處理邏輯不依賴於order by後列表記錄的順序，則去除查詢sql中的order by語句。

業務中有很多定時任務，掃描某個表中某個產品下所有數據，對數據進行處理，比如：

三個查詢條件都是區分度不高的列，查出的數據有27W條，加索引意義也不大。

實際業務量沒那麼大，頂多幾千條數據，表裡的數據是從上游同步過來的，最好的辦法是讓上游精簡數據，但是由於業務太久遠，找上游的人維護難度太大，因此只能想其他的辦法。

這個定時任務目的是拉出XXX_rules表的某些產品下的數據，和另一張表數據對比，更新有差異的數據。每天凌晨處理，對時效性沒有很高的要求，因此，能不能轉移任務處理的地方，不在本應用機器上實時處理那麼多條數據？

數據是離線任務odps同步過來的，首先想到的就是dataWork數據處理平台。

建立數據對比任務，將定時任務做的數據對比邏輯放到dataWork上用sql實現，每天差異數據最多幾百條，且結果集含有區分度很高的列，將差異數據寫入odps表，再將數據迴流到idb。

新建定時任務，通過迴流回來的差異數據中區分度高的列作為查詢條件查詢XXX_rules，更新XXX_rules，解決了慢sql問題。

這個方法的前提是對數據實效性要求不高，且離線產出的結果集很小。

explain上述查詢語句，得到結果如下：

XXX_white_list表有將biz_id作為索引，這里查詢XXX_white_list表有傳入biz_id作為查詢條件，為啥explain結果里type為ALL，即掃描全表？索引失效了？索引失效有哪些情況？

索引失效場景

1、OR查詢左右有未命中索引的；

2、復合索引不滿足最左匹配原則；

3、Like以%開頭；

4、需要類型轉換；

5、where中索引列有運算；

6、where中索引列使用了函數；

7、如果mysql覺得全表掃描更快時（數據少時）

上述查詢語句第8行，customer_id為XXX_level_report表欄位，未命中XXX_white_list表索引，導致索引失效。

這個語句用condition、枚舉、join花里胡哨的代碼拼接起來的，改起來好麻煩，而且看起來「OR customer_id LIKE CONCAT(t.biz_id, '@%')」這句不能直接刪掉。最後重構了該部分的查詢語句，去除or查詢，解決了慢sql。

E. SQL優化萬能公式：5 大步驟 + 10 個案例

在應用開發的早期，數據量少，開發人員開發功能時更重視功能上的實現，隨著生產數據的增長，很多SQL語句開始暴露出性能問題，對生產的影響也越來越大，有時可能這些有問題的SQL就是整個系統性能的瓶頸。

1、通過慢查日誌等定位那些執行效率較低的SQL語句

2、explain 分析SQL的執行計劃

type由上至下，效率越來越高

Extra

3、show profile 分析

了解SQL執行的線程的狀態及消耗的時間。默認是關閉的，開啟語句「set profiling = 1;」

4、trace

trace分析優化器如何選擇執行計劃，通過trace文件能夠進一步了解為什麼優惠券選擇A執行計劃而不選擇B執行計劃。

5、確定問題並採用相應的措施

案例1、最左匹配

索引

SQL語句

查詢匹配從左往右匹配，要使用order_no走索引，必須查詢條件攜帶shop_id或者索引( shop_id , order_no )調換前後順序

案例2、隱式轉換

索引

SQL語句

隱式轉換相當於在索引上做運算，會讓索引失效。mobile是字元類型，使用了數字，應該使用字元串匹配，否則MySQL會用到隱式替換，導致索引失效。

案例3、大分頁

索引

SQL語句

對於大分頁的場景，可以優先讓產品優化需求，如果沒有優化的，有如下兩種優化方式， 一種是把上一次的最後一條數據，也即上面的c傳過來，然後做「c < xxx」處理，但是這種一般需要改介面協議，並不一定可行。另一種是採用延遲關聯的方式進行處理，減少SQL回表，但是要記得索引需要完全覆蓋才有效果，SQL改動如下

案例4、in + order by

索引

SQL語句

in查詢在MySQL底層是通過n*m的方式去搜索，類似union，但是效率比union高。in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_pe_limit)，5.6之後超過這個臨界值後該列的cost就不參與計算了。因此會導致執行計劃選擇不準確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不準確。

處理方式，可以( order_status , created_at )互換前後順序，並且調整SQL為延遲關聯。

案例5、范圍查詢阻斷，後續欄位不能走索引

索引

SQL語句

范圍查詢還有「IN、between」

案例6、不等於、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、>、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、優化器選擇不使用索引的情況

如果要求訪問的數據量很小，則優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的蠻大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找數據。

查詢出所有未支付的訂單，一般這種訂單是很少的，即使建了索引，也沒法使用索引。

案例8、復雜查詢

如果是統計某些數據，可能改用數倉進行解決；如果是業務上就有那麼復雜的查詢，可能就不建議繼續走SQL了，而是採用其他的方式進行解決，比如使用ES等進行解決。

案例9、asc和desc混用

desc 和asc混用時會導致索引失效

案例10、大數據

對於推送業務的數據存儲，可能數據量會很大，如果在方案的選擇上，最終選擇存儲在MySQL上，並且做7天等有效期的保存。那麼需要注意，頻繁的清理數據，會照成數據碎片，需要聯系DBA進行數據碎片處理。

F. sql 如何用臨時表優化性能

InnoDB 類型的臨時表存在的潛在問題

盡管使用 InnoDB 是性能最佳的，但可能會出現新的潛在問題。在某些特定情況下，您可能會出現磁碟耗盡和伺服器中斷。

與資料庫中的任何其他 InnoDB 表一樣，臨時表具有自己的表空間文件。新文件與通用表空間一起位於數據目錄中，名稱為 ibtmp1。它存儲所有 tmp 表。不運行手動運行 OPTIMIZE TABLE，表空間文件就會不斷增長。如果你不能使用 OPTIMIZE，那麼唯一能將 ibtmp1 大小縮小為零的方法，就是重新啟動伺服器。幸運的是，即使文件無法減小，在執行查詢後，臨時表也會自動刪除，表空間可回收使用。現在，我們想一想以下情境：

• 存在未優化的查詢，需要在磁碟上創建非常大的的臨時表

• 存在優化的查詢，但他們正在磁碟上創建非常大的臨時表，因為你正在對此數據集進行計算（統計，分析）

• 高並發連接時，運行相同的查詢，伴隨臨時表的創建

• 沒有很多可用空間

• 在這些情況下，文件 ibtmp1 大大增加，很容易耗盡可用空間。這種情況每天發生幾次，並且必須重啟伺服器才能完全縮小 ibtmp1 表空間。使用不可收縮的文件可以輕松耗盡磁碟空間！

那麼，如何避免磁碟耗盡和中斷呢？




簡單的解決方案：使用更大的磁碟

• 雖然可以暫時解決問題，但這不是最佳解決方案。實際上，您可以通過逐步增加磁碟大小，來猜測具體需要的空間。如果環境位於雲中，或者在非常大的虛擬平台，這很容易實現。但是使用這種解決方案，您可能會面臨不必要的開支。您還可以通過設置以下配置變數將 ibtmp1 文件移動到專用大型磁碟上： [mysqld] innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend



需要重啟 MySQL 。注意，必須將路徑指定為相對於數據目錄。

設置 ibtmp1 大小的上限

• 例如： [mysqld] innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G



在這種情況下，文件不能超過 10GB。可以降低宕機概率，但也是一個危險的解決方案。當數據文件達到最大值時，會查詢失敗並顯示一個錯誤，提示表已滿。



• 退回 MyISAM 將臨時表存儲在磁碟上

這個解決方案似乎違反直覺，但它可能是快速避免中斷的最佳方法，並保證使用所有需要的臨時表。

• internal_tmp_disk_storage_engine = MYISAM


• 由於變數是動態的，您也可以在運行時設置它： SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;


• 回到 MyISAM，您將大大降低寫滿磁碟空間的可能性。實際上，臨時表將創建到不同的文件中，並在查詢結束時立即刪除。

雖然總是有可能看到相同的問題，以防你可以在同一時間運行查詢或非常接近。在我的實際案例中，這是避免所有中斷的解決方案。

優化你的查詢

• 在將存儲引擎退回到 MyISAM 以減輕中斷發生後，必須花時間分析查詢。目標是減小磁碟上臨時表的大小。本文的目的不是解釋如何調查查詢，而是可以依賴慢速日誌，像 pt-query-digest 和 EXPLAIN 這樣的工具。一些技巧：

• 在表上創建缺少的索引

• 如果不需要，可以在查詢中添加更多過濾條件以更少收集的數據

• 重寫查詢以優化執行計劃

• 可以在應用程序中使用隊列管理器來序列化它們的執行或減少並發性

但希望在所有優化之後，您可以返回將臨時存儲引擎設置為 InnoDB 以獲得更好的性能。

結論

有時這些改進會產生意想不到的副作用。用於磁碟上臨時表的 InnoDB 存儲引擎是一個很好的改進，但在某些特定情況下，例如，如果您有未優化查詢和很少的可用空間，則可能因「磁碟已滿」錯誤而中斷。將 tmp 存儲引擎退回到 MyISAM 是避免中斷的最快方法，但是為了返回到 InnoDB，查詢的優化是更重要的事情。更大或專用的磁碟也可能有所幫助。但這是一個微不足道的建議。

G. 數據分析課程筆記 - 19 - HiveSQL 常用優化技巧

大家好呀，這節課學習 HiveSQL 的常用優化技巧。由於 Hive 主要用來處理非常大的數據，運行過程由於通常要經過 MapRece 的過程，因此不像 MySQL 一樣很快出結果。而使用不同方法寫出來的 HiveSQL 語句執行效率也是不一樣的，因此為了減少等待的時間，提高伺服器的運行效率，我們需要在 HiveSQL 的語句上進行一些優化。

本節課的主要內容 ：

引言
1、技巧一：列裁剪和分區裁剪
（1）列裁剪
（2）分區裁剪
2、技巧二：排序技巧——sort by代替order by
3、技巧三：去重技巧——用group by來替換distinct
4、技巧四：聚合技巧——grouping sets、cube、rollup
（1）grouping sets
（2）cube
（3）rollup
5、技巧五：換個思路解題
6、技巧六：union all時可以開啟並發執行
7、技巧七：表連接優化
8、技巧八：遵循嚴格模式

Hive 作為大數據領域常用的數據倉庫組件，在平時設計和查詢時要特別注意效率。影響Hive效率的幾乎從不是數據量過大，而是數據傾斜、數據冗餘、job 或 I/O 過多、MapRece 分配不合理等等。對 Hive 的調優既包含對HiveSQL 語句本身的優化，也包含 Hive 配置項和 MR 方面的調整。

列裁剪就是在查詢時只讀取需要的列。當列很多或者數據量很大時，如果select 所有的列或者不指定分區，導致的全表掃描和全分區掃描效率都很低。Hive中與列裁剪優化相關的配置項是 hive.optimize.cp ，默認是 true 。

分區裁剪就是在查詢時只讀需要的分區。Hive中與分區裁剪優化相關的則是 hive.optimize.pruner ，默認是 true 。

HiveSQL中的 order by 與其他 SQL 語言中的功能一樣，就是將結果按某個欄位全局排序，這會導致所有map端數據都進入一個 rece 中，在數據量大時可能會長時間計算不完。

如果使用 sort by ，那麼就會視情況啟動多個 recer 進行排序，並且保證每個 recer 內局部有序。為了控制 map 端數據分配到 rece 的 key，往往還要配合 distribute by 一同使用。如果不加 distribute by 的話，map 端數據就會隨機分配給 recer。

這里需要解釋一下， distribute by 和 sort by 結合使用是如何相較於 order by 提升運行效率的。

假如我們要對一張很大的用戶信息表按照年齡進行分組，優化前的寫法是直接 order by age 。使用 distribute by 和 sort by 結合進行優化的時候， sort by 後面還是 age 這個排序欄位， distribute by 後面選擇一個沒有重復值的均勻欄位，比如 user_id 。

這樣做的原因是，通常用戶的年齡分布是不均勻的，比如20歲以下和50歲以上的人非常少，中間幾個年齡段的人又非常多，在 Map 階段就會造成有些任務很大，有些任務很小。那通過 distribute by 一個均勻欄位，就可以讓系統均勻地進行「分桶」，對每個桶進行排序，最後再組合，這樣就能從整體上提升 MapRece 的效率。

取出 user_trade 表中全部支付用戶：

原有寫法的執行時長：

優化寫法的執行時長：

考慮對之前的案例進行優化：

注意： 在極大的數據量（且很多重復值）時，可以先 group by 去重，再 count() 計數，效率高於直接 count(distinct **) 。

如果我們想知道用戶的性別分布、城市分布、等級分布，你會怎麼寫？

通常寫法：

缺點 ：要分別寫三次SQL，需要執行三次，重復工作，且費時。

那該怎麼優化呢？

注意 ：這個聚合結果相當於縱向地堆在一起了（Union all），分類欄位用不同列來進行區分，也就是每一行數據都包含 4 列，前三列是分類欄位，最後一列是聚合計算的結果。

GROUPING SETS() ：在 group by 查詢中，根據不同的維度組合進行聚合，等價於將不同維度的 group by 結果集進行 union all。聚合規則在括弧中進行指定。

如果我們想知道用戶的性別分布以及每個性別的城市分布，你會怎麼寫？

那該怎麼優化呢？

注意： 第二列為NULL的，就是性別的用戶分布，其餘有城市的均為每個性別的城市分布。

cube：根據 group by 維度的所有組合進行聚合

注意 ：跑完數據後，整理很關鍵！！！

rollup：以最左側的維度為主，進行層級聚合，是cube的子集。

如果我想同時計算出，每個月的支付金額，以及每年的總支付金額，該怎麼辦？

那應該如何優化呢？

條條大路通羅馬，寫SQL亦是如此，能達到同樣效果的SQL有很多種，要學會思路轉換，靈活應用。

來看一個我們之前做過的案例：

有沒有別的寫法呢？

Hive 中互相沒有依賴關系的 job 間是可以並行執行的，最典型的就是
多個子查詢union all。在集群資源相對充足的情況下，可以開啟並
行執行。參數設置： set hive.exec.parallel=true;

時間對比：

所謂嚴格模式，就是強制不允許用戶執行3種有風險的 HiveSQL 語句，一旦執行會直接報錯。

要開啟嚴格模式，需要將參數 hive.mapred.mode 設為 strict 。

好啦，這節課的內容就是這些。以上優化技巧需要大家在平時的練習和使用中有意識地去注意自己的語句，不斷改進，就能掌握最優的寫法。