MySQL分区sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
#前提：分区字段需要主键或者唯一索引，或者表无主键。 #目的：dc_rte_plan_snapshot 表使用crt_tm(datetime)字段分区。 1.去除id主键（量大会比较耗时，800W数据耗时15分钟，供参考） ALTER TABLE `whjk2`.`zh_line_stat` DROP COLUMN `id`, DROP PRIMARY KEY; 2.分区 ALTER TABLE dc_rte_plan_snapshot PARTITION BY RANGE(TO_DAYS(crt_tm))( PARTITION p202012 VALUES LESS THAN (TO_DAYS('20190101')), PARTITION p202101 VALUES LESS THAN (TO_DAYS('20190201')), PARTITION p202102 VALUES LESS THAN (TO_DAYS('20190301')), PARTITION p202103 VALUES LESS THAN (TO_DAYS('20190401')), PARTITION p202104 VALUES LESS THAN (TO_DAYS('20190501')), PARTITION p202105 VALUES LESS THAN (TO_DAYS('20190601')), PARTITION p202106 VALUES LESS THAN (TO_DAYS('20190701')), PARTITION p202107 VALUES LESS THAN (TO_DAYS('20190801')), PARTITION p202108 VALUES LESS THAN (TO_DAYS('20190901')), PARTITION p202109 VALUES LESS THAN (TO_DAYS('20191001')), PARTITION p202110 VALUES LESS THAN (TO_DAYS('20191101')), PARTITION p202111 VALUES LESS THAN (TO_DAYS('20191201')), PARTITION p202112 VALUES LESS THAN (TO_DAYS('20200101')), PARTITION p202201 VALUES LESS THAN (TO_DAYS('20200201')), PARTITION p0 VALUES LESS THAN (MAXVALUE) ); 或 ALTER TABLE dc_rte_plan_snapshot PARTITION BY RANGE(crt_tm)( PARTITION p202012 VALUES LESS THAN ('2019-01-01'), PARTITION p202101 VALUES LESS THAN ('2019-02-01'), PARTITION p202102 VALUES LESS THAN ('2019-03-01'), PARTITION p202103 VALUES LESS THAN ('2019-04-01'), PARTITION p202104 VALUES LESS THAN ('2019-05-01'), PARTITION p202105 VALUES LESS THAN ('2019-06-01'), PARTITION p202106 VALUES LESS THAN ('2019-07-01'), PARTITION p202107 VALUES LESS THAN ('2019-08-01'), PARTITION p202108 VALUES LESS THAN ('2019-09-01'), PARTITION p202109 VALUES LESS THAN ('2019-10-01'), PARTITION p202110 VALUES LESS THAN ('2019-11-01'), PARTITION p202111 VALUES LESS THAN ('2019-12-01'), PARTITION p202112 VALUES LESS THAN ('2020-01-01'), PARTITION p202201 VALUES LESS THAN ('2020-02-01'), PARTITION p0 VALUES LESS THAN (MAXVALUE) ); |
