什么是表分區(qū)

今天統(tǒng)計(jì)數(shù)據(jù)的時(shí)候發(fā)現(xiàn)一張表使用了表分區(qū)，借此機(jī)會(huì)記錄一下。

1. 什么是表分區(qū)？

表分區(qū)，是指根據(jù)一定規(guī)則，將數(shù)據(jù)庫(kù)中的一張表分解成多個(gè)更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個(gè)物理分區(qū)組成。

2. 表分區(qū)與分表的區(qū)別

分表：指的是通過(guò)一定規(guī)則，將一張表分解成多張不同的表。比如將用戶訂單記錄根據(jù)時(shí)間成多個(gè)表。 分表與分區(qū)的區(qū)別在于：分區(qū)從邏輯上來(lái)講只有一張表，而分表則是將一張表分解成多張表。

3. 表分區(qū)有什么好處？

1）分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上，從而高效地利用多個(gè)硬件設(shè)備。 2）和單個(gè)磁盤或者文件系統(tǒng)相比，可以存儲(chǔ)更多數(shù)據(jù) 3）優(yōu)化查詢。在where語(yǔ)句中包含分區(qū)條件時(shí)，可以只掃描一個(gè)或多個(gè)分區(qū)表來(lái)提高查詢效率；涉及sum和count語(yǔ)句時(shí)，也可以在多個(gè)分區(qū)上并行處理，最后匯總結(jié)果。 4）分區(qū)表更容易維護(hù)。例如：想批量刪除大量數(shù)據(jù)可以清除整個(gè)分區(qū)。 5）可以使用分區(qū)表來(lái)避免某些特殊的瓶頸，例如InnoDB的單個(gè)索引的互斥訪問(wèn)，ext3問(wèn)價(jià)你系統(tǒng)的inode鎖競(jìng)爭(zhēng)等。

4. 分區(qū)表的限制因素

1）一個(gè)表最多只能有1024個(gè)分區(qū) 2） MySQL5.1中，分區(qū)表達(dá)式必須是整數(shù)，或者返回整數(shù)的表達(dá)式。在MySQL5.5中提供了非整數(shù)表達(dá)式分區(qū)的支持。 3）如果分區(qū)字段中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進(jìn)來(lái)。即：分區(qū)字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列。 4）分區(qū)表中無(wú)法使用外鍵約束 5）MySQL的分區(qū)適用于一個(gè)表的所有數(shù)據(jù)和索引，不能只對(duì)表數(shù)據(jù)分區(qū)而不對(duì)索引分區(qū)，也不能只對(duì)索引分區(qū)而不對(duì)表分區(qū)，也不能只對(duì)表的一部分?jǐn)?shù)據(jù)分區(qū)。

5. 如何判斷當(dāng)前MySQL是否支持分區(qū)？

命令：show variables like '%partition%'?運(yùn)行結(jié)果:

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

have_partintioning 的值為YES，表示支持分區(qū)。

6. MySQL支持的分區(qū)類型有哪些？

1）RANGE分區(qū)：按照數(shù)據(jù)的區(qū)間范圍分區(qū) 2）LIST分區(qū)：按照List中的值分區(qū)，與RANGE的區(qū)別是，range分區(qū)的區(qū)間范圍值是連續(xù)的。 3）HASH分區(qū) 4）KEY分區(qū)?說(shuō)明?在MySQL5.1版本中，RANGE,LIST,HASH分區(qū)要求分區(qū)鍵必須是INT類型，或者通過(guò)表達(dá)式返回INT類型。但KEY分區(qū)的時(shí)候，可以使用其他類型的列（BLOB，TEXT類型除外）作為分區(qū)鍵。

7. Range分區(qū)

利用取值范圍進(jìn)行分區(qū)，區(qū)間要連續(xù)并且不能互相重疊。 語(yǔ)法：

partition?by?range(exp)(?//exp可以為列名或者表達(dá)式，比如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)
????->?);

上面的語(yǔ)句創(chuàng)建了emp表，并根據(jù)store_id字段進(jìn)行分區(qū)，小于10的值存在分區(qū)p0中，大于等于10，小于20的值存在分區(qū)p1中。?注意?每個(gè)分區(qū)都是按順序定義的，從最低到最高。上面的語(yǔ)句，如果將less than(10) 和less than (20)的順序顛倒過(guò)來(lái)，那么將報(bào)錯(cuò)，如下：

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

