Table users が1000万行を超えたころ、スローダウンが現れ始めた。EXPLAINをあちこちで実行し、インデックスをどんどん追加していったが、ある日振り返ってみると、クエリが一度も使わないインデックスを何十個も抱えていることに気づいた。そこで初めて、未使用インデックスの整理に本腰を入れることにした。
未使用インデックスがなぜ問題なのか？
インデックスはメリットしかないと思いがちだ。クエリが速くなり、検索も楽になる。しかし、作成したインデックスにはそれぞれコストが伴う：
- 書き込み速度の低下：
INSERT、UPDATE、DELETEのたびに、MySQLは関連するすべてのインデックスを更新しなければならない。不要なインデックスが5つあれば、書き込みのたびに5回の余分な更新が発生する。 - ディスク容量の無駄：インデックスは実データと同様にディスクに保存される。不要なインデックスがあると、50GBのテーブルがさらに10〜20GB余分に占有することもある。
- バッファプールの無駄遣い：InnoDBのバッファプールはインデックスページをキャッシュする。不要なインデックスが本当に必要なデータの領域を奪い、キャッシュヒット率が低下する。
- クエリオプティマイザへの悪影響：オプティマイザはより多くの実行プランを計算しなければならず、場合によっては誤ったインデックスを選択することもある。
数千万行規模の本番データベースでは、これは見過ごせない問題だ。
準備：Performance Schemaを有効にする
MySQLはPerformance Schemaを通じてインデックスの使用状況を追跡する。まず有効になっているか確認しよう：
SHOW VARIABLES LIKE 'performance_schema';結果がOFFの場合は、設定ファイル/etc/mysql/mysql.conf.d/mysqld.cnf（またはディストリビューションによってmy.cnf）で有効にする：
[mysqld]
performance_schema = ONその後、MySQLを再起動する：
sudo systemctl restart mysql注意：Performance Schemaにはメモリに若干のオーバーヘッド（〜5〜10%）があるが、ほとんどの本番サーバーでは問題ない。MySQL 5.7以降はデフォルトで有効になっている。
インデックスルックアップのインストゥルメンテーションがアクティブになっていることを確認する：
-- インデックスインストゥルメンテーションを確認する
SELECT * FROM performance_schema.setup_consumers
WHERE NAME LIKE '%statements%';
-- 未有効の場合は有効化する
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history_long';未使用インデックスの見つけ方
方法1：sys Schemaを使う（最速）
MySQL 5.7.7以降ではsys.schema_unused_indexesビューが標準で用意されている。クエリ一発で結果が得られる：
SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
ORDER BY object_schema, object_name;典型的な出力はこんな感じだ：
+---------------+-------------+------------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------------+
| myapp | users | idx_users_phone |
| myapp | orders | idx_orders_ref |
| myapp | logs | idx_logs_level |
+---------------+-------------+------------------+idx_users_phoneが見つかった。このインデックスは電話番号検索機能が存在していた頃に作成されたものだが、その機能はとっくに廃止されているのにインデックスはそのまま残り、静かにリソースを消費し続けていた。長年続くプロジェクトでよく見られる問題だ。
方法2：Performance Schemaから詳細なクエリを実行する
処理の優先順位付けのために行数情報も確認したい場合は、performance_schemaとinformation_schemaを組み合わせる：
SELECT
t.TABLE_SCHEMA AS db_name,
t.TABLE_NAME AS table_name,
s.INDEX_NAME AS index_name,
s.COLUMN_NAME AS column_name,
t.TABLE_ROWS AS approx_rows
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t
ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
WHERE s.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
AND s.INDEX_NAME != 'PRIMARY'
AND CONCAT(s.TABLE_SCHEMA, '.', s.TABLE_NAME, '.', s.INDEX_NAME) NOT IN (
SELECT CONCAT(object_schema, '.', object_name, '.', index_name)
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star > 0
)
ORDER BY t.TABLE_ROWS DESC;このクエリは推定行数も返すので、影響が大きいテーブルのインデックスを優先して削除できる。
方法3：冗長インデックス（重複インデックス）を検出する
未使用インデックスの他に、冗長インデックスも存在する。これは別のインデックスがすでにカバーしているため、MySQLが必要としないインデックスだ：
SELECT *
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');典型的な例：(user_id, created_at)の複合インデックスがあるのに、(user_id)の単独インデックスを追加してしまうケース。複合インデックスがそのプレフィックスをカバーしているため、後者は完全に不要だ。
削除前の分析と判断
リストにインデックス名が表示されても、すぐに削除してはいけない。インデックスをdropする前に必ず確認するチェックリストを紹介する：
1. 観測期間が十分かを確認する
performance_schemaのデータはMySQLが再起動するたびにリセットされる。サーバーが2日前に再起動されたばかりなら、月次レポートに使うインデックスが誤って「未使用」リストに入ってしまうことがある。
-- MySQLの稼働時間を確認する（秒単位）
SHOW GLOBAL STATUS LIKE 'Uptime';
-- 2592000秒 = 30日少なくとも30日、理想的には四半期ごとに実行されるバッチジョブをカバーするために90日間待つようにしている。
2. アプリケーションコードで検索する
# コードベースでインデックス名を検索する（FORCE INDEXで使われている可能性があるため）
grep -r "idx_users_phone" /var/www/myapp/
# ハードコードされたインデックスヒントを検索する
grep -r "FORCE INDEX\|USE INDEX\|IGNORE INDEX" /var/www/myapp/ --include="*.php"3. 削除前にインデックス定義をバックアップする
-- バックアップのためにCREATE TABLEステートメントをエクスポートする
SHOW CREATE TABLE users\G出力をファイルに保存しておく。後でインデックスを再作成する必要が生じたとき、構造を推測せずにすぐに使えるコマンドが手元にある。
4. ALGORITHM=INPLACEを使って安全にインデックスをdropする
MySQL 5.6以降では、トラフィックがある状態でテーブルのロックを避けるためにALGORITHM=INPLACE, LOCK=NONEを使用する：
-- インデックスを1つ削除する
ALTER TABLE users
DROP INDEX idx_users_phone,
ALGORITHM=INPLACE,
LOCK=NONE;
-- 複数のインデックスを同時に削除する（リビルド回数を減らすため）
ALTER TABLE orders
DROP INDEX idx_orders_ref,
DROP INDEX idx_orders_old_status,
ALGORITHM=INPLACE,
LOCK=NONE;テーブルが特に大きい（50GB以上）場合や完全なゼロダウンタイムが必要な場合は、Percona Toolkitのpt-online-schema-changeを検討する：
pt-online-schema-change \
--alter "DROP INDEX idx_users_phone" \
--execute \
D=myapp,t=users削除後の確認とモニタリング
dropした後は、影響が出ないことを確認するために少なくとも24〜48時間は監視する。
書き込みパフォーマンスの比較
SELECT
OBJECT_NAME,
COUNT_WRITE,
ROUND(SUM_TIMER_WRITE / 1000000000, 2) AS write_time_ms,
ROUND(AVG_TIMER_WRITE / 1000000, 2) AS avg_write_us
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'myapp'
ORDER BY SUM_TIMER_WRITE DESC
LIMIT 10;解放されたストレージ容量を確認する
SELECT
TABLE_NAME,
ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp'
ORDER BY INDEX_LENGTH DESC;週次の自動モニタリングスクリプト
#!/bin/bash
# check_unused_indexes.sh
MYSQL_USER="monitor_user"
MYSQL_PASS="your_password"
DB="myapp"
UNUSED=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT COUNT(*) FROM sys.schema_unused_indexes
WHERE object_schema = '$DB';" 2>/dev/null | tail -1)
if [ "$UNUSED" -gt "0" ]; then
echo "ALERT: $UNUSED unused indexes found in $DB" | \
mail -s "MySQL Unused Index Alert" [email protected]
fi# 毎週月曜日の午前9時に実行するようにcrontabに追加する
0 9 * * 1 /opt/scripts/check_unused_indexes.sh実際の結果
1200万行のusersテーブルを持つ本番データベースでインデックスを整理した後、次の結果が得られた：
- インデックスのサイズが4.2GBから2.8GBに減少 — 33%の削減
- usersテーブルの平均INSERT時間が約18%短縮
- インデックスページの占有が減ったことでInnoDBバッファプールのヒット率がわずかに向上
具体的な数値はスキーマやワークロードによって異なるが、原則は常に変わらない：使われないインデックスは害しかなく、メリットはゼロだ。定期的な整理はデータベースメンテナンスの一部だが、ジュニアエンジニアがよく見落とす。難しいからではなく、MySQLに最初から用意されているツールを知らないからだ。
未使用インデックスの整理が終わったら、次のステップは使われているインデックスが本当に効果的かどうかを評価することだ。それはEXPLAIN ANALYZEとインデックスの選択性の話で、次回の記事に譲ろうと思う。
