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

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ツリーを検証する

4. オプティマイザー: 実行プランを取得する

5. クエリ実行エンジン: エグゼキュータはステートメントを実行してデータ結果セットを取得します。

6. データを呼び出し元に返します。

さて、以上がこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM をご愛顧いただきありがとうございます。

以下もご興味があるかもしれません:
  • 初心者が必ず読むべき 15 個の基本的な SQL クエリ ステートメント
  • SqlServer の基本データ取得、クエリの並べ替えステートメント
  • SQLベースのクエリステートメント

<<:  Vueソースコード解析における仮想DOMの詳しい説明

>>:  WIN2008 サーバーのコマンド ラインを使用して IIS7 コンポーネントをインストールおよびアンインストールする方法

推薦する

Alibaba Cloud Ubuntu 16.04でpptpdサービスを構築する方法

1. PPTP VPNを構築するには、ポート1723とGREプロトコルを開く必要があります。 1. ...

システム外のフォント参照とトランジション効果

コードをコピーコードは次のとおりです。 <span style="font-fami...

EasyUEFI を使用して Windows 10 で USB ドライブなしで Ubuntu 18 をインストールする

1. BIOSを確認するまず、コンピュータの起動モードを確認します。win+R と入力し、msinf...

Vueはミックスインを使用してコンポーネントを最適化します

目次ミックスインの実装フック関数のマージプロジェクト実践伸ばす要約するVue は mixins AP...

MySQLに必要な共通知識のまとめ

目次主キー制約一意の主キー非 Null 制約デフォルトの制約外部キー制約1NF 2NF 3NFデータ...

MySQL5.7.03 上位バージョンから MySQL 5.7.17 への置き換えインストール プロセスと見つかった問題の解決策

1. インストール方法は? 1. [実行] -> [cmd] と入力して、小さな黒いウィンドウ...

MySQL ロックブロッキングの詳細な分析

日常のメンテナンスでは、スレッドがブロックされることが多く、データベースの応答が非常に遅くなります。...

Dockerはコンテナを通じてイメージを生成し、詳細にDockerCommitを送信します

目次ローカルでコンテナを作成した後、このコンテナに基づいてローカル イメージを作成し、このイメージを...

CentOS7でFTPサーバーを設定する方法

FTP は主にファイル転送に使用され、Linux では vsftpd で実装されるのが一般的です。F...

Prometheus を使用して、MySQL の自動増分主キーの残りの使用可能パーセンテージをカウントします。

最近、本番環境のデータベースがログデータを狂ったように書き込み、主キー値のオーバーフローを引き起こし...

Vueのリストレンダリングの詳細な説明

目次1. v-for: 配列の内容を走査する(よく使われる) 2. v-for: オブジェクトのプロ...

Dockerでmysqlのルートパスワードを変更する方法

最初のステップはmysqlコンテナを作成することです docker exec -it コンテナID ...

MySQL SQL ステートメントが遅い場合の一般的な原因と解決策

1. インデックス不足または無効なインデックスによるクエリの遅延数千万件のデータを含むテーブルで、イ...

CSS の子要素の Z インデックスと親要素の兄弟ノードの階層問題を解決する

1. 問題の出現フラット リストを作成しました。リストの一部には、マウスをホバーすると表示されるポッ...

JSはビデオの再生速度を制御するための簡単なサンプルコードを実装します

導入以前、ある問題に気づきました。学習ビデオを視聴しているとき、動きが遅すぎる、先生が黒板に書くのに...