MySQLでSQLクエリ文がどのように実行されるかを分析する

MySQLでSQLクエリ文がどのように実行されるかを分析する

1. MySQL論理アーキテクチャの概要

MySQL の最も重要かつ特徴的な機能は、クエリ処理やその他のシステム タスクをデータの保存/取得から分離するように設計された、プラグ可能なストレージ エンジン アーキテクチャです。公式サイトの説明を見てみましょう。

MySQL のプラガブル ストレージ エンジン アーキテクチャにより、データベース プロフェッショナルは、特定のアプリケーション コーディング要件を管理する必要がなくなり、特定のアプリケーション ニーズに合わせて特殊なストレージ エンジンを選択できます。

基本的に、MySQL のプラガブル ストレージ エンジン アーキテクチャにより、開発者は特定のアプリケーション コーディング要件を管理することなく、特定のアプリケーション ニーズに合わせて特殊なストレージ エンジンを選択できます。つまり、ストレージ エンジンによって機能が異なりますが、アプリケーションはこれらの違いの影響を受けません。

アプリケーションの変更により基盤となるストレージ エンジンを変更する必要が生じた場合、または新しい要件をサポートするために 1 つ以上のストレージ エンジンを追加する必要がある場合でも、動作させるために大規模なコーディングやプロセスの変更は必要ありません。 MySQL サーバー アーキテクチャは、ストレージ エンジン間で一貫性があり使いやすい API を提供することで、アプリケーションをストレージ エンジンの基盤となる複雑さから保護します。

MySQL の論理アーキテクチャ図は次のとおりです。「High Performance MySQL - 3rd Edition」を参照してください。

MySQL の論理アーキテクチャは、大まかにサーバー層とストレージ エンジン層に分けることができます。

1) MySQL のコア サービス機能のほとんどはサーバー層にあり、接続、クエリ解析、分析、最適化、キャッシュ、すべての組み込み関数 (日付、時刻、数学、暗号化関数など) が含まれます。ストアド プロシージャ、トリガー、ビューなど、すべてのクロス ストレージ エンジン機能はこの層に実装されています。

サーバーの最上位サービスはコネクタであり、MySQL 接続の管理と権限検証の機能を備えていることは特筆に値します。明らかに、これは MySQL に固有のものではなく、ほとんどのネットワークベースのクライアント/サーバー ツールやサービスは同様のアーキテクチャを備えています。

2) 2 番目のレイヤーはストレージ エンジンです (InnoDB、MyISAM、Memory などの複数のストレージ エンジンをサポートします)。ストレージ エンジンは、MySQL でのデータの保存と取得、および上位サーバーからの要求への応答を担当します。それぞれのストレージ エンジンには当然、長所と短所があります。異なるストレージ エンジンは相互に通信できないため、さまざまなシナリオに応じて適切なストレージ エンジンを選択する必要があります。

サーバーは API を介してストレージ エンジンと通信します。これらのインターフェースは、異なるストレージ エンジン間の違いを隠蔽し、これらの違いを上位レベルのクエリ プロセスに対して透過的にします。ストレージ エンジン API は、「トランザクションを開始する」や「主キーに基づいてレコードの行を取得する」などの操作を実行するための多数の低レベル関数で構成されています。

MySQL 5.1 以前のバージョンでは、MyISAM がデフォルトのストレージ エンジンであり、MySQL 5.5.5 以降では InnoDB がデフォルトのストレージ エンジンになることに注意してください。

2. コネクタ

MySQL 5.7 の公式ドキュメントでは、コネクタについて次のように説明されています。

MySQL コネクタは、クライアント プログラムに MySQL サーバーへの接続を提供します。

MySQL コネクタは、クライアント プログラムに MySQL サーバーへの接続を提供します。 具体的には、コネクタは実際には 2 つのことを行います。1 つは MySQL 接続の管理、もう 1 つは権限の検証です。それぞれ順番に説明していきましょう。

まず、MySQL サーバーに接続するには、通常、MySQL のユーザー名とパスワードを入力する必要があります。また、ログインしているマシンとは別のマシンでサーバーが実行されている場合は、host などのホスト名も指定する必要があります。 したがって、接続コマンドは通常次のようになります。

シェル> mysql -h ホスト -u ユーザー -p

パスワードを入力してください: ********

もちろん、MySQL を実行している同じマシンにログインしている場合は、ホスト名を省略して次のように使用できます。

シェル>mysql -u ユーザー -p

皆さんは上記のコマンドをよくご存知でしょう。

