最近优化了个使用mysql 数据库的项目,其中有个日志表,比较大了,导致查询的时候比以前慢多了,且数据每日还在增加10万+,
为了业务稳定,在不修改业务逻辑的情况下优化日志系统,让查询效率增加,做了表分区处理,
话不多说,直接上干货:
1 创建分区表的sql语句:
CREATE TABLE user_log (
user_id BIGINT,
user_type VARCHAR(50),
op_code VARCHAR(50),
content TEXT,
add_time DATETIME,
PRIMARY KEY (user_id, add_time)
)
PARTITION BY RANGE (YEAR(add_time) * 100 + MONTH(add_time)) (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION p202303 VALUES LESS THAN (202304),
-- 继续添加更多月份的分区
PARTITION pMax VALUES LESS THAN MAXVALUE
);
解释:
PARTITION BY RANGE (YEAR(add_time) * 100 + MONTH(add_time)):表示按年月进行范围分区。
每个 PARTITION 块代表一个月,如 p202301 表示 2023 年 1 月份的数据分区,VALUES LESS THAN (202302) 表示该分区包含小于 2023 年 2 月的数据。
PARTITION pMax VALUES LESS THAN MAXVALUE 是一个 catch-all 分区,用于捕获所有不符合前面条件的数据。
注意事项:
1 确保主键或唯一键包含分区字段。
2 根据实际情况需要定期添加新的分区,否则当新月份数据到来时会无法插入。
2 已经有数据的表如何添加分区
步骤:
1 确保原表没有数据
如果表 user_log 中已经有大量数据,那么直接使用 ALTER TABLE 添加分区会比较复杂且耗时。建议先清空表或将数据迁移至临时表,然后再添加分区。
2 使用 ALTER TABLE 添加分区:
这里假设 user_log 表中数据量较少或者已被清空。
修改表sql 语句:
ALTER TABLE user_log
PARTITION BY RANGE (YEAR(add_time) * 100 + MONTH(add_time)) (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION p202303 VALUES LESS THAN (202304),
-- 根据需要继续添加更多月份的分区
PARTITION pMax VALUES LESS THAN MAXVALUE
);
3 实例示范:
1. 备份当前数据(可选)
CREATE TABLE user_log_backup AS SELECT * FROM user_log;
2. 清空原表中的数据(可选)
TRUNCATE TABLE user_log;
3. 使用 ALTER TABLE 添加分区
ALTER TABLE user_log
PARTITION BY RANGE (YEAR(add_time) * 100 + MONTH(add_time)) (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION p202303 VALUES LESS THAN (202304),
-- 根据需要继续添加更多月份的分区
PARTITION pMax VALUES LESS THAN MAXVALUE
);
4. 将备份数据导入新分区表(可选)
如果之前执行了数据备份和清空操作,现在可以把数据导入新分区表。
INSERT INTO user_log (user_id, user_type, op_code, content, add_time)
SELECT user_id, user_type, op_code, content, add_time
FROM user_log_backup;
5. 验证数据
确保数据完整且准确地从备份表复制到了新分区表中。
6. 清理备份表(可选)
DROP TABLE user_log_backup;