一种被称为 分区修剪 的优化基于一个相对简单的概念，可以描述为 “不要扫描可能不存在匹配值的分区”。假设通过以下语句创建了一个分区表 t1：The optimization known as partition pruning is based on a relatively simple concept which can be described as “Do not scan partitions where there can be no matching values”. Suppose a partitioned table t1 is created by this statement:

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

假设你希望从如下 SELECT 语句中获取结果：Suppose that you wish to obtain results from a SELECT statement such as this one:

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

很容易看出，应该返回的行没有在分区 p0 或 p3 中；也就是说，我们只需要在分区 p1 和 p2 中搜索以找到匹配的行。通过限制搜索范围，可以比扫描表中的所有分区花费更少的时间和精力来找到匹配的行。对不需要的分区进行这种“裁剪”被称为 分区修剪。当优化器在执行此查询时能够利用分区修剪，查询的执行速度可以比对具有相同列定义和数据的非分区表的相同查询快一个数量级。It is easy to see that none of the rows which ought to be returned are in either of the partitions p0 or p3; that is, we need search only in partitions p1 and p2 to find matching rows. By limiting the search, it is possible to expend much less time and effort in finding matching rows than by scanning all partitions in the table. This “cutting away” of unneeded partitions is known as pruning. When the optimizer can make use of partition pruning in performing this query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data.

优化器可以在 WHERE 条件可以简化为以下两种情况之一时执行裁剪：The optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases:

在第一种情况下，优化器只是对给定值评估分区表达式，确定哪个分区包含该值，然后只扫描该分区。在许多情况下，等号可以被其他算术比较替代，包括 <、>、<=、>= 和 <>。在 WHERE 子句中使用 BETWEEN 的一些查询也可以利用分区剪裁。请参见本节后面的示例。In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including <, >, <=, >=, and <>. Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning. See the examples later in this section.

在第二种情况下，优化器对列表中的每个值评估分区表达式，创建匹配分区的列表，然后只扫描该分区列表中的分区。In the second case, the optimizer evaluates the partitioning expression for each value in the list, creates a list of matching partitions, and then scans only the partitions in this partition list.

SELECT、DELETE 和 UPDATE 语句支持分区裁剪。INSERT 语句每插入一行也只访问一个分区；即使是按 HASH 或 KEY 分区的表，这也是成立的，尽管目前在 EXPLAIN 的输出中没有显示。SELECT, DELETE, and UPDATE statements support partition pruning. An INSERT statement also accesses only one partition per inserted row; this is true even for a table that is partitioned by HASH or KEY although this is not currently shown in the output of EXPLAIN.

裁剪也可以应用于短范围，优化器可以将其转换为等效的值列表。例如，在前面的示例中，WHERE 子句可以转换为 WHERE region_code IN (126, 127, 128, 129)。然后优化器可以确定列表中的前两个值位于分区 p1，剩下的两个值位于分区 p2，而其他分区不包含相关值，因此无需搜索匹配的行。Pruning can also be applied to short ranges, which the optimizer can convert into equivalent lists of values. For instance, in the previous example, the WHERE clause can be converted to WHERE region_code IN (126, 127, 128, 129). Then the optimizer can determine that the first two values in the list are found in partition p1, the remaining two values in partition p2, and that the other partitions contain no relevant values and so do not need to be searched for matching rows.

优化器还可以对涉及多个列的上述类型比较的 WHERE 条件执行裁剪，对于使用 RANGE COLUMNS 或 LIST COLUMNS 分区的表亦然。The optimizer can also perform pruning for WHERE conditions that involve comparisons of the preceding types on multiple columns for tables that use RANGE COLUMNS or LIST COLUMNS partitioning.

每当分区表达式由等式或可简化为一组等式的范围组成，或当分区表达式表示递增或递减关系时，都可以应用这种类型的优化。当分区表达式使用 YEAR() 或 TO_DAYS() 函数时，对于按 DATE 或 DATETIME 列分区的表，也可以应用裁剪。当分区表达式使用 TO_SECONDS() 函数时，对于此类表也可以应用裁剪。This type of optimization can be applied whenever the partitioning expression consists of an equality or a range which can be reduced to a set of equalities, or when the partitioning expression represents an increasing or decreasing relationship. Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. Pruning can also be applied for such tables when the partitioning expression uses the TO_SECONDS() function.

