Zenn
📝

MySQLで複数テーブルに跨るOR条件は避けるべき理由

に公開
MySQL
SQL
join
tech

結論

複数テーブルに跨るOR条件は、データ取得のパフォーマンスを著しく悪化させる（将来的に悪化する）可能性があるので、なるべく利用するべきではない。

問題となり得るQueryの例

例えば、こういったQueryです。
campaigns.tenant_id OR campaign_participants.account_id のLEFT JOINに跨った OR条件で抽出すると駆動テーブルが肥大になってしまう傾向にあります。

SELECT 
    `campaign_id`
FROM
    `campaigns`
    LEFT JOIN `campaign_participants` ON `campaigns`.`id` = `campaign_participants`.`campaign_id`
WHERE
    (`campaigns`.`tenant_id` = 100 OR `campaign_participants`.`account_id` = 10000)
    AND `campaigns`.`archived_at` IS NULL;

JOINの処理

パフォーマンスで問題となる場合は、殆どがJOINが影響していると思います。
JOIN時は2つのテーブル間で処理が行われます。JOINの基本的なアルゴリズムであるNested Loopというものがあります。
https://dev.mysql.com/doc/refman/8.0/ja/nested-loop-joins.html

Nested Loopにおける基本概念の駆動テーブル、内部テーブルについて簡単に紹介します。

駆動テーブル（driving table）

JOIN の処理において 最初に読み込まれ、基準となるテーブル
ループの外側で走査され、その行に応じて次のテーブル（内部テーブル）を参照する
例：

SELECT * FROM A JOIN B ON A.id = B.a_id;

この場合、実行計画で A から先に読まれるなら、A が駆動テーブルです。

内部テーブル（inner table）

  • 駆動テーブルの各行に対して 繰り返し参照されるテーブル
  • ループの内側でアクセスされる
  • 各駆動テーブルの行に対して「このキーに合うものがあるか？」をインデックスなどで探します

JOIN処理の実態：Nested Loop Join のイメージ

イメージとしては駆動テーブルに対して、各行に対してloop処理で結合処理を探します。
なので、駆動テーブルの規模大きければloopの回数が増えます。このloopが高負荷の原因となる場合があります。

for row in 駆動テーブル:
    内部テーブルから row に合う行を探す（インデックスまたは全走査）

内部テーブルのサイズは影響しない？

内部テーブルの結合データによります。

駆動テーブル：R(n)
内部テーブル：R(m)
と仮定します。

前述したように、駆動テーブルに対しては全行にてloop処理が実行されます。R(n)
それに対して、内部テーブルも全行を確認する必要があればR(n)×R(m)回の処理が走ります。
こういった場合は、R(n)×R(m)、R(m)×R(n)どちらにしても駆動テーブルのサイズに関係なくloop処理が走ります。

ただし、内部テーブルは必要なデータにアクセスできればいいので、JOIN対象のデータにindexが適切に貼られているとします。仮に、2回のテーブル検索で、データにアクセスできるとするとR(n)× 2 のloop処理でよくなるのです。

実行計画・実行結果で処理を確認

話を戻して、問題となり得るQueryの例　の場合のQuery処理を確認していきます。

実行計画

Queryのパフォーマンスを確認する際には、よく実行計画を確認すると思います。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	campaigns	NULL	ref	campaigns_tenant_id_foreign,campaigns_archived_at_tenant_id_id_index	campaigns_archived_at_tenant_id_id_index	5	const	407064	100.00	Using where; Using index
1	SIMPLE	campaign_participants	NULL	ref	campaign_participation_account	campaign_participation_account	4	demo.campaigns.id	3	100.00	Using where; Using index

一見すると、インデックスを使った効率的な参照になっているようにも思えます。
ただし、id = 1でのrowsが大きくなっています。（今後、もっと大きくなる可能性もあります。）

実行結果

# step-4
-> Filter: ((campaigns.tenant_id = 100) or (campaign_participants.account_id = 10000))
    (actual time=4165.354..4470.488 rows=232 loops=1)
# step-3
    -> Nested loop left join
        (actual time=2.439..4243.789 rows=2880925 loops=1)
# step-1-2
        -> Filter: (campaigns.archived_at is null)
            (actual time=0.106..180.690 rows=407064 loops=1)
# step-1-1
            -> Covering index lookup on campaigns using campaigns_archived_at_tenant_id_id_index
                (actual time=0.105..153.797 rows=407064 loops=1)
# step-2
        -> Covering index lookup on campaign_participants using campaign_participation_account
            (actual time=0.007..0.009 rows=7 loops=407064)

step1：campaigns テーブルのインデックススキャン

処理内容：campaigns.archived_at IS NULL を満たすレコードをcampaigns_archived_at_tenant_id_id_index を使ってインデックス上から取得
回数：1回のインデックススキャン
取得件数：407,064行
合計処理時間：約 0.15 秒
ポイント：行数は多いが、0.15 秒で終了している。これ自体が物凄い負荷が高い訳では無い。

step2：

Step 1 で取得した 407,064 行の campaigns に対して、1行ずつ campaign_participants を campaign_id でインデックスルックアップ
回数：407,064回のルックアップ
平均取得件数：7行程度（想定）
合計処理時間：約 4.2 秒
ポイント：このloop処理がもっとも負荷が高い

step3:

処理内容：Step 1 の campaigns と、Step 2 で取得した campaign_participants を Nested Loop Join で結合し、中間結果を構築
回数：1回（結合操作としては1プロセス）
生成件数：2,880,925行
合計処理時間：Step 2と同一（4.2 秒）※内部処理時間を包含
ポイント：Step 2の一連の処理。内部的には407,064行×7 で、約2,880,925行の内部的な中間レコードが生成されている。

step4：

処理内容：Step 3 の結合結果に対して、campaigns.tenant_id = 100 OR campaign_participants.account_id = 10000 の条件を評価し、該当レコードを抽出
評価対象件数：2,880,925行
抽出件数：232行
合計処理時間：約 0.3 秒
ポイント：2,880,925に対して、OR条件を評価している。これ自体もそこまで時間が掛かる訳では無い

結論（再）

複数テーブルに跨るOR条件は、データ取得のパフォーマンスを著しく悪化させる（将来的に悪化する）可能性があるので、なるべく利用するべきではない。

Discussion