MySQL分区

一、MySQL分区创建

  MySQL创建方式一共有四种：range、list、hash和key。

1.range（官方文档）

1.1 int

create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ date,
  primary key(`id`,`time_`)
)
partition by range(id)(
		partition p0 values less than (5),
		partition p1 values less than (10),
		partition p2 values less than (15),
		partition p3 values less than (MAXVALUE)
)
1
2
3
4
5
6
7
8
9
10
11
12
13

MAXVALUE:始终大于最大可能整数值的整数值。

1.2 DATE、TIME和DATETIME
  使用一个对DATE、TIME或DATETIME列进行操作的函数，并返回一个整数值。

create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ date,
  PRIMARY key(`id`,`time_`)
)
partition by range(year(time_))(
		partition p0 VALUES less than (202201),
		partition p1 VALUES less than (202301),
		partition p2 VALUES less than (202401),
		partition p3 VALUES less than MAXVALUE
)
1
2
3
4
5
6
7
8
9
10
11
12
13

1.3 TIMESTAMP
  在MySQL8.0中，也可以使用UNIX TIMESTAMP()函数根据TIMESTAMP列的值对表进行RANGE分区。

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN (MAXVALUE)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

1.4 非int
  COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势：

-- 单列
create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ varchar(30),
  PRIMARY key(`id`,`time_`)
)
partition by range columns(time_)(
		partition p0 VALUES less than ('202201'),
		partition p1 VALUES less than ('202301'),
		partition p2 VALUES less than ('202401'),
		partition p3 VALUES less than MAXVALUE
)

--多列
create table stafft(
  one varchar(30),
  two varchar(30)
)
partition by range columns(one,two)(
  partition p0 values less than ('0','10'),
  partition p1 values less than ('10','20'),
  partition p2 values less than ('20','30'),
  partition p3 values less than (maxvalue,maxvalue)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2.List（官方文档）

2.1 int
  LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
  假定有20个店铺，分布在4个有经销权的地区，如下表所示：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.2 非int
  与Range相同，添加COLUMNS关键字可支持非整数和多列。

  如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。
  当插入多条数据出错时，如果表的引擎支持事务（Innodb），则不会插入任何数据；如果不支持事务，则出错前的数据会插入，后面的不会执行。

3.HASH（官方文档）

3.1 HASH
  Hash分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数。
  Hash分区通过使用“PARTITION BY HASH(expr)”来实现，其中“expr”是一个返回整数的表达式。也可以是一个列名，但是类型必须是MySQL的整数类型之一。使用PARTITIONS num设置分区个数，如不设置默认为1，其中num是一个非负的整数。

create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ varchar(30),
  PRIMARY key(`id`,`time_`)
)
partition by hash(id)
partitions 3;
1
2
3
4
5
6
7
8
9

3.2 LINEAR HASH（官方文档）
  与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。但是会有热点区的问题，因为线性哈希给每个区分配的会不均匀，分配到较多的内容时，其访问量就会较多，从而成为热点区。

create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ varchar(30),
  PRIMARY key(`id`,`time_`)
)
partition by linear hash(id)
partitions 3;
1
2
3
4
5
6
7
8
9

4.KEY（官方文档）

4.1 KEY
  Key分区与Hash分区很相似，Key调用自己内部的Hash函数，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。Key不局限于整数类型。
  当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错（MySQL 8.0）。

create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ varchar(30),
  PRIMARY key(`id`,`time_`)
)
partition by key(time_)
partitions 3;
1
2
3
4
5
6
7
8
9

4.2 LINEAR KEY
  影响与Linear Hash一样，请参考上文中的Linear Hash。

create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ varchar(30),
  PRIMARY key(`id`,`time_`)
)
partition by linear key(id)
partitions 3;
1
2
3
4
5
6
7
8
9

二、MySQL分区操作

1. 添加分区

1.1 在最后追加分区

ALTER TABLE staff ADD PARTITION (PARTITION p4 VALUES LESS THAN (12));
1

当已有分区最后是MAXVALUE的时候不可用。报错:1481 - MAXVALUE can only be used in last partition definition

1.2 创建表之后修改分区
  表已有数据，这种做法，运行时间会比较长。建议新建表然后导入数据。修改分区会覆盖之前建立的分区。

alter table staff partition by range(id) (
		partition p0 values less than (5),
		partition p1 values less than (10),
		partition p2 values less than (15),
		partition p3 values less than (MAXVALUE)
);
1
2
3
4
5
6

1.3 分区

create table staff(
  id int(32) not null,
  code_ varchar(30),
  fname varchar(30),
  time_ varchar(20),
  primary key(`id`,`time_`)
)
partition by range(id)(
		partition p0 values less than (5),
		partition p1 values less than (10),
		partition p2 values less than (15),
		partition p3 values less than (MAXVALUE)
)
-- p2分为p4和p2
alter table staff reorganize partition p2 into
(	
	partition p4 values less than (12),
	partition p2 values less than (15)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.删除分区

2.1 drop
  删除分区的同时也会该分区内的删除数据。

alter table staff drop partition p0;
1

3.合并分区

  常规HASH和线性HASH的增加收缩分区的原理是一样的。增加和收缩分区后原来的数据会根据现有的分区数量重新分布。HASH分区不能删除分区，所以不能使用DROP PARTITION操作进行分区删除操作；
  通过ALTER TABLE … COALESCE PARTITION num来合并分区，这里的num是减去的分区数量；
  通过ALTER TABLE … ADD PARTITION PARTITIONS num来增加分区，这里是num是在原先基础上再增加的分区数量。

3.1 合并分区
  减少分区后数据会根据现有的分区进行重新分配。

alter table staff coalesce partition 3;
1

3.2 增加分区
  增加分区之后数据会相应进行调整。

alter table tblinhash add partition partitions 4;
1

3.3 移除分区
  移除分区的定义不会删除数据（所有分区移除）。

alter table staff remove partitioning
1

4.查询分区信息

4.1 查询分区表中各个分区的数据量

select partition_name,table_rows from information_schema.partitions where table_name='staff'
1

4.2 查询某个分区信息

select * from staff partition(p1)
1

4.3 查询的时候使用到分区
  如果查询是基于分区表的话，会显示查询将访问的分区。在5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示filtered需要使用explain extended命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

explain select * from staff where id = 6
1

普通表一个.frm和一个.idb 而分区表一个.frm和多个.idb文件
.frm：表结构的文件
.ibd：表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

附录：

• 分区字段必须是主键

  报错：1503 - A PRIMARY KEY must include all columns in the table’s partitioning function (prefixed columns are not considered).

• 分区字段，必须以分区字段进行查询，否则分区失效
• 一张表最多只能有1024个分区。
• 查询条件不是分区建立的条件，会走所有分区。