技术分享 | 基于 PROXYSQL 查找从未使用过的表 - 技术分享 - 新闻资讯 - 爱可生

技术分享 | 基于 PROXYSQL 查找从未使用过的表

发布时间:2021-04-19 浏览次数:288

关键字:MySQL数据库监控工具MySQL数据库热备份MySQL数据库容灾

作者:RAY


DBA,9 年数据库实战经验,尤其专注于 MySQL 技术栈,Oracle 11g OCP,现任天天鉴宝首席 DBA,负责设计公司整体数据架构与保障数据库服务高安全,高可用与高性能地运行。
本文来源:原创投稿
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。



前言

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

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

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

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

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


实现方法

  1. 导出全量表
mysql -uroot -pxxx -s -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA in ('test');" > table_name.txt
  1. 循环打印最后一次访问时间和从未使用过的表名称
for i in `cat table_name.txt`;do mysql -u admin -p'xxx' -h 127.0.0.1 -P6032 --default-auth=mysql_native_password -s -e "select ifnull((SELECT from_unixtime(last_seen+28800) FROM stats_mysql_query_digest where digest_text like '%${i}%'),'${i}');";done > ./unused.txt

查看文件输出如下:

2021-03-23 13:42:37
2021-03-23 14:43:56
tb2
tb3
  1. 删除最后一次访问时间
sed '/:/d' unused.txt (不修改文件)
sed -i '/:/d' unused.txt (修改文件)

删除后如下图所示:

tb2
tb3
  1. 人工确认是否可以清理

此筛选出的表需要与项目负责人确认是否可以清理,如果确认可以清理,也不是直接物理删除,需要 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' …”,反之手动复制粘贴即可。

关键字:MySQL数据库监控工具MySQL数据库热备份MySQL数据库容灾


相关推荐

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