複合インデックスとは？

複数のカラムを組み合わせて作るインデックス。「このテーブルのAカラムとBカラムはよく一緒の条件に出てくるな～」というときに使います。単一インデックスより複数条件の絞り込みが高速になるという特徴があります。

【CakePHP・MySQL】8分かかった2万件のCSV出力はサブクエリで10秒にできた

とある商品管理システムで相対した激重クエリの原因と改善について、思い出話をしていこうと思います。
当時はまだSELECT文すら調べてしまうような新人の頃で、めちゃくちゃ勉強になった記憶。

環境
背景
1. 期間単価
課題
原因と対策
まとめと教訓

環境

システムがどんな環境で稼働していたか、前もってご紹介します。

項目	バージョン
OS	CentOS Linux 7 (Core)
PHP	5.6
MySQL (InnoDB)	5.7
CakePHP	3.2

ちなみに、出てくるテーブル名やカラム名は適度に変更しております。

背景

商品管理システムには、登録してある商品を一覧でCSV出力してくれる―――いわゆる一括ダウンロード機能があったんです。
自分が参画した当初はあろうことか検索条件を付与できず、全件ダウンロードのみの仕様でした。
昔は商品数が少なかったからでしょうか？

「そろそろカテゴリーなどの条件に絞って取得したい」というご要望を先方からいただき、自分が担当となって実装を始めました。

追加したかった項目はこんな感じ。

カテゴリ
サブカテゴリ
商品名
発注状態（発注可・不可）
発注エリア
現単価
新単価
新単価適用日

ここで、現単価・新単価・新単価適用日が何たるか説明しましょう。

期間単価

このシステムで厄介だったのは、期間単価という考え方でした。
よくある商品マスタの扱いって、商品Aに対して金額って一個（つまり1対1）の関係じゃないですか。
でも期間単価は、1対多の関係…簡単に言うと「〇日～〇日は100円だったけど、△日からは120円になるよ」って意味なんです。株価？

なので現単価・新単価・新単価適用日はどれも期間単価の情報をもとに示されます。

現単価：「今の金額」
新単価：「次に設定される予定の金額」
新単価適用日：「新単価になる日」

課題

そうなってくると全部で2万件ほどある商品テーブルのみならず、期間単価を司る単価テーブルも結合しなきゃいけません。
「一括ダウンロード実行日」が1月1日だったら、

1月1日が期間に含まれる、現在の金額
次の金額
次の金額になる日

をテーブルから引っ張ってこなければならないのです。
~~ばかの一つ覚えで~~商品テーブルと単価テーブルを結合し、あれこれ取得させようとした結果。

まさかの処理時間8分超え！！！！！

条件を絞ればそりゃ件数が少なくなる＝早くはなりますが、全件取得しようとすると8分。
いや～ローカル環境だけかと思ったけど本番でも8分かかって大焦りしましたね（震え声）。

このとき、筆者が何をやらかしていたのか、振り返ってみました。

原因と対策

適切なインデックスをつけろ

大前提としてDBにインデックスがなかったんです。
ここでは複合インデックスをつけたんですが、「複合」って初めて聞きましたね。

Q 複合インデックスとは？: A

複数のカラムを組み合わせて作るインデックス。
「このテーブルのAカラムとBカラムはよく一緒の条件に出てくるな～」というときに使います。
単一インデックスより複数条件の絞り込みが高速になるという特徴があります。

単価テーブルに、商品ID・開始日・終了日の3カラムを対象とした複合インデックスを追加しました。

CREATE INDEX idx_***
  ON 単価テーブル (商品ID, 開始日, 終了日);

インデックスはリリース時に必ず適用
開発・ステージング環境だと忘れがちだけど、デプロイしてもDB構成はそのまんま！
本番DBでもインデックスの追加を忘れないように（自戒）！

NULL非許容カラムは INNER JOIN にしとけ

これ私はLEFT JOINを使っていたんですが、INNER JOINのほうが適してたんです。

なんでかっていうと、

今回の条件で指示していた商品テーブルのカラムはNOT NULLだったから。
LEFT JOINが「右テーブルに一致する行がなくても、左テーブルの行は必ず残す」のに対して
INNER JOINは「両テーブルに一致する行だけを返す」から。

つまり、絶対にNULLが来ないならわざわざLEFT JOINする必要なくね？って話です。
SQL側も（いつかNULL来るかも…）ってそわそわしながら処理するより、「NULLは来ない！　検討しない！」って切り捨てちゃったほうが早いんだろうなぁ。

ORM（クエリビルダ）側で type => 'INNER' を指定することで、LEFT JOINではなくINNER JOINに変更することができました。

サブクエリは切り出せ

次回単価の取得に相関サブクエリを使用していたことが最たる原因でした。

相関サブクエリとは？

SQLのサブクエリ（クエリの中に書く入れ子のクエリ）には2種類あります。

通常のサブクエリ

SELECT * FROM 商品テーブル
WHERE カテゴリID IN (SELECT id FROM カテゴリテーブル WHERE name = '野菜');

カテゴリのサブクエリは1回だけ実行されて結果が使い回されます。

相関サブクエリ

SELECT * FROM 商品テーブル
WHERE 金額 = (SELECT MIN(金額) FROM 単価テーブル WHERE 商品ID = 商品テーブル.id);
                                                                ^^^^^^^^^^^^^^^^
                                                          外側のテーブルを参照している

商品テーブル.id という外側のクエリのテーブルを参照しているのがわかります。
これ、「商品テーブルの行が変わるたびにサブクエリを実行し直す」という動きになっちゃうんです。

てことは、通常のサブクエリが「サブクエリを1回実行 → 結果を使い回してメインクエリを処理」で済むのに対して、

