MySQL | SQLite経験者向け、30日で習得するMySQL：パフォーマンスと設計 － Day18 クエリチューニング①

SQL MySQL
スポンサーリンク

Day18 前半のゴール

「“なんとなく遅いSQL”を、筋道立てて改善ポイントまで分解できるようになる」

Day18 からはいよいよ「クエリチューニング」です。
テーマは「遅いSQLの改善」。

ここで目指すのは、

遅いSQLに出会ったときに、感覚ではなく手順で原因を追える
EXPLAIN とインデックス設計の知識を“実戦投入”できる
「どこがボトルネックか」を言葉で説明できる

この状態です。

前半では、まず「遅いSQLに出会ったときの基本の流れ」と「典型的な遅いパターン」を、シンプルな例で押さえていきます。

遅いSQLに出会ったときの基本ステップ

「いきなり書き換えない。まず“見える化”する」

いきなりクエリを書き換えるのは、正直ギャンブルです。
やるべき順番は決まっています。

まず、そのSQLを EXPLAIN する
どのテーブルがフルスキャンになっているかを見る
どのカラムにインデックスがなくて苦しんでいるかを見る
必要なら、対象テーブルの行数やデータ分布も確認する

この「見える化」をせずにチューニングを始めると、
たまたま速くなったり、逆に遅くなったりして、理由が分からないままになります。

Day16・Day17でやったことは、まさにこの「見える化」のための準備でした。
今日はそれを「遅いSQL」という具体的な相手にぶつけていきます。

例題：よくある“なんか遅い一覧画面”

「orders × users のJOIN＋絞り込み＋並び替え」

次のようなテーブルを想像してください。

CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50)  NOT NULL,
  email      VARCHAR(100) NOT NULL,
  status     VARCHAR(20)  NOT NULL,
  created_at DATETIME     NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT          NOT NULL,
  total      INT          NOT NULL,
  created_at DATETIME     NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
Python

アプリ側で、こんな画面があるとします。

「アクティブユーザーの注文履歴を、新しい注文順に20件表示する」

それに対応するSQLは、だいたいこんな感じです。

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;
Python

これが「なんか遅い」とします。
ここから、チューニングの思考を始めます。

ステップ1：EXPLAINで“どこが重いか”を特定する

「テーブルごとに type / key / rows / Extra を見る」

まずは EXPLAIN します。

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;
Python

インデックスがほとんど貼られていない前提だと、結果はだいたいこうなります（簡略版）。

tabletypekeyrowsExtra
uALLNULL100000Using where
oALLNULL500000Using where; Using filesort

これを Day16 の視点で読むと、

users はフルスキャン（type = ALL, key = NULL, rows = 100000）
orders もフルスキャン（type = ALL, key = NULL, rows = 500000）
さらに orders 側で Using filesort（ORDER BY のために別途ソート）

という、かなり“重そうな顔”をしていることが分かります。

ここで大事なのは、

「遅い」という感覚を、「どのテーブルのどの部分が重いか」という言葉に変換すること

です。

ステップ2：クエリの意図を言葉に戻す

「DBに“何をしてほしいのか”を整理する」

いきなりインデックスを貼る前に、
このクエリの意図を日本語で整理します。

アクティブユーザー（status = ‘active’）の中から
そのユーザーたちの注文を取り出し
注文日時の新しい順に並べて
先頭20件だけ欲しい

この「意図」と EXPLAIN の結果を見比べると、
今の実行計画はこうなっています。

全ユーザー10万件をなめて、active だけに絞る
全注文50万件をなめて、activeユーザーに紐づくものだけに絞る
その結果を全部並べ替えて、先頭20件だけ返す

「やりたいこと」に対して、「やり方」がかなり雑なのが分かります。

ここから、

どこで絞り込みを効かせるべきか
どこにインデックスを貼ると効きそうか

を考えていきます。

ステップ3：インデックス候補を洗い出す

「WHERE・JOIN・ORDER BY に出てくるカラムをチェックする」

このクエリで重要なカラムは、明らかにここです。

