資料倉儲中的 SQL 效能最佳化（MySQL篇）

標籤：des   style   blog   http   color   os   使用   io   java   

做資料倉儲的頭兩年，使用高配置單機 + MySQL的方式來實現所有的計算（包括資料的ETL，以及報表計算。沒有OLAP）。用過MySQL內建的MYISAM和列儲存引擎Infobright。這篇文章總結了自己和團隊在那段時間碰到的一些常見效能問題和解決方案。

P.S.如果沒有特別指出，下面說的mysql都是指用MYISAM做儲存引擎。

1. 利用已有資料避免重複計算

業務需求中往往有計算一周/一個月的某某資料，比如計算最近一周某個特定頁面的PV/UV。這裡出現的問題就是實現的時候直接取整周的日誌資料，然後進行計算。這樣其實就出現了重複計算，某一天的資料在不同的日子裡被重複計算了7次。

解決辦法非常之簡單，就是把計算進行切分，如果是算PV，做法就是每天算好當天的PV，那麼一周的PV就把算好的7天的PV相加。如果是算UV，那麼每天從日誌資料取出相應的訪客資料，把最近七天的訪客資料單獨儲存在一個表裡面，計算周UV的時候直接用這個表做計算，而不需要從原始日誌資料中抓上一大把資料來算了。

這是一個非常簡單的問題，甚至不需要多少SQL的知識，但是在開發過程中往往被視而不見。這就是只實現業務而忽略效能的表現。從小規模資料倉儲做起的工程師，如果缺乏這方面的意識和做事規範，就容易出現這種問題，等到資料倉儲的資料量變得比較大的時候，才會發現。需求決定能力。

2 . case when 關鍵字的使用方法

case when這個關鍵字，在做彙總的時候，可以很方便的將一份資料在一個SQL語句中進行分類的統計。舉個例子，比如下面有一張成績表(表名定為scores)：

 

現在需要統計小張的平均成績，小明的平均成績和小明的語文成績。也就是最終結果應該是：

SQL實現如下：

如果現在這個成績表有1200萬條資料，包含了400萬的名字 * 3個科目，上面的計算需要多長時間？我做了一個簡單的測試，答案是5.5秒。

而如果我們在name列上面加了索引，並且把sql改成下面的寫法：

這樣的話，只需要0.05秒就能完成。

那麼如果有索引的話，前面的一種實現方法會不會變快？答案是不會，時間還是跟原來一樣。

而如果沒有索引，後面一種寫法會用多少時間？測試結果是3.3秒。

把幾種情況再理一遍：

之所以後面一種寫法總是比前面一種寫法快，不同之處就在於是否先在where裡面把資料過濾掉。用where有兩個好處：一個是有索引的話就能使用，而case when很有可能用不到索引（關於索引的具體使用這裡就不詳細解釋了，至少在這個例子中前一種寫法沒有用到索引），第二是能夠提前過濾資料，哪怕沒有索引，前一種寫法掃描了三遍全表的資料（做一個case when掃一遍），後面的寫法掃描一遍全表，把資料過濾了之後，case when就不用過這麼多資料量了。

而實際情況是，開發經常只是為了實現功能邏輯，而習慣了在case when中限制條件取資料。這樣在出現類似例子中的需求時，沒有把應該限制的條件寫到where裡面。這是在實際代碼中發現最多的一類問題。

3 . 分頁取數方式

在資料倉儲中有一個重要的基礎步驟，就是對資料進行清洗。比如資料來源的資料如果以JSON方式儲存，在mysql的資料倉儲就必須將json中需要的欄位提取出來，做成單獨的表欄位。這個步驟用sql直接處理很麻煩，所以可以用主流程式設計語言（比如java）的json庫進行解析。解析的時候需要讀取資料，一次性讀取進來是不可能的，所以要分批讀取（相當於分頁了）。

最初的實現方式就是標記住每次取資料的位移量，然後一批批讀取：

這樣的代碼，在開始幾句sql的時候執行速度還行，但是到後面會越來越慢，因為每次要讀取大量資料再丟棄，其實是一種浪費。

高效的實現方式，可以是用表中的主鍵進行分頁。如果資料是按照主鍵排序的，那麼可以是這樣（這麼做是要求主鍵的取值序列是連續的。假設主鍵的取值序列我們比較清楚，是從10001-1000000的連續值）：

