MySQL 8 中的分区详解

在上一章中，介绍了 MySQL 8 中的复制。这包括对复制、配置和实现的详细解释。本章还解释了组复制与集群的区别，并介绍了作为解决方案的复制方法。

在本章中，我们将在 MySQL 8 中进行分区。分区的概念是使用多个操作符通过特定操作管理和维护数据，并定义控制分区的规则。基本上，它提供了一个配置挂钩，用于以指定的方式管理底层数据文件。

我们将介绍以下有关分区的主题：

分区的概念与数据库中数据存储的物理方面有关。如果你看一下SQL标准，它们并没有给出很多关于这个概念的信息，SQL语言本身打算独立于用于存储特定于不同模式、表、行或列的信息或数据的媒体或数据结构来工作。高级数据库管理系统增加了将用于数据存储的物理位置指定为硬件、文件系统或两者的方法。在 MySQL 中，InnoDB存储引擎通过tablespace的概念为这些目的提供支持。

分区使我们能够将各个表的部分作为单独的表存储在文件系统中的不同位置。此外，分布是通过提供用户指定的规则来完成的，例如以模、散列函数的形式，或与简单值或范围匹配，并且用户提供的表达式充当通常称为分区函数的参数。

在 MySQL 8 中，InnoDB是目前唯一支持分区的存储引擎。在InnoDB存储引擎中启用分区不需要额外的规范。分区不能与存储引擎MyISAM、CSV、FEDERATED、MERGE一起使用。对于本章中给出的所有示例，我们假设默认存储引擎为InnoDB。

创建分区表时，将使用默认存储引擎，与创建表相同，并且可以通过指定STORAGE ENGINE选项来覆盖，就像我们对任何表所做的一样。下面的示例演示如何创建一个表，该表由哈希分区为四个分区，所有分区都使用InnoDB存储引擎：

CREATE TABLE tp (tp_id INT, amt DECIMAL(5,2), trx_date DATE)
 ENGINE=INNODB
 PARTITION BY HASH ( MONTH (trx_date) )
 PARTITIONS 4;

Partitioning is applicable on all indexes and all data of the table. It is not applicable on either indexes or data, and vice versa is also not applicable. It can be applicable on both indexes and data together and it cannot be applied on part of the table. The preceding table tp has no unique or primary keys defined but in general practice we usually have primary keys, unique keys, or both as part of the table, and partitioning column choice depends upon these keys if any of them is present. The partitioning column choice is given in detail in the partitioning keys, primary keys, and unique keys section. To simplify the concept of partitioning the examples given may not include these keys.

MySQL 8 支持几种类型的分区，如下所示：

• RANGE Partitioning：根据给定值范围内的列值将行分配给分区
• LIST Partitioning：从与给定值集之一匹配的列值向分区分配行
• COLUMNS Partitioning：使用RANGE或LIST分区将行分配给具有多个列值的分区
• HASH Partitioning：根据对列值求值的用户指定表达式分配分区
• KEY Partitioning：除了HASH分区之外，还允许使用多个列值
• Subpartitioning：除了分区之外，还允许在分区表中进一步分区，也称为复合分区

表的不同行可以分配给不同的物理分区；这就是所谓的水平分区。表的不同列可以分配给不同的物理分区；这就是所谓的垂直分区。MySQL 8 目前支持水平分区。

对于LIST、RANGE和LINEAR HASH类型的分区，分区列的值被赋予分区函数。分区函数返回一个整数值，该整数值是存储记录的分区号。partition函数必须是非随机和非恒定的。partition函数不能包含查询，可以使用返回整数或NULL的 SQL 表达式，其中作为intval的整数必须跟在表达式-MAXVALUE <= intval <= MAXVALUE后面。此处，-MAXVALUE表示下限，MAXVALUE表示整型值的上限。

对于同一个表的所有分区，存储引擎必须相同，但是对于同一数据库或 MySQL 服务器中的不同分区表，使用不同的存储引擎没有限制。

使用SQL语句修改分区表和执行诸如添加、重定义、合并、删除或拆分现有分区表等操作的方法有很多种。有关分区表和分区的信息也可以通过SQL语句获得。

MIN_ROWS和MAX_ROWS可用于配置最大和最小行数，并可存储在分区表中。

还提供了分区和子分区的显式选择。它允许行匹配 where 子句中给定的条件。在分区中，所描述的修剪概念不扫描不可能存在匹配值的分区，而是使用查询应用，而分区选择同时适用于查询和许多DML语句。

存储过程或函数、用户定义函数或插件、用户变量或声明变量在分区表达式中受到限制。在详细章节中给出了一些适用于分区的限制和限制。

有关分区的一些优点，请参见以下列表：

• 分区有助于在一个表中存储比文件系统分区或单个磁盘上存储的数据更多的数据。
• 通过删除一个或多个仅包含无用数据的分区，可以轻松删除已变得无用的数据。在某些需要单独添加特定数据的情况下，根据指定的规则在单个或多个分区中进行分区可以很容易地做到这一点。
• 根据分区数据自动进行的查询优化，方法是不根据 where 条件搜索不适用的分区中的数据。
• 除了分区修剪之外，还显式支持分区选择，where 子句应用于指定的分区或多个分区。
• 通过将数据搜索分离到多个磁盘，可以实现更大的查询吞吐量。

在本节中，您将了解不同类型的分区以及使用特定分区的目的。以下是 MySQL 8 中可用的分区类型列表：

• RANGE partitioning
• LIST partitioning
• COLUMNS partitioning
• HASH partitioning
• KEY partitioning
• Subpartitioning

除了上面的列表，我们还将在详细章节中看到 MySQL 8 分区中的NULL处理。

