技術共有|PROXYSQLに基づいて使用したことのないテーブルを検索

作者:RAY
DBA、9年のデータベース実戦経験、特にMySQL技術スタックに専念し、Oracle 11 g OCP、現天天鑑宝首席DBA、会社全体のデータアーキテクチャを設計し、データベースサービスの高安全を保障し、高利用と高性能に運行する.
オリジナル投稿
*爱可生开源社区出品,原创内容无许可使用,转载请联系小编,并注明出所.
前言
途中で本番ビジネス・ライブラリを受け取ると、廃棄テーブル、バックアップ・テーブル、アーカイブ・テーブルなど、「tmp」、「copy」、「backup」、日付などの接尾辞のテーブル名のような多くのテーブルの名前が表示される場合があります.もちろんこれらは最も直感的な判断であり、歴史的な遺留問題で発生したゴミ表が依然として多いかもしれないが、直接表の命名で整理できるかどうかは正確に判断できない.では、長い間整理しないと何か問題が発生するのだろうか.
まず、本番環境の基準に従って、これらのテーブルまたはテスト、または一時的にバックアップされたテーブルは保持されず、メタデータの分析時に追加のワークロードが増加します.
次に、一部のテーブルの体積が膨大すぎて、大量のストレージ空間を浪費している.最後に、これらの歴史が残した問題がタイムリーに解決されていないため、時間が経つにつれて問題はますます複雑になり、遡及しにくくなっている.
以上のように、どのテーブルが長い間アクセスしていないのかを統計する信頼できる技術手段が必要です.general logは統計できますが、本番データベースはこのパラメータをオンにしません.結局、ディスクの性能に影響します.
Proxysqlは優れたミドルウェアとしてstats_mysql_query_digestテーブルにはデフォルトですべてのデータベース要求が記録されており、このテーブルから使用されたことのないテーブルを分析できます(時間がかかるほど分析が正確になりますが、アクセスサイクルが長いテーブルもあります).
実装方法

フルメータ

を導出

mysql -uroot -pxxx -s -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA in ('test');" > table_name.txt

最後のアクセス時間と未使用のテーブル名

をループ印刷する.

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

最後のアクセス時間

を削除する.

sed '/:/d' unused.txt （     ）
sed -i '/:/d' unused.txt （    ）

削除後は、次の図のようになります.

tb2
tb3

手動で

を清掃できるかどうかを確認する.
このフィルタリングされたテーブルは、プロジェクト責任者とクリーンアップ可能かどうかを確認する必要があります.クリーンアップ可能であることを確認したり、直接物理的に削除されていないことを確認したり、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」を新規作成するか、テキスト編集ツールを使用して「'table 1','table 2'...」を一括生成し、逆に手動でコピーして貼り付けます.