MySQL 入門 - SQL 実行プロセス

1. プロセス

2. コアアーキテクチャ

簡単に言うと、MySQL は主にサーバー層とストレージ エンジン層に分かれています。

• サーバー層: 主にコネクタ、クエリ キャッシュ、アナライザー、オプティマイザー、エグゼキュータなどが含まれます。ストアド プロシージャ、トリガー、ビュー、関数など、すべてのクロス ストレージ エンジン機能はこの層に実装されています。一般的なログ モジュールである binglog ログ モジュールもあります。
• ストレージ エンジン: 主にデータの保存と読み取りを担当し、交換可能なプラグイン アーキテクチャを採用し、InnoDB、MyISAM、Memory などの複数のストレージ エンジンをサポートします。その中で、InnoDB エンジンには独自のログ モジュール redolog モジュールがあります。現在最も一般的に使用されているストレージ エンジンは InnoDB であり、MySQL 5.5.5 以降、デフォルトのストレージ エンジンとして使用されています。

2.1 サーバー層の基本コンポーネントの紹介

1. コネクタ

コネクタは、高レベルのドアマンのように、主に認証と権限関連の機能に関係します。

主にデータベースへのユーザーログインとユーザーID認証を担当し、アカウントパスワード、権限、その他の操作の検証を含みます。ユーザーアカウントパスワードが渡されると、コネクタは権限テーブル内のユーザーのすべての権限を照会します。その後、この接続における権限ロジックの判断は、この時点で読み取られた権限データに依存します。つまり、接続が切断されない限り、管理者がユーザーの権限を変更しても、ユーザーには影響はありません。

2.クエリキャッシュ（MySQL 8.0以降は削除）

クエリ キャッシュは主に、実行した SELECT ステートメントとステートメントの結果セットをキャッシュするために使用されます。

接続が確立された後、クエリ ステートメントを実行すると、最初にキャッシュがクエリされます。MySQL は最初に SQL が実行されたかどうかを確認し、キーと値の形式でメモリにキャッシュします。キーはクエリの推定値であり、値は結果セットです。キャッシュ キーがヒットした場合は、クライアントに直接返されます。ヒットしなかった場合は、後続の操作が実行され、完了後に結果がキャッシュされ、次の呼び出しに使用されます。もちろん、キャッシュ クエリが実際に実行されると、テーブルに対するクエリ条件があるかどうかを確認するためにユーザーの権限がチェックされます。

実際のビジネス シナリオではクエリ キャッシュの無効化が非常に頻繁に発生する可能性があるため、MySQL クエリにキャッシュを使用することは推奨されません。テーブルを更新すると、このテーブルのすべてのクエリ キャッシュがクリアされます。頻繁に更新されないデータの場合は、キャッシュを使用することも考えられます。

したがって、ほとんどの場合、クエリ キャッシュの使用はお勧めしません。

MySQL バージョン 8.0 以降、キャッシュ機能は削除されました。公式もこの機能の実際の適用シナリオは少ないと判断したため、単純に削除されました。

3. アナライザー

MySQL がキャッシュにヒットしない場合は、アナライザーに入ります。アナライザーは主に SQL ステートメントの目的を分析するために使用されます。アナライザーもいくつかのステップに分かれています。

1. 最初のステップは字句解析です。SQL 文は複数の文字列で構成されています。まず、select などのキーワードを抽出し、クエリ テーブルを提案し、フィールド名を提案し、クエリ条件を提案する必要があります。これらの操作を完了すると、2 番目のステップに進みます。
2. 2 番目のステップである構文分析は、主に、入力した SQL が正しいかどうか、および MySQL 構文に準拠しているかどうかを判断することです。

これら 2 つの手順を完了すると、MySQL の実行を開始する準備が整いますが、どのように実行し、最良の結果を得るにはどうすればよいでしょうか。ここでオプティマイザーが役立ちます。

4. オプティマイザー

オプティマイザの役割は、複数のインデックスがある場合にインデックスを選択する方法、複数のテーブルをクエリするときに関連付け順序を選択する方法など、最適であると判断した実行プランを実行することです (最適ではない場合もあります。この記事では、この知識の部分について詳しく説明します)。

オプティマイザの後では、このステートメントの具体的な実行が決定されたと言えます。

5. アクチュエータ

実行プランが選択されると、MySQL は実行を開始する準備が整います。まず、実行前にユーザーに権限があるかどうかを確認します。ユーザーに権限がない場合は、エラー メッセージが返されます。ユーザーに権限がある場合は、エンジン インターフェイスが呼び出され、インターフェイス実行の結果が返されます。

3. ステートメント分析

3.1 クエリステートメント

SQL は、クエリと更新 (追加、更新、削除) の 2 つのタイプに分けられます。まずクエリ ステートメントを分析してみましょう。ステートメントは次のとおりです。

select * from tb_student A where A.age='18' and A.name=' 张三';

上記の説明と組み合わせて、このステートメントの実行フローを分析します。

まず、ステートメントに権限があるかどうかを確認します。権限がない場合は、直接エラー メッセージが返されます。権限が付与されている場合、MySQL 8.0 より前では、まずキャッシュが照会され、この SQL ステートメントをキーとして使用して、メモリ内に結果があるかどうかが確認されます。結果がある場合は、キャッシュが直接キャッシュされます。結果がない場合は、次の手順に進みます。

アナライザーを通じて、字句解析が実行され、SQL ステートメントのキー要素が抽出されます。たとえば、上記のステートメントはクエリ選択であり、クエリ対象のテーブル名は tb_student であり、すべての列をクエリする必要があり、クエリ条件はこのテーブルの ID = '1' です。次に、キーワードが正しいかどうかなど、SQL ステートメントに構文エラーがないか確認します。チェックが OK の場合は、次の手順に進みます。

次のステップは、オプティマイザが実行プランを決定することです。上記の SQL ステートメントには、次の 2 つの実行プランがあります。

a. まず、学生テーブルで「Zhang San」という名前の学生を検索し、その年齢が 18 歳かどうかを判断します。
b. まず 18 歳の学生を見つけ、次に名前が「Zhang San」である学生を検索します。

次に、オプティマイザーは独自の最適化アルゴリズムに基づいて、実行効率が最も高いソリューションを選択します (オプティマイザーは、これが常に最適なソリューションであるとは限らないと考えています)。実行プランを確認したら、実行を開始する準備が整います。

権限チェックを実行します。権限がない場合はエラーメッセージが返されます。権限がある場合は、データベースエンジンインターフェイスが呼び出され、エンジンの実行結果が返されます。

3.2 更新ステートメント

上記はクエリSQLの実行プロセスですが、更新文がどのように実行されるかを見てみましょう。 SQL ステートメントは次のとおりです。

update tb_student A set A.age='19' where A.name=' 張三';

張三の年齢を変更しましょう。実際のデータベースでは、この年齢フィールドは絶対に設定されません。そうしないと、テクニカルディレクターに殴られてしまいます。実際、このステートメントは基本的に前のクエリのプロセスに従いますが、更新を実行するときにログを記録する必要があるため、ログモジュールが導入されます。 MySQLの組み込みログモジュールはbinlog（アーカイブログ）であり、すべてのストレージエンジンで使用できます。 私たちがよく使用するInnoDBエンジンにも、ログモジュールredo log（リドログ）が付属しています。 InnoDBモードでのこのステートメントの実行プロセスについて説明します。プロセスは次のとおりです。

まず、Zhang San のデータをクエリします。キャッシュがある場合は、それも使用されます。

次に、クエリ ステートメントを取得し、年齢を 19 に変更し、エンジン API インターフェイスを呼び出してこのデータ行を書き込みます。InnoDB エンジンはデータをメモリに保存し、REDO ログを記録します。この時点で、REDO ログは準備状態になり、実行が完了し、いつでも送信できることをエグゼキュータに伝えます。

通知を受信した後、エグゼキュータはバイナリログを記録し、エンジン インターフェイスを呼び出して、REDO ログをコミット済み状態に送信します。

更新が完了しました。

ここで、生徒の中には、なぜ 2 つのログ モジュールが必要なのか、1 つのログ モジュールで済ませられないのかと疑問に思う人もいるでしょう。

これは、MySQL が当初 InnoDB エンジンと互換性がなかったためです (InnoDB エンジンは、他社によってプラグインとして MySQL に挿入されました)。MySQL のネイティブ エンジンは MyISAM ですが、REDO ログは InnoDB エンジン独自のものであり、他のストレージ エンジンにはないことがわかっています。このため、クラッシュ セーフ機能 (クラッシュ セーフ機能とは、データベースが異常に再起動しても、以前に送信されたレコードが失われない機能) がなく、binlog ログはアーカイブにしか使用できません。

1 つのログ モジュールのみを使用できないという意味ではありませんが、InnoDB エンジンは REDO ログを介したトランザクションをサポートします。すると、一部の学生は、2 つのログ モジュールを使用してもそれほど複雑にならないのかと疑問に思うかもしれません。なぜ redo ログで準備のコミット前ステータスを導入する必要があるのでしょうか。ここでは、なぜこれを行うのかを説明するために、背理法による証明を使用します。

まず、REDO ログを書き込んで直接コミットし、次に binlog を書き込みます。REDO ログを書き込んだ後にマシンがクラッシュし、binlog が書き込まれなかったとします。マシンが再起動すると、マシンは REDO ログを通じてデータを復元します。ただし、この時点では binlog にデータは記録されません。後でマシンをバックアップすると、このデータは失われます。同時に、マスター スレーブ同期でもこのデータは失われます。

最初に binlog を書き込み、次に redo log を書き込みます。binlog を書き込んだ後、マシンが異常に再起動したとします。redo log がないため、マシンはこのレコードを回復できません。ただし、binlog には別のレコードがあります。すると、上記と同じ理由でデータの不整合が発生します。

REDOログ2フェーズコミット方式を採用すると状況は異なります。binglogを書き込んだ後、REDOログを送信することで上記のような問題の発生を防ぎ、データの一貫性を確保できます。そこで疑問なのが、極端な状況が存在するかどうかです。 redo ログがコミット前の状態にあり、binglog が書き込まれているとします。このとき異常な再起動が発生するとどうなるでしょうか。 これは MySQL の処理メカニズムに依存します。MySQL の処理プロセスは次のとおりです。

REDO ログが完了しているかどうかを判断します。完了している場合は、すぐにコミットします。

REDO ログが事前コミット済みだがコミットされていない状態の場合、バイナリログが完了するとトランザクションはロールバックされます。

これにより、データの一貫性の問題が解決されます。

4. 結論

• MySQL は主にサーバー層とエンジン層に分かれています。サーバー層には主にコネクタ、クエリ キャッシュ、アナライザ、オプティマイザ、エグゼキュータ、ログ モジュール (binlog) が含まれます。このログ モジュールはすべての実行エンジンで共有でき、redolog は InnoDB でのみ使用できます。
• エンジン層はプラグインベースで、現在は主に MyISAM、InnoDB、メモリなどが含まれています。
• SQL 実行プロセスは 2 つのカテゴリに分かれています。1 つのカテゴリは、クエリ プロセス用で、次のようになります: 権限チェック -> クエリ キャッシュ -> アナライザ -> オプティマイザ -> 権限チェック -> エグゼキュータ -> エンジン
• 更新ステートメントの実行プロセスは次のとおりです: Analyzer ----》 権限チェック ----》 Executor—》 エンジン — redo log prepare—》 binlog—》 redo log commit

この記事はこれで終わりです。123WORDPRESS.COM の他のコンテンツにも注目していただければ幸いです。