MySQL 创建分区

一、分区类型

分区类型有：

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