SQLサーバー Archi構造（説明あり）

MS SQL Server はクライアントサーバーアーキテクチャです。MS SQL Server プロセスは、クライアントアプリケーションが要求を送信することから始まります。SQL Server は要求を受け入れ、処理し、処理されたデータで応答します。以下に示すアーキテクチャ全体を詳しく説明しましょう。

以下の図が示すように、SQL Server には 3 つの主要なコンポーネントがあります。 Archi構造:

プロトコル層
リレーショナルエンジン
ストレージエンジン

プロトコル層 – SNI

MS SQL SERVER PROTOCOL LAYER は 3 種類のクライアントサーバーをサポートします Archi構造。から始めます 「3種類のクライアントサーバー」 Archiテクチャ」 MS SQL Server がサポートするもの。

共有メモリ

早朝の会話シナリオを再考してみましょう。

ママとトム – ここでは、トムと彼のママは論理的に同じ場所、つまり自宅にいました。トムはコーヒーを頼むことができ、ママはそれを温かいまま出すことができました。

MS SQL サーバー – ここに MS SQL サーバーが提供する 共有メモリプロトコル。ここに クライアント と MS SQL サーバーは同じマシン上で実行されます。どちらも共有メモリプロトコル経由で通信できます。

類推： 上記の XNUMX つのシナリオでエンティティをマップしてみましょう。トムをクライアントに、ママを SQL サーバーに、ホームをマシンに、口頭コミュニケーションを共有メモリプロトコルに簡単にマッピングできます。

設定とインストールのデスクから:

ローカルDB接続用 – 入力 SQL Management Studio, 「サーバー名」オプションは次のとおりです。

「。」

「ローカルホスト」

"127.0.0.1"

「マシン\インスタンス」

TCP / IP

さて、夕方のことを考えてみましょう。トムはパーティー気分です。彼は有名なコーヒーショップから注文したコーヒーを望んでいます。そのコーヒーショップは彼の家から10キロ離れたところにあります。

ここで、トムとスターバックは物理的に異なる場所にいます。トムは家にいて、スターバックスはにぎやかな市場にいます。彼らはセルラーネットワークを介して通信しています。同様に、MS SQL SERVER は、 TCP / IPプロトコルここで、CLIENT と MS SQL Server は相互にリモートであり、別のマシンにインストールされています。

類推： 上記の XNUMX つのシナリオでエンティティをマップしてみましょう。トムをクライアントに、スターバックを SQL サーバーに、自宅/市場をリモートの場所に、最後にセルラーネットワークを TCP/IP プロトコルに簡単にマッピングできます。

設定/インストールのデスクからのメモ:

SQL Management Studio – TCP\IP 経由の接続の場合、「サーバー名」オプションは「サーバーのマシン\インスタンス」である必要があります。
SQL サーバーは TCP/IP のポート 1433 を使用します。

名前付きパイプ

さて、いよいよ夜になり、トムは隣人のシエラがとても上手に作ってくれた薄緑茶を飲みたかったのです。

ここにトムと彼隣人、シエラは同じです 物理的な 場所、お互いが隣人であること。 彼らは経由して通信しています イントラネットワーク。 同様に、 MS SQL サーバー を介して対話する機能を提供します。 名前付きパイプ プロトコル。ここでクライアントと MS SQL サーバー 経由で接続されています LAN.

類推： 上記の XNUMX つのシナリオでエンティティをマップしてみましょう。 Tom をクライアントに、Sierra を SQL サーバーに、Neighbor を LAN に、そして最後にイントラネットワークを名前付きパイププロトコルに簡単にマッピングできます。

設定/インストールのデスクからのメモ:

名前付きパイプ経由の接続の場合。 このオプションはデフォルトでは無効になっており、SQL 構成マネージャーで有効にする必要があります。

TDSとは何ですか？

クライアントサーバーには 3 つのタイプがあることがわかりました。 Archi構造については、TDS を見てみましょう。

TDS は表形式データストリームの略です。
3 つのプロトコルはすべて TDS パケットを使用します。 TDS はネットワークパケットにカプセル化されます。これにより、クライアントマシンからサーバーマシンへのデータ転送が可能になります。
TDS は最初に Sybase によって開発され、現在は Sybase によって所有されています。 Microsoft

リレーショナルエンジン

リレーショナルエンジンはクエリプロセッサとも呼ばれます。それは SQLサーバークエリが正確に何を行う必要があるか、そしてそれを最適に実行する方法を決定するコンポーネント。ストレージエンジンにデータを要求し、返された結果を処理することにより、ユーザークエリを実行します。

に描かれているように、 Archi構造図があります 3つの主要コンポーネント リレーショナルエンジンの。コンポーネントを詳しく調べてみましょう。

