[MySQL Reference Manual] 20 分區 20 分區

<a href="#_Toc451872499">20.1 MySQL的分區概述... 2</a>

<a href="#_Toc451872501">20.2.1 RANGE分區... 3</a>

<a href="#_Toc451872502">20.2.2 LIST分區... 5</a>

<a href="#_Toc451872503">20.2.3 COLUMNS分區... 7</a>

<a href="#_Toc451872504">20.2.3.1 RANGE COLUMNS分區... 7</a>

<a href="#_Toc451872505">20.2.3.2 LIST COLUMNS分區... 7</a>

<a href="#_Toc451872506">20.2.4 Hash分區... 8</a>

<a href="#_Toc451872507">20.2.4.1 LINEAR HASH分區... 8</a>

<a href="#_Toc451872508">20.2.5 Key分區... 9</a>

<a href="#_Toc451872510">20.2.7 MySQL分區如何處理NULL. 11</a>

<a href="#_Toc451872512">20.3.1 RANGE和LIST分區管理... 11</a>

<a href="#_Toc451872513">20.3.2 管理HASH和KEY分區... 12</a>

<a href="#_Toc451872514">20.3.3移動表的分區和子分區... 13</a>

<a href="#_Toc451872516">20.3.5 擷取分區的資訊... 15</a>

<a href="#_Toc451872517">20.4 分區裁剪(Pruning)16</a>

<a href="#_Toc451872519">20.6 分區的限制和缺點... 19</a>

<a href="#_Toc451872521">20.6.2 各個存儲引擎分區表限制... 21</a>

<a href="#_Toc451872522">20.6.3 分區表對函數的限制... 21</a>

可以通過show plugins檢視是否支援分區。

mysql> SHOW PLUGINS;

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

11 rows in set (0.00 sec)

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

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

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

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.資料分散在多個磁盤上，加大查詢的吞吐量。

安裝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就會報錯因為沒有地方存放這個記錄。可以做一下修改存放大記錄：

PARTITION

p3 VALUES LESS THAN MAXVALUE

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

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

PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01

PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01

PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01

PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01

PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01

PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01

PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01

PARTITION p9 VALUES LESS THAN (MAXVALUE)

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

1.想要删除老的資料，剛好在p0上，那麼可以直接alter

table employee drop partition p0删除分區，達到删除資料的目的

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

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

有個RANGE分區的變種RANGE

COLUMNS，可以多個列一起決定一個分區。

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

id INT NOT NULL,

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

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

| c1 | c2 |

| 7

| 5 |

| 1

| 9 |

| 2

3 rows in set (0.00 sec)

COLUMNS分區是RANGE和LIST的變種。COLUMNS分區可以可以使用多個列作為分解鍵。2個列都可以用來配置設定分區。另外RANGE COLUMNS和LIST

COLUMNS分區支援費insert定義range或者list項。允許的字段類型如下：

1.所有int類型

2.date和datetime類型

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

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依次對比，來決定放入哪個分區。

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

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

使用hash分區，使用create

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

PARTITION BY HASH(store_id)

PARTITIONS 4;

如果不包含PARTITIONS 那麼預設分區個數是1個。

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

插入了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分區一樣的資料均勻。

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;

子分區建立如圖：

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子句顯示的指定選項，比如：

SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990)

(

SUBPARTITION

s0,

PARTITION p1 VALUES LESS THAN (2000)

s2,

MAXVALUE (

s4,

)

一些注意點：

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

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

SUBPARTITION

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

4.配置設定的子分區名必須在表内是唯一的。

在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來處理。

首先删除分區，删除一個分區可以直接用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，也不能修改分區的表達式或者列。隻能通過語句修改：

PARTITION BY HASH( YEAR(dob) )

PARTITIONS 8;

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

CREATE TABLE clients (

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;

在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 (

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

mysql> SELECT PARTITION_NAME, TABLE_ROWS

-> FROM INFORMATION_SCHEMA.PARTITIONS

-> WHERE TABLE_NAME = 'e';

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

| PARTITION_NAME | TABLE_ROWS |

| 1 |

| 0 |

| 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 * FROM e2;

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

| id | fname | lname |

| 16 | Frank | White |

1 row in set (0.00 sec)

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進行處理。

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

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

partitions: p0,p1

key_len:

Extra: Using where

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

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

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

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

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

當使用查詢語句：

SELECT fname, lname, region_code, dob

FROM t1

WHERE region_code > 125 AND region_code < 130;

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

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

1.<code>partition_column</code> = <code>constant</code>

2.<code>partition_column</code> IN (<code>constant1</code>, <code>constant2</code>,

...,<code>constantN</code>)

首先評估給定的值，決定哪個分區包含了這個值，然後掃描這個分區。很多情況下等号可以被一下符号代替，<, >, <=, >=,

和 <>。一些語句使用了between

and也可以使用分區裁剪。

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

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

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

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

裁剪也可以在分區表達式為使用了year或者to_days

函數的 date或者datetime類型。假設有個表t2如下：

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

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

3.那麼掃描2個分區範圍内的所有分區。

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

比如語句:

<code>SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3</code>,

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

對于表分區是hash或者 linear key，分區裁剪隻有等号才能使用：

那麼以下語句可以裁剪：

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

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

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

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

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

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

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

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

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不被分區表支援。

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

MERGE存儲引擎,使用者定義分區表和MERGE存儲引擎不相容。

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

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

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

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

隻有一下函數可以被分區表支援：

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_dayofmonth"><code>DAYOFMONTH()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_dayofweek"><code>DAYOFWEEK()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_dayofyear"><code>DAYOFYEAR()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff"><code>DATEDIFF()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_microsecond"><code>MICROSECOND()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_minute"><code>MINUTE()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_month"><code>MONTH()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_quarter"><code>QUARTER()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_second"><code>SECOND()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_to-seconds"><code>TO_SECONDS()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_weekday"><code>WEEKDAY()</code></a>

<a href="http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_yearweek"><code>YEARWEEK()</code></a>

對于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語句不支援鎖裁剪

[MySQL Reference Manual] 20 分區 20 分區

繼續閱讀

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

windows不能在本地計算機上運作oracleDbConsoleorcl

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Windows下VS開發環境環境安裝工程項目設定關于Debug和Release的提示

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Windows下配置Apache的SSL服務

Mac｜Windows系統本地照片自動上傳到伺服器

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離