MySQL 分區表

jopen 8年前發布 | 11K 次閱讀 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分區存在的問題

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. 盡量選擇能夠全部覆蓋的字段作為分區鍵，比如一年的十二個月等。
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分區進行檢索。

[參考資料] 《深入MySQL數據庫開發、優化與管理維護（第2版）》
《高性能MySQL》

來自：http://my.oschina.net/jasonultimate/blog/548745

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1450918717167.html

MySQL 數據庫服務器