MySQL8 表维护详解

在本章中，我们将介绍以下配方：

维护数据库的一个关键方面是管理表。通常，您需要更改一个大表或克隆一个表。在本章中，您将学习如何管理大型表。由于 MySQL 不支持某些操作，因此使用了一些开源第三方工具。本章还介绍了第三方工具的安装和使用。

Percona Toolkit 是高级开源命令行工具的集合，由 Percona 开发和使用，用于执行各种难以手动执行或复杂的任务。本节将介绍安装。在后面的部分中，您将学习如何使用它。

让我们看看如何在各种操作系统上安装 Percona Toolkit。

1. 下载存储库包：

shell> wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

2. 安装存储库包：

shell> sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb

3. 更新本地包列表：

shell> sudo apt-get update

4. 确保 Percona 软件包可用：

shell> apt-cache search percona

您应该看到类似于以下内容的输出：

percona-xtrabackup-dbg - Debug symbols for Percona XtraBackup
percona-xtrabackup-test - Test suite for Percona XtraBackup
percona-xtradb-cluster-client - Percona XtraDB Cluster database client
percona-xtradb-cluster-server - Percona XtraDB Cluster database server
percona-xtradb-cluster-testsuite - Percona XtraDB Cluster database regression test suite
percona-xtradb-cluster-testsuite-5.5 - Percona Server database test suite
...

5. 安装percona-toolkit包：

shell> sudo apt-get install percona-toolkit

如果不想安装存储库，也可以直接安装：

shell> wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/debian/xenial/x86_64/percona-toolkit_3.0.4-1.xenial_amd64.deb

shell> sudo dpkg -i percona-toolkit_3.0.4-1.yakkety_amd64.deb;
shell> sudo apt-get install -f

1. 安装存储库包：

shell> sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

如果成功，您将看到以下内容：

Installed:
  percona-release.noarch 0:0.1-4

Complete!

2. 确保 Percona 软件包可用：

shell> sudo yum list | grep percona

您应该看到类似于以下内容的输出：

percona-release.noarch                     0.1-4                       @/percona-release-0.1-4.noarch
Percona-Server-55-debuginfo.x86_64         5.5.54-rel38.7.el7          percona-release-x86_64
Percona-Server-56-debuginfo.x86_64         5.6.35-rel81.0.el7          percona-release-x86_64
Percona-Server-57-debuginfo.x86_64         5.7.17-13.1.el7             percona-release-x86_64
...

3. 安装 Percona 工具包：

shell> sudo yum install percona-toolkit

如果不想安装存储库，可以使用 YUM 直接安装：

shell> sudo yum install https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/redhat/7/x86_64/percona-toolkit-3.0.4-1.el7.x86_64.rpm

ALTER TABLE改变表格的结构。例如，您可以添加或删除列、创建或销毁索引、更改现有列的类型或重命名列或表本身。

在执行某些 alter 操作（如更改列数据类型、添加SPATIAL INDEX、删除主键、转换字符集、添加/删除加密等）时，会阻止表上的 DML 操作。如果表很大，则需要花费更多的时间来修改，并且应用程序在此期间无法访问该表，这是不需要的。在这些情况下，pt-online-schema更改是有帮助的，因为 DML 语句是允许的。

alter 操作有两种算法：

• 到位（默认）：不需要复制整表数据
• 复制：将数据复制到临时磁盘文件中并重命名

只有某些 alter 操作可以就地执行。联机 DDL 操作的性能在很大程度上取决于该操作是就地执行，还是需要复制和重建整个表。参见https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-overview.html#innodb-在线 ddl 摘要网格查看可以执行哪些类型的操作，以及避免表复制操作的任何要求。

复制算法的工作原理（摘自参考手册https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

ALTER TABLE未执行的操作到位临时复制原始表格。MySQL 等待修改表的其他操作，然后继续。它将修改合并到副本中，删除原始表，并重命名新表。当ALTER TABLE正在执行时，其他会话可以读取原始表。在ALTER TABLE操作开始后开始的对表的更新和写入被暂停，直到新表准备就绪，然后被自动重定向到新表，而没有任何失败的更新。原始表的临时副本将在新表的数据库目录中创建。对于将表重命名为其他数据库的ALTER TABLE操作，这可能不同于原始表的数据库目录。

要了解 DDL 操作是就地执行还是表复制，请查看命令完成后显示的rows affected值：

• 更改列的默认值（超高速，完全不影响表数据），输出将如下所示：

Query OK, 0 rows affected (0.07 sec)

• 添加一个索引（需要时间，但是0 rows affected显示表没有被复制），输出如下： Query OK, 0 rows affected (21.42 sec)
• 更改列的数据类型（需要大量时间，并且确实需要重建表中的所有行），输出如下：

Query OK, 1671168 rows affected (1 min 35.54 sec)

更改列的数据类型需要重建表中的所有行，但更改VARCHAR大小除外，这可以使用联机ALTER TABLE执行。请参见中提到的使用在线模式更改工具更改表的示例，该示例演示了如何使用pt-online-schema修改列属性。

如果要在employees表中添加新列，可以执行ADD COLUMN语句：

mysql> ALTER TABLE employees ADD COLUMN address varchar(100);
Query OK, 0 rows affected (5.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

您可以看到，受影响的行数为0，这意味着该表没有被复制，操作已经到位。

如果要增加varchar列的长度，可以执行MODIFY COLUMN语句：

mysql> ALTER TABLE employees MODIFY COLUMN address VARCHAR(255);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果您认为varchar(255)不足以存储地址，并且希望将其更改为tinytext，则可以使用MODIFY COLUMN语句。但是，在这种情况下，由于要修改列的数据类型，因此应修改现有表的所有行，这需要表复制，并且 DML 被阻止：

mysql> ALTER TABLE employees MODIFY COLUMN address tinytext;
Query OK, 300025 rows affected (4.36 sec)
Records: 300025  Duplicates: 0  Warnings: 0

您会注意到受影响的行是300025，这是表的大小。

您还可以执行各种其他操作，例如重命名列、更改默认值、重新排列列位置等；参考处的手册 https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-overview.html 了解更多详情。

添加虚拟生成的列只是元数据更改，几乎是即时的：

mysql> ALTER TABLE employees ADD COLUMN full_name VARCHAR(40) AS (CONCAT('first_name', ' ', 'last_name'));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

但是，添加STORED GENERATED列和修改VIRTUAL GENERATED列不在线：

mysql> ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, '-', last_name)) VIRTUAL;
Query OK, 300026 rows affected (4.37 sec)
Records: 300026  Duplicates: 0  Warnings: 0

您可以通过执行RENAME TABLE语句来重命名表。

要使以下插图起作用，请创建示例表和数据库

mysql> CREATE DATABASE prod;
mysql> CREATE TABLE prod.audit_log (id int NOT NULL, msg varchar(64));
mysql> CREATE DATABASE archive;

例如，如果要重命名audit_log表audit_log_archive_2018，可以执行以下操作：

mysql> USE prod;
Database changed

