已dayofmonth来分区，查询时无效？

[asdmusic8]

Server version: 5.7.18 MySQL Community Server (GPL)

CREATE TABLE `p0_05` (
`aid` bigint(20) NOT NULL AUTO_INCREMENT,
`atime` datetime NOT NULL,
`num` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`aid`,`atime`),
KEY `atime` (`atime`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (dayofmonth(atime))
(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (31) ENGINE = MyISAM) */;

insert into `p0_05` (`atime`,`num`) values('2017-05-01 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-02 10:00:00',1);
insert into `p0_05` (`atime`,`num`) values('2017-05-03 10:00:00',1);


mysql> explain partitions select *
-> from `p0_05`
-> where atime BETWEEN '2017-05-01' and
-> '2017-05-02';
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | p0_05 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30 | range | atime | atime | 5 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+-------------------------------------------------------------------------------------------------------------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

为什么 他查询了全部分区
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30

[ACMAIN_CHM]

你的分区是 dayofmonth(atime)

[zjcxc]

看起来是分区列上使用了函数导致的问题 如果你用 =， 会发现没有问题，只扫描确定的分区 用 between, >,< 之类的范围比较，则会扫描所有分区 另外，就算用分区表达式做条件，也会扫描所有分区（也就是这个 dayofmonth(atime) =1 也扫描所有分区），这点似乎应该改进一下

[asdmusic8]

to_days 就可以 ，所以不单单是 使用了函数导致