MySQL LEFT JOINとNOT EXISTSの使い分けとパフォーマンス差

はじめに

SQLの世界では、同じ目的を達成する複数の方法が存在します。その代表的な例が「LEFT JOIN を使った除外」と「NOT EXISTS を使った除外」です。一見似たような動きをするこれらの手法ですが、実際のパフォーマンスには大きな違いが出る場合があります。この記事では、両者の動作の違いを解説しつつ、実際にMySQL上で検証を行い、どちらがより高速なのかを検討していきます。

MySQLは8.4.5を利用しています。

---

テストテーブルの作成

まずはパフォーマンス検証用に、ユーザーと注文履歴の2つのテーブルを用意します。意図的にインデックスを張らないことで、フルスキャンの影響も観察しやすくしています。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    item VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

次に、それぞれのテーブルに10万件のデータを挿入します。ユーザーは10万件、注文はそのうち5万件のユーザーにだけ発行します。

-- ユーザーを10万件挿入
INSERT INTO users (id, name)
SELECT
    seq,
    CONCAT('user_', seq)
FROM (
    SELECT
        @row := @row + 1 AS seq
    FROM
        (SELECT 0 FROM information_schema.columns LIMIT 1000) AS t1,
        (SELECT 0 FROM information_schema.columns LIMIT 100) AS t2,
        (SELECT @row := 0) AS init
) AS numbers;

-- 5万件分の注文データを挿入（偶数ユーザーのみ）
INSERT INTO orders (id, user_id, item)
SELECT
    seq,
    seq * 2,
    CONCAT('item_', seq)
FROM (
    SELECT
        @row := @row + 1 AS seq
    FROM
        (SELECT 0 FROM information_schema.columns LIMIT 1000) AS t1,
        (SELECT 0 FROM information_schema.columns LIMIT 50) AS t2,
        (SELECT @row := 0) AS init
) AS numbers;

id	user_id	item
1	2	item_1
2	4	item_2
3	6	item_3
4	8	item_4
5	10	item_5
6	12	item_6
7	14	item_7
8	16	item_8
9	18	item_9
10	20	item_10

偶数のユーザーだけ注文するデータですね。

---

LEFT JOIN を使った未注文ユーザーの抽出

注文していないユーザーを抽出するために、LEFT JOIN と IS NULL を使うパターンが以下です。

SELECT
    u.id,
    u.name
FROM
    users AS u
LEFT JOIN
    orders AS o
        ON
            u.id = o.user_id
WHERE
    o.id IS NULL;

このクエリは一見わかりやすく、多くの人に好まれますが、MySQLの内部的にはフルスキャンになりがちで、場合によってはパフォーマンスが低下します。

id	name
1	user_1
3	user_3
5	user_5
7	user_7
9	user_9
11	user_11
13	user_13
15	user_15
17	user_17
19	user_19

---

NOT EXISTS を使った未注文ユーザーの抽出

次に、同じ目的を NOT EXISTS で実現してみましょう。

SELECT
    u.id,
    u.name
FROM
    users AS u
WHERE 
    NOT EXISTS (
        SELECT
            1
        FROM
            orders AS o
        WHERE
            o.user_id = u.id
    );

サブクエリがユーザーごとに評価されるため、効率が悪いように思われがちですが、実際にはインデックスの有無やオプティマイザの判断によって高速になるケースが少なくありません。

id	name
1	user_1
3	user_3
5	user_5
7	user_7
9	user_9
11	user_11
13	user_13
15	user_15
17	user_17
19	user_19

結果は同じです。

---

実行計画とパフォーマンスの比較

それぞれのクエリに対して、EXPLAINを使って実行計画を確認しました。

LEFT JOIN

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	u	NULL	ALL	NULL	NULL	NULL	NULL	100175	100.00	NULL
1	SIMPLE	o	NULL	ref	user_id	user_id	5	testdb.u.id	1	10.00	Using where; Not exists; Using index

NOT EXISTS

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	u	NULL	ALL	NULL	NULL	NULL	NULL	100175	100.00	NULL
1	SIMPLE	<subquery2>	NULL	eq_ref	<auto_distinct_key>	<auto_distinct_key>	5	testdb.u.id	1	100.00	Using where; Not exists
2	MATERIALIZED	o	NULL	index	user_id	user_id	5	NULL	50317	100.00	Using index

• LEFT JOIN パターンでは、orders テーブルの reference が確認されました。
• NOT EXISTS パターンでは、usersテーブルはallで共通ですが、ordersテーブルをindex scan＆equal referenceでより効率的な検索になっていました。

さらに、両者のクエリに対して SELECT SQL_NO_CACHE を使って測定したところ、以下のような実行時間の差がありました（あくまで一例）。

DIGEST_TEXT	COUNT_STAR	total_time_sec	avg_time_sec
LEFT JOIN	20	3.5257	0.1763
NOT EXISTS	20	1.8674	0.0934

このように、テーブルサイズが大きい場合やインデックスが不足している状況では、NOT EXISTS の方が有利な結果になることが多いです。

---

MySQL まとめ

「LEFT JOIN + IS NULL」と「NOT EXISTS」は、どちらも同じ論理的結果を返すことができますが、MySQLのオプティマイザが選ぶ実行計画によって、実行速度に差が生まれる可能性があります。

• 可読性重視なら LEFT JOIN も選択肢に入りますが、
• パフォーマンス重視であれば NOT EXISTS を検討すべきです。
• 特に大規模なテーブルでの除外クエリには注意が必要です。

実際の案件では、 EXPLAIN 使ってみましょう。

---

次回のテーマ

EXISTSでもDISTINCTを使わない形式でも早いのか次回で比べてみたいと思います。