Mysql的分區(qū)詳解

分區(qū)：

分區(qū)的功能不是在存儲(chǔ)引擎層實(shí)現(xiàn)的。因此不只是InnoDB才支持分區(qū)。MyISAM上炎、NDB都支持分區(qū)操作。

分區(qū)的過(guò)程是將一個(gè)表或者索引分解為多個(gè)更小雏搂、更可管理的部分藕施。從邏輯上將，只有一個(gè)表或者索引凸郑，但是在物理上這個(gè)表或索引可能由數(shù)十個(gè)物理分區(qū)組成裳食。

每個(gè)分區(qū)都是獨(dú)立的對(duì)象，可以獨(dú)自處理芙沥，也可以作為一個(gè)更大對(duì)象的一部分進(jìn)行處理诲祸。

MySQL只支持水平分區(qū)，不支持垂直分區(qū)而昨。

水平分區(qū)：將同一表中不同行的記錄分配到不同的物理文件中救氯。
垂直分區(qū)：將同一表中不同列的記錄分配到不同的物理文件中。

MySQL數(shù)據(jù)庫(kù)的分區(qū)是局部分區(qū)索引歌憨。一個(gè)分區(qū)中既存放了數(shù)據(jù)又存放了索引着憨。而全局分區(qū)索引指的是數(shù)據(jù)存放在各個(gè)分區(qū)中，但是所有數(shù)據(jù)的索引放在一個(gè)對(duì)象中躺孝。MySQL暫時(shí)還不支持全局分區(qū)索引享扔。

操作

查看當(dāng)前數(shù)據(jù)庫(kù)是否啟用了分區(qū)功能：

> show plugins\G   partition狀態(tài)是ACTIVE表示可以支持分區(qū)。

查看目前MySQL上有哪些分區(qū)表：

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,
PARTITION_METHOD,CREATE_TIME from `PARTITIONS` 
where PARTITION_NAME is not null ;

MySQL數(shù)據(jù)庫(kù)支持以下幾種類型的分區(qū)：

RANGE分區(qū)
LIST分區(qū)
HASH分區(qū)
KEY分區(qū)

如下就是創(chuàng)建分區(qū)的表的方式：

 create table t4 (

col1 int null,

col2 date null,

col3 int null,

col4 int null,

key (col3)

) engine=InnoDB

partition BY HASH (col3)

partitions 4;        -- 劃分成4個(gè)分區(qū)



-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p0.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p1.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p2.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p3.ibd

分區(qū)類型：

1 RANGE分區(qū)：

 create table t1 (id int)

partition by range(id)(

partition p0 values less than (10),

partition p1 values less than (20));


當(dāng)數(shù)據(jù)小于10的時(shí)候植袍，插入p0分區(qū)惧眠。大于等于10小于20時(shí)候，插入p1分區(qū)于个。


> INSERT INTO t1 SELECT 12;

> INSERT INTO t1 SELECT 2;



表物理文件變成了下面這種：

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:14 t1#P#p0.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:17 t1#P#p1.ibd



從表面上氛魁，看不出來(lái)到底插入到什么分區(qū)中了，可以用下面的命令查看：

> SELECT * from information_schema.PARTITIONS where table_schema=database() and table_name='t1'\G



***************************[ 1. row ]***************************

TABLE_CATALOG                 | def

TABLE_SCHEMA                  | hellodb

TABLE_NAME                    | t1

PARTITION_NAME                | p0        # 這里能看到是插入到p0分區(qū)的

SUBPARTITION_NAME             | None

PARTITION_ORDINAL_POSITION    | 1

SUBPARTITION_ORDINAL_POSITION | None

PARTITION_METHOD              | RANGE       # 這里看得出是range分區(qū)類型

SUBPARTITION_METHOD           | None

PARTITION_EXPRESSION          | id

SUBPARTITION_EXPRESSION       | None

PARTITION_DESCRIPTION         | 10

TABLE_ROWS                    | 1           # 這個(gè)反映了該分區(qū)(這里是p0)記錄的行數(shù)量厅篓。

AVG_ROW_LENGTH                | 16384

DATA_LENGTH                   | 16384

MAX_DATA_LENGTH               | None

INDEX_LENGTH                  | 0

DATA_FREE                     | 0

CREATE_TIME                   | None

UPDATE_TIME                   | None

CHECK_TIME                    | None

CHECKSUM                      | None

