Open6

Railsプロジェクトにおける未使用インデックスの検出方法

shimadamashimadama

1. MySQL sysスキーマの活用

stgで確認。

MySQLのパフォーマンススキーマが有効になっていることを確認。

SHOW VARIABLES LIKE 'performance_schema';

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

OFFが返ってきた場合は、MySQLの設定ファイルで有効にしてサーバーを再起動する必要あり。

SELECT
  object_name,
  index_name
FROM
  sys.schema_unused_indexes
WHERE
  object_schema = 'データベース名'
ORDER BY
  object_name,
  index_name;
shimadamashimadama

2. インデックス使用統計の確認

インデックス使用に関するより詳細な情報を得る：

SELECT
	t.TABLE_SCHEMA,
	t.TABLE_NAME,
	s.INDEX_NAME,
	s.COLUMN_NAME,
	t.TABLE_ROWS AS table_rows,
	s.CARDINALITY,
	ROUND( IF(t.TABLE_ROWS > 0, s.CARDINALITY / t.TABLE_ROWS * 100, 0), 2) AS selectivity,
	i.COUNT_READ,
	i.COUNT_WRITE,
	IF(i.COUNT_READ > 0, i.COUNT_WRITE / (i.COUNT_READ + IF(i.COUNT_READ = 0, 1, 0)) * 100, 0) AS change_to_read_ratio
	FROM
		information_schema.STATISTICS s
		JOIN information_schema.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
	LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage i ON i.OBJECT_SCHEMA = t.TABLE_SCHEMA AND i.OBJECT_NAME = t.TABLE_NAME AND i.INDEX_NAME = s.INDEX_NAME
WHERE
	t.TABLE_SCHEMA = 'データベース名' AND t.TABLE_ROWS > 0
ORDER BY
	change_to_read_ratio DESC,
	table_rows DESC;
shimadamashimadama

MySQLインデックス分析の各列の説明

クエリ結果の各列についての説明：

列名 説明
TABLE_SCHEMA データベース名。
TABLE_NAME テーブルの名前。例: versions など。
INDEX_NAME インデックスの名前。例: PRIMARY (主キー)、index_job_prefectures_on_job_id など。
COLUMN_NAME インデックスに含まれる列名。複合インデックスの場合は複数行に分かれて表示されます。
table_rows テーブル内の推定行数。これは厳密な値ではなく、統計情報に基づく推定値です。
CARDINALITY インデックス内のユニーク値の推定数。これも統計情報に基づく推定値です。
selectivity 選択性の値。計算式は (CARDINALITY / table_rows * 100)。この値が高いほど、インデックスは効率的です。
COUNT_READ Performance Schemaから取得した、インデックスの読み取り回数。現在はNULLが表示されています。
COUNT_WRITE Performance Schemaから取得した、インデックスの書き込み回数。現在はNULLが表示されています。
change_to_read_ratio 書き込みと読み取りの比率。書き込みが多い場合、インデックスのメンテナンスコストが高くなります。

選択性（selectivity）の確認方法

選択性はselectivity列で確認できます。これは以下の計算式で求められています：

ROUND( IF(t.TABLE_ROWS > 0, s.CARDINALITY / t.TABLE_ROWS * 100, 0), 2)

この値はインデックスの有効性を示す重要な指標です：

選択性の範囲 解釈
90-100% 非常に効率的なインデックス。ほぼユニークな値を持ちます。PrimaryKeyやユニークキーは通常この範囲です。
20-90% 中程度に効率的。多くの場合、このレベルの選択性でもクエリプランナーは使用します。
5-20% 効率が低くなります。大量のデータがある場合はテーブルスキャンより効率的かもしれません。
0-5% 非常に効率が低いインデックス。クエリプランナーに使用されない可能性が高いです。
0%に近い値 ほぼ無意味なインデックス。カテゴリデータや真偽値などでよく見られます。

例えば、データからは以下のような選択性の低いインデックスが見られます：