假设表 t2 按 DATE 列进行分区，是使用此处所示的语句创建的：Suppose that table t2, partitioned on a DATE column, is created using the statement shown here:

CREATE TABLE t2 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION d0 VALUES LESS THAN (1970),
    PARTITION d1 VALUES LESS THAN (1975),
    PARTITION d2 VALUES LESS THAN (1980),
    PARTITION d3 VALUES LESS THAN (1985),
    PARTITION d4 VALUES LESS THAN (1990),
    PARTITION d5 VALUES LESS THAN (2000),
    PARTITION d6 VALUES LESS THAN (2005),
    PARTITION d7 VALUES LESS THAN MAXVALUE
);

使用 t2 的以下语句可以利用分区剪裁：The following statements using t2 can make of use partition pruning:

SELECT * FROM t2 WHERE dob = '1982-06-23';

UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

在最后一条语句的情况下，优化器还可以执行以下操作：In the case of the last statement, the optimizer can also act as follows:

到目前为止，我们只看了使用 RANGE 分区的示例，但裁剪也可以应用于其他分区类型。So far, we have looked only at examples using RANGE partitioning, but pruning can be applied with other partitioning types as well.

考虑一个按 LIST 分区的表，其中分区表达式是递增或递减的，例如这里显示的表 t3。（在此示例中，为了简洁起见，我们假设 region_code 列的值仅限于 1 到 10 之间，包括 1 和 10。）Consider a table that is partitioned by LIST, where the partitioning expression is increasing or decreasing, such as the table t3 shown here. (In this example, we assume for the sake of brevity that the region_code column is limited to values between 1 and 10 inclusive.)

CREATE TABLE t3 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
    PARTITION r0 VALUES IN (1, 3),
    PARTITION r1 VALUES IN (2, 5, 8),
    PARTITION r2 VALUES IN (4, 9),
    PARTITION r3 VALUES IN (6, 7, 10)
);

对于诸如 SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3 的语句，优化器会确定值 1、2 和 3 位于哪些分区（r0 和 r1），并跳过其余分区（r2 和 r3）。For a statement such as SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3, the optimizer determines in which partitions the values 1, 2, and 3 are found (r0 and r1) and skips the remaining ones (r2 and r3).

对于按 HASH 或 [LINEAR] KEY 分区的表，在 WHERE 子句中使用简单的 = 关系与用于分区的列时，也可以进行分区裁剪。考虑如下创建的表：For tables that are partitioned by HASH or [LINEAR] KEY, partition pruning is also possible in cases in which the WHERE clause uses a simple = relation against a column used in the partitioning expression. Consider a table created like this:

CREATE TABLE t4 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;

将列值与常量进行比较的语句可以被裁剪：A statement that compares a column value with a constant can be pruned:

UPDATE t4 WHERE region_code = 7;

分区裁剪也可以用于短范围，因为优化器可以将此类条件转换为 IN 关系。例如，使用之前定义的同一个表 t4，如下查询可以被裁剪：Pruning can also be employed for short ranges, because the optimizer can turn such conditions into IN relations. For example, using the same table t4 as defined previously, queries such as these can be pruned:

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;

SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

在这两种情况下，优化器将 WHERE 子句转换为 WHERE region_code IN (3, 4, 5)。In both these cases, the WHERE clause is transformed by the optimizer into WHERE region_code IN (3, 4, 5).

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

当表按 HASH 或 [LINEAR] KEY 分区时，裁剪只能用于整数列。例如，该语句无法使用裁剪，因为 dob 是一个 DATE 列：When a table is partitioned by HASH or [LINEAR] KEY, pruning can be used only on integer columns. For example, this statement cannot use pruning because dob is a DATE column:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

但是，如果表在 INT 列中存储年份值，那么包含 WHERE year_col >= 2001 AND year_col <= 2005 的查询可以被剪枝。However, if the table stores year values in an INT column, then a query having WHERE year_col >= 2001 AND year_col <= 2005 can be pruned.

使用提供自动分区功能的存储引擎的表，例如 MySQL Cluster 使用的 NDB 存储引擎，如果它们被显式分区，也可以被剪枝。Tables using a storage engine that provides automatic partitioning, such as the NDB storage engine used by MySQL Cluster can be pruned if they are explicitly partitioned.