PARTITION_COMMENT             | 

NODEGROUP                     | default

TABLESPACE_NAME               | None

***************************[ 2. row ]***************************

TABLE_CATALOG                 | def

TABLE_SCHEMA                  | hellodb

TABLE_NAME                    | t1

PARTITION_NAME                | p1        # 這里能看到是插入到p1分區(qū)的

SUBPARTITION_NAME             | None

PARTITION_ORDINAL_POSITION    | 2

SUBPARTITION_ORDINAL_POSITION | None

PARTITION_METHOD              | RANGE

SUBPARTITION_METHOD           | None

PARTITION_EXPRESSION          | id

SUBPARTITION_EXPRESSION       | None

PARTITION_DESCRIPTION         | 20

TABLE_ROWS                    | 1

AVG_ROW_LENGTH                | 16384

DATA_LENGTH                   | 16384

MAX_DATA_LENGTH               | None

INDEX_LENGTH                  | 0

DATA_FREE                     | 0

CREATE_TIME                   | None

UPDATE_TIME                   | None

CHECK_TIME                    | None

CHECKSUM                      | None

PARTITION_COMMENT             | 

NODEGROUP                     | default

TABLESPACE_NAME               | None



> INSERT INTO t1 SELECT 32;  # 這個(gè)插入會(huì)報(bào)錯(cuò)秀存，因?yàn)槲覀兩厦娑x的分區(qū)，并不包含這個(gè)區(qū)間羽氮。

對(duì)此或链，要允許插入大數(shù)的話，可以修改下表：

> ALTER TABLE t1 add partition( partition p2 values less than (30)); 或者 ALTER TABLE t1 add partition( partition p2 values less than maxvalue );

這下我們插入大數(shù)值的話档押，也不會(huì)報(bào)錯(cuò)了祈纯。

RANGE分區(qū)主要用于日期列的分區(qū)，例如對(duì)于銷售類的表腕窥，可以根據(jù)年來(lái)分區(qū)存放銷售記錄筛婉。如下面的分區(qū)表sales:

> CREATE TABLE sales (

money INT UNSIGNED NOT NULL,

`date` DATETIME

) ENGINE=INNODB

PARTITION BY RANGE (YEAR(DATE)) (

PARTITION p2008 VALUES LESS THAN (2009),

PARTITION p2009 VALUES LESS THAN (2010),

PARTITION p2010 VALUES LESS THAN (2011)

);



> INSERT INTO sales SELECT 2399,'2008-04-20';

> INSERT INTO sales SELECT 6569,'2009-01-25';

> INSERT INTO sales SELECT 2399,'2010-12-20';

這樣的話，不同年份的數(shù)據(jù)就插入到不同的分區(qū)中入蛆，便于對(duì)sales這張表進(jìn)行管理匆浙。

如果要?jiǎng)h除2008年的數(shù)據(jù)，不需要執(zhí)行delete from sales where date>='2008-01-01' and date<='2008-12-31'; 只要?jiǎng)h除2008年數(shù)據(jù)所在的分區(qū)即可：

> alter table sales drop partition p2008;

分區(qū)的另一個(gè)好處是：

加快某些查詢首尼，例如我們只要查詢2009年整年的銷售額，如下即可：

> explain partitions select * from sales where date >='2009-01-01' and date <='2009-12-31'\G


***************************[ 1. row ]***************************

id            | 1

select_type   | SIMPLE

table         | sales

partitions    | p2009    # 只去p2009這個(gè)分區(qū)去搜索

type          | ALL

possible_keys | None

key           | None

key_len       | None

ref           | None

rows          | 2

Extra         | Using where

最常用的就是range分區(qū)软能。

但是注意：如果分區(qū)鍵是timestamp類型的迎捺，則必須用UNIX_TIMESTAMP轉(zhuǎn)換下。如下例子：

ALTER TABLE `order_his_tmp` drop primary key, add primary key(id,order_time);

去掉原先的主鍵查排，加一個(gè)帶分區(qū)ID的主鍵凳枝。


