做數據倉庫的頭兩年，使用高配置單機 + 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倍左右了。

來自：http://my.oschina.net/leejun2005/blog/308242