ミライトデザイン Advent Calendar 2024 9日目の記事になります。

8日目は takuma さんの MySQL のギャップロック（とネクストキーロック）についての記事でした。

まだ記事を読んでいないのですが、タイトルにある「MySQL のギャップロック」と言う言葉を初めて聞きました。
ギャップロックとはどう言うものなのか？読むのが楽しみです。

はじめに

この内容は、以前 YouTube 動画撮影用に調べた内容を、技術記事として改めて整理・形に残す目的で作成しました。

「実行計画」という言葉を聞いたことがあるけれど、具体的にどこでどう動いているのかを理解できていない方に向けて書いています。

この記事を読むことで、実行計画の基礎やEXPLAINの出力結果の見方を理解していただけると幸いです。

実行計画とは？

実行計画は、SQL実行時にデータベースマネジメントシステム（DBMS）のオプティマイザが「データを実際に取得する手順」を計画したものです。
※オプティマイザについては後述します

以下に「ユーザーがWebサイトで検索を行った」ときを例に、図を用いて説明します。

①ユーザーが検索を行う

ユーザーが検索フォームにキーワード入力して検索を実行すると、
パーサ (Parser)というモジュールが動き「構文解析」を行います。

この段階では、SQLの構文に誤りがないかをチェックをし、エラーがあれば通知します。

パーサは、DBMS 内の門番のような役割を担っています。
構文チェックをクリアしないと、後続の処理には進めません。

②パーサで構文チェックを通った情報をオプティマイザへ渡す

オプティマイザ（optimizer）は、その名の通り「最適化」を行うモジュールで、DBMS の中でも非常に重要な役割を果たします。

具体的には、後の処理で使用する実行計画を作成し、最適なデータ取得経路（アクセスパス）を選択します。

ただし、この段階では必要な情報（のちに出てるくる統計情報）が不足しているため、まだ実行計画の作成は行いません。

③オプティマイザがカタログマネージャへ統計情報の照会をかける

カタログマネージャはオプティマイザにとって必要な統計情報を提供する役割を担っています。

統計情報とは、テーブルやインデックス、データなどに関する情報のことで、
いわゆるDBMSの地図のようなものです。

この地図が最新でない場合、最短のアクセスパスを選ぶことができないため、正確な統計情報が必要です。

④ カタログマネージャがオプティマイザへ統計情報を返却する

カタログマネージャから得た統計情報をもとに、オプティマイザは作成した複数のアクセスを比較し、最短時間で処理できると思われる経路を選択します。

ここで生成される手順、がいわゆる「実行計画」です。

実行計画では以下のような決定が行われます。

インデックスを使用するか（例：インデックススキャン、フルテーブルスキャン）
結合の順序をどうするか（例：テーブルAとBのどちらを先に処理するか）
など

⑤実行計画に従ってテーブルへアクセスを行う

ここまでに作成された実行計画に基づいて、DBMSがテーブルからデータを取得します。

この結果、ユーザーが入力した検索条件に一致するデータが返され、Webサイト上で結果として表示されます。

EXPLAIN を実行した環境

MySQL 8.0

以下の記事を参考に、Docker を使って MySQL の環境を作り実施しました。
Dockerを使ってMySQLの環境を構築してみました

テーブル構造

mysql> CREATE TABLE user (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(50) NOT NULL,
    ->     email VARCHAR(100) NOT NULL,
    ->     age INT NOT NULL,
    ->     INDEX (email)
    -> );

データ

mysql> select * from user;
+----+---------+---------------------+-----+
| id | name    | email               | age |
+----+---------+---------------------+-----+
| 11 | Alice1  | alice@example.com   |  25 |
| 12 | Alice2  | alice@example.com   |  16 |
| 13 | Bob     | bob@example.com     |  30 |
| 14 | Charlie | charlie@example.com |  35 |
| 15 | David   | david@example.com   |  40 |
| 16 | Emma    | emma@example.com    |  22 |
| 17 | Fiona   | fiona@example.com   |  28 |
| 18 | George  | george@example.com  |  33 |
| 19 | Hannah  | hannah@example.com  |  37 |
| 20 | Ian     | ian@example.com     |  29 |
+----+---------+---------------------+-----+
10 rows in set (0.00 sec)

