分區(qū)是指根據(jù)一定的規(guī)則，數(shù)據(jù)庫把一個表分解成多個更小的，更容易管理的部分。就訪問數(shù)據(jù)庫的應(yīng)用而言，邏輯上只有一個表或一個索引，但是實(shí)際上這個表可能由數(shù) 10 個物理分區(qū)對象組成，每個分區(qū)都是一個獨(dú)立的對象，可以獨(dú)自處理，可以作為表的一部分進(jìn)行處理。分區(qū)對應(yīng)用來說是完全透明的，不影響應(yīng)用的業(yè)務(wù)邏輯

MySQL 分區(qū)的優(yōu)點(diǎn)主要包括以下 4 個方面

• 和單個磁盤或者文件系統(tǒng)分區(qū)相比，可以存儲更多數(shù)據(jù)
• 優(yōu)化查詢。在 where 子句中包含分區(qū)條件時，可以只掃描必要的一個或多個分區(qū)來提高查詢效率；同時在涉及 sum() 和 count() 這類聚合函數(shù)的查詢時，可以容易的在每個分區(qū)上并行處理，最終只需要匯總所有分區(qū)得到的結(jié)果
• 對于已經(jīng)過期或者不需要保存的數(shù)據(jù)，可以通過刪除與這些數(shù)據(jù)有關(guān)的分區(qū)來快速刪除數(shù)據(jù)
• 跨多個磁盤來分散數(shù)據(jù)查詢，以獲得更大的查詢吞吐量

使用 show variables like '%partition%'; 命令來確定當(dāng)前的 MySQL 是否支持分區(qū)

分區(qū)類型

• range 分區(qū)：基于一個給定連續(xù)區(qū)間范圍，把數(shù)據(jù)分配到不同的分區(qū)

• list 分區(qū)：類似 range 分區(qū)，區(qū)別在 list 分區(qū)是基于枚舉出的值列表分區(qū)，range 是基于給定的連續(xù)區(qū)間范圍分區(qū)

• hash 分區(qū)：基于給定的分區(qū)個數(shù)，把數(shù)據(jù)分配到不同的分區(qū)

• key 分區(qū)：類似于 hash 分區(qū)

range 分區(qū)，list 分區(qū)，hash 分區(qū)都要求分區(qū)鍵必須是 int 類型，或者通過表達(dá)式返回 int 類型。key 分區(qū)可以使用其他類型的列（blob 或 text 列類型除外）作為分區(qū)鍵

無論是哪種 MySQL 分區(qū)類型，要么分區(qū)表上沒有主鍵/唯一鍵，要么分區(qū)表的主鍵/唯一鍵都必須包含分區(qū)鍵，也就是說不能使用主鍵/唯一鍵字段之外的其他字段分區(qū)

分區(qū)的名字不區(qū)分大小寫

range 分區(qū)

按照 range 分區(qū)的表是利用取值范圍將數(shù)據(jù)分成分區(qū)，區(qū)間要連續(xù)并且不能互相重疊，使用 values less than 操作符進(jìn)行分區(qū)定義，也使用 values less than maxvalue 子句提供給所有大于明確指定的最高值的值

create table tablename(value1 int)
partition by range(value1)(
    partition p0 values less than(10),
    partition p1 values less than(20),
    partition p2 values less than maxvalue
)

MySQL 支持在 values less than 子句中使用表達(dá)式

list 分區(qū)

list 分區(qū)是建立離散的值列表告訴數(shù)據(jù)庫特定的值屬于哪個分區(qū)，list 分區(qū)在很多方面類似于 range 分區(qū)，區(qū)別在 list 分區(qū)是從屬于一個枚舉列表的值的集合，range 分區(qū)是從屬于一個連續(xù)區(qū)間值的集合

list 分區(qū)是通過使用 partition by list(expr) 子句來實(shí)現(xiàn)，expr 是某列值或一個基于某列值返回一個整數(shù)值的表達(dá)式，然后通過 values in(value_list) 的方式來定義分區(qū)，其中 value_lsit 是一個逗號分隔的整數(shù)列表。與 range 分區(qū)不同，list 分區(qū)不必聲明任何特定的順序

create table tablename(v1 int)
partition by list(v1)(
partition p0 values in(1,3,5),
partition p1 values in(2,4,6))

如果試圖插入的列值（或者分區(qū)表達(dá)式的返回值）不包含分區(qū)值列表中時，那么 insert 操作會失敗并報錯。要重點(diǎn)注意的是，list 分區(qū)不存在類似 values less than maxvalue 這樣包含其他值在內(nèi)的定義方式。將要匹配的任何值都必須在值列表中找得到

columns 分區(qū)

columns 分區(qū)解決了 range 分區(qū)和 list 分區(qū)只支持整數(shù)分區(qū)，從而導(dǎo)致需要額外的函數(shù)計(jì)算得到整數(shù)或者通過額外的轉(zhuǎn)換表來轉(zhuǎn)換為整數(shù)再分區(qū)的問題

columns 分區(qū)分為 range columns 分區(qū)和 list columns 分區(qū)，它們支持整數(shù)，日期時間，字符串三個數(shù)據(jù)類型

