网站推广的作用,聊城网站建设策划建设公司,优化设计七年级上册语文答案,wordpress会员vip在处理高并发的物联网平台或者其他日志密集型应用时#xff0c;数据库中的日志表往往会迅速增长#xff0c;数据量庞大到数百GB甚至更高#xff0c;严重影响数据库性能。如何有效管理这些庞大的日志数据#xff0c;特别是在不影响在线业务的情况下#xff0c;成为了一项技… 在处理高并发的物联网平台或者其他日志密集型应用时数据库中的日志表往往会迅速增长数据量庞大到数百GB甚至更高严重影响数据库性能。如何有效管理这些庞大的日志数据特别是在不影响在线业务的情况下成为了一项技术难题。本文将分享如何高效删除MySQL日志表中的历史数据并且按月分区管理日志以提升数据库的读写性能。 一 问题背景300GB 日志表如何清理 国庆假期节前检查数据库发现正在维护一张名为 ali_iot_log 的设备日志表数据量已经膨胀到380GB表中的历史数据占据了大量存储并且严重拖慢了查询和插入的速度。为了优化性能目标是删除只保留最近3个月的数据。
设备日记表占用385G,如下图 在MySQL 5.7中如果这张表没有分区直接使用delete条件删除历史数据会耗时很较长并且可能会锁表影响在线系统的使用。也不能直接清空表业务系统要求保留最近几个月的数据。那么如何快速高效地清理数据并为后续的日志管理奠定基础呢 二 常用的方案有哪些 方案一表分区推荐
步骤 确认表的结构和时间字段 确保日记表中有一个用于时间的字段例如created_at或date这是进行分区的基础。 备份数据 在进行任何结构性更改之前务必备份当前表以防止数据丢失。 mysqldump -u username -p your_database diary_table diary_table_backup.sql 添加分区 使用按月分区的方法。假设时间字段为created_at可以使用以下SQL语句为表添加分区 ALTER TABLE diary_table PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p0 VALUES LESS THAN (TO_DAYS(2024-01-01)), PARTITION p1 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p2 VALUES LESS THAN (TO_DAYS(2024-03-01)), ... PARTITION pN VALUES LESS THAN MAXVALUE ); 注意为每个月创建一个分区。可以根据具体需要调整分区策略。 删除旧分区 一旦表被分区可以通过丢弃早期的分区来快速删除大量数据而无需逐行删除。 ALTER TABLE diary_table DROP PARTITION p0, p1, p2, ..., pM; 其中p0到pM代表要删除的旧月份分区。
优点
高效分区操作是元数据级别的速度非常快。维护简单通过分区可以更方便地管理和查询数据。
缺点
预先规划如果表未分区初次分区可能需要时间和资源。复杂性增加需要持续关注在数据持续增长时,要为以后的时间节点手工增加分区。 方案二创建新表并交换适用已存有大量数据
如果表当前未分区且无法立即进行分区可以考虑以下步骤
步骤 创建新表 创建一个新的日记表结构与原表相同建议在创建时添加适当的索引以提高插入效率。 CREATE TABLE diary_table_new LIKE diary_table; 插入需要保留的数据 将最近一个月的数据插入新表。可以使用INSERT INTO ... SELECT语句。 INSERT INTO diary_table_new SELECT * FROM diary_table WHERE created_at DATE_SUB(NOW(), INTERVAL 1 MONTH); 注意此步骤可能需要一些时间具体取决于数据量和服务器性能。 重命名表 为确保操作的原子性使用RENAME TABLE进行表交换。 RENAME TABLE diary_table TO diary_table_old, diary_table_new TO diary_table; 删除旧表 确认新表数据无误后删除旧表以释放空间。 DROP TABLE diary_table_old;
优点
较快相比逐行删除创建新表并插入所需数据更快。简单操作步骤相对简单易于执行。
缺点
需要双倍存储空间需要额外的存储空间来容纳新表。短时间内锁定在重命名表期间可能会有短暂的表锁定影响应用程序。 方案三分批删除数据定时任务实现
如果上述方法不可行可以考虑分批删除数据以减少对数据库性能的影响。
步骤 确定删除的时间范围 例如删除超过一个月的数据。 分批执行删除操作 使用DELETE语句配合LIMIT逐步删除数据。 DELETE FROM diary_table WHERE created_at DATE_SUB(NOW(), INTERVAL 1 MONTH) LIMIT 100000; 重复执行上述语句直到满足删除条件。 优化删除过程 禁用二级索引如果适用在删除前暂时禁用非必要的索引可以提高删除速度删除后再重建索引。监控事务日志大小确保删除过程不会导致事务日志过大。
优点
控制删除速度避免一次性大量删除导致的性能问题。灵活性可以根据服务器负载调整删除批次大小和频率。
缺点
耗时较长相比直接删除分批删除可能需要更长的时间完成全部删除操作。复杂性增加需要编写脚本或程序来自动化批量删除过程。 三 在线实战操作 当前状态不可能影在线业务由于日志表主要是开发与运维查询问题时查看只要保证数据不丢失即可所以这里使用创建新表分区表比较合适。这个方案可以做到清理旧数据并保持系统稳定。
操作步骤
1 创建分区表并迁移数据 如果你的原始日志表还没有分区可以通过创建新表的方式实现按月分区。创建新表时我们可以设计好分区结构然后将旧表中的数据迁移到新表中。
-- 创建与原来结构一样的新表
CREATE TABLE ali_iot_log_n LIKE ali_iot_log; 创建表 如下图 -- 为新表分区按月份ALTER TABLE ali_iot_log_n
PARTITION BY RANGE (TO_DAYS(ctime)) (PARTITION p0 VALUES LESS THAN (TO_DAYS(2024-01-01)),PARTITION p1 VALUES LESS THAN (TO_DAYS(2024-02-01)),PARTITION p2 VALUES LESS THAN (TO_DAYS(2024-03-01)),PARTITION p3 VALUES LESS THAN (TO_DAYS(2024-04-01)),PARTITION p4 VALUES LESS THAN (TO_DAYS(2024-05-01)),PARTITION p5 VALUES LESS THAN (TO_DAYS(2024-06-01)),PARTITION p6 VALUES LESS THAN (TO_DAYS(2024-07-01)),PARTITION p7 VALUES LESS THAN (TO_DAYS(2024-08-01)),PARTITION p8 VALUES LESS THAN (TO_DAYS(2024-09-01)),PARTITION p9 VALUES LESS THAN (TO_DAYS(2024-10-01)),PARTITION p10 VALUES LESS THAN (TO_DAYS(2024-11-01)),PARTITION p11 VALUES LESS THAN (TO_DAYS(2024-12-01)),PARTITION pp0 VALUES LESS THAN (TO_DAYS(2025-01-01)),PARTITION pp1 VALUES LESS THAN (TO_DAYS(2025-02-01)),PARTITION pp2 VALUES LESS THAN (TO_DAYS(2025-03-01)),PARTITION pp3 VALUES LESS THAN (TO_DAYS(2025-04-01)),PARTITION pp4 VALUES LESS THAN (TO_DAYS(2025-05-01)),PARTITION pp5 VALUES LESS THAN (TO_DAYS(2025-06-01)),PARTITION pp6 VALUES LESS THAN (TO_DAYS(2025-07-01)),PARTITION pp7 VALUES LESS THAN (TO_DAYS(2025-08-01)),PARTITION pp8 VALUES LESS THAN (TO_DAYS(2025-09-01)),PARTITION pp9 VALUES LESS THAN (TO_DAYS(2025-10-01)),PARTITION pp10 VALUES LESS THAN (TO_DAYS(2025-11-01)),PARTITION pp11 VALUES LESS THAN (TO_DAYS(2025-12-01)),PARTITION pN VALUES LESS THAN MAXVALUE
);为表创建分区如下图 2 旧表数据插入新表重要 然后将旧表中的需要保留的数据插入新表这里一定要注意性能查询条件尽量小批量进行
INSERT INTO ali_iot_log_n SELECT * FROM ali_iot_log WHERE ctime BETWEEN 2024-09-01 00:00:00 AND 2024-09-10 23:59:59;我这边线上环境保存到数据库是使用队列负责消费的处理保存的可以停下数据都放在队列先后面启动会继续使用所以下面以条件10天数据量大概1亿条数据转存数据到新表如下图 每次迁移1亿条数据分三次即可以1个月数据了如果线上不间断运行保存就要考虑把最后某个时间点的数据不迁移先可以改完表后再迁移 。 3 重命名表快速切换到新表 确认数据迁移完成可以通过以下SQL实现旧表与新表的快速切换
RENAME TABLE ali_iot_log TO ali_iot_log_old, ali_iot_log_n TO ali_iot_log; 该操作是原子性的几乎不会影响业务的连续性因为RENAME TABLE 是元数据操作时间消耗极少但是数据多的情况下还是需要几分钟时间这里涉及30亿条数据量如下图 如果之前还有未迁完数据迁完旧表即可。
迁移完后检查数据确认没问题直接清空旧表即可
TRUNCATE TABLE ali_iot_log_old4 定期删除旧分区数据
通过分区表管理日志后删除旧数据变得非常简单。可以按月删除不再需要的分区以释放存储空间。
比如删除1个月的数据指定数据所在的分区即可
ALTER TABLE ali_iot_log DROP PARTITION p0;
这比直接删除数据要高效得多因为它只需要在元数据层面进行操作避免了全表扫描和锁定。 操作建议 分批迁移数据 对于大型表的数据迁移操作建议分批进行。可以通过LIMIT关键字对数据进行分块插入避免对数据库造成过大的压力。 选择低峰时段进行操作 大规模数据操作对性能有一定影响建议在业务低峰时段进行操作以最小化对用户的影响。 备份重要数据 在执行重大操作之前建议对数据库进行备份。即便是删除旧数据或重命名表这样看似简单的操作也应当做好应急恢复的准备。 四 总结
通过分区表的管理可以极大提升MySQL在处理大规模日志表时的性能特别是按时间维度划分分区后数据的清理和查询将更加高效。结合重命名表和按月分区的方式我们可以轻松应对日志表膨胀的问题而不会对在线业务造成严重影响。
