MySQL大表数据量持续增长会带来查询效率下降、存储成本上升等一系列问题，传统的使用DELETE语句按条件删除数据的方式，不仅执行速度慢，还容易引发长时间锁表，影响线上业务正常运行。而使用表分区按天删除数据，能够将大表拆分为多个按日期划分的独立分区，删除过期数据时直接删除对应分区即可，无需逐行操作，大幅提升清理效率。
什么是MySQL表分区
MySQL表分区是将一张大表的数据按照特定规则拆分到多个独立的存储单元中，每个单元称为一个分区，分区对用户是透明的，查询时MySQL会自动定位到对应的分区获取数据。常见的分区类型包括范围分区、列表分区、哈希分区等，按天删除数据的场景通常使用范围分区（RANGE分区）。
表分区的优势
- 数据清理效率高：删除整个分区的速度远快于逐行删除数据，几乎不会影响数据库正常服务
- 查询性能提升：查询时如果带有分区键条件，MySQL可以只扫描对应的分区，减少数据扫描量
- 维护成本低：可以单独对某个分区进行备份、恢复、优化等操作，不影响其他分区数据
按天创建表分区的步骤
首先需要确认MySQL版本支持分区功能，MySQL 5.1及以上版本默认支持分区，创建分区表时需要注意分区键必须是主键或者唯一索引的一部分。
1. 创建按天分区的表
以下示例创建一个存储日志数据的表，按日期字段create_time按天进行范围分区：
-- 创建日志表，按天分区
CREATE TABLE log_table (
id BIGINT NOT NULL AUTO_INCREMENT,
content VARCHAR(255) NOT NULL,
create_time DATE NOT NULL,
PRIMARY KEY (id, create_time)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p20240501 VALUES LESS THAN (TO_DAYS('2024-05-02')),
PARTITION p20240502 VALUES LESS THAN (TO_DAYS('2024-05-03')),
PARTITION p20240503 VALUES LESS THAN (TO_DAYS('2024-05-04')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
上述代码中，TO_DAYS(create_time)是将日期转换为天数，作为分区的范围判断依据，每个分区对应一天的数据，p_future分区用于存储超出预设日期范围的数据。
2. 动态添加新分区
为了避免分区不足，需要定期添加新的按天分区，比如每天凌晨添加下一天的分区：
-- 添加2024-05-04的分区
ALTER TABLE log_table ADD PARTITION (
PARTITION p20240504 VALUES LESS THAN (TO_DAYS('2024-05-05'))
);
按天删除分区的操作方法
当数据过期需要清理时，直接删除对应的分区即可，操作会立即生效，且不会产生大量日志：
-- 删除2024-05-01的分区，即删除当天所有数据 ALTER TABLE log_table DROP PARTITION p20240501;
执行上述语句后，p20240501分区对应的所有数据会被直接删除，分区本身也会被移除，整个过程耗时极短，不会对业务造成影响。
操作注意事项
- 分区键选择：必须选择日期类型的字段作为分区键，且需要包含在主键或者唯一索引中，否则创建分区表会失败
- 分区数量控制：单张表的分区数量不建议过多，通常控制在1000个以内，避免影响查询优化器的效率
- 数据迁移：如果现有大表没有分区，需要先创建分区表，再将原表数据迁移到分区表中，迁移过程可以选择业务低峰期进行
- 分区备份：删除分区前如果需要保留数据，可以先导出对应分区的数据再进行删除操作
分区清理效果对比
以下是常规DELETE删除和分区删除的效果对比：
| 删除方式 | 1000万数据删除耗时 | 锁表时间 | 产生日志量 |
|---|---|---|---|
| DELETE按条件删除 | 约30分钟 | 全程锁表 | 大量binlog日志 |
| 分区DROP删除 | 小于1秒 | 几乎无锁表 | 极少日志 |
通过对比可以看出，使用表分区按天删除数据的方式，在清理效率、对业务的影响程度上都有明显优势，非常适合大表数据定期清理的场景。