[MySQL Reference Manual] 20 分區，referencemanual

[MySQL Reference Manual] 20 分區，referencemanual

20 分區

20 分區... 1

20.1 MySQL的分區概述... 2

20.2 分區類型... 3

20.2.1 RANGE分區... 3

20.2.2 LIST分區... 5

20.2.3 COLUMNS分區... 7

20.2.3.1 RANGE COLUMNS分區... 7

20.2.3.2 LIST COLUMNS分區... 7

20.2.4 Hash分區... 8

20.2.4.1 LINEAR HASH分區... 8

20.2.5 Key分區... 9

20.2.6 子分區... 9

20.2.7 MySQL分區如何處理NULL. 11

20.3 分區管理... 11

20.3.1 RANGE和LIST分區管理... 11

20.3.2 管理HASH和KEY分區... 12

20.3.3移動表的分區和子分區... 13

20.3.4 管理分區... 15

20.3.5 獲取分區的信息... 15

20.4 分區裁剪(Pruning)16

20.5 分區選擇... 19

20.6 分區的限制和缺點... 19

20.6.1 分區鍵，主鍵，唯一鍵... 21

20.6.2 各個存儲引擎分區表限制... 21

20.6.3 分區表對函數的限制... 21

20.6.4 分區和鎖... 21

 

可以通過show plugins查看是否支持分區。

mysql> SHOW PLUGINS;

+------------+----------+----------------+---------+---------+

| Name       | Status   | Type           | Library | License |

+------------+----------+----------------+---------+---------+

| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |

| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |

| ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |

+------------+----------+----------------+---------+---------+

11 rows in set (0.00 sec)

也可以檢查information_schema.plugins表檢查是否支持。

如果partition的status不是active，或者沒有記錄。那麼就不支持分區。

如果編譯的時候已經支持了分區，就不需要去啟動。如果想要不支持分區，可以使用參數—skip-partition選項。不啟動分區後，不能看到已經分區的表，也不能刪除他們。

20.1 MySQL的分區概述

SQL標准一般不會涉及到關於數據存放物理方面。SQL語言本身盡量從數據結構，schema，表，行，列中獨立出來。但是很多高級的數據庫管理系統都涉及了某些數據存放的物理位置，文件系統，硬件等。在MySQL，InnoDB存儲引擎，支持表空間已經很久，MySQL服務可以把不同的數據庫存放到不同的物理目錄中。

分區者更進了一步，把一個表根據規則分布到文件系統。實際上表的不同分區以獨立表的方式被保存在不同的位置上。用戶選擇的分區規則，在MySQL中可以是一個模塊，一個range或者一個值的列表，或者內部hash函數，或者線性hash函數。這個方法根據用戶指定的分區類型決定，參數為用戶提供表達式的值。這個表達式可以是一個列的值，可以是一個或者多個列的值，也可以是列值的集合，根據分區類型決定。

比如RANGE,LIST,[LINEAR]HASH分區，把分區列傳遞到分區函數，然後返回一個整型表示該行應該存放的分區號。函數必須是非常量或者非隨機。也不能包含查詢但是可以使用SQL表達式只要表達式返回要不是NULL要不是整型數據。

對於[LINEAR] KEY,RANGE COLUMNS,LIST COLUMNS分區，分區表達式由一個或者多個列組成。

對於[LINEAR] KEY表達式，分區函數由MySQL提供。

這個就是水平分區，對於垂直分區目前不支持。大多數存儲引擎是支持分區的，MySQL分區引擎是獨立的一層，可以和其他引擎進行交互。在MySQL 5.7一個表的所有分區必須使用相同的引擎。以下引擎不支持分區：MERGE，CSV，FEDERATED存儲引擎。

要指定分區存儲引擎，和非分區表一樣，指定engine參數：

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

每個分區都可以指定存儲引擎，但是在MySQL 5.7中沒有效果。

每個分區的數據和索引可以適應data directory 和index directory選項分配獨立的目錄。DATA DIRECTOY和INDEX DIRECTORY對於windows的myisam存儲引擎不支持。Innodb所有平台都支持。

分區表達式中的涉及到的列必須是唯一索引的一部分，包括主鍵。也就是說以下表不能創建分區：

