三十七、MySQL基础系列笔记之MySQL分区(二)HASH/KEY/子分区
HASH分区
HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。
MySQL支持两种HASH分区，即常规HASH分区和线性HASH分区（LINEAR HASH）。常规HASH使用的是取模算法；线性HASH分区使用的是一个线性的2的幂的运算法则。
常规HASH使用
常规HASH使用PARTITION BY HASH(EXPR) PARTITIONS num子句对分区类型、分区键和分区个数进行定义；使用PARTITION BY LINEAR创建线性HASH分区。
EXPR条件为 某列值或一个基于某列值返回一个整数值的表达式；
num是一个非负的整数，表示分割成分区的数量，默认为1.
使用常规HASH分区案例
# 为demo_emp表使用store_id创建4个hash分区
create table demo_emp(
id int not null,
name varchar(20),
store_id int not null
)partition by hash(store_id) partitions 4;
# 插入值
mysql> insert into demo_emp values(1,'lisi',567);
Query OK, 1 row affected (0.01 sec)
计算保存的分区位置：如store_id=567，select它会被保存到哪个分区？n=mod(234,4)的结果就对应保存的存储。4代表partions所创建的4个分区。
线性HASH使用与计算
线性HASH使用的是partition by linear。
# 线性HASH
create table demo_emp(
id int not null,
name varchar(20),
store_id int not null
)partition by hash(store_id) partitions 4;
计算线性HASH分区：假设保存记录的分区编号为N，num为非负整数(表示分割成分区的数量)，那么分区编号N通过如下步骤得到：
步骤一：计算一个大于等于num的2的幂，其值可以通过公式V=Power(2,Ceiling(Log(2,num)))得到。
如：创建的表中设置了4个分区，即num=4,下面计算num的2的幂。
V = power(2, ceiling(log(2,num)))
= power(2,ceiling(log(2,4)))
= power(2,ceiling(2))
= power(2,2)
= 4
步骤二：计算分区：公式 N=F(column_list) & (V-1)
如：可知我们在步骤一时计算出了V=4，现在计算sotre_id=567对应的N值。
N = F(column_list) & (V-1)
= 567 & (4-1)
= 3
步骤二计算出了store_id=567所对应存储的分区，若是计算出store_id>=4(即设置的分区时)，需要通过步骤三来计算存储入哪个分区
步骤三：当 N >= num时，设置V=ceiling(V/2)，设置N=N & (V-1)
由于 store_id=567，即N=3<4,因此该记录会被存储第三个分区中。
需要知道的是，当线性HASH的分区个数是2的N次幂时，线性HASH分区的结果与常规HASH分区的结果是一致的。
线性分区的优点：对分区维护(增、删、合并、拆分分区)处理时，速度更快；缺点点，对比常规的HASH分区（取模）时，线性各个分区之间数据的分布不太均衡。
KEY分区
key分区使用mysql服务器提供的hash函数；key分区支持使用除blob和text外其他类型的列作为分区键。
key分区使用partition by key(expr)子句来创建一个key分区表；expr是0个或多个字段名的列表。
案例：创建一个基于job字段分区的表
create table demo_emp02(
id int not null,
name varchar(20) not null,
job varchar(20) not null
)partition by key(job) partitions 4;
mysql> insert into demo_emp02 values (1,'lisi','it');
Query OK, 1 row affected (0.01 sec)
key分区有如下几种情况：
1）key分区可以不指定分区键，默认使用主键作为分区键。
create table demo01(
id int not null primary key,
name varchar(20) not null
)partition by key() partitions 4;
2）没有主键时，则选择非空唯一键作为分区键。
create table demo02(
id int not null,
name varchar(20),
unique key (id)
)partition by key() partitions 4;
3）若无主键又无唯一键，则必须指定分区键，否则报错。
create table demo03(
id int,
name varchar(20)
)partition by key() partitions 4;
子分区
子分区（Subpartitioning）是分区表中对每个分区的再次分割，又被称为复合分区（Composite Partitioning）。
MySQL5.7之后可以通过对RANGE或LIST分区表再次进行子分区。子分区既可以使用HASH分区，也可以使用KEY分区。
子分区语法：
create table 表名(
)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);
分区名称：要保证分区名称的不同；
maxvalue：表示一个最大值
案例：
create table demo_emp03(
id int,
create_time date
)partition by range(year(create_time))
subpartition by hash(to_days(create_time))
subpartitions 2
(
partition p0 values less than(1970),
partition p1 values less than(2010),
partition p2 values less than maxvalue
);
表demo_emp03有3个range分区p0、p1、p2，这三个range分区又被分割成了2个子分区。实际情况是整张表被分成了6个分区。
MySQL分区对NULL值的处理
MySQL允许在分区键中使用NULL,分区键可以是一个字段也可以是一个表达式。一般情况下，MySQL的分区把NULL当作0值或一个最小值处理。
需要注意的是：RANGE分区中，null值被当作最小值处理；LIST分区中，null值必须出现在枚举列表中，否则不被接受；HASH/KEY分区中，null值被当作0值处理。
create table demo01(
id int,
name varchar(5)
)partition by range(id) (
partition p0 values less than (-10),
partition p1 values less than(0),
partition p2 values less than maxvalue
);
mysql> insert into demo01 values (null,'null');
Query OK, 1 row affected (0.01 sec)
# 确认null是否被当作最小值处理
mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows
from information_schema.partitions
where table_schema=schema() and table_name='demo01';
+------+------+----------+------------+
| part | expr | descr | TABLE_ROWS |
+------+------+----------+------------+
| p0 | `id` | -10 | 1 |
| p1 | `id` | 0 | 0 |
| p2 | `id` | MAXVALUE | 0 |
+------+------+----------+------------+
3 rows in set (0.00 sec)
2021-11-16