月份：2026年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的查询就会被删除。

获取分区信息
获取有关现有分区的信息，这可以通过多种方式完成。获取此类信息的方法包括：
.使用SHOW CREATE TABLE语句查看创建分区表时使用的分区子句。

.使用SHOW TABLE STATUS语句确定表是否被分区。

.查询INFORMATION_SCHEMA.PARTITIONS表。

.使用EXPLAIN SELECT语句查看给定SELECT使用了哪些分区。

正如本章其他地方所讨论的，SHOW CREATE TABLE在其输出中包含用于创建分区表的PARTITION BY子句。例如:

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY KEY (id)
PARTITIONS 2 */
1 row in set (0.01 sec)

对于已分区表，SHOW TABLE STATUS的输出与未分区表的输出相同，只是Create_options列包含字符串partitioned。Engine列包含表的所有分区 使用的存储引擎的名称。

您还可以从INFORMATION_SCHEMA中获取有关分区的信息，它包含一个PARTITIONS表。

使用EXPLAIN可以确定给定的SELECT查询涉及分区表的哪些分区。EXPLAIN输出中的partitions列列出了将从匹配的分区中查询记录。

假设您创建了一个表trb1，并按如下方式填充：

mysql> CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    -> PARTITION BY RANGE(id)
    -> (
    -> PARTITION p0 VALUES LESS THAN (3),
    -> PARTITION p1 VALUES LESS THAN (7),
    -> PARTITION p2 VALUES LESS THAN (9),
    -> PARTITION p3 VALUES LESS THAN (11)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO trb1 VALUES
    -> (1, 'desk organiser', '2003-10-15'),
    -> (2, 'CD player', '1993-11-05'),
    -> (3, 'TV set', '1996-03-10'),
    -> (4, 'bookcase', '1982-01-10'),
    -> (5, 'exercise bike', '2004-05-09'),
    -> (6, 'sofa', '1987-06-05'),
    -> (7, 'popcorn maker', '2001-11-22'),
    -> (8, 'aquarium', '1992-08-04'),
    -> (9, 'study desk', '1984-09-16'),
    -> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

您可以看到在SELECT * FROM trb1；这样的查询中使用了哪些分区，如下所示：

mysql> EXPLAIN SELECT * FROM trb1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

在本例中，搜索所有四个分区。然而，当在查询中添加使用分区键的限制条件时，您可以看到只有那些包含匹配值的分区才会被扫描，如下所示:

mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

EXPLAIN还提供了使用的键和可能的键的信息：

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

如果使用EXPLAIN分区来检查针对未分区表的查询，则不会产生错误，但PARTITIONS列的值始终为NULL。EXPLAIN输出的rows列显示表中的总行数 。

分区维护
在MySQL 5.7中，许多表和分区维护任务可以使用SQL语句在分区表上执行。

对分区表的维护可以使用CHECK TABLE,OPTIMIZE TABLE, ANALYZE TABLE和REPAIR TABLE，它们支持分区表。

用户可以使用ALTER TABLE的一些扩展来直接在一个或多个分区上执行这种类型的操作，如下所示：
.重建分区。重建分区；这与删除存储在分区中的所有记录，然后重新插入它们具有相同的效果。这对于碎片整理很有用。
例如:

ALTER TABLE t1 REBUILD PARTITION p0, p1;

.优化分区。如果你从一个分区中删除了大量的行，或者你对一个有变长度行（即有VARCHAR、BLOB或TEXT列）的分区表做了很多修改，你可以使 用ALTER TABLE … OPTIMIZE PARTITION以回收任何未使用的空间并整理分区数据文件。
例如:

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

在一个给定分区上使用OPTIMIZE PARTITION，等价于在该分区上运行CHECK PARTITION,ANALYZE PARTITION和REPAIR PARTITION。

一些MySQL存储引擎，包括InnoDB，不支持单个分区优化；在这种情况下，ALTER TABLE … OPTIMIZE PARTITION会分析并重建整个表，并发出 适当的警告。（Bug #11751825, Bug #42822）使用ALTER TABLE … REBUILD PARTITION和ALTER TABLE … ANALYZE PARTITION 避免这个问题 。

.分析分区。这将读取并存储分区的键分布。
例如:
ALTER TABLE t1 ANALYZE PARTITION p3;

.修复分区。这将修复损坏的分区。
例如:

ALTER TABLE t1 REPAIR PARTITION p0,p1;

通常，当分区包含重复的键错误时，REPAIR PARTITION会失败。在MySQL 5.7.2及更高版本中，你可以使用ALTER IGNORE TABLE这个选项，在这 种情况下，所有因为重复键而无法移动的行都会从分区中移除（Bug #16900947）。

.检查分区。您可以检查分区是否有错误，其方式与对未分区的表使用CHECK TABLE的方式大致相同。
例如:

ALTER TABLE trb3 CHECK PARTITION p1;

这个命令将告诉您表t1的分区p1中的数据或索引是否损坏。如果是这种情况，请使用ALTER TABLE…REPAIR PARTITION修复分区。

正常情况下，当分区包含重复的键错误时，CHECK PARTITION会失败。在MySQL 5.7.2及更高版本中，你可以使用ALTER IGNORE TABLE这个选项， 在这种情况下，该语句将返回发现违反重复键的分区中每一行的内容。只报告表的分区表达式中列的值。(错误# 16900947)
刚才显示的列表中的每个语句还支持关键字ALL来代替分区名称列表。使用ALL会导致语句作用于表中的所有分区。

mysqlcheck和myisamchk对分区表不支持。

在MySQL 5.7中，你也可以使用ALTER TABLE … TRUNCATE PARTITION。这条语句可以用来删除一个或多个分区中的所有行，其方式与TRUNCATE TABLE删除表中的所有行差不多。

ALTER TABLE…TRUNCATE PARTITION ALL截断表中的所有分区。