mysql> RENAME TABLE audit_log TO audit_log_archive_2018;
Query OK, 0 rows affected (0.07 sec)

如果要将表从一个数据库移动到另一个数据库，可以使用点表示法指定数据库名称。例如，如果要将audit_log表从名为prod的数据库移动到名为archive的数据库，请执行以下操作：

mysql> USE prod
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> SHOW TABLES;
+------------------------+
| Tables_in_prod         |
+------------------------+
| audit_log_archive_2018 |
+------------------------+
1 row in set (0.00 sec)

mysql> RENAME TABLE audit_log_archive_2018 TO archive.audit_log;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> USE archive
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_archive |
+-------------------+
| audit_log         |
+-------------------+
1 row in set (0.00 sec)

在本节中，您将了解 Percona 的pt-online-schema-change（pt-osc工具，该工具用于在不阻塞 DML 的情况下执行ALTER TABLE操作。

pt-osc随 Percona 工具包一起提供。本章前面已经介绍了 Percona 工具包的安装。

（摘自https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

pt-online-schema-change的工作原理是创建要修改的表的空副本，根据需要对其进行修改，然后将原始表中的行复制到新表中。复制完成后，它将移开原始表，并用新表替换它。默认情况下，它还会删除原始表。

数据复制过程是在小块数据中执行的，这些数据会发生变化，以试图使它们在特定的时间内执行。复制期间对原始表中数据的任何修改都将反映在新表中，因为该工具会在原始表上创建触发器以更新新表中的相应行。使用触发器意味着，如果表中已经定义了任何触发器，则该工具将无法工作。

当工具完成将数据复制到新表中时，它使用原子RENAME TABLE操作同时重命名原始表和新表。完成此操作后，该工具将放下原始表格。

外键使工具的操作复杂化，并引入额外的风险。当外键引用表时，以原子方式重命名原始表和新表的技术不起作用。模式更改完成后，工具必须更新外键以引用新表。该工具支持两种方法来实现这一点。您可以在--alter-foreign-keys-method的文档中了解更多信息。

可以按如下方式修改列数据类型：

shell> pt-online-schema-change D=employees,t=employees,h=localhost -u root --ask-pass --alter="MODIFY COLUMN address VARCHAR(100)" --alter-foreign-keys-method=auto --execute
Enter MySQL password: 
No slaves found.  See --recursion-method if host server1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `employees`.`dept_emp` (approx. 331143 rows)
  `employees`.`titles` (approx. 442605 rows)
  `employees`.`salaries` (approx. 2838426 rows)
  `employees`.`dept_manager` (approx. 24 rows)
Will automatically choose the method to update foreign keys.
Altering `employees`.`employees`...
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
2017-09-24T09:56:49 Creating triggers...
2017-09-24T09:56:49 Created triggers OK.
2017-09-24T09:56:49 Copying approximately 299478 rows...
2017-09-24T09:56:56 Copied rows OK.
2017-09-24T09:56:56 Max rows for the rebuild_constraints method: 88074
Determining the method to update foreign keys...
2017-09-24T09:56:56   `employees`.`dept_emp`: too many rows: 331143; must use drop_swap
2017-09-24T09:56:56 Drop-swapping tables...
2017-09-24T09:56:56 Analyzing new table...
2017-09-24T09:56:56 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2017-09-24T09:56:56 Dropping triggers...
2017-09-24T09:56:56 Dropped triggers OK.
Successfully altered `employees`.`employees`.

您会注意到，该工具创建了一个具有修改结构的新表，在表上创建了触发器，将行复制到新表，最后重命名了新表。

如果要修改已经有触发器的salaries表，需要指定--preserver-triggers选项，否则会出现错误：The tableemployees.salarieshas triggers but --preserve-triggers was not specified.：

shell> pt-online-schema-change D=employees,t=salaries,h=localhost -u user --ask-pass --alter="MODIFY COLUMN salary int" --alter-foreign-keys-method=auto --execute --no-drop-old-table --preserve-triggers 
No slaves found.  See --recursion-method if host server1 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
No foreign keys reference `employees`.`salaries`; ignoring --alter-foreign-keys-method.
Altering `employees`.`salaries`...
Creating new table...
Created new table employees._salaries_new OK.
Altering new table...
Altered `employees`.`_salaries_new` OK.
2017-09-24T11:11:58 Creating triggers...
2017-09-24T11:11:58 Created triggers OK.
2017-09-24T11:11:58 Copying approximately 2838045 rows...
2017-09-24T11:12:20 Copied rows OK.
2017-09-24T11:12:20 Adding original triggers to new table.
2017-09-24T11:12:21 Analyzing new table...
2017-09-24T11:12:21 Swapping tables...
2017-09-24T11:12:21 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2017-09-24T11:12:21 Dropping triggers...
2017-09-24T11:12:21 Dropped triggers OK.
Successfully altered `employees`.`salaries`

如果服务器有从属服务器，此工具可以在从现有表复制到新表时创建从属滞后。为了避免这种情况，您可以指定--check-slave-lag（默认启用）；它暂停数据复制，直到该副本的延迟小于--max-lag，默认情况下为 1 秒。您可以通过传递--max-lag选项来指定--max-lag。

如果要确保从机的滞后时间不会超过 10 秒，请通过--max-lag=10：

shell> pt-online-schema-change D=employees,t=employees,h=localhost -u user --ask-pass --alter="MODIFY COLUMN address VARCHAR(100)" --alter-foreign-keys-method=auto --execute --preserve-triggers --max-lag=10
Enter MySQL password: 
Found 1 slaves:
server2 -> xx.xxx.xxx.xx:socket
Will check slave lag on:
server2 -> xx.xxx.xxx.xx:socket
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `employees`.`dept_emp` (approx. 331143 rows)
  `employees`.`titles` (approx. 442605 rows)
  `employees`.`salaries` (approx. 2838426 rows)
  `employees`.`dept_manager` (approx. 24 rows)
Will automatically choose the method to update foreign keys.
Altering `employees`.`employees`...
Creating new table...
Created new table employees._employees_new OK.
Waiting forever for new table `employees`.`_employees_new` to replicate to ubuntu...
Altering new table...
Altered `employees`.`_employees_new` OK.
2017-09-24T12:00:58 Creating triggers...
2017-09-24T12:00:58 Created triggers OK.
2017-09-24T12:00:58 Copying approximately 299342 rows...
2017-09-24T12:01:05 Copied rows OK.
2017-09-24T12:01:05 Max rows for the rebuild_constraints method: 86446
Determining the method to update foreign keys...
2017-09-24T12:01:05   `employees`.`dept_emp`: too many rows: 331143; must use drop_swap
2017-09-24T12:01:05 Skipping triggers creation since --no-swap-tables was specified along with --drop-new-table
2017-09-24T12:01:05 Drop-swapping tables...
2017-09-24T12:01:05 Analyzing new table...
2017-09-24T12:01:05 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2017-09-24T12:01:05 Dropping triggers...
2017-09-24T12:01:05 Dropped triggers OK.
Successfully altered `employees`.`employees`.

