17.Mysql分区

17.Mysql分区
分区是指根据一定的规则把一个表分解成多个部分，逻辑上仍是一张表，实际上由多个物理分区对象组成。
分区对于应用是完全透明的，不影响业务逻辑和SQL编写。
分区的优点：
可以存储更多的数据；
优化查询；当where条件包含分区键时只在特定的分区查询；当涉及聚合函数时每个分区可以并行查询。
可以通过分区快速删除过期数据；
可以将不同的分区分散在多个磁盘上，获取更高的磁盘IO。

17.1 分区概述
分区使用分区键对某个列的取值进行范围、列表、HASH值划分，从而将表中的数据划分到为不同的分区。
查看当前Mysql是否支持分区：
show variables like '%partition%';
Mysql分区由具体的存储引擎实现，只有部分存储引擎(Innodb)支持分区。
同一个表的所有分区必须使用相同的存储引擎；
不同的分区表可以使用不同的存储引擎。
例子：
create table temp (empid int,salary decimal(7,2),birth_date date)
engine=innodb
partition by hash(month(birth_date)) partitions 4;

17.2 分区类型
17.2.1 Range分区
Range分区：基于一个给定列的取值的连续范围进行分区。分区键必须是INT类型。
在有主键列的表中，主键列必须作为分区键；
在没有主键列的表中，INT类型列可以作为分区键，非INT类型列经过表达式处理后转换为INT类型也可以作为分区键。
语法：
create table 表名 (
列名 类型(长度) 约束,
...
)
partition by range (列表达式) (
partition 分区名1 values less than (列值1),
...
[partition 分区名n values less than MAXVALUE]
);
说明：
列值区间必须连续，且不能重叠；
分区名不区分大小写，且不能重复；
列值区间为前闭后开[)，即包含左侧，不包含右侧；
插入值不在所有区间内，插入将报错；
MAXVALUE为默认最大值，设置MAXVALUE分区后将不能再扩展分区；
如果列是非INT类型，可使用函数、表达式将列转为INT类型，也可以使用range columns分区。
range columns分区支持对日期列和字符列进行分区。
如果分区键列为null，将被当作最小值处理。
例子1：
create table temp (empid int,salary decimal(7,2),birth_date date)
partition by range(year(birth_date)) (
partition p1 values less than (1990),
partition p2 values less than (2000),
partition p3 values less than (2010)
);
例子2：
create table temp (empid int,salary decimal(7,2),birth_date date)
partition by range columns(birth_date) (
partition p1 values less than ('1990-01-01'),
partition p2 values less than ('2000-01-01'),
partition p3 values less than ('2010-01-01')
);
查询SQL：
explain parttions select count(1) from temp where birth_date>='2000-01-01';

17.2.2 List分区
List分区：基于一个给定列的取值的枚举值进行分区。分区键必须是INT类型。
在有主键列的表中，主键列必须作为分区键；
在没有主键列的表中，INT类型列可以作为分区键，非INT类型列经过表达式处理后转换为INT类型也可以作为分区键。
语法：
create table 表名 (
列名 类型(长度) 约束,
...
)
partition by list (列表达式) (
partition 分区名1 values in (整数列表),
...
);
说明：
整数列表是列的一个或多个取值，多个值时用逗号分隔；
整数列表取值与顺序无关；
插入值不在所有区间内，插入将报错；
如果列是非INT类型，可使用函数、表达式将列转为INT类型，也可以使用list columns分区。
list columns分区支持对日期列和字符列进行分区。
例子1：
create table temp (empid int,category int,birth_date date)
partition by list(category) (
partition p1 values in (1,3),
partition p2 values in (5,2),
partition p3 values in (4)
);
例子2：
create table temp (empid int,category varchar(10),birth_date date)
partition by list columns(category) (
partition p1 values in ('高中','大专'),
partition p2 values in ('本科'),
partition p3 values in ('硕士','博士')
);

