SQL クエリステートメントはどのように実行されるか-mysql チュートリアル-php.cn

皆さん、こんにちは！これが私の最初の記事です。

この記事では、SQL クエリステートメントがどのように実行されるかを紹介します

以下は MySQL アーキテクチャ図です:

How is an SQL Query Statement executed

一般に、MySQl はサーバーとストレージエンジン層の 2 つの部分に分割できます。

サーバー層には、コネクタ、クエリキャッシュ、パーサー、オプティマイザ、エグゼキュータなどが含まれ、MySQL のコアサービス関数のほとんどと、すべての組み込み関数 (日付、時刻、計算、暗号化など) が含まれています。機能）。ストアドプロシージャ、トリガー、ビューなどのすべてのクロスストレージエンジン機能は、このレイヤーで実装されます。

ストレージエンジン層は、データの保存と取得を担当します。そのアーキテクチャはプラグインベースであり、InnoDB、MyISAM、Memory などの複数のストレージエンジンをサポートしています。 MySQL 5.5.5 から、InnoDB が MySQL のデフォルトのストレージエンジンになりました。

エンジン=メモリを指定した create table ステートメントを使用して、テーブルを作成するときにメモリエンジンを指定できます。

異なるストレージエンジンが同じサーバー層を共有します

コネクタ

最初のステップはデータベースに接続することです。これにはコネクタが必要です。コネクタは、クライアントとの接続を確立し、許可を取得し、接続を維持および管理する責任があります。接続コマンドは次のとおりです:

mysql -h$ip -P$port -u$user -p

ログイン後にコピー

このコマンドは、サーバーとの接続を確立するために使用されます。クラシック TCP ハンドシェイクが完了すると、コネクタはプロバイダーのユーザー名とパスワードを使用して ID を認証します。

ユーザー名またはパスワードが間違っている場合、「ユーザーのためアクセスが拒否されました」というエラーが表示され、クライアントプログラムが終了します。
認証が成功すると、コネクタは現在のアカウントの権限を権限テーブルから取得します。この接続中のすべての権限チェックは、この初期取得に依存します。

これは、接続が正常に確立されると、管理者がユーザーの権限に加えた変更は既存の接続の権限に影響を与えないことを意味します。新しい接続のみが、更新された権限設定を使用します。

接続が確立された後、後続のアクションがない場合、接続はアイドル状態に入ります。これは、show processlist コマンドを使用して確認できます。

How is an SQL Query Statement executed

クライアントが非アクティブな状態が長時間続くと、コネクタは自動的に切断されます。期間は wait_timeout パラメータによって制御され、デフォルトは 8 時間です。

接続が終了し、クライアントがリクエストを送信すると、次のエラーメッセージが表示されます: クエリ中に MySQL サーバーへの接続が失われました。続行するには、再接続してリクエストを実行する必要があります。

データベースでは、永続接続は、クライアントが接続に成功した後、継続的なリクエストに対して同じ接続を維持する接続を指します。短い接続とは、数回のクエリ後に切断し、後続のクエリのために再接続することを指します。

接続プロセスは複雑であるため、開発中の接続の作成を最小限に抑えること、つまり可能な限り永続的な接続を使用することをお勧めします。

ただし、永続接続を使用すると、実行中に使用される一時メモリが接続オブジェクト内で管理されるため、MySQL のメモリ使用量が大幅に増加する可能性があります。これらのリソースは、接続が終了した場合にのみ解放されます。永続的な接続が蓄積すると、過剰なメモリ使用量が発生し、システムが MySQL を強制終了 (OOM) し、予期しない再起動が発生する可能性があります。

解決策:

永続的な接続を定期的に切断します。しばらく接続を使用した後、または過剰なメモリを消費するクエリを実行した後は、切断し、後続のクエリのために再接続してください。
MySQL 5.7 以降を使用している場合は、リソースを大量に消費する操作を実行した後に mysql_reset_connection を使用して接続リソースを再初期化できます。このプロセスでは再接続や再認証は必要ありませんが、接続は作成されたばかりの状態にリセットされます。

クエリキャッシュ

注: MySQL 8.0 以降、その欠点が利点を上回るため、クエリキャッシュ機能は完全に削除されました。

