前言
同事离职,从他手里接了一个表分区的任务,以前只是听过分区,懂得一点,现在接了这个活,就深入研究了一下。
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