MySQLでSQL文がどのように実行されるかの詳細な説明

概要

最近MySQL関連の知識を勉強し始めました。学んだ知識ポイントと自分の理解を元に整理して共有します。この記事ではMySQL内でSQLクエリがどのように流れるか、SQL文がどのように更新されるかなど、MySQLでの次のSQL文の実行プロセスを分析します。

1. MySQLアーキテクチャの分析

以下は MySQL の簡単なアーキテクチャ図です。

MySQLは主にサーバー層とストレージエンジン層に分かれています

サーバー層: 主にコネクタ、クエリ キャッシュ、アナライザー、オプティマイザー、エグゼキュータなどが含まれます。ストアド プロシージャ、トリガー、ビュー、関数など、すべてのクロス ストレージ エンジン機能はこの層に実装されています。一般的なログ モジュールである binglog ログ モジュールもあります。

ストレージ エンジン: 主にデータの保存と読み取りを担当し、交換可能なプラグイン アーキテクチャを採用し、InnoDB、MyISAM、Memory などの複数のストレージ エンジンをサポートします。その中で、InnoDB エンジンには独自のログ モジュール redolog モジュールがあります。

デフォルトのエンジンとして InnoDB バージョン 5.5.5 が使用されます。

コネクタ

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

クエリキャッシュ

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

頻繁に更新されるデータの場合、キャッシュの有効時間が短すぎて、効果が良くないことが多いため、Mysqlクエリにキャッシュを使用することは推奨されません。頻繁に更新されないデータの場合、キャッシュを使用することは可能です。キャッシュ機能はMysqlバージョン8.0以降で削除されました。公式も、この機能の実際の適用シナリオは比較的少ないため、単純に削除されたと考えています。

アナライザ

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

最初のステップは字句解析です。SQL 文は複数の文字列で構成されています。まず、select などのキーワードを抽出し、クエリ テーブルを提案し、フィールド名を提案し、クエリ条件を提案する必要があります。これらの操作を完了すると、2 番目のステップに進みます。

2 番目のステップである構文分析は、主に、入力した SQL が正しいかどうか、および MySQL 構文に準拠しているかどうかを判断することです。

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

オプティマイザ

オプティマイザの役割は、複数のインデックスがある場合にどのようにインデックスを選択するか、複数のテーブルをクエリする場合にどのように関連付け順序を選択するかなど、最適であると判断した実行プランを実行することです（最適ではない場合もあります）。

アクチュエータ

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

2. ステートメント分析

2.1 クエリステートメント

ここまで述べてきましたが、SQL ステートメントはどのように実行されるのでしょうか?実際、SQL は 2 つのタイプに分けられます。1 つはクエリ、もう 1 つは更新 (追加、更新、削除) です。まずクエリ ステートメントを分析してみましょう。ステートメントは次のとおりです。

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

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

1. まず、ステートメントに権限があるかどうかを確認します。権限がない場合は、直接エラー メッセージが返されます。権限が付与されている場合、MySQL 8.0 より前では、まずキャッシュが照会され、この SQL ステートメントをキーとして使用して、メモリ内に結果があるかどうかを照会します。結果がある場合は、キャッシュが直接キャッシュされます。結果がない場合は、次の手順に進みます。
2. アナライザーを通じて、字句解析が実行され、SQL ステートメントのキー要素が抽出されます。たとえば、上記のステートメントはクエリ選択であり、クエリ対象のテーブル名は tb_student であり、すべての列をクエリする必要があり、クエリ条件はこのテーブルの ID = '1' です。次に、キーワードが正しいかどうかなど、SQL ステートメントに構文エラーがないか確認します。チェックが OK の場合は、次の手順に進みます。
3. 次のステップは、オプティマイザが実行プランを決定することです。上記の SQL ステートメントには、次の 2 つの実行プランがあります。

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

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

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

2.2 更新ステートメント

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

tb_student A を更新し、A.age='19' とし、A.name='张三' とします。

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

1. まず、Zhang San のデータをクエリします。キャッシュがある場合は、それも使用されます。
2. 次に、クエリ ステートメントを取得し、年齢を 19 に変更し、エンジン API インターフェイスを呼び出してこのデータ行を書き込みます。InnoDB エンジンはデータをメモリに保存し、同時に redo ログを記録します。この時点で、redo ログは準備状態になり、実行が完了し、いつでも送信できることをエグゼキュータに伝えます。
3. 通知を受信した後、エグゼキュータはバイナリログを記録し、エンジン インターフェイスを呼び出して、REDO ログをコミット済み状態に送信します。
4. 更新が完了しました。

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

1. まず、REDO ログを書き込んで直接コミットし、次に binlog を書き込みます。REDO ログを書き込んだ後にマシンがクラッシュし、binlog が書き込まれなかったとします。マシンが再起動すると、マシンは REDO ログを通じてデータを復元しますが、この時点では binlog にデータは記録されません。後でマシンをバックアップすると、このデータは失われ、マスター スレーブ同期でもこのデータは失われます。
2. 最初に binlog を書き込み、次に redo log を書き込みます。binlog を書き込んだ後、マシンが異常に再起動したとします。redo log がないため、マシンはこのレコードを回復できません。ただし、binlog には別のレコードがあります。すると、上記と同じ理由でデータの不整合が発生します。

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

1. REDO ログが完了しているかどうかを判断します。完了している場合は、すぐにコミットします。
2. 再実行ログがコミット前でコミットされていない状態の場合、システムはバイナリログが完了しているかどうかを判断します。完了している場合は、再実行ログがコミットされ、完了していない場合は、トランザクションがロールバックされます。

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

結論

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

IV. 参考文献

「MySQL ナレッジ ネットワークを一緒に構築する」

上記は、MySQL で SQL ステートメントが実行される方法についての詳細な説明です。お役に立てば幸いです。ご質問がある場合は、メッセージを残していただければ、すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。