通常情況下，同一張表的數(shù)據(jù)在物理層面都是存放在一起的。隨著業(yè)務(wù)增長，當同一張表的數(shù)據(jù)量過大時，會帶來管理上的不便。而分區(qū)特性可以將一張表從物理層面根據(jù)一定的規(guī)則將數(shù)據(jù)劃分為多個分區(qū)，多個分區(qū)可以單獨管理，甚至存放在不同的磁盤/文件系統(tǒng)上，提升效率。

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

• 數(shù)據(jù)可以跨磁盤/文件系統(tǒng)存儲，適合存儲大量數(shù)據(jù)。
• 數(shù)據(jù)的管理非常方便，以分區(qū)為單位操作數(shù)據(jù)，不會影響其他分區(qū)的正常運行。
• 數(shù)據(jù)查詢上在某些條件可以利用分區(qū)裁剪(partition pruning)特性，將搜索范圍快速定位到特性分區(qū)，提升查詢性能。

對于應(yīng)用來說，表依然是一個邏輯整體，但數(shù)據(jù)庫可以針對不同的數(shù)據(jù)分區(qū)獨立執(zhí)行管理操作，不影響其他分區(qū)的運行。而數(shù)據(jù)劃分的規(guī)則即稱為分區(qū)函數(shù)，數(shù)據(jù)寫入表時，會根據(jù)運算結(jié)果決定寫入哪個分區(qū)。

MySQL的分區(qū)插件與存儲引擎運行在不同的層，因此大部分存儲引擎都可以利用MySQL的分區(qū)特性，只有少數(shù)存儲引擎（merge，CSV，federated）不支持分區(qū)特性。若某張表使用的分區(qū)特性，則所有的分區(qū)都需要使用相同的存儲引擎，且分區(qū)特性會同時應(yīng)用到數(shù)據(jù)和索引上。

一、分區(qū)的類型

1. Range partition(范圍分區(qū))

Range partition是按照分區(qū)表達式的運算結(jié)果，判斷結(jié)果落在某個范圍內(nèi)，從而將數(shù)據(jù)存儲在對應(yīng)的分區(qū)。各個分區(qū)定義之間需要連續(xù)且不能重疊，范圍分區(qū)通過partition by range子句定義，而分區(qū)的范圍通過values less than子句劃分。

例：定義一個員工表，根據(jù)員工ID分區(qū)，1~10號員工一個分區(qū)，11~20號員工一個分區(qū)，依次類推，共建立4個分區(qū)：

create table employees (
    id int not null primary key,
    first_name varchar(30),
    last_name varchar(30))
    partition by range(id)(
    partition p0 values less than (11),
    partition p1 values less than (21),
    partition p2 values less than (31),
    partition p3 values less than (41)
    );

現(xiàn)在隨便插入幾條數(shù)據(jù)：

insert into employees values(1,'Vincent','Chen');
insert into employees values(6,'Victor','Chen');
insert into employees values(11,'Grace','Li');
insert into employees values(16,'San','Zhang');
commit;

分區(qū)查詢：

如果在查詢時候明確的知道數(shù)據(jù)所在的分區(qū)，我們可以直接指定分區(qū)：

select * from employees partition(p0);  -- 查詢p0分區(qū)
select * from employees partition(p0,p1);  -- 查詢p0和p1分區(qū)

 分區(qū)刪除：

如果某分區(qū)數(shù)據(jù)不再需要的時候，我們可以用alter table ... drop partition來刪除指定分區(qū)，例如刪除分區(qū)p1，采用drop partition的方式可以快速清除歷史數(shù)據(jù):

alter table employees drop partition p1;

 分區(qū)p1被刪除后，所有p1分區(qū)的數(shù)據(jù)都已丟失，此時原p1分區(qū)的范圍將由p2覆蓋。

分區(qū)新增：

對于range分區(qū)來說，分區(qū)新增只能在最大范圍之上增加分區(qū)，因此p1分區(qū)被刪除后就無法通過新增分區(qū)的方式加回了，下例試圖對ID10~20的員工新增一個分區(qū)，系統(tǒng)會返還錯誤。

alter table employees add partition (partition n1 values less than(21));

而在最大的分區(qū)范圍之上是可以的：

alter table employees add partition (partition p4 values less than(51));

 分區(qū)重組織：

如果一定要在分區(qū)之間插入新的分區(qū)，則可以采用重組織的方式，將已有分區(qū)的數(shù)據(jù)重新劃分，達到創(chuàng)建新分區(qū)的效果：

 例如我要將p2劃分為2個分區(qū)，分別是11~20,21~30:

