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 
); 

我需要按年份和月份打破表提高选择的时候，当我的日期之间选择它sholdn't在全表搜索它应该在相关的分区中进行搜索。我怎样才能做到这一点？

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

为什么分区不加快简单的查询

比方说，你有一个简单的SELECT，有一个很好的指标，如您指定PRIMARY KEY精确值。 （这称为“点查询”。）

情况1：非分区表。索引使用BTree结构。定位一百万行中的特定记录需要深入挖掘BTree，该深度约为3级。对于十亿行，它可能是5个级别。

案例2：分区表。分区将表分成多个表，每个表都有索引。找到一个特定的行首先必须找到特定的分区（子表），然后向下钻取该分区的较浅的BTree。

想想如果它（或许）从BTree中移除一个等级，但是增加了达到分区的额外努力。性能差异很小。目前尚不清楚你是赢或输。 （缓存，数据结构等使分析变得复杂）。

结论：对于点查询，假设您在非分区等价物上有合适的索引，分区永远不会有帮助。

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

最佳表结构（包括分区和索引）是

• 没有分区
• INDEX(action_time)

另一个注意：如果涉及多个分区，则SELECT将从eac中提取行（如果有的话） h分区（修剪后），将它们放在一起，然后可能必须对结果进行排序（取决于SELECT中的其他子句）。唉，在执行查询时没有并行性，所以分区变量更多地涉及，因此可能会更慢。