MySQL では SQL ステートメントはどのように実行されますか?

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

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

mysqlは主にServer層とストレージエンジン層に分かれています

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

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

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

1.1 コネクタ

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

1.2 クエリキャッシュ

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

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

1.3 アナライザー

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

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

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

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

1.4 オプティマイザー

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

1.5 アクチュエータ

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

2. ステートメント分析

2.1 クエリステートメント

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

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

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

• まず、ステートメントに権限があるかどうかを確認します。権限がない場合は、直接エラー メッセージが返されます。権限が付与されている場合、 mysql8.0 8.0 より前では、まずキャッシュが照会され、この SQL ステートメントをキーとして使用して、メモリ内に結果があるかどうかを照会します。結果がある場合は、キャッシュが直接キャッシュされます。結果がない場合は、次の手順に進みます。
• アナライザーを通じて、字句解析が実行され、SQL ステートメントのキー要素が抽出されます。たとえば、上記のステートメントはクエリ選択であり、クエリ対象のテーブル名はtb_studentです。すべての列をクエリする必要があり、クエリ条件はこのテーブルの ID = '1' です。次に、キーワードが正しいかどうかなど、SQL ステートメントに構文エラーがないか確認します。チェックが OK の場合は、次の手順に進みます。
• 次のステップは、オプティマイザが実行プランを決定することです。上記の SQL ステートメントは、次の2 つの方法で実行できます。(1) まず、名前が「Zhang San」である学生の学生テーブルを照会し、次に年齢が 18 歳かどうかを判断します。 （２）まず18歳の生徒を探し、次に「張三」という名前の生徒を探します。
• 次に、オプティマイザーは独自の最適化アルゴリズムに基づいて、実行効率が最も高いソリューションを選択します (オプティマイザーは、これが常に最適なソリューションであるとは限らないと考えています)。実行プランを確認したら、実行を開始する準備が整います。
• 権限チェックを実行します。権限がない場合はエラーメッセージが返されます。権限がある場合は、データベースエンジンインターフェイスが呼び出され、エンジンの実行結果が返されます。
  

2.2 更新ステートメント

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

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

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

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

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

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

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

• redo logが完了しているかどうかを判断します。完了している場合は、すぐにコミットします。
• redo log事前コミットされた状態のみでcommitいない場合、システムはbinlogが完了しているかどうかを判断します。完了している場合は、 redo logがコミットされ、不完全な場合は、トランザクションがロールバックされます。
  

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

結論

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

MySQL で SQL 文を実行する方法についての記事はこれで終わりです。MySQL で SQL 文を実行する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。