SQL 解析順序)

序文：

私はずっと、SQL 文がどのように、どのような順序で実行されるのかを知りたいと思っていました。そこで、さまざまな関係者からの情報を調べてまとめ、次のブログ記事を作成しました。

この記事では、MySQL の全体的なアーキテクチャ ---> クエリ実行プロセス ---> ステートメント実行順序の観点から知識を探ります。

1. MySQL アーキテクチャの概要:

建築物は、必要な説明文を添えた写真で見るのが最適です。

下の写真は参考図書の写真を元に、私なりの解釈を加えています。

上の図から、アーキテクチャ全体が 2 つのレイヤーに分かれていることがわかります。上層は「SQL レイヤー」と呼ばれる MySQLD で、下層は上層にインターフェイスを提供するさまざまなストレージエンジンで、「ストレージエンジンレイヤー」と呼ばれます。その他のモジュールやコンポーネントの機能は名前から簡単に理解できるので、ここでは詳細には触れません。

2. クエリ実行プロセス

もう少し詳しく説明して、私の理解に基づいてクエリ実行プロセスを説明します。

1. 接続する

1.1 クライアントはクエリ要求を開始し、クライアントの「接続管理モジュール」をリッスンして要求を受信します。

1.2 リクエストを「接続/スレッドモジュール」に転送する

1.3 'ユーザーモジュール'を呼び出して認証チェックを実行する

1.4 チェックに合格すると、「接続/スレッドモジュール」は「スレッド接続プール」からアイドル状態のキャッシュされた接続スレッドを取り出し、クライアントのリクエストに接続します。失敗した場合は、新しい接続リクエストが作成されます。

2. 処理

2.1 まずキャッシュを照会してクエリステートメントが完全に一致するかどうかを確認し、次に権限があるかどうかを確認します。両方とも成功した場合は、データを直接取得して返します。

2.2 前のステップが失敗した場合は、「コマンドパーサー」に転送され、語彙解析と構文解析の後に解析ツリーが生成されます。

2.3 次は前処理段階です。パーサーが解決できないセマンティクスを処理し、権限などをチェックし、新しい解析ツリーを生成します。

2.4 対応するモジュールに転送して処理する

2.5 SELECT クエリの場合、クエリオプティマイザーは多くの最適化を実行し、実行プランを生成します。

2.6 リクエストを受信した後、モジュールは接続されたユーザーが「アクセス制御モジュール」を通じてターゲットテーブルとターゲットフィールドにアクセスする権限を持っているかどうかを確認します。

2.7 はいの場合、「テーブル管理モジュール」を呼び出してテーブルキャッシュが存在するかどうかを確認します。はいの場合、対応するテーブルを直接ロックし、そうでない場合はテーブルファイルを再度開きます。

2.8テーブルのメタデータに従って、テーブルのストレージエンジンタイプとその他の情報を取得し、インターフェースを介して対応するストレージエンジン処理を呼び出す

2.9 上記のプロセス中にデータの変更が発生した場合、ログ機能がオンになっていると、対応するバイナリログファイルに記録されます。

3. 結果

3.1クエリ要求が完了すると、結果セットが「接続/スレッドモジュール」に返されます。

3.2 返される値は、成功や失敗などの対応するステータスインジケーターである場合もあります。

3.3 「接続/スレッドモジュール」は、後続のクリーンアップ作業を実行し、リクエストを待機し続けるか、クライアントから切断します。

一枚の写真で簡単にまとめる

3. SQL解析順序

次に、さらに一歩進んで、SQL ステートメントの過去と現在を見てみましょう。

まず、例文を見てみましょう

選択する
 < 選択リスト >
から
 < 左テーブル > < 結合タイプ >
JOIN < 右テーブル > ON < 結合条件 >
どこ
 < 条件 >
グループ化
 < グループリスト >
持つ
 < 条件がある >
注文する
 < 順序条件 >
LIMIT <制限数>

ただし、実行順序は以下のとおりです

<左テーブル> から
ON <結合条件>
<結合タイプ> JOIN <右テーブル>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
選択 
DISTINCT <選択リスト>
ORDER BY <order_by_condition>
LIMIT <制限数>

こうなるとは思っていませんでしたが、一目見るととても自然で調和がとれています。どこから手に入れるのでしょうか? 条件を常にフィルタリングし、同じものまたは異なるものを選択して並べ替えます。すると、最初のいくつかを取得することがわかります。

それでは、詳細を順を追って見ていきましょう。

準備

1. テストデータベースを作成する

データベース testQuery を作成する

2. テストテーブルを作成する

テーブルテーブル1を作成する
（
 uid VARCHAR(10) NOT NULL、
 名前 VARCHAR(10) NOT NULL,
 主キー(uid)
)ENGINE=INNODB デフォルト文字セット=UTF8;

テーブルテーブル2を作成する
（
 oid INT NOT NULL auto_increment、
 uid VARCHAR(10)、
 主キー(oid)
)ENGINE=INNODB デフォルト文字セット=UTF8;

3. データを挿入する

table1(uid,name) に値('aaa','mike'),('bbb','jack'),('ccc','mike'),('ddd','mike') を挿入します。

table2(uid)にVALUES('aaa'),('aaa'),('bbb'),('bbb'),('bbb'),('ccc'),(NULL)を挿入します。

4. 最終的に望む結果

選択
 a.uid、
 count(b.oid) AS 合計
から
 表1 AS a
LEFT JOIN table2 AS b ON a.uid = b.uid
どこ
 a. 名前 = 'マイク'
グループ化
 a.uid
持つ
 カウント(b.oid) < 2
注文する
 合計DESC
制限 1;

！今すぐ SQL 解析の旅を始めましょう。

1. から

複数のテーブルが関係する場合、左側のテーブルの出力が右側のテーブルの入力として使用され、仮想テーブル VT1 が生成されます。

(1-J1) デカルト積

関連する 2 つのテーブルの直積 (CROSS JOIN) を計算し、仮想テーブル VT1-J1 を生成します。

mysql> テーブル1、テーブル2から*を選択します。
+-----+------+-----+------+
| uid | 名前 | oid | uid |
+-----+------+-----+------+
| aaa | マイク | 1 | aaa |
| bbb | ジャック | 1 | aaa |
| ccc | マイク | 1 | aaa |
| ddd | マイク | 1 | aaa |
| aaa | マイク | 2 | aaa |
| bbb | ジャック | 2 | aaa |
| ccc | マイク | 2 | aaa |
| ddd | マイク | 2 | aaa |
| aaa | マイク | 3 | bbb |
| bbb | ジャック | 3 | bbb |
| ccc | マイク | 3 | bbb |
| ddd | マイク | 3 | bbb |
| aaa | マイク | 4 | bbb |
| bbb | ジャック | 4 | bbb |
| ccc | マイク | 4 | bbb |
| ddd | マイク | 4 | bbb |
| aaa | マイク | 5 | bbb |
| bbb | ジャック | 5 | bbb |
| ccc | マイク | 5 | bbb |
| ddd | マイク | 5 | bbb |
| aaa | マイク | 6 | ccc |
| bbb | ジャック | 6 | ccc |
| ccc | マイク | 6 | ccc |
| ddd | マイク | 6 | ccc |
| aaa | マイク | 7 | NULL |
| bbb | ジャック | 7 | NULL |
| ccc | マイク | 7 | NULL |
| ddd | マイク | 7 | NULL |
+-----+------+-----+------+
セット内の行数 (0.00 秒)

(1-J2)ONフィルター

仮想テーブル VT1-J1 に基づいて、ON 述語条件を満たすすべての列がフィルタリングされ、仮想テーブル VT1-J2 が生成されます。

注:文法上の制約により、ここでは代わりに「WHERE」が使用されています。これにより、読者は 2 つの間の微妙な関係も感じることができます。

mysql> 選択
 -> *
 -> から
 -> テーブル1、
 -> 表2
 -> どこ
 -> テーブル1.uid = テーブル2.uid
 -> ;
+-----+------+-----+------+
| uid | 名前 | oid | uid |
+-----+------+-----+------+
| aaa | マイク | 1 | aaa |
| aaa | マイク | 2 | aaa |
| bbb | ジャック | 3 | bbb |
| bbb | ジャック | 4 | bbb |
| bbb | ジャック | 5 | bbb |
| ccc | マイク | 6 | ccc |
+-----+------+-----+------+
セット内の行数 (0.00 秒)

(1-J3) 外部列を追加する

外部結合（LEFT、RIGHT、FULL）を使用すると、メインテーブル（保持テーブル）内の ON 条件を満たさない列も外部行として VT1-J2 に追加され、仮想テーブル VT1-J3 が生成されます。

mysql> 選択
 -> *
 -> から
 -> テーブル1 AS a
 -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid;
+-----+------+------+------+
| uid | 名前 | oid | uid |
+-----+------+------+------+
| aaa | マイク | 1 | aaa |
| aaa | マイク | 2 | aaa |
| bbb | ジャック | 3 | bbb |
| bbb | ジャック | 4 | bbb |
| bbb | ジャック | 5 | bbb |
| ccc | マイク | 6 | ccc |
| ddd | マイク | NULL | NULL |
+-----+------+------+------+
セット内の行数 (0.00 秒)

以下は、インターネットで見つけた「SQL JOINS」についての非常に鮮明な説明図です。権利を侵害している場合は、お知らせください。ありがとうございます。

2. どこで

VT1 プロセスで生成された一時テーブルがフィルタリングされ、WHERE 句を満たす列が VT2 テーブルに挿入されます。

知らせ：

現時点では、グループ化のため、集計操作は使用できません。また、SELECT で作成されたエイリアスも使用できません。

ONとの違い:

外部列がある場合、ON にすると関連付けられたテーブルがフィルターされ、メインテーブル (保持されたテーブル) はすべての列を返します。

外部列を追加しない場合、効果は同じです。

応用：

メインテーブルのフィルタリングは WHERE に配置する必要があります。

関連テーブルの場合、最初に条件をクエリしてから接続する場合は ON を使用し、最初に接続してから条件をクエリする場合は WHERE を使用します。

mysql> 選択
 -> *
 -> から
 -> テーブル1 AS a
 -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
 -> どこ
 -> a.NAME = 'マイク';
+-----+------+------+------+
| uid | 名前 | oid | uid |
+-----+------+------+------+
| aaa | マイク | 1 | aaa |
| aaa | マイク | 2 | aaa |
| ccc | マイク | 6 | ccc |
| ddd | マイク | NULL | NULL |
+-----+------+------+------+
セット内の行数 (0.00 秒)

3. グループ化

この句は、GROUP BY の列に従って VT2 で生成されたテーブルをグループ化します。 VT3 テーブルを生成します。

知らせ：

SELECT や HAVING などの後続の処理ステートメントで使用される列は、GROUP BY に含める必要があります。含まれていない列については、集計関数を使用する必要があります。

理由：

GROUP BY はテーブルへの参照を変更し、新しい参照モードに変換します。これにより、次のレベルの論理演算に使用できる列の数が削減されます。

私の理解はこうです:

グループ化フィールドに従って、同じグループ化フィールドを持つレコードは 1 つのレコードに結合されます。これは、各グループはフィルタリングされない限り 1 つのレコードしか返すことができず、グループ化フィールドにないフィールドには複数の値がある場合があり、複数の値を 1 つのレコードに入れることができないため、これらの複数値列は集計関数を使用して単一の値に変換する必要があるためです。

mysql> 選択
 -> *
 -> から
 -> テーブル1 AS a
 -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
 -> どこ
 -> a.NAME = 'マイク'
 -> グループ化
 -> a.uid;
+-----+------+------+------+
| uid | 名前 | oid | uid |
+-----+------+------+------+
| aaa | マイク | 1 | aaa |
| ccc | マイク | 6 | ccc |
| ddd | マイク | NULL | NULL |
+-----+------+------+------+
セット内の行数 (0.00 秒)

4. 持つ

この句は、VT3 テーブル内のさまざまなグループをフィルタリングし、グループ化されたデータのみに作用します。HAVING 条件を満たす句は、VT4 テーブルに追加されます。

mysql> 選択
 -> *
 -> から
 -> テーブル1 AS a
 -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
 -> どこ
 -> a.NAME = 'マイク'
 -> グループ化
 -> a.uid
 -> 持つ
 -> count(b.oid) < 2;
+-----+------+------+------+
| uid | 名前 | oid | uid |
+-----+------+------+------+
| ccc | マイク | 6 | ccc |
| ddd | マイク | NULL | NULL |
+-----+------+------+------+
セット内の行数 (0.00 秒)

5. 選択

この句は、SELECT 句内の要素を処理し、VT5 テーブルを生成します。

(5-J1) SELECT句の式を計算し、VT5-J1を生成する

(5-J2)異なる

VT5-1の重複列を見つけて削除し、VT5-J2を生成します。

クエリで DISTINCT 句が指定されている場合は、一時的なメモリ内テーブルが作成されます (メモリに収まらない場合は、ディスクに保存する必要があります)。この一時テーブルのテーブル構造は、前の手順で生成された仮想テーブル VT5 と同じです。違いは、重複データを排除するために、DISTINCT 操作の列に一意のインデックスが追加されていることです。

mysql> 選択
 -> a.uid、
 -> count(b.oid) 合計として
 -> から
 -> テーブル1 AS a
 -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
 -> どこ
 -> a.NAME = 'マイク'
 -> グループ化
 -> a.uid
 -> 持つ
 -> count(b.oid) < 2;
+-----+-------+
| uid | 合計 |
+-----+-------+
| ccc | 1 |
| 0 |
+-----+-------+
セット内の行数 (0.00 秒)

6. 注文する

VT5-J2 のテーブルから、ORDER BY 句の条件に従って結果をソートし、VT6 テーブルを生成します。

知らせ：

エイリアスを使用できる唯一の場所は SELECT です。

mysql> 選択
 -> a.uid、
 -> count(b.oid) 合計として
 -> から
 -> テーブル1 AS a
 -> LEFT OUTER JOIN table2 AS b ON a.uid = b.uid
 -> どこ
 -> a.NAME = 'マイク'
 -> グループ化
 -> a.uid
 -> 持つ
 -> count(b.oid) < 2
 -> 順序
 -> 合計 DESC;
+-----+-------+
| uid | 合計 |
+-----+-------+
| ccc | 1 |
| 0 |
+-----+-------+
セット内の行数 (0.00 秒)

7.制限

LIMIT 句は、前の手順で取得した VT6 仮想テーブルから、指定された位置から指定された行データを選択します。

知らせ：

オフセットと行の正と負の値の影響。

オフセットが大きい場合、効率は非常に低くなります。次の操作を実行できます。

サブクエリを使用して最適化します。サブクエリでは、まずインデックスから最大 ID を取得し、次に逆順に並べ替えて、N 行の結果セットを取得します。

INNER JOIN 最適化を使用すると、JOIN 句でもインデックスから ID リストを取得することが優先され、クエリが直接関連付けられて最終結果が取得されます。

mysql> 選択
 -> a.uid、
 -> count(b.oid) 合計として
 -> から
 -> テーブル1 AS a
 -> LEFT JOIN table2 AS b ON a.uid = b.uid
 -> どこ
 -> a.NAME = 'マイク'
 -> グループ化
 -> a.uid
 -> 持つ
 -> count(b.oid) < 2
 -> 順序
 -> 合計DESC
 -> 制限 1;
+-----+-------+
| uid | 合計 |
+-----+-------+
| ccc | 1 |
+-----+-------+
セット内の行数 (0.00 秒)

これで SQL 解析の過程は終わりです。上の図はそれをまとめたものです。

参考図書:

MySQL パフォーマンスチューニングとアーキテクチャの実践
「MySQL テクノロジーインサイダー: SQL プログラミング」

終わり：

さて、この深い理解の旅はここでほぼ終わりです。あまり深くはありませんが、いくつかのものをつなぎ合わせただけです。以前に読んだ本もいくつか参照しました。師匠の文章は確かに違います。そして、その過程で多くのことを得ました。最も重要なことは、コンピュータソフトウェアの世界の壮大さをさらに実感することです〜

また、私の知識が限られているため、抜けや間違いが避けられません。もし何かお気づきの点がありましたら、ご指摘、ご訂正をお願いいたします。よろしくお願いします〜

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

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