MySQL はクエリリクエストを受信すると、まずクエリキャッシュをチェックして、このクエリが以前に実行されたかどうかを確認します。以前に実行されたクエリとその結果は、キーと値のペアとしてメモリにキャッシュされます。キーはクエリステートメントであり、値は結果です。キーがクエリキャッシュで見つかった場合、値はクライアントに直接返されます。

クエリがクエリキャッシュに見つからない場合、プロセスは続行されます。

クエリキャッシュはなぜ良いことよりも害を及ぼすのでしょうか?

クエリキャッシュの無効化が非常に頻繁に発生します。テーブルを更新すると、そのテーブルに関連するすべてのクエリキャッシュがクリアされるため、テーブルが静的構成テーブルでない限り、キャッシュヒット率が非常に低くなります。

MySQL は、クエリキャッシュを使用するための「オンデマンド」方式を提供します。パラメータ query_cache_type を DEMAND に設定すると、SQL ステートメントはデフォルトでクエリキャッシュを使用しなくなります。クエリキャッシュを使用するには、SQL_CACHE:
を明示的に指定できます。

mysql -h$ip -P$port -u$user -p

ログイン後にコピー

パーサー

クエリキャッシュがヒットしない場合は、ステートメントの実行プロセスが開始されます。 MySQL はまず何をすべきかを理解する必要があるため、SQL ステートメントを解析します。

パーサーは最初に字句解析を実行します。文字列とスペースで構成される入力 SQL ステートメントは MySQL によって分析され、各部分が何を表しているかを特定します。たとえば、select はクエリステートメント、T はテーブル名、ID は列として識別されます。

字句解析の後、構文解析が実行されます。字句解析の結果に基づいて、構文アナライザーは SQL ステートメントが MySQL の構文規則に準拠しているかどうかを判断します。

構文エラーがある場合は、「SQL 構文にエラーがあります」のようなエラーメッセージが表示されます。たとえば、次のクエリでは、select キーワードのスペルが間違っています:

select SQL_CACHE * from T where ID=10;

ログイン後にコピー

オプティマイザ

解析後、MySQL はユーザーが何をしたいのかを認識します。次に、オプティマイザがその方法を決定します。

オプティマイザーは、テーブルに複数のインデックスがある場合に使用するインデックス、またはクエリに複数のテーブルが含まれる場合のテーブル結合の順序を決定します。たとえば、次のクエリでは:

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

ログイン後にコピー

クエリは、t1 または t2 から値を取得することで開始できます。どちらのアプローチでも論理的には同じ結果が得られますが、パフォーマンスは異なる場合があります。オプティマイザーの役割は、最も効率的なプランを選択することです。

最適化フェーズの後、プロセスはエグゼキュータに進みます。

執行者

エグゼキューターがクエリの実行を開始します。

実行前に、現在の接続にテーブルをクエリする権限があるかどうかが最初にチェックされます。そうでない場合は、権限が不十分であることを示すエラーが返されます。 (クエリキャッシュから結果を返すときにも権限チェックが実行されます。)

権限が付与されると、テーブルが開かれ、実行が続行されます。このプロセス中、エグゼキューターはテーブルのエンジン定義に基づいてストレージエンジンと対話します。

たとえば、テーブル T に ID 列のインデックスがないとします。エグゼキュータの実行プロセスは次のようになります:

InnoDB エンジンインターフェイスを呼び出してテーブルの最初の行をフェッチし、ID 値が 10 であるかどうかを確認します。そうでない場合はスキップします。「はい」の場合、それを結果セットに追加します。
エンジンインターフェイスを呼び出して「次の行」をフェッチし、すべての行がチェックされるまで同じロジックを繰り返します。
エグゼキュータは、蓄積された結果セットをクライアントに返します。

この時点で、クエリは完了です。

インデックス付きテーブルの場合、プロセスには、エンジンの事前定義メソッドを使用して、「最初に一致する行」と「次に一致する行」を繰り返しフェッチすることが含まれます。

スロークエリログの rows_examined フィールドは、クエリの実行中にスキャンされた行数を示します。この値は、エグゼキューターがエンジンを呼び出してデータ行を取得するたびに蓄積されます。

場合によっては、エグゼキューターへの 1 回の呼び出しで、エンジン内の内部で複数の行をスキャンする必要がある場合があります。したがって、エンジンによってスキャンされた行数は、必ずしも rows_examined と等しくなりません。