CREATE TABLE tnp (
    id INT NOT NULL AUTO_INCREMENT,
    ref BIGINT NOT NULL,
    name VARCHAR(255),
    PRIMARY KEY pk (id),
    UNIQUE KEY uk (name)
);

因為pk，uk沒有相同的列，沒有列可以用來作為分區列。要麼pk上加上name，id加到uk，也可以直接刪掉uk。

分區表的一些好處：

1.分區表可以把一個表的數據分散到不同的文件系統或者磁盤中。

2.分區表的數據比較容易刪除，可以直接刪除一個分區。

3.如果where子句可以滿足分區列，那麼查詢性能會得到提升。

其他的好處：

1.聚合函數，如果在分區表上可以並發。

2.數據分散在多個磁盤上，加大查詢的吞吐量。

20.2 分區類型

20.2.1 RANGE分區

安裝RANGE分區表的，行會根據RANGE的劃分存放到分區中。RANGE是連續的但是沒有重疊，使用VALEUS LESS THAN定義。對於store_id進行分區：

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 (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

在這個分區框架，所有的行store_id從1到5都保存在p0分區中，6到10保存在p1中類推。在這裡如果插入21就會報錯因為沒有地方存放這個記錄。可以做一下修改存放大記錄：

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 (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE表示最大值，比int型最大值還要大。所以大於16的都會被放在p3分區中。分區列也可以使用表達式：

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

那麼在1991年之前離職的員工都會保存在p0依次類推。如果分區列時個時間戳字段，可以如下分區：

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('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

RANGE在以下場景下非常有用：

1.想要刪除老的數據，剛好在p0上，那麼可以直接alter table employee drop partition p0刪除分區，達到刪除數據的目的

2.想要使用包含時間或者日期的列，或者其他連續的升序列。

3.想要頻繁的根據分區列進行查詢。可以快速的定位到某個分區。

有個RANGE分區的變種RANGE COLUMNS，可以多個列一起決定一個分區。

20.2.2 LIST分區

很多情況下range分區和list分區很像。LIST和RANGE的區別是LIST是一組值，RANGE是一組連續的區間。

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

LIST分區刪除數據也會很快比如要刪除pWest分區數據，用階段分區比delete快。和RANGE不同沒有MAXVALUE。所有的值都要在分區裡面，不然就報錯。

mysql> CREATE TABLE h2 (

    ->   c1 INT,

    ->   c2 INT

    -> )

    -> PARTITION BY LIST(c1) (

    ->   PARTITION p0 VALUES IN (1, 4, 7),

    ->   PARTITION p1 VALUES IN (2, 5, 8)

    -> );

Query OK, 0 rows affected (0.11 sec)

 

mysql> INSERT INTO h2 VALUES (3, 5);

ERROR 1525 (HY000): Table has no partition for value 3

報錯的時候，如果表示innodb表就會全部回滾。如果是非事務表，錯誤之前都插入了，之後的都沒插入。

也可以通過IGNORE關鍵字，對錯誤行進行忽略，這樣錯誤行就會自動被忽略，正常行就可以被全部插入。

mysql> TRUNCATE h2;

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM h2;

Empty set (0.00 sec)

 

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);

Query OK, 3 rows affected (0.00 sec)

Records: 5  Duplicates: 2  Warnings: 0

 

mysql> SELECT * FROM h2;

+------+------+

| c1   | c2   |

+------+------+

|    7 |    5 |

|    1 |    9 |

|    2 |    5 |

+------+------+

3 rows in set (0.00 sec)

20.2.3 COLUMNS分區

COLUMNS分區是RANGE和LIST的變種。COLUMNS分區可以可以使用多個列作為分解鍵。2個列都可以用來分配分區。另外RANGE COLUMNS和LIST COLUMNS分區支持費insert定義range或者list項。允許的字段類型如下：

1.所有int類型

2.date和datetime類型

3.字符串類型，char，varchar，binary和varbinary。TEXT和BLOB列不支持分區。

20.2.3.1 RANGE COLUMNS分區

RANGE列分區和RANGE分區很像。就是啟用了多列range。另外你可以使用其他數據類型，不一定只用int類型。

RANGE COLUMNS和RANGE明顯的區別有以下幾點：

1.RANGE COLUMNS不能用表達式，只能是列名

2.RANGE COLUMNS可以使用一個或者多個列。

3.RANGE COLUMNS分區列可以不是int類型。

mysql> CREATE TABLE rcx (

    ->     a INT,

    ->     b INT,

    ->     c CHAR(3),

    ->     d INT

    -> )

    -> PARTITION BY RANGE COLUMNS(a,d,c) (

    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),

    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),

    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),

    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)

    -> );