17.2.3 Range/List Columns分区
Range/List Columns分区是Range分区和List分区的增强，区别在于分区键可以是整数、日期、字符串类型。
Range/List Columns分区的分区键支持的数据类型包括：
整数类型(tinyint、smallint、mediumint、int、bigint),日期时间类型(date、datetime),字符类型(char、varchar、binary、varbinary)。
Range/List Columns分区的分区键不支持的数据类型包括：浮点类型(decimal、float)、大对象类型(blob、text)。
Range/List Columns分区不支持列表达式分区，只能使用原始列。
Range/List Columns分区支持多个列组合分区，多列组合分区时将基于元组进行比较，即进行多列排序根据排序结果来选择分区。
例子1：
create table test(
a int,
b int
)
partition by range columns(a,b) (
partition p01 values less than (0,10),
partition p02 values less than (10,10),
partition p03 values less than (10,20),
partition p04 values less than (20,20)
);
17.2.4 Hash分区
Hash分区：基于一个给定列的取值的HASH函数进行分区。分区键必须是INT类型。
Hash分区可以用来分散热点读，并确保数据在预先确定个数的分区中尽可能的均匀分布。
Hash分区分为常规Hash分区和线性Hash分区(Linear hash),常规Hash分区采用取模算法，线性Hash分区采用2的幂运算法则。
例子1：
create table test(
a int,
b int
)
partition by hash(a) partitions 3;
对a列的数值取除以3的余数，余数为0/1/2，分别对应在一个分区中。
常规Hash分区不支持新增分区、合并分区等分区管理操作。
例子2：
create table test(
a int,
b int
)
partition by linear hash(a) partitions 4;
线性Hash分区建议分区个数是2的幂次个，否则将导致数据分布不均匀。
首先找到大于等于分区个数的2的幂次，V=power(2,ceiling(log(2,分区个数)))；
其次将列值和(V-1)进行按位与操作。
线性Hash分区支持分区管理操作，包括：增加分区、删除分区、合并分区、拆分分区。

17.2.5 Key分区
Key分区是Hash分区的增强，区别在于分区键可以任何类型(除大对象类型BLOB和TEXT)。
Key分区是支持整数、日期、字符类型的Hash分区，但不支持表达式。
例子1：
create table test(
a int,
b int
)
partition by key(a) partitions 4;
注意：
不指定分区键列时默认使用主键列，没有主键时默认非空的唯一键列；
不指定分区键列且没有主键时，唯一键列可空将报错；
没有主键、唯一键时，必须指定分区键列。
例子2：
create table test(
a int,
b int
)
partition by linear key(a) partitions 4;
注意：Key分区同样支持常规Key分区和线性key分区。

17.2.6 子分区
子分区(subpartitioning)是对分区表的再次分区，又称复合分区。
可以对Range分区和List分区再次进行分区，子分区可以是hash分区或key分区。
例子：
create table temp (empid int,salary decimal(7,2),birth_date date)
partition by range columns(birth_date)
subpartition by hash(empid) subpartitions 2
(
partition p1 values less than ('1990-01-01'),
partition p2 values less than ('2000-01-01'),
partition p3 values less than ('2010-01-01')
);

17.2.7 mysql分区处理null值的方式
mysql允许可空列作为分区键。
Range分区中null值被当作最小值处理，始终在第一个分区中；
List分区中null值必须出现在枚举列表中(否则报错)，可以自行定义null值在哪个分区中；
hash分区中null值被当作0处理，始终在第一个分区中。
建议分区键设置为非空。
分区信息在information_schema.partitions表中记录。

17.3 分区管理
17.3.1 Range和List分区管理
增加分区：
alter table 表名 add partition (partition 分区名 values less than (列值));
alter table 表名 add partition (partition 分区名 values in (列值列表));
注意：
Range分区添加分区时必须保证列值与已有列值连续且有序；
List分区添加分区时必须保证列值列表与已有列值列表不重复。
删除分区:删除分区的同时会删除分区内的数据。
alter table 表名 drop partition 分区名;
重定义分区：可以将一个分区拆分为多个分区；也可以将相邻多个分区合并为一个分区。
重定义分区只能重新定义相邻的分区，且分区区间必须能够覆盖原来的分区区间，且不能改变分区类型。
拆分分区：
alter table 表名 reorganize partition 原分区名 into (
partition 现分区名1 values less than (新区间列值),
partition 现分区名2 values less than (等于原列值)
);
合并分区：
alter table 表名 reorganize partition 原分区名1,原分区名2 into (
partition 现分区名1 values less than (等于原分区名2列值)
);
alter table 表名 reorganize partition 原分区名1,原分区名2 into (
partition 现分区名1 values in (新列值列表1),
partition 现分区名2 values in (新列值列表2)
);
注意：重组前后总的列值列表要相等，每个分区的列值列表可以调整。

17.3.2 Hash和Key分区管理
增加分区：
alter table 表名 add partition partitions 新增的分区个数;
减少分区：
alter table 表名 coalesce partition 减少到几个分区;

17.4 小结

转载于:https://www.cnblogs.com/BradMiller/p/10123395.html