今回は、データベースパフォーマンスの要となるSQLインデックスについて、インデックスとはなんぞやというところから、実践的な内容まで詳しく解説していきます。特に、インデックスのチューニングの再構築について、詳しく書いてみました。
インデックスとは？データベースの電話帳のような存在
インデックスは、データベースにおける検索を高速化するための重要な機能です。たとえば、50万件のデータが格納されているテーブルから特定のレコードを探す場合、インデックスがないと全件検索（フルテーブルスキャン）が必要になります。これは、本の索引がない状態で特定の項目を探すようなものです。
インデックスを作成すると、データベースエンジンは指定されたカラムの値とその物理的な位置情報を別途保持します。これにより、目的のデータに素早くアクセスすることが可能になります。
インデックスの種類と特徴
1. B-treeインデックス
– 最も一般的なインデックス型
– 等価検索、範囲検索に優れている
– ほとんどのDBMSでデフォルトのインデックス型
2. ハッシュインデックス
– 完全一致検索に特化
– 範囲検索には不向き
– メモリ使用量が比較的少ない
3. 複合インデックス
– 複数のカラムを組み合わせたインデックス
– 検索条件に応じて効果的に設計する必要がある
インデックスの作成方法
基本的なインデックスの作成は以下のSQL文で行います：
CREATE INDEX index_name ON table_name (column_name);
-- 複合インデックスの場合
CREATE INDEX index_name ON table_name (column1, column2, column3);実際の例として、ユーザーテーブルにメールアドレスのインデックスを作成する場合：
CREATE INDEX idx_users_email ON users (email);効果的なインデックス設計のポイント
1. カーディナリティを考慮する
– カーディナリティとは、カラムの値の種類の多さを表します
– 性別のような値の種類が少ないカラムは、単体でのインデックスの効果が低い
– ユーザーIDやメールアドレスなど、一意性の高いカラムが適している
2. 検索条件との整合性
– WHERE句やJOIN条件で頻繁に使用されるカラムを優先
– ORDER BY、GROUP BYで使用されるカラムも考慮
3. 更新頻度の考慮
– 頻繁に更新されるカラムへのインデックスは要注意
– インデックスが多すぎると更新処理のオーバーヘッドが増加
インデックスのチューニング手法
1. 実行計画の確認
EXPLAIN SELECT * FROM users WHERE email = 'example@email.com';実行計画を確認することで、インデックスが適切に使用されているか確認できます。特に注目すべき点は：
– インデックススキャンが行われているか
– スキャンされる行数は適切か
– 使用されているインデックスは意図したものか
2. インデックスの選択性の確認
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;選択性が0.1以上（10%以上）あれば、そのカラムへのインデックスは効果的と考えられます。
インデックスの再構築と保守
インデックスは時間の経過とともに断片化していきます。これは、データの追加、更新、削除が繰り返されることで発生する現象です。断片化が進むと、以下のような問題が発生します：
検索パフォーマンスの低下
ディスク容量の非効率な使用
メモリキャッシュの効率低下
そこで、定期的なインデックスの再構築が必要になります。以下、主要なデータベースごとの再構築方法を詳しく解説します。
MySQL/MariaDBでのインデックス再構築
インデックスの断片化状態確認：
SHOW TABLE STATUS LIKE 'table_name';判断基準：
Data_free値がテーブルサイズの20%以上：再構築を推奨
Index_lengthがData_lengthの2倍以上：インデックスの肥大化が疑われるため再構築を検討
テーブル分析：
ANALYZE TABLE table_name;インデックスの再構築：
-- 方法1: OPTIMIZE TABLE
OPTIMIZE TABLE table_name;
-- 方法2: ALTER TABLE
ALTER TABLE table_name FORCE;
-- 方法3: DROP & CREATE
DROP INDEX index_name ON table_name;
CREATE INDEX index_name ON table_name (column_name);注意点：
OPTIMIZE TABLEはテーブルをロックするため、サービス時間外での実行を推奨
大規模なテーブルの場合、一時的に大量のディスク容量が必要
PostgreSQLでのインデックス再構築
インデックスの断片化状態確認：
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes;判断基準：
bloat（膨張率）が30%以上：再構築を強く推奨
bloat（膨張率）が20-30%：監視を強化
インデックスサイズがテーブルサイズの50%以上：再構築を検討
index_scans（スキャン回数）が0：未使用インデックスの可能性があるため、必要性を再検討
インデックスの再構築：
-- 方法1: REINDEX（排他ロック発生）
REINDEX INDEX index_name;
REINDEX TABLE table_name;
REINDEX DATABASE database_name;
-- 方法2: CREATE INDEX CONCURRENTLY（並行処理可能）
CREATE INDEX CONCURRENTLY new_index_name
ON table_name (column_name);
DROP INDEX CONCURRENTLY old_index_name;SQL Serverでのインデックス再構築
断片化状態の確認：
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID('table_name'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;判断基準（Microsoft推奨）：
5%未満の断片化：対応不要
5-30%の断片化：インデックスの再構成（REORGANIZE）を推奨
30%以上の断片化：インデックスの再構築（REBUILD）を推奨
page_countが1000以上の場合は、上記の基準をより重視
インデックスの再構築：
-- 断片化が30%以上の場合：再構築
ALTER INDEX index_name ON table_name REBUILD;
-- 断片化が5-30%の場合：再構成
ALTER INDEX index_name ON table_name REORGANIZE;インデックス再構築の自動化
大規模なシステムでは、インデックスの再構築を自動化することが推奨されます。以下は、自動化する際の主要なポイントです：
スケジューリング
システム負荷の低い時間帯を選択
バッチ処理との競合を避ける
バックアップスケジュールとの調整
監視と通知
-- MySQL例：断片化率の監視クエリ
SELECT table_name,
data_length,
index_length,
table_rows
FROM information_schema.tables
WHERE table_schema = 'your_database';再構築時の注意点
インデックスを再構築する際の確認事項を纏めました。再構築する際のチェックポイントとして確認する事をお勧めします。
ディスク容量の確保：
インデックス再構築時は、一時的に元のインデックスと同じ容量が必要
大規模なテーブルの場合、元のインデックスサイズの2〜3倍の空き容量を推奨
テンポラリテーブルスペースの使用量も考慮が必要
RAID構成やストレージの性能も確認
バックアップの実施：
再構築直前の完全バックアップを必ず取得
リストア手順の確認と検証
ポイントインタイムリカバリが必要な場合はログバックアップも考慮
バックアップの保管期間の一時的な延長を検討
依存システムへの影響確認：
関連する全てのアプリケーションの洗い出し
レプリケーション環境での影響確認
バッチジョブのスケジュール確認
外部システム連携への影響調査
パフォーマンスへの配慮
段階的な再構築の実施：
優先度の高いインデックスから順次実施
テーブルサイズに応じた実行時間の見積もり
1回あたりの再構築対象を適切にサイズ分割
メンテナンス時間枠内で完了可能な範囲を慎重に判断
リソース使用量の監視：
CPU使用率の監視と制限設定
メモリ使用量の監視（特にバッファプール）
I/O負荷の監視と制御
ネットワーク帯域の使用状況確認（特に分散環境の場合）
セッション数の制限：
アクティブセッション数の制御
長時間トランザクションの監視
デッドロックの監視強化
コネクションプールの設定調整
まとめ
SQLインデックスは、データベースのパフォーマンスを最適化する上で非常に重要な要素です。適切なインデックス設計と運用により、アプリケーションの応答性を大幅に改善することができます。
ただし、インデックスは「諸刃の剣」であることを忘れないでください。過剰なインデックスはかえってパフォーマンスを低下させる可能性があります。実際の使用パターンを分析し、必要なインデックスを適切に設計・運用することが重要です。
定期的なモニタリングと保守を行い、システムの要件や使用パターンの変化に応じて適切に見直しを行うことで、長期的に安定したパフォーマンスを維持することができます。
コメント