通过 HASH 进行分区主要用于确保数据在预定数量的分区之间均匀分布。使用范围或列表分区时，必须明确指定给定列值或列值集合应存储到哪个分区中；而使用哈希分区时，这一决策会自动为你处理，你只需指定一个列值或基于列值的表达式进行哈希，以及要将分区表划分的分区数量。Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly which partition a given column value or set of column values should be stored in; with hash partitioning, this decision is taken care of for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.

要使用 HASH 分区来划分表，需要在 CREATE TABLE 语句中附加 PARTITION BY HASH (expr) 子句，其中 expr 是返回整数的表达式。这可以仅仅是一个列名，该列的类型必须是 MySQL 的整数类型之一。此外，你很可能还希望在其后跟上 PARTITIONS num，其中 num 是表示表要划分的分区数量的正整数。To partition a table using HASH partitioning, it is necessary to append to the CREATE TABLE statement a PARTITION BY HASH (expr) clause, where expr is an expression that returns an integer. This can simply be the name of a column whose type is one of MySQL's integer types. In addition, you most likely want to follow this with PARTITIONS num, where num is a positive integer representing the number of partitions into which the table is to be divided.

下面的语句创建了一个在 store_id 列上使用哈希分区的表，并分为 4 个分区：The following statement creates a table that uses hashing on the store_id column and is divided into 4 partitions:

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 HASH(store_id)
PARTITIONS 4;

如果您未包含 PARTITIONS 子句，分区数默认为 1；如果使用了 PARTITIONS 关键字但后面未跟数字，则会导致语法错误。If you do not include a PARTITIONS clause, the number of partitions defaults to 1; using the PARTITIONS keyword without a number following it results in a syntax error.

您还可以使用返回整数的 SQL 表达式作为 expr。例如，您可能希望根据员工的入职年份进行分区。可以按如下方式操作：You can also use an SQL expression that returns an integer for expr. For instance, you might want to partition based on the year in which an employee was hired. This can be done as shown here:

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 HASH( YEAR(hired) )
PARTITIONS 4;

expr 必须返回非固定、非随机的整数值（换句话说，它应该是可变但确定的），并且不得包含 第 26.6 节，“分区的限制和约束” 中描述的任何禁止构造。您还应记住，每次插入或更新（或可能删除）行时都会评估此表达式；这意味着非常复杂的表达式可能会导致性能问题，尤其是在执行影响大量行的操作（例如批量插入）时。expr must return a nonconstant, nonrandom integer value (in other words, it should be varying but deterministic), and must not contain any prohibited constructs as described in Section 26.6, “Restrictions and Limitations on Partitioning”. You should also keep in mind that this expression is evaluated each time a row is inserted or updated (or possibly deleted); this means that very complex expressions may give rise to performance issues, particularly when performing operations (such as batch inserts) that affect a great many rows at one time.

The most efficient hashing function is one which operates upon a single table column and whose value increases or decreases consistently with the column value, as this allows for “pruning” on ranges of partitions. That is, the more closely that the expression varies with the value of the column on which it is based, the more efficiently MySQL can use the expression for hash partitioning.

例如，当 date_col 是类型为 DATE 的列时，表达式 TO_DAYS(date_col) 被认为与 date_col 的值呈直接变化关系，因为每当 date_col 的值发生变化时，表达式的值也会以一致的方式变化。表达式 YEAR(date_col) 相对于 date_col 的变化并不像 TO_DAYS(date_col) 那样直接，因为并非 date_col 的每一次可能变化都会在 YEAR(date_col) 中产生等量变化。即便如此，YEAR(date_col) 仍然是哈希函数的一个良好候选，因为它与 date_col 的一部分呈直接变化，并且不存在 date_col 的变化会在 YEAR(date_col) 中产生不成比例的变化。For example, where date_col is a column of type DATE, then the expression TO_DAYS(date_col) is said to vary directly with the value of date_col, because for every change in the value of date_col, the value of the expression changes in a consistent manner. The variance of the expression YEAR(date_col) with respect to date_col is not quite as direct as that of TO_DAYS(date_col), because not every possible change in date_col produces an equivalent change in YEAR(date_col). Even so, YEAR(date_col) is a good candidate for a hashing function, because it varies directly with a portion of date_col and there is no possible change in date_col that produces a disproportionate change in YEAR(date_col).

