参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html

本节讨论称为分区修剪的优化。 分区修剪背后的核心概念相对简单，可以描述为“不扫描没有匹配值的分区”。 假设您有一个由此语句定义的分区表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中搜索以查找匹配的行。 通过这样做，可以花费更少的时间和精力来查找匹配的行，而不是扫描表中的所有分区所需的行。 这种“切掉”不需要的分区称为修剪。 当优化器在执行此查询时可以使用分区修剪时，查询的执行速度可以比包含相同列定义和数据的非分区表的相同查询快一个数量级。

注意：

在分区的MyISAM表上执行修剪时，由于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中，并且其他分区不包含相关值，因此不需要搜索匹配的行。

优化器可以在多个类型列上的where条件进行剪裁

只要分区表达式由等式或可以减少到一组等式的范围组成，或者当分区表达式表示增加或减少关系时，就可以应用这种类型的优化。 当分区表达式使用YEAR（）或TO_DAYS（）函数时，也可以对在DATE或DATETIME列上分区的表应用修剪。 此外，在MySQL 5.7中，当分区表达式使用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分区中找到。

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

年（'1999-06-21'）评估到1999年，在d5分区中找到。

• 仅扫描这两个分区和可能位于它们之间的任何分区。
  

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

重要：

在针对分区表的语句的WHERE条件中引用的DATE和DATETIME值被视为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子句对分区表达式中使用的列使用simple = relation的情况下，也可以进行分区修剪。 考虑一个像这样创建的表：

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个分区。 这意味着无法进行剪裁。

当表由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 5.7.1之前，使用提供自动分区的存储引擎（例如NDB Cluster使用的NDB存储引擎）对所有表禁用分区修剪。 （bug 14672885）从MySQL 5.7.1开始，如果这些表被明确分区，则可以对其进行修剪。 （bug 14827952）