ALTER TABLE `order_his_tmp` PARTITION BY RANGE (UNIX_TIMESTAMP (order_time)) ( 

 PARTITION  p201508  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-09-01')) ,

 PARTITION  p201509  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-10-01')) ,

 PARTITION  P201510  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-11-01')) ,

 PARTITION  P201511  VALUES LESS THAN  (UNIX_TIMESTAMP('2015-12-01')) ,

 PARTITION  P201512  VALUES LESS THAN  (UNIX_TIMESTAMP('2016-01-01')) ,

 PARTITION  P201601  VALUES LESS THAN  (UNIX_TIMESTAMP('2016-02-01')) ,

 PARTITION  P201602  VALUES LESS THAN  (UNIX_TIMESTAMP('2016-03-01')) );

對(duì)于分區(qū)鍵是DATETIME的，要用TO_DAYS()函數(shù)操作,如下例子：

> CREATE TABLE sales(

money int unsigned not null,

date datetime)

partition by range (TO_DAYS(date)) (

partition p201001 values less than (TO_DAYS('2010-02-01')), 

partition p201002 values less than (TO_DAYS('2010-03-01')), 

partition p201003 values less than (TO_DAYS('2010-04-01'))

);

2 LIST分區(qū)【不多見】：

和range分區(qū)類似跋核，只是分區(qū)列的值是散列的岖瑰，而非連續(xù)的。

> CREATE TABLE t (

a INT,

b INT) ENGINE INNODB

PARTITION BY LIST(b) (

PARTITION p0 VALUES IN (1,3,5,7,9),

PARTITION p1 VALUES IN (2,4,6,8)

);

注意：list分區(qū)中使用的是VALUES IN 這種格式砂代。

> insert into t select 3,2;

> insert into t select 2,12;  執(zhí)行這行插入語(yǔ)句會(huì)報(bào)錯(cuò)蹋订，因?yàn)榘凑誏IST(b)劃分的話，12不在上述的LIST里面刻伊。

> insert into t select 3,4;  執(zhí)行這行插入語(yǔ)句會(huì)報(bào)錯(cuò)露戒，因?yàn)榘凑誏IST(b)劃分的話，4不在上述的LIST里面捶箱。

> insert into t select 3,5;



> SELECT table_name,partition_name,table_rows from information_schema.Partitions where table_name='t' and table_schema=DATABASE()\G

結(jié)果如下：

***************************[ 1. row ]***************************

table_name     | t

partition_name | p0

table_rows     | 1

***************************[ 2. row ]***************************

table_name     | t

partition_name | p1

table_rows     | 2   表示p1分區(qū)有2行數(shù)據(jù)

注意：

InnoDB和MyISAM在遇到一次性插入多條數(shù)據(jù)中出現(xiàn)分區(qū)未定義錯(cuò)誤的時(shí)候處理方式是不同的智什。

InnoDB會(huì)把整個(gè)SQL語(yǔ)句當(dāng)做一個(gè)事務(wù)，只要有錯(cuò)誤丁屎，就完全不執(zhí)行荠锭。而MyISAM則會(huì)將錯(cuò)誤之前的sql都執(zhí)行成功。

3 HASH分區(qū)：

HASH分區(qū)的目的是將數(shù)據(jù)均勻地分布到預(yù)先定義的各個(gè)分區(qū)中晨川，保證各分區(qū)的數(shù)據(jù)數(shù)量大致都是一樣的节沦。

在RANGE和LIST分區(qū)中键思，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中。在HASH分區(qū)中甫贯，MySQL自動(dòng)完成這些工作，用于所要做的只是基于將要進(jìn)行哈希分區(qū)的列值指定一個(gè)列值或表達(dá)式看蚜，以及指定備份去的表將要被分割成的分區(qū)數(shù)量叫搁。

要使用HASH分區(qū)來(lái)分割一個(gè)表渴逻，要在CREATE TABLE語(yǔ)句上添加一個(gè) PARTITION BY HASH(expr) 子句惨奕，其中expr是一個(gè)返回一個(gè)整數(shù)的表達(dá)式。它可以僅僅是字段類型為MySQL整型的列名卧波。

此外庇茫，用戶可以自定義分區(qū)的數(shù)量旦签，只要加上參數(shù)PARTITIONS xxx即可宁炫。如下：

> create table t_hash (

a int,

b datetime

) engine innodb

PARTITION BY HASH (YEAR(b))

PARTITIONS 4;

> INSERT INTO t_hash VALUES (6,'2013-03-11 12:21:22');

上面這條插入的話遥昧，實(shí)際上要執(zhí)行MOD(2013,4)取余數(shù)朵纷，得出這行數(shù)據(jù)存放在哪個(gè)分區(qū)中鞋仍。


