インデックスの可視化と最適化：データベース管理の要諦
データベースのパフォーマンスを語る上で、インデックスの管理は避けて通れない最重要課題です。しかし、運用が長期間にわたると、どのテーブルにどのようなインデックスが存在し、それが本当に活用されているのかを把握し続けることは困難になります。本稿では、PostgreSQLとMySQL（InnoDB）を対象に、システムカタログや情報スキーマを駆使してインデックス情報を抽出・分析する手法を詳細に解説します。
インデックス情報の取得が必要な理由
データベース管理者がインデックスの一覧を定期的に確認すべき理由は、単なる構成管理に留まりません。主な目的は以下の3点に集約されます。
第一に「重複インデックスの排除」です。例えば、(A, B)という複合インデックスが存在する場合、(A)という単一インデックスは冗長であり、書き込み負荷を増大させるだけの「死に体」となります。第二に「断片化（フラグメンテーション）の把握」です。更新頻度の高いテーブルでは、インデックスページが断片化し、読み取り効率が低下します。第三に「未使用インデックスの特定」です。一度もスキャンされていないインデックスを特定することで、不要なインデックスを削除し、INSERT/UPDATEのオーバーヘッドを劇的に改善できます。
PostgreSQLにおけるインデックス情報の抽出
PostgreSQLでは、pg_catalog配下のシステムビューを結合することで、インデックスの定義だけでなく統計情報まで詳細に取得可能です。特にpg_stat_user_indexesビューは、インデックスが実際に活用されているかを判断する上で欠かせません。
以下のクエリは、特定のスキーマ内にある全インデックスの一覧と、それらが何回スキャンされたか（idx_scan）を取得するものです。
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.amname AS index_type,
idx.indisunique AS is_unique,
idx.indisprimary AS is_primary,
s.idx_scan AS scan_count,
pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM
pg_class t
JOIN
pg_index idx ON t.oid = idx.indrelid
JOIN
pg_class i ON idx.indexrelid = i.oid
JOIN
pg_am a ON i.relam = a.oid
JOIN
pg_stat_user_indexes s ON i.oid = s.indexrelid
WHERE
t.relkind = 'r'
AND t.relname NOT LIKE 'pg_%'
ORDER BY
t.relname, i.relname;
このクエリにより、どのインデックスが「お飾り」になっているかを一目瞭然にできます。idx_scanが極端に低い、あるいはゼロのインデックスは、削除候補として調査対象に加えるべきです。
MySQL（InnoDB）におけるインデックス情報の抽出
MySQLでは、information_schema.statisticsテーブルを利用するのが標準的です。MySQL 8.0以降では、sysスキーマが導入され、より直感的にインデックスの利用状況を把握できるようになっています。
例えば、未使用のインデックスを調査する場合、sys.schema_unused_indexesビューが非常に強力です。
-- 未使用のインデックスを一覧表示する
SELECT
object_schema,
object_name,
index_name
FROM
sys.schema_unused_indexes
WHERE
object_schema NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');
-- 特定テーブルのインデックス詳細情報を取得する
SELECT
TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
SEQ_IN_INDEX,
NON_UNIQUE,
CARDINALITY
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';
CARDINALITY（カーディナリティ）の値を確認することも重要です。この値が低いインデックスは、検索効率が悪く、オプティマイザがインデックスを使用しない可能性が高いことを示唆しています。
インデックス管理の実務アドバイス
DBAとしてインデックスを管理する際、以下の3つの運用ルールを徹底することを推奨します。
1. インデックス命名規則の厳格化
デフォルトのインデックス名（idx_table_colなど）を放置せず、一目で役割がわかる名前（idx_table_col1_col2_uniqueなど）を付与してください。これにより、トラブルシューティング時の可読性が向上します。
2. 定期的なスキャンカウントの監視
未使用インデックスの削除は、アプリケーションのデプロイサイクルや特定のバッチ処理の終了後に慎重に行う必要があります。数ヶ月間スキャンが発生していないことを確認した上で、まずは「不可視（Invisible）」インデックスとして設定し、数週間様子を見てから物理削除するプロセスを推奨します。
3. 複合インデックスの順序を最適化する
「左端一致の原則」を常に意識してください。頻繁に使用される検索条件の列をインデックスの先頭に配置することが、パフォーマンス向上の近道です。また、等価比較を行う列を不等号比較を行う列よりも前に配置するのがセオリーです。
インデックスの断片化とメンテナンス
データ量が増大するにつれ、インデックスのB-tree構造は歪んでいきます。特にUUIDのようなランダムな値を主キーにしている場合、ページの分割が頻発し、論理的なデータ順序と物理的な配置が乖離します。
PostgreSQLであればREINDEXコマンド、MySQLであればOPTIMIZE TABLEコマンドを定期的に発行することで、断片化を解消し、ディスク容量の最適化と検索速度の向上を同時に実現できます。ただし、これらのコマンドはテーブルのロックを伴う可能性があるため、必ずアクセスが少ない時間帯に実施してください。
まとめ
インデックスはデータベースの心臓部であり、適切に管理されなければシステムのパフォーマンスを著しく低下させる負債となります。今回紹介したクエリを活用し、現在のデータベース環境を「可視化」することから始めてください。
「何がどこにあり、何が使われているのか」という情報を常に把握しておくことは、パフォーマンスチューニングの第一歩です。定期的な棚卸しを行い、肥大化したインデックスを整理し、必要なインデックスにのみリソースを集中させる。この地道な積み重ねこそが、スケーラブルで堅牢なデータベース運用を実現する唯一の道です。本記事が、皆様のデータベース管理の一助となれば幸いです。
コメント