MySQL最適化実践：LEFT JOINが期待通りに動作しない場合、駆動テーブルを強制指定する方法

データベースパフォーマンスチューニングの現場において、LEFT JOINはしばしば「確定的な操作」と見なされます。多くの開発者、経験豊富なDBAでさえ、LEFT JOINの左側に書かれたテーブルがクエリの駆動テーブルであると信じています。この認識は、ほとんどの単純なシナリオでは成立し、直感的であり、初心者の理解を容易にします。しかし、千万レベルあるいは億レベルのデータテーブルを結合する複雑なクエリに直面した場合、この「当たり前」という考え方がパフォーマンスボトルネックの隠れた原因となり、実行計画が期待に反する結果につながり、クエリ応答時間がミリ秒レベルから分レベルにまで急降下する可能性があります。

MySQLクエリオプティマイザは非常に複雑な「ブラックボックス」であり、その核心的な目標は最低の予測コストでクエリを完了させることです。この「コスト」の計算には、テーブルのサイズ、インデックス状況、フィルタ条件、統計情報など多くの要因が組み合わされています。オプティマイザが、LEFT JOINの右側のテーブルを駆動テーブルとして使用し、INNER JOINアルゴリズムで実行する方が、LEFT JOINの文字通りの順序で実行するよりもはるかにコストが低いと判断した場合、躊躇なくクエリロジックを「書き換えます」。この時点で、表示されるEXPLAINの出力は驚きをもたらすかもしれません：駆動テーブルは左側のテーブルではないのです！

このオプティマイザの「独断的な」行動は、多くの場合有益であり効率向上を目的としています。しかし、特定のシナリオでは災害的な結果をもたらす可能性があります。例えば、左側のテーブルが厳密にフィルタリングされて結果セットが極小であることが明確に分かっている一方で、右側のテーブルは巨大ですが効率的なインデックスを持っている場合、左側のテーブルが右側のテーブルを駆動し、効率的なIndex Nested-Loop Joinを経由することを期待するかもしれません。しかし、オプティマイザは右側のテーブルの特定フィールドのWHERE条件により、セマンティクスを誤解し、クエリをINNER JOINに書き換え、右側のテーブルを駆動テーブルとして選択し、フルテーブルスキャンを引き起こし、パフォーマンスが大幅に低下する可能性があります。

本記事は、基礎的なSQL作成を超え、パフォーマンスの深部へと踏み込もうとする上級開発者およびDBA向けに準備されています。我々は「小さいテーブルが大きいテーブルを駆動する」という原則的な議論にとどまらず、実践に直接切り込み、LEFT JOIN実行計画が「制御不能」になる背後の原理を深く分析し、経験豊富な指揮官のようにオプティマイザからJOIN順序の絶対的な制御権を取り戻す方法を重点的に伝授します。`STRAIGHT_JOIN」という「特権の剣」の利点と欠点を探求し、駆動テーブルの選択に間接的に影響を与えるその他の高度なテクニックを明らかにすることで、複雑な関連クエリに直面した際に、正確な戦略を講じ、実行計画が常に最適なルート上を走行できるようにします。

1. 駆動テーブルの理解：LEFT JOINの「暗黙のルール」とオプティマイザの「裏切り」

JOINを制御するためには、まず駆動テーブル（Driving Table）と被駆動テーブル（Driven Table）がクエリ実行プロセスにおいて果たす役割とワークフローを深く理解する必要があります。これは単なる二つの名前ではなく、JOINの全パフォーマンスを理解するための基盤です。

1.1 駆動テーブルと被駆動テーブルのメカニズム

JOINクエリを入れ子ループと想像してみてください。駆動テーブルは外側ループの源です。MySQLはまず駆動テーブルのデータ（WHERE条件によるフィルタリング後）を読み取り、結果セットを取得します。次に、この結果セットの各行に対して、データベースエンジンは被駆動テーブルで一致する行を探します。この「検索」プロセスが内側ループです。

-- JOINプロセスを示す概念的な疑似コード
for each row_D in driving_table { // 駆動テーブルの各行を反復
    for each row_P in driven_table { // 駆動テーブルの現在の行に対し、被駆動テーブルを反復
        if (join_condition_is_true(row_D, row_P)) {
            output_row(row_D, row_P); // 一致する行を出力
        }
    }
}

このモデルから明らかなように、駆動テーブルの結果セットが小さいほど、外側ループの回数は少なくなり、全JOIN操作の総コストは一般的に低くなります。これが「小さいテーブルが大きいテーブルを駆動する」という古典的な最適化原則の根源です。ここでの「小さいテーブル」は絶対的なデータ量が小さいという意味ではなく、このJOINに参加する際に、利用可能なすべての条件でフィルタリングされた後の結果セットが小さいことを意味します。

1.2 なぜLEFT JOINの左側が必ずしも駆動テーブルではないのか？

LEFT JOINのセマンティクスは、右側のテーブルに一致する行がなくても左側のテーブルのすべての行を返すことです。SQLの記述論理において、左側のテーブルが主導的です。しかし、MySQLオプティマイザが物理的実行計画を生成する際の主要な目標は、クエリの実行順序を維持することではなく、クエリの実行コストを最小化することです。

オプティマイザはあなたのSQL文を書き換えと変換を行い、その中で重要な変換の一つが「外結合の単純化」です。オプティマイザが、WHERE句の特定の条件がLEFT JOINを暗黙的にINNER JOINのセマンティクスに変換すると判断した場合、大胆に書き換えを行います。

典型的な「裏切り」のケースを見てみましょう。

2つのテーブルがあるとします：orders（注文テーブル、データ量は小さいが頻繁に更新される）とorder_details（注文詳細テーブル、データ量は巨大だがorder_idインデックスを持つ）。

-- クエリ1：標準的なLEFT JOIN、すべての注文とその詳細（存在する場合）をリストアップする意図
SELECT *
FROM orders o
LEFT JOIN order_details od ON o.id = od.order_id;

このクエリでは、ordersテーブルがおそらく駆動テーブルです。通常はより小さく、LEFT JOINのセマンティクスが明確だからです。

次に、右側のテーブルorder_detailsに対するWHERE条件を追加してみましょう。

-- クエリ2：WHEREで右側のテーブルフィルタリング
SELECT *
FROM orders o
LEFT JOIN order_details od ON o.id = od.order_id
WHERE od.quantity > 10; -- 右側のテーブルフィルタリング

重要なポイントがここにあります：WHERE od.quantity > 10という条件は、order_deta

タグ: MySQL JOIN 最適化クエリチューニングデータベース

6月21日 21:28 投稿

異端開発室

MySQL最適化実践：LEFT JOINが期待通りに動作しない場合、駆動テーブルを強制指定する方法