[MySQL 之五] 分区

MySQL 分区是一种 "分而治之" 的思想，根据一定的规则，将一个表分解成更小的、更容易管理的部分，用来指导分区划分的列称为分区键；而对于访问数据库的应用来说，逻辑上只有一个表或一个索引，屏蔽了底层的复杂性，分区对应用来说是完全透明的，不影响应用的业务逻辑。

1、分区的优点

① 和单个磁盘或者文件系统分区相比，可以存储更多的数据；

② 优化查询：在 where 子句中包含分区条件时，可以只扫描一个或多个分区来提高查询效率；同时在涉及 SUM()、COUNT() 这类聚合函数的查询时，可以容易地在每个分区上并行处理，最终只需要汇总所有分区得到的结果；

③ 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据；

④ 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

新版本的 MySQL 默认支持分区，通过 show plugins 可以检查当前版本是否安装了分区插件。

2、各大引擎对分区的支持

MySQL 支持使用大部分引擎（比如 MyISAM、InnoDB、Memory 等）创建分区；不支持使用 MERGE 或 CSV 存储引擎来创建分区表。

同一个分区表的所有分区必须使用相同的存储引擎，但是对不同的分区表可以使用不同的存储引擎。

MySQL 分区会把数据和索引完全分区，而不是单独对数据或索引分区。

3、分区类型

分区类型有 RANGE、LIST、HASH、KEY 四种。

如果一个分区表定义了主键或唯一索引，则分区键必须被包含在主键或唯一索引的字段中，否则无法建表成功。这很好理解，使用主键或唯一索引是最常见、最高效检索数据的方式，如果使用主键或唯一索引做分区键，根据分区定义，可以直接定位数据在哪个分区，不需要查询其余分区，最大程度发挥分区的优点。

（1）RANGE 分区

基于一个给定连续区间范围，把数据分配到不同的分区，且区间要连续并且不能重叠。

下面的分区表定义了 3 个分区，分区键为 store_id

其值为 < 10 的记录存储在分区 p0

其值为 10~20（不包含20）的记录存储在分区 p1

其值为 20~30（不包含30）的记录存储在分区 p2

create table emp (
    id int not null,
    ename varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job varchar(30) not null,
    store_id int not null
)
partition by range (store_id) (
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p2 values less than (30)
);

假如有一条 store_id ≥ 30 的记录插入分区表，则无法插入，因为该值不在分区范围内。

所以要考虑好分区键值的范围，一般都会设定一个默认值放在最末尾，其结构就类似于程序中的 if... else if... else if... else... 中最后的 else。

如果分区键的值不是整数，如常见的日期时间等数据类型，在 MySQL 5.1 之前需要借助 YEAR() 和 TO_DAY() 函数来将数据转换成整数，也是唯二支持的两个分区辅助函数，分区的关键声明语句如：