> SELECT table_name,partition_name,table_rows FROM information_schema.`PARTITIONS` WHERE table_schema=DATABASE() AND table_name='t_hash' ;

結(jié)果如下：

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

| table_name | partition_name | table_rows |

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

| t_hash     | p0             |          0 |  

| t_hash     | p1             |          1 |  # 可以看到p1插入了4條數(shù)據(jù)威创，因?yàn)?013年除以4，余數(shù)是1溃斋。因此2013年都數(shù)據(jù)都落在p1分區(qū)

| t_hash     | p2             |          0 |

| t_hash     | p3             |          0 |

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

4 LINEAR HASH分區(qū)：(線性hash)

mysql還支持這種復(fù)雜的分區(qū)算法梗劫。語(yǔ)法和HASH分區(qū)類似梳侨，只是將關(guān)鍵字HASH改成了LINEAR HASH。

如下：

> CREATE TABLE t_l_hash (

a INT,

b DATETIME

) ENGINE INNODB

PARTITION BY LINEAR HASH(YEAR(b))

PARTITIONS 4;

取大于分區(qū)數(shù)量4的下一個(gè)2的冪值V丙躏，V=POWER(2彼哼，CEILING(LOG(2敢朱，num)))=4

所在分區(qū)N=YEAR('2010-04-01')&(V-1)=2.

LINEAR HASH分區(qū)的

優(yōu)點(diǎn)：

增加拴签、刪除蚓哩、合并和拆分分區(qū)將變得更加快捷，有利于處理含有大量數(shù)據(jù)的表曹阔。

缺點(diǎn)：

與使用HASH分區(qū)得到的數(shù)據(jù)分布相比赃份，各個(gè)分區(qū)間數(shù)據(jù)的分布可能不大均衡纠永。


> INSERT INTO t_l_hash VALUE (2,'2014-04-23 12:23:33');



> SELECT table_name,partition_name,table_rows

FROM information_schema.`PARTITIONS`

WHERE table_schema=DATABASE() AND table_name='t_l_hash'\G


結(jié)果如下：

***************************[ 1. row ]***************************

table_name     | t_l_hash

partition_name | p0

table_rows     | 1

***************************[ 2. row ]***************************

table_name     | t_l_hash

partition_name | p1

table_rows     | 0

***************************[ 3. row ]***************************

table_name     | t_l_hash

partition_name | p2

table_rows     | 1

***************************[ 4. row ]***************************

table_name     | t_l_hash

partition_name | p3

table_rows     | 0

5 KEY分區(qū)：

和HASH分區(qū)類似尝江，不同之處在于HASH分區(qū)使用用戶定義的函數(shù)進(jìn)行分區(qū)茂装，KEY分區(qū)使用MySQL數(shù)據(jù)庫(kù)提供的函數(shù)進(jìn)行分區(qū)。

對(duì)于NDB Cluster引擎易遣，MySQL數(shù)據(jù)庫(kù)使用MD5函數(shù)來(lái)分區(qū)豆茫；對(duì)于其他引擎，使用MySQL內(nèi)部的哈希函數(shù)來(lái)分區(qū)火脉。

> CREATE TABLE t_key (

a INT,

b DATETIME

) ENGINE INNODB

PARTITION BY KEY(b)

PARTITIONS 4 ;

6 COLUMNS分區(qū)【很常用】：

前面的幾種分區(qū)都是有條件限制的倦挂。條件是：必須是整型方援，如果不是整型犯戏，那么也必須是可以通過(guò)函數(shù)轉(zhuǎn)換為整型的，如YEAR()胚鸯、TO_DAYS()姜钳、MONTH()等函數(shù)辙浑。

MySQL5.5版本開始支持的COLUMNS分區(qū)判呕，可視為RANGE分區(qū)和LIST分區(qū)的一種進(jìn)化。

COLUMNS分區(qū)看直接使用非整型的數(shù)據(jù)進(jìn)行分區(qū)犁嗅，分區(qū)根據(jù)類型直接比較而得功蜓，不需要轉(zhuǎn)換為整型宠蚂。

此外著隆，COLUMNS分區(qū)可以對(duì)多個(gè)列的值進(jìn)行分區(qū)。

COLUMNS分區(qū)支持以下的數(shù)據(jù)類型：

1 所有的整型類型

