この記事の要点

インデックスは大量更新・削除を繰り返すと断片化し、サイズ肥大とアクセス低速化を招く MySQL InnoDB は ALTER TABLE ... ENGINE=INNODB; または OPTIMIZE TABLE でテーブルごと再構築 PostgreSQL は REINDEX INDEX/TABLE/DATABASE。CONCURRENTLY でオンライン実行可 Oracle は ALTER INDEX ... REBUILD ONLINE MySQL 5.6+ は Online DDL で多くの操作がノンブロッキング。ただし大容量テーブルは別途領域必要 本番では必ずレプリカ・メンテナンス時間・ストレージ余裕を確保。pt-online-schema-change / gh-ost の利用も検討

なぜ再作成が必要か — 断片化のメカニズム

B-Tree インデックスは更新・削除を繰り返すと:

• 論理断片化: ページ内の行が論理順 ≠ 物理順 になり、Range Scan で I/O 増
• 領域断片化: ページ内に削除済の空きができ、密度が下がる（fill factor 低下）
• サイズ肥大: 同じ件数でもインデックスサイズが本来の 1.5〜3 倍に

結果: 「Index Range Scan が遅い」「キャッシュヒット率低下」「バックアップ時間増」。

MySQL (InnoDB) の再作成

-- 方法 1: ALTER TABLE ... ENGINE=INNODB（テーブルごと再構築）
-- → 全インデックスが再構築される
ALTER TABLE orders ENGINE=INNODB;

-- 方法 2: OPTIMIZE TABLE（InnoDB では ALTER ENGINE と同等）
OPTIMIZE TABLE orders;

-- 方法 3: 個別インデックスを DROP & CREATE
ALTER TABLE orders DROP INDEX idx_user_id, ADD INDEX idx_user_id (user_id);

-- 方法 4: MySQL 8.0+ の Online DDL（デフォルトでオンライン）
ALTER TABLE orders ENGINE=INNODB, ALGORITHM=INPLACE, LOCK=NONE;
-- ALGORITHM: INPLACE (オンライン) / COPY (旧来)
-- LOCK: NONE (読み書き OK) / SHARED (読のみ) / EXCLUSIVE (停止)

注意: ALTER TABLE ... ENGINE=INNODB は内部的にテーブルをコピーするため、テーブルと同じだけの空きディスクが必要です。1TB のテーブルなら 1TB の追加領域が要ります。

PostgreSQL の REINDEX

-- 個別インデックス
REINDEX INDEX idx_orders_user_id;

-- テーブル内の全インデックス
REINDEX TABLE orders;

-- スキーマ内の全テーブル
REINDEX SCHEMA public;

-- データベース全体
REINDEX DATABASE myapp;

-- オンライン実行（PG 12+）。ロック時間を最小化
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
REINDEX TABLE CONCURRENTLY orders;

-- 使用状況・サイズの確認（再作成判断）
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 断片化（bloat）の推定
SELECT * FROM pgstattuple('idx_orders_user_id');

Oracle のオンライン REBUILD

-- オンライン再構築（DML 継続可）
ALTER INDEX idx_orders_user_id REBUILD ONLINE;

-- パラレル実行
ALTER INDEX idx_orders_user_id REBUILD ONLINE PARALLEL 4;

-- 別表領域に移動しながら再構築
ALTER INDEX idx_orders_user_id REBUILD TABLESPACE indx_ts ONLINE;

-- 統計情報も同時に更新
ALTER INDEX idx_orders_user_id REBUILD ONLINE COMPUTE STATISTICS;

-- 使用状況の収集を開始
ALTER INDEX idx_orders_user_id MONITORING USAGE;
SELECT * FROM v$object_usage;

SQL Server の再構築

-- 個別インデックス
ALTER INDEX idx_orders_user_id ON dbo.orders REBUILD;

-- オンライン再構築（Enterprise Edition）
ALTER INDEX idx_orders_user_id ON dbo.orders REBUILD WITH (ONLINE = ON);

-- テーブルの全インデックス
ALTER INDEX ALL ON dbo.orders REBUILD;

-- REORGANIZE（軽量、ページ並べ替えのみ。30% 未満の断片化向け）
ALTER INDEX idx_orders_user_id ON dbo.orders REORGANIZE;

-- 断片化率の確認
SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name,
       ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

判断基準: REBUILD か REORGANIZE か（SQL Server の目安）

断片化率	推奨アクション
0 〜 10 %	放置 OK
10 〜 30 %	REORGANIZE（オンライン・軽量）
30 % 超	REBUILD（再構築）

本番運用での注意点

• メンテナンス時間を確保: 大規模テーブルは数時間〜数日かかることもある
• ストレージ余裕: テーブル + 既存インデックスと同等のサイズが追加で必要
• レプリカで先に試す: ロック時間・処理時間を計測
• バイナリログの容量: 大量行コピーで binlog が肥大化、レプリ遅延に注意
• 監視を仕込む: 進捗 (SHOW ENGINE INNODB STATUS) / 接続数 / IO 待ち
• 失敗時のロールバック計画: 既存インデックスを別名で残し、新規構築後に切替

無停止再構築ツール

• pt-online-schema-change (Percona Toolkit): トリガベース。本番でロック最小
• gh-ost (GitHub): バイナリログベース。トリガなし
• MySQL 8.0 INSTANT DDL: 列追加など瞬時に完了する操作も増えた

# pt-online-schema-change で再構築
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=myapp,t=orders \
  --execute

# gh-ost で再構築（MySQL 5.7+）
gh-ost \
  --host=master.example.com --user=admin --password=xxx \
  --database=myapp --table=orders \
  --alter="ENGINE=InnoDB" \
  --execute

定期メンテナンスの自動化

-- MySQL の例: イベントスケジューラで月次再構築
CREATE EVENT IF NOT EXISTS monthly_optimize
ON SCHEDULE EVERY 1 MONTH STARTS '2025-06-01 03:00:00'
DO
  BEGIN
    OPTIMIZE TABLE orders;
    OPTIMIZE TABLE logs;
  END;

-- PostgreSQL は cron + REINDEX
-- 0 3 1 * * psql -d myapp -c "REINDEX TABLE CONCURRENTLY orders;"

FAQ

Q: ANALYZE と REINDEX の違い
A: ANALYZE は統計情報を更新するだけ（プラン改善）、REINDEX は実際にインデックスを物理的に作り直す。両方必要なら REINDEX → ANALYZE。

Q: REINDEX CONCURRENTLY は安全?
A: PG 12+ で導入。読み書き継続可能だが、失敗すると _ccnew 等の中間オブジェクトが残ることがある。pg_class を確認して手動削除。

Q: 再作成後に逆に遅くなった
A: 統計情報が古いまま。ANALYZE TABLE (MySQL) / ANALYZE (PG) / DBMS_STATS.GATHER_TABLE_STATS (Oracle) で統計を取り直す。