本节提供 MySQL 8.4 中分区的概念性概述。This section provides a conceptual overview of partitioning in MySQL 8.4.

有关分区限制和功能限制的信息，请参阅 第 26.6 节，“分区的限制和局限”。For information on partitioning restrictions and feature limitations, see Section 26.6, “Restrictions and Limitations on Partitioning”.

SQL 标准在数据存储的物理方面并没有提供太多指导。SQL 语言本身旨在独立于其操作的模式、表、行或列所依赖的任何数据结构或存储介质。然而，大多数先进的数据库管理系统已经发展出某种方法，用于确定在文件系统、硬件甚至两者中存储特定数据的物理位置。在 MySQL 中，InnoDB 存储引擎早已支持表空间的概念（见 第 17.6.3 节，“表空间”），即使在引入分区之前，MySQL 服务器也可以配置使用不同的物理目录来存储不同的数据库（见 第 10.12.2 节，“使用符号链接”，了解如何操作）。The SQL standard does not provide much in the way of guidance regarding the physical aspects of data storage. The SQL language itself is intended to work independently of any data structures or media underlying the schemas, tables, rows, or columns with which it works. Nonetheless, most advanced database management systems have evolved some means of determining the physical location to be used for storing specific pieces of data in terms of the file system, hardware or even both. In MySQL, the InnoDB storage engine has long supported the notion of a tablespace (see Section 17.6.3, “Tablespaces”), and the MySQL Server, even prior to the introduction of partitioning, could be configured to employ different physical directories for storing different databases (see Section 10.12.2, “Using Symbolic Links”, for an explanation of how this is done).

分区更进一步，通过使您能够根据可设置的规则将单个表的部分数据分布到文件系统中。实际上，表的不同部分作为独立的表存储在不同的位置。用于实现数据划分的用户选择规则被称为分区函数，在 MySQL 中可以是模运算、与一组范围或数值列表的简单匹配、内部哈希函数或线性哈希函数。该函数根据用户指定的分区类型选择，并以用户提供的表达式的值作为参数。根据所使用的分区类型，该表达式可以是列值、作用于一个或多个列值的函数，或一个或多个列值的集合。Partitioning takes this notion a step further, by enabling you to distribute portions of individual tables across a file system according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations. The user-selected rule by which the division of data is accomplished is known as a partitioning function, which in MySQL can be the modulus, simple matching against a set of ranges or value lists, an internal hashing function, or a linear hashing function. The function is selected according to the partitioning type specified by the user, and takes as its parameter the value of a user-supplied expression. This expression can be a column value, a function acting on one or more column values, or a set of one or more column values, depending on the type of partitioning that is used.

在 RANGE、LIST 和 [LINEAR] HASH 分区的情况下，分区列的值会被传递给分区函数，该函数返回一个整数值，表示该特定记录应存储的分区编号。该函数必须是非恒定且非随机的。它不能包含任何查询，但可以使用 MySQL 中有效的 SQL 表达式，只要该表达式返回 NULL 或整数 intval，使得In the case of RANGE, LIST, and [LINEAR] HASH partitioning, the value of the partitioning column is passed to the partitioning function, which returns an integer value representing the number of the partition in which that particular record should be stored. This function must be nonconstant and nonrandom. It may not contain any queries, but may use an SQL expression that is valid in MySQL, as long as that expression returns either NULL or an integer intval such that

-MAXVALUE <= intval <= MAXVALUE

(MAXVALUE 用于表示相关整数类型的最小上限。-MAXVALUE 表示最大下限。)(MAXVALUE is used to represent the least upper bound for the type of integer in question. -MAXVALUE represents the greatest lower bound.)

对于 [LINEAR] KEY、RANGE COLUMNS 和 LIST COLUMNS 分区，分区表达式由一个或多个列的列表组成。For [LINEAR] KEY, RANGE COLUMNS, and LIST COLUMNS partitioning, the partitioning expression consists of a list of one or more columns.