上記のコマンドで接続を確立するための従来の TCP 3 ウェイ ハンドシェイクを完了すると、コネクタは入力したユーザー名とパスワードに基づいて ID を認証します。

1) ユーザー名またはパスワードが正しくない場合は、「ユーザーのアクセスが拒否されました」というエラーが表示され、クライアント プログラムの実行が終了します。

2) ユーザー名とパスワードの認証が成功すると、次の文字列が表示されます。

mysql>は、MySQL の準備ができており、SQL ステートメントの入力を開始できることを示しています。

もちろん、コネクタはユーザー名とパスワードを比較するだけでなく、ユーザーが特定のクエリを実行する権限を持っているかどうかも確認します (たとえば、ユーザーが world データベースの Country テーブルで SELECT ステートメントを実行できるかどうかなど)。その後、この接続におけるすべての権限判断ロジックは、この時点で読み取られた権限に依存します。

つまり、ユーザーが正常に接続を確立すると、別の端末の管理者アカウントを使用してこのユーザーの権限を変更しても、既存の接続の権限には影響しません。

つまり、ユーザー権限が変更された後は、新しく作成された接続のみが新しい権限設定を使用することになります。

接続が確立された後、その後のアクションを何も実行しないと、接続はアイドル状態 (スリープ) になります。

実際、MySQL 接続 (またはスレッド) には、MySQL が現在何をしているかを示す状態が常に存在します。現在のステータスを表示するには、さまざまな方法があります。最も簡単な方法は、 SHOW FULL PROCESSLISTコマンドを使用することです (コマンドによって返される結果のコマンド列に現在のステータスが示されます)。

クエリのライフサイクル中に、状態は何度も変化します。ここでは詳細には記載しません。上図のSleep状態は、現在の接続がクライアントからの新しいリクエストの送信を待機していることを意味し、 Query状態は、現在の接続がクエリを実行しているか、結果をクライアントに送信していることを意味します。

MySQL のデフォルト設定では、接続が 8 時間スリープ状態にある場合 (つまり、8 時間以上使用されていない場合)、サーバーは接続を切断し、接続に対するその後のすべての操作は失敗します。この時間はパラメータwait_timeoutによって制御されます。

クエリキャッシュ

接続が確立されたら、クエリの選択ステートメントを入力できます。実行ロジックは、2 番目のステップであるクエリ キャッシュに進みます。

公式ドキュメントでは、クエリ キャッシュについて次のように説明されています。

クエリ キャッシュには、SELECT ステートメントのテキストと、クライアントに送信された対応する結果が保存されます。後で同一のステートメントを受信した場合、サーバーはステートメントを再度解析して実行するのではなく、クエリ キャッシュから結果を取得します。クエリ キャッシュはセッション間で共有されるため、あるクライアントによって生成された結果セットは、別のクライアントによって発行された同じクエリへの応答として送信できます。

つまり、クエリ キャッシュには、SELECT ステートメントのテキストと、クライアントに応答された対応する結果が格納されます。こうすることで、サーバーが後で同じ SELECT ステートメントを受け取った場合、サーバーはステートメントを再度解析して実行するのではなく、まずクエリ キャッシュから結果を取得します。クエリ キャッシュはセッション間で共有されるため、あるクライアントによって生成された結果セットは、別のクライアントによって発行された同じクエリへの応答として送信できます。

現在のクエリがクエリ キャッシュにヒットした場合、MySQL はクエリ結果を返す前にユーザー権限を 1 回チェックします。現在のクエリがアクセスする必要があるテーブル情報はすでにクエリ キャッシュに保存されているため、クエリ SQL ステートメントを解析する必要はありません。

キャッシュが関係するため、キャッシュの一貫性の問題を回避することはできません。ありがたいことに、キャッシュをクエリしても、追加の作業なしで古いデータが返されることはありません。

クエリ キャッシュは古いデータを返しません。テーブルが変更されると、クエリ キャッシュ内の関連するエントリはすべてフラッシュされます。

テーブルが変更されると、クエリ キャッシュ内の関連エントリがすべてフラッシュされます。ここでのフラッシュは、更新ではなくクリアを意味することに注意してください。

かなり良さそうでしょ?無効化キャッシュは、手動操作なしで自動的にクリアできます。

残念ながら、この機能のため、MySQL 5.7.20 以降では公式ではクエリ キャッシュの使用が推奨されなくなり、MySQL 8.0 ではクエリ キャッシュが直接削除されました。

クエリ キャッシュは MySQL 5.7.20 以降では非推奨となり、MySQL 8.0 では削除されます。

