MySQL分区总结_mysql分区表小结2

更改分区类型

如果想更改分区类型，只需调用alter table partition by即可，比oracle要简便；

--将range分区改为key分区

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)

PARTITION BY RANGE( YEAR(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (1995),

PARTITION p2 VALUES LESS THAN (2000),

PARTITION p3 VALUES LESS THAN (2005)

);

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS

Reorganize partition

对于range/list分区表，如想分割/合并相邻分区，除了split/coalesce命令外，还可以使用reorganize partition

Create Table: CREATE TABLE `members` (

`id` int(11) default NULL,

`fname` varchar(25) default NULL,

`lname` varchar(25) default NULL,

`dob` date default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE ( YEAR(dob) ) (

PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,

PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,

PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.

PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM

)

--分割第一个分区

ALTER TABLE members REORGANIZE PARTITION p0 INTO (

PARTITION s0 VALUES LESS THAN (1960),

PARTITION s1 VALUES LESS THAN (1970)

);

--合并前两个分区

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (

PARTITION p0 VALUES LESS THAN (1970)

);

分区修剪

对于key/hash分区，分区修剪只使用于整数列；

range/list分区限制没有那么多，5.5开始to_days()/to_seconds()/year()也支持分区修剪

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

);

--该sql照样能使用分区修剪

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

Tiphash/linear hash分区表，不能使用drop partition命令，可用coalesce partition替代

分区表创建后，不要修改sql_mode，否则有可能导致数据损坏，且必须保持master/slave使用同一种sql_mode

一个表最多可包含1024个分区/子分区(NDB存储引擎除外)，若表没有达到最大分区数限制却仍遭遇Got error ... from storage engine: Out of resources when opening file,，则需检查open_files_limit参数设置；

5.5新增操作命令alter table … truncate partition

分区表不支持外键

临时表和日志表都不可分区

数据库升级时，使用非NDBCLUSTER的key分区表必须重建

子分区只能使用key/hash类型

如果表指定了主键或者unique列，则必须指定这些列或其部分列做分区键

mysql>CREATE TABLE np_pk (

->id INT NOT NULL AUTO_INCREMENT,

->name VARCHAR(50),

->added DATE,

->PRIMARY KEY (id)

->);

Query OK, 0 rows affected (0.08 sec)

mysql>ALTER TABLE np_pk

->PARTITION BY HASH( TO_DAYS(added) )

->PARTITIONS 4;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function