...
partition by range (YEAR(separated)) (
...

到了 MySQL 5.5 增加了一个支持函数 TO_SECONDS() 把日期转换成秒。并提供了 RANGE COLUMNS 分区支持非整数分区，这样创建日期分区就不需要通过函数进行转换了。

...
partition by range columns (separated) (
    partition p0 values less than ('1996-01-01'),
    partition p1 values less than ('2001-01-01'),
    partition p2 values less than ('2006-01-01')
);

（2）LIST 分区

如果分区键的取值是一些离散的值，比如订单状态，可以使用 LIST 分区。

LIST 分区是建立离散的值列表告诉数据库特定的值属于哪个分区，而 RANGE 分区是一个连续的数据段。

如下面的费用表根据分类进行分区，其分区划分如下：

类别为 3、5 的记录放在分区 p0

类别为 1、10 的记录放在分区 p1

类别为 4、9 的记录放在分区 p2

类别为 2 的记录放在分区 p3

类别为 6 的记录放在分区 p4

create table expenses (
    expense_date date not null,
    category int,
    amount decimal(10,3)
)partition by list(category) (
    partition p0 values in (3,5),
    partition p1 values in (1,10),
    partition p2 values in (4,9),
    partition p3 values in (2),
    partition p4 values in (6)
);

任何分区键离散值集合之外的值的记录都无法插入

MySQL 5.1 之前，LIST 分区只支持分区键是整数，所以若用来分区的元数据字段非整数，则需要有个中间的转换表来转换为整数；MySQL 5.5 开始通过 LIST COLUMNS 支持非整数分区，不需要额外的转换表。

create table expenses (
    expense_date date not null,
    category varchar(30),
    amount decimal(10,3)
)partition by list columns (category) (
    partition p0 values in ('lodging', 'food'),
    partition p1 values in ('flights', 'ground transportation'),
    partition p2 values in ('leisure', 'customer entertainment'),
    partition p3 values in ('communications'),
    partition p4 values in ('fees')
);

（3）COLUMNS 分区

Columns 分区是在 MySQL 5.5 引入的分区类型，解决之前的 RANGE 分区和 LIST 分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。具体的语句是 RANGE COLUMNS 和 LIST COLUMNS，支持整数、日期时间、字符串三大数据类型。

整数：只支持整型，其他数据类型比如浮点数、定点数不支持。
日期时间：只支持 date 和 datetime 类型。
字符串：只支持 char、varchar、binary、varbinary。

最大的亮点是支持多列分区，比如有一些主键或唯一索引是存在多个列的，这种场景下非常需要使用多列分区。对 RANGE 分区使用多列分区是，分区键的比较是对多列值的元组的比较。

创建一个多列分区的 RANGE 表，定义如下：

create table rc (
    a int,
    b int
) partition by range columns(a,b) (
    partition p0 values less than (0,10),
    partition p1 values less than (10,10),
    partition p2 values less than (10,20),
    partition p3 values less than (10,maxvalue),
    partition p4 values less than (maxvalue,maxvalue)
);

现在要插入一条记录 (1,10)，则确定分区的过程如下：

分区 p0 的元组是 (0,10)，插入的数据 a > 0，所以不会插入分区 p0
分区 p1 的元组是 (10,10)，插入的数据 a < 10，所以插入分区 p1，比较 b < 10 与否是在 a = 10 的基础上进行的

插入之后，可以通过以下 SQL 来查看数据在哪个分区：

SELECT
    partition_name part,
    partition_expression expr,
    partition_description descr,
    table_rows
FROM
    INFORMATION_SCHEMA.partitions
WHERE
    TABLE_SCHEMA = schema()
    AND TABLE_NAME = 'rc';

现在要插入一条记录 (10,9)，则确定分区的过程如下：

分区 p0 的元组是 (0,10)，插入数据的 a > 0，所以不会插入分区 p0
分区 p1 的元组是 (10,10)，插入数据的 a = 10，b < 10，所以插入分区 p1

（4）HASH 分区

Hash 分区有常规 HASH 分区和线性 HASH 分区，常规 HASH 使用的是取模算法，线性 HASH 使用的是一个线性的 2 的幂的运算法则。

// 常规 HASH
create table tablename (
    ...
) partition by hash(columnname) partitions num;

// 线性 HASH
create table tablename (
    ...
) partition by linear hash(columnname) partitions num;

如下面的表使用了常规 HASH 分区，一共分了 4 个区，分区键对 4 取模来决定放到哪个分区，插入的记录对应分区键的值为 234，MOD(234,2) = 2，所以数据会放到分区 2 中。

但是常规 HASH 存在一个缺陷就是，当需要新增或减少分区时，所有记录都需要重新计算分区，代价比较大，不适合需要灵活变动的需求。

使用线性 HASH 分区能解决这个问题，线性 HASH 采用一种类似于动态散列的技术，在分区维护（包含增加、删除、合并、拆分分区）时，能处理得更加迅速，缺点是各个分区之间数据分布不太均匀，解决办法是用一个随机性和分布性比较好的散列函数先计算分区键的散列值，再用分区值分区，HASH 分区允许使用用户自定义的表达式，如下所示：

// 线性 HASH
create table tablename (
    ...
) partition by linear hash(mermerhash(columnname)) partitions num;

Mermerhash 是一个比较流行的散列函数，它计算速度快，对分布规律的输入，也能获得很好的随机性，从而让数据分布均匀。

（5）Key 分区

Key 分区类似 Hash 分区，但不允许使用用户自定义的表达式，需要使用 MySQL 服务器提供的 HASH 函数，HASH 只支持整数分区，而 Key 分区支持使用除 Blob、Text 类型外的其他类型的列作为分区键。

create table tablename (
    ...
) partition by key(columnname) partitions num;

创建 Key 分区表时可以不指定分区键，默认会首先选择使用主键作为分区键。

在没有主键的情况下，会选择非空唯一键作为分区键。

作为分区键的唯一键必须是非空的，否则会报错。

在没有主键和唯一索引的情况下，必须指定分区键。

Key 分区同样有常规 Key 分区和线性 Key 分区，语法如下：

create table tablename (
    ...
) partition by linear key(columnname) partitions partnum;

（6）子分区

对每个分区再次进行分割，又成为复合分区

如上面的子分区的定义，首先外围是定义了一个 RANGE 分区，一共有 3 个分区；子分区又会对这 3 个分区分别做 HASH 分区，每个分区分成两个子分区，所以一共会被分成 6 个子分区。

4、分区管理

（1）RANGE、LIST 分区管理

删除分区

alter table tablename drop partition partname;

某个分区被删除后，后续插入的本来应该插入这个分区的值，可能会被分到其他分区。

如下面创建了一个表，划分了 3 个分区，插入 9 条数据，每个分区有 3 个。

现在删除分区 p1，则 11、12、13 的记录会被删除，再重新插入这几条记录，会被插入到分区 p2，因为 10 < 11,12,13 < 30。

如果是 LIST 分区，因为分区值是离散值，并且各个分区不重叠，所以分区删除后，在分区列上包含该分区离散值的记录无法插入成功。

增加分区

alter table tablename add partition

对于 RANGE 分区来说，只能在分区列表的大的一端添加新分区，原则是不会导致原来分区的数据需要重新调整分区，如果是在原分区范围内划分新的分区，则会添加分区失败，如下所示：

对于 LIST 分区来说，添加新分区的分区值，不能包含原分区的离散值，否则添加新分区失败。

重新定义分区

alter table tablename reorganize partition info

重新定义分区，可以拆分一个分区为多个新分区，也可以合并多个相邻的分区为一个分区。

（2）HASH、KEY 分区管理

减少分区

alter table tablename coalesce partition