mysql2008r1_mysql8 参考手册-分区修剪

最新推荐文章于 2024-08-24 12:39:45 发布

树花

最新推荐文章于 2024-08-24 12:39:45 发布

阅读量105

点赞数

文章标签： mysql2008r1

本文链接：https://blog.csdn.net/weixin_35531779/article/details/113323096

版权

称为分区修剪的优化基于一个相对简单的概念，该概念可以描述为“ 不扫描没有匹配值的分区 ”。假设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找到匹配的行。通过限制搜索，与扫描表中的所有分区相比，查找匹配的行可以花费更少的时间和精力。这种“ 切掉 ”不需要的分区被称为修剪。当优化程序可以在执行此查询时使用分区修剪时，对于包含相同列定义和数据的未分区表，查询的执行速度可以比同一查询快一个数量级。

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

partition_column = constant

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

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

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

SELECT， DELETE和 UPDATE语句支持分区修剪。一条INSERT语句对插入的每一行也只能访问一个分区。即使对于由HASH或进行分区的表也是如此，KEY尽管当前未在中显示EXPLAIN。

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

优化程序还可以对WHERE涉及使用RANGE COLUMNS或LIST COLUMNS分区的表的多个列上的前述类型进行比较的条件进行修剪。

只要分区表达式包含一个等于或可以减少为一组相等项的范围，或者当分区表达式表示增加或减少的关系时，都可以应用这种类型的优化。当分区表达式使用or 函数时，修剪还可以应用于在 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

);

以下语句using 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，可在partition中找到 d5。

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

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

重要

在针对分区表的语句条件中引用的无效值DATE和DATETIME值WHERE均视为 NULL。这意味着诸如之类的查询不会返回任何值(请参见Bug＃40972)。 SELECT * FROM partitioned_table WHERE date_column < '2008-12-00'

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

考虑一个由分区的表LIST，其中分区表达式在增加或减少，例如t3此处所示的表。(在本示例中，为简洁起见，我们假设该 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如果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)。

重要

仅当范围大小小于分区数时才使用此优化。考虑以下语句：

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可以删除具有的查询。

如果表NDB被显式分区，则可以修剪使用提供自动分区的存储引擎的表，例如MySQL Cluster使用的存储引擎。