Query OK, 0 rows affected (0.15 sec)

如創建了一個rcx表，有4個列，a,b,c進行分區。那麼如果有一樣要插入，根據a,b,c依次對比，來決定放入哪個分區。

20.2.3.2 LIST COLUMNS分區

MySQL 5.7支持LIST COLUMNS分區。是LIST分區的變種可以使用非int類型作為分區列，而且可以使用多個列。

20.2.4 Hash分區

使用HASH分區可以保證數據均勻的分布在各分區上面。使用RANGE，LIST分區需要顯示給定值進行分區。使用hash分區，MySQL會幫你處理。

使用hash分區，使用create table子句PARTITION BY HASH(expr)，表達式返回int類型。然後需要指定分區個數，如PARTITIONS 4。

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(store_id)
PARTITIONS 4;

如果不包含PARTITIONS 那麼默認分區個數是1個。

20.2.4.1 LINEAR HASH分區

MySQL也支持線性hash分區，和傳統分區不一樣線性hash利用線性2的指數算法，來代替老的hash函數。

算法大致如下：

num表示分區個數

1.V=POWER(2,CEILING(LOG(2,NUM)))，V為num的2的最小冪次的值。

2.N=expr&(V-1)計算分區號

3.如果N>=num,N=N&(ceil(V/2)-1)，否則就用N編號的分區。

如表如下：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

插入了col3為‘20030414’的列：

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

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

   = 2003 & 7

   = 3

 

(3 >= 6 is FALSE: record stored in partition #3)

如果N>=num那麼就需要額外計算，比如：

V = 8

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

  = 1998 & 7

  = 6

 

(6 >= 6 is TRUE: additional step required)

 

N = 6 & CEILING(8 / 2)

  = 6 & 3

  = 2

 

(2 >= 6 is FALSE: record stored in partition #2)

使用線性hash的好處是增加，刪除，合並分立分區會比較快，特別是對於T級別的表來說。壞處是不能像傳統hash分區一樣的數據均勻。

20.2.5 Key分區

Key分區和hash分區有點類似，但是和HASH分區有很多不同：

1.KEY不單單是hash

2.如果表有主鍵，key被用於分區的必須是表主鍵的一部分。不需要指定分區列。如果沒有primary key但是有唯一建那麼使用唯一建作為分區列。如果唯一建沒有定義為 not null那麼就會報錯。

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

20.2.6 子分區

子分區創建如圖：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

ts表有3個RANGE分區，然後每個range分區有2個hash子分區。表一共有6個分區。

MySQL 5.7可以對RANGE LIST分區的表進行子分區，子分區要不是hash要不是key。

也可以使用通過SUBPARTITION子句顯示的指定選項，比如：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

一些注意點：

1.每個分區的子分區個數要一樣。

2.如果使用subpartition顯示的定義分區，就必須定義所有的分區，否則就會失敗。

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s2,
            SUBPARTITION s3
        )
    );

3.每個子分區必須包含子分區的分區名。

4.分配的子分區名必須在表內是唯一的。

20.2.7 MySQL分區如何處理NULL

在MySQL分區中，null會被認為小於任何非null的值和order by 一樣。

RANGE分區處理NULL

RANGE分區中，NULL值會被放入最小的分區中。

LIST分區處理NULL

list分區中，如果分區list沒有null值，插入就會報錯。需要在分區list中指定null存放的位置。

Hash和Key分區NULL處理

hash和Key分區對NULL的處理方法和上面的不同，HASH和KEY會把null當成0來處理。

20.3 分區管理

20.3.1 RANGE和LIST分區管理

首先刪除分區，刪除一個分區可以直接用alter table … drop partition來實現。但是刪除分區權限需要有表的drop權限。

