Day18 後半のゴール
「“なんとなく速くなった”ではなく“なぜ速くなったか”を説明できるようになる」
前半で「遅いSQLに出会ったときの考え方」と「EXPLAINでの見える化」をやりました。
後半では、実際に「どう書き換えるか」「どんなインデックスを足すか」を、具体例で踏み込みます。
ここでのゴールは、次の状態です。
この変更で何が変わり、なぜ速くなったのかを、自分の言葉で説明できる
これができると、チューニングが「勘」ではなく「再現可能なスキル」になります。
例題の続き：orders × users の遅いクエリを本気で直す
「インデックス追加 → EXPLAIN 再確認、までを一気に見る」
前半で扱った「アクティブユーザーの注文履歴一覧」をもう一度出します。
SELECT
o.id,
u.name,
o.total,
o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active'
ORDER BY o.created_at DESC
LIMIT 20;
インデックスがない状態の EXPLAIN は、こういう“重い顔”でした。
users は type = ALL, key = NULL, rows = 100000
orders は type = ALL, key = NULL, rows = 500000, Extra に Using filesort
ここから、改善の一手を実際に打っていきます。
orders.user_id にインデックスを貼る
「JOIN の片側をまず軽くする」
最初の一手として、JOIN に使う orders.user_id にインデックスを貼ります。
CREATE INDEX idx_orders_user_id
ON orders (user_id);
これで EXPLAIN を取り直すと、だいたいこう変わります。
users は相変わらず type = ALL, key = NULL
orders は type = ref, key = idx_orders_user_id, rows = 5000
つまり、
users はまだ全件スキャンしている
orders は、users から来た user_id に対してインデックス検索できるようになった
という状態です。
ここでのポイントは、「一手で全部を直そうとしない」ことです。
JOIN の片側だけでもインデックスが効けば、orders のフルスキャンは避けられます。
users.status にインデックスを貼るかどうか
「値の偏りを意識して“貼る価値”を考える」
次に候補になるのが users.status です。
CREATE INDEX idx_users_status
ON users (status);
これを貼ると、EXPLAIN はこう変わる可能性があります。
users が type = ref, key = idx_users_status, rows = 30000 などになる
ただし、ここで効き方はデータ分布に依存します。
active が全体の 90% なら、インデックスを使っても結局ほとんどの行を読むので、
オプティマイザが「インデックスを使わない（ALL のまま）」と判断することもあります。
active が全体の 10% なら、インデックスでかなり絞れるので、
type = ref / rows が小さめ、という“良い顔”になります。
ここで大事なのは、
インデックスを貼るかどうかは、「よく使うか」だけでなく「どれくらい絞れるか」も見る
という視点です。
複合インデックス (user_id, created_at) を検討する
「JOIN と ORDER BY を一気に助ける」
さらに一歩進めると、orders 側に複合インデックスを貼る案が出てきます。
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);
このインデックスは、
JOIN のための user_id
ORDER BY のための created_at
をまとめてカバーできます。
EXPLAIN がうまくハマると、orders の行はこうなります。
type = ref
key = idx_orders_user_created
rows がかなり小さくなる
Extra から Using filesort が消える
つまり、
アクティブユーザーの user_id に対して
そのユーザーの注文をインデックスで取り
その中で created_at の降順がすでにインデックス順になっている
という状態です。
ここでの重要ポイントは、「複合インデックスの並び順」です。
このクエリでは、
まず user_id で絞りたい
その中で created_at で並べたい
ので、(user_id, created_at) の順が理にかなっています。
クエリを書き換えて速くするパターン
「同じ意味を、インデックスが効きやすい形で書き直す」
インデックスだけでなく、「書き方の工夫」で速くなることも多いです。
不要な列を SELECT しない
「“とりあえず SELECT *”を卒業する」
例えば、さっきのクエリをアプリ側でこう使っていたとします。
画面に表示するのは、注文ID・ユーザー名・合計金額・注文日時だけ
それなのに、SQLはこう書いている。
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active'
ORDER BY o.created_at DESC
LIMIT 20;
これを、必要な列だけに絞ります。
SELECT
o.id,
u.name,
o.total,
o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active'
ORDER BY o.created_at DESC
LIMIT 20;
一見、パフォーマンスへの影響は小さそうに見えますが、
実は「インデックスだけで完結できるかどうか」に効くことがあります。
例えば、users.name も orders.total もインデックスに含まれているような設計なら、
Extra に「Using index」が出て、テーブル本体を読まずに済むケースも出てきます。
Day18 の段階では、
SELECT * は「とりあえず」ではなく「最後の手段」くらいに思っておく
くらいの意識を持っておくと、後々効いてきます。
LIMIT と WHERE の順番を意識する
「“まず絞ってから LIMIT”が基本」
次のようなクエリを考えます。
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;
これは「全注文の中から新しい20件」です。
これ自体は悪くありません。
しかし、もし本当の要件が、
アクティブユーザーの注文の中から新しい20件
なら、本来はこう書くべきです。
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active'
ORDER BY o.created_at DESC
LIMIT 20;
「LIMIT 20 だから軽いでしょ」と思いがちですが、
WHERE で絞る前に大量の行を並べ替えてから LIMIT していると、
内部的にはかなり重い処理になっていることがあります。
EXPLAIN の Extra に「Using filesort」が出ているときは、
本当に必要な行数まで、先に WHERE で絞れているか？
を疑ってみるとよいです。
集計クエリのチューニングの入り口
「COUNT(*) や GROUP BY が重いときの最初の視点」
一覧だけでなく、集計系のクエリもよく「遅い」と言われます。
例えば、次のようなクエリです。
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
ORDER BY COUNT(*) DESC
LIMIT 10;
「注文数が多いユーザーTOP10」を出すクエリです。
EXPLAIN すると、こうなりがちです。
table: orders
type: ALL
key: NULL
rows: 500000
Extra: Using temporary; Using filesort
つまり、
orders を全件スキャン
一時テーブルを作って GROUP BY
さらに filesort で並べ替え
という、なかなか重い動きです。
ここでの最初の一手はシンプルです。
GROUP BY に使っている user_id にインデックスを貼る
CREATE INDEX idx_orders_user_id
ON orders (user_id);
これで EXPLAIN を取り直すと、
type = index
key = idx_orders_user_id
rows は同じでも、Extra から Using temporary が消えることがある
など、少なくとも「一時テーブルを作らない」方向に寄せられます。
集計クエリのチューニングは奥が深いですが、
Day18 では、
GROUP BY / ORDER BY に出てくるカラムにもインデックスを意識する
という入口だけ押さえておけば十分です。
「改善前／改善後」を必ず EXPLAIN で比べる
「“速くなった気がする”を卒業する」
最後に、チューニングの一番大事な習慣をはっきりさせておきます。
クエリを変えたら、必ず EXPLAIN を取り直す
これを徹底してください。
改善前の EXPLAIN と、改善後の EXPLAIN を並べて見て、
type が ALL → ref / range / index などに変わったか
key が NULL → インデックス名に変わったか
rows が明らかに減っているか
Extra から Using filesort / Using temporary が消えたか
を確認します。
これができると、
このインデックス追加で、orders のフルスキャンがなくなった
この書き換えで、filesort が消えてソートコストが減った
といった説明ができるようになります。
それはそのまま、「次のプロジェクトでも再現できるスキル」になります。
Day18 後半のまとめ
遅いSQLを本気で改善するときは、まず EXPLAIN で「どのテーブルがフルスキャンか」「どこで filesort / temporary が発生しているか」を特定し、JOIN・WHERE・ORDER BY に出てくるカラムからインデックス候補を洗い出し、例えば orders.user_id に単一インデックスを貼る→必要に応じて (user_id, created_at) の複合インデックスを貼る、といった形で「JOIN と ORDER BY を同時に助ける」打ち手を順番に試し、そのたびに EXPLAIN を取り直して type / key / rows / Extra の変化を確認する。
同時に、SELECT * をやめて必要な列だけに絞ることで「インデックスだけで完結する（Using index）」余地を作ったり、WHERE user_id IN (SELECT ...) を JOIN に書き換えてインデックスが効きやすい形に変えたり、GROUP BY / ORDER BY に使うカラムにもインデックスを意識することで、Using temporary / Using filesort を減らしていくなど、「インデックス追加」と「クエリの書き方の工夫」をセットで考えるのが、Day18 で身につけたいクエリチューニングの核心になる。