alter table employees reorganize partition p2 into (
partition p1 values less than(21),
partition p2 values less than(31));

 此時原p2分區(qū)被拆分為了p1,p2，數(shù)據(jù)也在2個分區(qū)間重新分布，保證不會丟失。效果就像我們在中間插入了一個分區(qū)一樣。

目前定義的分區(qū)都是有上限的，如果有大于分區(qū)上限的值想插入表中，系統(tǒng)會返還錯誤，為了兼容這種情況，我們可以新增一個分區(qū)，上限為maxvalue。所有大于當前上限的值都會放入這個分區(qū)：

alter table employees add partition(partition pmax values less than(maxvalue));

范圍分區(qū)的條件除了直接用值，還可以用函數(shù)來定義。一個常用的場景就是按時間分區(qū)，例如：在create table中使用partition by range(year(hire_date))，可以按照年份來進行分區(qū)。這種分區(qū)方式在需要定期清理過期數(shù)據(jù)的場景會非常方便。

Range columns分區(qū)：

Range分區(qū)還有一個變種，叫做range columns分區(qū)。此分區(qū)方式允許使用多個column來作為分區(qū)范圍條件。但是此分區(qū)方式不能接受函數(shù)，只能直接用列的名稱。但是對分區(qū)列的類型不再限制為整數(shù)，可以使用string,date等類型。

使用range columns對多個列進行分區(qū)：

CREATE TABLE rc2 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE) );

使用range columns直接對date類型進行分區(qū)：

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE);

Range partition和null:

對range分區(qū)來說，如果插入數(shù)據(jù)分區(qū)鍵為null，是可以成功的，數(shù)據(jù)會被放到第一個分區(qū)中。

2. List partition(列表分區(qū))

列表分區(qū)和范圍分區(qū)類似，主要區(qū)別是list partition的分區(qū)范圍是預(yù)先定義好的一系列值，而不是連續(xù)的范圍。列表分區(qū)采用partition by list和values in子句定義。

示例，創(chuàng)建一張員工表按照ID進行列表分區(qū)：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
PARTITION BY LIST(id) (
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (2,4,6,8,10)
);

和range分區(qū)一樣，可以使用alter table ... add/drop partition新增/刪除分區(qū)：

ALTER TABLE employees ADD PARTITION(PARTITION p2 VALUES IN (11,12,13,14,15));

ALTER TABLE employees DROP PARTITION p0;

List partition和非事務(wù)引擎：

如果插入的值在list分區(qū)范圍中不存在的話，語句會返還錯誤。如果表使用的是事務(wù)型引擎，如innodb。則這個事務(wù)會完全回滾。如果使用的是非事務(wù)引擎，若MyISAM，雖然也會報錯，但是已插入的行無法回滾。

下面新建兩張表，一張使用innodb,一張使用myisam:

CREATE TABLE employees_innodb (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
) ENGINE=InnoDB   -- 事務(wù)型引擎
PARTITION BY LIST(id) (
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (2,4,6,8,10));

CREATE TABLE employees_myisam (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
) ENGINE=MyISAM   -- 非事務(wù)型引擎
PARTITION BY LIST(id) (
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (2,4,6,8,10));

insert into employees_innodb values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');
insert into employees_myisam values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');

select * from employees_innodb;
select * from employees_myisam;

我們在中間放了一個不存在對應(yīng)分區(qū)的記錄來觸發(fā)錯誤， 可以看到innodb引擎數(shù)據(jù)沒有數(shù)據(jù)插入(回滾)。但MyISAM引擎報錯前的數(shù)據(jù)已經(jīng)保留了下來，但是報錯之后的記錄未執(zhí)行插入。

如果要忽略此錯誤，可以在insert語句中使用ignore關(guān)鍵字來忽略此錯誤，只插入符合分區(qū)條件的值：

insert ignore into employees_innodb values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');
insert ignore into employees_myisam values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');

 可以看到符合條件的記錄被插入，不符合條件的自動被過濾，不返回錯誤。

List columns分區(qū)：

和range分區(qū)類似，list partition也有一個變種的list columns 分區(qū)，此分區(qū)類型可以使用多個列同時作為分區(qū)條件，且不再限制整數(shù)。并且可以使用string,date等數(shù)據(jù)類型作為分區(qū)條件。

list partition和null:

對于列表分區(qū)來說，必須有1個分區(qū)顯示的指定可以包含null，否則插入會失敗。

3. Hash partition(哈希分區(qū))