如果要修改分區，但是不想丟失數據可以使用alter table …reorganize partition語句。

刪除分區之後表結構變換，可以使用show create table來查看表結構變化。

 

如果使用range分區，要增加一個高端的分區可以使用alter table …add partition語句，比如：

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1970),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (1990)
);

那麼要增加一個2000的分區可以使用如下語句：

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000)

但是如果要加一個小端的分區比如1960，那麼就不能在使用這個語句，要重新整理分區才行，語句如下：

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1960),
        PARTITION n1 VALUES LESS THAN (1970)
);

P0分區會被分為n0,n12個分區。

對於list分區可以直接通過alter table…add partition添加分區。但是如果list值裡面已經包含在老的分區中就會報錯。如果需要拆分已有的分區也需要使用alter table … reorganize partition語句。

語法如下：

ALTER TABLE tbl_name

    REORGANIZE PARTITION partition_list

    INTO (partition_definitions);

使用alter table… reorganize partition的時候有以下幾個注意點：

1.Partition子句用來定義性分區的和create table的規則是一樣的。

2.partition_Definitions中的分區定義要能夠覆蓋partition_list的分區。

3.對於range分區，partition_list必須是連續的不能跳過中間的一個分區。

4.修改表的分區類型不能通過alter table…reorganize partition，也不能修改分區的表達式或者列。只能通過語句修改：

ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;

20.3.2 管理HASH和KEY分區

被hash或者key分區的分區表不能刪除分區。但是可以通過alter table…coalesce partition語句合並分區。如：

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

表有12個分區，那麼可以通過以下語句把分區收縮到4個。

mysql> ALTER TABLE clients COALESCE PARTITION 4;

Query OK, 0 rows affected (0.02 sec)

Alter table…coalesce partition可以使用在hash，key，linear hash或者linear key。

如果Alter table…coalesce partition指定了大於現在分區的個數，就會報錯。

如果要增加分區可以使用alter table…add partition實現：

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

20.3.3移動表的分區和子分區

在MySQL 5.7，可以使用alter table pt exchange partition p with table nt 來移動分區。pt表示一個分區表，p是分區或者子分區。移動到非分區表nt。nt的限制條件：

1.nt不是分區表

2.nt不是臨時表

3.pt和nt 2個表的表結構一直。

4.nt沒有外鍵約束，也沒有其他表引用了nt

5.nt中沒有行。如果加了without validation那麼就不會驗證這個條件。

需要alter，insert，create，drop的權限。

Alter table…exchange partiton注意點：

1.這個語句執行的時候不會調用任何觸發器。

2.auto_increment會被重置

3.ignore關鍵字在這個語句的時候不會起作用。

如：

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES
    (1669, "Jim", "Smith"),    (337, "Mary", "Jones"),
    (16, "Frank", "White"),    (2005, "Linda", "Black");

mysql> CREATE TABLE e2 LIKE e;

Query OK, 0 rows affected (1.34 sec)

 

mysql> ALTER TABLE e2 REMOVE PARTITIONING;

