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個有經銷權的地區，如下表所示：
地區 商店ID 號
北區 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函數) 一般只針對某一列

1. 案例分析
   這個案例是針對有個員工、部門、部門經理、頭銜和銷售記錄的模擬數據，其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分區中。有興趣的讀者可以自己收到試試。
   
2. 總結和不足
   所以，分區的好處有很多：
   
3. 與單個磁盤或文件系統分區相比，可以存儲更多的數據
   
4. 對于那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。
   
5. 一些查詢可以得到極大的優化,如where語句數據可以只保存在一個或多個分區內
   
6. 涉及到例如SUM() 和 COUNT()這樣聚合函數的查詢，可以很容易地進行并行處理
   
7. 通過跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量
   在設計分區過程中，需要考慮的因素有很多，如：
   – 分區的列
   – 分區使用的函數，特別為非Integer類型的列
   – 服務器性能
   – 內存大小
   根據分區技術，有一些技巧：
   – 若索引的大小 > RAM，考慮選用分區，不采用索引
   – 盡量不采用Primary Key做分區的key
   – 當CPU性能高的時候，考慮使用Archive存儲引擎
   – 對于大量的歷史數據，考慮使用Archive+PARTITION
   –總之，
   MySQL分區技術是一種邏輯的水平分表技術；
   它只訪問需要訪問的分區，從而提高性能；
   支持range, hash, key, list和復合分區方法；
   支持MySQL服務器所支持的任何存儲引擎；
   除了Key分區方法，Partition的key 必須是整數(或者能轉化成整數)。