columns 分區(qū)的亮點(diǎn)除了支持?jǐn)?shù)據(jù)類型增加之外，另外就是它還支持多列分區(qū)

create table tname(v1 int,v2 int)
partition by range columns(v1,v2)(
    partition p0 values less than(0,10),
    partition p1 values less than(10,10),
    partition p2 values less than(10,20)
)

需要注意的是，range columns 分區(qū)鍵的比較是基于元組的比較，也就是基于字段組的比較。它先用插入的數(shù)據(jù)的第一個字段值和分區(qū)的第一個值進(jìn)行比較，如果插入的第一個值小于分區(qū)的第一個值那么就不需要比較第二個值就屬于該分區(qū)；如果第一個值等于分區(qū)的第一個值，開始比較第二個值同樣如果第二個值小于分區(qū)的第二個值那么就屬于該分區(qū)，以此類推

hash 分區(qū)

hash 分區(qū)主要用來分散熱點(diǎn)讀，確保數(shù)據(jù)在預(yù)先確定個數(shù)的分區(qū)中盡可能平均分布。對一個表執(zhí)行 hash 分區(qū)時，MySQL 會對分區(qū)鍵應(yīng)用一個散列函數(shù)，以此確定數(shù)據(jù)應(yīng)當(dāng)放在 N 個分區(qū)中的哪個分區(qū)中

MySQL 支持兩種 hash 分區(qū)，常規(guī) hash 分區(qū)和線性 hash 分區(qū)（linear hash 分區(qū)）。常規(guī) hash 使用的是取模算法，線性 hash 使用的是一個線性的 2 的冪的運(yùn)算法則

使用 partition by hash(expr) partitions num 子句對分區(qū)類型，分區(qū)鍵和分區(qū)個數(shù)進(jìn)行定義，其中 expr 是某列值或一個基于某列值返回一個整數(shù)值的表達(dá)式，num 是一個非負(fù)的整數(shù)，表示分割成分區(qū)的數(shù)量，默認(rèn) num 為 1

create table tn(v1 int)
partition by hash(v1)
partitions 4;

插入 234 ，可以通過取模算法 mod(234,4) 得知數(shù)字被插到第 2 個分區(qū)表

表達(dá)式 expr 可以是 MySQL 中有效的任何函數(shù)或者其他表達(dá)式，只要它們返回一個既非常數(shù)也非隨機(jī)數(shù)的整數(shù)。每當(dāng)插入/更新/刪除一行數(shù)據(jù)時，這個表達(dá)式都需要計(jì)算一次，這意味著非常復(fù)雜的表達(dá)式可能會引起性能問題，MySQL 也不推薦使用涉及多列的哈希表達(dá)式

常規(guī) hash 分區(qū)在增加分區(qū)或者合并分區(qū)時會出現(xiàn)問題。假設(shè)原來是 5 個常規(guī) hash 分區(qū)，現(xiàn)在需要新增一個常規(guī) hash 分區(qū)，原來的取模算法是 mod(expr,5)，根據(jù)余數(shù) 0~4 分布在 5 個分區(qū)中，現(xiàn)在新增一個分區(qū)后，取模算法變成 mod(expr,6)，根據(jù)余數(shù) 0~5 分布在 6 個分區(qū)中，原來的 5 個分區(qū)中的數(shù)據(jù)大部分都需要通過重新計(jì)算重新分區(qū)。常規(guī) hash 在分區(qū)管理上帶來的代價太大了，不適合需要靈活變動分區(qū)的需求。為了降低分區(qū)管理上的代價，MySQL 提供了線性 hash 分區(qū)，分區(qū)函數(shù)是一個線性的 2 的冪的運(yùn)算法則

線性 hash 分區(qū)和常規(guī) hash 分區(qū)在語法上唯一區(qū)別是在 partition by 子句中添加 linear 關(guān)鍵字

create table tn(v1 int)
partition by linear hash(v1)
partitions 4;

同樣的，使用線性 hash 時，指定記錄保存在哪個分區(qū)是可以計(jì)算出來的，假設(shè)將要保存記錄的分區(qū)編號為 N，num 是一個非負(fù)的整數(shù)，表示分割成分區(qū)的數(shù)量，那么 N 可以通過以下算法得到

• 首先，找到下一個大于等于 num 的 2 的冪，這個值設(shè)為 V，V 可以通過下面的公式得到：V = Power(2,Ceiling(Log(2,num)))
• 其次，設(shè)置 N=F(column_list)&(V-1)
• 當(dāng) N>=num。設(shè)置 N=N&(ceiling(V/2)-1)

有意思的是，當(dāng)線性 hash 的分區(qū)個數(shù)是 2 的 N 次冪時，線性 hash 的分區(qū)結(jié)果和常規(guī) hash 的分區(qū)結(jié)果是一致的

線性 hash 分區(qū)的優(yōu)點(diǎn)是，在分區(qū)維護(hù)（增加，刪除，合并，拆分分區(qū)）時，MySQL 能夠處理的更加迅速；缺點(diǎn)是，對比常規(guī) hash分區(qū)的時候，線性 hash 各個分區(qū)之間數(shù)據(jù)的分布不太均衡

