データベースSQL SELECTクエリの仕組み

データベースSQL SELECTクエリの仕組み

私たちは Web 開発者として、プロの DBA ではありませんが、データベースなしではやっていけません。ほとんどの開発者は、select、insert、delete、update という 4 つの標準的な SQL ステートメントの使い方しか知りません。これまで、select がどのように動作するかを学んだことがなかったので、ここでは、select がデータベース内でどのように動作するかについて説明します。

B/S アーキテクチャで最も古典的なトピックは 3 層アーキテクチャであり、これは大まかにデータ層、ビジネス ロジック層、プレゼンテーション層に分けられます。データ層の機能は、一般的にレコードのクエリなど、データベースと対話することです。多くの場合、クエリ SQL を記述してから、プログラムを呼び出して SQL を実行します。しかし、その内部ワークフローはどのようなものでしょうか?私の友人のほとんども、私と同じように、最初にどのステップを踏めばいいのか、次にどのステップを踏めばいいのかわからないと思います。

ステップ1: アプリケーションはクエリSQL文をサーバーに送信して実行します。

データ層で SQL ステートメントを実行すると、アプリケーションは対応するデータベース サーバーに接続し、SQL ステートメントをサーバーに送信して処理します。

ステップ2: サーバーは要求されたSQL文を解析します

1. SQL プラン キャッシュ。クエリ アナライザーをよく使用する友人は、おそらくこのような事実を知っているでしょう。多くの場合、クエリ ステートメントは、初めて実行するときに実行に非常に長い時間がかかりますが、同じステートメントをすぐに実行するか、特定の期間内に実行すると、クエリ結果が非常に短い時間で返されます。

理由:

  • クエリ要求を受信した後、サーバーはすぐにデータベースをクエリするのではなく、データベースのプラン キャッシュを調べて、対応する実行プランがあるかどうかを確認します。存在する場合は、コンパイルされた実行プランを直接呼び出すため、実行プランのコンパイル時間が節約されます。
  • クエリ対象の行がデータ バッファー ストレージ領域にすでに存在する場合、物理ファイルをクエリする必要はありません。代わりに、データはキャッシュから取得されます。この方法では、メモリからデータを取得する方がハード ディスクからデータを読み取るよりもはるかに高速であるため、クエリの効率が向上します。データ バッファー ストレージ領域については後で説明します。

2. SQL プラン キャッシュ内に対応する実行プランがない場合、サーバーはまずユーザーが要求した SQL ステートメントの構文検証を実行します。構文エラーがある場合、サーバーはクエリ操作を終了し、呼び出し元のアプリケーションに対応するエラー メッセージを返します。

注意: この時点で返されるエラー メッセージには、select が selec として記述されているなど、基本的な構文エラー情報のみが含まれます。エラー メッセージにテーブルに存在しない列が含まれている場合、これは構文の検証のみであるため、サーバーはこの時点でそれをチェックしません。セマンティクスが正しいかどうかは、次の手順で処理されます。

3. 構文が適合したら、テーブル名、列名、ストアド プロシージャ、その他のデータベース オブジェクトが実際に存在するかどうかなど、セマンティクスが正しいかどうかの検証を開始します。存在しないオブジェクトが見つかった場合は、アプリケーションにエラーが報告され、クエリが終了します。

4. 次のステップは、オブジェクトの解析ロックを取得することです。テーブルをクエリすると、サーバーは最初にオブジェクトをロックして、データの一貫性を確保します。ロックされていない場合は、この時点でデータが挿入されますが、ロックがないため、クエリはレコードを読み取ってしまい、トランザクションの失敗により一部の挿入がロールバックされ、ダーティリードが発生します。

5. 次のステップは、データベース ユーザーの権限を確認することです。SQL ステートメントの構文とセマンティクスが正しい場合でも、この時点ではクエリ結果が取得されない場合があります。データベース ユーザーが対応するアクセス権を持っていない場合、サーバーはアプリケーションに権限不足エラーを報告します。大規模なプロジェクトでは、プロジェクト内に複数のデータベース接続文字列が存在することがよくあります。これらのデータベース ユーザーにはさまざまな権限があり、読み取り専用権限、書き込み専用権限、読み取りと書き込みが可能な権限があります。異なる操作に応じて、異なるユーザーが実行するように選択されます。注意しないと、SQL ステートメントがどれだけ完璧であっても役に立たなくなります。

6. 分析の最後のステップは、最終的な実行計画を決定することです。構文、セマンティクス、権限が検証された後、サーバーはすぐに結果を返すのではなく、SQL を最適化し、さまざまなクエリ アルゴリズムを選択して、最も効率的な形式で結果をアプリケーションに返します。たとえば、テーブル結合クエリを実行する場合、サーバーは最終的にコスト、どのインデックスがより効率的かなどに基づいて、ハッシュ結合、マージ結合、またはループ結合のどれを使用するかを決定します。ただし、自動最適化には制限があります。効率的なクエリ SQL を記述する場合は、SQL クエリ ステートメントを最適化する必要があります。

実行プランが決定されると、SQL プラン キャッシュに保存されます。次に同じ実行要求があった場合、実行プランの再コンパイルを回避するために、プラン キャッシュから直接取得されます。

ステップ3: ステートメントの実行

サーバーは SQL ステートメントの解析を完了すると、ステートメントの実際の意味を認識し、実際に SQL ステートメントを実行します。

