MySQL インフラストラクチャチュートリアル: クエリステートメント実行プロセスの詳細な説明

序文

私は以前から、SQL 文がどのように実行され、どのような順序で実行されるのかを知りたいと思っていました。そこで、さまざまな方面からの情報を調べてまとめ、以下の記事にたどり着きました。

このノートでは主に、MySQL の基本的なアーキテクチャとクエリステートメントの実行方法について記録します。

詳しい紹介を見てみましょう。

例えば、学生テーブルからid=2をクエリする場合

id=2 の学生から * を選択します。

このステートメントの実行フローを説明する前に、MySQL の基本的なアーキテクチャを見てみましょう。

この画像は Geek Time の MySQL 実践から引用したものです。この画像は MySQL の論理アーキテクチャを説明しています。

サーバーレイヤーには、コネクタ、クエリキャッシュ、アナライザー、オプティマイザー、エグゼキューターが含まれており、MySQL のコアサービス機能のほとんどとすべての組み込み関数をカバーしています。ストアドプロシージャ、トリガー、ビューなど、すべてのクロスストレージエンジン機能は、このレイヤーに実装されています。
ストレージエンジン層は、データの保存と取得を担当します。アーキテクチャモードはプラグインベースであり、InnoDB、MyISAM、Memory などの複数のストレージエンジンをサポートします。通常は innoDB エンジンが使用されます。

コネクタ

データベースを使用する前に、データベースに接続する必要があります。接続ステートメントは

mysql -h $ip -u $ユーザー名 -p $パスワード

このプロセスは当社のコネクタが処理します。コネクタの主な機能は、クライアントとの接続を確立し、権限を取得し、接続を維持および管理することです。コネクタの使用中にユーザーの権限が変更された場合、すぐには有効になりません。これは、ユーザーの権限は接続時に読み取られ、権限は再接続によってのみ更新できるためです。

コネクタがクライアントと通信するためのプロトコルは TCP です。接続が確立された後、show processlist を使用して実行された接続の数を確認できます。

同時に、接続時間が 8 時間を超えると、スリープ状態で自動的に切断されます。これは MySQL のデフォルト設定です。切断されない場合は、このプロセスは長時間接続と呼ぶことができます。

これに対応するのが短い接続であり、これは 1 つまたは複数の操作を実行した後に切断することを意味します。

長時間の接続を連続して使用すると、大量のメモリリソースが占有されます。MySQL 5.7 以降では、mysql_reset_connection ステートメントを使用してリソースを再初期化できます。

クエリキャッシュ

接続すると、データベースに接続され、ステートメントを実行できるようになります。

ステートメントを実行すると、MySQL はまずキャッシュを照会して、そのステートメントが以前に実行されたかどうかを確認します。MySQL は、以前に実行されたステートメントと結果をキー値の形式で保存します (もちろん、一定の保存期間と有効期間があります)。キャッシュが存在する場合は、キャッシュされた結果が直接返されます。

キャッシュワークフローは

サーバーはSQLを受信し、SQLとその他の条件をキーとしてキャッシュテーブルを検索します。
キャッシュが見つかった場合、キャッシュは直接返されます
キャッシュが見つからない場合は、元の SQL 解析、最適化などを含む SQL クエリが実行されます。
SQLクエリ結果を実行した後、SQLクエリ結果をキャッシュテーブルにキャッシュします。

もちろん、このテーブルが変更されると、このテーブルを使用しているすべてのキャッシュは有効ではなくなり、クエリキャッシュエントリはクリアされます。したがって、繰り返し変更されるテーブル内のステートメントをキャッシュすることは不適切です。キャッシュはいつでも有効になるため、クエリキャッシュのヒット率が大幅に低下し、コスト効率があまり良くありません。

このテーブルにデータが書き込まれているとき、このテーブルのキャッシュ (ヒットキャッシュ、キャッシュ書き込みなど) は無効になります。Innodb では、トランザクションがこのテーブルを変更すると、トランザクションがコミットされる前にこのテーブルのキャッシュは無効になります。このトランザクションがコミットされる前は、このテーブルの関連クエリをキャッシュすることはできません。

一般的に、静的なテーブルやほとんど変更されないテーブルであればキャッシュすることができ、ヒット率は非常に高くなります。

キャッシュをいつ使用するかについて話しましょう。キャッシュをオンにするとシステムのパフォーマンスが向上するかどうかを測定するのは難しいトピックです。