支持：INT抵代、SMALLINT荤牍、TINYINT康吵、BIGINT。不支持：FLOAT和DECIMAL

2 日期類型【常用】

支持 DATE旱函、DATETIME

3 字符串類型

支持 CAHR棒妨、VARCHAR、BINARY纷纫、VARBINARY。不支持BLOB和TEXT

> CREATE TABLE t_columns_range (

a INT,

b DATETIME

)ENGINE INNODB

PARTITION BY  RANGE COLUMNS (B) (

PARTITION p0 VALUES LESS THAN ('2009-01-01'),

PARTITION p1 VALUES LESS THAN ('2010-01-01')

);

注意和range分區(qū)的SQL語(yǔ)句差別Ｅ汶纭涛酗！(分區(qū)范圍里不用函數(shù)處理列了)

對(duì)于現(xiàn)有的表改成分區(qū)表:

ALTER TABLE `tb_detail` drop primary key ,add primary key (id, bill_date);



ALTER TABLE `tb_detail` PARTITION BY RANGE COLUMNS (bill_date)   (   

PARTITION  p201509  VALUES LESS THAN  ('2015-10-01') ,   

PARTITION  P201510  VALUES LESS THAN  ('2015-11-01') ,

PARTITION  P201511  VALUES LESS THAN  ('2015-12-01') ,

PARTITION  P201512  VALUES LESS THAN  ('2016-01-01') ,

PARTITION  P201601  VALUES LESS THAN  ('2016-02-01') ,

PARTITION  P201602  VALUES LESS THAN  ('2016-03-01') ,

PARTITION  P201603  VALUES LESS THAN  ('2016-04-01') ,

PARTITION  P201604  VALUES LESS THAN  ('2016-05-01') );

對(duì)于RANGE COLUMNS分區(qū)，可以使用多個(gè)列進(jìn)行分區(qū)偷厦，如：

> CREATE TABLE rcx (

a INT,

b INT,

c CHAR(3),

d INT

)ENGINE INNODB

PARTITION BY  RANGE COLUMNS (a,b,c) (

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

PARTITION p1 VALUES LESS THAN (10,20,'mmm'),

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

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

);

MySQL5.5開始支持COLUMNS分區(qū)。對(duì)于之前的RANGE和LIST分區(qū)燕刻。用戶可以用RANGE COLUMNS和LIST COLUMNS分區(qū)進(jìn)行很好的代替只泼。

手工添加分區(qū)：

CREATE TABLE `t10` (

 `a` int(11) NOT NULL AUTO_INCREMENT,

 `b` int(11) DEFAULT NULL,

 PRIMARY KEY (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8

PARTITION BY RANGE  COLUMNS(a)

(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (40) ENGINE = InnoDB,

PARTITION p4 VALUES LESS THAN (50) ENGINE = InnoDB,

PARTITION p5 VALUES LESS THAN (60) ENGINE = InnoDB,

PARTITION p6 VALUES LESS THAN (70) ENGINE = InnoDB,

PARTITION p7 VALUES LESS THAN (80) ENGINE = InnoDB,

PARTITION p8 VALUES LESS THAN (90) ENGINE = InnoDB,

PARTITION p9 VALUES LESS THAN (100) ENGINE = InnoDB );  ---> 注意這里最后一個(gè)分區(qū)范圍沒(méi)有像上面其他案例那樣寫死

后期可以使用 ALTER TABLE t10 ADD PARTITION (PARTITION p10 VALUES LESS THAN (110)); 這樣就可以加一個(gè)分區(qū)了。

子分區(qū)：

子分區(qū)(subpartitioning)是在分區(qū)的基礎(chǔ)上在進(jìn)行分區(qū)卵洗，有時(shí)也稱這種分區(qū)為復(fù)合分區(qū)(composite partitioning)本橙。

MySQL數(shù)據(jù)庫(kù)允許在RANGE和LIST分區(qū)上再進(jìn)行HASH或KEY的子分區(qū)亏狰。如：


> CREATE TABLE ts (

a INT,

b DATE

) ENGINE=INNODB

PARTITION BY RANGE(YEAR(b))

SUBPARTITION BY HASH(TO_DAYS(b))

SUBPARTITIONS 2 (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN MAXVALUE);



在物理文件上表示形式如下：

-rw-rw---- 1 mariadb mariadb   711 2016-08-07 19:28 ts.frm

-rw-rw---- 1 mariadb mariadb   108 2016-08-07 19:28 ts.par

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p0#SP#p0sp0.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p0#SP#p0sp1.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p1#SP#p1sp0.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p1#SP#p1sp1.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p2#SP#p2sp0.ibd

-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 19:28 ts#P#p2#SP#p2sp1.ibd

先進(jìn)行range分區(qū)(p0\p1\p2)硫戈，再進(jìn)行hash分區(qū)(sp0\sp1)。(合計(jì)共3*2=6個(gè)分區(qū))，上面物理文件上面也能看出來(lái)是6個(gè)分區(qū)探熔。

子分區(qū)的建立需要注意下面問(wèn)題：

1苛蒲、每個(gè)子分區(qū)的數(shù)量必須相同寄月。

2忱辅、要在一個(gè)分區(qū)表的任何分區(qū)上使用SUBPARTITION來(lái)明確定義任何子分區(qū)，就必須定義所有的子分區(qū)弄匕。

3、每個(gè)SUBPARTITION子句必須包括子分區(qū)的一個(gè)名字。

4、子分區(qū)的名字必須是唯一的赶诊。

子分區(qū)可以用于特別大的表夺英，在多個(gè)磁盤間分別分配數(shù)據(jù)和索引。

如下為6個(gè)磁盤的分區(qū)實(shí)例(InnoDB引擎)：

> CREATE TABLE ts (

a INT,

b DATE)ENGINE INNODB

PARTITION BY RANGE(YEAR(b))

SUBPARTITION BY HASH(TO_DAYS(b)) (

PARTITION p0 VALUES LESS THAN (2000) (

SUBPARTITION s0

DATA DIRECTORY = '/disk0/data'

INDEX DIRECTORY = '/disk0/idx',

SUBPARTITION s1

DATA DIRECTORY = '/disk1/data'

INDEX DIRECTORY = '/disk1/idx'

),

PARTITION p1 VALUES LESS THAN (2010) (

SUBPARTITION s2

DATA DIRECTORY = '/disk2/data'

INDEX DIRECTORY = '/disk2/idx',

SUBPARTITION s3

DATA DIRECTORY = '/disk3/data'

INDEX DIRECTORY = '/disk3/idx'

),

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s4

DATA DIRECTORY = '/disk4/data'

INDEX DIRECTORY = '/disk4/idx',

SUBPARTITION s5

DATA DIRECTORY = '/disk5/data'

INDEX DIRECTORY = '/disk5/idx'

)

);

分區(qū)中的NULL值：

MySQL允許對(duì)NULL值做分區(qū)知给。但是處理的方法與其他數(shù)據(jù)庫(kù)完全不同花墩。

MySQL數(shù)據(jù)庫(kù)的分區(qū)總是視NULL值小于任何的一個(gè)非NULL值武氓，這和MySQL數(shù)據(jù)庫(kù)中處理NULL值的ORDER BY操作是一樣的。因此對(duì)于不同的分區(qū)類型垒拢，MySQL數(shù)據(jù)庫(kù)對(duì)于NULL值的處理也是各不相同。


1症革、RANGE分區(qū)中使用NULL值：

> CREATE table t_range(

     a int,

     b int)

     partition by range(b) (

     partition p0 VALUES less than (10),

     partition p1 VALUES less than (20),

     partition p2 values less than maxvalue);



   > insert into t_range select 1,1;

   > insert into t_range select 2,null;



   > SELECT table_name,partition_name,table_rows

FROM information_schema.`PARTITIONS`

WHERE table_schema=DATABASE() AND table_name='t_range';


結(jié)果如下：

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

| table_name | partition_name | table_rows |

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

| t_range    | p0             |          2 |

| t_range    | p1             |          0 |

| t_range    | p2             |          0 |

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

可以看到插入的數(shù)據(jù)都放到了p0分區(qū)侧巨。

也就是說(shuō)在RANGE分區(qū)下禁添，NULL值會(huì)放入最左邊的分區(qū)中撮胧。

另外，如果刪除了p0分區(qū)老翘，刪除的將是小于10的記錄和NULL值的記錄芹啥，這點(diǎn)非常重要锻离。

> alter table t_range drop partition p0;

> SELECT * from t_range;  可以看到下圖的表已經(jīng)為空值了

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

| a   | b   |

|-----+-----|

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

2、LIST分區(qū)下使用NULL值墓怀，必須顯式的指出哪個(gè)分區(qū)中放入NULL值汽纠，否則會(huì)報(bào)錯(cuò)灯蝴。

如下寫法才能允許插入NULL值斗搞。

> CREATE table t_list (

a int,

b int)

partition by list(b) (

partition p0 values in (1,3,5,7,9,NULL),    # 注意必須顯式的指出NULL插入到那個(gè)分區(qū)才行

partition p1 VALUES in (2,4,6,8,10));



> INSERT INTO t_list SELECT 1,4;

> INSERT INTO t_list SELECT 3,null;



> SELECT table_name,partition_name,table_rows

IFROM information_schema.`PARTITIONS`

WHERE table_schema=DATABASE() AND table_name='t_list'\G



結(jié)果如下：

***************************[ 1. row ]***************************

table_name     | t_list

partition_name | p0

table_rows     | 1

***************************[ 2. row ]***************************

table_name     | t_list

partition_name | p1

table_rows     | 1

3巩趁、HASH和KEY分區(qū)對(duì)于NULL的處理方式和RANGE分區(qū)鸣皂、LIST分區(qū)不一樣梳虽。

任何分區(qū)函數(shù)都會(huì)將含有NULL值的記錄返回為0垒手〖酰【返回0的說(shuō)明是存放在第一個(gè)分區(qū)中】


> create table t_hash (

a int,

b int ) engine=innodb

partition by hash(b)

partitions 4;



> INSERT INTO t_hash SELECT 3,null;

> INSERT INTO t_hash SELECT 3,6778;



 > SELECT table_name,partition_name,table_rows

FROM information_schema.`PARTITIONS`

WHERE table_schema=DATABASE() AND table_name='t_hash'\G

結(jié)果如下：

***************************[ 1. row ]***************************

table_name     | t_hash

partition_name | p0

table_rows     | 1

***************************[ 2. row ]***************************

table_name     | t_hash

partition_name | p1

table_rows     | 0

***************************[ 3. row ]***************************

table_name     | t_hash

partition_name | p2

table_rows     | 1

***************************[ 4. row ]***************************

table_name     | t_hash

partition_name | p3

table_rows     | 0

在表和分區(qū)之間交換數(shù)據(jù)：

mysql5.6開始支持 ALTER TABLE ... EXCHANGE PARTITION 語(yǔ)法按傅。該語(yǔ)句允許分區(qū)或子分區(qū)的數(shù)據(jù)與另一個(gè)非分區(qū)的表中的數(shù)據(jù)進(jìn)行交換梆暮。

如果非分區(qū)表中的數(shù)據(jù)為空服协，那么相當(dāng)于分區(qū)中的數(shù)據(jù)移動(dòng)到非分區(qū)表中。

若分區(qū)表中的數(shù)據(jù)為空啦粹，則相當(dāng)于將外部表中的數(shù)據(jù)導(dǎo)入到分區(qū)中偿荷。

要使用ALTER TABLE ... EXCHANGE語(yǔ)句，必須滿足下面的條件：

1 要交換的表需要和分區(qū)表有相同的表結(jié)構(gòu)卖陵，但是不能有分區(qū)遭顶。

2 在非分區(qū)表中的數(shù)據(jù)必須在交換的分區(qū)定義內(nèi)。

3 被交換的表中不能含有外鍵泪蔫，或者其他的表含有對(duì)該表的外鍵引用棒旗。

4 用戶除了需要alter、insert撩荣、create權(quán)限外铣揉，還需要drop的權(quán)限。

此外餐曹，有2個(gè)小的細(xì)節(jié)要注意：

1逛拱、使用該語(yǔ)句時(shí)，不會(huì)觸發(fā)交換表和被交換表上的觸發(fā)器台猴。

2朽合、AUTO_INCREMENT列將被重置。

例子：

創(chuàng)建一個(gè)含有RANGE分區(qū)的表e饱狂，并填充數(shù)據(jù)：

> 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

(33,'zhang','san'),

(156,'jim','ling'),

(1114,'wang','wu'),

(1454,'li','si');

然后創(chuàng)建交換表e2曹步。結(jié)構(gòu)和表e一樣，但是表e2中不含有分區(qū)休讳。

> CREATE TABLE e2 (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30)

);

