mysql分區技術

標籤：

1. 背景介紹
當 MySQL中一個表的總記錄數超過了1000萬後，會出現效能的大幅度下降嗎？答案是肯定的，但是效能下降的比率不一而同，要看系統的架構、應用程式，甚至還要根據索引、伺服器硬體等多種因素而定。比如FCDB和SFDB中的關鍵詞，多達上億的資料量，分表之後的單個表也已經突破千萬的資料量，導致單個表的更新等均影響著系統的運行效率。甚至是一條簡單的SQL都有可能壓垮整個資料庫，如整個表對某個欄位的排序操作等。目前，針對海量資料的最佳化主要有2中方法：大表拆小表的方式和SQL語句的最佳化。SQL語句的最佳化可以通過增加索引等來調整，但是資料量的增大將會導致索引的維護代價增大。在此不詳述，建議大家參考相應的High Performance MySQL等書籍。另外，大表拆小表的方式主要有兩種：
垂直分表：

圖1. 垂直資料分割
對於垂直分表，它將一個N1+N2個欄位的表Tab拆分成N1欄位的子表Tab1和(N2+1)欄位的子表Tab2；其中子表Tab2包含了關於子表Tab1的主鍵資訊，否則兩個表的關聯關係就會丟失。當然垂直分表會帶來程式端SQL的修改，若是應用程式已經應用很長的一段時間，然後程式的升級將是耗時而且易出錯的，即升級的代價將會很大。
水平分表：

圖2. 水平資料分割
水平資料分割技術將一個表拆成多個表，比較常用的方式是將表中的記錄按照某種Hash演算法進行拆分，簡單的拆分方法如模數方式。同樣，這種分區方法也必須對前端的應用程式中的SQL進行修改方可使用。而且對於一個SQL，它可能會修改兩個表，那麼你必須得寫成2個SQL語句從而可以完成一個邏輯的事務，使得程式的判斷邏輯越來越複雜，這樣也會導致程式的維護代價高，也就失去了採用資料庫的優勢。因此，分區技術可以有力地避免如上的弊端，成為解決海量資料存放區的有力方法。2. MySQL分區介紹
MySQL的分區技術不同與之前的分表技術，它與水平分表有點類似，但是它是在邏輯層進行的水平分表，對與應用程式而言它還是一張表。
2.1 MySQL分區類型
MySQL5.1有5中分區類型：
RANGE 分區：基於屬於一個給定連續區間的列值，把多行分配給分區；
LIST 分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇；
HASH分區：基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效、產生非負整數值的任何錶達式。
KEY 分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL 伺服器提供其自身的雜湊函數。
2.2 RANGE分區
對於RANGE分區，舉個例子：
例1. 假定你建立了一個如下的一個表，該表儲存有20家音像店的職員記錄，這20家音像店的編號從1到20。 如果你想將其分成4個小分區，那麼你可以採用RANGE分區，建立的資料庫表如下：

這個例子，它的key是一個整型的資料，那是否對於其它類型的欄位就無法作為key呢？　　答案是否定的，例子2說明這種情況。
例2. 假定你建立了一個如下的一個表，該表儲存有20家音像店的職員記錄，這20家音像店的編號從1到20。你想把不同時期離職的員工進行分別儲存，那麼你可以將日期欄位separated（即離職時間）作為一個key，建立的SQL語句如下：

這樣你就可以對一個日期類型的欄位調用mysql的日期函數YEAR()轉換為一種整數類型，從而可以作為RANGE分區的key。這個時候，你可以看到，按照分區後的物理檔案是相對獨立的：

可知，每個分區有自己獨立的資料檔案和索引檔案，這是為什麼你對某一個查詢，它只會訪問它需要訪問的資料區塊，而不訪問根本不是結果的物理塊，從而可以大大提高系統的效率。2.3 LIST分區
LIST分區與RANGE分區有類似的地方，舉個與例1類似的例子如下：
例3. 假定你建立了一個如下的一個表，該表儲存有20家音像店的職員記錄，這20家音像店的編號從1到20。 而這20個音像店，分布在4個有經銷權的地區，如下表所示：
地區 商店識別碼
北區 3, 5, 6, 9, 17
東區 1, 2, 10, 11, 19, 20
西區 4, 12, 13, 14, 18
中心區 7, 8, 15, 16
那麼你可以採用如下的LIST分區語句建立資料表：

同樣，它在物理檔案上也會標識不同的分區：

2.4 HASH分區
HASH分區主要用來確保資料在預先確定數目的分區中平均分布。它可以基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。
例4. 假定你建立了一個如下的一個表，該表儲存有20家音像店的職員記錄，這20家音像店的編號從1到20。你想把不同時期加入的員工進行分別儲存，那麼你可以將日期欄位hired（即離職時間）作為一個key，建立的SQL語句如下：

那麼要插入一個‘2005-09-15’入職的員工E1，那麼按照模數函數會將其放置到第2分區中：

MOD(YEAR(‘2005-09-01’), 4)= MOD(2005,4)= 1 //即第2分區