对于 [LINEAR] KEY 分区，分区函数由 MySQL 提供。For [LINEAR] KEY partitioning, the partitioning function is supplied by MySQL.

有关允许的分区列类型和分区函数的更多信息，请参阅第 26.2 节，“分区类型”，以及第 15.1.20 节，“CREATE TABLE 语句”，其中提供了分区语法说明和更多示例。有关分区函数限制的信息，请参阅第 26.6.3 节，“与函数相关的分区限制”。For more information about permitted partitioning column types and partitioning functions, see Section 26.2, “Partitioning Types”, as well as Section 15.1.20, “CREATE TABLE Statement”, which provides partitioning syntax descriptions and additional examples. For information about restrictions on partitioning functions, see Section 26.6.3, “Partitioning Limitations Relating to Functions”.

这被称为水平分区——也就是说，表的不同行可以分配到不同的物理分区。MySQL 8.4 不支持垂直分区，即表的不同列被分配到不同的物理分区。目前没有在 MySQL 中引入垂直分区的计划。This is known as horizontal partitioning—that is, different rows of a table may be assigned to different physical partitions. MySQL 8.4 does not support vertical partitioning, in which different columns of a table are assigned to different physical partitions. There are no plans at this time to introduce vertical partitioning into MySQL.

要创建分区表，必须使用支持分区的存储引擎。在 MySQL 8.4 中，同一分区表的所有分区必须使用相同的存储引擎。但是，这并不妨碍您在同一 MySQL 服务器上或甚至在同一数据库中对不同的分区表使用不同的存储引擎。For creating partitioned tables, you must use a storage engine that supports them. In MySQL 8.4, all partitions of the same partitioned table must use the same storage engine. However, there is nothing preventing you from using different storage engines for different partitioned tables on the same MySQL server or even in the same database.

在 MySQL 8.4 中，唯一支持分区的存储引擎是 InnoDB 和 NDB。分区不能用于不支持它的存储引擎；这些存储引擎包括 MyISAM、MERGE、CSV 和 FEDERATED 存储引擎。In MySQL 8.4, the only storage engines that support partitioning are InnoDB and NDB. Partitioning cannot be used with storage engines that do not support it; these include the MyISAM, MERGE, CSV, and FEDERATED storage engines.

使用 NDB 时，可以通过 KEY 或 LINEAR KEY 进行分区，但使用此存储引擎的表不支持其他类型的用户定义分区。此外，使用用户定义分区的 NDB 表必须有显式的主键，并且表的分区表达式中引用的任何列都必须是主键的一部分。但是，如果在用于创建或修改用户分区 NDB 表的 PARTITION BY KEY 或 PARTITION BY LINEAR KEY 语句中的 CREATE TABLE 或 ALTER TABLE 子句中没有列被列出，则该表不要求具有显式主键。有关更多信息，请参阅 第 25.2.7.1 节，“NDB Cluster 中的 SQL 语法不兼容性”。Partitioning by KEY or LINEAR KEY is possible with NDB, but other types of user-defined partitioning are not supported for tables using this storage engine. In addition, an NDB table that employs user-defined partitioning must have an explicit primary key, and any columns referenced in the table's partitioning expression must be part of the primary key. However, if no columns are listed in the PARTITION BY KEY or PARTITION BY LINEAR KEY clause of the CREATE TABLE or ALTER TABLE statement used to create or modify a user-partitioned NDB table, then the table is not required to have an explicit primary key. For more information, see Section 25.2.7.1, “Noncompliance with SQL Syntax in NDB Cluster”.

