MySQL 按月分区不起作用

Question

我创建一个表

CREATE TABLE `part_tab` (
  `id` int NOT NULL,
  `use_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY HASH(MONTH(use_time)) PARTITIONS 12;

然后我使用EXPLAIN PARTIONS语法，但是MySQL似乎没有使用该分区，它仍然扫描整个表:

mysql> explain partitions select * from part_tab where use_time < '2013-02-01' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_tab
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
        Extra: Using where
1 row in set (0.00 sec)

如果我将where条件更改为等于，则使用分区:

 mysql> explain partitions select * from part_tab where use_time = '2013-02-01' \G
 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_tab
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

Answer 1

您的查询是:

select *
from part_tab
where use_time = '2013-02-01';

您的分区语句是:

PARTITION BY HASH(MONTH(use_time)) PARTITIONS 12;

您尝试做的事情称为“分区修剪”。根据documentation ,

Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. In addition, in MySQL 5.7, pruning can be applied for such tables when the partitioning expression uses the TO_SECONDS() function.

我不相信有任何方法可以直接在 MONTH() 而不是 YEAR() 上进行分区修剪。您可以将查询重写为:

select *
from part_tab
where month(use_time) = 1;

但是，我怀疑日期的范围分区是更好的解决方案。