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

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

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

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

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

分區(qū)表的分區(qū)類型

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

• RANGE分區(qū)

給定一個連續(xù)的區(qū)間范圍（區(qū)間要求連續(xù)并且不能重疊），某個字段的值滿足這個范圍就會被分配到該分區(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ù)每個員工離開公司的年份進行劃分，對于1991年之前離職員工存儲在分區(qū)p0中，1991年至1995年離職的人存儲在分區(qū)p1，1996年至2000年離職的人存儲在分區(qū)p2中。

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

若設(shè)置了 LESS THAN MAXVALUE分區(qū)，添加新分區(qū)時需要重新分區(qū)，此時存儲在LESS THAN MAXVALUE分區(qū)中的數(shù)據(jù)會根據(jù)重新分區(qū)規(guī)則進行數(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
);

對于日期類型的字段，也可以使用UNIX_TIMESTAMP()函數(shù)，根據(jù)TIMESTAMP列的值按RANGE對表進行分區(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ū)是基于列值匹配一個離散值集合中的某個值來進行選擇分區(qū)。LIST分區(qū)沒有類似RANGE分區(qū)中“VALUES LESS THAN MAXVALUE”包含其他值在內(nèi)的定義，其要匹配的任何值都必須在值列表中找到，如果不在列表中的數(shù)據(jù)插入表中操作會失敗。

示例代碼：

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ū)

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

MySQL支持兩種HASH分區(qū)，常規(guī)HASH(HASH)分區(qū)和線性HASH(LINEARHASH) 分區(qū)。常規(guī)HASH分區(qū)使用取模算法，對應(yīng)一個表達式expr可以計算出它被保存到哪個分區(qū)中，N = MOD(expr, num)。線性HASH分區(qū)使用一個線性的2的冪運算法則，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ū)非常簡便，使用HASH函數(shù)值的模數(shù)，可以讓數(shù)據(jù)平均的分布到每一個分區(qū)，但是由于分區(qū)在創(chuàng)建表的時候已經(jīng)固定了，如果新增或者收縮分區(qū)的數(shù)據(jù)遷移比較大。

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

語法上線性HASH分區(qū)和HASH分區(qū)之間的唯一區(qū)別是在PARTITION BY子句中添加了LINEAR關(guā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;

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

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

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

如果num的值是2的冪數(shù)，那么這個表達式計算出來的結(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代表計算出來的數(shù)據(jù)所在分區(qū)編號。
• F代表對分區(qū)鍵進行的操作，該操作返回一個整數(shù)值。
• &代表位與運算。

當num是2的倍數(shù)時由于V計算出來的結(jié)果不變，這個時候線性HASH算法的計算結(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中計算出來的N比總分區(qū)數(shù)量num大，則需要執(zhí)行本步驟操作，直到N

接下來用一個示例解釋下線性HASH算法找具體分區(qū)號的實現(xiàn)原理。創(chuàng)建一個有六個分區(qū)的線性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()在這里對應(yīng)YEAR()，那么根據(jù)算法原理可以確定第一個分區(qū)號如下所示：

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

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

= 2003 & 7

= 3

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

當插入的值是'1998-10-19'計算所存儲的分區(qū)號如下所示：

V = 8

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

= 1998 & 7

= 6

判斷6>=6成立，所以需要做下一步運算。

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

= 6 & 3

= 2

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

下面是用EXCEL做的一個簡單的測試，測算了一下MySQL使用線性HASH分區(qū)算法，將分區(qū)鍵值?。?-1048575），分區(qū)數(shù)量分別為4、5、6、7、8個時，各分區(qū)數(shù)據(jù)數(shù)量如圖所示：

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

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

• KEY分區(qū)

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

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

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

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

線性KEY示例代碼：

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

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

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

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

下面的三個例子是不正確的：

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;

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

以下三個示例是正確的：

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;

在以上三個示例中表的分區(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)
);

在上面這個示例表是沒有辦法分區(qū)的，因為無法在分區(qū)鍵中包含屬于兩個唯一鍵的任何列。

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

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

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

優(yōu)點 ：

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

缺點：

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

總 結(jié)：

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

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

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

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

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

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