CMDパーサー

プロトコル層から受信したデータは、リレーショナルエンジンに渡されます。 「CMDパーサー」 クエリデータを受信するリレーショナルエンジンの最初のコンポーネントです。 CMD パーサーの主な仕事は、クエリをチェックすることです。 構文上および意味上のエラー。 最後に、それ クエリツリーを生成します。詳しく話し合いましょう。

構文チェック:

他のプログラミング言語と同様に、MS SQL にも事前定義されたキーワードのセットがあります。また、SQL Server には、SQL Server が理解できる独自の文法があります。
SELECT、INSERT、UPDATE、その他多くのキーワードは、MS SQL の事前定義されたキーワードリストに属します。
CMD パーサーは構文チェックを行います。ユーザーの入力がこれらの言語構文または文法規則に従っていない場合、 エラーを返します。

例：ロシア人が日本食レストランに行ったとしましょう。彼はロシア語でファストフードを注文する。残念ながら、ウェイターは日本語しか理解できません。最も明白な結果は何でしょうか?

答えは、ウェイターはそれ以上注文を処理できないということです。

SQL サーバーが受け入れる文法や言語に逸脱があってはなりません。存在する場合、SQL サーバーはそれを処理できないため、エラーメッセージが返されます。

MS SQL クエリについては、今後のチュートリアルで詳しく学習します。ただし、以下の最も基本的なクエリ構文を次のように考慮してください。

SELECT * from <TABLE_NAME>;

ここで、構文が何を行うかを理解するために、ユーザーが次のような基本的なクエリを実行するとします。

SELECR * from <TABLE_NAME>

「SELECT」の代わりに「SELECR」と入力したことに注意してください。

結果： CMD パーサーはこのステートメントを解析し、エラーメッセージをスローします。「SELECR」は、事前に定義されたキーワード名と文法に従っていないため。ここで、CMD パーサーは「SELECT」を期待していました。

セマンティックチェック:

これを実行するのは、 ノーマライザー.
最も単純な形式では、クエリ対象の列名、テーブル名がスキーマに存在するかどうかをチェックします。存在する場合は、Query にバインドします。これは次のようにも知られています バインディング.
ユーザークエリに VIEW が含まれている場合、複雑さが増します。Normalizer は、内部に保存されているビュー定義との置き換えなどを実行します。

以下の例を使ってこれを理解しましょう –

SELECT * from USER_ID

結果： CMD パーサーは、セマンティックチェックのためにこのステートメントを解析します。ノーマライザーは要求されたテーブル (USER_ID) が存在しないため見つからないため、パーサーはエラーメッセージをスローします。

クエリツリーを作成します。

このステップでは、クエリを実行できるさまざまな実行ツリーを生成します。
すべての異なるツリーが同じ目的の出力を持つことに注意してください。

オプティマイザ

オプティマイザーの仕事は、ユーザーのクエリの実行計画を作成することです。これは、ユーザークエリがどのように実行されるかを決定する計画です。

すべてのクエリが最適化されるわけではないことに注意してください。最適化は、SELECT、INSERT、DELETE、UPDATE などの DML (データ変更言語) コマンドに対して行われます。このようなクエリは最初にマークされ、その後オプティマイザに送信されます。 CREATE や ALTER などの DDL コマンドは最適化されませんが、代わりに内部形式にコンパイルされます。クエリのコストは、CPU 使用率、メモリ使用率、入出力のニーズなどの要因に基づいて計算されます。

オプティマイザーの役割は、 最良ではなく最も安価な、費用対効果の高い実行計画。

オプティマイザーの技術的な詳細に入る前に、以下の実際の例を検討してください。

例：

たとえば、オンライン銀行口座を開設したいとします。口座開設に最大 2 日かかる銀行があることはすでにご存知でしょう。ただし、他の 20 の銀行のリストもあり、2 日以内にかかる場合とそうでない場合があります。これらの銀行との連携を開始すると、2 日以内にどの銀行がかかるかを判断できます。現在、銀行を見つけるのに 2 日もかからない可能性があり、検索作業自体によって追加の時間が失われます。最初の銀行自体に口座を開設した方が良かったでしょう。

結論： 賢明に選択することがより重要です。正確に言うと、 オプションは最も安いものではなく、最良のものです。

同様に、MS SQL オプティマイザーは、組み込みの徹底的/ヒューリスティックアルゴリズムに基づいて動作します。目標は、クエリの実行時間を最小限に抑えることです。すべてのオプティマイザーアルゴリズムは の妥当性 Microsoft そして秘密。 しかし, 以下は、MS SQL オプティマイザーによって実行される高レベルの手順です。最適化の検索は、次の図に示すように XNUMX つのフェーズに従います。

