MySQL Delete cannot free space with partition instead

As the amount of data in the database becomes larger, if you want to erase a month's data and delete it with the delete command, MySQL will not free up space, must defragment, or rebuild the table with the modification engine to free up space, but because of the large amount of data, each rebuild takes 10-12 hours, too long, Think of using MySQL partition to solve this problem, by deleting the partition, you can delete the data file directly, it can also free up space.

Partitioning knowledge I'll just go ahead and say two.

1. The partition field does not have a null value because the null value defaults to the garbage partition, so the query scans the partition more than once.

2. The query criteria is best to take the partition field, otherwise the performance will be very low, need to sweep through all the partitions.

Adding a partition script

Engine=innodb PARTITION by RANGE COLUMNS (date) (

PARTITION p201410 VALUES less than (20141031),

PARTITION p201411 VALUES less than (20141131),

PARTITION p201412 VALUES less than (20141231),

PARTITION p201501 VALUES less than (20150131),

...........................

PARTITION pcatchall VALUES less THAN MAXVALUE

);

Delete Partition

ALTER table Table DROP PARTITION p201010;

Merging partitions

ALTER TABLE b REORGANIZE PARTITION p1,p2 into (P12);

Split partition

ALTER TABLE b REORGANIZE PARTITION p1,p2 into (P12);

Now, partition splitting and merging are not available, so write first.

MySQL Delete cannot free space with partition instead