実際、理解するのは難しくありません。たとえば、大量のトラフィックがあるフォーラム プロジェクトの場合、投稿テーブルをクエリする需要が常に存在し、投稿はほぼ毎分増加しています。このテーブルが更新される限り、このテーブル上のすべてのクエリ キャッシュがクリアされます。MySQL データベースに大きな負荷がかかることは想像に難くありません。クエリ結果を保存するために多大な労力を費やしましたが、使用する前に更新によって消去されてしまいました。

MySQL 8.0 より前のバージョンでは、パラメータquery_cache_type DEMANDに設定すると、すべての SQL ステートメントでクエリ キャッシュが使用されなくなります。クエリ キャッシュを確実に使用するステートメントの場合は、次のステートメントのように、 SQL_CACHEを使用して明示的に指定できます。

mysql> id = 1 の場合、t1 から SQL_CACHE * を選択します。

4. パーサー

ヒットがない場合、またはクエリ キャッシュが有効になっていない場合、MySQL サーバーは次に SQL ステートメントを実行プランに変換し、この実行プランに従ってストレージ エンジンと対話します。これには、SQL の解析、前処理、SQL 実行プランの最適化という複数のサブフェーズが含まれます。このプロセス中にエラー (構文エラーなど) が発生すると、クエリが終了する可能性があります。

SQL の解析と前処理はパーサーが行い、SQL 実行プランの最適化はオプティマイザーが行います。ここではまずパーサーについて説明します。

ここで、「High Performance MySQL - 3rd Edition」という本では、さらに細かく分けています。SQLを解析するために使用するのをパーサー、前処理に使用するのがプリプロセッサです。ここでは、すべてパーサーとして分類します。

SQL の解析とは、MySQL がキーワードを通じて SQL 文を解析し、対応する「解析ツリー」を生成して、文が文法規則に従って正しいかどうかを確認することを意味します。たとえば、間違ったキーワードが使用されていないか、キーワードが正しい順序で使用されているか、引用符が正しく一致しているかなどが検証されます。

前処理では、さらに、データ テーブルとデータ列が存在するかどうか、テーブル名とフィールド名が正しいかどうかなど、解析ツリーが正当かどうかがチェックされます。

5. オプティマイザー

これで解析ツリーが有効になり、MySQL は何をしようとしているのかを認識します。ただし、クエリには複数の実行プランがあり、それらはすべて同じ結果を返します。では、どの実行プランを選択すればよいのでしょうか。

以下に簡単な例を示します。

mysql> id = 10、name = "good" の場合、t1 から * を選択します。

上記のステートメントでは、最初に name = good を検索してから id = 10 を検索することも、最初に id = 10 を検索してから name = good を検索することもできます。これら 2 つの異なる実行プランの時間コストは異なる場合があります。

オプティマイザの役割は、それらの中から最適な実行プランを見つけることです。ここでの実行プランは、他の多くのリレーショナル データベースのように対応するバイトコードを生成するのではなく、データ構造であることに注意してください。

さらに、オプティマイザーはテーブルがどのストレージ エンジンを使用するかは気にしませんが、ストレージ エンジンはクエリの最適化に影響を与えます。オプティマイザーは、ストレージ エンジンに対して、特定の操作の容量またはコスト情報、およびテーブル データに関する統計情報を提供するように要求します。

オプティマイザー フェーズが完了すると、ステートメントの実行プランが確定し、エグゼキューター フェーズを開始できます。

6. アクチュエータ

クエリ キャッシュにアクセスする場合と同様に、SQL ステートメントの実行を開始する前に、エグゼキュータはまず現在のユーザーにこのテーブルでクエリを実行する権限があるかどうかを判断します。権限がない場合は、ユーザーに権限がないことを示すエラーが返されます。

権限認証が完了すると、MySQL は実行プランに指定された指示に従って段階的に実行します。実行計画に従って段階的に実行する過程では、ストレージ エンジンによって実装されたインターフェイスを呼び出して、多数の操作を完了する必要があります。これらのインターフェイスは、「ハンドラー API」インターフェイスと呼ばれます。

クエリ内の各テーブルは、ハンドラーのインスタンスによって表されます。実際、MySQL は最適化フェーズ中に各テーブルに対してハンドラー インスタンスを作成します。オプティマイザーは、これらのインスタンスのインターフェイスに基づいて、すべての列名、インデックス統計など、テーブルに関する関連情報を取得できます。

例えば:

mysql> t1 から * を選択します (id = 10)。

