iimon TECH BLOG

iimonエンジニアが得られた経験や知識を共有して世の中をイイモンにしていくためのブログです

EXPLAINで探る！SQLが速くなる・遅くなる理由

はじめに

　本記事はiimon Advent Calendar 2025 11日目の記事となります。 　こんにちは、ｉｉｍｏｎエンジニアのmariです！最近、業務の中で 既存の SQL を見直す機会 が増えてきました。 その中で、 「これ、もっとパフォーマンス良くできるんじゃないかな？」 と感じる場面があったため、今回は SQL の性能を分析して、改善ポイントを整理してみることにしました。

前提

  • 本記事はInnoDBのB+Treeを前提としており、MySQL8.0.42を使用して検証をしています

  • MySQL におけるインデックスの基本概念を理解していること

以下の記事を参考にしてみてください

B+Treeで理解するMySQLインデックスの基礎 - iimon TECH BLOG

EXPLAIN（実行計画)とは

EXPLAIN は、MySQL に SQL（主に SELECT）文を「どのように実行するか」を教えてくれる分析ツールです。 SQL を実行する前に、MySQL がどのテーブルをどの順番で読み、どんな方法で結合し、どのインデックスを使うかを確認できます。 例えば、SQL は成功してもエラーが出ないため、遅い原因がパッと見では分からない、という特徴があります。 しかし実際の遅い原因のほとんどは SQL の内部処理が非効率だからです。 その内部処理を可視化する方法が EXPLAINになります。

EXPLAINを使ってみる

使用方法： SELECT 文の前に EXPLAIN または DESC を付けるだけです

EXPLAIN SELECT 列一覧 
FROM テーブル名 
WHERE 条件;

EXPLAIN の主なフィールドを見てみよーー

次の SQL は、3つのテーブルを結びつけてデータを取得するクエリです。 まずは EXPLAIN を使って実行計画を確認してみます。

EXPLAIN SELECT * FROM student1 s 
WHERE s.id IN (
    SELECT studentid FROM student_course sc 
    WHERE sc.courseid = (
        SELECT id FROM course c WHERE c.name = 'MySQL'
    )
);

項目 意味
id（クエリ実行順序） SELECT 文やサブクエリの実行単位。大きい数字から先に実行。同じ id は上から順。
select_type（クエリの種類） SIMPLE：単純 SELECT
PRIMARY：外側のメイン SELECT
SUBQUERY：サブクエリ
UNION：UNION の後続 SELECT
type（テーブルアクセス方法）※重要 性能の良い順：
system > const > eq_ref > ref > range > index > ALL（ALL はフルスキャン）
possible_keys（利用可能インデックス） MySQL が「使えるかもしれない」と判断したインデックス一覧。
key（実際に使用したインデックス） 実際に使用されたインデックス。NULL の場合は未使用。
key_len（インデックス使用バイト数） 実際に利用したインデックス部分の長さ。短いほど効率が良い。
ref（参照している値） インデックスを使う際に、どの値を比較に使ったか。
例：const、table.column など。
rows（読み取る行数の予測） MySQL が「このくらい読む」と予測した件数。多いほど遅くなる可能性。
filtered（WHERE での絞り込み率） rows のうち条件に一致する割合。高いほど効率が良い。
Extra（追加情報） ◎ Using index：カバリングインデックス使用
○ Using where：WHERE句でフィルタリング
△ Using temporary：一時テーブル作成
△ Using filesort：追加ソート処理が必要

EXPLAIN を理解して読み解けるようになると、SQL のどこがボトルネックなのか、どのインデックスが効いているのか、そして どう最適化すべきか が明確に見えるようになります！

インデックスありと無しのEXPLAINを比較してみた

使うテーブル（user_tb）：

対象レコード数：

インデックス状況：

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
user_tb 0 PRIMARY 1 id A 101265 NULL NULL NULL BTREE YES NULL
▼ インデックス無しで検索した場合の EXPLAIN
EXPLAIN SELECT * FROM user_tb WHERE name = 'Dan';
id select_type table type key rows Extra
1 SIMPLE user_tb ALL NULL 101265 Using where
  • type = ALL：フルテーブルスキャン（全行を検査）
  • key = NULL：インデックスが使われていない
  • rows = 101265：101265行を読み取る想定

これは非効率なクエリです。ユーザー情報のように行数が増えやすいテーブルでは状況が一変します。検索対象となるカラムに適切なインデックスが存在しない場合、行数の増加とともに処理時間が急激に伸び、パフォーマンスの劣化が顕著になります。

▼ インデックスありで検索した場合の EXPLAIN

次に、name カラムへインデックスを追加した場合に、検索の実行計画がどのように変わるのかを確認してみます。

CREATE INDEX idx_user_tb_name ON user_tb(name);
EXPLAIN SELECT * FROM user_tb WHERE name = 'Dan';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_tb NULL ref idx_user_tb_name idx_user_tb_name 203 const 1 100.00 NULL
  • type = ref：インデックスを使用した検索
  • key = idx_user_tb_name：作成したインデックスが使われている
  • rows = 1：1行だけ読み取る

