分區(qū)是將一個(gè)表的數(shù)據(jù)按照某種方式，比如按照月、天或者其他方式，分成多個(gè)較小的、更容易管理的部分，也就是物理存儲(chǔ)根據(jù)一定規(guī)則放在不同文件中，但是邏輯上所有的數(shù)據(jù)仍在一個(gè)表中。如下圖所示：

MySQL實(shí)現(xiàn)分區(qū)表的方式是對(duì)底層表的封裝，意味著索引也是按照分區(qū)的子表定義的，沒(méi)有全局索引。這和Oracle不同，在Oracle中可以更加靈活的定義索引和表是否進(jìn)行分區(qū)。

分區(qū)表的實(shí)現(xiàn)原理

分區(qū)表由多個(gè)相關(guān)的底層表實(shí)現(xiàn)，這些底層表也是由句柄對(duì)象表示，因此也可以直接訪問(wèn)各個(gè)分區(qū)。存儲(chǔ)引擎管理分區(qū)的各個(gè)底層表和管理普通表一樣，所有的底層表都必須使用相同的存儲(chǔ)引擎，分區(qū)表的索引只是在各個(gè)底層表各自加上一個(gè)完全相同的索引。從存儲(chǔ)引擎的角度來(lái)看，底層表和一個(gè)普通表沒(méi)有任何不同，存儲(chǔ)引擎也無(wú)需知道這是一個(gè)普通表還是一個(gè)分區(qū)表的一部分。

那么在分區(qū)表上的操作是怎樣進(jìn)行的呢？其實(shí)常規(guī)的CRUD操作以及返回結(jié)果和普通表沒(méi)有任何區(qū)別。具體分區(qū)層實(shí)現(xiàn)是先打開(kāi)并鎖定所有底層表，優(yōu)化器先判斷是否可以過(guò)濾部分分區(qū)，然后調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎接口訪問(wèn)各個(gè)分區(qū)的數(shù)據(jù)進(jìn)行相應(yīng)的操作。

分區(qū)表的分區(qū)類(lèi)型

分區(qū)表的類(lèi)型主要包括RANGE、LIST、HASH、KEY四種，另外還有一種COLUMNS分區(qū)類(lèi)型，因不經(jīng)常用，本文不做介紹。

• RANGE分區(qū)

給定一個(gè)連續(xù)的區(qū)間范圍（區(qū)間要求連續(xù)并且不能重疊），某個(gè)字段的值滿(mǎn)足這個(gè)范圍就會(huì)被分配到該分區(qū)。適用于字段的值是連續(xù)的字段，比如日期范圍, 連續(xù)的數(shù)字等。

示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(separated))
 (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
)

該示例中根據(jù)每個(gè)員工離開(kāi)公司的年份進(jìn)行劃分，對(duì)于1991年之前離職員工存儲(chǔ)在分區(qū)p0中，1991年至1995年離職的人存儲(chǔ)在分區(qū)p1，1996年至2000年離職的人存儲(chǔ)在分區(qū)p2中。

如果沒(méi)有創(chuàng)建LESS THAN MAXVALUE分區(qū)，那么可能存在插入超過(guò)2000年離職的人的數(shù)據(jù)丟失不存儲(chǔ)，因此RANGE分區(qū)為了防止丟數(shù)據(jù)會(huì)加入該分區(qū)。MAXVALUE表示一個(gè)始終大于最大可能整數(shù)值的整數(shù)值，因此2001年以后離開(kāi)的所有人存儲(chǔ)在分區(qū)p3中。

若設(shè)置了 LESS THAN MAXVALUE分區(qū)，添加新分區(qū)時(shí)需要重新分區(qū)，此時(shí)存儲(chǔ)在LESS THAN MAXVALUE分區(qū)中的數(shù)據(jù)會(huì)根據(jù)重新分區(qū)規(guī)則進(jìn)行數(shù)據(jù)重新分配，如下所示：

ALTER TABLE employees  BY RANGE (YEAR(separated))
(
  PARTITION p0 VALUES LESS THAN (1991),
  PARTITION p1 VALUES LESS THAN (1996),
  PARTITION p2 VALUES LESS THAN (2001),
  PARTITION p3 VALUES LESS THAN (2006),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

對(duì)于日期類(lèi)型的字段，也可以使用UNIX_TIMESTAMP()函數(shù)，根據(jù)TIMESTAMP列的值按RANGE對(duì)表進(jìn)行分區(qū)，如下例所示：

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

• LIST分區(qū)

和RANGE分區(qū)方式相似， LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇分區(qū)。LIST分區(qū)沒(méi)有類(lèi)似RANGE分區(qū)中“VALUES LESS THAN MAXVALUE”包含其他值在內(nèi)的定義，其要匹配的任何值都必須在值列表中找到，如果不在列表中的數(shù)據(jù)插入表中操作會(huì)失敗。

示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
 )
 PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
 );