デフォルトの InnoDB エンジンを使用すると仮定すると、エグゼキュータの実行フローはおおよそ次のようになります (id がインデックスでない場合は、行ごとに検索する完全なテーブル スキャンが実行されることに注意してください。インデックスの場合は、インデックス構成テーブルでクエリが実行され、より効率的になります。ここでは、非インデックスを例に挙げます)。

1) InnoDB エンジン インターフェイスを呼び出して、このテーブルの最初のレコード行を取得し、id 値が 10 かどうかを判断します。10 の場合は、このレコード行をセットに保存します。10 でない場合は、このテーブルの最後の行が取得されるまで、次の行の判断に進みます。

2) エグゼキュータは、上記のトラバーサル処理の条件を満たすすべての行からなるレコードセットを結果としてクライアントに返します。

VII. 要約

次のクエリ ステートメントの実行プロセスを要約します。

1. MySQL クライアントとサーバーの間で接続が確立され、クライアントがサーバーにクエリを送信します。

2. サーバーはまずクエリ キャッシュをチェックします。キャッシュにヒットした場合は、キャッシュに保存されている結果がすぐに返されます。ヒットしなかった場合は、次のステージに進みます。

3. サーバーは SQL 解析と前処理を実行して、有効な解析ツリーを生成します。

4. オプティマイザは対応する実行プランを生成します。

5.MySQL は、オプティマイザによって生成された実行プランに従って対応するストレージ エンジン API を呼び出して実行し、実行結果をクライアントに返します。

上記は、MySQL で SQL クエリ ステートメントが実行される方法についての詳細な分析です。MySQL クエリ ステートメントの実行方法の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • PHP で mysqli を使用して複数の SQL クエリ ステートメントを同時に実行する例
  • SQLクエリの実行順序をゼロから学ぶ
  • MySQLクエリ文の実行プロセスを理解するための記事
  • Mysql系SQLクエリ文の書き順と実行順を詳しく解説
  • MySQL では SQL クエリはどのように実行されますか?
  • SQLクエリステートメントの実行プロセス

<<:  CSS における px、em、rem、%、vw、vh 単位の違いの詳細な説明

>>:  Vueスロットの詳細な説明

推薦する

Linuxコマンドとファイル検索の詳しい説明

1. ファイル名検索を実行するwhich ('実行可能ファイル' を検索) //PA...

49 個の JavaScript のヒントとコツ

目次1. js整数の演算2. ネイティブアラートを書き換えてポップアップボックスの数を記録する3. ...

MySQLの自動増分主キーの実装の詳細な説明

目次1. 自己増分値はどこに保存されますか? 2. 自己価値修正メカニズム3. 自動増分値を変更する...

JSでHTML本文のスタイルを変更する

目次1. 本来の定義2. JS操作、幅の変更を例に3. 効果: 幅が変更されました 1. 本来の定義...

基本的な HTML ディレクトリの問題 (相対パスと絶対パスの違い)

相対パス - ファイルを参照する Web ページの場所に基づいて確立されたディレクトリ パス。そのた...

ハイパーリンクの表示と開き方

<br />関連記事: ハイパーリンクを表示して開く方法症状<br />ユー...

HTML ページ共通スタイル (推奨)

以下のように表示されます。 XML/HTML コードコンテンツをクリップボードにコピーbody、di...

MySQL 8.0.22 winx64 のインストールと設定方法のグラフィックチュートリアル

MySQL-8.0.22-winx64のデータベースインストールチュートリアルは参考になります。具体...

よく使われるCSSスタイル(レイアウト)の詳しい説明

新しいCSS3プロパティと互換性ありCSS3では、プラグインprefixfree.min.jsを使用...

MySQL で B+ ツリー インデックスを使用する利点は何ですか?

この問題を理解する前に、まず MySQL テーブルのストレージ構造を確認し、次にバイナリ ツリー、マ...

Vueがビデオアップロード機能を実装

この記事では、参考までに、ビデオアップロード機能を実現するためのVueの具体的なコードを紹介します。...

CentOS6.9+Mysql5.7.18 ソースコードのインストール詳細チュートリアル

CentOS6.9+Mysql5.7.18 ソースコードのインストールでは、以下の操作を root ...

MySQL のフィールドにデフォルトの時間を追加する方法

日付型の違いと用途MySQL には、日付、時刻、年、日付時刻、タイムスタンプの 5 つの日付タイプが...

MySQL 関数インデックス最適化ソリューション

MySQL を使用する場合、多くの開発者は一部の列に対して関数計算を実行することが多く、その結果、イ...