RANGE分區(qū)存在的問(wèn)題

range范圍覆蓋問(wèn)題：當(dāng)插入的記錄中對(duì)應(yīng)的分區(qū)鍵的值不在分區(qū)定義的范圍中的時(shí)候，插入語(yǔ)句會(huì)失敗。 上面的例子，如果我插入一條store_id = 30的記錄會(huì)怎么樣呢？ 我們上面分區(qū)的時(shí)候，最大值是20，如果插入一條超過(guò)20的記錄，會(huì)報(bào)錯(cuò):

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

提示30這個(gè)值沒有對(duì)應(yīng)的分區(qū)。?解決辦法?A. 預(yù)估分區(qū)鍵的值，及時(shí)新增分區(qū)。 B. 設(shè)置分區(qū)的時(shí)候，使用values less than maxvalue?子句,MAXVALUE表示最大的可能的整數(shù)值。 C. 盡量選擇能夠全部覆蓋的字段作為分區(qū)鍵，比如一年的十二個(gè)月等。Range分區(qū)中，分區(qū)鍵的值如果是NULL，將被作為一個(gè)最小值來(lái)處理。 8. LIST分區(qū)

List分區(qū)是建立離散的值列表告訴數(shù)據(jù)庫(kù)特定的值屬于哪個(gè)分區(qū)。 語(yǔ)法：

????partition?by?list(exp)(?//exp為列名或者表達(dá)式
????????partition?p0?values?in?(3,5)??//值為3和5的在p0分區(qū)
????)

與Range不同的是，list分區(qū)不必生命任何特定的順序。例如：

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)
????->?);

注意?如果插入的記錄對(duì)應(yīng)的分區(qū)鍵的值不在list分區(qū)指定的值中，將會(huì)插入失敗。并且，list不能像range分區(qū)那樣提供maxvalue。

9. Columns分區(qū)

MySQL5.5中引入的分區(qū)類型，解決了5.5版本之前range分區(qū)和list分區(qū)只支持整數(shù)分區(qū)的問(wèn)題。 Columns分區(qū)可以細(xì)分為 range columns分區(qū)和 list columns分區(qū)，他們都支持整數(shù)，日期時(shí)間，字符串三大數(shù)據(jù)類型。（不支持text和blob類型作為分區(qū)鍵） columns分區(qū)還支持多列分區(qū)（這里不詳細(xì)展開）。

10. Hash分區(qū)

Hash分區(qū)主要用來(lái)分散熱點(diǎn)讀，確保數(shù)據(jù)在預(yù)先確定個(gè)數(shù)的分區(qū)中盡可能平均分布。 MySQL支持兩種Hash分區(qū):常規(guī)Hash分區(qū)和線性Hash分區(qū)。 A. 常規(guī)Hash分區(qū):使用取模算法 語(yǔ)法：

partition?by?hash(store_id)?partitions?4;

上面的語(yǔ)句，根據(jù)store_id對(duì)4取模，決定記錄存儲(chǔ)位置。 比如store_id = 234的記錄，MOD(234,4)=2,所以會(huì)被存儲(chǔ)在第二個(gè)分區(qū)。

常規(guī)Hash分區(qū)的優(yōu)點(diǎn)和不足?優(yōu)點(diǎn)：能夠使數(shù)據(jù)盡可能的均勻分布。 缺點(diǎn)：不適合分區(qū)經(jīng)常變動(dòng)的需求。假如我要新增加兩個(gè)分區(qū)，現(xiàn)在有6個(gè)分區(qū)，那么MOD(234,6)的結(jié)果與之前MOD(234,4)的結(jié)果就會(huì)出現(xiàn)不一致，這樣大部分?jǐn)?shù)據(jù)就需要重新計(jì)算分區(qū)。為解決此問(wèn)題，MySQL提供了線性Hash分區(qū)。

B. 線性Hash分區(qū)：分區(qū)函數(shù)是一個(gè)線性的2的冪的運(yùn)算法則。 語(yǔ)法：

partition?by?LINER?hash(store_id)?partitions?4;