作为对比，假设你有一个名为 int_col 的列，其类型是 INT。现在考虑表达式 POW(5-int_col,3) + 6。这是一个不佳的哈希函数选择，因为 int_col 的值变化并不能保证会在表达式值上产生相应的变化。将 int_col 的值改变一个固定量可能会在表达式值上产生很大的不同变化。例如，将 int_col 从 5 改为 6 会使表达式值变化 -1，但将 int_col 的值从 6 改为 7 则会使表达式值变化 -7。By way of contrast, suppose that you have a column named int_col whose type is INT. Now consider the expression POW(5-int_col,3) + 6. This would be a poor choice for a hashing function because a change in the value of int_col is not guaranteed to produce a proportional change in the value of the expression. Changing the value of int_col by a given amount can produce widely differing changes in the value of the expression. For example, changing int_col from 5 to 6 produces a change of -1 in the value of the expression, but changing the value of int_col from 6 to 7 produces a change of -7 in the expression value.

换句话说，当列值与表达式值的图形遵循由方程 y=cx 描述的直线时，其中 c 是某个非零常数，该表达式越适合用于哈希。这与这样一个事实有关：表达式越非线性，其在分区中产生的数据分布就越不均匀。In other words, the more closely the graph of the column value versus the value of the expression follows a straight line as traced by the equation y=cx where c is some nonzero constant, the better the expression is suited to hashing. This has to do with the fact that the more nonlinear an expression is, the more uneven the distribution of data among the partitions it tends to produce.

理论上，涉及多列值的表达式也可以进行剪枝，但确定哪些表达式适用可能相当困难且耗时。因此，不特别推荐使用涉及多列的哈希表达式。In theory, pruning is also possible for expressions involving more than one column value, but determining which of such expressions are suitable can be quite difficult and time-consuming. For this reason, the use of hashing expressions involving multiple columns is not particularly recommended.

当使用 PARTITION BY HASH 时，存储引擎会根据表达式结果的取模来确定使用 num 分区中的哪一个。换句话说，对于给定的表达式 expr，记录存储的分区为分区编号 N，其中 N = MOD(expr, num)。假设表 t1 定义如下，因此它有 4 个分区：When PARTITION BY HASH is used, the storage engine determines which partition of num partitions to use based on the modulus of the result of the expression. In other words, for a given expression expr, the partition in which the record is stored is partition number N, where N = MOD(expr, num). Suppose that table t1 is defined as follows, so that it has 4 partitions:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

如果你向 t1 插入一条 col3 值为 '2005-09-15' 的记录，则它存储的分区的确定方式如下：If you insert a record into t1 whose col3 value is '2005-09-15', then the partition in which it is stored is determined as follows:

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

MySQL 8.4 还支持一种 HASH 分区的变体，称为 线性哈希，它使用更复杂的算法来确定插入到分区表中的新行的位置。参见 第 26.2.4.1 节，“线性哈希分区”，了解该算法的描述。MySQL 8.4 also supports a variant of HASH partitioning known as linear hashing which employs a more complex algorithm for determining the placement of new rows inserted into the partitioned table. See Section 26.2.4.1, “LINEAR HASH Partitioning”, for a description of this algorithm.

每次插入或更新记录时，都会评估用户提供的表达式。根据情况，它也可能在删除记录时进行评估。The user-supplied expression is evaluated each time a record is inserted or updated. It may also—depending on the circumstances—be evaluated when records are deleted.