数据库分区的一个非常常见的用例是按日期分隔数据。MySQL 8 不支持某些数据库系统显式提供的日期分区，但可以很容易地创建带有日期、时间或日期时间列的分区方案，或者基于计算这些列类型值的日期/时间相关表达式的分区方案。

如果使用KEY或LINEAR KEY分区，则可以使用 date、time 或 datetime 类型作为分区列的列值，无需任何修改，而在其他分区类型中，则需要返回整数或NULL值的表达式。

无论您使用哪种类型的分区，分区总是按照创建分区的顺序自动编号，并带有整数。例如，如果表使用四个分区，则根据创建顺序，每个分区的编号分别为 0、1、2 和 3。

指定分区数时，必须将其计算为不带任何前导零的正非零整数。小数部分不允许作为分区号。

分区的名称不区分大小写，应该遵循约定或规则，就像其他 MySQL 标识符（如表）一样。分区定义中使用的选项已经由CREATE TABLE语法提供。

现在，让我们详细了解分区，并检查每种类型，以了解它们之间的差异。

在这种类型的分区中，正如名称所述，RANGE在一个表达式中给出，该表达式计算某个值是否位于给定的范围内。范围是用VALUES LESS THAN运算符定义的，它们不应该重叠和连续。

在接下来的几个示例中，假设我们正在创建一个表，其中包含 25 家食品店的员工个人记录。门店编号从 1 到 25，是由 25 家食品店组成的连锁店，如下图所示：

CREATE TABLE employee (
 employee_id INT NOT NULL,
 first_name VARCHAR(30),
    last_name VARCHAR(30),
    hired_date DATE NOT NULL DEFAULT '1990-01-01',
    termination_date DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
);

现在让我们对表进行分区，这样您就可以根据需要按范围对表进行分区。假设您考虑使用除法来分割数据五个方法，用“{ To.t0}”范围进行分区。为此，表创建定义如下所示：

CREATE TABLE employee (
 employee_id INT NOT NULL,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    hired_date DATE NOT NULL DEFAULT '1990-01-01',
 termination_date 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 (21),
 PARTITION p4 VALUES LESS THAN (26)
);

因此，根据上述分区方案，包含在门店 1 到 5 工作的员工的所有插入行都存储在p0分区中，在门店 6 到 10 工作的员工存储在p1分区中，依此类推。如果你看一下分区定义，分区是从最低到最高的store_id列值排序的，PARTITION BY RANGE语法看起来类似于编程语句if… elseif …语句，不是吗？

好吧，你在想如果一张唱片带有store_id``26会发生什么；这将导致错误，因为服务器不知道将记录放置在何处。有两种方法可以防止发生此错误：

1. 在INSERT语句中使用IGNORE关键字。
2. 使用MAXVALUE代替指定范围（26）。

当然，您可以通过使用ALTER TABLE语句为存储区 26-30、30-35 等添加新分区来扩展限制。

与store_id类似，您还可以基于作业代码（基于列值的范围）对表进行分区。假设管理职位使用 5 位代码，办公室和支持人员使用 4 位代码，普通员工使用 3 位代码，则分区表创建定义如下：

CREATE TABLE employee (
 employee_id INT NOT NULL,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    hired_date DATE NOT NULL DEFAULT '1990-01-01',
    termination_date DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
 PARTITION p0 VALUES LESS THAN (1000),
 PARTITION p1 VALUES LESS THAN (10000),
    PARTITION p2 VALUES LESS THAN (100000)
);

您还可以使用两列日期类型值中的一列指定分区。假设您希望根据每个员工加入的年份进行划分，因此按YEAR(hired_date)的值进行划分。现在，表定义如下所示：

CREATE TABLE employee (
 employee_id INT NOT NULL,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    hired_date DATE NOT NULL DEFAULT '1990-01-01',
    termination_date DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(hired_date)) (
    PARTITION p0 VALUES LESS THAN (1996),
    PARTITION p1 VALUES LESS THAN (2001),
 PARTITION p2 VALUES LESS THAN (2006),
 PARTITION p3 VALUES LESS THAN MAXVALUE
);

根据该方案，在1996之前记录的所有雇佣员工将存储在p0分区中，然后在2001之前记录的雇佣日期将存储在p1分区中，在p2分区中记录的雇佣日期介于2001和2006之间，其余记录将存储在p3分区中。

基于时间间隔的划分方案可以通过以下两种方式实现：

1. 通过RANGE对表进行分区，并使用对日期、时间或日期时间列值进行操作的函数为分区表达式返回整数值
2. 按RANGE COLUMN对表进行分区，并使用日期、时间或日期时间列作为分区列

MySQL 8 支持RANGE COLUMN，在COLUMN PARTITIONING部分详细介绍。

正如名称所述，LIST分区使用列表进行表分区。列表为使用VALUES IN (value_list)分区时定义的逗号分隔整数值；这里，value_list指的是逗号分隔的整数文本。

LIST分区在许多方面与RANGE分区类似，但存在差异。每个分区中使用的运算符是不同的。运算符使用逗号分隔的值列表与列值或计算为整数值的分区表达式匹配。

以 employee 表为例，使用 create table 语法的表的基本定义如下：

CREATE TABLE employee (
 employee_id INT NOT NULL,
 first_name VARCHAR(30),
 last_name VARCHAR(30),
 hired_date DATE NOT NULL DEFAULT '1990-01-01',
 termination_date DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
);

假设您希望将这 25 家食品店分配到北区、南区、东区、西区和中区五个区域，每个区域的门店 ID 号分别为（1,2,11,12,21,22）、（3,4,13,14,23,24）、（5,6,15,16,25）、（7,8,17,18）和（9,10,19,20）。