在创建分区表时，默认存储引擎的使用方式与创建其他表时相同；要覆盖此行为，只需像对非分区表一样使用[STORAGE] ENGINE选项。目标存储引擎必须提供原生分区支持，否则语句将失败。你应该记住，[STORAGE] ENGINE（以及其他表选项）需要在CREATE TABLE语句中使用任何分区选项之前列出。此示例展示了如何创建一个按哈希分区为 6 个分区并使用InnoDB存储引擎的表（无论default_storage_engine的值如何）：When creating a partitioned table, the default storage engine is used just as when creating any other table; to override this behavior, it is necessary only to use the [STORAGE] ENGINE option just as you would for a table that is not partitioned. The target storage engine must provide native partitioning support, or the statement fails. You should keep in mind that [STORAGE] ENGINE (and other table options) need to be listed before any partitioning options are used in a CREATE TABLE statement. This example shows how to create a table that is partitioned by hash into 6 partitions and which uses the InnoDB storage engine (regardless of the value of default_storage_engine):

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

每个 PARTITION 子句可以包含一个 [STORAGE] ENGINE 选项，但在 MySQL 8.4 中无效。Each PARTITION clause can include a [STORAGE] ENGINE option, but in MySQL 8.4 this has no effect.

除非另有说明，本讨论中其余示例假设 default_storage_engine 为 InnoDB。Unless otherwise specified, the remaining examples in this discussion assume that default_storage_engine is InnoDB.

可以使用 DATA DIRECTORY 和 INDEX DIRECTORY 选项，将每个分区的数据和索引分配到特定目录，这些选项用于创建分区表时的 CREATE TABLE 语句中的 PARTITION 子句。Data and indexes for each partition can be assigned to a specific directory using the DATA DIRECTORY and INDEX DIRECTORY options for the PARTITION clause of the CREATE TABLE statement used to create the partitioned table.

对于 InnoDB 表的单个分区和子分区，只支持 DATA DIRECTORY 选项。DATA DIRECTORY 子句中指定的目录必须为 InnoDB 所知。有关更多信息，请参阅 使用 DATA DIRECTORY 子句。Only the DATA DIRECTORY option is supported for individual partitions and subpartitions of InnoDB tables. The directory specified in a DATA DIRECTORY clause must be known to InnoDB. For more information, see Using the DATA DIRECTORY Clause.

用于表分区表达式的所有列必须是表可能拥有的每个唯一键的一部分，包括任何主键。这意味着以下 SQL 语句创建的表不能进行分区：All columns used in the table's partitioning expression must be part of every unique key that the table may have, including any primary key. This means that a table such as this one, created by the following SQL statement, cannot be partitioned:

CREATE TABLE tnp (
    id INT NOT NULL AUTO_INCREMENT,
    ref BIGINT NOT NULL,
    name VARCHAR(255),
    PRIMARY KEY pk (id),
    UNIQUE KEY uk (name)
);

由于键 pk 和 uk 没有共享的列，因此没有列可用于分区表达式。在这种情况下的可能解决方法包括将 name 列添加到表的主键中，将 id 列添加到 uk 中，或者干脆完全移除唯一键。更多信息请参见 第 26.6.1 节，“分区键、主键和唯一键”。Because the keys pk and uk have no columns in common, there are no columns available for use in a partitioning expression. Possible workarounds in this situation include adding the name column to the table's primary key, adding the id column to uk, or simply removing the unique key altogether. See Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”, for more information.

此外，MAX_ROWS 和 MIN_ROWS 可分别用于确定每个分区中可以存储的最大行数和最小行数。有关这些选项的更多信息，请参见 第 26.3 节，“分区管理”。In addition, MAX_ROWS and MIN_ROWS can be used to determine the maximum and minimum numbers of rows, respectively, that can be stored in each partition. See Section 26.3, “Partition Management”, for more information on these options.

MAX_ROWS 选项在创建具有额外分区的 NDB Cluster 表时也非常有用，从而允许存储更多的哈希索引。请参阅 DataMemory 数据节点配置参数的文档，以及 第 25.2.2 节，“NDB Cluster 节点、节点组、分片副本和分区”，以获取更多信息。The MAX_ROWS option can also be useful for creating NDB Cluster tables with extra partitions, thus allowing for greater storage of hash indexes. See the documentation for the DataMemory data node configuration parameter, as well as Section 25.2.2, “NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions”, for more information.

分区的一些优势如下：Some advantages of partitioning are listed here: