MySQLにおけるSQLの実行順序についてのちょっとした質問

今日、仕事中に左結合に関するSQLの問題に遭遇しました。後で解決しましたが、この問題を通じてSQLの実行順序について学びました。

シーンの復元

セキュリティ上の紛争を回避するには、シナリオをシミュレートします。

生徒テーブルS、成績テーブルGがあります

テーブル「test_student」を作成します（
 `id` bigint(20) NOT NULL COMMENT '学生番号',
	`sex` TINYINT デフォルト '0' コメント '性別 0-男性 1-女性',
 `name` varchar(255) デフォルト NULL コメント '名前'
)ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='student table';
 
テーブル `test_score` を作成します (
 `id` bigint(20) NOT NULL COMMENT '学生番号',
 `score` int NOT NULL COMMENT 'score',
	`level` TINYINT COMMENT 'グレード 0-不合格 1-合格 2-良好 3-優秀'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='スコアテーブル';
 
-- 生徒を初期化します INSERT INTO test_student VALUES(1, 0, '张三'), (2, 0, '李四'), (3, 1, '王X芳');

-- スコアを初期化します INSERT INTO test_score VALUES(1, 10, 0), (2, 20, 0), (3, 100, 3);

今、生徒のテストの点数を調べたいという需要があります。可能なSQL

ts.name を '名前'、tc.score を 'スコア' として選択します
test_student ts より
左結合 test_score tc
ON ts.id = tc.s_id;

すべて順調に進んでいました。試験が終わった直後、突然、ある生徒が転校してきました。

test_student VALUES(4, 0, '新入生') に INSERT INTO します。

結果がない、またはSQLクエリが現在実行されていない

先生が試験を受けた生徒の点数だけを確認する必要がある場合はどうすればよいですか?

1. 内部結合を使用する

ts.name を '名前'、tc.score を 'スコア' として選択します
test_student ts より
内部結合テストスコアtc
ON ts.id = tc.s_id;

2. 条件付きフィルタリングを追加する

ts.name を '名前'、tc.score を 'スコア' として選択します
test_student ts より
LEFT JOIN test_score tc
オン ts.id = tc.s_id
かつtc.scoreがNULLではない
;

条件付きフィルタリングを追加してもまだ正しくないことがわかりました。代わりに where を使用するのはいかがでしょうか?

ts.name を '名前'、tc.score を 'スコア' として選択します
test_student ts より
左結合 test_score tc
オン ts.id = tc.s_id
tc.score が NULL ではない場合
;

ビンゴ、ではなぜどこが正しいのでしょうか?

これはSQLの実行順序に関係する。

どこで参加するか

上記の例から推測すると

結果セットから条件を満たすレコードをフィルタリングし、条件を満たさないレコードを破棄します。

結合操作: 完全な結果を得るために、2 つ以上のテーブルから結果を取得する必要がある場合があります。結合を実行する必要があります。

上記の例で使用した INNER JOIN に加えて、使用できる結合は他にもいくつかあります。

以下は、使用できる JOIN の種類とそれらの違いの一覧です。

JOIN: テーブルに少なくとも1つの一致がある場合に行を返します
LEFT JOIN: 右側のテーブルに一致するものがない場合でも、左側のテーブルのすべての行を返します。
右結合: 左のテーブルに一致するものがない場合でも、右のテーブルのすべての行を返します。
FULL JOIN: いずれかのテーブルに一致する行を返します(MySQL ではサポートされていないため、ビューを使用して実装することを検討できます)

ここでは、SQL を結合する方法について、シンプルでわかりやすいチュートリアルを紹介します。

SQL 順序

上記から、SQL では on が where 条件の前に記述されていることがわかります。では、データベースエンジンが SQL を分析して実行する場合、on も where の前に記述されるのでしょうか?

一般的なSQLの記述順序

1.SELECT [列名*はすべての列を表します]

2.FROM [テーブル名]

3.join_type JOIN [テーブル名]

4.ON [結合条件]

5.WHERE [フィルター条件]

6. GROUP BY [グループ化フィールド]

7. HAVING [グループ化条件]

8.ORDER BY [並べ替えフィールド]

では、SQL が実行される順序は何でしょうか?

標準の SQL 解析順序は次のとおりです。

1. FROMはさまざまなデータソース（テーブル）からデータを組み立てます

2.WHERE 条件に基づいてレコードをフィルタリングする

3. GROUP BYでデータをグループ化する

4. avg、sumなどの集計関数を計算する

5. HAVING句を使用してグループをフィルタリングする

6. すべての式を計算する

7. ORDER BYを使用して結果を並べ替える

では、SQL の実行順序は何でしょうか?

1.FROM: 最初の2つのテーブルに対して直積を実行し、仮想テーブルvt1を生成します。

2.ON: vt1にon条件を適用し、join_conditionを満たすものだけが仮想テーブルvt2に挿入されます。

3. OUTER (結合): OUTER JOIN が指定されている場合、保存されたテーブルに見つからない行は外部行として vt2 に追加され、t3 が生成されます。from に 2 つ以上のテーブルが含まれている場合は、前の結合によって生成された結果テーブルと次のテーブルに対して手順 1 と 2 を繰り返し、直接終了します。

4.WHERE: vt3 に対して where フィルターを実行し、where 条件を満たすものだけを vt4 に挿入します。

5.GROUP BY: vt4をgroup byフィールドでグループ化してvt5を取得します。

6.HAVING: vt5にHAVINGフィルタを適用し、having_conditionが真となるグループのみをvt6に挿入する

7.SELECT: 選択リストを処理してvt7を生成する

8.DISTINCT: vt7から重複行を削除してvt8を生成する

9.ORDER BY: order by句の列リストでvt8の行をソートしてカーソルvc9を生成します。

10.LIMIT (MySQL): vc9の先頭から指定された行数を選択してvt10を生成し、呼び出し元に返します。

この時点で、適切な SQL を書くのは簡単ではないことがわかるはずです。ただし、SQL の実行順序を理解しておくと、開発中に優れたプログラムを作成できるようになります。

たとえば、結合テーブルが多すぎることはできません (最初に条件をフィルタリングし、次にテーブルを接続して、テーブル内の関連するクエリフィールドにインデックスを作成します。これにより、ビッグデータ内の複数のテーブルに対するクエリが大幅に高速化されます)。次回は、このような SQL 最適化の問題を研究して整理します。

練習してみましょう！

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません: