🌟 はじめに

自社で月間PV数1500～2000万以上のメディアサイトを運営しており、私はそのメインエンジニアを担当させてもらっています。日々、保守運用から新規開発のリードまでいろいろとやっていますが、なにをするにも可用性とパフォーマンスが重要だと実感しています。

ある日、突然、サイトが504エラーで表示できなくなるという事態が発生しました。障害です。
AWSのCloudWatch Logsとパフォーマンスインサイトを調査した結果、トップページの新着記事取得クエリがボトルネックになっていることが判明しました。この記事では、そのクエリを分析し、最終的に約2,255倍の高速化に成功した事例をナレッジとして残すものです。

📋 この記事でわかること

大規模サイトにおけるスロークエリの特定方法（ざっくり）
MySQLの実行計画（EXPLAIN）を使った問題分析の手法
複合インデックスの設計と適用方法
クエリ最適化のベストプラクティス（の一つ）
効果測定のためのパフォーマンステスト方法（の一つ）

🛠 必要なツール・想定環境

MySQL 8系
AWS RDS (または同等のデータベースサービス)
パフォーマンス測定ツール (CloudWatch, Performance Insights)
Claude 3.7 sonnet thinking（実行計画や改善結果の分析・評価のサポート）

👨‍💻 想定読者

Webアプリケーションのバックエンドエンジニア
データベース管理者やデータベース設計者
パフォーマンスチューニングに興味のあるエンジニア
大規模Webサイトの運用担当者

💯 結論 - 改善結果

	実行時間	1秒あたり処理可能数	従来比
元のクエリ	487 ms	約2件	-
最終改善版	0.216 ms	約4,630件	約2,255倍

※詳細はのちほど。

この改善により、同じハードウェアで2,000倍以上のリクエスト処理が可能になり、
以降これまで504エラーの発生も解消されているように思います。

🔍 問題の特定

障害の発見

AWS CloudWatchのアラートから、サイトが504 Gateway Timeout エラーを返し始めていることを検知しました。初期調査では以下の点がわかりました：

特にピーク時間帯に発生頻度が高い
サーバーのCPU使用率は正常範囲内
メモリ使用率も問題なし
データベースの接続数が異常に高い

DBまわりがあやしいですね。

RDSパフォーマンスインサイトでの分析

AWS RDSのPerformance Insightsを確認してみると、
特定のSQLクエリがCPU負荷の大部分を占めていることがわかりました。

スロークエリログを確認すると、トップページの新着記事取得クエリが頻繁に記録されており、実行時間が最悪の場合で574秒（9分半以上） に達することもありました。
9分半！！ブラウザのタイムアウトも納得です。。。

以降、クエリはところどころダミー化してマスキングしてあります。
また、アプリケーションはPHP, CakePHP製となっております。

🔬 原因の分析

問題のクエリ

問題となっていたのは、トップページで表示する新着記事一覧を取得するクエリでした。以下はその概略です（実際のクエリは長いため簡略化しています）：

SELECT
    Table1.id, Table1.name, /* 他多数のカラム */
    Table2.id, Table2.name, /* 他多数のカラム */
FROM
    table1 Table1  
LEFT JOIN
    table2 Table2 ON Table2.id = Table1.reference_id
WHERE
    (
        Table1.status = 2          
        AND (
            now() BETWEEN Table1.date_from AND COALESCE(Table1.date_to, now())         
        )          
        AND is_visible     
    )  
ORDER BY
    Table1.date_from DESC 
LIMIT 20;

実行計画（EXPLAIN）の分析

クエリの問題点を特定するために、
EXPLAIN ANALYZEとEXPLAIN FORMAT=JSONの両方を使って
実行計画を詳細に調査しました。

EXPLAIN ANALYZE SELECT /* 省略 */ FROM table1 Table1 LEFT JOIN table2 Table2 /* 省略 */;

実行計画の結果：

-> Limit: 20 row(s)  (actual time=487..487 rows=20 loops=1)
    -> Sort row IDs: Table1.published_at DESC, limit input to 20 row(s) per chunk  (actual time=487..487 rows=20 loops=1)
        -> Table scan on <temporary>  (cost=700..740 rows=2948) (actual time=469..484 rows=21004 loops=1)
            -> Temporary table  (cost=700..700 rows=2948) (actual time=469..469 rows=21004 loops=1)
                -> Left hash join (Table2.id = Table1.reference_id)  (cost=405 rows=2948) (actual time=0.0722..233 rows=21004 loops=1)
                    -> Filter: ((<cache>(now()) between Table1.date_from and coalesce(Table1.date_to,<cache>(now()))) and (0 <> Table1.is_visible))  (cost=1353 rows=2948) (actual time=0.0293..212 rows=21004 loops=1)
                        -> Index lookup on Table1 using idx_status (status=2)  (cost=1353 rows=6552) (actual time=0.0256..203 rows=21254 loops=1)
                    -> Hash
                        -> Table scan on Table2  (cost=0.0375 rows=1) (actual time=0.0238..0.0274 rows=1 loops=1)