使用分区列表对表进行分区将为表分区提供以下定义：

**```sql CREATE TABLE employee ( employee_id INT NOT NULL, first_name VARCHAR(30), last_name VARCHAR(30), hired_date DATE NOT NULL DEFAULT '1990-01-01', termination_date DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY LIST (store_id) ( PARTITION pNorth VALUES IN (1,2,11,12,21,22), PARTITION pSouth VALUES IN (3,4,13,14,23,24), PARTITION pEast VALUES IN (5,6,15,16,25), PARTITION pWest VALUES IN (7,8,17,18), PARTITION pCentral VALUES IN (9,10,19,20) );

正如您在前面的语句中所看到的，按区域分区意味着可以很容易地根据特定分区内的区域更新存储记录。假设该组织将西区出售给另一家公司；然后，您可能需要使用查询中的`pWest`分区从西区删除所有员工记录。执行`ALTER TABLE`员工`TRUNCATE PARTITION pWest`比`DELETE`报表`DELETE from employee where store_id IN (7,8,17,18)`更容易、更高效；此外，您还可以使用`DROP`语句删除员工记录—`ALTER TABLE employee DROP PARTITION pWest`。在执行上一条语句的同时，您还将从表分区定义中删除`pWest PARTITION`，并且需要再次使用`ALTER`语句来添加`pWest PARTITION`并恢复先前的分区表方案。

与`RANGE`分区类似，您也可以使用使用散列或键的`LIST`分区来生成复合分区，也称为`subpartitioning`。您将了解更多关于`subpartitioning`的详细信息，以下是`subpartitioning`的专用部分。

在`LIST`分区中，没有像`MAXVALUE`这样可以包含所有可能值的全面捕获机制。相反，您必须管理`values_list`本身中的预期值列表，否则`INSERT`语句将导致表中没有值 9 分区的错误，如下例所示：

```sql
CREATE TABLE tpl (
 cl1 INT,
 cl2 INT
)
PARTITION BY LIST (cl1) (
 PARTITION p0 VALUES IN (1,3,4,5),
 PARTITION p1 VALUES IN (2,6,7,8)
);

INSERT INTO tpl VALUES (9,5) ;

正如您在前面的INSERT语句中所看到的，值 9 不是分区模式期间给出的列表的一部分，因此存在错误。如果使用多值 insert 语句，相同的错误可能导致所有插入失败，并且不会插入任何记录；而是使用IGNORE关键字来避免此类错误，如以下INSERT语句示例所示：

INSERT IGNORE INTO tpl VALUES (1,2), (3,4), (5,6), (7,8), (9,11);

顾名思义，这种类型的分区使用列本身。我们可以使用两个版本的列分区。一个是RANGE COLUMN，另一个是LIST COLUMN。除了RANGE COLUMN和LIST COLUMN分区之外，MySQL 8 还支持使用非整数类型的列，这些列可用于定义值范围或列表值。允许的数据类型列表如下所示：

• RANGE和LIST分区列支持INT、BIGINT、MEDIUMINT、SMALLINT、TINYINT的所有列类型，但不支持FLOAT或DECIMAL等其他数值列类型
• 支持DATE和DATETIME，但不支持与日期和时间相关的其他列类型作为分区列
• 支持字符串列类型BINARY、VARBINARY、CHAR和VARCHAR，但不支持将TEXT和BLOB列类型作为分区列

现在，让我们逐一详细了解一下RANGE COLUMN分区和LIST COLUMN分区。

顾名思义，您可以使用具有RANGE分区和RANGE COLUMN分区的列定义范围，但区别在于您可以定义多个提供范围的列，此外，您还可以选择除整数以外的列类型。

因此，RANGE COLUMN分区与RANGE分区的区别如下：

• RANGE COLUMNS可以使用一列或多列，比较发生在列值列表之间，而不是标量值之间
• RANGE COLUMNS只能使用列的名称，不能使用任何表达式
• RANGE COLUMNS分区列类型不限于INTEGER列类型，可以使用字符串、日期、日期时间列类型作为分区列

RANGE COLUMNS的表分区具有以下基本语法：

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS (column_list) (
 PARTITION partition_name VALUES LESS THAN (value_list) [,
 PARTITION partition_name VALUES LESS THAN (value_list) ] [,
...]
)
column_list:
 column_name[, column_name] [, ...]
value_list :
 value[, value][, ...]

在前面的语法中，column_list表示分区列列表，value_list表示分区定义值列表，value_list必须与column_list中定义的值的数量相同。坦率地说，COLUMNS条款中的列数（column_list）必须与VALUES LESS THAN条款中的值数（value_list）相同。

下面的示例清楚地说明了它是什么以及它与表定义的关系：

