MySQL分区表：SQL语句显式指定分区

MySQL支持显式分区选择，当你已经知道要检查的一个或多个分区时，这种指定分区的方式可以大大加快查询速度。

MySQL 支持显式分区选择，这允许您在查询中显式地指定要访问的分区，从而优化查询性能。显式分区选择可以减少不必要的分区扫描，提高查询效率。

显式分区选择的基本语法

在 MySQL 中，可以使用 PARTITION 子句来显式指定要访问的分区。基本语法如下：

SELECT ...
FROM table_name
PARTITION (partition_list)
WHERE ...

其中 partition_list 是一个逗号分隔的分区列表。

示例：显式分区选择

我们继续使用之前的 range_columns 表，并插入了一些示例数据。假设我们要查询 hiredate 在 2015-12-01 到 2015-12-07 之间的数据，并显式指定涉及的分区。

创建表并插入数据

CREATE TABLE range_columns (
    id INT,
    hiredate DATETIME
)
PARTITION BY RANGE COLUMNS(hiredate) (
    PARTITION p1 VALUES LESS THAN ('2015-12-02'),
    PARTITION p2 VALUES LESS THAN ('2015-12-03'),
    PARTITION p3 VALUES LESS THAN ('2015-12-04'),
    PARTITION p4 VALUES LESS THAN ('2015-12-05'),
    PARTITION p5 VALUES LESS THAN ('2015-12-06'),
    PARTITION p6 VALUES LESS THAN ('2015-12-07'),
    PARTITION p7 VALUES LESS THAN ('2015-12-08'),
    PARTITION p8 VALUES LESS THAN ('2015-12-09'),
    PARTITION p9 VALUES LESS THAN ('2015-12-10'),
    PARTITION p10 VALUES LESS THAN ('2015-12-11')
);

INSERT INTO range_columns (id, hiredate)
VALUES (1, '2015-12-01 10:00:00'), -- 应该进入 p1
       (2, '2015-12-02 11:00:00'), -- 应该进入 p1
       (3, '2015-12-03 12:00:00'), -- 应该进入 p2
       (4, '2015-12-04 13:00:00'), -- 应该进入 p3
       (5, '2015-12-05 14:00:00'), -- 应该进入 p4
       (6, '2015-12-06 15:00:00'), -- 应该进入 p5
       (7, '2015-12-07 16:00:00'), -- 应该进入 p6
       (8, '2015-12-08 17:00:00'), -- 应该进入 p7
       (9, '2015-12-09 18:00:00'), -- 应该进入 p8
       (10, '2015-12-10 19:00:00'); -- 应该进入 p10

显式指定分区的查询

假设我们要查询 hiredate 在 2015-12-01 到 2015-12-07 之间的数据，并显式指定涉及的分区：

SELECT *
FROM range_columns
PARTITION (p1, p2, p3, p4, p5, p6)
WHERE hiredate >= '2015-12-01' AND hiredate <= '2015-12-07';

使用 EXPLAIN PARTITIONS 检查查询计划

为了验证查询是否正确地命中了指定的分区，可以使用 EXPLAIN PARTITIONS 语句：

EXPLAIN PARTITIONS SELECT *
FROM range_columns
PARTITION (p1, p2, p3, p4, p5, p6)
WHERE hiredate >= '2015-12-01' AND hiredate <= '2015-12-07';

输出结果解释

执行上述 EXPLAIN PARTITIONS 语句后，您将看到类似于以下的输出：

+----+-------------+----------------+--------+---------------+---------+---------+-------------------+---------+------------------------------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref               | rows    | Extra                              |
+----+-------------+----------------+--------+---------------+---------+---------+-------------------+---------+------------------------------------+
|  1 | SIMPLE      | range_columns  | ALL    | NULL          | NULL    | NULL    | NULL              | 10      | Using where                        |
|    |             |                |        |               |         |         |                   |         | Partitions p1, p2, p3, p4, p5, p6 |
+----+-------------+----------------+--------+---------------+---------+---------+-------------------+---------+------------------------------------+

在这个输出中，Extra 列显示了涉及的分区信息：

Partitions p1, p2, p3, p4, p5, p6

这意味着查询仅涉及了 p1 到 p6 这六个分区。

总结

使用显式分区选择可以帮助您减少不必要的分区扫描，提高查询效率。通过显式指定涉及的分区，您可以确保查询仅访问相关分区，从而提高性能。

注意事项

1. 正确选择分区：确保显式指定的分区列表正确无误，否则可能导致查询结果不完整。
2. 性能优化：显式分区选择主要用于优化查询性能，特别是在涉及多个分区的大表查询时。
3. 查询验证：使用 EXPLAIN PARTITIONS 语句验证查询是否正确地命中了指定的分区。

通过显式指定分区，您可以更好地控制查询的行为，并确保查询高效地利用分区结构。