また、JSON形式での詳細な実行計画も確認しました：

EXPLAIN FORMAT=JSON SELECT /* 省略 */ FROM table1 Table1 LEFT JOIN table2 Table2 /* 省略 */;

詳細な実行計画（クリックして展開）

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2119.14"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "Table1",
            "access_type": "ref",
            "possible_keys": [
              "idx_status",
              "idx_date_from"
            ],
            "key": "idx_status",
            "used_key_parts": [
              "status"
            ],
            "key_length": "4",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 6552,
            "rows_produced_per_join": 2948,
            "filtered": "45.00",
            /* 省略 */
            "attached_condition": "((<cache>(now()) between `db_name`.`Table1`.`date_from` and coalesce(`db_name`.`Table1`.`date_to`,<cache>(now()))) and (0 <> `db_name`.`Table1`.`is_visible`))"
          }
        },
        {
          "table": {
            "table_name": "Table2",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 2948,
            "filtered": "100.00",
            "using_join_buffer": "hash join",
            /* 省略 */
          }
        }
      ]
    }
  }
}

特定した問題点

EXPLAIN ANALYZE と EXPLAIN FORMAT=JSON の両方の実行計画結果から見える具体的な問題点：

実行時間が長い
- クエリ全体で487ms（actual time=487..487）かかっている
- 一時テーブル作成に469ms（actual time=469..469）を費やしている
  　
大量のレコード処理
- 一時テーブルに21,004行（rows=21004）ものデータを格納
- わずか20行を返すためだけに21,254行（rows=21254）を読み込んでいる
  　
非効率なソート処理
- ソート処理に時間がかかっている（Sort row IDs: Table1.published_at DESC）
- ソートのためだけに487msを消費
  　
インデックスの不十分な活用
- 単一カラムのインデックス（idx_status）のみ使用
- フィルタリング条件の多くはインデックス利用せず後処理（Filter:部分）
  　
ハッシュ結合の非効率
- 結合操作に233ms（actual time=0.0722..233）かかっている
- 大量のデータ（21,004行）に対してハッシュ結合を実行
  　
非効率な条件評価
- BETWEENとCOALESCEを含む複雑な条件がインデックス活用を妨げる
- 条件評価に212ms（actual time=0.0293..212）を消費

↓
これらの問題点から、インデックスの見直しとクエリの最適化が必要と判断できます。

💡 改善策の実施

1. 複合インデックスの作成

問題分析に基づき、クエリに最適化された複合インデックスを作成しました：

CREATE INDEX idx_table1_is_visible_status_date_from_desc ON article
(colomn1, colomn2, colomn3 DESC);

このインデックスは以下の点で効果的な可能性がありました：

colomn1とcolomn2の等価比較を最初に配置
colomn3のソート順（DESC）をインデックスに含める
頻繁に実行されるクエリに合わせた最適な順序

2. クエリの書き換え

次に、インデックスを最大限活用するようクエリを書き換えました：

SELECT 
  Table1.id, Table1.content_type, Table1.date_from, 
  Table1.group_id, Table1.name, Table1.image_main, 
  Table1.image_pc, Table1.image_sp 
FROM 
  table1 Table1 FORCE INDEX (idx_table1_is_visible_status_date_from_desc) 
WHERE 
  Table1.is_visible = 1
  AND Table1.status = 2
  AND Table1.date_from <= '2025-03-03 19:12:51'
  AND (
    Table1.date_to IS NULL 
    OR Table1.date_to >= '2025-03-03 19:12:51'
  )
ORDER BY 
  Table1.date_from DESC, 
  Table1.id ASC 
LIMIT 20;

主な改善点：

必要なカラムのみの取得
- 大きなテキストフィールドを除外
- 表示に必要な8つのカラムのみに限定
  　
インデックスヒントの使用
- FORCE INDEXでオプティマイザに最適なインデックスを指示
  　
条件式の最適化
- BETWEENとCOALESCEを避け、より明示的な条件に変更
- インデックスの効率的な使用を促進
  　
テーブル結合の排除
- 一覧表示に追加情報が不要だったため、結合を排除
  　
ソート順の安定化
- date_from DESC, id ASCによる安定したソート順の確保

