一、問題背景#
あるオンラインインターフェースの応答時間は 50 秒以上に達し、頻繁にタイムアウトが発生し、ユーザー体験に深刻な影響を与えています。テスト環境ではスムーズに動作しているため、調査が行われました。
二、初歩的な調査プロセス#
✅ 1. リンクトレースで遅いインターフェースを特定
リンクトレースシステム（SkyWalking など）を使用して、特定のデータベースクエリがインターフェースの応答を遅くしていることを迅速に特定しました。
✅ 2. ログ + SQL インターセプターで完全な SQL を出力
MyBatis の SQL インターセプターを組み合わせて、完全なパラメータ結合後の SQL を取得しました：
SELECT MAX(t.id)
FROM user_log t
WHERE t.deleted_at = '1970-01-01 00:00:00'
AND LENGTH(t.platform_id) > 0
AND t.platform_id != '0'
AND t.platform_type = 'MOBILE'
AND t.nickname LIKE CONCAT('%', 'abc', '%')
GROUP BY t.platform_id
LIMIT 20;
✅ 3. 実行計画分析（EXPLAIN）
本番環境で誤って deleted_at で始まるインデックスにヒットしていることが判明しました：
idx_deletedat_memberaccountid_updatedat
→ スキャン行数：2,126,736
→ filesort + 一時テーブル
→ 総時間：50秒
テスト環境ではより適切なインデックス idx_platformtype_deletedat_platformid_nickname を使用しており、実行には数百ミリ秒しかかかりませんでした。
三、問題の根本原因#
オプティマイザーが本番環境で最適なインデックスを誤って判断し、全表スキャン + 一時テーブルソートを引き起こしました。
原因は以下の通りです：
| 条件フィールド | テスト環境のマッチ量級 | 本番環境のマッチ量級 |
|---|---|---|
deleted_at | 千級（約 5,000） | 百万級（約 2,100,000） |
platform_type | 十万級（約 700,000） | 百万級（約 1,250,000） |
💡推測結果：本番環境で deleted_at にマッチするデータが多すぎて、オプティマイザーがフィルタリングが強いフィールドと誤判断し、サブオプティマルなインデックスを選択しました。
四、最終的な最適化案：二つの戦略を組み合わせる#
✅ 案 1：条件付きで USE INDEX を使用
MyBatis XML で動的に注入：
<if test="ro.platformType != null and ro.partialNickname != null and ro.talentPlatformIdList == null">
USE INDEX (idx_platformtype_deletedat_platformid_nickname)
</if>
効果：
・ ニックネームリストのクエリや、platform_id の正確な範囲がない場合にオプティマイザーが誤った選択をしないようにする；
・ 制限付き注入により、特定の組み合わせでのみ強制的にインデックスを使用し、一律適用を避ける。
✅ 案 2：範囲最適化で関数フィルタを置き換える
元の冗長な判断ロジックを置き換えます：
-- 元々
AND LENGTH(t.platform_id) > 0 AND t.platform_id != '0'
-- 最適化後
AND t.platform_id > '0'
利点：
- length と！= はインデックスを使用せず、直接スキャンするため、> に変更することでインデックスを使用してクエリが可能になります。
- 関数がフィールドに干渉するのを避け、インデックス使用の確率を高めます；
- 簡潔で効率的、意味的に等価です。
五、最適化効果#
| 環境 | 最適化前 | 最適化後 | 向上倍率 |
|---|---|---|---|
| 本番環境 | ~50 秒 | ~0.9 秒 | 55x |
六、今後の最適化#
以下は現在の案に基づいてまとめた、さらなる選択肢の最適化方向であり、今後の進展を考慮するのに適しています。現在は ROI が低いため、さらなる最適化は行いません。
- ✅ フルテキストインデックスを使用してあいまい検索を最適化
- 現在の LIKE '% xxx%' クエリは B-Tree インデックスを使用できません。
- FULLTEXT インデックス（InnoDB の VARCHAR または TEXT フィールドに適用可能）を使用して、あいまい検索のパフォーマンスを向上させます：
ALTER TABLE some_table ADD FULLTEXT INDEX idx_nickname_ft (nickname);
- クエリを次のように書き換えます：
SELECT ... FROM some_table
WHERE MATCH(nickname) AGAINST('+abc' IN BOOLEAN MODE);
- ✅ OR 条件の SQL を分割して処理
- 元のクエリには次のようなものがあります：
WHERE platform_id IN (...) OR home_link IN (...)
- MySQL は OR に遭遇すると、通常は二つのインデックスを同時に利用できず、全表スキャンを引き起こしやすくなります。
- SQL を二つのサブクエリに分割して UNION ALL を使用することを推奨します：
(SELECT ... FROM ... WHERE platform_id IN (...))
UNION ALL
(SELECT ... FROM ... WHERE home_link IN (...))
- ✅ 異なるパラメータの組み合わせに対応する複合インデックスを設計
- 異なるクエリシナリオ（platform_type を含むか、あいまい検索か、notification_id があるか）に応じて、専用の複合インデックスを設計します；
- そして MyBatis の条件 + USE INDEX で動的にパスを制御し、常に最適な実行計画を確保します。
- ✅ ヒストグラムを使用してオプティマイザーのデータ分布の理解を向上させる（MySQL 8+）
- ANALYZE TABLE が特定のフィールドの基数分布を正確に反映できない場合は、次のように考慮します：
ANALYZE TABLE some_table UPDATE HISTOGRAM ON platform_type WITH 256 BUCKETS;
- オプティマイザーのインデックスコスト評価の正確性を向上させます。
七、まとめと示唆#
🎯 最適化のキーワード：
・ USE INDEX の条件制御
・ 関数フィルタを範囲判断に変更
・ データ分布の差異を分析
・ オプティマイザーの動作に注目
・ 正確な条件下での強い誘導、全体的な強制を避ける
🧠 経験のまとめ：
| 提案 | 説明 |
|---|---|
| 最適化前に必ず EXPLAIN で比較 | 本番とテストの差異を理解するため |
| インデックスフィールドに関数を慎重に使用 | 関数フィルタはインデックスの無効化を引き起こすことが多い |
| 条件付き USE INDEX を使用し、全体固定は避ける | 互換性と堅牢性を高める |
| フィールドの分布がオプティマイザーの動作を決定する | 異なる環境でそれぞれ調整する必要がある |
⸻
一度の誤ったインデックス選択が、クエリの時間を 50 倍に増加させることがある；一度の精密な微調整の組み合わせが、システムに新たな命を吹き込むことができる。
オプティマイザーに対して、全てを信じず、また強引に対抗せず、より詳細な戦略で協調することが、高度な SQL 最適化の道です。