データベース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 を実行するサービスの操作プロセスを知ることによってのみ、アプリケーションを適切にデバッグできます。