MySQL :: MySQL 8.3 Reference Manual :: 26.4 Partition Pruning

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / Partitioning / Partition Pruning

26.4 分区修剪

所谓的优化技术称为分区修剪是基于一个相对简单的概念，可以描述为 “不要扫描没有匹配值的分区”。假设创建了一个分区表 t1，使用以下语句：

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 分区中搜索匹配的行。通过限制搜索，可以比扫描整个表节省大量时间和努力。这种““剪枝””不需要的分区称为修剪。当优化器可以使用分区修剪来执行查询时，查询的执行速度可以比同样的非分区表快一个数量级。

优化器可以在以下两种情况下执行修剪：

partition_column = constant
partition_column IN (constant1, constant2, ..., constantN)

在第一种情况下，优化器简单地评估分区表达式的值，确定哪个分区包含该值，然后只扫描该分区。在许多情况下，等号可以被其他算术比较符号所取代，包括 <、>、<=、>= 和 <>。一些使用 BETWEEN 的查询也可以利用分区修剪。见本节后面的示例。

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

SELECT、DELETE 和 UPDATE 语句支持分区修剪。INSERT 语句也只访问每个插入行的单个分区；这甚至适用于使用 HASH 或 KEY 分区的表，尽管当前在 EXPLAIN 的输出中没有显示。

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

优化器也可以对使用 RANGE COLUMNS 或 LIST COLUMNS 分区的表执行修剪，前提是 WHERE 子句涉及到多个列的比较。

这种优化技术可以应用于分区表达式是等式或范围的任何情况，也可以应用于分区表达式表示递增或递减关系的情况。修剪也可以应用于使用 DATE 或 DATETIME 列的表，当分区表达式使用 YEAR() 或 TO_DAYS() 函数时。修剪也可以应用于使用 TO_SECONDS() 函数的表。

假设表 t2 按照 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。其余分区可以安全地忽略（并被忽略）。

Important

无效的 DATE 和 DATETIME 值在 partitioned 表的 WHERE 条件中被视为 NULL。这意味着查询如 SELECT * FROM partitioned_table WHERE date_column < '2008-12-00' 不会返回任何值（参见 Bug #40972）。

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

考虑一个按 LIST 分区的表，其中分区表达式是递增或递减的，如表 t3 所示。（在这个示例中，我们假设 region_code 列的值介于 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 分区的表，分区修剪也可以在 WHERE 子句使用简单的 = 关系对分区表达式中使用的列时应用。考虑一个表创建如下：

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 关系。例如，使用同一个表 t4，查询如下可以被修剪：

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

Important

只有当范围大小小于分区数时，才使用这种优化。考虑以下语句：

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

范围在 WHERE 子句中涵盖 9 个值（4、5、6、7、8、9、10、11、12），但 t4 只有 8 个分区。这意味着 DELETE 不能被修剪。

当表按 HASH 或 [LINEAR] KEY 分区时，修剪只能用于整数列。例如，以下语句不能使用修剪，因为 dob 是一个 DATE 列：

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

然而，如果表存储年份值在一个 INT 列中，那么查询 WHERE year_col >= 2001 AND year_col <= 2005 可以被修剪。

使用自动分区的存储引擎的表，如 MySQL Cluster 中的 NDB 存储引擎，可以在明确分区的情况下被修剪。

PREV HOME UP NEXT