key 分區(qū)

按照 key 進(jìn)行分區(qū)非常類似于按照 hash 進(jìn)行分區(qū)，只不過 hash 分區(qū)允許使用用戶自定義的表達(dá)式，而 key 分區(qū)不允許使用用戶自定義的表達(dá)式，需要使用 MySQL 服務(wù)器提供的 hash 函數(shù)；同時 hash 分區(qū)只支持整數(shù)分區(qū)，而 key 分區(qū)支持除 blob 和 text 類型外其他類型的列作為分區(qū)鍵

用 partition by key(expr) 子句來創(chuàng)建一個 key 分區(qū)表，expr 是零個或者多個字段名的列表

與 hash 分區(qū)不同，創(chuàng)建 key 分區(qū)表的時候，可以不指定分區(qū)鍵，默認(rèn)會首先選擇使用主鍵作為分區(qū)鍵；在沒有主鍵的情況，會選擇非空唯一鍵作為分區(qū)鍵，作為分區(qū)鍵的唯一鍵必須是非空的，如果不是非空的，會報錯；在沒有主鍵，也沒有唯一鍵的情況下，就不能不指定分區(qū)鍵了

和 hash 分區(qū)類似，在 key 分區(qū)中使用關(guān)鍵字 linear 具有同樣的作用，也就是 linear key 分區(qū)時，分區(qū)的編號是通過 2 的冪算法得到的，而不是通過取模得到的

子分區(qū)

子分區(qū)（subpartitioning）是分區(qū)表中對每個分區(qū)再次分割，又被成為復(fù)合分區(qū)（composite partitionning）?？梢詫?range 或者 list 分區(qū)進(jìn)行再分區(qū)，子分區(qū)可以使用 hash 和 key

create table tn(v date)
partition by range(year(v))
subpartition by hash(to_days(v))
subpartitions 2
(partition p0 values less than(2000),
partitioni p1 values less than(2010))

分區(qū)處理 NULL 值的方式

MySQL 不禁止在分區(qū)鍵上使用 null，分區(qū)鍵可能是一個字段或者一個用戶定義的表達(dá)式。一般情況下，MySQL 的分區(qū)把 null 當(dāng)作零值，或者一個最小值進(jìn)行處理

range 分區(qū)中，null 值會被當(dāng)作最小值來處理；list 分區(qū)中，null 值必須出現(xiàn)在枚舉列表中，否則不被接受；hash/key 分區(qū)中，null 值會被當(dāng)作零值來處理

由于針對不同的分區(qū)類型，null 值時而被當(dāng)作零值處理，時而被當(dāng)作最小值處理，為了避免在處理 null 值出現(xiàn)誤判，更推薦通過設(shè)置字段非空和默認(rèn)值來繞開 MySQL 默認(rèn)對 null 值的處理

分區(qū)管理

range 和 list 分區(qū)管理

從分區(qū)的表中刪除一個分區(qū)，可以使用 alter table drop partition 語句

alter table tn drop partition p0;

增加一個分區(qū)，使用 alter table add partition 語句。對于 range 分區(qū)，只能 add partition 添加新的分區(qū)到分區(qū)列表的最大一端

alter table tn add partition (partition p3 values less than(40))
alter table tn add partition (partition p3 values in(1,2))

增加 list 分區(qū)時，不能添加一個包含現(xiàn)有分區(qū)值列表中的任何值的分區(qū)，也就是說對一個固定的分區(qū)鍵值，必須指定并且只能指定一個唯一的分區(qū)，否則會出現(xiàn)錯誤

可以在不丟失數(shù)據(jù)的情況下，通過 alter table reorganize partition into 語句重新定義 range 或 list 分區(qū)

alter table xixi reorganize partition p2 into (partition p2 values less than(2020),partition p3 values less than maxvalue);

重新定義分區(qū)可以用來拆分一個 range 或 list 分區(qū)為多個 range 或 list 分區(qū)，也可以用來合并多個相鄰 range 或 list 分區(qū)為一個 range 或 list 分區(qū)或者多個range 或 list 分區(qū)

alter table xixi reorganize partition p1,p2 into(partition p1 values less than maxvalue);

重新定義 range 或 list 分區(qū)時，只能重新定義相鄰的分區(qū)，不能跳過某個 range 或 list 分區(qū)進(jìn)行重新定義，同時重新定義的分區(qū)區(qū)間必須和原分區(qū)區(qū)間覆蓋相同的區(qū)間；也不能使用重新定義分區(qū)來改變分區(qū)表分區(qū)的類型

hash 和 key 分區(qū)管理

通過 alter table coalesce partition 來拆分 hash 分區(qū)或者 key 分區(qū)

alter table xixi coalesce partition 2;

coalesce 不能用來增加分區(qū)的數(shù)量，通過 alter table add partition 語句來實(shí)現(xiàn)

alter table xixi add partition partitions 4;

通過 alter table xixi add partition partitions n 語句新增 hash 分區(qū)或者 key 分區(qū)時，是對原表新增 n 個分區(qū)，而不是增加到 n 個分區(qū)