MySQL 如何清理海量数据 - 文章 - 开发者社区

问题描述

在数据日常管理/巡检中，应该监控数据量的增长情况，对于一些冷数据，应该及时进行归档/清理，避免数据库越来越大，最终导致性能问题。同时从业务的角度来说，不同类型的数据应该放置到最合适的数据库中，如一些监控数据我们可以使用 Elasticsearch，如果日常分析任务较重，可以使用数据仓库。

问题分析

删除大表中大量数据时需要特别注意，有两方面的考量：

产生大量的 binlog，可能导致磁盘空间急剧下降，业务停摆。
对实例性能产生影响，影响线上业务。

因此，在删除大量数据行时，应该分批次，缓慢的执行删除动作，让这个过程尽量平滑，减少对实例的影响。

解决方案

pt-archiver

pt 工具不只是能实现数据归档，还能实现平滑的删除大量数据，具体使用如下：

1.使用 --dry-run，查看生成的删除语句是否符合预期

(base) [root@ip-10-0-0-22 ~]# pt-archiver --source h=111.62.119.86,P=3306,u=rudonx,p='ASDFasdf@2020',D=sbtest,t=sbtest4 --purge --charset=utf8mb4 --where "id <= 1000000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics  --dry-run

SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest4` FORCE INDEX(`PRIMARY`) WHERE (id <= 1000000) AND (`id` < '17196993') ORDER BY `id` LIMIT 200

SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `sbtest`.`sbtest4` FORCE INDEX(`PRIMARY`) WHERE (id <= 1000000) AND (`id` < '17196993') AND ((`id` >= ?)) ORDER BY `id` LIMIT 200

DELETE FROM `sbtest`.`sbtest4` WHERE (`id` = ?)

2.去掉 --dry-run 之后真实执行语句

(base) [root@ip-10-0-0-22 ~]# pt-archiver --source h=111.62.119.86,P=3306,u=rudonx,p='ASDFasdf@2020',D=sbtest,t=sbtest4 --purge --charset=utf8mb4 --where "id <= 1000000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics  
TIME                ELAPSED   COUNT
2022-03-15T10:17:39       0       0
2022-03-15T10:17:45       5     200
2022-03-15T10:17:51      12     400

2022-03-15T10:22:23     284    8800
2022-03-15T10:22:30     290    9000
2022-03-15T10:22:36     297    9200
2022-03-15T10:22:40     301    9311
Started at 2022-03-15T10:17:39, ended at 2022-03-15T10:22:42
Source: A=utf8mb4,D=sbtest,P=3306,h=111.62.119.86,p=...,t=sbtest4,u=rudonx
SELECT 9311
INSERT 0
DELETE 9311
Action        Count       Time        Pct
deleting       9311   249.2245      82.38
sleep            47    47.0157      15.54
select           48     1.8596       0.61
commit           47     1.2743       0.42
other             0     3.1441       1.04

参数说明：

--purge：使用此选项意味着清除数据而不是进行归档
--progress：每多少行打印进度信息
--limit：每次 select 多少行进行删除
--sleep：指定休眠时间
--txn-size 指定每多少行进行一次事务提交
--statistics：打印执行时间的统计信息

注意：建议在测试环境中充分进行测试，然后应用到生产环境中，同时建议在业务低峰期执行此操作。

参考文档

[1] https://www.percona.com/doc/percona-toolkit/3.0/pt-archiver.html

如果您有其他问题，欢迎您联系火山引擎技术支持服务