工夫？試行錯誤？ FORCE INDEXしている理由：

複合インデックスを作成した後でも、MySQLオプティマイザが自動的にそれを選択してくれませんでした。新しく作った複合インデックスがあるにもかかわらず、以前の実行計画と同様に単一カラムインデックスを使用し続けていたのです。
細かい内容は割愛しますが、いろいろと試したのですが。。
”こうすればよかったんじゃないの？”というご意見があれば、ぜひお願い申し上げます

3. CakePHPでの実装

最終的に、CakePHPのQueryビルダーで以下のように実装しました：

public function getTopNewList(int $limit = 20): ResultSetInterface
{
    $table = parent::getTable('Table1');
    $query = $table->find();
    $now = FrozenTime::now();

    // 必要なフィールドのみを選択
    $query->select([
        'Table1.id',
        'Table1.content_type',
        'Table1.date_from',
        'Table1.group_id',
        'Table1.name',
        'Table1.image_main',
        'Table1.image_pc',
        'Table1.image_sp'
    ]);

    // インデックスの存在確認
    if ($this->utilService::indexExists('table1', self::INDEX_TOP_NEW_ITEMS)) {
        // インデックスがある場合はFORCE INDEXを指定
        $sql = 'table1 Table1 FORCE INDEX (' . self::INDEX_TOP_NEW_ITEMS . ')';
        $query->from($sql);
    }

    $query->where([
        'Table1.is_visible' => 1,
        'Table1.status' => 2,
        'Table1.date_from <=' => $now,
        'OR' => [
            'Table1.date_to IS' => null,
            'Table1.date_to >=' => $now
        ]
    ])
        ->orderDesc('Table1.date_from')
        ->orderAsc('Table1.id')
        ->limit($limit);

    return $query->all();
}

エラー回避のため、自作関数・indexExistsで該当インデックスの存在チェックをしています。

📊 改善の検証

改善後の実行計画

最適化後のクエリでEXPLAIN ANALYZEを実行した結果：

-> Limit: 20 row(s)  (cost=6633 rows=20) (actual time=0.0448..0.216 rows=20 loops=1)
    -> Filter: ((Table1.date_to is null) or (Table1.date_to >= TIMESTAMP'2025-03-03 19:12:51'))  (cost=6633 rows=2636) (actual time=0.0442..0.214 rows=20 loops=1)
        -> Index range scan on Table1 using idx_table1_is_visible_status_date_from_desc over (is_visible = 1 AND status = 2 AND '2025-03-03 19:12:51' <= date_from), with index condition: ((Table1.`status` = 2) and (Table1.is_visible = 1) and (Table1.date_from <= TIMESTAMP'2025-03-03 19:12:51'))  (cost=6633 rows=6591) (actual time=0.0429..0.21 rows=20 loops=1)

改善後のJSONフォーマット実行計画：

改善後の詳細な実行計画（クリックして展開）

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6632.56"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "Table1",
        "access_type": "range",
        "possible_keys": [
          "idx_table1_is_visible_status_date_from_desc"
        ],
        "key": "idx_table1_is_visible_status_date_from_desc",
        "used_key_parts": [
          "is_visible",
          "status",
          "date_from"
        ],
        "key_length": "10",
        "rows_examined_per_scan": 6591,
        "rows_produced_per_join": 2636,
        "filtered": "40.00",
        "index_condition": "((`db_name`.`Table1`.`status` = 2) and (`db_name`.`Table1`.`is_visible` = 1) and (`db_name`.`Table1`.`date_from` <= TIMESTAMP'2025-03-03 19:12:51'))",
        "cost_info": {
          "read_cost": "6368.94",
          "eval_cost": "263.62",
          "prefix_cost": "6632.56",
          "data_read_per_join": "25M"
        },
        "used_columns": [
          "id",
          "group_id",
          "name",
          "content_type",
          "is_visible",
          "image_main",
          "image_sp",
          "image_pc",
          "status",
          "date_from",
          "date_to"
        ],
        "attached_condition": "((`db_name`.`Table1`.`date_to` is null) or (`db_name`.`Table1`.`date_to` >= TIMESTAMP'2025-03-03 19:12:51'))"
      }
    }
  }
}

主な改善ポイント

実行時間の劇的な短縮：487ms → 0.216ms（2,255倍の高速化）
一時テーブルとファイルソートの排除：using_filesort: false
最適なインデックス使用：複合インデックスの全カラムが活用されている
必要最小限のデータ取得：必要なカラムのみ取得し、データ転送量が減少