インデックス追加により、約101265行 → 1行 と大幅に効率化されました！✨適切なインデックスを付けることでクエリのパフォーマンスを改善できることがわかりました。

カバリングインデックス実践編

実は、普通のインデックスで検索できていても、欲しいカラムがインデックスの中に入っていないと、MySQL は一度インデックスで絞ったあとに、さらに テーブル本体を読み直す必要があります。 これがいわゆる「ルックアップ」と呼ばれる動きで、これが積み重なると意外とパフォーマンスを落としがちです。💦

このルックアップをなくして、もっとサクッと検索できるようにする方法ないかなと考え、「カバリングインデックス」を知りました。

カバリングインデックスとは

カバリングインデックスとは、クエリで必要な全てのカラムがインデックス内に含まれており、テーブル本体へのアクセス（ルックアップ）が不要になるインデックスのことです。理解を深めたいので以下のイメージを作りました。

使うテーブル（user_tb）：

対象レコード数：

インデックス状況：

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
user_tb 0 PRIMARY 1 id A 101265 NULL NULL BTREE YES NULL
user_tb 1 idx_user_tb_name 1 name A 100318 NULL NULL YES BTREE YES NULL
▼ idによるクラスター化インデックス
SELECT * FROM user_tb WHERE id = 3;

EXPLAIN SELECT * FROM user_tb WHERE id = 3;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_tb NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL

idで検索した場合はクラスター化インデックスになるので、リーフノードに行の実データが直接格納されているため、そのまま返され、とても高速です。

▼ nameによる非クラスタ化インデックス
SELECT id, name FROM user_tb WHERE name = 'Hina';

EXPLAIN SELECT id, name FROM user_tb WHERE name = 'Hina';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_tb NULL ref idx_user_tb_name idx_user_tb_name 203 const 1 100.00 Using index

非クラスタ化インデックスは主キー(id)への参照が入っています。 なので、今回の検索では必要なidとnameの情報が全部非クラスタ化インデックスだけでそろっているため、MySQL はテーブル本体を参照（ルックアップ）する必要がなくなります。 EXPLAINのExtraがUsing indexになっていますね！カバリングインデックス使用されていることがわかります。

▼ ルックアップが必要となる事例
SELECT id, name, gender FROM user_tb WHERE name = 'Hina';

EXPLAIN SELECT id, name, gender FROM user_tb WHERE name = 'Hina';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_tb NULL ref idx_user_tb_name idx_user_tb_name 203 const 1 100.00 NULL

今回は必要なデータにgenderが追加されています、しかし、gender は非クラスター化インデックスに存在しないため、実データを持つクラスター化インデックスまで取りに行く必要があります。その結果、カバリングインデックスのときよりも、わずかに追加コストが発生します。

▼ 複合インデックスを検討してみる

　今回の事例ではgenderが追加されたため、ルックアップが必要になりました、不要なテーブルのアクセスを減らしたいなと考え、複合インデックスを検討してみました。（※頻繁に取得するカラムの前提で作ります、またカラムを追加しすぎるとインデックスが肥大化し、INSERT / UPDATE 時のコストが増加するため、設計には注意が必要です。）

-- 既存のnameのインデックス削除
DROP INDEX idx_user_tb_name ON user_tb;

-- nameとgenderの複合インデックスを作る
CREATE INDEX idx_name_gender ON user_tb (name, gender);

インデックス状況：

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
user_tb 0 PRIMARY 1 id A 101265 NULL NULL BTREE YES NULL
user_tb 1 idx_name_gender 1 name A 99908 NULL NULL YES BTREE YES NULL
user_tb 1 idx_name_gender 2 gender A 99761 NULL NULL YES BTREE YES NULL 
SELECT id, name, gender FROM user_tb WHERE name = 'Hina';

EXPLAIN SELECT id, name, gender FROM user_tb WHERE name = 'Hina';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_tb NULL ref idx_name_gender idx_name_gender 203 const 1 100.00 Using index

nameとgenderの複合インデックスを作ったため、非クラスター化インデックスにはname, gender, 主キー(id)の3つの値が格納されています、欲しいデータが全部揃っていますね✨なので、クラスター化インデックスまで取りに行く必要がなくなり、不要なテーブルのアクセスがないため、クエリのパフォーマンスが上がりました！EXPLAINで確認してみるとExtraのとこにUsing indexになっています！

まとめ

今回いろいろと EXPLAIN を使って実行計画を確認しながら、インデックスの有無や種類によってクエリの動きがどう変わるのかを一つずつ見てきました、 SQLは”裏側でどう処理されているか” を理解することで、どう改善されるべきかを少しずつ自分の中でわかったような気がします。この記事が、同じようにパフォーマンスで悩む人のヒントになったら嬉しいです！

参考

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibili

最後に

最後まで読んでくださりありがとうございます！

弊社ではエンジニアを募集しております。 ご興味がありましたらカジュアル面談も可能ですので、下記リンクより是非ご応募ください！

iimon採用サイト / Wantedly

明日はリーダー「まつむら」さんの記事です！ どんな記事を書いてくださるか楽しみです！