Hash partition主要的應(yīng)用場景是將數(shù)據(jù)平均的分布在指定數(shù)量的hash分區(qū)中。在range和list分區(qū)類型中，根據(jù)分區(qū)條件的計算結(jié)果，數(shù)據(jù)可以確定存儲在哪個分區(qū)，而在hash分區(qū)中，數(shù)據(jù)存儲在某個分區(qū)是由數(shù)據(jù)庫自己決定的，你只需要指定分區(qū)的數(shù)量。

要創(chuàng)建hash分區(qū)，你需要使用create table的partition by hash(expr)子句，其中expr是整數(shù)類型的列或返還整數(shù)的表達式。另外還需要使用partions num來指定hash分區(qū)的數(shù)量(若忽略partitions子句則默認只創(chuàng)建1個hash分區(qū))。

例：創(chuàng)建一個具有4個hash分區(qū)的表，按照ID進行分區(qū)：

CREATE TABLE employees (
    id INT NOT NULL,
    first_name VARCHAR(30),
    last_name VARCHAR(30)
)
PARTITION BY HASH(id)
PARTITIONS 4;

Hash分區(qū)的性能考慮：

對于hash分區(qū)，數(shù)據(jù)庫決定將數(shù)據(jù)存儲在哪個分區(qū)是采用取余的方式進行運算的。存儲分區(qū)的編號n = mod(expr, num)，其中expr是分區(qū)值，num為定義的分區(qū)數(shù)量。

例如：對于4個hash分區(qū)，ID為5的記錄，會存儲在mod(5,4)=1，即1號分區(qū)中。

因此，最佳的分區(qū)鍵值的變化方式應(yīng)該是線性變化，此時使用hash分區(qū)的效率最高，分布也會均勻。由于分區(qū)鍵值expr在每次insert/update/delete時都會運算，太復雜的表達式也會帶來負面的性能影響，在選擇時也需要考慮。

Liner hash partition(線性哈希分區(qū))：

以上的分區(qū)類型即普通hash分區(qū)，另外還有一類變種叫做liner hash partition(線性哈希分區(qū))，線性哈希分區(qū)的區(qū)別是其使用了更復雜的計算方法來確定數(shù)據(jù)的分布：

1. 對于分區(qū)數(shù)量為num的分區(qū)表，先計算V：
   V = POWER(2, CEILING(LOG(2, num)))

2. 對于分區(qū)值與V-1進行位與運算
   N = expr & (V - 1)   -- 位與運算

3. 對于N>num的情況，再次計算
   N = N & ((v/2)-1)  

例：假設(shè)分區(qū)數(shù)量為4

1. 先根據(jù)分區(qū)數(shù)量num計算V的值，先log再power,如果分區(qū)數(shù)量如果是2的次方，則此公式計算結(jié)果不變。

V = power(2,ceilling(log(2,4)))  = power(2,ceillling(2)) = power(2,2) = 4

2. 對于ID為5的記錄：

N = 5 & (V-1) = 5 & 3 = 1

3. 第二步計算出的結(jié)果為1，N<=num，不再需要第三步計算，數(shù)據(jù)存儲在1號分區(qū)。

計算機的位計算效率是非常高的，因此Liner hash在新增/刪除/合并/分裂分區(qū)場景（需要重新計算并分布數(shù)據(jù)）速度會快很多，非常適合特別大的數(shù)據(jù)存儲場景（TB級別）。

Liner hash的缺點是數(shù)據(jù)的分布可能沒有普通hash均勻。

管理hash分區(qū)數(shù)量：

Hash分區(qū)無法像range和list那樣添加和刪除分區(qū)，但是你可以用alter table的coalesce partition子句來減少hash分區(qū)的數(shù)量，用add partition partitions N來增加指定數(shù)量的分區(qū)。

例：將employees的hash分區(qū)數(shù)量由4調(diào)整為3：

ALTER TABLE employees COALESCE PARTITION 1;  -- 移除一個分區(qū)

例：為employees表新增3個hash分區(qū)：

ALTER TABLE employees ADD PARTITION PARTITIONS 3;  -- 新增3個hash分區(qū)

hahs partition和null:

對于hash partition和key partition，任何表達式對null運算，都會被當做返回為0.

4. Key partition(鍵值分區(qū))

Key paritition與hash分區(qū)類似，主要區(qū)別在于key partition的hash函數(shù)是由MySQL server提供的，且使用主鍵（或非空唯一鍵）作為分區(qū)列：

