mysql分区后查询总是扫描所有分区

Question

使用如下sql语句创建表：

create table t(
name varchar(20) not null,
age tinyint unsigned,
created_at datetime
) partition by range(month(created_at))(
partition p1 values less than(2),
partition p2 values less than(3),
partition p3 values less than(4),
partition p4 values less than(5),
partition p5 values less than(6),
partition p6 values less than(7),
partition p7 values less than(8),
partition p8 values less than(9),
partition p9 values less than(10),
partition p10 values less than(11),
partition p11 values less than(12),
partition p12 values less than(13)
);

插入数据查看分区的存储情况如下：

select partition_name, PARTITION_DESCRIPTION, PARTITION_EXPRESSION, table_rows from information_schema.partitions where table_name = 't'

但是在查询数据时总是需要查询所有分区

explain select * from t where created_at < '2018-04-01';

正常情况下不应该只查询前3个分区吗，为什么在这里会扫描所有分区呢？

Answer 1

根据mysql文档：This type of optimization can be applied whenever the partitioning expression consists of an equality or a range which can be reduced to a set of equalities, or when the partitioning expression represents an increasing or decreasing relationship. 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() function不可以。

Answer 2

这个应该是跟分区类型有关吧。印象中根据ID进行分区的时候不会全部扫描所有分区。记忆不是很清了，可以参考mysql官方文档。

Answer 3

emm...我测试过，V5.6，mod没问题，但是月份是不行。如第1楼的回答。

处理方式是：单独加字段保存月份，用这个月份字段做分区，查询加条件就可以。