MySQL 表分区

实验环境

数据库：MySQL 5.7.24
官方文档：
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
参考文章：
https://www.cnblogs.com/zhouguowei/p/9360136.html
https://blog.csdn.net/yongqi_wang/article/details/86576006
https://blog.csdn.net/weixin_40901788/article/details/83215889
参考书籍：《高性能 MySQL》

说明

在某些场景中，需要将表以一定的规律分割以达到优化效果。通常的做法是分表或者分区。分表是将一张表以 不同的规则分割（ 水平分割或垂直分割 ）成多张表。而分区与分表不同，分区只支持水平分区，表分区之后，从逻辑上来看仍是一张独立的表（ 执行 SQL 时仍将其作为一张表来查询 ），而底层则是分为多个物理子表（ 文件系统中表的每个分区都有各自的存储文件 ）。

注意：

1、从 MySQL 5.7.17 开始，通用的分区处理程序（ generic partitioning handler ）将被弃用，并在 MySQL 8.0 中正式删除。而后由表的存储引擎提供自己的分区处理程序（ native partitioning handler ），当前只有 InnoDB 和 NDB 存储引擎做到这一点。为了兼容 MySQL 8.0，需要将存储引擎修改为提供分区处理程序的引擎，如：

ALTER TABLE table_name ENGINE = INNODB;

当然，在 MySQL 8.0 之前还是可以使用大部分存储引擎来实现分区的，除了 MERGE, CSV, 和 FEDERATED 存储引擎。另外不可以在同一张表中为各个分区指定不同的存储引擎。

2、NDB 存储引擎只支持通过 KEY 或 LINEAR KEY 进行分区

3、分区适用于表的所有数据和索引;不能只对数据进行分区而不对索引进行分区，反之亦然，也不能只对表的一部分进行分区

4、可以在 CREATE TABLE 语句中的分区子句使用 DATA DIRECTORY 和 INDEX DIRECTORY 来为数据和索引指定特定的目录。在 Windows 上，MyISAM 表的各个分区和子分区不支持这两个选项。对于InnoDB表的各个分区和子分区，则只支持 DATA DIRECTORY 选项。

5、表的分区表达式中使用的所有列必须是表可能具有的每个惟一键的一部分，包括任何主键

-- 不能对以下 SQL 语句创建的表进行分组
-- 因为键 pk 和 uk 没有相同的列，所以在分区表达式中没有可用的列
CREATE TABLE tnp ( 
    id INT NOT NULL AUTO_INCREMENT,
    ref BIGINT NOT NULL,
    name VARCHAR(255), 
    PRIMARY KEY pk (id), 
    UNIQUE KEY uk (name) 
);

开始

检查是否支持分区

使用以下语句检查数据库是否支持分区：

SHOW plugins;

执行结果

也可以使用以下语句查询：

SELECT 
    PLUGIN_NAME as Name,
    PLUGIN_VERSION as Version,
    PLUGIN_STATUS as Status
FROM 
    INFORMATION_SCHEMA.PLUGINS
WHERE
    PLUGIN_TYPE='STORAGE ENGINE';

执行结果
检查输出列表中 partition 的 Status 值是否为 ACTIVE，没有的话证明当前 MySQL 不支持分区操作。如果需要 MySQL 支持分区，需要在编译时加上 -DWITH_PARTITION_STORAGE_ENGINE 选项。

分区类型

MySQL 5.7 中可用的分区类型如下：

• RANGE 分区：这一类型的分区根据给定范围的列值将行分配给分区
• LIST 分区：类似于 RANGE 分区，只是该类型是根据与一组离散值之一匹配的列来进行选择的
• HASH 分区：对于这种类型的分区，将根据用户定义的表达式返回的值来选择分区，该表达式对要插入到表中的行中的列值进行操作。该函数可以由 MySQL 中任何有效的表达式组成，表达式产生一个非负整数值。对这种类型的扩展 LINEAR HASH 也可用。
• KEY 分区：这个类型的分区类似于 HASH 分区，除了提供一个或多个要计算的列之外，MySQL 还提供了自己的哈希函数。这些列可以包含整数之外的值，因为 MySQL 提供的哈希函数保证无论列的数据类型是什么，结果都是一个整数值。

务必要记住，无论使用的分区类型是什么，分区总是在创建时自动从 0 开始编号和排序。当新的一行插入到一个分区表中时，将使用这些分区号来标识正确的分区。比如，表使用了 4 个分区，则这些分区的编号为 0，1，2 和 3。对于 RANGE 和 LIST 类型的分区，有必要确保为每个分区号定义了一个分区。对于 HASH 分区类型，用户提供的表达式计算的整数值必须大于 0。对于 KEY 分区类型，将由 MySQL 提供的哈希函数自动处理。

RANGE 分区

以范围分区的表的分区方式是，每个分区包含分区表达式返回值位于给定范围内的行。给定范围应该是连续而不重叠的，并且使用 VALUES LESS THAN 运算符来定义。

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 NOT NULL, 
    store_id INT NOT NULL 
);

现在有如上一张表，按 RANGE 方式分区有多种方式，比如根据 store_id 的大小划分：

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 NOT NULL, 
    store_id INT NOT NULL 
) PARTITION BY RANGE (store_id) ( 
    PARTITION p0 VALUES LESS THAN (6), 
    PARTITION p1 VALUES LESS THAN (11), 
    PARTITION p2 VALUES LESS THAN (16), 
    PARTITION p3 VALUES LESS THAN MAXVALUE 
);

该 SQL 语句划分了 4 个分区，当插入记录的 store_id 小于 6 时，该记录分配到 p0 分区，当 store_id 的值小于 11 时分配到 p1 分区，其它同理。最后一个分区定义使用了 MAXVALUE 以涵盖 store_id 大于 16 的行。假如没有这样做，当插入一条 store_id 大于 16 的记录时，MySQL 不知道将记录插入哪个分区，就会返回一个错误。

从数据文件可以看出， employees 表的底层已经被分为四份，以 # 加分区编号对应分区。

文件

此外还可以根据日期范围来分区，比如根据 separated 字段的年份来分区， YEAR 方法将返回日期的年份：

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 RANGE ( YEAR(separated) ) ( 
    PARTITION p0 VALUES LESS THAN (1991), 
    PARTITION p1 VALUES LESS THAN (1996), 
    PARTITION p2 VALUES LESS THAN (2001), 
    PARTITION p3 VALUES LESS THAN MAXVALUE 
);