MySQL还能这样玩---第二篇之不为人知的分区
分区是啥子嘛?
分区是指根据一定的规则，数据库把一个表分解成更多更小的，更容易管理的部分。
就访问数据库的应用程序而言，逻辑上只有一个表或者一个索引，但是实际上这个表可能由数十个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。
分区对应用来说完全透明，不影响应用的业务逻辑。
这里大家可以还不太懂分区的意义所在，耐心看下去，就懂了
分区有啥好处，能用来干啥?
分区有利于管理非常大的表，它采用 "分而治之"的逻辑。
分区引入了分区键的概念，分区键用于根据某个区间值或者范围值，特定值列表或者HASH函数执行行数据的聚集，让数据根据规则分步在不同的分区中，让一个大对象变成一些小对象。
分区优点:
- 和单个磁盘或者文件系统分区相比，可以存储更多数据
- 优化查询，在where子句中包含分区条件时，可以只扫描必要的一个或多个分区表来提高查询效率；同时在涉及SUM()和COUNT()这类聚合函数查询的时候，可以在每个分区上并行计算，最后汇总结果即可。在Mysql 5.7中，可以通过
select * from t partition (p0,p1)指定查询指定分区的数据 - 对于已经过期和不需要保存的数据，可以通过删除与这些数据有关的分区来删除数据
- 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。
典型使用场景: 日志文件按一定时间，分区存放，不需要的日志文件可以通过时间范围按区删除
Mysql 5.7开始默认支持分区，通过下面的命令可以查看当前Mysql是否支持分区功能
select * from information_schema.plugins where PLUGIN_NAME='partition'\G;
对于一个分区表的所有分区来说，必须使用同一个存储引擎，且分区数量不能超过8192
- 分区表设置存储引擎，只能用engine子句，并且该子句必须在其他任何分区选项之前。
例如:
create table emp(empid INT)
engine=innodb
partition by hash(MONTH(birth_date))
partition 6;
注意: MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区; 反过来也是一样，不能只对索引分区而不对表分区，同时也不能只对表的一部分数据进行分区，MYSQL的分区表上创建的索引一定是本地LOCAL索引。
分区类型
MYSQL 5.7中，主要分区有一下六种:
- RANGE分区: 基于一个给定连续区间范围,把数据分配到不同的分区
- LIST分区: 类似RANGE分区，区别在于List分区是基于枚举出的值列表分区，RANGE也是基于给定的连续区间范围分区。
- COLUMNS分区：类似于RANGE和LIST，区别在于分区键既可以是多列，又可以是非整数。
- HASH分区: 基于给定的分区个数，把数据取模分配到不同的分区。
- KEY分区: 类似于HASH分区，但使用MYSQL提供的HASH函数。
- 子分区: 也叫做复合分区或者组合分区，即在主分区下再做一层分区，将数据再次分割。
RANGE分区，LIST分区，HASH分区的分区键必须是INT类型，或者通过表达式返回INT类型，但KEY和COLUMENS分区除外，可以使用其他类型的列作为分区键。
如果希望RANGE和LIST了下的分区中使用非INT列作为分区键，可以选择COLUMNS分区。
无论使用哪种分区类型，要么分区没有主键和唯一键; 如果分区含有主键或者唯一键，就必须使用主键或者唯一键进行分区
RANGE分区
USE test;
CREATE TABLE emp(
id INT NOT NULL,
ename VARCHAR(30) NOT NULL
)
PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10)
);
员工id为1到4的保存在分区p0中。
员工id为5到9的保存在分区p1中。
大家思考如果员工id为10保存在哪里呢?
insert into emp values(2,"dhy");
insert into emp values(6,"like");
insert into emp values(10,"xpy");
因为没有规则包含员工id大于等于10的行，服务器不知道应该将记录保存到哪里，我们可以通过MAXVALUE表示最大可能的整数值，来防止这种情况发生:
增加一个分区p3来存放大于9的值
ALTER TABLE emp ADD PARTITION (PARTITION p3 VALUES LESS THAN MAXVALUE);
maxvalue表示最大可能的整数值
查看每个分区中记录数:
select partition_name part,partition_expression expr,partition_description descr,table_rows
from information_schema.partitions
where table_schema =schema()
and table_name='test';
partition_name:分区名
partition_expression: 分区字段
partition_description: 分区的范围
table_rows：当前分区内的数据量
values less than 子句还支持使用表达式?
MySQL支持在values less than 子句中使用表达式，比如，以日期作为range分区的分区列.
CREATE TABLE `emp` (
`id` INT(11) NOT NULL,
`ename` VARCHAR(30) NOT NULL,
loveDate DATE NOT NULL DEFAULT "1970-01-01"
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(loveDate))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN (2010) ENGINE = INNODB,
PARTITION p3 VALUES LESS THAN (2022) ENGINE = INNODB)
NULL值算啥?
在range分区中，分区键如果是null值会被当做一个最小值来处理。
range columns 可以支持非整数分区
DROP TABLE IF EXISTS emp;
CREATE TABLE `emp` (
`id` INT(11) NOT NULL,
`ename` VARCHAR(30) NOT NULL,
loveDate DATE NOT NULL DEFAULT "1970-01-01"
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS (loveDate)
(PARTITION p0 VALUES LESS THAN ("1900-01-01") ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN ("2010-01-01") ENGINE = INNODB,
PARTITION p3 VALUES LESS THAN ("2022-01-01") ENGINE = INNODB);
range分区小结
使用场景:
- 当需要删除过期的数据时，只需要简单的alter table emp drop partition p0 来删除p0分区中的数据，对于具有上百万条记录的表来说，删除分区要比运行delete语句快得多
- 经常运行包含分区键的查询。mysql可以很快地确定一个或者某些分区需要扫描，因为其他分区不可能包含有符合该WHERE子句的任何记录。
explain partitions select count(1) from emp where id=1\G
range分区的顺序必须是严格递增的
LIST分区
List分区是通过枚举一组特定的值，将其划分到一个分区中。
List分区通过下面的语句实现:
partition by list(expr)
expr是某列整数值，或者可以是通过一个表达式将某列值计算后返回整数值。
通过下面的方式定义分区
values in(val1,val2,val3,...)
与range分区不同，list分区没有严格的递增顺序要求
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
ename VARCHAR(30) NOT NULL
)
PARTITION BY LIST (id)
(
PARTITION p1 VALUES IN (10,11),
PARTITION p0 VALUES IN (5,6)
);
如果试图插入的列值或者分区表达式的返回值不包含分区值列表中时，那么insert操作会失败报错。
list分区不存在values lesss than maxvlaue这样包含其他值在内的定义方式。
将匹配的任何值都必须在值列表中找得到。
如果要使用非整数分区，可以创建list columns分区:
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
ename VARCHAR(30) NOT NULL
)
PARTITION BY LIST COLUMNS(ename)
(
PARTITION p1 VALUES IN ("dhy","dhylikexpy"),
PARTITION p0 VALUES IN ("ly")
);
COLUMNS分区
在mysql 5.5版本之前，range分区和list分区只支持整数分区，从而需要额外的函数计算得到整数或者通过额外的转化表来转化为整数再分区。
cloumns分区解决了这个问题，columns分区可以细分为range columns和list columns分区，两者都支持整数，日期时间和字符串三大数据类型。
columns分区的亮点在于支持多列分区和多数据类型分区，但是在mysql 5.7中，columns分区不支持表达式作为分区键
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
e_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS(id,e_id)
(
PARTITION p0 VALUES LESS THAN (10,20),
PARTITION p1 VALUES LESS THAN (10,30)
);
这里分键区元组的比较，就是多列排序，先按照
Hash分区
Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分区。
对一个表执行HASH分区时，MySQL会对分区键应用一个散列函数，以此确定数据应该放在哪个分区中。
Mysql支持Hash分区和线性Hash分区，常规Hash使用的是取模算法，线性Hash分区使用的是一个线性的2的幂运算法则。
PARTITION BY HASH(expr) PARTITIONS num;
expr是某列值，或者基于某列值计算得到整数的表达式
num是分区数量
举例:
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
e_id INT NOT NULL
)
PARTITION BY HASH(id) PARTITIONS 4;
注意事项
表达式expr可以是mysql中有效的任何函数或者表达式，只要他们返回一个既非常数也非随机数的整数。
每当插入/更新/删除一行数据的时候，这个表达式都需要重写计算一次，这意味着非常复杂的表达式可能会引起性能问题.
Mysql不推荐使用涉及多列的哈希表达式
线性hash
常规hash问题在哪里?
先说优点，常规hash通过取模的方式让数据尽可能分步在每个分区中，让每个分区管理的数据都减少了，提高了查询的效率。
可是当我们需要增加分区或者合并分区的时候，问题就出现了。
假设原来有4个常规hash分区，现在增加一个，变成六个，此时取模算法变为了mod(expr,6),这意味着原先5个分区中的大部分数据都需要通过重新计算重新分区。
常规hash的短板在此，因为分区管理上带来的代价太大了，所以为了降低分区管理的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。
线性hash和常规hash分区的区别在语法上表现为"PARTITION BY"子句中添加"LINEAR"关键字。
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
e_id INT NOT NULL
)
PARTITION BY LINEAR HASH(id) PARTITIONS 4;
这里线性hash计算过程，如果感兴趣的小伙伴，可以自行搜索，这里不做展开
线性hash和常规hash的对比
线性hash的优点在于分区维护(包括增加，删除，合并，拆分分区)时，Mysql能够处理的更加快速。
缺点是，对比常规hash分区取模的时候，线性hash各个分区之间数据的分步不太均衡。
KEY分区
KEY分区类似HASH分区，但与之不同的是，HASH分区能够使用自定义的表达式，KEY分区不可以，并且只能使用MYSQL服务器提供的HASH函数。
同时HASH分区只支持整数分区，而KEY分区支持使用BLOB和TEST外其他类型的列作为分区键。
PARTITION BY KEY(expr)子句可以创建一个KEY分区表
expr是零个或多个字段名的列表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
e_id INT NOT NULL
)
PARTITION BY KEY (id) PARTITIONS 4;
创建key分区表的时候，可以不指定分区键，默认会首先选择使用主键作为分区键
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
e_id INT NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY KEY () PARTITIONS 4;
在没有主键的情况下，会选择非空唯一键作为分区键
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL,
e_id INT NOT NULL,
UNIQUE KEY (id)
)
PARTITION BY KEY () PARTITIONS 4;
注意: 作为分区键的唯一键必须是非空的，如果不是非空的，依然会报错。并且在没有主键和唯一键的情况下，就不能指定分区键了，这点和前面不同
和Hash分区类似，在KEY分区中使用关键字LINEAR具有同样的作用，也就是LINEAR KEY分区时，分区的编号是通过2的幂算法得到的，而不是通过取模得到的。
KEY分区和HASH分区类似，在处理大量数据记录时，能够有效地分散热点。
子分区
子分区是对分区表中每个分区的再次分割，又被称为复合分区。
MYSQL 5.7支持对已经进行过RANGE 和 LIST 分区的表再进行子分区。
子分区可以使用HASH分区，也可以使用KEY分区,不能使用其他分区。
子分区由两种创建方法：
一种是不定义每个子分区子分区的名字和路径由分区决定，
二是定义每个子分区的分区名和各自的路径。
不定义每个子分区子分区的名字和路径
create table <table> (
// 字段
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段)
SUBPARTITION BY HASH(分区字段)
SUBPARTITIONS NUM(
partition <分区名称> values less than (Value),
partition <分区名称> values less than (Value),
...
partition <分区名称> values less than maxvalue
)
分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是对某个字段进行表达式运算如year(create_time)，使用range最终的值必须是数字
分区名称: 要保证不同，也可以采用 p0、p1、p2 这样的分区名称，
less than : 表示小于
Value : 表示要小于某个具体的值，如 less than (10) 那么分区字段的值小于10的都会被分到这个分区
maxvalue: 表示一个最大的值
NUM:子分区数
例如:
假如一个大型超市有40多家门店，该表保存40家超市的职员记录。这40家超市的的编号从1到40，如果你想将入职员工按年份区分同时还能精确到天区分，那么你可以采用range分区，创建的数据库表如下：
CREATE TABLE `employees_range_sub` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ename` varchar(30) NOT NULL DEFAULT '' COMMENT '员工名称',
`ecode` varchar(30) NOT NULL DEFAULT '' COMMENT '员工编号',
`store_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属门店',
`create_time` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
partition by range(year(create_time))
subpartition BY HASH (to_days(create_time))
subpartitions 2(
partition f0 values less than (2018),
partition f1 values less than (2019),
partition f2 values less than MAXVALUE
)
上面的表中有三个分区f0,f1,f2，其中每个分区又被分为了两个子分区。
分区文件（截图）：
定义每个子分区的分区名：
create table <table> (
// 字段
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段)
SUBPARTITION BY HASH(分区字段)
SUBPARTITIONS NUM(
partition <分区名称> values less than (Value)(
subpartition <子分区名称>，
subpartition <子分区名称>
),
partition <分区名称> values less than (Value)(
subpartition <子分区名称>，
subpartition <子分区名称>
),
...
partition <分区名称> values less than maxvalue(
subpartition <子分区名称>，
subpartition <子分区名称>
)
)
分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是对某个字段进行表达式运算如year(create_time)，使用range最终的值必须是数字
分区名称: 要保证不同，也可以采用 p0、p1、p2 这样的分区名称，
子分区名称: 要保证不同，也可以采用 s0、s1、s2 这样的子分区名称，子分区的名称不能喝分区名称重复
less than : 表示小于
Value : 表示要小于某个具体的值，如 less than (10) 那么分区字段的值小于10的都会被分到这个分区
maxvalue: 表示一个最大的值
假如一个大型超市有40多家门店，该表保存40家超市的职员记录。这40家超市的的编号从1到40，如果你想将入职员工按年份区分同时还能精确到天区分，那么你可以采用range分区，创建的数据库表如下：
CREATE TABLE `employees_range_sub_name` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ename` varchar(30) NOT NULL DEFAULT '' COMMENT '员工名称',
`ecode` varchar(30) NOT NULL DEFAULT '' COMMENT '员工编号',
`store_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属门店',
`create_time` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
partition by range(year(create_time))
subpartition BY HASH (to_days(create_time))
(
partition f0 values less than (2018)(
subpartition f0s0,
subpartition f0s1
),
partition f1 values less than (2019)(
subpartition f1s0,
subpartition f1s1
),
partition f2 values less than (2020)(
subpartition f2s0,
subpartition f2s1
)
);
分区文件（截图）：
注意:
复合分区适合保存非常大量的数据，在实际使用中，注意以下几点:
- 每个分区必须具有相同数量的子分区
- 如果要显示指定子分区，则每个分区都要显示指定，比如下面的语句中，f1没有显式指定子分区，执行会失败
CREATE TABLE `employees_range_sub_name` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`ename` varchar(30) NOT NULL DEFAULT '' COMMENT '员工名称',
`ecode` varchar(30) NOT NULL DEFAULT '' COMMENT '员工编号',
`store_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属门店',
`create_time` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
partition by range(year(create_time))
subpartition BY HASH (to_days(create_time))
(
partition f0 values less than (2018)(
subpartition f0s0,
subpartition f0s1
),
partition f1 values less than (2019),
partition f2 values less than (2020)(
subpartition f2s0,
subpartition f2s1
)
);
- 子分区的名称在整个表中是唯一的
Mysql分区对于NULL的处理
MySQL不禁止在分区键上使用NULL，分区键可能是一个字段或者一个用户定义的表达式。
在RANGE分区中，NULL值会被当做最小值来处理，LIST分区中，NULL值必须出现在枚举列表中，否则不被接受；
HASH/KEY分区中，NULL值会被当做零值来处理。
分区管理
mysql提供了添加，删除，重定义，合并，拆分，交换分区的命令，这些操作都可以通过ALTER TABLE命令来进行实现。
RANGE与LIST分区管理
删除分区
语法:
alter table drop partition 分区名
删除分区命令执行后，并不实际显示从表中删除的行数，但是确实会把当前分区下的所有数据删除。
删除list分区和删除rang分区的语句相同，只不过删除list分区之后，由于list分区的定义中不再包含已经被删除了的分区的值列表，所以后续无法写入包含有已经删除了的分区值列表的数据。
增加分区,子分区
添加分区:
alter table add partition (partition p4 values less than (2030));
对于list分区来说:
alter table add partition (partition p6 values in (6,11));
添加子分区:
alter table employees_range_sub_name add partition
(
partition f3 values less than (2021)(
subpartition f3s0,
subpartition f3s1
)
)
对于range分区来说，只能将新添加的分区，添加到分区列表最大一端
对于list分区来说，不能添加一个包含现有分区值列表中任意值的分区
分区拆分和合并
range分区
MYSQL提供了在不丢失数据的情况下，通过重新定义分区的语句
alter table partition into
重新定义分区
拆分举例:
计划将f1分区(2000-2015)拆分为两个分区f4(2000-2005)和f5(2005-2015)
alter table emp reorganize partition f1 into(
partition f4 values less than (2005),
partition f5 values less than (2015),
)
合并分区可以将多个相邻的range分区合并为一个range分区或者多个range分区,举例:
alter table emp reorganize partition f4,f5 into(
partition f4 values less than (2005),
partition f5 values less than (2015),
)
注意事项
重新定义range分区时，只能够重新定义相邻的分区，不能跳过某个range分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义分区来改变表分区的类型，例如，不能把RANGE分区变为HASH分区，也不能把HAHS分区变为RANGE分区。
LIST分区
重定义p4,p5,p6分区
alter table expenses reorganize partition p4,p5,p6 into (
partition p4 values in (6,11),
partition p4 values in (7,8),
)
注意事项
类似重定义range分区，重新定义list分区时，只能够重新定义相邻的分区，不能跳过list分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间。
也不能用重新定义分区来改变表分区的类型，例如，不能把list分区变为range分区，也不能把range分区变为list分区。
HASH和KEY分区管理
HASH分区和KEY分区类型，但是不能以RANGE和LIST分表中删除分区的相同方式，来从HASH或者KEY分区的表中删除分区，而key通过ALTER TABLE COALESCE PARTITION 语句来合并HASH分区或者KEY分区。
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT
)
PARTITION BY HASH (id) PARTITIONS 4;
要减少HASH分区的数量，从4个分区变为2个分区，可以执行下面的命令:
alter table emp coalesce partition 2;
coalesce 不能用来增加分区数量，否则会报下面的错:
alter table emp coalesce partition 8;
可以通过下面的语句来增加分区数量:
alter table emp add partition partitions 8;
上面是对原表新增n个分区，而不是增加到n个分区
交换分区
MYSQL 5.6增加了交换分区的功能，语句如下:
alter table pt exchange partition p with table nt
可以实现将分区表pt的一个分区或者子分区p中的数据和普通表nt中的数据进行交换。交换分区需要满足下面的条件:
- 表nt不能是分区表，因为交换分区不能通过分区对分区的方式进行，如果有这种需求，可以用一个普通表作为中间表，通过两次交换分区来实现
- 表nt不能是临时表
- 表pt和nt的结构，除分区外，应该完全一致，包括索引名称和索引列都必须一致
- 表nt上不能有外键，也不能有其他表的外键依赖nt
- nt表的所有数据，应该都在分区p定义范围内。在MYSQL 5.7中，如果确定数据都在界限内，可以通过增加WITHOUT VALIDATION来跳过这个逐行验证的过程
交换分区使用演示
先创建一个分区表，插入一些测试数据:
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
)
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10)
);
INSERT INTO emp VALUES(1,"dhy");
INSERT INTO emp VALUES(2,"dhy2");
INSERT INTO emp VALUES(3,"dhy3");
INSERT INTO emp VALUES(6,"dhy6");
INSERT INTO emp VALUES(7,"dhy7");
INSERT INTO emp VALUES(8,"dhy8");
仿照分区表，创建一个结构一样的普通表，插入一些不同的测试数据:
CREATE TABLE test_emp LIKE emp;
#将分区表改为普通表
ALTER TABLE test_emp REMOVE PARTITIONING;
INSERT INTO test_emp VALUES(8,"xpy");
查看分区表e中的数据分布:
SELECT partition_name part,partition_expression expr,partition_description descr,table_rows
FROM information_schema.partitions
WHERE table_name='emp';
执行交换分区命令
alter table emp exchange partition p1 with table test_emp;
数据成功完成了交换
注意事项
使用交换分区，可以方便地完成对包含大量数据的分区，子分区的备份，迁移等工作。
但是特别注意，交换前尽量提前做好备份，避免交换后带来的数据问题:
- 交换分区不会触发任务被交换的表或分区上的触发器
- 表中自增列的值会被重置
- 交换分区的命令中，ignore关键字不会产生任何影响