基于 PROXYSQL 查找从未使用过的表

基于 PROXYSQL 查找从未使用过的表

发布时间:2022-04-06 浏览次数:2579

前言

当你半路接手一个生产业务库时,可能会发现其中很多的表命名很像废弃表、备份表或者归档表,比如以 “tmp”、“copy”、“backup” 和日期等等后缀的表名。当然这些都是最直观的判断,可能依然会有很多因为历史遗留问题产生的垃圾表,然而直接通过表命名无法准确判断是否可以清理,那么如果长时间不清理会带来什么问题吗?

首先按照生产环境的标准,这些或测试,或临时备份的表都不应该保留,并且在分析元数据时会增加额外的工作量。

其次有些表的体积过于庞大,浪费大量存储空间,最后因为这些历史遗留问题没有及时解决,随着时间的流逝导致问题会越来越复杂,越来越难以追溯。

综上所述,我需要一种可靠的技术手段去统计到底哪些表长时间没有访问过,这时有些人会说 general log 可以统计,但是生产数据库不会开启此项参数,毕竟比较影响磁盘的性能。

Proxysql 作为一款优秀的中间件,stats_mysql_query_digest 表默认记录着所有的数据库请求,可以从此表分析出从未使用过的表(时间越久分析越准确,毕竟不排除有些表的访问周期比较长)。

实现方法

1、导出全量表

 PROXYSQL 查找

2、循环打印最后一次访问时间和从未使用过的表名称

 PROXYSQL 查找

查看文件输出如下:

 PROXYSQL 查找

3、删除最后一次访问时间

 PROXYSQL 查找

删除后如下图所示:

 PROXYSQL 查找

4、人工确认是否可以清理

此筛选出的表需要与项目负责人确认是否可以清理,如果确认可以清理,也不是直接物理删除,需要 rename 统一的后缀名,并且再观察一段时间是否有人反馈因为访问不到表产生的问题,如果不再出现任何问题,那么就可以放心地清理了。

附批量生成 rename 语句:

SELECT
 CONCAT( 'ALTER TABLE ', TABLE_NAME, ' RENAME ', TABLE_NAME, '_unused;' ) 
FROM
 INFORMATION_SCHEMA.TABLES 
WHERE
table_schema IN ( 'unused' ) AND  TABLE_TYPE='BASE TABLE';

SELECT
 CONCAT( 'ALTER TABLE ', TABLE_NAME, ' RENAME ', TABLE_NAME, '_unused;' ) 
FROM
 INFORMATION_SCHEMA.TABLES 
WHERE

table_name IN ( 'table1', 'table2' …) AND  TABLE_TYPE='BASE TABLE';

注:如果筛选出的表过多,可以新建一个数据库 “unused” 包含所有未使用的表,或者使用文本编辑工具批量生成 “'table1', 'table2' …”,反之手动复制粘贴即可。

相关推荐

产品试用 产品试用
400-820-6580 免费电话