CREATE TABLE trc (
 p INT,
    q INT,
    r CHAR(3),
    s INT
)
PARTITION BY RANGE COLUMNS (p,s,r) (
 PARTITION p0 VALUES LESS THAN (5,10,'ppp'),
 PARTITION p1 VALUES LESS THAN (10,20,'sss'),
 PARTITION p2 VALUES LESS THAN (15,30,'rrr'),
 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

现在使用以下语句将记录插入表trc：

INSERT INTO trc VALUES (5,9,'aaa',2) , (5,10,'bbb',4) , (5,12,'ccc',6) ;

在这种类型的分区中，表分区定义中使用列列表，并且与RANGE COLUMN类似，必须提供相应列的值列表。与RANGE COLUMN类似，可以使用整数类型以外的列类型，即字符串、日期和日期时间列类型。

假设您要求一家企业分布在 12 个城市，并且出于营销目的，您使用三个城市的四个区域进行管理，如下所示：

• 1 区及城市：艾哈迈达巴德、苏拉特、孟买
• 2 区及城市：德里、古尔冈、旁遮普
• 3 区及城市：加尔各答、米佐拉姆、海得拉巴
• 4 区，含城市：班加罗尔、钦奈、高知

现在，为客户数据创建一个表，该表包含相应区域的四个分区，并将它们与客户所在城市的名称一起列出。表分区定义如下所示：

CREATE TABLE customer_z (
 first_name VARCHAR(30),
    last_name VARCHAR(30),
    street_1 VARCHAR(35),
    street_2 VARCHAR(35),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS (city) (
 PARTITION pZone_1 VALUES IN ('Ahmedabad', 'Surat', 'Mumbai'),
 PARTITION pZone_2 VALUES IN ('Delhi', 'Gurgaon', 'Punjab'),
 PARTITION pZone_3 VALUES IN ('Kolkata', 'Mizoram', 'Hyderabad'),
 PARTITION pZone_4 VALUES IN ('Bangalore', 'Chennai', 'Kochi')

);

与RANGE COLUMN分区类似，不需要在COLUMNS()子句中提供任何将列值转换为整型文字的表达式，只允许列名称列表本身。

引入HASH分区的主要目的是确保定义的分区数量之间的日期分布均匀。因此，对于HASH分区，您需要指定列值或计算被散列的列值的表达式，以及分区表要划分到的分区数。

在表中定义HASH分区需要在表定义中指定PARTITION BY HASH (expr)子句，其中expr是返回整数的表达式，另外需要用PARTITIONS n指定分区数，其中n是表示分区数的正整数。

以下定义在store_id列上创建了一个具有HASH分区的表，分为五个分区：

CREATE TABLE employee (
 employee_id INT NOT NULL,
 first_name VARCHAR(30),
 last_name VARCHAR(30),
 hired_date DATE NOT NULL DEFAULT '1990-01-01',
 termination_date DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
)
PARTITION BY HASH (store_id)
PARTITIONS 4;

在上面的语句中，如果您排除了PARTITIONS子句，那么分区的数量将自动默认为 1。

MySQL 8 支持线性散列，它基于线性二次幂算法，而不是基于散列函数值模的常规散列。LINEAR HASH分区需要PARTITION BY子句中的LINEAR关键字，如下图：

CREATE TABLE employee (
 employee_id INT NOT NULL,
 first_name VARCHAR(30),
 last_name VARCHAR(30),
 hired_date DATE NOT NULL DEFAULT '1990-01-01',
 termination_date DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL
)
PARTITION BY LINEAR HASH ( YEAR(hired_date))
PARTITIONS 4;

使用线性散列的优点是分区操作更快，缺点是与常规散列分区相比，数据分布更不均匀。

**# 密钥分区

这种类型的分区类似于HASH分区，使用用户定义的表达式而不是散列函数。KEY PARTITIONING使用CREATE TABLE语句中的PARTITION BY KEY子句进行分区定义。KEY分区的语法规则与HASH分区的语法规则相似，下面我们列出它们的区别，以便理解：

• 分区使用的不是HASH，而是KEY

KEY()中有一个或多个列名列表，如果KEY中没有定义列，但该表有一个定义的主键或唯一键，并带有NOT NULL约束，则该列自动作为KEY的分区列：

CREATE TABLE tk1 (
 tk1_id INT NOT NULL PRIMARY KEY,
    note VARCHAR(50)
)
PARTITION BY KEY ()
PARTITIONS 2;

与其他分区类型不同，列类型不仅限于NULL或整数值：

CREATE TABLE tk2 (
 cl1 INT NOT NULL,
 cl2 CHAR(10),
 cl3 DATE
)
PARTITION BY LINEAR KEY (cl1)
PARTITIONS 3;

正如您在前面的示例语句中所看到的，与HASH分区类似，KEY分区也支持LINEAR KEY分区，并且与LINEAR HASH分区具有相同的效果。

子分区也称为复合分区，顾名思义，它只是将每个分区划分为分区表本身。见以下声明：

CREATE TABLE trs (trs_id INT, sold DATE)
PARTITION BY RANGE ( YEAR(sold) )
    SUBPARTITION BY HASH ( TO_DAYS(sold) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (2001),
        PARTITION p2 VALUES LESS THAN MAXVALUE
);

正如您在前面的示例语句中所看到的，表trs有三个RANGE分区，每个分区p0, p1, p2被进一步划分为两个子分区。实际上，整个表被划分为六个分区。

子分区可以在使用RANGE或LIST分区的表上进行，子分区可以使用KEY或HASH分区类型。子分区的语法规则与常规分区中的相同，只是在KEY分区中指定默认列，因为它不会自动将该列作为子分区。

下面是使用子分区时要考虑的要点列表：

• 定义的每个分区的分区数必须相同
• 必须使用SUBPARTITIONING子句指定名称，或者指定默认选项
• 为子分区指定的名称在整个表中必须是唯一的

MySQL 8 没有任何特定的内容禁止将NULL分区为列值、分区表达式或用户定义表达式中的值。即使允许将NULL作为值，表达式返回的值也必须是整数，因此 MySQL 8 具有分区实现，因此它将NULL视为小于ORDER BY子句中的任何非NULL值。

NULL处理的行为在不同类型的分区中有所不同：

• RANGE分区中的处理NULL：如果插入了列中包含的NULL值，则该行将插入到范围中指定的最低分区中
• 使用LIST分区处理NULL：如果该表有一个使用LIST分区的分区定义，并且其分区定义了一个值列表，该值列表在value_list中明确指定NULL为值，则插入成功；否则，它将给出一个没有为NULL指定分区的错误表
• 使用HASH和KEY分区处理NULL：NULL在使用HASH或KEY分区定义表分区时处理方式不同，如果分区表达式返回NULL则用零值包装。因此，基于分区，插入操作将成功地将记录插入到零分区。

有很多方法可以使用SQL语句来修改分区表，您可以使用ALTER TABLE语句删除、添加、合并、拆分或重新定义分区。还有一些方法可以检索分区表和分区信息。我们将在以下各节中看到这些内容：

• RANGE和LIST分区管理
• HASH和KEY分区管理
• 分区维护
• 获取分区信息

分区添加和删除的处理方式与RANGE和LIST分区类型类似。通过使用带有DROP PARTITION选项的ALTER TABLE语句，可以删除由RANGE或LIST分区的表。

在执行ALTER TABLE ... DROP PARTITION语句之前，请确保您拥有DROP权限。DROP PARTITION将删除所有数据，并从表分区定义中删除分区。

以下示例说明了带有ALTER TABLE语句的DROP PARTITION选项：

SET @@SQL_MODE = '';
CREATE TABLE employee (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(25) NOT NULL,
 last_name VARCHAR(25) NOT NULL,
 store_id INT NOT NULL,
 department_id INT NOT NULL
) 
 PARTITION BY RANGE(id) (
 PARTITION p0 VALUES LESS THAN (5),
 PARTITION p1 VALUES LESS THAN (10),
 PARTITION p2 VALUES LESS THAN (15),
 PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employee VALUES
 ('', 'Chintan', 'Mehta', 3, 2), ('', 'Bhumil', 'Raval', 1, 2),
 ('', 'Subhash', 'Shah', 3, 4), ('', 'Siva', 'Stark', 2, 4),
 ('', 'Chintan', 'Gajjar', 1, 1), ('', 'Mansi', 'Panchal', 2, 3),
 ('', 'Hetal', 'Oza', 2, 1), ('', 'Parag', 'Patel', 3, 1),
 ('', 'Pooja', 'Shah', 1, 3), ('', 'Samir', 'Bhatt', 2, 4),
 ('', 'Pritesh', 'Shah', 1, 4), ('', 'Jaymin', 'Patel', 3, 2),
 ('', 'Ruchek', 'Shah', 1, 2), ('', 'Chandni', 'Patel', 3, 3),
 ('', 'Mittal', 'Patel', 2, 3), ('', 'Shailesh', 'Patel', 2, 2),
 ('', 'Krutika', 'Dave', 3, 3), ('', 'Dinesh', 'Patel', 3, 2);

ALTER TABLE employee DROP PARTITION p2;

在前面的语句中，执行了ALTER TABLE employee DROP PARTITION p2;语句后，可以看到分区p2中的所有数据都被删除了，如果您想删除所有数据，但又需要保留表定义和分区方案，可以使用TRUNCATE PARTITION选项来实现类似的结果。

为了向现有分区表添加新的LIST或RANGE分区，可以使用ALTER TABLE ... ADD PARTITION语句。

通过使用SHOW CREATE TABLE语句，您可以验证并查看ALTER TABLE语句是否对表定义和分区模式产生了预期的效果。

HASH或KEY类型的表分区与RANGE或LIST类型分区的表分区类似。如果表是按HASH或KEY类型的分区进行分区的，则删除分区不适用，但可以使用ALTER TABLE ... COALESCE PARTITION合并HASH或KEY分区。

考虑如果您的客户端表数据是由 AutoT0A.分区划分的，则在十二个分区中划分如下：

CREATE TABLE client (
 client_id INT,
 first_name VARCHAR(25),
 last_name VARCHAR(25),
 signed DATE
)
PARTITION BY HASH (MONTH (signed))
PARTITIONS 12;

在前面的表分区模式中，如果要将分区数量减少到 8 个而不是 12 个，请使用以下ALTER TABLE语句：

ALTER TABLE client COALESCE PARTITION 8;

在前面的语句中，数字 8 表示要从表中删除的分区数。删除的分区不能超过表分区架构中已存在的分区。类似地，您可以使用ALTER TABLE... ADD PARTITION语句添加更多分区。

有许多维护任务可以通过一些表和分区上的几个语句来完成。可以使用ANALYSE TABLE、CHECK TABLE、REPAIR TABLE和OPTIMIZE TABLE等语句来完成这些操作，这些语句是分区表特别支持的。

对于单个或多个分区表上的此类操作，ALTER TABLE有许多扩展，如下所示：

• 重建分区：此选项从分区中删除所有记录并重新插入，因此在碎片整理过程中被认为是有帮助的。以下是一个例子：

 ALTER TABLE trp REBUILD  PARTITION p0, p1, p2;

• 优化分区：如果从表的一个或多个分区中删除了多行，或者在VARCHAR、BLOB、TEXT等变长列类型的大量数据中有多行变化，可以执行OPTIMIZE PARTITION回收分区数据文件中未使用的空间。以下是一个例子：

 ALTER TABLE top OPTIMIZE PARTITION p0, p1, p2;

ALTER TABLE ... OPTIMIZE PARTITION does not work correctly with the InnoDB storage engine, so instead use ALTER TABLE ... REBUILD PARTITION and ALTER TABLE ... ANALYZE PARTITION for such tables.

• 分析分区：在该选项中，读取并存储分区的密钥分布。以下是一个例子：

 ALTER TABLE tap ANALYZE  PARTITION p1, p2;

• 正在修复分区：此选项仅在发现有损坏的分区需要修复时使用。以下是一个例子：

 ALTER TABLE trp REPAIR PARTITION p3;

• 检查分区：此选项用于检查分区中的任何错误，例如非分区表中使用的CHECK TABLE选项。以下是一个例子：

 ALTER TABLE tcp CHECK PARTITION p0;

为了在所有分区上执行操作，有一个选项使用ALL而不是上述所有选项中指定的特定分区。

有关分区的信息可以通过多种方式获得，如下所示：

• SHOW CREATE TABLE语句可用于查看分区的模式信息，其中包含分区表中的所有分区子句
• SHOW TABLE STATUS语句可以通过查看表的状态来检查表是否已分区
• EXPLAIN SELECT语句可用于查看给定SELECT选项使用的分区
• 使用INFORMATION_SCHEMA.PARTITIONS表查询分区表信息

下面是一个使用SHOW CREATE TABLE语句选项查看分区信息的示例：

SHOW CREATE TABLE employee;

前面语句的输出具有分区模式的单独信息，包括表模式的公共信息。

类似地，您可以从INFORMATION_SCHEMA.PARTITIONS表中检索有关分区的信息。

EXPLAIN选项提供了大量关于使用列进行分区的信息。例如，它给出了从特定于分区的查询中获得的行数。分区将根据查询语句进行搜索。它还提供有关键的信息。

EXPLAIN还用于从非分区表获取信息。如果没有分区，则不会给出任何错误，但会在分区列中给出一个NULL值。

在本节中，您将看到分区如何使用称为分区修剪的优化器优化SQL语句子句执行，以及如何使用SQL语句有效地使用分区数据进行选择并对分区执行修改操作。

分区剪枝与分区中的优化概念有关。在分区修剪中，描述为的概念不扫描不可能存在匹配值的分区基于查询语句应用。

假设有一个分区表tp1，使用以下语句创建：

CREATE TABLE tp1 (
 first_name VARCHAR (30) NOT NULL,
 last_name VARCHAR (30) NOT NULL,
 zone_code TINYINT UNSIGNED NOT NULL,
 doj DATE NOT NULL
)
PARTITION BY RANGE (zone_code) (
 PARTITION p0 VALUES LESS THAN (65),
 PARTITION p1 VALUES LESS THAN (129),
 PARTITION p2 VALUES LESS THAN (193),
 PARTITION p3 VALUES LESS THAN MAXVALUE
);

在前面的示例表tp1中，假设您希望从以下SELECT语句中检索结果：

SELECT first_name, last_name , doj from tp1 where zone_code > 126 AND zone_code < 131;

现在，您可以从前面的语句中看到，根据该语句，分区p0或p3中没有包含数据的行，因此我们只需要在p1或p2中搜索数据以获得匹配条件。因此，通过限制搜索，可以花费更少的时间和精力通过表中的所有分区匹配和搜索数据。这种对不匹配分区的裁剪称为修剪

与具有相同模式、数据和查询语句的非分区表相比，优化器可以利用分区修剪来更快地执行查询。

优化器可以根据WHERE条件约简在以下情况下进行修剪：

• partition_column IN (constant1, constant2, ..., contantN)
• partition_column =  constant

在第一种情况下，优化器为列表中的每个值计算分区表达式，并创建在计算期间匹配的分区列表，然后仅对该分区列表中的分区执行扫描或搜索。

在第二种情况下，优化器仅基于给定的常量或特定值计算分区表达式，并确定哪个分区包含该值，并且仅对该分区执行搜索或扫描。对于这种情况，可以使用另一个算术比较，而不是使用 equals。

目前，INSERT语句不支持剪枝，但SELECT、UPDATE和DELETE语句支持剪枝。

修剪也适用于短范围，其中优化器可以将范围转换为等效的值列表。当分区表达式包含可缩减为相等集的相等或范围时，或者如果分区表达式表示递增或递减关系，则可以应用优化器。

如果分区使用TO_DAYS()或YEAR()函数，则修剪也适用于DATE或DATETIME的列类型，如果此类表在分区表达式中使用TO_SECONDS()函数，则修剪也适用。

假设您有一个表tp2，如下所示：

CREATE TABLE tp2 (
 first_name VARCHAR (30) NOT NULL,
 last_name VARCHAR (30) NOT NULL,
 zone_code TINYINT UNSIGNED NOT NULL,
 doj DATE NOT NULL
)
PARTITION BY RANGE (YEAR(doj)) (
 PARTITION p0 VALUES LESS THAN (1971),
 PARTITION p1 VALUES LESS THAN (1976),
 PARTITION p2 VALUES LESS THAN (1981),
 PARTITION p3 VALUES LESS THAN (1986),
 PARTITION p4 VALUES LESS THAN (1991),
 PARTITION p5 VALUES LESS THAN (1996),
 PARTITION p6 VALUES LESS THAN (2001),
 PARTITION p7 VALUES LESS THAN (2006),
 PARTITION p8 VALUES LESS THAN MAXVALUE
);

现在，在前面的语句中，以下语句可以从分区修剪中受益：

SELECT * FROM tp2  WHERE doj = '1982-06-24';
UPDATE tp2  SET region_code = 8 WHERE doj BETWEEN '1991-02-16' AND '1997-04-26';
DELETE FROM tp2  WHERE doj >= '1984-06-22' AND doj <= '1999-06-22';

对于最后一条语句，优化器的作用如下：

1. 找到范围低端为YEAR('1984-06-22')的分区，给出了在p3分区中找到的值 1984。
2. 找到范围高端为YEAR('1999-06-22')的分区，得到p5分区中的值 1999。
3. 只扫描上面两个已识别的分区以及它们之间的任何分区。

因此，在上述情况下，需要扫描的分区仅为p3、p4和p5，其余分区在匹配时可以忽略

前面的示例使用RANGE分区，但分区修剪也适用于其他类型的分区。假设您有如下语句所示的表tp3模式：

CREATE TABLE tp3 (
 first_name VARCHAR (30) NOT NULL,
 last_name VARCHAR (30) NOT NULL,
 zone_code TINYINT UNSIGNED NOT NULL,
 description VARCHAR (250),
 doj DATE NOT NULL
)
PARTITION BY LIST(zone_code) (
 PARTITION p0 VALUES IN (1, 3),
 PARTITION p1 VALUES IN (2, 5, 8),
 PARTITION p2 VALUES IN (4, 9),
 PARTITION p3 VALUES IN (6, 7, 10)
);

对于前面的表模式，请考虑是否要执行这个语句。优化器确定哪些分区可以具有值1、2和3，并找到p1和p0，因此跳过其余分区p3和p2。

可以修剪带有常量的列值，如以下示例语句所示：

UPDATE tp3 set description = 'This is description for Zone 5' WHERE zone_code = 5;

The optimization is performed only when the size of the range is smaller than the number of partitions.

还支持分区和子分区的显式选择，这使行能够匹配 where 子句中给出的条件，这称为分区选择。这与分区修剪非常相似，因为只扫描特定分区进行匹配，但在以下两个关键方面有所不同：

• 要扫描的分区由语句的颁发者指定，并且不是自动的，例如分区修剪
• 分区修剪仅限于查询，而分区选择同时支持查询和许多DML语句

显式分区选择支持的 SQL 语句如下所示：

• INSERT
• SELECT
• UPDATE
• REPLACE
• LOAD DATA
• LOAD XML
• DELETE

以下带有PARTITION选项的语法用于显式分区选择：

PARTITION (partition_names)
partition_names :
 partition_name, ...

前面的选项后面始终紧跟着它所属的表结构或表模式。partition_names表示将在分区中使用的分区或子分区的逗号分隔名称列表。partition_names中的分区和子分区名称可以是任意顺序，甚至可以重叠，但列表中的每个名称必须是特定表的现有分区或子分区名称，否则语句将失败，错误消息partition_name不存在。

如果使用了PARTITION选项，则仅检查列出的分区和子分区是否有匹配的行。PARTITION选项也可以在SELECT语句中用于检索属于任何给定分区的行

假设您使用以下语句创建了表employee：

SET @@SQL_MODE = '';
CREATE TABLE employee (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(25) NOT NULL,
 last_name VARCHAR(25) NOT NULL,
 store_id INT NOT NULL,
 department_id INT NOT NULL
) 
 PARTITION BY RANGE(id) (
 PARTITION p0 VALUES LESS THAN (5),
 PARTITION p1 VALUES LESS THAN (10),
 PARTITION p2 VALUES LESS THAN (15),
 PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employee VALUES
 ('', 'Chintan', 'Mehta', 3, 2), ('', 'Bhumil', 'Raval', 1, 2),
 ('', 'Subhash', 'Shah', 3, 4), ('', 'Siva', 'Stark', 2, 4),
 ('', 'Chintan', 'Gajjar', 1, 1), ('', 'Mansi', 'Panchal', 2, 3),
 ('', 'Hetal', 'Oza', 2, 1), ('', 'Parag', 'Patel', 3, 1),
 ('', 'Pooja', 'Shah', 1, 3), ('', 'Samir', 'Bhatt', 2, 4),
 ('', 'Pritesh', 'Shah', 1, 4), ('', 'Jaymin', 'Patel', 3, 2),
 ('', 'Ruchek', 'Shah', 1, 2), ('', 'Chandni', 'Patel', 3, 3),
 ('', 'Mittal', 'Patel', 2, 3), ('', 'Shailesh', 'Patel', 2, 2),
 ('', 'Krutika', 'Dave', 3, 3), ('', 'Dinesh', 'Patel', 3, 2);

现在，如果您使用分区p1进行检查，您将看到以下输出作为在分区p1中添加的行：

mysql> SELECT * FROM employee PARTITION (p1);
+----+-----------+------------+----------+---------------+
| id | last_name | last_name | store_id | department_id |
+----+-----------+------------+----------+---------------+
| 5 | Chintan | Gajjar | 1 | 1 |
| 6 | Mansi | Panchal | 2 | 3 |
| 7 | Hetal | Oza | 2 | 1 |
| 8 | Parag | Patel | 3 | 1 |
| 9 | Pooja | Shah | 1 | 3 |
+----+-----------+------------+----------+---------------+
5 rows in set (0.00 sec) 

如果您使用此语句SELECT * FROM employee WHERE id BETWEEN 5 AND 9;，它将给出相同的输出。

为了从多个分区中检索行，可以使用以逗号分隔的分区名称列表。例如，SELECT * FROM employee PARTITION (p1,p2)将生成分区p1和p2中的所有行，并排除其余分区。

任何受支持的分区类型都可以使用分区选择语句。MySQL 8 在创建表时自动添加分区名称，表中指定了LINEAR HASH或LINEAR KEY分区类型，但没有任何名称，这也适用于子分区。在这个表上执行SELECT语句时，您可以指定 MySQL 8 生成的分区名称，用于特定于分区的数据检索。

PARTITION选项也适用于INSERT ... SELECT语句的SELECT语句，通过该选项，我们还可以插入从特定分区或子分区检索到的数据

PARTITION选项也适用于对具有特定分区或子分区数据的表执行 join 查询的SELECT语句。

在本节中，您将看到 MySQL 8 分区中的限制和限制，详细介绍禁止的构造、性能注意事项以及与存储引擎和函数相关的限制方面，以从表分区中获得最佳好处。

分区键与主键和唯一键之间的关系对于分区模式结构设计非常重要。一行中的规则是，分区表中分区使用的列必须包含表中的每个唯一键。因此，每个唯一键，包括表上的主键列，都必须是分区表达式的一部分。请看以下使用不符合规则的唯一键的CREATE TABLE语句示例：

CREATE TABLE tk1 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 UNIQUE KEY (cl1, cl2)
)
PARTITION BY HASH(cl3)
PARTITIONS 4;

CREATE TABLE tk2 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 UNIQUE KEY (cl1),
 UNIQUE KEY (cl3)
)
PARTITION BY HASH(cl1 + cl3)
PARTITIONS 4;

在前面创建表tk1和tk2的每个语句中，建议的表可以至少有一个不包括分区表达式中所有列的唯一键。

现在看一下以下修改后的表创建语句，这些语句是为了工作，并从无效语句变为有效语句：

CREATE TABLE tk1 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 UNIQUE KEY (cl1, cl2, cl3)
)
PARTITION BY HASH(cl3)
PARTITIONS 4;

CREATE TABLE tk2 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 UNIQUE KEY (cl1, cl3)
)
PARTITION BY HASH(cl1 + cl3)
PARTITIONS 4;