例如：創(chuàng)建一個2個分區(qū)的key partition table:

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()  -- 未指定分區(qū)列，自動使用主鍵
PARTITIONS 2;

CREATE TABLE k1 (
    id INT NOT NULL,  -- 如果未定義not null，創(chuàng)建表會失敗
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()  -- 未定義主鍵，自動使用unique key
PARTITIONS 2;

另外對于key partition，paritition key也不像其他分區(qū)類型那樣限制為整數(shù)類型，例如，可以使用字符型作為分區(qū)鍵：

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY   -- 字符型主鍵，同時作為partition key
)
PARTITION BY KEY(s1)
PARTITIONS 10;

對于key partition，由于primary key需要同時作為partition key，所以執(zhí)行alter table ... drop partition會報錯（NDB引擎表會重組織并生成隱藏的primary key,不受此限制）。

mysql> alter table k1 drop primary key;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
-- 主鍵刪除失敗

Key partition和hash partition一樣，也有l(wèi)iner key分區(qū)。

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

Key partition數(shù)量的管理方法與hash partition相同。

二、subparitioning(子分區(qū))

subpartitioning可以在原有分區(qū)表的基礎(chǔ)上，對每個分區(qū)再次進行分區(qū)(子分區(qū))。子分區(qū)的分區(qū)類型可以和父分區(qū)不同，因此也叫復合分區(qū)。

下面的示例即對range partition的每個分區(qū)再次進行hash partition。父分區(qū)p0,p1,p2將各包含2個子分區(qū)，因此最終分區(qū)的數(shù)量為3*2=6個

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )        -- 父分區(qū)采用range partition
    SUBPARTITION BY HASH( TO_DAYS(purchased) )   -- 子分區(qū)采用hash partition
    SUBPARTITIONS 2                              -- 子分區(qū)數(shù)量為2
    (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

你也可以顯示的定義每個子分區(qū)的名稱，但如果采用這種方式定義，則必須顯示指定所有子分區(qū)。且子分區(qū)的數(shù)量必須相同，子分區(qū)名稱不能重復：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )         --父分區(qū)為range partition
    SUBPARTITION BY HASH( TO_DAYS(purchased) )    --子分區(qū)為hash partition
   (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,                      -- 顯式指定子分區(qū)名稱和數(shù)量
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

三、分區(qū)的基本維護

分區(qū)表的一大優(yōu)勢就是各個分區(qū)可以獨立存儲和管理。因此大部分在表級別上的管理操作都可以應(yīng)該在分區(qū)上。

重建分區(qū)：

重建分區(qū)功能相當于清除分區(qū)中所有記錄，然后在重新插入。對于頻繁的更新的分區(qū)，可以定期使用重建的方式清除碎片。

ALTER TABLE employees REBUILD PARTITION p0, p1;  -- 重建p0,p1分區(qū)
ALTER TABLE employees REBUILD PARTITION ALL;     -- 重建所有分區(qū)

檢查分區(qū)：

你可以用check table語句來檢查指定分區(qū)是否存在損壞：

ALTER TABLE employees CHECK PARTITION p1;  -- 檢查p1分區(qū)
ALTER TABLE employees CHECK PARTITION all; -- 檢查所有分區(qū)

鍵值分布統(tǒng)計：

使用alter table ... analyze partition可以統(tǒng)計指定分區(qū)的鍵值分布，已便更好的生成執(zhí)行計劃：

ALTER TABLE employees ANALYZE PARTITION p0;  -- 分析p0分區(qū)
ALTER TABLE employees ANALYZE PARTITION ALL; -- 分析所有分區(qū)

分區(qū)修復：

使用alter table ... repair partition來修復損壞的分區(qū)：

ALTER TABLE employees REPAIR PARTITION p0; -- 分析p0分區(qū)
ALTER TABLE employees REPAIR PARTITION ALL; -- 修復所有分區(qū)

優(yōu)化分區(qū)：

如果分區(qū)存在大量的數(shù)據(jù)更新，你可以使用optimize partition來回收空間，收集統(tǒng)計信息。其效果相當于在分區(qū)上執(zhí)執(zhí)行check partition、analyze partition 和 repair partition一樣。

ALTER TABLE employees OPTIMIZE PARTITION p0;

但是對于innodb存儲引擎來說，并不支持optimize partition操作，對單一分區(qū)執(zhí)行會導致所有的分區(qū)都重建，謹慎使用（Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.）。

到此這篇關(guān)于MySQL分區(qū)表的具體使用的文章就介紹到這了,更多相關(guān)Mysql分區(qū)表 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論