MySQL 分割槽表原理及使用詳解

今天統計資料的時候發現一張表使用了表分割槽，藉此機會記錄一下。

1. 什麼是表分割槽？

表分割槽，是指根據一定規則，將資料庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分割槽組成。

2. 表分割槽與分表的區別

分表：指的是通過一定規則，將一張表分解成多張不同的表。比如將使用者訂單記錄根據時間成多個表。 分表與分割槽的區別在於：分割槽從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。

3. 表分割槽有什麼好處？

1）分割槽表的資料可以分佈在不同的物理裝置上，從而高效地利用多個硬體裝置。 2）和單個磁碟或者檔案系統相比，可以儲存更多資料 3）優化查詢。在where語句中包含分割槽條件時，可以只掃描一個或多個分割槽表來提高查詢效率；涉及sum和count語句時，也可以在多個分割槽上並行處理，最後彙總結果。 4）分割槽表更容易維護。例如：想批量刪除大量資料可以清除整個分割槽。 5）可以使用分割槽表來避免某些特殊的瓶頸，例如InnoDB的單個索引的互斥訪問，ext3問價你係統的inode鎖競爭等。

4. 分割槽表的限制因素

1）一個表最多隻能有1024個分割槽 2） MySQL5.1中，分割槽表示式必須是整數，或者返回整數的表示式。在MySQL5.5中提供了非整數表示式分割槽的支援。 3）如果分割槽欄位中有主鍵或者唯一索引的列，那麼多有主鍵列和唯一索引列都必須包含進來。即：分割槽欄位要麼不包含主鍵或者索引列，要麼包含全部主鍵和索引列。 4）分割槽表中無法使用外來鍵約束 5）MySQL的分割槽適用於一個表的所有資料和索引，不能只對表資料分割槽而不對索引分割槽，也不能只對索引分割槽而不對錶分割槽，也不能只對表的一部分資料分割槽。

5. 如何判斷當前MySQL是否支援分割槽？

命令：show variables like '%partition%' 執行結果:

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

have_partintioning 的值為YES，表示支援分割槽。

6. MySQL支援的分割槽型別有哪些？

1）RANGE分割槽：按照資料的區間範圍分割槽 2）LIST分割槽：按照List中的值分割槽，與RANGE的區別是，range分割槽的區間範圍值是連續的。 3）HASH分割槽 4）KEY分割槽 說明 在MySQL5.1版本中，RANGE,LIST,HASH分割槽要求分割槽鍵必須是INT型別，或者通過表示式返回INT型別。但KEY分割槽的時候，可以使用其他型別的列（BLOB，TEXT型別除外）作為分割槽鍵。

7. Range分割槽

利用取值範圍進行分割槽，區間要連續並且不能互相重疊。 語法：

partition by range(exp)( //exp可以為列名或者表示式，比如to_date(created_date)
    partition p0 values less than(num)
)

例如：

mysql> create table emp(
    -> id INT NOT null,
    -> store_id int not null
    -> )
    -> partition by range(store_id)(
    ->   partition p0 values less than(10),
    ->   partition p1 values less than(20)
    -> );

上面的語句建立了emp表，並根據store_id欄位進行分割槽，小於10的值存在分割槽p0中，大於等於10，小於20的值存在分割槽p1中。 注意 每個分割槽都是按順序定義的，從最低到最高。上面的語句，如果將less than(10) 和less than (20)的順序顛倒過來，那麼將報錯，如下：

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

RANGE分割槽存在的問題

1. range範圍覆蓋問題：當插入的記錄中對應的分割槽鍵的值不在分割槽定義的範圍中的時候，插入語句會失敗。 上面的例子，如果我插入一條store_id = 30的記錄會怎麼樣呢？ 我們上面分割槽的時候，最大值是20，如果插入一條超過20的記錄，會報錯:

   mysql> insert into emp(id,store_id) values(2,30);
   ERROR 1526 (HY000): Table has no partition for value 30

   提示30這個值沒有對應的分割槽。 解決辦法 A. 預估分割槽鍵的值，及時新增分割槽。 B. 設定分割槽的時候，使用values less than maxvalue 子句,MAXVALUE表示最大的可能的整數值。 C. 儘量選擇能夠全部覆蓋的欄位作為分割槽鍵，比如一年的十二個月等。

2. Range分割槽中，分割槽鍵的值如果是NULL，將被作為一個最小值來處理。

8. LIST分割槽

List分割槽是建立離散的值列表告訴資料庫特定的值屬於哪個分割槽。 語法：

   partition by list(exp)( //exp為列名或者表示式
        partition p0 values in (3,5)  //值為3和5的在p0分割槽
    )

與Range不同的是，list分割槽不必生命任何特定的順序。例如：

mysql> create table emp1(
    -> id int not null,
    -> store_id int not null
    -> )
    -> partition by list(store_id)(
    ->   partition p0 values in (3,5),
    ->   partition p1 values in (2,6,7,9)
    -> );

注意 如果插入的記錄對應的分割槽鍵的值不在list分割槽指定的值中，將會插入失敗。並且，list不能像range分割槽那樣提供maxvalue。

9. Columns分割槽

MySQL5.5中引入的分割槽型別，解決了5.5版本之前range分割槽和list分割槽只支援整數分割槽的問題。 Columns分割槽可以細分為 range columns分割槽和 list columns分割槽，他們都支援整數，日期時間，字串三大資料型別。（不支援text和blob型別作為分割槽鍵） columns分割槽還支援多列分割槽（這裡不詳細展開）。