相関サブクエリ：
商品テーブルの1行目を読む → サブクエリを商品ID=1で実行
商品テーブルの2行目を読む → サブクエリを商品ID=2で実行
商品テーブルの3行目を読む → サブクエリを商品ID=3で実行
        ・
        ・

商品テーブルが10,000行あれば → サブクエリが10,000回実行されるなどという事態に…。

今回のコードで何が起きていたか

次回単価の取得条件として、結合条件の中に以下のような相関サブクエリを書いていました。

// 改修前：商品テーブルの行数分だけサブクエリが繰り返し実行される（相関サブクエリ）
'次回単価.開始日 = (SELECT MIN(開始日)
                    FROM 単価テーブル
                    WHERE 商品ID = 商品テーブル.id   ← 外側の商品テーブルを参照
                    AND 開始日 > :today)'

この方式では、商品テーブルのレコード数だけサブクエリを実行することになります。
普段DBを触るときは繰り返し処理もしないですし、相関サブクエリへの関心がなかったんですよね…。

おそらく最も実行速度を遅延させてたこの問題、以下のとおり対処しました。

サブクエリをJOIN用のクエリオブジェクトに切り出す

単価テーブルのモデルを読み込んだ後、「今日より未来の単価を、日付の昇順で1件取得」しています。
未来の単価が同じ日に二つあるなんてことは（先方のデータ登録に不備がない限り…）あり得ないので、1件の取得で大丈夫ですもんね。

この時点ではまだDBに問い合わせは発生しておらず、クエリオブジェクトとして変数に保持しているだけです。

$this->loadModel('【単価モデル】');
$unitPriceSubquery = $this->【単価モデル】->find()
    ->select([
        '商品ID'  => '単価テーブル.商品ID',
        '金額'    => '単価テーブル.金額',
        '開始日'  => '単価テーブル.開始日',
    ])
    ->where([
        '単価テーブル.開始日 >' => $today,
    ])
    ->limit(1)
    ->order(['単価テーブル.開始日' => 'ASC']);

JOINテーブルをサブクエリに置き換える

改修前は単価テーブルのテーブル名を直接指定したうえで、結合条件の中に相関サブクエリを書いていました。
改修後は先ほど組み立てたクエリオブジェクトをテーブルとして渡しています。

MySQLはこれをFROM句の派生テーブルとして扱ってくれるので、結合条件も商品IDの一致だけに簡略化されるんです。

// 改修前
'次回単価' => [
    'table' => '単価テーブル',
    'type'  => 'LEFT',
    'conditions' => [
        '次回単価.商品ID = 商品テーブル.id',
        '次回単価.開始日 > :today',
        '次回単価.開始日 = (SELECT MIN(開始日)
                            FROM 単価テーブル
                            WHERE 商品ID = 商品テーブル.id
                            AND 開始日 > :today)'
    ]
]

// 改修後
'次回単価' => [
    'table'      => $unitPriceSubquery,   // ← クエリオブジェクトをテーブルとして渡す
    'type'       => 'LEFT',
    'conditions' => '次回単価.商品ID = 商品テーブル.id',
]

こんな感じで相関サブクエリをなくし、あらかじめ絞り込んだサブクエリをJOINすることで、DBへの問い合わせ回数を大幅に削減しました。

->all() 使うな

これはもう当然っちゃ当然なんですが、->all()を呼ぶと、DBから取得した全レコードをいったんPHPのメモリ上に配列として展開→ループ処理って動きます。
商品が1万件あれば1万件分のデータが一度にメモリに乗ってしまうので、そりゃぁもうメモリ不足待ったなし。

一方、foreach ($query as $item) と直接イテレート＝1件ずつ取得→処理→次へ…ってすると、メモリ使用量を抑えられます。

// 改修前：全レコードをメモリに展開してからループ
foreach ($query->all() as $item) { ... }

// 改修後：カーソルで1件ずつ処理（メモリ使用量を抑制）
foreach ($query as $item) { ... }

CakePHP3のクエリオブジェクトはこの書き方を標準でサポートしているので、大量データを扱う場面では->all()なんて論外だったんだな…。

ちなみに一時的にメモリ上書きも試したけど、そんなに処理時間は変わらなかった気がする。
ボトルネックがメモリじゃなくてSQL側にあったから？なのか？？

ini_set('memory_limit', '512M') → '1024M'

ループ内に unset() いれろ

PHPはループ変数を次のループで上書きするため、明示的に解放しなくても動作上は問題ないんです。

でもここで言う $row（CSVの一行分のデータ）みたいな配列変数は、unset() を呼ぶまでメモリ上に残り続けます。
1件あたりのサイズは小さくても、ループが数千・数万回まわると解放されない変数が積み重なってメモリを圧迫してしまうわけです。

fputcsv() でCSVへの書き出しが終わった直後に unset($row) を呼ぶことで、1件ごとに確実にメモリを解放し、ループ全体のメモリ使用量を一定に保つことができます。

foreach ($query as $item) {
    // ... $row を組み立て ...
    fputcsv($fp, $row);
    unset($row);  // ← 追加：各ループで$rowを明示的に解放
}

まとめと教訓

今回の激重クエリの原因って、件数が少ない段階では誰も気づかなかったというか。

どれも「動く」んですよね。

件数が少なければ気にならないレベルで動く。
だからこそ、データが増えたタイミングで初めて「あれ、なんか遅くない？」って気づく。

対策	ひとこと教訓
複合インデックスの追加	まずインデックスを疑え
INNER JOINへの変更	NULLが来ないならLEFT JOINするな
相関サブクエリの排除	件数分だけ実行されるクエリを書くな
`->all()` の除去	全件メモリ展開は大量データでは論外
`unset()` の追加	使い終わった変数は解放する癖をつけろ