就算資料不是按主鍵排序的，也可以通過限制主鍵的範圍來分頁。這樣處理的話，主鍵的取值序列不連續也沒有太大問題，就是每次拿到的資料會比理想中的少一些，反正是用在資料處理，不影響正確性：

這樣的話，由於主鍵上面有索引，取資料速度就不會受到資料的具體位置的影響了。

4 . 索引使用

索引的使用是關聯式資料庫的SQL最佳化中一個非常重要的主題，也是一個常識性的東西。但是工程師在實際開發中往往是加完索引就覺得萬事大吉了，也不去檢查索引是否被正確的使用了，所以還是簡單的提一下關於索引的案例。

還是舉例說明。假如有一個電商網站，積累了某一天的訪問日誌表item_visits，每條記錄表示某一個商品（item）被訪問了一次，包括訪問者的一些資訊，比如使用者的id，暱稱等等，有1200多萬條資料。樣本如下：

商品本身有一個商品表items，包含800多種商品，表名了商品名字和所屬種類：

現在要計算每個商品種類（item_type）被訪問的次數。sql的實現不難：

然後既然是join，那麼在join key上需要加索引。這時候有的工程師就隨手在items的item_id上面加了索引。跑了一下，需要95秒。（p.s.在我的測試情境中，這個日誌表有20多個欄位，所以雖然這個表的記錄數跟問題2中的那個表的記錄數差不多，但是大小會差很多，瞭解這個背景可以解釋這裡的計算用時為什麼會遠遠超過問題2中的用時。）

前面說是隨手加的索引，其實就已經在暗示加的有問題。那我們在item_visit的item_id上面再加個索引，需要跑多久？80秒。

用explain查一下執行計畫：

注意到這裡是以日誌表作為驅動表的（即從日誌表開始掃描資料，而商品表是nest loop的內層嵌套），這樣的話兩個表的item_id都用到了，商品表的索引做join，日誌表的索引可以做覆蓋索引（這個覆蓋索引就是比前面快的原因）。看上去挺“划算”的，實際上由於放棄了item小表驅動，速度反而慢了很多。

接下來用straight_join的串連方式把這個sql強制改成小表驅動：

再來看執行計畫：

雖然這樣一來商品表的索引就用不到了，但是這其實是正確的做法（當然如果條件允許，也未必要用straight join，把商品表上的索引去掉其實是最合理的做法，這樣mysql就會自己選擇正確的執行計畫了。），測試下來只需要8秒。原因就在於大表驅動時，根據標準的Block Nested Loop Join演算法，小表的資料會被反覆迴圈讀取。當然實際上小表是可以進cache而不用重複讀取的，但是由於mysql只認索引有沒有用上，所以還是會反覆讀取小表（這個問題在這個slides的35頁也有描述）。而如果小表驅動，就不會有這個問題。

後續更新：嚴格來說，這個情境有一個限制條件，就是大表中的商品item_id只佔全部item_id的一部分。如果大表中的商品item_id幾乎均勻覆蓋所有item_id，那麼無論join時用哪個表的索引，其實已耗用時間都差不多。原來做實驗的時候忽視了這一點，後來重新嘗試的時候發現了這個問題。特此補充。

小結一下：這裡說了兩個問題，一個是添加索引的時候需要想想如何去加，在不是很肯定的時候可以看看執行計畫，而不是教條式的知道“join要加索引”。學習sql最佳化切忌只是背幾個tips。另外就是mysql在選擇執行計畫的時候也不一定能夠做到最好，如果發現mysql的執行計畫有很大問題，那麼就需要工程師進行調整，mysql中一樣有類似oracle中的hint協助我們達到想要的目的，就像例子中的straight_join。

5. 過多的 join

在mysql中，需要join的表如果太多，會對效能造成很顯著的下降。同樣，舉個例子來說明。

首先產生一個表（命名為test），這個表只有60條記錄，6個欄位，其中第一個欄位為主鍵：

然後做一個查詢：

也就是說讓test表跟自己關聯。計算的結果顯然是60，而且幾乎不費時間。

但是如果是這樣的查詢（十個test表關聯），會花費多少時間？

答案是：肯定超過5分鐘。因為做了實際測試，5分鐘還沒有出結果。這裡的測試為了方便起見，用了一個表自己關聯10次，實際上如果是不同的表，效果也是一樣的。