フェーズ 0: 簡単な計画の検索:

これはまたとして知られています 最適化の前段階.
場合によっては、実用的な実行可能なプランが 1 つしかないことがあります。これは、単純なプランと呼ばれます。最適化されたプランを作成する必要はありません。その理由は、さらに検索すると、同じ実行時実行プランが見つかるからです。しかも、最適化されたプランを検索するという余分なコストがかかりますが、これはまったく必要ありません。
Trivial プランが見つからない場合は 1^st フェーズが始まります。

フェーズ 1: トランザクション処理計画の検索

これには、次の検索が含まれます。 シンプルかつ複雑な計画.
簡易プラン検索：クエリに含まれるカラムとインデックスの過去データを統計分析に利用します。これは通常、テーブルごとに XNUMX つのインデックスで構成されますが、これに限定されません。
それでも、単純なプランが見つからない場合は、より複雑なプランが検索されます。これには、テーブルごとに複数のインデックスが含まれます。

フェーズ 2: 並列処理と最適化。

上記の戦略がいずれも機能しない場合、オプティマイザーは並列処理の可能性を探します。これはマシンの処理能力と構成によって異なります。
それでも不可能な場合は、最終的な最適化フェーズが開始されます。ここで、最適化の最終的な目的は、クエリを最適な方法で実行するための他のすべての可能なオプションを見つけることです。最終最適化フェーズ Algorithms 　 Microsoft 可否。

クエリ実行プログラム

クエリ実行プログラムの呼び出し アクセス方法。 実行に必要なデータフェッチロジックの実行プランを提供します。ストレージエンジンからデータを受信すると、その結果がプロトコル層に公開されます。最後に、データはエンドユーザーに送信されます。

ストレージエンジン

ストレージエンジンの仕事は、ディスクや SAN などのストレージシステムにデータを保存し、必要に応じてデータを取得することです。ストレージエンジンについて詳しく説明する前に、データがどのように保存されるかを見てみましょう。 データベース と 利用可能なファイルの種類。

データファイルとエクステント:

データファイルは、データページの形式でデータを物理的に格納します。各データページのサイズは 8 KB で、SQL Server の最小のストレージユニットを形成します。これらのデータページは論理的にグループ化されてエクステントを形成します。 SQL Server ではオブジェクトにページが割り当てられません。

オブジェクトのメンテナンスはエクステントを介して行われます。このページには、サイズが 96 バイトのページヘッダーと呼ばれるセクションがあり、ページタイプ、ページ番号、使用済みスペースのサイズ、空きスペースのサイズ、次のページと前のページへのポインターなどのページに関するメタデータ情報が含まれています。、など。

ファイルの種類

プライマリファイル

すべてのデータベースには XNUMX つのプライマリファイルが含まれています。
これには、テーブル、ビュー、トリガーなどに関連するすべての重要なデータが保存されます。
拡張子はです。MDF 通常は任意の拡張子にすることができます。

二次ファイル

データベースには複数のセカンダリファイルが含まれる場合と含まれない場合があります。
これはオプションであり、ユーザー固有のデータが含まれます。
拡張子はです。ndf 通常は任意の拡張子にすることができます。

ログファイル

先行書き込みログとも呼ばれます。
拡張子はです。LDF
トランザクション管理に使用されます。
これは、不要なインスタンスから回復するために使用されます。コミットされていないトランザクションへのロールバックという重要なタスクを実行します。

ストレージエンジンには 3 つのコンポーネントがあります。それらを詳しく見てみましょう。

アクセス方法

クエリ実行プログラムとクエリ実行プログラムの間のインターフェイスとして機能します。 Buffer マネージャー/トランザクションログ。

Access Method 自体は何も実行しません。

最初のアクションは、クエリが次のとおりであるかどうかを判断することです。

Select ステートメント (DDL)
非選択ステートメント (DDL および DML)

結果に応じて、アクセスメソッドは次の手順を実行します。

クエリが次の場合 DDL、SELECT ステートメントでは、クエリが Buffer マネージャー さらなる処理のため。
そして if クエリ if DDL、NON-SELECT ステートメント、クエリはトランザクションマネージャーに渡されます。これには主に UPDATE ステートメントが含まれます。

Buffer マネージャー

Buffer マネージャーは、以下のモジュールのコア機能を管理します。

プランキャッシュ
データ解析： Buffer キャッシュとデータストレージ
ダーティページ

私たちは計画を学びます、 Buffer このセクションではデータキャッシュについて説明します。ダーティページについては「トランザクション」セクションで説明します。

プランキャッシュ

