ISUCON14のチューニングにおいて、MySQLスロークエリログ（pt-query-digest）で最も重かったクエリを、共通テーブル式（CTE: WITH 句）による事前絞り込みと複合インデックス追加によって改善。
対象SQLの実行時間を 512ms から 15.4ms（約33倍高速化） に短縮し、ベンチマークスコアを 1,323点から1,995点 へ引き上げた技術的な仕組みと手順。
目次
TL;DR：この記事のまとめ
要するに、「全件計算してから最後に絞り込む」非効率なSQLを、「先に必要な行だけ絞り込んでから計算する」構成に書き換えた記録。
- 問題の発見:
pt-query-digestで最悪クエリ（DB総時間の26.9%、合計約85秒）を特定。 - 原因: 1人のオーナーの椅子一覧を表示したいだけなのに、サブクエリ内で全椅子の位置情報履歴（約2.5万行）に対してWindow関数（
LAG）で距離計算を実行。 - 対策: CTE（
WITH句）を使って対象オーナーの椅子だけを先に絞り込んでから距離計算を行い、さらに(chair_id, created_at)の複合インデックスを追加。 - 結果: 検査行数を 24,897行から414行へ98%削減 し、1回あたりの処理時間を 512ms → 15.4ms へ短縮。
1. スロークエリ解析で「最悪クエリ」を発見する
ベンチマーク実行後、pt-query-digest でMySQLのスロークエリログを解析したところ、以下のクエリが圧倒的1位として浮上。
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
# 1 0xFA8D713EE4B9E473194D3A91C75FE99F 84.9800 26.9% 166 0.5120 0.00 SELECT chairs chair_locations distance_tableこの数値からわかること
- 合計時間: 84.98秒（DB全体の処理時間の 26.9% を1つのSQLが占有）
- 呼出回数: 166回
- 平均時間: 512ms（約0.5秒）
該当ハンドラーは、オーナー画面で自分の椅子一覧を取得する ownerGetChairs（/api/owner/chairs）。
2. 変更前のSQLと問題点
変更前のSQL
SELECT id,
owner_id,
name,
access_token,
model,
is_active,
created_at,
updated_at,
IFNULL(total_distance, 0) AS total_distance,
total_distance_updated_at
FROM chairs
LEFT JOIN (SELECT chair_id,
SUM(IFNULL(distance, 0)) AS total_distance,
MAX(created_at) AS total_distance_updated_at
FROM (SELECT chair_id,
created_at,
ABS(latitude - LAG(latitude) OVER (PARTITION BY chair_id ORDER BY created_at)) +
ABS(longitude - LAG(longitude) OVER (PARTITION BY chair_id ORDER BY created_at)) AS distance
FROM chair_locations) tmp
GROUP BY chair_id) distance_table ON distance_table.chair_id = chairs.id
WHERE owner_id = ?;【図解】そもそもWindow関数と LAG() とは何をしているのか
SQL初心者が最初につまずきやすいのが、このサブクエリ内で使われている Window関数（OVER (...)）と LAG()。
「椅子の移動距離を計算したい」というユースケースにおいて、通常の GROUP BY ではなぜ駄目で、なぜWindow関数が必要なのかを図解する。
■ 普通の GROUP BY（行が1行に潰れてしまう）
┌──────────┬───────────┐
│ chair_id │ latitude │
├──────────┼───────────┤
│ chair_A │ 10 (12:00)│ ──┐
│ chair_A │ 15 (12:05)│ ──┼─▶ 【合計/平均】 1行に潰れる（前後の差分は取れない）
│ chair_A │ 20 (12:10)│ ──┘
└──────────┴───────────┘
■ Window関数（行を潰さずに「直前の行」の値を参照できる）
┌──────────┬───────────┬──────────────────────────────────────────┐
│ chair_id │ latitude │ LAG(latitude) で1つ前の行の値を取得 │
├──────────┼───────────┼──────────────────────────────────────────┤
│ chair_A │ 10 (12:00)│ NULL （最初なので前がない） │
│ chair_A │ 15 (12:05)│ 10 （1つ前の 10 を取得 → 差分は 5） │
│ chair_A │ 20 (12:10)│ 15 （1つ前の 15 を取得 → 差分は 5） │
└──────────┴───────────┴──────────────────────────────────────────┘構文の役割
LAG(latitude) OVER (PARTITION BY chair_id ORDER BY created_at)PARTITION BY chair_id: 椅子ごとにグループを分割（別々の椅子の座標が混ざらないように分離）。ORDER BY created_at: 時系列順に整列。LAG(latitude): 1行手前（直前）の座標値を取得。
この LAG() によって「前回の位置との差分（移動距離）」を算出し、外側の GROUP BY で合計距離を出す構造。
なぜこのSQLは遅いのか
このSQLは「指定された owner_id が所有する椅子の情報と、それぞれの総移動距離を返すこと」が目的。
しかし、実行の流れを見ると致命的な無駄が存在していた。
- 内側のサブクエリ（
tmp）: –chair_locationsテーブルの**全データ（約2.5万行）**を全走査。 – すべての椅子（他人の所有する椅子を含む）について、Window関数LAG()を使って前回の座標との差分（移動距離）を1行ずつ計算。 - 中間のサブクエリ（
distance_table）: – 全椅子の計算結果をchair_idでGROUP BYし、合計距離（SUM）と最終更新日時（MAX）を算出。 - 最外層のクエリ:
– 最後に
WHERE owner_id = ?で、特定のオーナーの椅子だけに絞り込み。
つまり、「全オーナーの全椅子の距離計算をすべて終わらせてから、最後の最後に対象オーナー分だけを切り出す」という極めて無駄な処理。
3. 改善方針：CTEによる「事前絞り込み」
CTE（共通テーブル式: Common Table Expression）とは
WITH 一時テーブル名 AS (SELECT ...) の形式で記述し、クエリ内で一時的な結果セットを定義する構文。
今回はCTEを使って以下の手順に組み替え。
- Step 1: 対象
owner_idの椅子（数件）だけを先に抽出（owned_chairs）。 - Step 2:
chair_locationsとowned_chairsをINNER JOINし、計算対象の行数をそのオーナーの椅子だけに絞り込み。 - Step 3: 絞り込まれた数百行に対してのみ、Window関数
LAG()による距離計算とGROUP BYを実行。
改善後のSQL
WITH owned_chairs AS (
SELECT *
FROM chairs
WHERE owner_id = ?
),
distance_table AS (
SELECT
chair_id,
SUM(IFNULL(distance, 0)) AS total_distance,
MAX(created_at) AS total_distance_updated_at
FROM (
SELECT
cl.chair_id,
cl.created_at,
ABS(
cl.latitude -
LAG(cl.latitude) OVER (
PARTITION BY cl.chair_id
ORDER BY cl.created_at
)
) +
ABS(
cl.longitude -
LAG(cl.longitude) OVER (
PARTITION BY cl.chair_id
ORDER BY cl.created_at
)
) AS distance
FROM chair_locations AS cl
INNER JOIN owned_chairs AS oc
ON oc.id = cl.chair_id
) AS locations_with_distance
GROUP BY chair_id
)
SELECT
oc.id,
oc.owner_id,
oc.name,
oc.access_token,
oc.model,
oc.is_active,
oc.created_at,
oc.updated_at,
IFNULL(dt.total_distance, 0) AS total_distance,
dt.total_distance_updated_at
FROM owned_chairs AS oc
LEFT JOIN distance_table AS dt
ON dt.chair_id = oc.id;4. インデックス追加による仕上げ
SQLの構造改善に加えて、chair_locations テーブルに適切なインデックスを追加。
追加したインデックス
-- webapp/sql/1-schema.sql（chair_locations テーブル定義内）
INDEX idx_chair_locations_chair_id_created_at (chair_id, created_at)なぜこの複合インデックスが必要なのか
Window関数の部分：
OVER (PARTITION BY cl.chair_id ORDER BY cl.created_at)この処理は、「chair_id ごとにグループ化し、created_at の昇順に並べ替えて直前の行と比較する」という動作。
(chair_id, created_at) の複合インデックスが存在すれば、データベースはメモリ上でのソート処理（Filesort）を行うことなく、インデックスの並び順のままデータを走査できる。
5. 計測結果（Before / After）
単体実行計画（EXPLAIN ANALYZE）の比較
特定のオーナーIDを指定して EXPLAIN ANALYZE を実行した結果。
改善前（旧SQL：全2.5万行スキャンとFilesort）
改善後（新CTE SQL：インデックス参照と走査行数の激減）
| 項目 | 改善前 | 改善後 | 改善効果 |
|---|---|---|---|
| 実行時間 | 165〜189 ms | 16.2〜40 ms | 約4〜11倍 高速化 |
| Window関数 走査行数 | 24,924 行 | 4,076 行（椅子単位でさらに414行へ） | 83.6〜98.3% 削減 |
ベンチマーク全体での比較
| 項目 | 改善前 | 改善後 | 改善効果 |
|---|---|---|---|
| 対象SQLの合計時間 | 84.98 秒 | 2.55 秒 | 約97% 短縮 |
| 対象SQLの平均時間 | 512 ms | 15.4 ms | 約33.2倍 高速化 |
| 対象SQLのDB負荷占有率 | 26.9%（1位） | 1.0%（14位） | ボトルネックから完全に脱落 |
| ベンチマークスコア | 1,323〜1,595 点 | 1,995 点 | +50.8% 向上 |
6. まとめ：SQLチューニングの原則
今回の改善から得られた汎用的な教訓2点：
- 重い集計（GROUP BY / Window関数）は、できる限り手前で対象行数を絞り込んでから実行
– サブクエリで全件集計した後に外側で
WHERE絞り込みを行う構成は、典型的なアンチパターン。 – CTE（WITH句）を使って対象レコードのみを先に抽出することで、計算コストを劇的に抑制。 PARTITION BY col1 ORDER BY col2には(col1, col2)の複合インデックスを貼る – Window関数が必要とするソート順と完全に一致するインデックスを用意することで、余分なソート負荷をゼロ化。
