もう悩まない！Mariadb - information_schema クエリ処理速度を向上させるための完全ガイド

2024-04-10

Mariadb - information schema queries 処理速度低下の原因と解決策

MariaDBでinformation_schemaクエリ処理速度が遅い場合、いくつかの原因が考えられます。本記事では、これらの原因と解決策について、分かりやすく解説します。

原因

information_schemaクエリ処理速度低下の主な原因は以下の3つです。

統計情報の更新頻度: MariaDBはデフォルトで15分ごとに統計情報を更新します。頻繁な更新はパフォーマンスに影響を与えます。
テーブルサイズ: information_schemaテーブルは、データベースの規模に合わせて大きくなる場合があります。大きなテーブルはクエリ処理速度を低下させます。
インデックス: information_schemaテーブルに適切なインデックスが作成されていない場合、クエリ処理速度が低下します。

解決策

以下の解決策を試すことで、information_schemaクエリ処理速度を向上させることができます。

統計情報の更新頻度調整: 統計情報の更新頻度を低くすることで、パフォーマンスを向上させることができます。ただし、更新頻度を低くすると、統計情報の精度が低下する可能性があります。
テーブルサイズ削減: 不要なデータや古いデータを削除することで、テーブルサイズを削減することができます。
インデックス作成: よく使用されるクエリに合わせて、information_schemaテーブルにインデックスを作成することで、クエリ処理速度を向上させることができます。

解決策の詳細

統計情報の更新頻度調整

以下のコマンドを実行することで、統計情報の更新頻度を変更できます。

SET GLOBAL innodb_stats_persistent=0;
SET GLOBAL innodb_stats_auto_recalc=OFF;

上記の例では、統計情報の更新を無効にしています。必要に応じて、更新頻度を調整してください。

テーブルサイズ削減

以下のコマンドを実行することで、information_schemaテーブルの不要なデータや古いデータを削除できます。

OPTIMIZE TABLE information_schema.tables;
OPTIMIZE TABLE information_schema.columns;

インデックス作成

CREATE INDEX idx_table_name ON information_schema.tables (table_name);
CREATE INDEX idx_column_name ON information_schema.columns (column_name);

上記は、tablesテーブルとcolumnsテーブルにインデックスを作成する例です。必要に応じて、他のテーブルにもインデックスを作成してください。

上記以外にも、information_schemaクエリ処理速度を向上させる方法はいくつかあります。詳細は、MariaDB公式ドキュメントを参照してください。

統計情報の更新頻度調整

-- 統計情報の更新を無効にする
SET GLOBAL innodb_stats_persistent=0;
SET GLOBAL innodb_stats_auto_recalc=OFF;

-- 統計情報の更新を有効にする
SET GLOBAL innodb_stats_persistent=1;
SET GLOBAL innodb_stats_auto_recalc=ON;

テーブルサイズ削減

-- 不要なデータや古いデータを削除する
OPTIMIZE TABLE information_schema.tables;
OPTIMIZE TABLE information_schema.columns;

インデックス作成

-- tables テーブルにインデックスを作成する
CREATE INDEX idx_table_name ON information_schema.tables (table_name);

-- columns テーブルにインデックスを作成する
CREATE INDEX idx_column_name ON information_schema.columns (column_name);