MySQLでINFORMATION_SCHEMA.key_column_usageのパフォーマンス低下を解決する方法

INFORMATION_SCHEMA.key_column_usageが遅くなる原因

MySQLでINFORMATION_SCHEMA.key_column_usageを参照するクエリを実行した際、処理に長い時間がかかってしまうことがあります。このパフォーマンス低下の主な原因は、システム変数innodb_stats_on_metadataがデフォルトで有効（ON）になっていることです。この設定が有効な場合、INFORMATION_SCHEMAのメタデータテーブルにアクセスするたびにInnoDBが統計情報を更新するため、大量のディスクI/Oが発生し、クエリが遅くなります。

解決方法：GLOBAL変数を無効化する

以下のように、GLOBAL変数をオフにすることでパフォーマンスを改善できます。

SET global innodb_stats_on_metadata = 0;

この設定により、INFORMATION_SCHEMA.key_column_usageへアクセスする際の統計情報の更新がスキップされ、クエリの実行時間が大幅に短縮されます。

実際の実行例

まず変数を無効化し、そのあとにkey_column_usageを参照するクエリを実行してみましょう。

mysql> SET global innodb_stats_on_metadata = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT REFERENCED_TABLE_NAME, TABLE_NAME, COLUMN_NAME, CONSTRAINT_SCHEMA
    -> FROM INFORMATION_SCHEMA.key_column_usage;

実行結果は以下の通りです。

この例では、674行のレコードをわずか0.28秒で取得できました。設定変更前と比べて、体感できるほどの速度改善が期待できます。

設定を永続化させるための注意点

SET GLOBALによる変更は一時的なものであり、MySQLサーバーの再起動を行うと元の値に戻ってしまいます。再起動後も設定を維持したい場合は、設定ファイル（my.cnf または my.ini）の[mysqld]セクションに以下の1行を追加してください。

innodb_stats_on_metadata = 0

これにより、サーバー起動時から常に最適な状態でINFORMATION_SCHEMA系テーブルへアクセスできるようになります。