MYSQL分区

前言

同事离职，从他手里接了一个表分区的任务，以前只是听过分区，懂得一点，现在接了这个活，就深入研究了一下。

MySQL数据库在5.1版本时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。

分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区。

MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

分区类型

1.RANGE分区

适用于连续不间断的字段，如日期类字段

CREATE TABLE range_partition(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
PRIMARY KEY (id,create_date)
) ENGINE=INNODB COMMENT= 'range分区测试表'
PARTITION BY RANGE(year(create_date))
(
PARTITION p01 VALUES less than (2018) ,
PARTITION p02 VALUES less than (2019) ,
PARTITION p03 VALUES less than (2020) ,
PARTITION p04 VALUES less than (2021) ,
PARTITION p05 VALUES less than (2022) ,
PARTITION pmax VALUES less than MAXVALUE 
);

2.LIST分区

适用于特定值的字段，如类型
要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

CREATE TABLE list_partition(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
list_type TINYINT COMMENT '类型',
PRIMARY KEY (id,list_type)
) ENGINE=INNODB COMMENT= 'list分区测试表'
PARTITION BY LIST(list_type)
(
PARTITION p01 VALUES IN (1,2,3,4) ,
PARTITION p02 VALUES IN (5,6) ,
PARTITION p03 VALUES IN (7)
);

3.HASH分区

适用于自增主键，如id
目的将数据均匀地分布，保证各分区的数据量大致都是一样的。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量，如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

CREATE TABLE hash_partition(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
list_type TINYINT COMMENT '类型',
PRIMARY KEY (id)
) ENGINE=INNODB COMMENT= 'hash分区测试表'
PARTITION BY HASH(id)
PARTITIONS 4;

LINEAR HASH分区
MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字。
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

CREATE TABLE linear_hash_partition(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
list_type TINYINT COMMENT '类型',
PRIMARY KEY (id)
) ENGINE=INNODB COMMENT= 'liner hash分区测试表'
PARTITION BY LINEAR HASH(id)
PARTITIONS 4;

4.KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，支持字符串HASH分区，KEY分区使用MySQL数据库提供的函数进行分区，这些函数基于与PASSWORD()一样的运算法则。
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

CREATE TABLE key_partition(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
list_type TINYINT COMMENT '类型',
PRIMARY KEY (id)
) ENGINE=INNODB COMMENT= 'key分区测试表'
PARTITION BY  KEY(id)
PARTITIONS 4;

CREATE TABLE key_partition2(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
list_type TINYINT COMMENT '类型',
PRIMARY KEY (id)
) ENGINE=INNODB COMMENT= 'key分区测试表2'
PARTITION BY LINEAR KEY(id)
PARTITIONS 4;

5.COLUMNS分区

前面说到的RANGE、LIST、HASH和KEY这四种分区中，分区的条件是：数据必须为整形（interger），如果不是整形，那应该需要通过函数将其转化为整形，如YEAR()，TO_DAYS()，MONTH()等函数。
MySQL5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。
COLUMNS分区支持以下的数据类型：
（1）所有的整形类型，如INT、SMALLINT、TINYINT和BIGINT。而FLOAT和DECIMAL则不予支持。
（2）日期类型，如DATE何DATETIME。其余的日期类型不予支持。
（3）字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。而BLOB和TEXT类型不予支持。

CREATE TABLE columns_partition(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
list_type TINYINT COMMENT '类型',
PRIMARY KEY (id,create_date)
) ENGINE=INNODB COMMENT= 'columns分区测试表'
PARTITION BY RANGE COLUMNS(create_date)
(
PARTITION p01 VALUES less than ('2018-01-01'),
PARTITION p02 VALUES less than ('2019-01-01'),
PARTITION p03 VALUES less than ('2020-01-01'),
PARTITION p04 VALUES less than ('2021-01-01'),
PARTITION p05 VALUES less than ('2022-01-01'),
PARTITION pmax VALUES less than MAXVALUE
);

分区字段限制

（1）MySQL的分区字段，必须包含在主键字段内。可以使用用联合索引。
（1）加了分区后，不能给其他非分区字段加唯一索引。每一个分区表中的公式中的列，必须在primary key/unique key中包括。

[SQL]CREATE TABLE columns_partition3(
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
create_date date  COMMENT '创建时间' ,
list_type TINYINT COMMENT '类型',
PRIMARY KEY (id,create_date),
UNIQUE KEY idx_type (list_type)
) ENGINE=INNODB COMMENT= 'columns分区测试表'
PARTITION BY RANGE COLUMNS(create_date)
(
PARTITION p01 VALUES less than ('2018-01-01'),
PARTITION p02 VALUES less than ('2019-01-01'),
PARTITION p03 VALUES less than ('2020-01-01'),
PARTITION pmax VALUES less than MAXVALUE
);
[Err] 1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

参考链接：MySQL技术内幕 InnoDB存储引擎：分区表