或者create table e2 like e; alter table e2 remove partitioning; 即可創(chuàng)建相同表結(jié)構(gòu)的不帶分區(qū)參數(shù)的表e2

觀察分區(qū)表的中的數(shù)據(jù)：

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

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

SELECT partition_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='e2'; 可以看到e2表暫時(shí)是空的

使用alter table命令交換數(shù)據(jù)：

ALTER TABLE e exchange PARTITION p0 WITH TABLE e2; 將表e的分區(qū)p0中的數(shù)據(jù)移動(dòng)到表e2中讲婚。

再次查看表e中分區(qū)的數(shù)據(jù)，可以看到p0中已經(jīng)沒(méi)有數(shù)據(jù)了俊柔。

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

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

> SELECT * from e2;   查看e2表筹麸，可以看到多了一條數(shù)據(jù)活合，就是從e交換而來(lái)的。

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

|   id | fname   | lname   |

|------+---------+---------|

|   33 | zhang   | san     |

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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末物赶，一起剝皮案震驚了整個(gè)濱河市白指，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌块差，老刑警劉巖侵续，帶你破解...
沈念sama閱讀 211,265評(píng)論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場(chǎng)離奇詭異憨闰，居然都是意外死亡状蜗，警方通過(guò)查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,078評(píng)論 2贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門鹉动，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)轧坎，“玉大人，你說(shuō)我怎么就攤上這事泽示「籽” “怎么了？”我有些...
開封第一講書人閱讀 156,852評(píng)論 0贊 347
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵械筛，是天一觀的道長(zhǎng)捎泻。經(jīng)常有香客問(wèn)我，道長(zhǎng)埋哟，這世上最難降的妖魔是什么笆豁？我笑而不...
開封第一講書人閱讀 56,408評(píng)論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮赤赊，結(jié)果婚禮上闯狱，老公的妹妹穿的比我還像新娘。我一直安慰自己抛计，他們只是感情好哄孤，可當(dāng)我...
茶點(diǎn)故事閱讀 65,445評(píng)論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開白布。她就那樣靜靜地躺著吹截，像睡著了一般瘦陈。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上波俄，一...
開封第一講書人閱讀 49,772評(píng)論 1贊 290
城市分裂傳說(shuō)
那天晨逝，我揣著相機(jī)與錄音，去河邊找鬼弟断。笑死咏花，一個(gè)胖子當(dāng)著我的面吹牛趴生，可吹牛的內(nèi)容都是我干的阀趴。我是一名探鬼主播昏翰，決...
沈念sama閱讀 38,921評(píng)論 3贊 406
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼刘急！你這毒婦竟也來(lái)了棚菊？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,688評(píng)論 0贊 266
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤叔汁，失蹤者是張志新（化名）和其女友劉穎统求，沒(méi)想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體据块，經(jīng)...
沈念sama閱讀 44,130評(píng)論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡码邻，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,467評(píng)論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了另假。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片像屋。...
茶點(diǎn)故事閱讀 38,617評(píng)論 1贊 340
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖边篮，靈堂內(nèi)的尸體忽然破棺而出己莺，到底是詐尸還是另有隱情，我是刑警寧澤戈轿，帶...
沈念sama閱讀 34,276評(píng)論 4贊 329
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布凌受，位于F島的核電站，受9級(jí)特大地震影響思杯，放射性物質(zhì)發(fā)生泄漏胜蛉。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,882評(píng)論 3贊 312
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一智蝠、第九天我趴在偏房一處隱蔽的房頂上張望腾么。院中可真熱鬧，春花似錦杈湾、人聲如沸解虱。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,740評(píng)論 0贊 21
一樁弒父案漆撞，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)殴泰。三九已至，卻和暖如春浮驳，著一層夾襖步出監(jiān)牢的瞬間悍汛，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 31,967評(píng)論 1贊 265
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工至会，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留离咐，地道東北人。一個(gè)月前我還...
沈念sama閱讀 46,315評(píng)論 2贊 360
代替公主和親
正文我出身青樓，卻偏偏與公主長(zhǎng)得像宵蛀，于是被迫代替她去往敵國(guó)和親昆著。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,486評(píng)論 2贊 348

Mysql的分區(qū)詳解

分區(qū)：

MySQL只支持水平分區(qū)，不支持垂直分區(qū)而昨。

操作

注意和range分區(qū)的SQL語(yǔ)句差別Ｅ汶纭涛酗！(分區(qū)范圍里不用函數(shù)處理列了)

推薦閱讀更多精彩內(nèi)容