Query OK, 0 rows affected (0.90 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> SELECT PARTITION_NAME, TABLE_ROWS

    ->     FROM INFORMATION_SCHEMA.PARTITIONS

    ->     WHERE TABLE_NAME = 'e';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0             |          1 |

| p1             |          0 |

| p2             |          0 |

| p3             |          3 |

+----------------+------------+

4 rows in set (0.00 sec)

 

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

Query OK, 0 rows affected (0.28 sec)

 

mysql> SELECT PARTITION_NAME, TABLE_ROWS

    ->     FROM INFORMATION_SCHEMA.PARTITIONS

    ->     WHERE TABLE_NAME = 'e';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0             |          0 |

| p1             |          0 |

| p2             |          0 |

| p3             |          3 |

+----------------+------------+

4 rows in set (0.00 sec)

 

mysql> SELECT * FROM e2;

+----+-------+-------+

| id | fname | lname |

+----+-------+-------+

| 16 | Frank | White |

+----+-------+-------+

1 row in set (0.00 sec)

 

20.3.4 管理分區

1.重建分區。就好像刪除表內所有數據，然後重新插入，減少碎片

ALTER TABLE t1 REBUILD PARTITION p0, p1;

2.優化分區。如果刪除了大量數據或者修改了大量數據，使用語句回收不適用空間，減少碎片，innodb不支持對單個分區進行優化，使用rebuild和analyze代替。

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

3.分析分區，讀取key在分區的分布

ALTER TABLE t1 ANALYZE PARTITION p3;

4.修復分區數據。當分區有重復鍵的時候修復會報錯。在MySQL 5.7.2之後可以使用alter ignore table，因為重復鍵問題導致不能應用的都會被刪除。

ALTER TABLE t1 REPAIR PARTITION p0,p1;

5.檢查分區。和檢查表一樣檢查分區是否有問題

ALTER TABLE trb3 CHECK PARTITION p1;

這個命令會告訴你分區在表上是否正常，如果有錯誤運行alter table…repair進行修復。Check partition在用有重復鍵的時候會檢查失敗。同上用alter ignore table進行處理。

20.3.5 獲取分區的信息

獲取分區的方法有以下幾個：

1.使用show create table

2.使用show table status

3.查詢information_schema.partitions表

4.使用explain parititons select查看那些分區被select。

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE(id)
    (
        PARTITION p0 VALUES LESS THAN (3),
        PARTITION p1 VALUES LESS THAN (7),
        PARTITION p2 VALUES LESS THAN (9),
        PARTITION p3 VALUES LESS THAN (11)
    );

 

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: trb1

   partitions: p0,p1,p2,p3

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 10

        Extra: Using filesort

這種情況下所有的4個分區都被使用了，如下語句就只是用了2個分區：

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: trb1

   partitions: p0,p1

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 10

        Extra: Using where

使用explain partitions要注意幾個問題：

1.不能 explain partitions和extentded 不能一起使用會有語法錯誤。

2.如果explain partitions用來檢查非分區表的查詢，不會產生錯誤就是partitions變成null。

20.4 分區裁剪(Pruning)

分區裁剪其實很簡單，就是查詢的時候不掃描不符合條件的分區。比如有一個分區表定義如下：

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

當使用查詢語句：

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

很明顯如果這個條件p0和p3不會返回數據，所以我們需要在p1和p2上查找這樣可以花很少的時間查找到匹配的數據。這種去掉不要用的分區就是裁剪。如果一個sql認為可以被裁剪，那麼比在非分區表上要快。

那麼如果where條件可以分解為，下面條件之一，就可以被裁剪：

1.partition_column = constant

2.partition_column IN (constant1, constant2, ...,constantN)

首先評估給定的值，決定哪個分區包含了這個值，然後掃描這個分區。很多情況下等號可以被一下符號代替，<, >, <=, >=, 和 <>。一些語句使用了between and也可以使用分區裁剪。

第二種情況，優化器評估表達式中的每個值，創建一個符合條件的分區列表，然後掃描分區分區中的列表。

分區裁剪可以使用在select，delete，update語句，但是不能使用在insert語句。

RANGE COLUMNS和LIST COLUMNS分區上使用分區裁剪。

這種優化不管是分區的表達式是由等號組成，或者是一個range但是可以被分解為等號，或者表達式表示一個增加或者減少的關系。

裁剪也可以在分區表達式為使用了year或者to_days 函數的 date或者datetime類型。假設有個表t2如下：

CREATE TABLE t2 (

    fname VARCHAR(50) NOT NULL,

    lname VARCHAR(50) NOT NULL,

    region_code TINYINT UNSIGNED NOT NULL,

    dob DATE NOT NULL

)

PARTITION BY RANGE( YEAR(dob) ) (

    PARTITION d0 VALUES LESS THAN (1970),

    PARTITION d1 VALUES LESS THAN (1975),

    PARTITION d2 VALUES LESS THAN (1980),

    PARTITION d3 VALUES LESS THAN (1985),

    PARTITION d4 VALUES LESS THAN (1990),

    PARTITION d5 VALUES LESS THAN (2000),

    PARTITION d6 VALUES LESS THAN (2005),

    PARTITION d7 VALUES LESS THAN MAXVALUE

);

那麼一下語句可以使用分區裁剪：

SELECT * FROM t2 WHERE dob = '1982-06-23';

UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

對於最後一個語句，優化器如下行為：

1.查找范圍的最小所在的分區。YEAR('1984-06-21')那麼在分區d3中。

2.查找范圍的最大所在的分區。YEAR('1999-06-21') 那麼在分區d5中。

3.那麼掃描2個分區范圍內的所有分區。

 

對於如果分區時LIST分區的，表達式是遞增或者遞減的，比如t3:

CREATE TABLE t3 (

    fname VARCHAR(50) NOT NULL,

    lname VARCHAR(50) NOT NULL,

    region_code TINYINT UNSIGNED NOT NULL,

    dob DATE NOT NULL

)

PARTITION BY LIST(region_code) (

    PARTITION r0 VALUES IN (1, 3),

    PARTITION r1 VALUES IN (2, 5, 8),

    PARTITION r2 VALUES IN (4, 9),

    PARTITION r3 VALUES IN (6, 7, 10)

);

比如語句:

SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3,

那麼優化器會去查找1,2,3所在的分區，r0,r1會跳過r2,r3分區。

對於表分區是hash或者 linear key，分區裁剪只有等號才能使用：

CREATE TABLE t4 (

    fname VARCHAR(50) NOT NULL,

    lname VARCHAR(50) NOT NULL,

    region_code TINYINT UNSIGNED NOT NULL,

    dob DATE NOT NULL

)

PARTITION BY KEY(region_code)

PARTITIONS 8;

那麼以下語句可以裁剪：

UPDATE t4 WHERE region_code = 7;

對於簡單的range也會使用分區裁剪，會把range當成in語句，比如：

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;

SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

2個語句都會被認為region_code in (3,4,5)。當表是hash或者Linear Key分區，裁剪只能是int類型，一下語句不會發生分區裁剪：

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

MySQL 5.7.1，分區裁剪對於自動分區的都不能使用。

20.5 分區選擇

MySQL 5.7支持顯示的選擇分區或者子分區。分區選擇和分區裁剪有點類似但是有點不同：

1.分區選擇需要顯示指定，不是自動完成的。

2.分區選擇可以支持所有的DML語句。

語法如下：

PARTITION (partition_names)

      partition_names:

          partition_name, ...

這個選項緊跟表後面，分區名使用逗號分隔。如果指定的分區名找不到會報錯。當partition已經使用，那麼只會在這個分區查找。

20.6 分區的限制和缺點

禁止的結構，一下結構不能用在分區表達式上：

1.存儲過程，存儲方法，用戶自定義函數，插件。

2.定義的變量和用戶變量

算術操作和邏輯操作，使用+，-，*在分區表達式是允許的，但是結果不能為null，並且是一個int類型。（Linear key分區除外）。DIV操作是允許的但是不能用/。邏輯操作可以使用|，&,^,<<,>>，~ 操作是不允許的。

HANDEL語句，之前分區表不支持HANDEL語句，MySQL 5.7.1之後允許使用。

Server SQL Mode，在分區表創建後，用戶定義分區不在維護sql mode。

性能問題，一些分區操作對性能有影響的如下：

·         文件系統操作，分區和重新分區操作依賴於文件系統操作。也就是說影響速度的有，文件系統類型，特點，磁盤速度，交換區空間，系統文件處理性能，和MySQL選項和變量對文件的處理。特別是保證large_files_support和open_files_limit的設置對於myisam的分區表，增加myisam_max_sort_file_size可能會提高性能。分區和重新分區操作如果是innodb表應該要啟動innodb_file_per_table。

·         MyISAM和分區文件描述符的使用，對於分區的MyISAM表，MySQL為每個分區使用2個文件描述符，對於分區表來說消耗的描述符大於普通表的描述符。

·         表鎖，在執行分區操作的時候表上有寫鎖。那麼讀取這個表相對來說是不影響的，但是insert和update會等到知道分區操作完成。

·         存儲引擎，分區操作，查詢和更新操作通常在myisam中比innodb中要快。

·         索引，分區裁剪，一般使用索引就可以加快查詢速度，特別是可以完美的利用分區裁剪的查詢。

·         LOAD Data性能，在MySQL 5.7 Load data使用buffer來提高導入性能。要注意每個分區使用130KB的buffer。

分區最大個數，如果不適用NDB，那麼分區最大個數是8192個包括子分區。

Query Cache不支持，查詢緩存不被支持。

每個分區的Key cache，在MySQL 5.7，key cache支持MyISAM表。

Innodb分區表不支持外鍵，更嚴格的是：

1.innodb分區表不能使用外建，包含了外鍵的innodb不能定義分區

2.innodb表不能引用分區表的外鍵，innodb分區表不能被引用。

Alter table order by，這個語句只會在各個分區內排序。

使用replace修改primary key，可能會導致語句被徹底修改。

全文索引，分區表不支持全文索引或者掃描。

空間列，空間數據類型不能被分區表支持。

臨時表，臨時表不能是分區表。

Log表，log表不能使用分區。

數據類型和分區key，分區關鍵字要不是int，要不是表達式返回int。enum類型也不能使用。有2個列外：

1. [Linear] Key分區支持任何非text，blob分區字段類型。

2.當使用range columns或者list columns分區可以使用date，datetime作為分區列。

子查詢，分區key不能是子查詢

子分區，子分區必須使用hash或者key分區。只有range和list分區可以有子分區。

DATA DIRECTORY和INDEX DIRECTORY,data directory和index directory如果是分區表有以下限制：

1.表級別的選項會被忽略

2.在windows 的時候，不能為myisam表指定某個分區或者子分區的該選項。

修復和重建分區表，分區表支持check table，optimize table，analyze table和repair table。

FLUSH TABLES的FOR EXPORT，對於FLUSH TABLES的FOR EXPORT不被分區表支持。

20.6.1 分區鍵，主鍵，唯一鍵

所有的使用在分區表達式的列必須是唯一鍵的一部分。如果沒有唯一鍵，包括沒有主鍵，那麼就不會有這個限制。

20.6.2 各個存儲引擎分區表限制

MERGE存儲引擎,用戶定義分區表和MERGE存儲引擎不兼容。

FEDERATED存儲引擎，FEDERATED表不支持分區表

CSV存儲引擎，不支持分區表

InnoDB存儲引擎，InnoDB外鍵和MySQL分區不兼容。分區innodb表不能有外鍵。Innodb引用了外鍵的不能是分區表。

NDB存儲引擎,NDB支持KEY分區

20.6.3 分區表對函數的限制

只有一下函數可以被分區表支持：

ABS()

CEILING() (see CEILING() and FLOOR())

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

DATEDIFF()

EXTRACT() (see EXTRACT() function with WEEK specifier)

FLOOR() (see CEILING() and FLOOR())

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

TO_SECONDS()

UNIX_TIMESTAMP() (with TIMESTAMP columns)

WEEKDAY()

YEAR()

 

YEARWEEK()

20.6.4 分區和鎖

對於MyISAM存儲引擎，對於MySQL 5.6.5之前的，會鎖整個表知道操作完成，在MySQL 5.7 分區鎖會裁剪不需要的鎖，比如讀取或者更新分區表，只有受影響的表才會被鎖。如果使用了innodb分區表，使用行級別鎖，因此不會像myisam一樣有鎖的分區裁剪。

DML語句

Select語句只會鎖定需要讀取的分區

Update鎖裁剪只會對更新非分區列有用。

Replace和insert鎖只會鎖要修改的或者插入的分區。如果auto_increment值被每個列生成那麼所有分區都會鎖。

Insert…on duplicate key update只會在非分區列上才會裁剪。

Insert…select只會鎖定被讀取的表，目標表的所有的分區都會被鎖。

Load data鎖不能被裁剪。

對於before insert，before update觸發器都不可以使用分區裁剪，因為觸發器可能會修改值。

DDL語句

Create view不會有鎖

Alter table…exchange partition，鎖裁剪，只會鎖定被切換分區的鎖。

Alter table…truncate partition，鎖裁剪，只會鎖定被截斷的分區。

另外alter table都會鎖元數據

其他語句

Lock tables不能裁剪分區。

Call stored_procedure(expr)支持鎖裁剪，但是expr不行

Do和set語句不支持鎖裁剪