• HASH分區(qū)

基于用戶(hù)定義表達(dá)式的返回值來(lái)進(jìn)行分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。

MySQL支持兩種HASH分區(qū)，常規(guī)HASH(HASH)分區(qū)和線(xiàn)性HASH(LINEARHASH) 分區(qū)。常規(guī)HASH分區(qū)使用取模算法，對(duì)應(yīng)一個(gè)表達(dá)式expr可以計(jì)算出它被保存到哪個(gè)分區(qū)中，N = MOD(expr, num)。線(xiàn)性HASH分區(qū)使用一個(gè)線(xiàn)性的2的冪運(yùn)算法則，V = POWER(2, CEILING(LOG(2,num)))。

常規(guī)HASH示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

常規(guī)HASH分區(qū)非常簡(jiǎn)便，使用HASH函數(shù)值的模數(shù)，可以讓數(shù)據(jù)平均的分布到每一個(gè)分區(qū)，但是由于分區(qū)在創(chuàng)建表的時(shí)候已經(jīng)固定了，如果新增或者收縮分區(qū)的數(shù)據(jù)遷移比較大。

因此MySQL還支持線(xiàn)性HASH分區(qū)，線(xiàn)性HASH分區(qū)采用線(xiàn)性二乘冪算法，可以增加分區(qū)維護(hù)(增加、刪除、合并、拆分分區(qū))時(shí)數(shù)據(jù)遷移和處理速度，但是也會(huì)存在各個(gè)分區(qū)之間數(shù)據(jù)的分布不太均衡的情況。

語(yǔ)法上線(xiàn)性HASH分區(qū)和HASH分區(qū)之間的唯一區(qū)別是在PARTITION BY子句中添加了LINEAR關(guān)鍵字。如下所示：

線(xiàn)性HASH示例代碼：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 6;

線(xiàn)性HASH分區(qū)的計(jì)算原理及步驟如下：

1. V = POWER(2,CEILING(LOG(2, num)))，其中：

• num為分區(qū)的數(shù)量。
• LOG(2, num) 代表計(jì)算num以2為底的對(duì)數(shù)。
• CEILING() 代表向上取整。
• POWER(2, x) 代表取2的x冪次。

如果num的值是2的冪數(shù)，那么這個(gè)表達(dá)式計(jì)算出來(lái)的結(jié)果不變。

假設(shè)num為13。則LOG（2,13）為3.7004397181411。CEILING（3.7004397181411）為4，而V = POWER（2,4）為16。

2. Set N =F(column_list) & (V - 1)。

• N代表計(jì)算出來(lái)的數(shù)據(jù)所在分區(qū)編號(hào)。
• F代表對(duì)分區(qū)鍵進(jìn)行的操作，該操作返回一個(gè)整數(shù)值。
• &代表位與運(yùn)算。

當(dāng)num是2的倍數(shù)時(shí)由于V計(jì)算出來(lái)的結(jié)果不變，這個(gè)時(shí)候線(xiàn)性HASH算法的計(jì)算結(jié)果F(column_list)&(V-1)=MOD(F(column_list)/num)和常規(guī)HASH取模算出的結(jié)果是一致的。

3. While N>= num:

   Set V = V / 2
   
    Set N = N & (V - 1)
   

特別的，如果步驟2中計(jì)算出來(lái)的N比總分區(qū)數(shù)量num大，則需要執(zhí)行本步驟操作，直到N

接下來(lái)用一個(gè)示例解釋下線(xiàn)性HASH算法找具體分區(qū)號(hào)的實(shí)現(xiàn)原理。創(chuàng)建一個(gè)有六個(gè)分區(qū)的線(xiàn)性HASH分區(qū)表，如下所示：

CREATE TABLE t1(
   Col1  INT ,
   Col2  CHAR(5) ,
   Col3  DATE 
)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;

現(xiàn)在給該表中插入兩條記錄，Col3的值分別為'2003-04-14'和'1998-10-19'，其中上文中的F()在這里對(duì)應(yīng)YEAR()，那么根據(jù)算法原理可以確定第一個(gè)分區(qū)號(hào)如下所示：

V = POWER(2, CEILING(LOG(2,6) )) = 8

N = YEAR('2003-04-14')& (8 - 1)

= 2003 & 7

= 3

判斷3>=6不成立，所以存儲(chǔ)在第3號(hào)分區(qū)，注意這里的3指的是P3,分區(qū)號(hào)是從P0開(kāi)始。

當(dāng)插入的值是'1998-10-19'計(jì)算所存儲(chǔ)的分區(qū)號(hào)如下所示：

V = 8

N = YEAR('1998-10-19')& (8 - 1)

