MySQLデータベース上のどのテーブルが変更されているかを知ることは、静的なテーブルを知る上で非常に有用です。
そのためには、DML (Data Manipulation Language)操作ー特に更新、追加、削除を調べる必要があります。MySQL には、この情報を収集するためのさまざまなメカニズムがあります。
本ブログでは、2つのオプションについて、どこに該当の情報が格納されているか、情報が最新であることを確認する方法について説明し、実際のアプリケーションに提供するアイデアを紹介します。
オプション 1: mysql.innodb_table_stats
MySQLには mysql.innodb_table_stats という統計テーブルが用意されており, last_update 列が含まれています。この列は、InnoDBテーブルでDML操作が行われると自動的に更新されます。
例として、t1という名前のテーブルがあるとします。:
mysql> select * from mysql.innodb_table_stats where table_name="t1"; +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | test | t1 | 2025-06-10 12:21:31 | 1 | 1 | 0 | +---------------+------------+---------------------+--------+----------------------+--------------------------+
この t1 に対してDML操作をいくつか実行してみます。操作後に同じクエリを再度実行するとlast_update のタイムスタンプが更新されています:
mysql> select * from mysql.innodb_table_stats where table_name="t1"; +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | test | t1 | 2025-06-10 12:23:24 | 1 | 1 | 0 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.001 sec)
上記のとおり、last_update タイムスタンプは最近のDML操作が反映されます。
もちろん、クエリのwhere句を変更し、order byステートメントを追加することで、このselect文ですべてのテーブル、あるいは特定のスキーマのすべてのテーブルを参照するように変更することもできます。
オプション 2: information_schema.tables
テーブルの変更に関する情報を確認するもう１つのオプションは information_schema.tables ビューです。
このビューにはUPDATE_TIME 列が含まれています。 パーティション化されていないInnoDBテーブルの場合、UPDATE_TIME は最後に行われた UPDATE、INSERT、DELETE のタイムスタンプを示しています。MVCC (Multi-Version Concurrency Control)では、タイムスタンプはコミット時刻を反映し、これが最終更新時刻とみなされます。ただし、これらのタイムスタンプは永続的ではないことに注意してください。サーバーの再起動やInnoDBデータディクショナリキャッシュからテーブルが削除されるとタイムスタンプは保持されません。
information_schema.tablesを見ると、DML操作を行った後でもUPDATE_TIME列がNULL もしくは想定よりも古い値になっている場合があります。
例えば、以下のような場合です。
mysql> select TABLE_NAME, CREATE_TIME, UPDATE_TIME from information_schema.tables where table_name="t1"; +------------+---------------------+---------------------+ | TABLE_NAME | CREATE_TIME | UPDATE_TIME | +------------+---------------------+---------------------+ | t1 | 2025-06-10 07:17:29 | 2025-06-10 07:17:37 | <<< 想定よりも古い値 +------------+---------------------+---------------------+ 1 row in set (0.003 sec)
その理由は、information_schema_stats_expiry システム変数にあります。この変数のデフォルト値は、86400秒 (24時間)となっておりinformation schema のキャッシュされた統計情報は1日1回しか更新されません。リアルタイム更新が必要な場合、その頻度は少なくなります。
現在の設定値を確認するには:
show variables like ‘information_schema_stats_expiry’ などのクエリを実行します;
+---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 86400 | +---------------------------------+-------+
デフォルトは24時間ですが、この設定は0から最大1年まで変更できます。常に最新の統計情報をストレージエンジンから直接取得してキャッシュされた値を回避したい場合は、information_schema_stats_expiry を0に設定してください。
set persist information_schema_stats_expiry=0;
注意事項: この変数の変更内容を有効にするにはセッションを切断して再接続する必要があります。
変数を変更して再接続すると、すぐにクエリ実行結果に反映されるようになります。
actorテーブルでの実行例を見てみましょう。
select table_schema, table_name, engine, create_time, update_time from information_schema.tables where table_name='actor'; +--------------+------------+--------+---------------------+---------------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | CREATE_TIME | UPDATE_TIME | +--------------+------------+--------+---------------------+---------------------+ | sakila | actor | InnoDB | 2025-06-05 15:22:55 | 2025-06-05 15:37:27 | +--------------+------------+--------+---------------------+---------------------+ update sakila.actor set first_name='sissi' where first_name='sissy'; Query OK, 1 row affected (0.0060 sec)
設定前は更新後24時間となっていたため、UPDATE_TIME がすぐに更新されない場合がありましたが、information_schema_stats_expiry を0に設定すると、再接続後にすぐに更新が反映されます:
詳細は下記ドキュメントを参照してください。
https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_information_schema_stats_expiry
select table_schema, table_name, engine, create_time, update_time from information_schema.tables where table_name='actor'; +--------------+------------+--------+---------------------+---------------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | CREATE_TIME | UPDATE_TIME | +--------------+------------+--------+---------------------+---------------------+ | sakila | actor | InnoDB | 2025-06-05 15:22:55 | 2025-06-10 13:52:50 | +--------------+------------+--------+---------------------+---------------------+
繰り返しになりますが、上記と同様にselect文のwhere句を変更してを変更して全てのテーブルや特定のスキーマのテーブルなどを参照できるようにしたり、order by句を追加することもできます。
どちらのオプションを選択すべきか？
mysql.innodb_table_stats と information_schema.tablesはどちらもDML操作を監視する方法を提供しています。それぞれの長所と短所を比較してみます。
オプション 1の長所: mysql.innodb_table_stats
- 最新情報へのアクセス: このテーブルは通常DML操作に関して即時に更新されます。
オプション1の短所: mysql.innodb_table_stats
- スキーマへのアクセス権限が必要: システムテーブルに対するクエリ実行権限が必要です。
- 将来変更される可能性: データディクショナリ内のテーブルフォーマットは将来のバージョンで変更される可能性があります。
- データディクショナリに対する権限変更の可能性: 今後のリリースでアクセス権限が変更される可能性があります。
オプション2の長所: information_schema.tables
- 標準に準拠: SQL:2003 標準に準拠しているため、互換性の問題が発生しにくい設計となっています。
- 読取り専用ビュー: information_schema テーブルは読取り専用ビューとして定義されており、誤った操作(INSERT、UPDATE、DELETE)が行われないよう保護されています。
- アクセシビリティ: ほとんどのINFORMATION_SCHEMA テーブルには、各MySQLユーザーがアクセス権限を持っていますが、アクセス可能なオブジェクトに対応する行のみ参照できます。
- クエリ: 必要な情報を1つのクエリで取得することができます。
オプション2の短所: information_schema.tables
- information_schema_stats_expiryの設定が必要: この変数を適切に設定する必要があります。
- DBAの対応が必要になる可能性: information_schema_stats_expiryを変更する権限がない場合、DBAにその設定値を変更するように依頼する必要があります。
なぜ重要か
MySQL上のテーブルのデータ変更の傾向を理解することで以下のような洞察を得ることができます。
- パフォーマンス最適化:
- 変更頻度の高いテーブルや頻繁にアクセスされる箇所を特定し、最適化の重点対象とします。
- 変更パターンに基づいてデータベースの拡張や容量確保を計画できます。
- データ整合性とセキュリティ:
- 異常または不審なデータ更新の検出ができます。
- バックアップやエクスポート方針:
- 変更頻度の高いテーブルについてはエクスポートの頻度を優先的に高くし、変更頻度の低いテーブルについてはバックアップ頻度を調整することも検討できます。
ガイドライン
一般的な監視や基準への準拠が重視される場面では、information_schema.tables (オプション2) が適している場合が多いです。ただし、information_schema_stats_expiry 変数をニーズに合った間隔に設定することが重要です。
この値を0に設定すると、システム変数を調整する必要はなく、より安定した標準準拠のアプローチを維持しつつ、即時に最新情報を取得することが可能です。一方で、必要な権限があり、システム変数を調整せずに最速での更新が必要な場合は、mysql.innodb_table_stats を使用するのも非常に効果的です。
いつもMySQLをご利用いただきありがとうございます!