那麼mysql到底在幹什麼呢？用show processlist去看一下運行時情況：

原來是處在statistics的狀態。這個狀態，根據mysql的解釋是在根據統計資訊去產生執行計畫，當然這個解釋肯定是沒有追根溯源。實際上mysql在產生執行計畫的時候，其中有一個步驟，是確定表的join順序。預設情況下，mysql會把所有join順序全部排列出來，依次計算各個join順序的執行代價並且取最優的那個。這樣一來，n個表join會有n!種情況。十個表join就是10！，大概300萬，所以難怪mysql要分析半天了。

而在實際開發過程中，曾經出現過30多個表關聯的情況（有10^32種join順序）。一旦出現，花費在statistics狀態的時間往往是在1個小時以上。這還只是在表資料量都非常小，需要做順序分析的點比較少的情況下。至於出現這種情況的原因，無外乎我們需要計算的匯總報表的欄位太多，需要從各種各樣的地方計算出來資料，然後再把資料拼接起來，報表在維護過程中不斷添加欄位，又由於種種原因沒有去掉已經廢棄的欄位，這樣欄位必定會越來愈多，實現這些欄位計算就需要用更多的臨時計算結果表去關聯到一起，結果需要關聯的表也越來越多，成了mysql無法承受之重。

這個問題的解決方案有兩個。從開發角度來說，可以控制join的表個數。如果需要join的表太多，可以根據業務上的分類，先做一輪join，把表的數量控制在一定範圍內，然後拿到第一輪的join結果，再做第二輪全域join，這樣就不會有問題了。從營運角度來說，可以設定optimizer_search_depth這個參數。它能夠控制join順序遍曆的深度，進行貪婪搜尋得到局部最優的順序。一般有好多個表join的情況，都是上面說的相同維度資料需要拼接成一張大表，對於join順序基本上沒什麼要求。所以適當的把這個值調低，對於效能應該說沒有影響。

6. 列儲存引擎 Infobright

Infobright是基於mysql的儲存引擎，具有列儲存/列壓縮和知識網格等特性，比較適合資料倉儲的計算。使用起來也不需要考慮索引之類的問題，非常方便。不過經過一段時間的運用，也發現了個別需要注意的問題。

一個問題和MYISAM類似，不要取不需要的資料。這裡說的不需要的資料，包括不需要的列（Infobright的使用常識。當然行儲存也要注意，只不過影響相對比較小，所以沒有專門提到），和不需要的行（行數是可以擴充的，行儲存一行基本上都能存在一個儲存單元中，但是列儲存一列明顯不可能存在一個儲存單元中）。

第二個問題，就是Infobright在長字元檢索的時候並不給力。一般來說，網站的訪問日誌中會有URL欄位用來標識訪問的具體地址。這樣就有尋找特定URL的需求。比如我要在cnblog的訪問日誌中尋找到我的blog的訪問次數：

類似這樣在一個長字串裡面檢索子串的需求，Infobright的執行時間測試下來是mysql的1.5-3倍。

至於速度慢的原因，這裡給出一個簡要的解釋：Infobright作為列式資料庫使用了列儲存的常用特性，就是壓縮（列式資料庫的壓縮率一般要能做到10%以內，Infobright也不例外）。另外為了加快尋找速度，它還使用了一種叫知識網格檢索方式，一般情況下能夠極大的減少需要讀取的資料量。關於知識網格的原理已經超出了本篇文章的討論篇幅，可以看這裡瞭解。但是在查詢url的時候，知識網格的優點無法體現出來，但是使用知識網格本身帶來的檢索代價和解壓長字串的代價卻仍然存在，甚至比查詢一般的數字類欄位要來的大。

然後根據其原理可以給出一個能夠說明問題的解決方案（雖然實用度不算高）：如果整個表裡面就有一個長字串欄位查詢起來比較麻煩，可以把資料根據這個欄位排序後再匯入。這樣一來按照該欄位查詢時，通過知識網格就能夠屏蔽掉比較多的“資料包”（Infobright的資料壓縮單元），而未排序的情況下合格資料散布在各個“資料包”中，其解壓工作量就大得多了。使用這個方法進行查詢，測試下來其執行時間就只有mysql的0.5倍左右了。

資料倉儲中的 SQL 效能最佳化（MySQL篇）