= 1998 & 7

= 6

判斷6>=6成立，所以需要做下一步運(yùn)算。

N = 6 & ((8 / 2) -1)

= 6 & 3

= 2

判斷2>=6不成立，所以存儲(chǔ)在第2號(hào)分區(qū)，同理這里的2指的是P2分區(qū)。

下面是用EXCEL做的一個(gè)簡(jiǎn)單的測(cè)試，測(cè)算了一下MySQL使用線(xiàn)性HASH分區(qū)算法，將分區(qū)鍵值取（1-1048575），分區(qū)數(shù)量分別為4、5、6、7、8個(gè)時(shí)，各分區(qū)數(shù)據(jù)數(shù)量如圖所示：

從圖中我們可以看到官網(wǎng)說(shuō)它是“線(xiàn)性”，但是又可能不太平均，是比較嚴(yán)謹(jǐn)?shù)摹乃惴ń嵌瓤紤]，在分區(qū)鍵值平均分布的前提下，為了各分區(qū)數(shù)據(jù)量盡量平均，線(xiàn)性HASH推薦分區(qū)數(shù)量盡量為2的冪次，比如2，4，8，16。如果不能保證，則應(yīng)盡量讓LOG(2, num)越接近于某個(gè)2的冪次。

常規(guī)HASH和線(xiàn)性HASH增加和收縮分區(qū)原理是一樣的。增加和收縮分區(qū)后原來(lái)的數(shù)據(jù)會(huì)根據(jù)現(xiàn)有的分區(qū)數(shù)量重新分布。HASH分區(qū)不能刪除分區(qū)，所以不能使用DROP PARTITION操作進(jìn)行分區(qū)刪除操作。可以通過(guò)ALTER TABLE ... COALESCE PARTITION num合并分區(qū)，這里的num是減去的分區(qū)數(shù)量。可以通過(guò)ALTERTABLE ... ADD PARTITION PARTITIONS num來(lái)增加分區(qū)，這里是num是增加的分區(qū)數(shù)量。

• KEY分區(qū)

和HASH分區(qū)類(lèi)似，但是KEY分區(qū)不允許使用自定義的表達(dá)式，需要使用MySQL Server提供的HASH函數(shù)。

MYSQL支持兩種KEY分區(qū), 常規(guī)KEY(KEY)分區(qū)和線(xiàn)性KEY(LINEARKEY) 分區(qū),其中計(jì)算區(qū)別和HASH分區(qū)一樣。

常規(guī)KEY示例代碼：

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

線(xiàn)性KEY示例代碼：

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

分區(qū)鍵的注意事項(xiàng)

無(wú)論采用哪種分區(qū)，要么分區(qū)表上沒(méi)有主鍵/唯一鍵，要么分區(qū)表的主鍵/唯一鍵都必須包含分區(qū)鍵，即不能使用主鍵/唯一鍵字段之外的其它字段分區(qū)。

針對(duì)只包含唯一鍵的表給出示例分析如下：

下面的三個(gè)例子是不正確的：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;


CREATE TABLE t3 (
         col1 INT NOT NULL,
         col2 DATE NOT NULL,
         col3 INT NOT NULL,
         col4 INT NOT NULL,
         UNIQUE KEY (col1, col2),
         UNIQUE KEY (col3)
 )
 PARTITION BY HASH(col1 + col3)
 PARTITIONS 4;

在以上三個(gè)示例中的表都有至少一個(gè)唯一鍵，但是在唯一鍵中不包括分區(qū)表達(dá)式中使用的所有列。因此在創(chuàng)建的時(shí)候就會(huì)報(bào)錯(cuò)：ERROR 1491 (HY000): A PRIMARY KEY must include all columns in thetable's partitioning function。

以下三個(gè)示例是正確的：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;


CREATE TABLE t3 (
      col1 INT NOT NULL,
      col2 DATE NOT NULL,
      col3 INT NOT NULL,
      col4 INT NOT NULL,
      UNIQUE KEY (col1, col2, col3),
      UNIQUE KEY (col3)
 )
PARTITION BY HASH(col3)
PARTITIONS 4;

在以上三個(gè)示例中表的分區(qū)鍵都是所屬表的所有唯一鍵包含的字段，因此可以成功創(chuàng)建。

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

在上面這個(gè)示例表是沒(méi)有辦法分區(qū)的，因?yàn)闊o(wú)法在分區(qū)鍵中包含屬于兩個(gè)唯一鍵的任何列。

關(guān)于主鍵和唯一鍵是一樣的，這里不重復(fù)做示例分析，秉承一個(gè)原則：主鍵/唯一鍵必須包含分區(qū)鍵的所有列。

分區(qū)表優(yōu)缺點(diǎn)

