关于 Mysql 分区问题

This topic created in 2454 days ago, the information mentioned may be changed or developed.

1.有一张心跳表 tb_heartbeat （ id int, tid varchar(64), hbTime dateTime);
2.保存最近七天的数据，每天凌晨删除七天前的数据
3.创建按星期几的分区（为了不频繁创建和 drop 分区）。
ALTER TABLE tb_heartbeat PARTITION BY RANGE(WEEKDAY(hbTime))(
PARTITION p1 VALUES less than (1),
PARTITION p2 VALUES less than (2),
PARTITION p3 VALUES less than (3),
PARTITION p4 VALUES less than (4),
PARTITION p5 VALUES less than (5),
PARTITION p6 VALUES less than (6),
PARTITION p7 VALUES less than (MAXVALUE)
);
4.但是在删除某一天的数据，它会遍历所有分区。
EXPLAIN PARTITIONS DELETE from tb_heartbeat where hbtime < '2019-08-29 23:59:59' and hbtime > '2019-08-29 00:00:00'
会遍历 partitions：p1,p2,p3,p4,p5,p6,p7
5.这里关于分区裁剪是没效果吗？
谢谢

Supplement 1 · Aug 30, 2019

大家摸鱼的时候，帮我也看下这个问题，
如果我用 to_days 一起都 ok。

ALTER TABLE tb_heartbeat PARTITION BY RANGE(TO_DAYS(hbTime))(
PARTITION p20190823 VALUES LESS THAN (to_days('20190823')),
PARTITION p20190824 VALUES LESS THAN (to_days('20190824')),
PARTITION p20190825 VALUES LESS THAN (to_days('20190825')),
PARTITION p20190826 VALUES LESS THAN (to_days('20190826')),
PARTITION p20190827 VALUES LESS THAN (to_days('20190827')),
PARTITION p20190828 VALUES LESS THAN (to_days('20190828')),
PARTITION p20190829 VALUES LESS THAN (to_days('20190829')),
PARTITION p20190830 VALUES LESS THAN (to_days('20190830'))
);

这里 to_days 和 weekday 有什么区别？

No Comments Yet

partition values Less than