一、分区类型
分区类型有:
- RANGE
- LIST
- HASH
- KEY
- LINEARY HASH
- LINEAR KEY
- RANGE COLUMN
- LIST COLUMN
二、创建分区
创建分区
-- 1.RANGE 分区 不过这个字段有一个要求,就是必须是主键或者是联合主键中的某个字段。
CREATE TABLE `range_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb
PARTITION BY RANGE(`id`)(
PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
-- 2.LIST 分区
-- LIST 分区和 RANGE 分区类似,区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择,而非连续的
CREATE TABLE `list_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb
PARTITION BY list(`gender`)(
PARTITION man VALUES IN (1),
PARTITION woman VALUES IN (0,3)
);
-- 3.HASH 分区
-- HASH 分区的目的是将数据均匀地分布到预先定义的各个分区中,保证各分区的数据量大致都是一样的。
-- 另外,HASH 分区不能删除分区,所以不能使用 DROP PARTITION 操作进行分区删除操作
CREATE TABLE `hash_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY HASH(`id`) PARTITIONS 4;
-- 4.KEY 分区
-- KEY 分区和 HASH 分区相似,但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区,而 HASH 分区只支持数字分区。
-- KEY 分区不允许使用用户自定义的表达式进行分区,KEY 分区使用系统提供的 HASH 函数进行分区。
-- 当表中存在主键或者唯一索引时,如果创建 KEY 分区时没有指定字段系统默认会首选主键列作为分区字段,如果不存在主键列会选择非空唯一索引列作为分区字段。
CREATE TABLE `key_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY KEY(`id`) PARTITIONS 4;
-- 5.RANGE COLUMNS 分区
-- 只有 RANGE COLUMN 和 LIST COLUMN 这两种分区;支持整形、日期、字符串;这种分区方式和 RANGE、LIST 的分区方式非常的相似。
CREATE TABLE `range_columns_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY RANGE COLUMNS(`createDate`) (
PARTITION p0 VALUES LESS THAN ('1990-01-01'),
PARTITION p1 VALUES LESS THAN ('2000-01-01'),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
-- 6.LIST COLUMNS 分区
CREATE TABLE `list_columns_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY LIST COLUMNS(`createDate`) (
PARTITION p0 VALUES IN ('1990-01-01'),
PARTITION p1 VALUES IN ('2000-01-01'),
PARTITION p2 VALUES IN ('2010-01-01')
);
-- 7.LINEARY HASH 分区
CREATE TABLE `lineary_hash_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY LINEAR HASH(`id`) PARTITIONS 4;
-- 8.LINEAR KEY 分区
CREATE TABLE `lineary_key_partition`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY LINEAR KEY(`id`) PARTITIONS 4;
创建复合分区
-- 复合分区:在MySQL 5.6版本中,只支持 RANGE 和 LIST 的子分区,且子分区的类型只能为 HASH 和 KEY。
CREATE TABLE `compound_range_hash_partition` (
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY RANGE( YEAR(createDate) )
SUBPARTITION BY HASH( TO_DAYS(createDate) ) (
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
)
);
创建表及分区,指定数据文件位置
-- 版本 mysql 5.7
-- 指定的数据目录存在,且目录所有者是 mysql,可创建成功
-- 数据文件在
-- /var/lib/mysql_data_0/test/range_partition_with_data#P#p0.ibd
-- /var/lib/mysql_data_1/test/range_partition_with_data#P#p1.ibd
-- /var/lib/mysql_data_2/test/range_partition_with_data#P#p2.ibd
-- 表结构文件在 /var/lib/mysql/test/range_partition_with_data.frm
CREATE TABLE `range_partition_with_data`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY RANGE(`id`)(
PARTITION p0 VALUES LESS THAN(100) DATA DIRECTORY = '/var/lib/mysql_data_0' MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p0',
PARTITION p1 VALUES LESS THAN(200) DATA DIRECTORY = '/var/lib/mysql_data_1' MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p1',
PARTITION p2 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/var/lib/mysql_data_2' MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p2'
);
-- 版本 mysql 5.7
-- 指定的数据目录存在,且目录所有者是mysql,可创建成功
-- 文件大小为 64K
-- 表结构文件在 /var/lib/mysql/test/range_partition_with_ts.frm
CREATE TABLESPACE ts0 ADD DATAFILE '/var/lib/mysql_ts/ts0.ibd';
CREATE TABLESPACE ts1 ADD DATAFILE '/var/lib/mysql_ts/ts1.ibd';
CREATE TABLESPACE ts2 ADD DATAFILE '/var/lib/mysql_ts/ts2.ibd';
CREATE TABLE `range_partition_with_ts`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=innodb PARTITION BY RANGE(`id`)(
PARTITION p0 VALUES LESS THAN(100) TABLESPACE ts0 MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p0',
PARTITION p1 VALUES LESS THAN(200) TABLESPACE ts1 MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p1',
PARTITION p2 VALUES LESS THAN MAXVALUE TABLESPACE ts2 MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p2'
);
-- myisam 可以设置成功 INDEX DIRECTORY 和 DATA DIRECTORY,但索引和数据文件还是在默认目录 /var/lib/mysql/test/ 下
CREATE TABLE `range_partition_with_data_myisam`(
`id` int,
`name` varchar(255),
`gender` int,
`createDate` date,
PRIMARY KEY(`id`, `gender`, `createDate`)
)ENGINE=myisam PARTITION BY RANGE(`id`)(
PARTITION p0 VALUES LESS THAN(100) INDEX DIRECTORY '/var/lib/mysql_index_0' DATA DIRECTORY = '/var/lib/mysql_data_0' MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p0',
PARTITION p1 VALUES LESS THAN(200) INDEX DIRECTORY '/var/lib/mysql_index_1' DATA DIRECTORY = '/var/lib/mysql_data_1' MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p1',
PARTITION p2 VALUES LESS THAN MAXVALUE INDEX DIRECTORY '/var/lib/mysql_index_2' DATA DIRECTORY = '/var/lib/mysql_data_2' MIN_ROWS = 2 MAX_ROWS = 3 COMMENT 'this is p2'
);