EXPLAINで実行計画を確認する

EXPLAINステートメントを使って、SQL文の実行計画を確認できます。
例えば、以下のようなクエリを使用して試してみます。
まずは SELECT 文で検索。

SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;

結果は以下のようになります。

+----+--------+-------------------+-----+
| id | name   | email             | age |
+----+--------+-------------------+-----+
| 11 | Alice1 | alice@example.com |  25 |
+----+--------+-------------------+-----+
1 row in set (0.01 sec)

次に、SQLの先頭にEXPLAINをつけます。

EXPLAIN SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;

結果は以下のようになります

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | email         | email | 402     | const |    2 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN の出力結果の読み方

ここではチューニング方法の詳細な解説は行いませんが、
チューニング時に注目すべき重要な項目について補足説明します。

※EXPLAIN の重要性
クエリの最適化には、EXPLAIN の出力結果を確認し、必要に応じてクエリを修正 → 再度 EXPLAIN を実行して結果を確認する、というプロセスを繰り返すことが欠かせません。

なお「クエリを最適化する」とは以下を指します。

修正前後でクエリの実行結果が同じであること
EXPLAIN の出力で、より効率的な実行計画が表示されること

id（クエリの識別子）

各SELECT文の識別子を示します。

JOINやUNIONなどある場合は複数行で出力されます。
同じidが複数行に表示される場合、それらのクエリは1つのクエリとして実行されています。

今回の結果

id: 1

クエリが 1 つの単純なSELECT文であるため、id=1が表示されています

※ select_type（クエリの種類）

クエリの種類を示します。

主な値

SIMPLE：UNIONやサブクエリを含まないSELECT, JOINなど
PRIMARY：主要なSELECT
UNION：UNION内の2つ目以降のSELECT
SUBQUERY：サブクエリ内の最初のSELECT

今回の結果

select_type: SIMPLE

UNIONやサブクエリがないためSIMPLEと表示されています

table（対象テーブル）

クエリの対象となるテーブル名を示します。

※テーブル名にエイリアス（別名）を設定していると、エイリアス名が表示されます。

今回の結果

table: user

クエリ対象のテーブルがuserであるため、userと表示されています

partitions

一致するパーティションを示します。

パーティションを設定している場合、パーティション情報が表示されます。
設定していない場合はNULLになります。
（パーティションとは DB の検索を早くするために、テーブルを内部的に分割することです）

今回の結果

partitions: NULL

パーティションを設定していないためNULLとなっています

※ type（アクセス方法）

対象のテーブルへのアクセス方法を示します（結合方法など）
クエリの効率性に大きな影響を与える重要な項目です。

主な値

const：定数比較（PRIMARY KEYやUNIQUEによる高速検索）
ref：インデックスを用いた等価検索（=または<=>演算子を使用）
ALL：インデックスを利用していないフルテーブルスキャン -> 改善必須

改善ヒント:
「ALL」と表示された場合、該当カラムにインデックスを張るか、クエリの条件を見直しましょう。特に大規模データではパフォーマンスへの影響が大きくなります。

今回の結果

type: ref

emailカラムにインデックスが設定されているため、インデックス参照（ref）で検索が行われています
emailインデックスを使って検索し、age > 20は後でフィルタリングされています

possible_keys（使用可能なインデックス）

クエリで使用できるインデックスを示します。
NULLが表示された場合、使用可能なインデックスが存在しません。

今回の結果

possible_keys: email

クエリ内のemailカラムにインデックスが設定されているため、possible_keys: emailが表示されています

※ key（使用されたインデックス）

実際に使用されたインデックス名を示します。
NULLの場合、インデックスが使用されていないためパフォーマンス改善の余地があります。

想定したインデックスが使われていない場合は、インデックス設定やクエリ内容を見直す余地があると考えられます。

今回の結果

key: email

emailインデックスが使用され、効率的な検索が行われています

key_len（インデックスの長さ）

使用されたインデックスの長さ（バイト単位）を示します。
インデックスの型により値が異なります。

※keyカラムにNULLと表示されている場合は、key_lenカラムもNULLとなります。

今回の結果

