MySQL 5.7 分区修剪 – 但行好事莫问前程

分区修剪
分区修剪背后的核心概念相对简单，可以描述为“不扫描可能没有匹配值的分区 ”。假设你有一个由以下语句定义的分区表t1：

mysql> 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
    -> );
Query OK, 0 rows affected (0.02 sec)

考虑这样一种情况，您希望从SELECT语句中获得如下结果：

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

很容易看出，应该返回的行都不会出现在p0或p3分区中；也就是说，我们只需要在分区p1和p2中查找匹配的行。这样，查找匹配的行所花费的时间和精力就比扫描表中的所有分区要少得多。这种“删除”不需要的分区称为剪枝（pruning）。当优化器可以在执行此查询时使用分区修剪时，查询的执行速度可以比针对包含相同列定义和数据的未分区表的相同查询快一个数量级。

在对已分区的MyISAM表进行修剪时，不管是否检查分区，都会打开所有分区，这取决于MyISAM存储引擎的设计。这意味着用户必须有足够数量的文件描述符来覆盖表的所有分区。

此限制不适用于使用其他MySQL存储引擎（如InnoDB）的分区表。

只要WHERE条件可以简化为以下两种情况之一，优化器就可以进行修剪。
.partition_column = constant

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

在第一种情况下，优化器只是计算给定值的分区表达式，确定哪个分区包含该值，然后只扫描这个分区。在很多情况下，等号可以替换为其他算术比较，包括< 、>、< =、>=和<>。在WHERE子句中使用BETWEEN的一些查询也可以利用分区修剪。

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

MySQL可以对SELECT、DELETE和UPDATE语句进行分区修剪。INSERT语句目前不能被修剪。

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

对于使用范围列或列表列分区的表，如果条件涉及对多个列进行上述类型的比较，优化器还可以执行修剪。

只要分区表达式包含一个等式或一个可以简化为一组等式的范围，或者分区表达式表示一个递增或递减关系，就可以应用这种类型的优化。当分区表达式使用YEAR（）或TO_DAYS（）函数时，还可以对在DATE或DATETIME列上分区的表进行剪枝。此外，在MySQL 5.7中，当分区表达式使用
TO_SECONDS()函数。

假设表t2，定义如下，在一个DATE列上分区：

mysql> 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
    -> );
Query OK, 0 rows affected (0.02 sec)

以下使用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'

最后一条语句，优化器也可以这样做：
1.找到包含范围下限的分区。
YEAR（'1984-06-21'）产生值1984，该值在分区d3中找到。

2.找到包含范围高端的分区。
YEAR（'1999-06-21'）的计算结果为1999，位于分区d5。

3.只扫描这两个分区以及它们之间的任何分区。
在本例中，这意味着只扫描分区d3、d4和d5。剩余的分区可以被安全地忽略（并且被忽略）。

在针对分区表的语句的WHERE条件中引用的无效DATE和DATETIME值将被视为NULL。这意味着像SELECT * FROM partitioned_table WHERE date_column < ‘2008-12-00’这样的查询不返回任何值（见Bug #40972）。到目前为止，我们只查看了使用范围分区的示例，但是修剪也可以应用于其他分区类型。考虑一个按列表分区的表，其中分区表达式是递增或递减的，例如这里展示的表t3。（在这个例子中，为了简洁，我们假设region_code列的值被限制在1到10之间，包括1和10。）

mysql> 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)
    -> );
Query OK, 0 rows affected (0.02 sec)

对于SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3这样的语句，优化器确定在哪些分区中可以找到值1、2和3 (r0和r1)，并跳过其余的分区（r2和r3）

对于按哈希或线性键分区的表，当WHERE子句对分区表达式中使用的列使用简单=关系时，也可以进行分区修剪。考虑这样创建一个表：

mysql> 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;
Query OK, 0 rows affected (0.03 sec)

将列值与常量进行比较的语句可以被修剪：

UPDATE t4 WHERE region_code = 7;

修剪也可以用于短范围，因为优化器可以将这种条件转化为关系。例如，使用前面定义的表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）。

只有当范围大小小于分区数量时，才会使用这种优化。想想这个说法：
DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

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

当表按按哈希或线性键分区时，剪枝只能用于整型列。例如，下面的语句不能使用剪枝，因为dob是日期列：

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

但是，如果表的年份存储在INT列中，那么WHERE year_col >= 2001和year_col <= 2005的查询就会被删除。

发表评论 取消回复

发表评论取消回复