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

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

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

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

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

一、分區(qū)的類型

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

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

例：定義一個(gè)員工表，根據(jù)員工ID分區(qū)，1~10號(hào)員工一個(gè)分區(qū)，11~20號(hào)員工一個(gè)分區(qū)，依次類推，共建立4個(gè)分區(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í)候明確的知道數(shù)據(jù)所在的分區(qū)，我們可以直接指定分區(qū)：

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

 分區(qū)刪除：

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

alter table employees drop partition p1;

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

分區(qū)新增：

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

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ù)重新劃分，達(dá)到創(chuàng)建新分區(qū)的效果：

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

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

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

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

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

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

Range columns分區(qū)：

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

使用range columns對(duì)多個(gè)列進(jìn)行分區(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直接對(duì)date類型進(jìn)行分區(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:

對(duì)range分區(qū)來(lái)說(shuō)，如果插入數(shù)據(jù)分區(qū)鍵為null，是可以成功的，數(shù)據(jù)會(huì)被放到第一個(gè)分區(qū)中。

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

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

示例，創(chuàng)建一張員工表按照ID進(jìn)行列表分區(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ū)范圍中不存在的話，語(yǔ)句會(huì)返還錯(cuò)誤。如果表使用的是事務(wù)型引擎，如innodb。則這個(gè)事務(wù)會(huì)完全回滾。如果使用的是非事務(wù)引擎，若MyISAM，雖然也會(huì)報(bào)錯(cuò)，但是已插入的行無(wú)法回滾。

下面新建兩張表，一張使用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;

我們?cè)谥虚g放了一個(gè)不存在對(duì)應(yīng)分區(qū)的記錄來(lái)觸發(fā)錯(cuò)誤， 可以看到innodb引擎數(shù)據(jù)沒(méi)有數(shù)據(jù)插入(回滾)。但MyISAM引擎報(bào)錯(cuò)前的數(shù)據(jù)已經(jīng)保留了下來(lái)，但是報(bào)錯(cuò)之后的記錄未執(zhí)行插入。

如果要忽略此錯(cuò)誤，可以在insert語(yǔ)句中使用ignore關(guān)鍵字來(lái)忽略此錯(cuò)誤，只插入符合分區(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');

 可以看到符合條件的記錄被插入，不符合條件的自動(dòng)被過(guò)濾，不返回錯(cuò)誤。

List columns分區(qū)：

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

list partition和null:

對(duì)于列表分區(qū)來(lái)說(shuō)，必須有1個(gè)分區(qū)顯示的指定可以包含null，否則插入會(huì)失敗。

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

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

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

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

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

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

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

例如：對(duì)于4個(gè)hash分區(qū)，ID為5的記錄，會(huì)存儲(chǔ)在mod(5,4)=1，即1號(hào)分區(qū)中。

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

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

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

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

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

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

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

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

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

2. 對(duì)于ID為5的記錄：

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

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

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

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

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

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

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

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

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

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

hahs partition和null:

對(duì)于hash partition和key partition，任何表達(dá)式對(duì)null運(yùn)算，都會(huì)被當(dāng)做返回為0.

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

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

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

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

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

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

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

對(duì)于key partition，由于primary key需要同時(shí)作為partition key，所以執(zhí)行alter table ... drop partition會(huì)報(bào)錯(cuò)（NDB引擎表會(huì)重組織并生成隱藏的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ǔ)上，對(duì)每個(gè)分區(qū)再次進(jìn)行分區(qū)(子分區(qū))。子分區(qū)的分區(qū)類型可以和父分區(qū)不同，因此也叫復(fù)合分區(qū)。

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

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

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

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ū)的基本維護(hù)

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

重建分區(qū)：

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

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

檢查分區(qū)：

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

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

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

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

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

分區(qū)修復(fù)：

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

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

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

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

ALTER TABLE employees OPTIMIZE PARTITION p0;

但是對(duì)于innodb存儲(chǔ)引擎來(lái)說(shuō)，并不支持optimize partition操作，對(duì)單一分區(qū)執(zhí)行會(huì)導(dǎo)致所有的分區(qū)都重建，謹(jǐn)慎使用（Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.）。

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

最新評(píng)論