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