10. Hash分割槽

Hash分割槽主要用來分散熱點讀，確保資料在預先確定個數的分割槽中儘可能平均分佈。 MySQL支援兩種Hash分割槽:常規Hash分割槽和線性Hash分割槽。 A. 常規Hash分割槽:使用取模演算法 語法：

partition by hash(store_id) partitions 4;

上面的語句，根據store_id對4取模，決定記錄儲存位置。 比如store_id = 234的記錄，MOD(234,4)=2,所以會被儲存在第二個分割槽。

常規Hash分割槽的優點和不足 優點：能夠使資料儘可能的均勻分佈。 缺點：不適合分割槽經常變動的需求。假如我要新增加兩個分割槽，現在有6個分割槽，那麼MOD(234,6)的結果與之前MOD(234,4)的結果就會出現不一致，這樣大部分資料就需要重新計算分割槽。為解決此問題，MySQL提供了線性Hash分割槽。

B. 線性Hash分割槽：分割槽函式是一個線性的2的冪的運演算法則。 語法：

partition by LINER hash(store_id) partitions 4;

與常規Hash的不同在於，“Liner”關鍵字。 演算法介紹: 假設要儲存記錄的分割槽編號為N,num為一個非負整數,表示分割成的分割槽的數量，那麼N可以通過以下步驟得到：

Step 1. 找到一個大於等於num的2的冪，這個值為V，V可以通過下面公式得到：
V = Power(2,Ceiling(Log(2,num)))
例如：剛才設定了4個分割槽，num=4，Log(2,4)=2,Ceiling(2)=2,power(2,2)=4,即V=4

Step 2. 設定N=F(column_list)&(V-1)
例如：剛才V=4，store_id=234對應的N值，N = 234&（4-1） =2

Step 3. 當N>=num,設定V=Ceiling(V/2),N=N&(V-1)
例如：store_id=234,N=2<4,所以N就取值2，即可。
假設上面算出來的N=5，那麼V=Ceiling(2.5)=3,N=234&(3-1)=1,即在第一個分割槽。

線性Hash的優點和不足 優點：在分割槽維護（增加，刪除，合併，拆分分割槽）時，MySQL能夠處理得更加迅速。 缺點：與常規Hash分割槽相比，線性Hash各個分割槽之間的資料分佈不太均衡。

11. Key分割槽

類似Hash分割槽，Hash分割槽允許使用使用者自定義的表示式，但Key分割槽不允許使用使用者自定義的表示式。Hash僅支援整數分割槽，而Key分割槽支援除了Blob和text的其他型別的列作為分割槽鍵。 語法:

partition by key(exp) partitions 4;//exp是零個或多個欄位名的列表

key分割槽的時候，exp可以為空，如果為空，則預設使用主鍵作為分割槽鍵，沒有主鍵的時候，會選擇非空惟一鍵作為分割槽鍵。

12. 子分割槽

分割槽表中對每個分割槽再次分割，又成為複合分割槽。

13. 分割槽對於NULL值的處理

MySQ允許分割槽鍵值為NULL，分割槽鍵可能是一個欄位或者一個使用者定義的表示式。一般情況下，MySQL在分割槽的時候會把NULL值當作零值或者一個最小值進行處理。

注意
Range分割槽中：NULL值被當作最小值來處理
List分割槽中：NULL值必須出現在列表中，否則不被接受
Hash/Key分割槽中：NULL值會被當作零值來處理

14. 分割槽管理

分割槽管理包括對於分割槽的增加，刪除，以及查詢。

增加分割槽：

對於Range分割槽和LIst分割槽來說：

alter table table_name add partition (partition p0 values ...(exp))

values後面的內容根據分割槽的型別不同而不同。

對於Hash分割槽和Key分割槽來說：

alter table table_name add partition partitions 8;

上面的語句，指的是新增8個分割槽 。

刪除分割槽

對於Range分割槽和List分割槽：

alter table table_name drop partition p0; //p0為要刪除的分割槽名稱

刪除了分割槽，同時也將刪除該分割槽中的所有資料。同時，如果刪除了分割槽導致分割槽不能覆蓋所有值，那麼插入資料的時候會報錯。

對於Hash和Key分割槽：

alter table table_name coalesce partition 2; //將分割槽縮減到2個

coalesce [ˌkəʊəˈles] vi. 聯合，合併

分割槽查詢 1）查詢某張表一共有多少個分割槽

mysql> select 
 ->   partition_name,
 ->   partition_expression,
 ->   partition_description,
 ->   table_rows
 -> from 
 ->   INFORMATION_SCHEMA.partitions
 -> where
 ->   table_schema='test'
 ->   and table_name = 'emp';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | store_id             | 10                    |          0 |
| p1             | store_id             | 20                    |          1 |
+----------------+----------------------+-----------------------+------------+

即，可以從information_schema.partitions表中查詢。

2）檢視執行計劃，判斷查詢資料是否進行了分割槽過濾

mysql> explain partitions select * from emp where store_id=10 \G;
*************************** 1. row ***************************
        id: 1
select_type: SIMPLE
     table: emp
partitions: p1
      type: system
possible_keys: NULL
       key: NULL
   key_len: NULL
       ref: NULL
      rows: 1
     Extra: 
1 row in set (0.00 sec)

上面的結果：partitions:p1 表示資料在p1分割槽進行檢索。