2.5 KEY分區

與HASH分區類似，但它的key可以不是整數類型，如字串等類型的欄位。MySQL 簇（Cluster）使用函數MD5()來實現KEY分區；對於使用其他儲存引擎的表，伺服器使用其自己內部的雜湊函數，這些函數是基於與 PASSWORD()一樣的運演算法則。
2.6 不同分區技術的對比
如上分別列出了不同的分區技術，接下來進行對比，如下表所示：
分區類型 優點 缺點 共性
Range 適合與日期類型，支援複合分區 有限的分區 一般只針對某一列
List 適合與有固定取值的列，支援複合分區 有限的分區，插入記錄在這一列的值不值List中，則資料丟失 一般只針對某一列
Hash 線性Hash使得增加、刪除和合并分區更快捷 線性Hash的資料分布不均勻，而一般Hash的資料分布較均勻 一般只針對某一列
Key 列可以為字元型等其它非Int類型 效率較之前的低，因為函數為複雜的函數(如. MD5或SHA函數) 一般只針對某一列
3. 案例分析
這個案例是針對有個員工、部門、部門經理、頭銜和銷售記錄的類比資料，其ER圖如下所示，資料量大概有4百萬左右。資料下載URL: https://launchpad.net/test-db


通過如上可知，對於同樣的資料按照分區和不分區的技術分別儲存，從而便於如下的查詢效能分析和對比。　　對於salaries表，它採用RANGE分區，定義如下：

3.1 單表查詢
從銷售記錄中找到1999年整年的銷售記錄有多少條，這個很簡單，查詢語句如下：
select count(*) from salaries s where s.from_date between "1999-01-01" and "1999-12-31" ;
那麼對於分區前後的查詢效能卻有很大的差別：

通過如上可知，利用分區之後它只需掃描p16分區，訪問的記錄明顯減少，所以效能自然有較大的提升：

無採用分區技術 採用分區技術
3.2 單表查詢-BAD Case
若現在有如下查詢：
select count(*) from salaries s where year(s.from_date)=1999;
那麼它是否能夠利用到分區技術呢，答案是否定的。為什麼呢，因為分區中的key是s.from_date，而不是 year(s.from_date)，mysql並不能很智能地判斷year是1999的，那麼它就是分為p16分區，這個可以通過如下的查詢計劃可以證 實：

也就是其實它訪問了所有的分區，所以並沒有很好地利用資料分割函數，將SQL改寫如下：
select count(*) from salaries s where year between ‘1999-01-01‘ and ‘1999-12-31‘ ;
則查詢計劃如下：

可知，書寫正確的SQL可以完全表現出兩種相差特別大的效能。

3.3 串連查詢
同樣地，對於串連查詢，在有沒有分區的條件下，將有效能3倍左右的差距。對於更大的資料量，可能會有更大的效能差距。SQL如下：
select count(*) from salaries s left join employees e on s.emp_no=e.emp_no where s.from_date between ‘1999-01-01‘ and ‘1999-12-31‘ ;

無採用分區 採用分區
3.4 刪除查詢
為了刪除1998年的銷售資料，那麼在有分區情況下可以不利用delete查詢快速地完成垃圾資料的清理。

可知，對於有分區的情況下，只需要將某個分區刪除掉即可，時間僅為0.05s，相對應原來的2.82s，這個提升是非常高的。　　　　當然，利用資料分割函數的資料刪除之後，資料檔案如下：

那麼接下來如果接著插入1998年的資料，資料是否丟失了呢？還是會寫不進去？答案也都是否定，它會將資料寫入p16分區中。有興趣的讀者可以自己收到試試。
4. 總結和不足
所以，分區的好處有很多：
1. 與單個磁碟或檔案系統分區相比，可以儲存更多的資料
2. 對於那些已經失去儲存意義的資料，通常可以通過刪除與那些資料有關的分區，很容易地刪除那些資料。
3. 一些查詢可以得到極大的最佳化,如where語句資料可以只儲存在一個或多個分區內
4. 涉及到例如SUM() 和 COUNT()這樣彙總函式的查詢，可以很容易地進行平行處理
5. 通過跨多個磁碟來分散資料查詢，來獲得更大的查詢輸送量
在設計分區過程中，需要考慮的因素有很多，如：
– 分區的列
– 分區使用的函數，特別為非Integer類型的列
– 伺服器效能
– 記憶體大小
根據分區技術，有一些技巧：
– 若索引的大小 > RAM，考慮選用分區，不採用索引
– 盡量不採用Primary Key做分區的key
– 當CPU效能高的時候，考慮使用Archive儲存引擎
– 對於大量的曆史資料，考慮使用Archive+PARTITION
–總之，
MySQL分區技術是一種邏輯的水平分表技術；
它只訪問需要訪問的分區，從而提高效能；
支援range, hash, key, list和複合分區方法；
支援MySQL伺服器所支援的任何儲存引擎；
除了Key分區方法，Partition的key 必須是整數(或者能轉化成整數)。

mysql分區技術