與常規(guī)Hash的不同在于，“Liner”關(guān)鍵字。 算法介紹: 假設(shè)要保存記錄的分區(qū)編號(hào)為N,num為一個(gè)非負(fù)整數(shù),表示分割成的分區(qū)的數(shù)量，那么N可以通過(guò)以下步驟得到：
Step 1. 找到一個(gè)大于等于num的2的冪，這個(gè)值為V，V可以通過(guò)下面公式得到：
V = Power(2,Ceiling(Log(2,num)))
例如：剛才設(shè)置了4個(gè)分區(qū)，num=4，Log(2,4)=2,Ceiling(2)=2,power(2,2)=4,即V=4
Step 2. 設(shè)置N=F(column_list)&(V-1)
例如：剛才V=4，store_id=234對(duì)應(yīng)的N值，N = 234&（4-1） =2
Step 3. 當(dāng)N>=num,設(shè)置V=Ceiling(V/2),N=N&(V-1)
例如：store_id=234,N=2<4,所以N就取值2，即可。
假設(shè)上面算出來(lái)的N=5，那么V=Ceiling(4/2)=2,N=5&(2-1)=1,即在第一個(gè)分區(qū)。

線性Hash的優(yōu)點(diǎn)和不足?優(yōu)點(diǎn)：在分區(qū)維護(hù)（增加，刪除，合并，拆分分區(qū)）時(shí)，MySQL能夠處理得更加迅速。 缺點(diǎn)：與常規(guī)Hash分區(qū)相比，線性Hash各個(gè)分區(qū)之間的數(shù)據(jù)分布不太均衡。

11. Key分區(qū)

類似Hash分區(qū)，Hash分區(qū)允許使用用戶自定義的表達(dá)式，但Key分區(qū)不允許使用用戶自定義的表達(dá)式。Hash僅支持整數(shù)分區(qū)，而Key分區(qū)支持除了Blob和text的其他類型的列作為分區(qū)鍵。 語(yǔ)法:

partition?by?key(exp)?partitions?4;//exp是零個(gè)或多個(gè)字段名的列表

key分區(qū)的時(shí)候，exp可以為空，如果為空，則默認(rèn)使用主鍵作為分區(qū)鍵，沒有主鍵的時(shí)候，會(huì)選擇非空惟一鍵作為分區(qū)鍵。

12. 子分區(qū)

分區(qū)表中對(duì)每個(gè)分區(qū)再次分割，又成為復(fù)合分區(qū)。

13. 分區(qū)對(duì)于NULL值的處理

MySQ允許分區(qū)鍵值為NULL，分區(qū)鍵可能是一個(gè)字段或者一個(gè)用戶定義的表達(dá)式。一般情況下，MySQL在分區(qū)的時(shí)候會(huì)把NULL值當(dāng)作零值或者一個(gè)最小值進(jìn)行處理。
注意
Range分區(qū)中：NULL值被當(dāng)作最小值來(lái)處理
List分區(qū)中：NULL值必須出現(xiàn)在列表中，否則不被接受
Hash/Key分區(qū)中：NULL值會(huì)被當(dāng)作零值來(lái)處理

14. 分區(qū)管理

分區(qū)管理包括對(duì)于分區(qū)的增加，刪除，以及查詢。

增加分區(qū)：
對(duì)于Range分區(qū)和LIst分區(qū)來(lái)說(shuō)：

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

values后面的內(nèi)容根據(jù)分區(qū)的類型不同而不同。
對(duì)于Hash分區(qū)和Key分區(qū)來(lái)說(shuō)：

alter?table?table_name?add?partition?partitions?8;

上面的語(yǔ)句，指的是新增8個(gè)分區(qū) 。刪除分區(qū)
對(duì)于Range分區(qū)和List分區(qū)：

alter?table?table_name?drop?partition?p0;?//p0為要?jiǎng)h除的分區(qū)名稱

刪除了分區(qū)，同時(shí)也將刪除該分區(qū)中的所有數(shù)據(jù)。同時(shí)，如果刪除了分區(qū)導(dǎo)致分區(qū)不能覆蓋所有值，那么插入數(shù)據(jù)的時(shí)候會(huì)報(bào)錯(cuò)。
對(duì)于Hash和Key分區(qū)：

alter?table?table_name?coalesce?partition?2;?//將分區(qū)縮減到2個(gè)

coalesce [?k????les] vi. 聯(lián)合，合并分區(qū)查詢 1）查詢某張表一共有多少個(gè)分區(qū)

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）查看執(zhí)行計(jì)劃，判斷查詢數(shù)據(jù)是否進(jìn)行了分區(qū)過(guò)濾

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)

上面的結(jié)果：partitions:p1 表示數(shù)據(jù)在p1分區(qū)進(jìn)行檢索。