如果查看以下表结构，它根本无法被分区，因为无法同时包含作为分区键列一部分的唯一键列：

CREATE TABLE tk4 (
 cl1 INT NOT NULL,
 cl2 INT NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 UNIQUE KEY (cl1, cl3),
 UNIQUE KEY (cl2, cl4)
);

根据定义，每个主键都是唯一的键。该限制也适用于表的主键（如果有）。以下是表tk5和tk6中无效语句的两个示例：

CREATE TABLE tk5 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 PRIMARY KEY(cl1, cl2)
)
PARTITION BY HASH(cl3)
PARTITIONS 4;

CREATE TABLE tk6 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 PRIMARY KEY(cl1, cl3),
 UNIQUE KEY(cl2)
)
PARTITION BY HASH( YEAR(cl2) )
PARTITIONS 4;

在前面的两条语句中，相应的主键不包括在分区表达式中引用的所有列中。以下陈述有效：

CREATE TABLE tk7 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 PRIMARY KEY(cl1, cl2)
)
PARTITION BY HASH(cl1 + YEAR(cl2))
PARTITIONS 4;

CREATE TABLE tk8 (
 cl1 INT NOT NULL,
 cl2 DATE NOT NULL,
 cl3 INT NOT NULL,
 cl4 INT NOT NULL,
 PRIMARY KEY(cl1, cl2, cl4),
 UNIQUE KEY(cl2, cl1)
)
PARTITION BY HASH(cl1 + YEAR(cl2))
PARTITIONS 4;