テーブル名 インデックス名 列名 選択性(%)
agent_job_search_query_prefectures idx_qprofessions_on_prefecture_id prefecture_id 0.00
agent_job_search_queries index_agent_job_search_queries_on_agency_id agency_id 0.02
job_summary_reports index_job_summary_reports_on_target_date target_date 0.02

これらのインデックスは、シーケンシャルスキャンと比較してもあまり効果的でない可能性があります。ただし、外部キー制約のために必要な場合もあります。

一方、高い選択性を持つインデックスの例：

テーブル名 インデックス名 列名 選択性(%)
agent_job_search_query_job_type_professions PRIMARY id 99.99
versions PRIMARY id 100.00
job_page_views PRIMARY id 100.00

これらは主キーとして高い選択性を持ち、効率的なインデックスとして機能しています。

選択性の値を使用して、特に大きなテーブルにある低選択性インデックスを特定し、それらが本当に必要かどうかを検討することができます。

shimadamashimadama

selectivityが低いインデックスの評価と対応

selectivityが低いインデックスの削除については、単純に値だけで判断するのではなく、以下の観点から総合的に評価する必要があります：

判断基準 説明
インデックスの目的 外部キー制約やユニーク制約（unique: true）のために作成されたインデックスは、選択性が低くても削除すべきではありません。これらはデータ整合性のために必要です。
テーブルサイズ 小さなテーブル（数千行以下）では、インデックスの有無による実行速度の差はわずかなため、選択性が低くても問題にならないことが多いです。
クエリパターン そのインデックスがアプリケーションで頻繁に使用されるクエリをサポートしている場合、選択性が低くても保持すべき場合があります。
複合インデックス 複合インデックスの一部として含まれる列は、その位置によって実効的な選択性が変わります。先頭の列の選択性が重要です。

削除を検討すべきケース

以下の条件をすべて満たす場合、削除を検討できます：

  1. selectivityが1%未満
  2. 大規模テーブル（数十万行以上）に存在する
  3. 外部キー制約やユニーク制約に関連していない
  4. Performance Schemaで使用頻度が低いことが確認できている（現在はNULLのため確認不可）

具体的なアプローチ

  1. まずPerformance Schemaを有効にして、実際の使用状況データを収集
  2. 使用頻度が低く、選択性も低いインデックスを特定
  3. テスト環境でインデックスを削除し、主要クエリのパフォーマンスをモニタリング
  4. 問題がなければ本番環境でも削除を実施

注意点

インデックスの削除は、特に大規模なテーブルでは重要なパフォーマンス改善につながる可能性がありますが、アプリケーションの特定のクエリパターンを考慮せずに行うと、予期せぬパフォーマンス低下を引き起こす可能性があります。

まずはPerformance Schemaを有効にして実際の使用状況を把握することが、安全な最適化の第一歩です。

shimadamashimadama

selectivityが５%以下のものだけ抽出

SELECT
	ROUND( IF(t.TABLE_ROWS > 0, s.CARDINALITY / t.TABLE_ROWS * 100, 0), 2) AS selectivity,
	t.TABLE_SCHEMA,
	t.TABLE_NAME,
	s.INDEX_NAME,
	s.COLUMN_NAME,
	t.TABLE_ROWS AS table_rows,
	s.CARDINALITY,
	i.COUNT_READ,
	i.COUNT_WRITE,
	IF(i.COUNT_READ > 0, i.COUNT_WRITE / (i.COUNT_READ + IF(i.COUNT_READ = 0, 1, 0)) * 100, 0) AS change_to_read_ratio
	FROM
		information_schema.STATISTICS s
		JOIN information_schema.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
	LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage i ON i.OBJECT_SCHEMA = t.TABLE_SCHEMA AND i.OBJECT_NAME = t.TABLE_NAME AND i.INDEX_NAME = s.INDEX_NAME
WHERE
	t.TABLE_SCHEMA = 'データベース名' AND t.TABLE_ROWS > 0
HAVING
	selectivity <= 5
ORDER BY
	selectivity ASC,
	change_to_read_ratio DESC,
	table_rows DESC;