mysql分区不起作用

Question

我有一个表，其中的字段是 action_time 主键，类型是 datetime

我尝试在分区上打破它

ALTER TABLE foo PARTITION BY RANGE (MONTH(action_time))
(
PARTITION p01 VALUES LESS THAN (02) ,
PARTITION p02 VALUES LESS THAN (03) ,
PARTITION p03 VALUES LESS THAN (04) ,
PARTITION p04 VALUES LESS THAN (05) ,
PARTITION p05 VALUES LESS THAN (06) ,
PARTITION p06 VALUES LESS THAN (07) ,
PARTITION p07 VALUES LESS THAN (08) ,
PARTITION p08 VALUES LESS THAN (09) ,
PARTITION p09 VALUES LESS THAN (10) ,
PARTITION p10 VALUES LESS THAN (11) ,
PARTITION p11 VALUES LESS THAN (12) ,
PARTITION p12 VALUES LESS THAN (13) ,
PARTITION pmaxval VALUES LESS THAN MAXVALUE 
);

在 phpmyadmin 中我看到有行的分区但是当我执行

explain partitions select * from foo where action_time between '2017-01-01 20:34:08' and '2017-01-21 20:34:08';

或

explain partitions select * from foo where action_time > '2017-01-01 20:34:08' && action_time < '2017-01-21 20:34:08'

它命中所有分区(p01、p02、p03、p04、p05、p06、p07、p08、p09、p10、p11、p12、pmaxval)

我做错了什么？

我也用这种方式试过同样的结果

ALTER TABLE foo
  PARTITION BY RANGE(  YEAR(action_time) )
  SUBPARTITION BY HASH( MONTH(action_time) )
  SUBPARTITIONS 12 (
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p2027 VALUES LESS THAN (2028),
    PARTITION p2028 VALUES LESS THAN (2029),
    PARTITION p2029 VALUES LESS THAN (2030),
    PARTITION pmax VALUES LESS THAN MAXVALUE
  );

我需要按年和月拆分表以改进选择时间，当我在日期之间进行选择时它不会在整个表中搜索它应该在相关分区中搜索。我该怎么做？

Answer 1

您已经找到了 PARTITIONing 实际上无用的另一个原因。

假设您指定了 BETWEEN '2015-11-05' AND '2017-02-02'。它需要命中哪些分区？所有这些。

假设您指定了 BETWEEN '2015-11-05' AND '2016-02-02'。它需要命中哪些分区？ 4、但是绕圈不够聪明。所以它(我认为)会击中所有目标。

只有有限数量的模式(MONTH() 不是其中之一)分区将“正确处理”。

要使 BY RANGE(some date) 起作用，您只能使用 BY RANGE(TO_DAYS(date))(以及其他一些)。但是你必须每个月(或无论如何)创建一个新分区。并且，可选地，DROP 最旧的分区。

现在由于另一个原因，您的计划可能没用。您希望从分区中获得什么好处？也许性能？可能不会给您带来任何性能优势。让我们看看您的问题，以便我解释原因。

一个简单的

SELECT ...
    WHERE date >= '...'
      AND date  < '...' + INTERVAL 20 DAY

使用INDEX(date) 时和分区一样快。可能更快。

如果 WHERE 中还有其他内容，那么一切都会改变。

My PARTITION blog

为什么 PARTITIONing 不能加速简单查询

假设您有一个简单的 SELECT，它具有非常好的索引，例如您为 PRIMARY KEY 指定了准确的值。 (这称为“点查询”。)

案例一:非分区表。索引使用 BTree 结构。要在一百万行中定位特定记录，需要向下钻取 BTree，这大约有 3 层深。对于十亿行，它可能是 5 个级别。

案例二:分区表。分区将表拆分为多个表，每个表都有索引。定位特定行首先必须定位特定分区(子表)，然后向下钻取该分区的较浅 BTree。

想想它是否(可能)从 BTree 中删除一个级别，但增加了到达分区的额外工作量。性能差异很小。而且，你是赚了还是亏了，还不清楚。 (缓存、数据结构等，使这个分析变得复杂。)

结论:对于点查询，分区无济于事，前提是您在非分区等价物上有合适的索引。

您的特定查询是一个简单的“范围”查询:WHERE action_time BETWEEN ... AND ...

最优的表结构(包括分区和索引)是

• 没有分区
• INDEX(action_time)

另一个注意事项:如果涉及多个分区，SELECT 将从每个分区(修剪后)获取行(如果有的话)，将它们放在一起，然后可能必须对结果进行排序(取决于 SELECT 中的其他子句)。可惜在查询的执行中没有并行性，因此分区变体涉及更多，因此可能更慢。