key_len: 402

emailカラムのデータ型がVARCHAR(100)です
int型などを使うと値は小さくなります

ref（インデックス参照の内容）

key（インデックス）カラムに対して、どのような検索条件が適用されたかを示します。

主な値

const：指定された条件が固定値（定数）であることを示します
- インデックスを使用した効率的な検索が行われます
NULL：インデックスが使用されていない場合

今回の結果

ref: const

検索条件が固定値（email = 'alice@example.com'）であるため、const と表示されています

rows（対象行数の推定）

検索する行数の推定値を示します。
多い場合はパフォーマンス改善の余地があるかもしれません。

今回の結果

rows: 2

インデックス検索の結果、2件の行が対象となったためrows: 2が表示されています

filtered（フィルタリングの割合）

クエリ条件に一致するテーブルの行の推定割合（%）を示します。

100（最大値）
- 条件にすべての行が一致し、追加のフィルタリングが行われなかったことを示します
- 効率的な検索が行われています
0に近い場合
- 条件に一致する行が少なく、多くの行がフィルタリングされたことを意味します
- 非効率な検索の可能性があります

今回の結果

filtered: 33.33

age > 20の条件でデータがフィルタリングされたため、filteredが33.33となっています

※ Extra（追加情報）

オプティマイザがクエリ実行時に選択した戦略や追加処理の内容を示します。
クエリの最適化状況を把握するための重要な指標です。

主な値

Using where
- インデックスだけでは検索条件を満たせず、WHERE句で追加のフィルタリングが行われた場合に表示されます
Using filesort
- インデックスを使用したソートが行われず、フルスキャンソートが実行された場合に表示され、パフォーマンスが低下する可能性があります

Extraで表示される項目は数が多く紹介しきれないため、一部を紹介しました。
気になる方は調べてみてください。

今回の結果

Extra: Using where

emailインデックスは使用されていますが、age > 20の条件はインデックス外でフィルタリングされているため、Using whereが表示されています

EXPLAIN の出力結果の読み方：重要な項目まとめ

これまで多くの項目について説明してきましたが、
チューニングする際に全部見るのは大変だよ、って思うかもしれません。

その場合、まずは以下の項目に注目すると良いと思います。

select_type
type
key
Extra

インデックス有無での比較

ここまで、インデックスの有無についていくつか話が出てきました。
インデックスの有無でそんなに変わるの？と思った方もいるかもしれないので、
実行計画の違いを比較してみます。

emailカラムにインデックスがある場合の実行計画

mysql> EXPLAIN SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | email         | email | 402     | const |    2 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

emailカラムにインデックスが無い場合の実行計画

mysql> EXPLAIN SELECT * FROM user WHERE email = 'alice@example.com' AND age > 20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

インデックス有無による結果の違い

項目	インデックス有	インデックス無
type	ref	ALL
key	email	NULL
rows	2	10
filtered	33.33	10.00

整理すると

インデックス有りの場合
- emailカラムにインデックスが使用されるため、効率的に検索が行われ、1件のデータのみが対象となります
インデックス無しの場合
- フルテーブルスキャン（ALL）が行われ、データ全体をスキャンするため、効率が悪化し、フィルタリングされた行の割合も減少します

※今回はデータが少ない例ですが、通常のシステムではデータ量が膨大になるため、適切にインデックスを張ることが重要です。
インデックスをうまく活用することで、検索速度が大きく向上し、パフォーマンスの改善が期待できます。

まとめ

実行計画は、SQLの最適化とパフォーマンス改善において非常に重要です。
特に以下のポイントに注目することで、効率的なSQLを作成できます。

必要に応じてインデックスを適切に設定する
EXPLAINで結果を確認し、不要なフルスキャンを避ける
項目ごとの意味を理解し、クエリの改善点を見つける

これを機に、実行計画についてさらに詳しく学び、活用してみてください！

終わりに

今回の記事を通して、「実行計画」について考えが整理できたので、よかったなと思います。

明日は、FrozenVoiceさんの2024年に導入して満足度が高かったアイテム10選という記事です。

毎年色々なアイテムを購入されているので、今年はどんなアイテムが紹介されるのか楽しみです。

参考