現時点では 2 つの状況があります。

  • クエリ ステートメントに含まれるデータ行がデータ バッファー ストレージ領域に読み込まれている場合、サーバーはデータ バッファー ストレージ領域からデータを直接読み取り、アプリケーションに返します。これにより、物理ファイルからの読み取りが回避され、クエリ速度が向上します。
  • データ行がデータ バッファー ストレージ領域にない場合は、レコードが物理ファイルから読み取られてアプリケーションに返され、データ行は次回の使用のためにデータ バッファー ストレージ領域に書き込まれます。

注: SQL キャッシュにはいくつかの種類があります。興味のある方は、ここで検索できます。キャッシュの存在により、最適化の結果をすぐに確認することが難しい場合があります。キャッシュの存在により 2 回目の実行が非常に高速になるため、通常は最初にキャッシュを削除してから、最適化の前後のパフォーマンスを比較します。一般的な方法をいくつか示します。

DBCC DROPCLEANBUFFERS

バッファ プールからすべてのクリア バッファを削除します。

DBCC FREEPROCCACHE

プロシージャ キャッシュからすべての要素を削除します。

DBCC FREESYSTEMCACHE

すべてのキャッシュから未使用のキャッシュ エントリをすべて解放します。

SQL Server 2005 データベース エンジンは、現在のエントリにメモリを使用できるようにするために、未使用のキャッシュ エントリを事前にバックグラウンドでクリーンアップします。ただし、このコマンドを使用して、すべてのキャッシュから未使用のエントリを手動で削除することができます。

これは基本的にSQLキャッシュの影響を排除することしかできません。キャッシュを完全に排除する解決策はないようです。もし何かあれば教えてください。

結論: アプリケーションによって送信された SQL を実行するサービスの操作プロセスを知ることによってのみ、アプリケーションを適切にデバッグできます。

  • SQL 構文が正しいことを確認してください。
  • SQL のセマンティックの正確性、つまりオブジェクトが存在するかどうかを確認します。
  • データベース ユーザーが対応するアクセス権を持っているかどうか。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。

以下もご興味があるかもしれません:
  • MySQL マスター/スレーブ データベース同期構成と一般的なエラー
  • PHP シングルトン モード データベース接続クラスとページ静的実装メソッド
  • データベースの水平セグメンテーションを実装するための2つのアイデア
  • MySQL データベースの大文字と小文字の区別の問題
  • Pythonはadbapiを使用してMySQLデータベースの非同期ストレージを実装します
  • データベースフィールド設計の経験について話す
  • MySQLデータベースのストアドプロシージャとトランザクションの違い
  • MySQLデータベースのQPSとTPSの意味と計算方法
  • MySQLデータベースが大きすぎる場合にバックアップと復元を行う方法
  • データベースの冗長フィールドを合理的に使用する方法

<<:  Dockerコンテナのホスト間通信におけるダイレクトルーティングの詳細な説明

>>:  Vueカスタムテーブル列実装プロセス記録

推薦する

Windows 10 で Ubuntu 20.04 LTS をアップデートする方法

2020 年 4 月 23 日、本日、Windows 上の Ubuntu 20.04 では、Ubun...

Nginx のリロード プロセスの背後にある真実を探る

本日の記事では、主にNginxのリロードプロセスについて紹介します。実は前回の記事では、nginx ...

mysql5.7.19 winx64 解凍版のインストールと設定のチュートリアル

mysql 5.7.19 winx64解凍版のインストールチュートリアルを収録しました。具体的な内容...

MySQLデータベースのSYNフラッディング問題を解決する

Syn 攻撃は、最も一般的で最も簡単に悪用される攻撃方法です。TCP プロトコルの欠陥を利用して、偽...

Vue.js での $emit の使用に関する詳細な説明

1. 親コンポーネントは props を使用して子コンポーネントにデータを渡すことができます。 2....

Docker がデータベースのデプロイに適さない 7 つの理由のまとめ

Docker は過去 2 年間で非常に人気が高まっています。開発者はすべてのアプリケーションとソフト...

CentOS はローカル yum ソースを使用して LAMP 環境を構築するグラフィック チュートリアル

この記事では、ローカル yum ソースを使用して CentOS 上に LAMP 環境を構築する方法に...

MySQL 接続制御プラグインの紹介

目次1. 接続制御プラグイン(connection_control)の紹介1.1 connectio...

7つの便利なTypeScriptの新機能

目次1. オプションの連鎖2. NULL値の結合3. 再帰型エイリアス4. アサーション署名5. P...

uni-app で scss を使用するサンプル コード

遭遇した落とし穴私は午後中ずっと、uni-app で scss を使用する際の落とし穴を解決すること...

TypeScript ジェネリックを簡単に説明する方法

目次概要ジェネリック医薬品とはビルドシステムジェネリック医薬品の一般的な理解ジェネリッククラスジェネ...

webkit-box-reflect を巧みに使用してさまざまな動的効果を実現する (要約)

かなり前の記事で、 -webkit-box-reflectプロパティについて説明しました。リフレクシ...

Docker で FastDFS をデプロイする方法

Dockerにfastdfsをインストールするディレクトリをマウント-v /e/fdfs/トラッカー...

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

この記事ではMySQL 5.7.21のインストールと設定方法を記録しています。具体的な内容は以下のと...

MySqlは、外部ネットワーク接続クライアントの低速問題を解決するためにskip-name-resolveを使用します。

Tencent Cloud上に構築されたMySQLは、開発用コンピュータでNavicatを使用して...