パフォーマンスが向上しています。よかった。
せっかくなのでもう少し詳しく検証してみましょう。

パフォーマンステストの実施

ここまではクエリレベルでの改善確認でしたが、
アプリケーションレベルでの改善確認のため、
パフォーマンステストコードを書き、本番相当のデータでテスト実施してみました。

※テストコードの詳細は割愛します。

$ docker exec -it docker-app_web-1 php app/tests/Performance/Table1/RunContentQueryPerformanceTest.php
トップページ新着コンテンツ取得クエリ - パフォーマンステスト開始
------------------------------------------------------
データベース: db_name
コンテンツ総数: 21918件

インデックス情報:
- PRIMARY
- idx_group_id
- idx_reference_id
- idx_type
- idx_status
- idx_date_from
- idx_date_to
- idx_update_user_id
- idx_updated_at
- idx_name
- idx_create_user_id
- idx_table1_is_visible_status_date_from_desc

ウォームアップ実行中...

テスト実行中...
..............................

結果サマリー:
==========================================================

■ 元のクエリ (全カラム取得・BETWEENあり)
  オリジナルの実装: WHERE (Table1.status = 2 AND (NOW() BETWEEN date_from AND COALESCE(date_to, NOW())) AND is_visible)
  - 取得件数: 20件
  - 先頭コンテンツID: 1
  - 平均実行時間: 500.939 ms
  - 実行時間範囲: 492.793 - 522.759 ms
  - 平均メモリ使用: 739.41 KB

■ 最適化クエリ (インデックス利用・条件書き換え)
  インデックス使用・条件書き換え: WHERE is_visible = 1 AND status = 2 AND date_from <= NOW() AND (date_to IS NULL OR date_to >= NOW())
  - 取得件数: 20件
  - 先頭コンテンツID: 1
  - 平均実行時間: 1.200 ms (417.5倍高速)
  - 実行時間範囲: 0.892 - 2.177 ms
  - 平均メモリ使用: 739.52 KB

■ 最適化クエリ (カラム制限)
  必要最小限のカラムのみ取得: id, content_type, date_from, group_id, name, image_main, image_pc, image_sp
  - 取得件数: 20件
  - 先頭コンテンツID: 49873
  - 平均実行時間: 0.678 ms (738.7倍高速)
  - 実行時間範囲: 0.646 - 0.711 ms
  - 平均メモリ使用: 736.29 KB

↓
アプリケーションレベルでの改善結果：

平均実行時間: 0.678 ms (738.7倍高速)

🔑 改善のポイント整理

今回のパフォーマンス改善で効果的だったかなーと思った内容をまとめます：

1. 適切な複合インデックス設計

複合インデックスの設計では、「=（等価）条件 → 範囲条件 → ソート条件」の順序が大事

等価条件を先に配置
- 等価条件は特定の値だけを選択するため、最初のステップで検索範囲を大幅に減らせる
ソート順（DESC）をインデックス定義に含める
- インデックスの順序がソート順と一致していれば、追加のソート処理（filesort）が不要
インデックスカバリングを意識する

2. クエリの最適化

複雑な式（BETWEEN, COALESCE）を単純な条件式に書き換え
必要なカラムのみを取得
必要な結合（JOIN）のみを行う
インデックスヒント（FORCE INDEX）の適切な使用
- オプティマイザによるインデックス選択よりもエンジニアリングを優先させる強制は、慎重に行なうべきですが、今回の場合は慎重な検証をふまえて判断

3. フレームワークでの実装テクニック

フレームワーク固有のクエリビルダーの理解
インデックス存在確認などの堅牢な実装
パフォーマンステストの自動化

📝 まとめ

この事例から得られる教訓は以下の通りです：

問題特定の重要性：パフォーマンスインサイトやスロークエリログは問題発見の強力なツール
実行計画分析のスキル：EXPLAIN ANALYZEとEXPLAIN FORMAT=JSONの違いを理解し、適切に分析することが重要
インデックス設計の原則：複合インデックスでは、等価条件、範囲条件、ソート条件の順序が効果を左右する
段階的な改善アプローチ：インデックス追加→クエリ書き換え→カラム最適化という段階的アプローチが効果的
パフォーマンステストの実施：仮説と効果を定量的に検証することの重要性

以上です。ベストプラクティスではない部分があるかもしれませんが、
一つのよい経験にはなりましたし、今後より向上していける感触を得ることができました。
ひきつづき、スロークエリ改善していきたいと思います。