キャッシュヒット率で判断すると、キャッシュヒット率 = キャッシュヒット数 (Qcache_hits) / クエリ数 (Com_select)
キャッシュ書き込み率では、書き込み率 = キャッシュ書き込み回数 (Qcache_inserts) / クエリ回数 (Qcache_inserts)
ヒット書き込み比率で判断すると、比率 = ヒット数 (Qcache_hits) / 書き込み数 (Qcache_inserts) となり、高性能 MySQL では、パフォーマンスの向上をよりよく反映できるインデックスと呼ばれます。一般的に、クエリキャッシュには 3:1 の比率が有効であると考えられており、10:1 が最適です。

アナライザ

クエリキャッシュが無効であるか、キャッシュが存在しない場合、MySQL サーバーはアナライザを使用してステートメントを分析します。アナライザはパーサーとも呼ばれます。

MySQL アナライザは 2 つの部分から構成されます。最初の部分は字句解析に使用され、文字ストリームをスキャンし、単語形成規則に従って単語を識別します。MySQL は Flex を使用して字句スキャナを生成します。MySQL キーワードと関数キーワードは sql/lex.h で定義され、2 つの配列に格納されます。2 番目の部分の機能は構文解析です。字句解析に基づいて、単語シーケンスが文法フレーズに結合され、最終的に構文ツリーが生成されてオプティマイザに送信されます。構文アナライザは Bison を使用し、sql/sql_yacc.yy で構文規則を定義します。次に、リレーショナル代数理論に従って構文ツリーを生成します。

上記のアナライザーの説明は公式的すぎて複雑です。実際には、アナライザーは主に「字句解析」を実行し、データベースステートメントが何を実行しているのか、それが何を意味するのかを理解するために使われます。

この時点で、アナライザーがこのステートメントに問題があることを検出した場合、エラー 1064 (42000): SQL構文にエラーがありますなどのエラーが報告されます。

オプティマイザ

アナライザーが分析を終了し、ステートメントの動作を把握したら、次のステップは専用のオプティマイザーを使用してステートメントを最適化することです。オプティマイザーのタスクは、SQL クエリを実行するための最適なソリューションを見つけることです。 MySQL クエリオプティマイザーを含むほとんどのクエリオプティマイザーは、すべての可能なクエリ評価シナリオの中で、多かれ少なかれ最適なレベルまで検索します。

オプティマイザーは主に、オーバーヘッドを削減し、実行効率を向上させるように設計された最適な実行プランを選択します。

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

テーブルの関連付けの順序を再定義する（複数のテーブルを関連付けてクエリする場合、必ずしもSQLで指定された順序になるわけではありませんが、関連付けの順序を指定するためのテクニックがいくつかあります）
MIN() および MAX() 関数を最適化します (列の最小値を見つけるには、列にインデックスがある場合は B+Tree インデックスの左端を見つけるだけで済みます。それ以外の場合は最大値を見つけることができます。具体的な原則については以下を参照してください)
クエリを早期に終了する（たとえば、Limit を使用する場合、数量を満たす結果セットが見つかった直後にクエリが終了します）
ソートを最適化します (MySQL の旧バージョンでは、2 転送ソートが使用されています。つまり、最初にメモリ内で行ポインタとソートするフィールドを読み取ってソートし、次にソート結果に従ってデータ行を読み取ります。新しいバージョンでは、1 転送ソートが使用されています。つまり、一度にすべてのデータ行を読み取り、指定された列に従ってソートします。I/O 集約型アプリケーションの場合、効率が大幅に向上します)

MySQL が進化するにつれて、オプティマイザが使用する最適化戦略も常に進化しています。ここでは、非常に一般的でわかりやすい最適化戦略をいくつか紹介します。

アクチュエータ

アナライザーがステートメントが何を行うべきかを認識し、オプティマイザーがその実行方法を認識した後、次のステップは実行であり、エグゼキュータに引き渡されます。

実行時に、エグゼキュータはまず、ユーザーがテーブルの実行権限を持っているかどうかを判断します。権限がない場合は、拒否などのエラーメッセージが返されます。

権限がある場合は、テーブルが開かれ、実行が続行されます。テーブルを開くとき、エグゼキュータはテーブルに定義されているエンジンに基づいてエンジンのインターフェースを使用します。

最後に、ステートメントが実行され、データが取得されてクライアントに返されます。

要約する

MySQL が SQL ステートメントを取得した後の一般的なプロセスは次のようになります。

0. コネクタはクライアントとの通信を担当します

1. キャッシュをクエリする: まずキャッシュをクエリして、kvキャッシュがあるかどうかを確認します。

2. パーサー: SQLの解析と転送を担当

3. プリプロセッサ: 解析されたSQLツリーを検証する