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

私はプロの DBA ではありませんが、B/S アーキテクチャを持つ開発者として、常にデータベースと切り離せない存在です。通常、開発者は SQL の 4 つの古典的なステートメント (select、insert、delete、update) のみを使用します。しかし、私はそれらがどのように機能するのかを一度も勉強したことがありません。この記事では、データベース内で select がどのように機能するかについて話したいと思います。

B/S アーキテクチャの最も古典的なトピックは、3 層アーキテクチャに他なりません。これは、データ層、ビジネス ロジック層、プレゼンテーション層に大別できます。データ層の役割は、一般にデータベースと対話することです。レコードのクエリなど。多くの場合、クエリ SQL を作成し、その SQL を実行するプログラムを呼び出します。しかし、その内部ワークフローはどうなっているのでしょうか?最初にどのステップを実行するか、次にどのステップを実行するかなど、ほとんどの友人は私と同じように確信が持てないと思います。

ステップ 1: アプリケーションは実行のためにクエリ SQL ステートメントをサーバーに送信します

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

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

1. クエリ アナライザーをよく使用する友人は、クエリ ステートメントを実行するときに特に長時間実行する必要があることを知っているでしょう。ただし、同じステートメントをすぐに実行するか、一定期間内に実行すると、クエリ結果は短時間で返されます。

理由:

サーバーは、クエリ要求を受信した後、すぐにデータベースにアクセスしてクエリを実行するのではなく、データベース内のプラン キャッシュを調べて、対応する実行プランがあるかどうかを確認します。実行プランはコンパイルされたものを直接呼び出しますので、実行プランのコンパイル時間が節約されます。

クエリされた行がデータ バッファー記憶域にすでに存在する場合、物理ファイルをクエリする必要はありませんが、データはキャッシュからフェッチされます。この方法では、メモリからデータをフェッチする方が読み取りよりもはるかに高速になります。ハードディスクからのデータの取得、向上 クエリ効率を向上させるために、データ バッファの格納領域については後述します。

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

注: この時点で返されるエラー メッセージには、select と書かれた select などの基本的な構文エラー情報のみが含まれます。エラー メッセージにリストにない列が含まれている場合、サーバーはこの時点ではチェックしません。単なる構文検証であるため、セマンティクスが正しいかどうかは次のステップに委ねられます。

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

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

5. 次のステップは、SQL ステートメントの構文とセマンティクスが正しいことを確認することです。この時点で、データベース ユーザーが対応するアクセス許可を持っていない場合は、クエリ結果が取得されない可能性があります。大規模なプロジェクトでは、アプリケーションに複数のデータベース接続文字列が含まれることがよくあります。これらのデータベース ユーザーには、読み取り専用の権限を持つ者、書き込み専用の権限を持つ者、および読み取りと書き込みが可能な者がいます。実行する異なるユーザーを選択します。注意を払わないと、SQL ステートメントがどれほど完璧であっても、完璧であれば意味がありません。

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

実行プランが決定されると、実行プランは SQL プラン キャッシュに保存され、次回同じ実行リクエストがあった場合は、実行プランの再コンパイルを避けるためにプラン キャッシュから直接フェッチされます。

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

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

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

クエリ ステートメントに含まれるデータ行がデータ バッファー記憶域に読み込まれている場合、サーバーはデータ バッファー記憶域からデータを直接読み取り、アプリケーションに返します。物理ファイルからデータを取得する必要がなくなり、クエリ速度が向上します。

データ行がデータ バッファーにない場合、レコードは物理ファイルから読み取られてアプリケーションに返され、データ行は次回使用するためにデータ バッファーに書き込まれます。

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

DBCCDROPCLEANBUFFERS

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

DBCCFREEPROCCACHE

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

DBCCFREESYSTEMCACHE

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

これは基本的にSQLキャッシュの影響を排除することしかできないようですが、キャッシュを完全に削除する解決策がある場合は、アドバイスをお願いします。

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

SQL 構文が正しいことを確認します。

SQL セマンティクスが正しいこと、つまりオブジェクトが存在するかどうかを確認します。

データベース ユーザーが対応するアクセス権を持っているかどうか。