mysqlの仕組み

MySQL の論理アーキテクチャ

MySQL の論理アーキテクチャは 3 つの層に分かれており、最上層は MySQL 特有のクライアント層であり、接続処理、認可認証、セキュリティなどの機能をすべて担っています。この層では。

MySQL のコア サービスのほとんどは、クエリ解析、分析、最適化、キャッシュ、組み込み関数 (時間、数学、暗号化など) を含む中間層にあり、すべてのクロスストレージ エンジン関数はレイヤーの実装: ストアド プロシージャ、トリガー、ビューなど。

最下層はストレージ エンジンで、MySQL でのデータの保存と取得を担当します。中間のサービス層は、API を介してストレージ エンジンと通信します。これらの API インターフェイスは、異なるストレージ エンジン間の違いを保護します。

#MySQL クエリ プロセス

MySQL にリクエストを送信する場合:

#1. クライアント/サーバー通信プロトコル

MySQL クライアント/サーバー通信プロトコルは「半二重」です: いつでも、サーバーがクライアントにデータを送信するか、または、クライアントがサーバーにデータを送信します。これら 2 つのアクションを同時に実行することはできません。一方の端がメッセージの送信を開始すると、もう一方の端はそれに応答する前にメッセージ全体を受信する必要があるため、メッセージを細かく分割して個別に送信することはできませんし、その必要もありません。また、メッセージを制御する方法はありません。流れ。

クライアントはクエリ要求を別のデータ パケットでサーバーに送信するため、クエリ ステートメントが非常に長い場合は、max_allowed_pa​​cket パラメータを設定する必要があります。ただし、クエリが大きすぎる場合、サーバーはそれ以上のデータの受け入れを拒否し、例外をスローすることに注意してください。

逆に、サーバーがユーザーに応答するデータは、通常、複数のデータ パケットで構成される大量のデータです。ただし、サーバーがクライアントのリクエストに応答するとき、クライアントは最初のいくつかの結果を単に取得してサーバーに送信の停止を要求するのではなく、返された結果全体を完全に受け入れる必要があります。したがって、実際の開発では、クエリをできるだけシンプルにして必要なデータのみを返すこと、および通信時のデータ パケットのサイズと数を減らすことは非常に良い習慣であり、これが SELECT の使用を避ける理由でもあります。 * クエリに LIMIT 制限を追加します。

2. クエリ キャッシュ

クエリ キャッシュがオンになっている場合、クエリ ステートメントを解析する前に、MySQL はクエリ ステートメントがクエリ キャッシュ内のデータにヒットするかどうかをチェックします。 . .現在のクエリがたまたまクエリ キャッシュにヒットした場合、ユーザーの権限を一度確認した後、キャッシュ内の結果が直接返されます。この場合、クエリは解析されず、実行プランも生成されず、実行されません。

MySQL は、ハッシュ値によってインデックス付けされた参照テーブル (HashMap に似たデータ構造) にキャッシュを保存します。このハッシュ値は、クエリ自体、現在クエリされているデータベース、クライアント プロトコルのバージョン番号によって決まります。 、など。結果に影響を与える可能性のある一部の情報が計算されます。したがって、2 つのクエリ間の文字 (スペース、コメント) に違いがあると、キャッシュが失われる原因になります。

クエリにユーザー定義関数、ストアド関数、ユーザー変数、一時テーブル、または mysql ライブラリ内のシステム テーブルが含まれている場合、クエリ結果はキャッシュされません。たとえば、関数 NOW() または CURRENT_DATE() は、クエリ時間が異なるため、異なるクエリ結果を返します。別の例として、CURRENT_USER または CONNECION_ID() を含むクエリ ステートメントは、ユーザーが異なるため、異なる結果が返されます。そのようなクエリ結果をキャッシュします。意味がありません。

3. キャッシュの無効化

MySQL のクエリ キャッシュ システムは、クエリに含まれる各テーブルを追跡します。これらのテーブル (データまたは構造) が変更されると、すべてがキャッシュされます。このテーブルに関連するデータは無効になります。このため、MySQL は書き込み操作中に対応するテーブルのすべてのキャッシュを無効にする必要があります。クエリ キャッシュが非常に大きいか断片化している場合、この操作によりシステムが大量に消費され、システムがしばらくフリーズする可能性があります。さらに、システム上のクエリ キャッシュの追加消費は、書き込み操作だけでなく読み取り操作でも発生します:

1. この SQL ステートメントがヒットしない場合でも、クエリ ステートメントは開始前にチェックする必要があります。キャッシュ

2. クエリ結果をキャッシュできる場合、実行完了後、結果はキャッシュに保存され、追加のシステム消費量も発生します

これに基づいて、この場合、クエリのキャッシュによってシステムのパフォーマンスが向上し、キャッシュと無効化によって追加の消費がもたらされます。キャッシュによってもたらされるリソースの節約が、それ自体によって消費されるリソースよりも大きい場合にのみ、パフォーマンスの向上がもたらされます。システム。ただし、キャッシュをオンにすることでパフォーマンスが向上するかどうかを評価するのは非常に困難です。システムにパフォーマンス上の問題がある場合は、クエリ キャッシュを有効にしてデータベース設計を最適化してください: 例:

1. 1 つの大きなテーブルではなく複数の小さなテーブルを使用すると、過度に設計しないように注意してください

2. 循環単一挿入ではなくバッチ挿入

3. キャッシュ領域のサイズを合理的に制御します。一般に、サイズを次のように設定することがより適切です。数十メガバイト

4. SQL_CACHEとSQL_NO_CACHEを使用して、特定のクエリ文をキャッシュする必要があるかどうかを制御できます

特に書き込み集中型のアプリケーションの場合は、クエリ キャッシュを簡単にオンにしないでください。どうしてもそれができない場合は、query_cache_type を DEMAND に設定できます。この時点では、SQL_CACH を追加するクエリのみがキャッシュされ、他のクエリはキャッシュされません。この方法により、どのクエリをキャッシュする必要があるかを自由に制御できます。

4. 構文解析と前処理

MySQL はキーワードを通じて SQL ステートメントを解析し、対応する解析ツリーを生成します。このプロセス パーサーは主に文法ルールを検証して解析します。たとえば、SQL で間違ったキーワードが使用されていないか、キーワードの順序は正しいかなどです。前処理では、解析ツリーが MySQL ルールに従って正当であるかどうかがさらにチェックされます。たとえば、クエリ対象のデータテーブルやデータ列が存在するかどうかなどを確認します。

5. クエリの最適化

構文ツリーが正当であると判断され、オプティマイザーがそれをクエリ プランに変換すると、ほとんどの場合、クエリには多くの All が含まれる可能性があります。実行メソッドは最終的に対応する結果を返しますが、オプティマイザーの役割は、その中から最適な実行プランを見つけることです。

MySQL のクエリ オプティマイザーは非常に複雑なコンポーネントです。多くの最適化戦略を使用して、最適な実行プランを生成します:

1. テーブルの関連付け順序を再定義します (複数のテーブルが関連付けられている場合)クエリの場合、必ずしも SQL で指定された順序に従う必要はありませんが、関連付けの順序を指定するいくつかのテクニックがあります)

2. MIN() 関数と MAX() 関数を最適化します (クエリの最小値を見つけます)。列) 値、列にインデックスがある場合は、B ツリー インデックスの左端を見つけるだけで済みます。それ以外の場合は、最大値を見つけることができます)

3. クエリを早期に終了します (Limit を使用する場合、クエリの終了の数を満たす結果セットが見つかった直後になります)

4. ソートの最適化 (古いバージョンでは、MySQL は 2 つの転送ソートを使用します。つまり、最初に行ポインタを読み取り、ソートが必要なフィールドをメモリ内でソートし、ソート結果に従ってデータを読み取ります。データ行をフェッチすると、新しいバージョンでは単一転送ソートが使用されます。つまり、すべてのデータ行を一度に読み取ってからソートします。

6. クエリ実行エンジン

解析と最適化の段階が完了すると、MySQL は対応する実行プランとクエリ実行を生成します。エンジンは実行計画に従って命令を徐々に実行し、結果を取得します。実行プロセス全体のほとんどの操作は、ストレージ エンジンによって実装されたインターフェイス (ハンドラー API と呼ばれます) を呼び出すことによって完了します。クエリ プロセスの各テーブルは、ハンドラー インスタンスによって表されます。実際、MySQL は、クエリの最適化フェーズ中に各テーブルのハンドラー インスタンスを作成します。オプティマイザーは、これらのインスタンスのインターフェイスに基づいて、すべてのカラム名を含むテーブル関連の情報を取得できます。 、テーブルのインデックス統計など。ストレージ エンジン インターフェイスは非常に豊富な機能を提供しますが、最下部には数十のインターフェイスしかなく、これらのインターフェイスはタワー ブロックのように、クエリのほとんどの操作を完了します。

7. 結果をクライアントに返す

クエリ実行の最後の段階では、結果をクライアントに返します。データをクエリできない場合でも、MySQL はクエリの影響を受ける行数や実行時間など、クエリに関連する情報を返します。

クエリ キャッシュがオンになっており、クエリをキャッシュできる場合、MySQL は結果もキャッシュに保存します。

結果セットをクライアントに返すことは、増分的かつ段階的な返却プロセスです。 MySQL は、最初の結果を生成したときに、徐々に結果セットをクライアントに返し始める可能性があります。このようにして、サーバーはあまりにも多くの結果を保存したり、大量のメモリを消費したりする必要がなく、クライアントも返された結果をできるだけ早く取得できます。なお、結果セットの各行は、①の通信プロトコルに従ったデータパケットとして送信され、TCPプロトコルで送信されますが、送信処理中にMySQLのデータパケットがキャッシュされて送信される場合があります。バッチ。

MySQL 全体のクエリ実行プロセス

1. クライアントはクエリ リクエストを MySQL サーバーに送信します

2. サーバーは最初にクエリ キャッシュをチェックします. If キャッシュがヒットすると、キャッシュに格納されている結果が即座に返されます。それ以外の場合は、次のレベルのセクションを入力します。

3. サーバーは SQL の解析と前処理を実行し、オプティマイザーは対応する実行プランを生成します。

#4.MySQL は、次のレベルに基づいてストレージ エンジンの API を呼び出します。実行プラン。クエリを実行

以上がmysqlの仕組みの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。