22.4 分区修剪

本节讨论称为分区修剪的优化。分区修剪背后的核心概念相对简单，可以描述为“不扫描没有匹配值的分区”。假设您有一个由以下语句定义的分区 tablet1：

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语句获得结果的情况：

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

显而易见，应该返回的行都不会在分区p0或p3中；也就是说，我们只需要在分区p1和p2中搜索以找到匹配的行。这样，与扫描 table 中的所有分区相比，在查找匹配行上可以花费更少的时间和精力。这种“切掉”不需要的分区称为修剪。当优化器可以在执行此查询时使用分区修剪时，对于包含相同列定义和数据的未分区 table，查询的执行可能比同一查询快一个数量级。

只要将WHERE条件减少到以下两种情况之一，优化器就可以执行修剪：

• partition_column = constant

• partition_column IN (constant1, constant2, ..., constantN)

在第一种情况下，优化器仅对给定值的分区 table 达式求值，确定哪个分区包含该值，然后仅扫描该分区。在许多情况下，等号可用另一种算术比较代替，包括<，>，<=，>=和<>。在WHERE子句中使用BETWEEN的某些查询也可以利用分区修剪功能。请参阅本节后面的示例。

在第二种情况下，优化器为列 table 中的每个值评估分区 table 达式，创建匹配分区的列 table，然后仅扫描此分区列 table 中的分区。

MySQL 可以将分区修剪应用于SELECT，DELETE和UPDATE语句。 INSERT语句还只对每个插入的行访问一个分区；即使对于由HASH或KEY分区的 table 也是如此，尽管EXPLAIN的输出当前未显示。

修剪还可以应用于短范围，优化器可以将其转换为等效的值列 table。例如，在前面的示例中，WHERE子句可以转换为WHERE region_code IN (126, 127, 128, 129)。然后，优化器可以确定列 table 中的前两个值是在分区p1中找到的，其余两个值是在分区p2中的，并且其他分区不包含任何相关值，因此不需要搜索匹配行。

优化程序还可以针对WHERE条件执行修剪，这些条件涉及使用RANGE COLUMNS或LIST COLUMNS分区的 table 在多列上的先前类型的比较。

只要分区 table 达式包含一个等于或可减少为一组相等的范围的范围，或者当分区 table 达式 table 示增加或减少的关系时，都可以应用这种类型的优化。当分区 table 达式使用YEAR()或TO_DAYS()函数时，修剪还可以应用于在DATE或DATETIME列上分区的 table。另外，在 MySQL 5.7 中，当分区 table 达式使用TO_SECONDS()函数时，可以对此类 table 应用修剪。

假设按如下所示定义的 tablet2被分区在DATE列上：

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的语句可以使用分区修剪：

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'

对于最后一条语句，优化器还可以执行以下操作：

• 找到包含范围下限的分区。

YEAR('1984-06-21')产生值1984，该值在分区d3中找到。

• 找到包含范围高端的分区。

YEAR('1999-06-21')的计算结果为1999，位于分区d5中。

• 仅扫描这两个分区以及它们之间可能存在的任何分区。

在这种情况下，这意味着仅扫描分区d3，d4和d5。其余分区可以安全地忽略(也可以忽略)。

到目前为止，我们仅查看了使用RANGE分区的示例，但是修剪也可以应用于其他分区类型。

考虑一个被LIST分区的 table，其中分区 table 达式在增加或减少，例如此处显示的 tablet3。 (在本示例中，为简洁起见，我们假设region_code列的值限制为 1 到 10 之间(包括 1 和 10)。

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)。

对于被HASH或[LINEAR] KEY分区的 table，如果WHERE子句对分区 table 达式中使用的列使用简单的=关系，则也可以进行分区修剪。考虑这样创建的 table：

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;

可以删除将列值与常量进行比较的语句：

UPDATE t4 WHERE region_code = 7;

修剪还可以用于短距离，因为优化程序可以将这种条件转换为IN关系。例如，使用与先前定义的 tablet4相同的查询可以被修剪：

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)。

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

当 table 被HASH或[LINEAR] KEY分区时，修剪只能在整数列上使用。例如，此语句不能使用修剪，因为dob是DATE列：

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

但是，如果 table 将年份值存储在INT列中，则可以删除具有WHERE year_col >= 2001 AND year_col <= 2005的查询。

在 MySQL 5.7.1 之前，使用提供自动分区的存储引擎(例如 NDB Cluster 使用的NDB存储引擎)对所有 table 禁用了分区修剪。 (缺陷号 14672885)从 MySQL 5.7.1 开始，如果对这些 table 进行了显式分区，则可以对其进行修剪。 (错误#14827952)