如果表没有唯一键或主键，则该限制不适用，并且可以根据分区类型的兼容列类型在分区表达式中使用任何列。上述所有限制也适用于ALTER TABLE声明。

分区支持不是由 MySQL 服务器提供的，而是由 MySQL 8 中存储引擎自己的或本机分区处理程序提供的。在 MySQL 8 中，InnoDB存储引擎只提供本机分区处理程序，因此分区表创建不适用于任何其他存储引擎。

ALTER TABLE ... OPTIMIZE PARTITION无法与InnoDB存储引擎一起正常工作，因此对此类表使用ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION操作。

在分区表达式中，MySQL 8 中只允许以下列出的 MySQL 函数：

• ABS()：提供给定参数的绝对值
• CEILING()：提供给定参数可能的最小整数
• DAY()：提供给定日期的月份日期
• DAYOFMONTH()：提供与DAY()相同的给定日期的月份日期
• DAYOFWEEK()：提供给定日期的工作日编号
• DAYOFYEAR()：提供给定日期的年度日期
• DATEDIFF()：提供两个给定日期之间的天数
• EXTRACT()：它提供了给定参数的一部分
• FLOOR()：提供给定参数可能的最大整数值
• HOUR()：提供从给定参数开始的小时
• MICROSECOND()：提供给定参数的微秒
• MINUTE()：提供给定参数的分钟数
• MOD()：执行模运算，提供N除以M的余数，其中MOD(N,M)
• MONTH()：提供从给定参数开始的月份
• QUARTER()：提供给定参数的季度
• SECOND()：提供给定参数的第二个参数
• TIME_TO_SEC()：提供给定时间值参数中的第二个
• TO_DAYS()：提供给定参数从 0 年开始的天数
• TO_SECONDS()：提供给定参数从 0 年开始的秒数
• UNIX_TIMESTAMP() (with TIMESTAMP columns)：它提供给定参数自“1970-01-01 00:00:00”UTC 起的秒数
• WEEKDAY()：提供给定参数的工作日索引
• YEAR()：提供给定参数的年份
• YEARWEEK()：提供给定参数的年和周

分区修剪支持 MySQL 8 中的TO_DAYS()、TO_SECONDS()、TO_YEAR()和UNIX_TIMESTAMP()函数。

在本章中，我们学习了不同类型的分区以及分区的需要。我们还介绍了有关管理所有类型分区的详细信息。我们学习了分区修剪和优化器使用的分区选择。我们还讨论了在使用分区时要考虑的适用限制和限制。

在下一章中，您将学习如何在 MySQL 8 中进行扩展，并发现在 MySQL 8 中提供可伸缩性时面临的常见挑战。您还将学习如何使 MySQL 服务器高可用并实现高可用性****