假设您想在MySQL表中存储信用卡交易/订单等数据。由于数据量巨大,您可能希望对其进行分区。例如,您可以根据交易月份对表进行分区。
PARTITION BY RANGE ( month(transactionDate))
(PARTITION p0 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (4) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (7) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (9) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (11) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (12) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (13) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
如果您的数据量很大,您也可以按周进行操作。
现在,您必须定期清理旧数据。这就是删除和截断之间的区别所在。您最初拥有的分区列表为p0、p1...p12。
当您删除一个分区p1时,列表变为p0、p2、p3、p4...p12。因此,二月和三月的数据实际上都会进入p2。
但是,当您执行截断时,p1仍然保持完好无损,但数据被驱逐。因此,列表仍然是p0、p1...p12。