有关更多详细信息和选项，请参阅 Percona 文档，位于https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html 。

pt-online-schema-change works only when there is a primary key or unique key, otherwise it will fail with the following error:

The new table `employees`.`_employees_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

因此，如果该表没有唯一键，则不能使用pt-online-schema-change。

有时，您不希望保留较旧的数据，而是希望将其删除。如果要删除一个多月前最后一次访问的所有行，如果表很小（<10k 行），可以直接使用以下命令：

DELETE FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH)

如果桌子很大怎么办？您知道InnoDB创建UNDO日志来恢复失败的事务。因此，所有被删除的行都保存在UNDO日志空间中，以便在DELETE语句中途中止时用于恢复。不幸的是，如果DELETE语句在这段时间内被中止，InnoDB会将UNDO日志空间中的行复制到表中，这会使表无法访问。

为了克服这种行为，您可以LIMIT删除的行数和COMMIT事务，在循环中运行相同的操作，直到删除所有不需要的行。

这是一个示例伪代码：

WHILE count<=0:
    DELETE FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH) LIMIT 10000;
    count=SELECT COUNT(*) FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH);

如果last_accessed上没有INDEX，则可以锁定该表。在这种情况下，您需要找到已删除行的主键，并根据PRIMARY KEY进行删除。

这是伪码，假设id是PRIMARY KEY：

WHILE count<=0:
    SELECT id FROM <TABLE> WHERE last_accessed < DATE_ADD(NOW(), INTERVAL -1 MONTH) LIMIT 10000;
    DELETE FROM <TABLE> WHERE id IN ('ids from above statement');
    count=SELECT COUNT(*) FROM <TABLE> WHERE     last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH);

您可以使用 Percona 的pt-archiver工具，而不是编写用于删除行的代码，该工具基本上也可以这样做，并提供许多其他选项，例如将行保存到另一个表或文件中，对加载和复制延迟进行精细控制，等等。

pt-archiver中有很多选项，我们将从简单的吹扫开始。

如果要从employees表中删除hire_date超过 30 年的所有行，可以执行以下操作：

shell> pt-archiver --source h=localhost,D=employees,t=employees -u <user> -p<pass> --where="hire_date<DATE_ADD(NOW(), INTERVAL -30 YEAR)" --no-check-charset --limit 10000 --commit-each

您可以通过--source选项传递主机名、数据库名和表名。您可以使用--limit选项限制批量删除的行数。

如果指定--progress，则输出为标题行，加上每隔一段时间的状态输出。状态输出中的每一行都列出了当前日期和时间、pt-archiver已运行的秒数以及已存档的行数。

如果您指定了--statistics，则pt-archiver会输出时间和其他信息，以帮助您确定归档过程中哪一部分花费的时间最多。

如果您指定--check-slave-lag，工具将暂停存档，直到从属延迟小于--max-lag。

如果要将删除后的行保存到单独的表或文件中，可以指定--dest选项。

假设要将employees表的所有行从employees数据库移动到employees_archive表，可以执行以下操作：

shell> pt-archiver --source h=localhost,D=employees,t=employees --dest h=localhost,D=employees_archive -u <user> -p<pass> --where="1=1" --no-check-charset --limit 10000 --commit-each

如果指定--where="1=1"，则复制所有行。

如果要将数据从一个表复制到另一个表，可以使用mysqldump或mysqlpump备份某些行，然后将它们加载到目标表中。作为替代，您也可以使用pt-archive。如果指定了--no-delete选项，pt-archiver将不会从源中删除行：

shell> pt-archiver --source h=localhost,D=employees,t=employees --dest h=localhost,D=employees_archive -u <user> -p<pass> --where="1=1" --no-check-charset --limit 10000 --commit-each --no-delete

参见https://www.percona.com/doc/percona-toolkit/LATEST/pt-archiver.html 了解pt-archiver的更多详情和选项。

如果要克隆表，有许多选项。

1. 使用INSERT INTO SELECT语句：

mysql> CREATE TABLE employees_clone LIKE employees;
mysql> INSERT INTO employees_clone SELECT * FROM employees;

注意，如果有任何生成的列，上述语句将不起作用。在这种情况下，您应该给出完整的 insert 语句，不包括生成的列。

mysql> INSERT INTO employees_clone SELECT * FROM employees;
ERROR 3105 (HY000): The value specified for generated column 'hire_date_year' in table 'employees_clone' is not allowed.

mysql> INSERT INTO employees_clone(emp_no, birth_date, first_name, last_name, gender, hire_date) SELECT emp_no, birth_date, first_name, last_name, gender, hire_date FROM employees;
Query OK, 300024 rows affected (3.21 sec)
Records: 300024  Duplicates: 0  Warnings: 0

But the preceding statement is very slow and dangerous on big tables. Remember, if the statement fails, to restore the table state, InnoDB saves all the rows in UNDO logs.

2. 使用mysqldump或mysqlpump对单个表进行备份，并将其恢复到目的地。如果桌子很大，这可能需要很长时间。
3. 使用Innobackupex对特定表进行备份，并将数据文件恢复到目的地。
4. 使用pt-archiver和--no-delete选项，将所需行或所有行复制到目标表。

您还可以使用可传输表空间来克隆表，这在第 11 章的管理表空间的将每个表空间的文件复制到另一个实例部分中进行了解释。

您可以使用分区跨文件系统分发各个表的部分。完成数据分割的用户选择的规则称为分区函数，它可以是模数、针对一组范围或值列表的简单匹配、内部哈希函数或线性哈希函数。

表的不同行可以分配给不同的物理分区，这称为水平分区。MySQL 不支持垂直分区，在垂直分区中，表的不同列被分配给不同的物理分区。

划分表的方法有很多：

• RANGE：这种类型的分区根据给定范围内的列值将行分配给分区。
• LIST：类似于RANGE的分区，只是分区是基于匹配一组离散值之一的列来选择的。
• HASH：对于这种类型的分区，根据用户定义的表达式返回的值选择分区，该表达式对要插入到表中的行中的列值进行操作。该函数可以由 MySQL 中生成非负整数值的任何有效表达式组成。
• KEY：这种类型的分区类似于HASH的分区，只是只提供了一个或多个要评估的列，MySQL 服务器提供了自己的哈希功能。这些列可以包含除整数值以外的其他值，因为 MySQL 提供的哈希函数可以保证整数结果，而不管列数据类型如何。

前面的每个分区类型都有一个扩展名。RANGE有RANGE COLUMNS、LIST有LIST COLUMNS、HASH有LINEAR HASH、KEY有LINEAR KEY。

对于[LINEAR] KEY、RANGE COLUMNS和LIST COLUMNS分区，分区表达式由一个或多个列的列表组成。

在RANGE、LIST和[LINEAR] HASH分区的情况下，分区列的值被传递给分区函数，该函数返回一个整数值，该整数值表示该特定记录应存储在其中的分区的编号。此函数必须是非常量和非随机函数。

数据库分区的一个非常常见的用途是按日期分隔数据。

参见https://dev.mysql.com/doc/refman/8.0/en/partitioning-overview.html 了解分区的优点和其他细节。

请注意，分区仅适用于InnoDB表，并且外键还不支持与分区结合使用。

您可以在创建表时指定分区，也可以通过执行ALTER TABLE命令来指定分区。分区列应该是表中所有唯一键的一部分。

如果您基于created_at列定义了分区，并且id是主键，那么您应该将create_at列作为PRIMARY KEY的一部分，即（id、created_at）。

下面的示例假定表中没有引用外键。

如果您希望在 MySQL 8.0 中实现基于范围或时间间隔的分区方案，您有两个选项：

• 通过RANGE对表进行分区，对于分区表达式，使用对DATE、TIME或DATETIME列进行操作并返回整数值的函数
• 通过RANGE COLUMNS对表进行分区，使用DATE或DATETIME列作为分区列

如果您希望基于emp_no对employees表进行分区，并且希望在一个分区中保留 100000 名员工，您可以这样创建它：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (emp_no)
(PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500000) ENGINE = InnoDB);

因此，所有emp_no小于 100000 的员工将进入分区p0，所有emp_no小于200000且大于100000的员工将进入分区p1，以此类推。

如果员工编号在500000以上，由于没有为他们定义分区，插入将失败并出错。为了避免这种情况，您必须定期检查并添加分区或创建一个MAXVALUE分区来捕获所有此类异常：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (emp_no)
(PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

如果要基于hire_date进行分区，可以使用YEAR(hire_date)函数作为分区表达式：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(hire_date))
(PARTITION p1980 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1990 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

MySQL 中的分区广泛应用于date、datetime或timestamp列。如果您想在数据库中存储一些事件，并且所有查询都基于一个时间范围之外的时间范围，那么可以像这样使用分区。

分区函数to_days()返回自0000-01-01起的天数，为整数：

mysql> CREATE TABLE `event_history` (
  `event_id` int(11) NOT NULL,
  `event_name` varchar(10) NOT NULL,
  `created_at` datetime NOT NULL,
  `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `event_type` varchar(10) NOT NULL,
  `msg` tinytext NOT NULL,
  PRIMARY KEY (`event_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (to_days(created_at))
(PARTITION p20170930 VALUES LESS THAN (736967) ENGINE = InnoDB,
PARTITION p20171001 VALUES LESS THAN (736968) ENGINE = InnoDB,
PARTITION p20171002 VALUES LESS THAN (736969) ENGINE = InnoDB,
PARTITION p20171003 VALUES LESS THAN (736970) ENGINE = InnoDB,
PARTITION p20171004 VALUES LESS THAN (736971) ENGINE = InnoDB,
PARTITION p20171005 VALUES LESS THAN (736972) ENGINE = InnoDB,
PARTITION p20171006 VALUES LESS THAN (736973) ENGINE = InnoDB,
PARTITION p20171007 VALUES LESS THAN (736974) ENGINE = InnoDB,
PARTITION p20171008 VALUES LESS THAN (736975) ENGINE = InnoDB,
PARTITION p20171009 VALUES LESS THAN (736976) ENGINE = InnoDB,
PARTITION p20171010 VALUES LESS THAN (736977) ENGINE = InnoDB,
PARTITION p20171011 VALUES LESS THAN (736978) ENGINE = InnoDB,
PARTITION p20171012 VALUES LESS THAN (736979) ENGINE = InnoDB,
PARTITION p20171013 VALUES LESS THAN (736980) ENGINE = InnoDB,
PARTITION p20171014 VALUES LESS THAN (736981) ENGINE = InnoDB,
PARTITION p20171015 VALUES LESS THAN (736982) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

如果要将现有表转换为分区表，并且分区键不是PRIMARY KEY的一部分，则需要删除PRIMARY KEY并将分区键添加为PRIMARY KEY的一部分以及所有唯一键。否则，您将得到错误ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function.。您可以按如下方式执行此操作：

mysql> ALTER TABLE employees DROP PRIMARY KEY, ADD PRIMARY KEY(emp_no,hire_date);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees PARTITION BY RANGE (YEAR(hire_date))
        (PARTITION p1980 VALUES LESS THAN (1980) ENGINE = InnoDB,
        PARTITION p1990 VALUES LESS THAN (1990) ENGINE = InnoDB,
        PARTITION p2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
        PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
        PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
        PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
       );
Query OK, 300025 rows affected (4.71 sec)
Records: 300025  Duplicates: 0  Warnings: 0

有关RANGE分区的更多详细信息，请参阅https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.html 。

如果要删除分区，可以执行REMOVE PARTITIONING语句：

mysql> ALTER TABLE employees REMOVE PARTITIONING;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

RANGE COLUMNS分区类似于RANGE分区，但允许您使用基于多个列值的范围定义分区。此外，可以使用整型以外的类型列定义范围。RANGE COLUMNS分区与RANGE分区的显著区别在于：

• RANGE COLUMNS不接受表达式，只接受列的名称
• RANGE COLUMNS接受一个或多个列的列表
• RANGE COLUMNS分区列不限于整数列；字符串、DATE和DATETIME列也可以用作分区列

不使用to_days()或year()功能，您可以直接使用RANGE COLUMNS中的hire_date列：

mysql> ALTER TABLE employees 
    PARTITION BY RANGE COLUMNS (hire_date) 
    (PARTITION p0 VALUES LESS THAN ('1970-01-01'),
     PARTITION p1 VALUES LESS THAN ('1980-01-01'),
     PARTITION p2 VALUES LESS THAN ('1990-01-01'),
     PARTITION p3 VALUES LESS THAN ('2000-01-01'),
     PARTITION p4 VALUES LESS THAN ('2010-01-01'),
     PARTITION p5 VALUES LESS THAN (MAXVALUE)
    );
Query OK, 300025 rows affected (4.71 sec)
Records: 300025  Duplicates: 0  Warnings: 0

也可以根据员工的last_name进行划分。这将不能保证分区之间的均匀分布：

mysql> ALTER TABLE employees 
PARTITION BY RANGE COLUMNS (last_name) 
    (PARTITION p0 VALUES LESS THAN ('b'),
     PARTITION p1 VALUES LESS THAN ('f'),
     PARTITION p2 VALUES LESS THAN ('l'),
     PARTITION p3 VALUES LESS THAN ('q'),
     PARTITION p4 VALUES LESS THAN ('u'),
     PARTITION p5 VALUES LESS THAN ('z')
  );
Query OK, 300025 rows affected (4.71 sec)
Records: 300025  Duplicates: 0  Warnings: 0

使用RANGE COLUMNS可以在分区函数中放置多列：

mysql> CREATE TABLE range_columns_example (
    a INT,
    b INT,
    c INT,
    d INT,
    e INT,
    PRIMARY KEY(a, b, c)
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,50,100),
    PARTITION p2 VALUES LESS THAN (10,100,200),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

如果插入值a=10、b=20、c=100、d=100、e=100，则会转到p1。在设计由RANGE COLUMNS分区的表时，您始终可以通过使用mysql客户端比较所需的元组来测试连续的分区定义，如下所示：

mysql> SELECT (10,20,100) < (0,25,50) p0, (10,20,100) < (10,50,100) p1, (10,20,100) < (10,100,200) p2;
+----+----+----+
| p0 | p1 | p2 |
+----+----+----+
|  0 |  1 |  1 |
+----+----+----+
1 row in set (0.00 sec)

在这种情况下，插入到p1。

LIST分区类似于RANGE分区，其中每个分区都是基于一组值列表中的一个列值的成员身份来定义和选择的，而不是在一组连续的值范围中的一个列值。

需要通过PARTITION BY LIST(<expr>)定义，其中expr为列值或基于列值并返回整数值的表达式。

分区定义包含VALUES IN (<value_list>)，其中value_list是逗号分隔的整数列表，而不是VALUES LESS THAN (<value>)。

如果您希望使用整数以外的数据类型，可以使用LIST COLUMNS。

与RANGE分区不同，没有MAXVALUE等catch-all；PARTITION表达式中应包含分区表达式的所有预期值。

假设有一个带有邮政编码和城市的 customer 表。例如，如果您想在分区中划分具有特定邮政编码的客户，您可以使用LIST分区：

mysql> CREATE TABLE customer (
customer_id INT,
zipcode INT,
city varchar(100),
PRIMARY KEY (customer_id, zipcode)
)
PARTITION BY LIST(zipcode) (
   PARTITION pnorth VALUES IN (560030, 560007, 560051, 560084),
   PARTITION peast VALUES IN (560040, 560008, 560061, 560085),
   PARTITION pwest VALUES IN (560050, 560009, 560062, 560086),
   PARTITION pcentral VALUES IN (560060, 560010, 560063, 560087)
);

如果希望直接使用列而不是整数，可以使用LIST COLUMNS：

mysql> CREATE TABLE customer (
customer_id INT,
zipcode INT,
city varchar(100),
PRIMARY KEY (customer_id, city)
)
PARTITION BY LIST COLUMNS(city) (
   PARTITION pnorth VALUES IN ('city1','city2','city3'),
   PARTITION peast VALUES IN ('city4','city5','city6'),
   PARTITION pwest VALUES IN ('city7','city8','city9'),
   PARTITION pcentral VALUES IN ('city10','city11','city12')
);

HASH分区主要用于确保数据在预定数量的分区之间均匀分布。对于范围分区或列表分区，必须明确指定给定列值或列值集应存储在哪个分区中；使用散列分区，您可以自行决定，只需根据要散列的列值和分区表要划分的分区数指定列值或表达式。

如果您想平均分配员工，可以使用HASH of YEAR(hire_date)并指定分区数量，而不是YEAR(hire_date)上的RANGE分区。使用PARTITION BY HASH时，存储引擎根据表达式结果的模数确定使用哪个分区。

例如，如果hire_date为1987-11-28，则YEAR(hire_date)为1987，而MOD(1987,8)为3。所以这一行转到第三个分区：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(YEAR(hire_date))
PARTITIONS 8;

最有效的散列函数是对单个表列进行操作的函数，其值与列值一致地增减。

在LINEAR HASH分区中，除了添加LINEAR关键字外，您可以使用相同的语法。MySQL 不使用MODULUS操作，而是使用二次幂算法来确定分区。参见https://dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html 欲了解更多详情：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LINEAR HASH(YEAR(hire_date))
PARTITIONS 8;

按键分区与按散列分区类似，不同之处在于，在散列分区使用用户定义的表达式的情况下，用于键分区的散列函数由 MySQL 服务器提供。此内部哈希函数基于与PASSWORD()函数相同的算法。

KEY只获取零个或多个列名的列表。任何用作分区键的列都必须包含表主键的一部分或全部（如果表有主键）。如果没有将列名指定为分区键，则使用表的主键（如果有）：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY()
PARTITIONS 8;

您可以进一步将每个分区划分为一个分区表。这称为子分区或复合分区：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE( YEAR(hire_date) )
  SUBPARTITION BY HASH(emp_no)
    SUBPARTITIONS 4 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN (2010),
        PARTITION p3 VALUES LESS THAN (2020),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );

MySQL 不会扫描没有匹配值的分区；这是自动的，称为分区修剪。MySQL 优化器计算给定值的分区表达式，确定哪个分区包含该值，并仅扫描该分区。

SELECT、DELETE和UPDATE语句支持分区修剪。INSERT当前无法删除语句。

您还可以显式地为匹配给定WHERE条件的行指定分区和子分区。

分区修剪仅适用于查询，但查询和许多 DML 语句都支持显式选择分区。

以employees表为例，根据emp_no进行分区：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(hire_date))
(PARTITION p1980 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1990 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

假设执行以下SELECT查询：

mysql> SELECT last_name,birth_date FROM employees WHERE hire_date='1999-02-01' AND first_name='Mariangiola';

MySQL 优化器检测到查询中正在使用分区列，并自动确定要扫描的分区。

在这个查询中，它首先计算YEAR('1999-02-01')，即1999，并扫描p2000分区，而不是整个表。这大大缩短了查询时间。

如果给定了一个范围（例如hire_date>='1999-02-01'），则扫描分区p2000、p2010、p2020和pmax，而不是hire_date='1999-02-01'。

如果WHERE子句中没有给出表达式hire_date='1999-02-01'，MySQL 必须扫描整个表。

要知道优化器扫描哪些分区，可以执行查询的EXPLAIN计划，这在第 13 章性能调优中的解释计划部分进行了说明：

mysql> EXPLAIN SELECT last_name,birth_date FROM employees WHERE hire_date='1999-02-01' AND first_name='Mariangiola'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
 partitions: p2000
         type: ref
possible_keys: name
          key: name
      key_len: 58
          ref: const
         rows: 120
     filtered: 10.00
        Extra: Using index condition

mysql> EXPLAIN SELECT last_name,birth_date FROM employees WHERE hire_date>='1999-02-01' AND first_name='Mariangiola'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
 partitions: p2000,p2010,p2020,pmax
         type: ref
possible_keys: name
          key: name
      key_len: 58
          ref: const
         rows: 121
     filtered: 33.33
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

分区修剪是基于WHERE子句的自动选择。可以在查询中显式指定要扫描的分区。查询可以是SELECT、DELETE、INSERT、REPLACE、UPDATE、LOAD DATA和LOAD XML。PARTITION选项用于从给定的表中选择分区，您应该在表名称后面紧接着指定关键字PARTITION<分区名称>，然后再指定所有其他选项，包括任何表别名，例如：

mysql> SELECT emp_no,hire_date FROM employees PARTITION (p1990) LIMIT 10;
+--------+------------+
| emp_no | hire_date  |
+--------+------------+
| 413688 | 1989-12-10 |
| 242368 | 1989-08-06 |
| 283280 | 1985-11-22 |
| 405098 | 1985-11-16 |
|  30404 | 1985-07-17 |
| 419259 | 1988-03-21 |
| 466254 | 1986-11-28 |
| 428971 | 1986-12-13 |
|  94467 | 1987-01-28 |
| 259555 | 1987-07-30 |
+--------+------------+
10 rows in set (0.00 sec)

同样，我们可以删除：

mysql> DELETE FROM employees PARTITION (p1980, p1990) WHERE first_name LIKE 'j%';
Query OK, 7001 rows affected (0.12 sec)

在管理分区时，最重要的是提前为基于时间的RANGE分区添加足够的分区。否则会导致插入时出错，或者，如果定义了MAXVALUE分区，则所有插入都会进入MAXVALUE分区。例如，以没有pmax分区的event_history表为例：

mysql> CREATE TABLE `event_history` (
  `event_id` int(11) NOT NULL,
  `event_name` date NOT NULL,
  `created_at` datetime NOT NULL,
  `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `event_type` varchar(10) NOT NULL,
  `msg` tinytext NOT NULL,
  PRIMARY KEY (`event_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (to_days(created_at))
(PARTITION p20170930 VALUES LESS THAN (736967) ENGINE = InnoDB,
PARTITION p20171001 VALUES LESS THAN (736968) ENGINE = InnoDB,
PARTITION p20171002 VALUES LESS THAN (736969) ENGINE = InnoDB,
PARTITION p20171003 VALUES LESS THAN (736970) ENGINE = InnoDB,
PARTITION p20171004 VALUES LESS THAN (736971) ENGINE = InnoDB,
PARTITION p20171005 VALUES LESS THAN (736972) ENGINE = InnoDB,
PARTITION p20171006 VALUES LESS THAN (736973) ENGINE = InnoDB,
PARTITION p20171007 VALUES LESS THAN (736974) ENGINE = InnoDB,
PARTITION p20171008 VALUES LESS THAN (736975) ENGINE = InnoDB,
PARTITION p20171009 VALUES LESS THAN (736976) ENGINE = InnoDB,
PARTITION p20171010 VALUES LESS THAN (736977) ENGINE = InnoDB,
PARTITION p20171011 VALUES LESS THAN (736978) ENGINE = InnoDB,
PARTITION p20171012 VALUES LESS THAN (736979) ENGINE = InnoDB,
PARTITION p20171013 VALUES LESS THAN (736980) ENGINE = InnoDB,
PARTITION p20171014 VALUES LESS THAN (736981) ENGINE = InnoDB,
PARTITION p20171015 VALUES LESS THAN (736982) ENGINE = InnoDB
);

本表接受INSERTS至 2017 年 10 月 15 日；之后，INSERTS失败。

另一个重要的事情是在数据通过保留后对其进行处理。

要执行这些操作，您需要执行ALTER命令。

要添加新分区，请执行ADD PARTITION (<PARTITION DEFINITION>)语句：

mysql> ALTER TABLE event_history ADD PARTITION (
PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB,
PARTITION p20171017 VALUES LESS THAN (736984) ENGINE = InnoDB
);

此语句在很短的时间内锁定整个表。

如果存在MAXVALUE分区，则不能在MAXVALUE之后添加分区；在这种情况下，您需要将REORGANIZE MAXVALUE分区分为两个分区：

mysql> ALTER TABLE event_history REORGANIZE PARTITION pmax INTO (PARTITION p20171016 VALUES LESS THAN (736983) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

请记住，MySQL 在重新组织分区时必须大量移动数据，在此期间表将被锁定。

您还可以将多个分区重新组织为单个分区：

mysql> ALTER TABLE event_history REORGANIZE PARTITION p20171001,p20171002,p20171003,p20171004,p20171005,p20171006,p20171007 
INTO (PARTITION p2017_oct_week1 VALUES LESS THAN (736974));

如果数据跨越了保留区，您可以DROP整个分区，这比传统的DELETE FROM TABLE语句速度更快。这对于高效地归档数据非常有帮助。

如果p20170930已经越过了保留，您可以使用ALTER TABLE ... DROP PARTITION语句DROP分区：

mysql> ALTER TABLE event_history DROP PARTITION p20170930;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除分区会从表中删除PARTITION DEFINITION。

如果您希望在表中保留PARTITION DEFINITION并且只删除数据，您可以执行TRUNCATE PARTITION命令：

mysql> ALTER TABLE event_history TRUNCATE PARTITION p20171001;
Query OK, 0 rows affected (0.08 sec)

在HASH和KEY分区上执行的操作非常不同。您只能减少或增加分区的数量。

假设employees表是基于HASH进行分区的：

mysql> CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(YEAR(hire_date))
PARTITIONS 8;

要将分区从8减少到6，可以执行COALESCE PARTITION语句并指定要减少的分区数，即8-6=2：

mysql> ALTER TABLE employees COALESCE PARTITION 2;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

要将分区从6增加到16，可以执行ADD PARTITION语句并指定要增加的分区数，即16-6=10：

mysql> ALTER TABLE employees ADD PARTITION PARTITIONS 10;
Query OK, 0 rows affected (5.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

您还可以针对特定分区执行其他操作，例如REBUILD、OPTIMIZE、ANALYZE和REPAIR语句，例如：

mysql> ALTER TABLE event_history REPAIR PARTITION p20171009, p20171010;

本节讨论获取有关现有分区的信息，这可以通过多种方式完成。

让我们进入细节。

要知道一个表是否被分区，可以执行SHOW CREATE TABLE\G语句，该语句显示表定义以及分区，例如：

mysql> SHOW CREATE TABLE employees \G
*************************** 1\. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (YEAR(hire_date))
(PARTITION p1980 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1990 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

您可以执行SHOW TABLE STATUS命令并在输出中检查Create_options：

mysql> SHOW TABLE STATUS LIKE 'employees'\G
*************************** 1\. row ***************************
           Name: employees
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2017-10-01 05:01:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: partitioned
        Comment: 
1 row in set (0.00 sec)

EXPLAIN计划显示为查询扫描的所有分区。如果您为SELECT * FROM <table>运行EXPLAIN计划，它会列出所有分区，例如：

mysql> EXPLAIN SELECT * FROM employees\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
 partitions: p1980,p1990,p2000,p2010,p2020,pmax
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 292695
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

与前面的所有方法相比，INFORMATION_SCHEMA.PARTITIONS提供了更多关于分区的信息：

mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.PARTITIONS\G
*************************** 1\. row ***************************
       Table: PARTITIONS
Create Table: CREATE TEMPORARY TABLE `PARTITIONS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `PARTITION_NAME` varchar(64) DEFAULT NULL,
  `SUBPARTITION_NAME` varchar(64) DEFAULT NULL,
  `PARTITION_ORDINAL_POSITION` bigint(21) unsigned DEFAULT NULL,
  `SUBPARTITION_ORDINAL_POSITION` bigint(21) unsigned DEFAULT NULL,
  `PARTITION_METHOD` varchar(18) DEFAULT NULL,
  `SUBPARTITION_METHOD` varchar(12) DEFAULT NULL,
  `PARTITION_EXPRESSION` longtext,
  `SUBPARTITION_EXPRESSION` longtext,
  `PARTITION_DESCRIPTION` longtext,
  `TABLE_ROWS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `AVG_ROW_LENGTH` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATA_LENGTH` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATA_FREE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `PARTITION_COMMENT` varchar(80) NOT NULL DEFAULT '',
  `NODEGROUP` varchar(12) NOT NULL DEFAULT '',
  `TABLESPACE_NAME` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

要了解更多关于表分区的详细信息，可以通过TABLE_SCHEMA指定数据库名称，通过TABLE_NAME指定表名称来查询INFORMATION_SCHEMA.PARTITIONS表，例如：

mysql> SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='employees';
+----------------+
| PARTITION_NAME |
+----------------+
| p1980          |
| p1990          |
| p2000          |
| p2010          |
| p2020          |
| pmax           |
+----------------+
6 rows in set (0.00 sec)

您可以在该分区中获得诸如PARTITION_METHOD、PARTITION_EXPRESSION、PARTITION_DESCRIPTION和TABLE_ROWS等详细信息：

mysql> SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='employees' AND PARTITION_NAME='p1990'\G
*************************** 1\. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: employees
                   TABLE_NAME: employees
               PARTITION_NAME: p1990
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: YEAR(hire_date)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 1990
                   TABLE_ROWS: 157588
               AVG_ROW_LENGTH: 56
                  DATA_LENGTH: 8929280
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: 8929280
                    DATA_FREE: 0
                  CREATE_TIME: NULL
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
1 row in set (0.00 sec)

更多详细信息，请参考https://dev.mysql.com/doc/refman/8.0/en/partitions-table.html 。

RANGE COLUMNS在管理生存时间和软删除行方面非常有用。假设您有一个应用程序，它指定了行的到期时间（该行在超过到期时间后将被删除），并且到期时间是可变的。

假设应用程序可以执行以下类型的插入：

• 插入持久数据
• 插入过期的

如果到期时间是常数，即插入的所有行在一定时间后将被删除，则可以使用范围分区。但是如果到期日是不同的，即一些行将在一周内删除，一些行将在一个月内删除，一些行将在一年内删除，而一些行没有到期日，则无法创建分区。在这种情况下，您可以使用下面解释的RANGE COLUMNS分区。

我们引入一个名为soft_delete的列，它将由触发器设置。soft_delete列将是范围列分区的一部分。

分区将类似于（soft_delete，过期）。soft_delete和 expires 一起控制一行应该去哪个分区。“软删除”列决定行的保留。如果 expires 为 0，则触发器将soft_delete值设置为 0，将该行放入no_retention分区；如果 expires 的值超出分区边界，则触发器将soft_delete值设置为 1，该行将放入long_retention分区。如果 expires 的值在分区边界内，触发器将soft_delete值设置为2。根据 expires 的值，该行将放在各自的分区中。

总之，soft_delete将是：

• 0：如果 expires 的值为 0
• 1：如果过期时间距时间戳超过 30 天
• 2：如果到期时间与时间戳的距离小于或等于 30 天

我们创造

• 1no_retention分区（soft_delete = 0
• 1long_retention分区（soft_delete = 1
• 8 个日常分区（soft_delete = 2）

您可以创建如下表：

mysql> CREATE TABLE `customer_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg` text,
  `timestamp` bigint(20) NOT NULL DEFAULT '0',
  `expires` bigint(20) NOT NULL DEFAULT '0',
  `soft_delete` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`,`expires`,`soft_delete`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(soft_delete,expires)
(PARTITION no_retention VALUES LESS THAN (0,MAXVALUE) ENGINE = InnoDB,
 PARTITION long_retention VALUES LESS THAN (1,MAXVALUE) ENGINE = InnoDB,
 PARTITION pd20171017 VALUES LESS THAN (2,1508198400000) ENGINE = InnoDB,
 PARTITION pd20171018 VALUES LESS THAN (2,1508284800000) ENGINE = InnoDB,
 PARTITION pd20171019 VALUES LESS THAN (2,1508371200000) ENGINE = InnoDB,
 PARTITION pd20171020 VALUES LESS THAN (2,1508457600000) ENGINE = InnoDB,
 PARTITION pd20171021 VALUES LESS THAN (2,1508544000000) ENGINE = InnoDB,
 PARTITION pd20171022 VALUES LESS THAN (2,1508630400000) ENGINE = InnoDB,
 PARTITION pd20171023 VALUES LESS THAN (2,1508716800000) ENGINE = InnoDB,
 PARTITION pd20171024 VALUES LESS THAN (3,1508803200000) ENGINE = InnoDB,
 PARTITION pd20171025 VALUES LESS THAN (3,1508869800000) ENGINE = InnoDB,
 PARTITION pd20171026 VALUES LESS THAN (3,1508956200000) ENGINE = InnoDB) */;

将有一个缓冲区每周分区，这将是 42 天以后，将始终是空的，以便我们可以分裂和 7+2 个每天分区与 2 个缓冲区。

mysql> DROP TRIGGER IF EXISTS customer_data_insert;
DELIMITER $$
CREATE TRIGGER customer_data_insert
BEFORE INSERT
   ON customer_data FOR EACH ROW
BEGIN
    SET NEW.soft_delete = (IF((NEW.expires = 0),0,IF((ROUND((((((NEW.expires - NEW.timestamp) / 1000) / 60) / 60) / 24),0) <= 7),2,1)));
END;
$$
DELIMITER ;

mysql> DROP TRIGGER IF EXISTS customer_data_update;
DELIMITER $$
CREATE TRIGGER customer_data_update
BEFORE UPDATE
   ON customer_data FOR EACH ROW
BEGIN
    SET NEW.soft_delete = (IF((NEW.expires = 0),0,IF((ROUND((((((NEW.expires - NEW.timestamp) / 1000) / 60) / 60) / 24),0) <= 7),2,1)));
END;
$$
DELIMITER ;

• 假设客户端插入一行，时间戳为 1508265000（2017-10-17 18:30:00），到期值为 1508351400（2017-10-18 18:30:00），则软删除将为 2，这将使其进入分区 pd20171019

mysql> INSERT INTO customer_data(id, msg, timestamp, expires) VALUES(1,'test',1508265000000,1508351400000);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM customer_data PARTITION (pd20171019);
+----+------+---------------+---------------+-------------+
| id | msg  | timestamp     | expires       | soft_delete |
+----+------+---------------+---------------+-------------+
|  1 | test | 1508265000000 | 1508351400000 |           2 |
+----+------+---------------+---------------+-------------+
1 row in set (0.00 sec)

• 假设客户端没有设置 expire，expires 列将为 0，这使得soft_delete到0并将转到no_retention分区。

mysql> INSERT INTO customer_data(id, msg, timestamp, expires)  VALUES(2,'non_expiry_row',1508265000000,0);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM customer_data PARTITION (no_retention);
+----+----------------+---------------+---------+-------------+
| id | msg            | timestamp     | expires | soft_delete |
+----+----------------+---------------+---------+-------------+
|  2 | non_expiry_row | 1508265000000 |       0 |           0 |
+----+----------------+---------------+---------+-------------+
1 row in set (0.00 sec)

• 假设客户机想要到期（假设 2017-10-19 06:30:00），可以更新到期列，将行从no_retention分区移动到相应的分区（这对性能有一定影响，因为行必须跨分区移动）

mysql> UPDATE customer_data SET expires=1508394600000 WHERE id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM customer_data PARTITION (no_retention);
Empty set (0.00 sec)

mysql> SELECT * FROM customer_data PARTITION (pd20171020);
+----+----------------+---------------+---------------+-------------+
| id | msg            | timestamp     | expires       | soft_delete |
+----+----------------+---------------+---------------+-------------+
|  2 | non_expiry_row | 1508265000000 | 1508394600000 |           2 |
+----+----------------+---------------+---------------+-------------+
1 row in set (0.00 sec)

• 假设客户端设置了一个超出我们分区的到期日，它将自动进入long_retention分区。

mysql> INSERT INTO customer_data(id, msg, timestamp, expires)  VALUES(3,'long_expiry',1507852800000,1608025600000);

mysql> SELECT * FROM customer_data PARTITION (long_retention);
+----+-------------+---------------+---------------+-------------+
| id | msg         | timestamp     | expires       | soft_delete |
+----+-------------+---------------+---------------+-------------+
|  3 | long_expiry | 1507852800000 | 1608025600000 |           1 |
+----+-------------+---------------+---------------+-------------+
1 row in set (0.00 sec)

跨分区的行移动缓慢，如果更新soft_delete，该行将从默认分区移动到另一个缓慢的分区。

扩展逻辑

我们可以扩展逻辑并增加soft_delete的值，以适应更多类型的分区。

• 0：如果 expires 的值为 0
• 3：如果到期时间与时间戳的距离小于或等于 7 天
• 2：如果到期时间与时间戳的距离小于或等于 60 天
• 1：如果过期时间距时间戳超过 60 天

soft_delete列将是分区的一部分。我们创造

• 如果soft_delete的值为0，则为单个no_retention分区
• 如果soft_delete 1的值为单个long_retention分区
• 如果soft_delete 2的值为
• 如果soft_delete 3的值为

示例分区表结构

将有一个缓冲区每周分区，这将是 42 天以后，将始终是空的，以便我们可以分裂和 7+2 个每天分区与 2 个缓冲区。

mysql> DROP TRIGGER IF EXISTS customer_data_insert;
DELIMITER $$
CREATE TRIGGER customer_data_insert
BEFORE INSERT
   ON customer_data FOR EACH ROW
BEGIN
    SET NEW.soft_delete = (IF((NEW.expires = 0),0,IF((ROUND((((((NEW.expires - NEW.timestamp) / 1000) / 60) / 60) / 24),0) <= 7),3,IF((ROUND((((((NEW.expires - NEW.timestamp) / 1000) / 60) / 60) / 24),0) <= 42),2,1))));
END;
$$
DELIMITER ; 

mysql> DROP TRIGGER IF EXISTS customer_data_update;
DELIMITER $$
CREATE TRIGGER customer_data_update
BEFORE INSERT
   ON customer_data FOR EACH ROW
BEGIN
    SET NEW.soft_delete = (IF((NEW.expires = 0),0,IF((ROUND((((((NEW.expires - NEW.timestamp) / 1000) / 60) / 60) / 24),0) <= 7),3,IF((ROUND((((((NEW.expires - NEW.timestamp) / 1000) / 60) / 60) / 24),0) <= 42),2,1))));
END;
$$
DELIMITER ;

mysql> CREATE TABLE `customer_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg` text,
  `timestamp` bigint(20) NOT NULL DEFAULT '0',
  `expires` bigint(20) NOT NULL DEFAULT '0',
  `soft_delete` tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`,`expires`,`soft_delete`)
) ENGINE=InnoDB AUTO_INCREMENT=609585360 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(`soft_delete`,`expires`)
(
 PARTITION no_retention VALUES LESS THAN (0,MAXVALUE) ENGINE = InnoDB,
 PARTITION long_retention VALUES LESS THAN (1,MAXVALUE) ENGINE = InnoDB, 
 PARTITION pw20171022 VALUES LESS THAN (2,1508630400000) ENGINE = InnoDB,
 PARTITION pw20171029 VALUES LESS THAN (2,1509235200000) ENGINE = InnoDB,
 PARTITION pw20171105 VALUES LESS THAN (2,1509840000000) ENGINE = InnoDB,
 PARTITION pw20171112 VALUES LESS THAN (2,1510444800000) ENGINE = InnoDB,
 PARTITION pw20171119 VALUES LESS THAN (2,1511049600000) ENGINE = InnoDB,
 PARTITION pw20171126 VALUES LESS THAN (2,1511654400000) ENGINE = InnoDB,
 PARTITION pw20171203 VALUES LESS THAN (2,1512259200000) ENGINE = InnoDB,
 -- buffer partition which will be 67 days away and will be always empty so that we can split
 PARTITION pw20171210 VALUES LESS THAN (2,1512864000000) ENGINE = InnoDB, 
 PARTITION pd20171016 VALUES LESS THAN (3,1508112000000) ENGINE = InnoDB,
 PARTITION pd20171017 VALUES LESS THAN (3,1508198400000) ENGINE = InnoDB,
 PARTITION pd20171018 VALUES LESS THAN (3,1508284800000) ENGINE = InnoDB,
 PARTITION pd20171019 VALUES LESS THAN (3,1508371200000) ENGINE = InnoDB,
 PARTITION pd20171020 VALUES LESS THAN (3,1508457600000) ENGINE = InnoDB,
 PARTITION pd20171021 VALUES LESS THAN (3,1508544000000) ENGINE = InnoDB,
 PARTITION pd20171022 VALUES LESS THAN (3,1508630400000) ENGINE = InnoDB,
 PARTITION pd20171023 VALUES LESS THAN (3,1508716800000) ENGINE = InnoDB,
 PARTITION pd20171024 VALUES LESS THAN (3,1508803200000) ENGINE = InnoDB
 ) */;

管理分区

您可以在 Linux 中创建CRON或在 mysql 中创建EVENT来管理分区。随着保留期的临近，分区管理工具应该将缓冲区分区重新组织为一个可用分区和一个缓冲区分区，并删除跨越保留期的分区。

例如，以前面提到的customer_data表为例。

在 20171203，您必须将分区 pw20171210 拆分为 pw20171210 和 pw20171217。

在 20171017，您必须将 pd20171024 拆分为 pd20171024 和 pd20171025。

只有在没有（或很少）数据的情况下，分割（重新组织）分区才会非常快（如果没有锁定表的查询，则约毫秒）。因此，我们应该在数据进入分区之前重新组织分区，以保持分区为空。