大多數(shù)互聯(lián)網(wǎng)公司都不建議用MySQL分區(qū)表，雖然它有不少好處，但是不足之處也同樣很多。接下來(lái)講解下對(duì)應(yīng)的優(yōu)缺點(diǎn)。

優(yōu)點(diǎn) ：

• 分區(qū)表對(duì)業(yè)務(wù)透明，只需要維護(hù)一個(gè)表的數(shù)據(jù)結(jié)構(gòu)，DML操作和普通表沒(méi)有區(qū)別。
• DML操作加鎖僅影響操作的分區(qū)，不會(huì)影響未訪問(wèn)分區(qū)。
• 通過(guò)TRUNCATE操作可以快速清理特定分區(qū)數(shù)據(jù)，通過(guò)DROP操作也可以直接刪除掉不需要的分區(qū)和對(duì)應(yīng)的數(shù)據(jù)。
• 通過(guò)大數(shù)據(jù)量分區(qū)能有效降低索引層數(shù)，提高查詢(xún)性能。

缺點(diǎn)：

• DDL操作需要鎖定所有分區(qū)，導(dǎo)致所有分區(qū)上操作都被阻塞。
• 當(dāng)表數(shù)據(jù)量較小時(shí)，分區(qū)表和非分區(qū)表性能相近，分區(qū)表效果有限。
• 當(dāng)表數(shù)據(jù)量較大時(shí)，對(duì)分區(qū)表進(jìn)行DDL或其他運(yùn)維操作難度大、風(fēng)險(xiǎn)高。
• 分區(qū)表在行業(yè)內(nèi)使用較少，社區(qū)資料有限，存在未知風(fēng)險(xiǎn)多。
• 當(dāng)單臺(tái)服務(wù)器性能無(wú)法滿(mǎn)足時(shí)，對(duì)分區(qū)表進(jìn)行分拆的成本較高。
• 當(dāng)分區(qū)表操作不當(dāng)導(dǎo)致訪問(wèn)所有分區(qū)時(shí)，會(huì)導(dǎo)致嚴(yán)重的性能問(wèn)題。
• 使用分庫(kù)分表可以有效降低運(yùn)維操作影響，對(duì)1億數(shù)據(jù)量表做DDL操作需要謹(jǐn)慎評(píng)估，而對(duì)10萬(wàn)數(shù)據(jù)量表做DDL操作可以默認(rèn)其很快完成。
• 使用分庫(kù)分表可以有效減小宕機(jī)或其他故障影響，將數(shù)據(jù)分庫(kù)分表到10套群集上，一套群集發(fā)生故障僅影響一部分的業(yè)務(wù)。
• MySQL不支持自動(dòng)分區(qū)擴(kuò)展，需要手動(dòng)新增分區(qū)并進(jìn)行數(shù)據(jù)再均衡。

總 結(jié)：

對(duì)于上億行或者更大數(shù)量的普通表清理，只能采用DELETE的方式，該方式不但性能差，而且無(wú)法真正回收空間。分區(qū)表可以通過(guò)刪除分區(qū)等方式，對(duì)歷史數(shù)據(jù)進(jìn)行清理的同時(shí)數(shù)據(jù)文件也做了回收，真正釋放了空間、而且效率很高。

很多互聯(lián)網(wǎng)公司不建議用MySQL分區(qū)表，那么我個(gè)人的見(jiàn)解是：

對(duì)于特定場(chǎng)景是可以考慮采用分區(qū)表，如歷史數(shù)據(jù)有明確的分區(qū)范圍，訪問(wèn)不垮分區(qū)，極少的變化操作，查詢(xún)語(yǔ)句邏輯簡(jiǎn)單，無(wú)性能瓶頸。

對(duì)于Oracle這些商業(yè)數(shù)據(jù)庫(kù)，由于商業(yè)授權(quán)導(dǎo)致橫向擴(kuò)展成本較高，且分區(qū)表功能穩(wěn)定，因此通過(guò)硬件擴(kuò)展和分區(qū)來(lái)承擔(dān)大數(shù)據(jù)量帶來(lái)的負(fù)載。

對(duì)于MySQL開(kāi)源數(shù)據(jù)庫(kù)，企業(yè)有資源有能力將很多需求遷移到數(shù)據(jù)庫(kù)外通過(guò)代碼邏輯或者其它替代方式實(shí)現(xiàn)，因此更追求MySQL使用過(guò)程中的簡(jiǎn)單、穩(wěn)定、可靠，且通過(guò)增加服務(wù)器以及分庫(kù)分表更能有效處理數(shù)據(jù)量爆炸式增長(zhǎng)帶來(lái)的性能問(wèn)題。

因此個(gè)人不建議大量使用MySQL分區(qū)表，尤其是在重要的業(yè)務(wù)上。