既存のクエリプラン: バッファマネージャは、保存されたプランキャッシュに実行プランが存在するかどうかを確認します。存在する場合は、クエリプランキャッシュとそれに関連付けられたデータキャッシュが使用されます。
初回キャッシュプラン: 既存のプランキャッシュはどこから取得されますか? 初回のクエリ実行プランが実行され、それが複雑な場合は、プランキャッシュに保存するのが合理的です。これにより、次回 SQL サーバーが同じクエリを取得したときに、より高速な可用性が確保されます。したがって、プラン実行が初めて実行される場合、保存されるのはクエリ自体だけです。

データ解析： Buffer キャッシュとデータストレージ

Buffer マネージャーは必要なデータへのアクセスを提供します。データキャッシュにデータが存在するかどうかに応じて、次の 2 つのアプローチが可能です。

Buffer キャッシュ – ソフト解析:

Buffer マネージャーは次のデータを探します Buffer データキャッシュ内。存在する場合、このデータはクエリエグゼキュータによって使用されます。これにより、データストレージからデータを取得する場合と比較して、キャッシュからデータを取得する場合の I/O 操作の数が減るため、パフォーマンスが向上します。

データストレージ – ハード解析:

データが存在しない場合 Buffer 管理者より必要なデータはデータストレージ内で検索されます。また、将来の使用に備えてデータをデータキャッシュに保存します。

ダーティページ

トランザクションマネージャーの処理ロジックとして格納されます。トランザクションマネージャーのセクションで詳しく学びます。

トランザクションマネージャー

トランザクションマネージャは、アクセスメソッドがクエリが非選択ステートメントであると判断したときに呼び出されます。

ログマネージャー

ログマネージャーは、トランザクションログのログを介して、システム内で行われたすべての更新を追跡します。
ログには トランザクション ID およびデータ変更レコードを含むシーケンス番号をログに記録します.
これは、を追跡するために使用されます トランザクションのコミットとトランザクションのロールバック.

ロックマネージャー

トランザクション中、データストレージ内の関連データはロック状態になります。このプロセスはロックマネージャーによって処理されます。
このプロセスにより、 データの一貫性と分離。 ACID プロパティとも呼ばれます。

実行プロセス

Log Manager はログ記録を開始し、Lock Manager は関連データをロックします。
データのコピーは次の場所に保持されます。 Buffer キャッシュ。
更新されるはずのデータのコピーはログバッファーに保持され、すべてのイベントはデータバッファー内のデータを更新します。
データを保存するページは、次のようにも呼ばれます。 ダーティページ.
チェックポイントと先行書き込みログ: このプロセスが実行され、ダーティページからディスクまでのすべてのページがマークされますが、ページはキャッシュ内に残ります。頻度は 1 分あたり約 XNUMX 回の実行です。ただし、ページは最初にログファイルのデータページにプッシュされます。 Buffer ログ。これはとして知られています 先行書き込みログ。
怠惰なライター: ダーティページはメモリ内に残る可能性があります。 SQL サーバーに大きな負荷が発生し、 Buffer 新しいトランザクションにメモリが必要になった場合、キャッシュからダーティページを解放します。 LRU – バッファプールからディスクにページを消去するための、最も最近使用されていないアルゴリズム。

まとめ

3種類のクライアントサーバー Archi存在する構造: 1) 共有メモリ 2) TCP/IP 3) 名前付きパイプ
TDS は Sybase によって開発され、現在は Sybase によって所有されています。 Microsoftは、クライアントマシンからサーバーマシンへのデータ転送のためにネットワークパケットにカプセル化されるパケットです。
リレーショナルエンジンには、次の XNUMX つの主要コンポーネントが含まれています。CMD パーサー: これは構文エラーと意味エラーの原因となり、最終的にクエリツリーを生成します。オプティマイザ： オプティマイザーの役割は、最良の費用対効果の高い実行計画ではなく、最も安価な実行計画を見つけることです。
クエリ実行者: クエリ実行プログラムは、Access Method を呼び出し、実行に必要なデータフェッチロジックの実行プランを提供します。
ファイルには、プライマリファイル、セカンダリファイル、ログファイルの XNUMX 種類が存在します。
ストレージエンジン: 次の重要なコンポーネントがありますアクセス方法： このコンポーネントは、クエリが選択ステートメントであるか非選択ステートメントであるかを判断します。呼び出します Buffer それに応じて転送マネージャー。Buffer マネージャー： Buffer マネージャーは、プランキャッシュ、データ解析、ダーティページのコア機能を管理します。
トランザクションマネージャー: ログマネージャーとロックマネージャーを使用して、非選択トランザクションを管理します。また、先行書き込みログと遅延ライターの重要な実装も容易になります。

よろしければ：