JOIN の ON 句：o.user_id = u.id
WHERE：u.status = 'active'
ORDER BY：o.created_at DESC

Day17 でやった通り、

JOIN に使うカラム
WHERE で絞り込みに使うカラム
ORDER BY で並び替えに使うカラム

は、インデックス候補の最有力です。

なので、まずこう考えます。

users.status にインデックスを貼るべきか？
orders.user_id にインデックスを貼るべきか？
orders.created_at にインデックスを貼るべきか？

ここで、「status は値の種類が少ないから絞り込みが効きにくいかも」という前半の知識も効いてきます。
一方で、orders.user_id と orders.created_at は、かなり有力な候補です。

ステップ4：改善案を“1つずつ”試すイメージを持つ

「いきなり全部やらず、“打ち手”を分解する」

実務では、いきなりインデックスを3本まとめて貼るのはあまり良くありません。
「どれが効いたのか」が分からなくなるからです。

考え方としては、

まず orders.user_id にインデックスを貼って EXPLAIN を見直す
次に orders.created_at にもインデックスを貼ってみる
必要なら複合インデックス (user_id, created_at) を検討する

というように、「打ち手」を分解して考えます。

Day18 前半では、まだ実際のインデックス追加SQLまでは書きません。
大事なのは、

EXPLAIN を見て「どこが重いか」を特定し
クエリの意図から「どのカラムで絞りたいか・並べたいか」を整理し
インデックス候補を挙げて、「どの順で試すか」を考える

という“思考の流れ”を体に入れることです。

もう一つの典型：サブクエリで遅くなるパターン

「IN (SELECT …) をJOINに書き換える発想」

もう一つ、よくある「なんか遅い」パターンを見ておきます。

例えば、こんなSQLです。

SELECT *
FROM orders
WHERE user_id IN (
  SELECT id
  FROM users
  WHERE status = 'active'
);
Python

意味としては、さっきのJOINとほぼ同じです。

「アクティブユーザーの注文だけ欲しい」

これを EXPLAIN すると、環境によってはこうなります。

外側の orders がフルスキャン
内側の users のサブクエリが何度も評価される

結果として、JOIN より遅くなることがあります。

このときの改善の発想はシンプルです。

サブクエリを JOIN に書き換えられないか？

つまり、こうです。

SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';
Python

この形にしてから EXPLAIN し直すと、

users に対して status で絞り込み
orders に対して user_id でインデックス検索

という「素直な実行計画」に乗りやすくなります。

Day18 前半では、

IN (SELECT …) や EXISTS を見たら、「JOIN で書けないか？」と一度立ち止まる

という癖を持っておくと、後半のチューニングがかなり楽になります。

Day18 前半のまとめ

遅いSQLに出会ったときはいきなり書き換えるのではなく、まず EXPLAIN して type / key / rows / Extra を見ながら「どのテーブルがフルスキャンになっているか」「どこで filesort が走っているか」を特定し、そのうえでクエリの意図を日本語に戻して「本当はどのカラムで絞り込みたいのか・どの順で並べたいのか」を整理するのが第一歩になる。
orders × users のJOINの例では、EXPLAIN から「users も orders もフルスキャン＋filesort」という“雑な作戦”が見え、そのうえで JOIN の ON 句（user_id）、WHERE（status）、ORDER BY（created_at）に出てくるカラムをインデックス候補として洗い出し、「まず orders.user_id、その次に orders.created_at、必要なら複合インデックス」というように“打ち手”を分解して考えることで、理由を持ったチューニングの流れが作れる。
また、WHERE user_id IN (SELECT id FROM users WHERE status = 'active') のようなサブクエリベースの書き方は、環境によっては非効率な実行計画になりやすく、「JOIN に書き換えられないか？」と発想を切り替えることで、インデックスが効きやすい形に変えられることが多く、「遅いSQLの改善＝EXPLAINで見える化→意図を言語化→インデックス候補と書き方の見直し」という3ステップで考えるのが、Day18 前半で身につけたい“思考の型”になる。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました