SQLサーバー Archi構造(説明あり)

MS SQL Server はクライアントサーバーです archi構造。 MS SQL Server プロセスは、クライアント アプリケーションがリクエストを送信することから始まります。 SQL Server は要求を受け入れ、処理し、処理されたデータを使用して応答します。全体について詳しく話しましょう archi以下に示す構造:

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

  1. プロトコル層
  2. リレーショナル エンジン
  3. ストレージエンジン
SQLサーバー Archi構造
SQLサーバー Archi構造図

プロトコル層 – SNI

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

共有メモリ

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

プロトコル層 - SNI

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

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

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

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

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

「。」

「ローカルホスト」

"127.0.0.1"

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

プロトコル層 - SNI

TCP / IP

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

TCP / IP

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

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

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

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

TCP / IP

名前付きパイプ

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

名前付きパイプ

ここに トム と彼 隣人、シエラは同じです 物理的な 場所、お互いが隣人であること。 彼らは経由して通信しています イントラネットワーク。 同様に、 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 パーサーの主な仕事は、クエリをチェックすることです。 構文上および意味上のエラー。 最後に、それ クエリツリーを生成します。 詳しく話し合いましょう。

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 にバインドします。 これは次のようにも知られています バインディング.
  • とplexユーザークエリに VIEW が含まれる場合、この値は増加します。 ノーマライザーは、内部に保存されているビュー定義などによる置換を実行します。

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

SELECT * from USER_ID

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

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

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

オプティマイザ

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

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

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

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

例:

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

結論: 選択することがより重要です wise嘘だ。正確に言うと、どちらを選択しますか オプションは最も安いものではなく、最良のものです。

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

オプティマイザ

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

  • これはまたとして知られています 最適化の前段階.
  • 場合によっては、簡単な計画と呼ばれる、実用的で実行可能な計画が 1 つだけ存在する場合があります。最適化された計画を作成する必要はありません。その理由は、archiこれ以上増やすと、同じ実行時実行プランが見つかることになります。それも Se の追加コストでarchi最適化されたプランではNGですが、まったく必要ありませんでした。
  • Trivial プランが見つからない場合は 1st フェーズが始まります。

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

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

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

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

クエリ実行プログラム

クエリ実行プログラム

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

ストレージエンジン

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

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

ストレージエンジン

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

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

ファイルの種類

ファイルの種類

  1. プライマリファイル
  • すべてのデータベースには XNUMX つのプライマリ ファイルが含まれています。
  • これには、テーブル、ビュー、トリガーなどに関連するすべての重要なデータが保存されます。
  • 拡張子は です。MDF 通常は任意の拡張子にすることができます。
  1. 二次ファイル
  • データベースには複数のセカンダリ ファイルが含まれる場合と含まれない場合があります。
  • これはオプションであり、ユーザー固有のデータが含まれます。
  • 拡張子は です。ndf 通常は任意の拡張子にすることができます。
  1. ログファイル
  • 先行書き込みログとも呼ばれます。
  • 拡張子は です。LDF
  • トランザクション管理に使用されます。
  • これは、不要なインスタンスから回復するために使用されます。 コミットされていないトランザクションへのロールバックという重要なタスクを実行します。

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

アクセス方法

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

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

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

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

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

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

アクセス方法

Buffer マネージャー

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

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

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

Buffer マネージャー

プランキャッシュ

  • 既存のクエリ プラン: 最大XNUMXWの出力を提供する buffer マネージャーは、保存されているプラ​​ン キャッシュに実行プランがあるかどうかを確認します。 「はい」の場合、クエリ プラン キャッシュとそれに関連するデータ キャッシュが使用されます。
  • 初回キャッシュプラン: 既存のプラン キャッシュはどこから来ますか?初回クエリ実行プランが実行されており、com である場合plex、プレーン キャッシュに保存するのが合理的です。 これにより、次回 SQL サーバーが同じクエリを取得するときの可用性が向上します。 したがって、初めて実行される場合、プランの実行が保存されるのはクエリ自体に他なりません。

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

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

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

Buffer キャッシュ - ソフト解析

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

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

データ ストレージ - ハード解析

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

ダーティページ

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

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

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

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

ログマネージャー

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

ロックマネージャー

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

実行プロセス

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

まとめ

  • 3種類のクライアントサーバー Archi存在する構造: 1) 共有メモリ 2) TCP/IP 3) 名前付きパイプ
  • TDS は Sybase によって開発され、現在は Sybase によって所有されています。 Microsoftは、クライアント マシンからサーバー マシンへのデータ転送のためにネットワーク パケットにカプセル化されるパケットです。
  • リレーショナル エンジンには、次の XNUMX つの主要コンポーネントが含まれています。CMD パーサー: これは構文エラーと意味エラーの原因となり、最終的にクエリ ツリーを生成します。オプティマイザ: オプティマイザーの役割は、最良の費用対効果の高い実行計画ではなく、最も安価な実行計画を見つけることです。

    クエリ実行者: クエリ実行プログラムは、Access Method を呼び出し、実行に必要なデータ フェッチ ロジックの実行プランを提供します。

  • ファイルには、プライマリ ファイル、セカンダリ ファイル、ログ ファイルの XNUMX 種類が存在します。
  • ストレージ エンジン: 以下がありますwing 重要なコンポーネントアクセス方法: このコンポーネントは、クエリが選択ステートメントであるか非選択ステートメントであるかを判断します。呼び出します Buffer それに応じて転送マネージャー。Buffer マネージャー: Buffer マネージャーは、プラン キャッシュ、データ解析、ダーティ ページのコア機能を管理します。

    トランザクションマネージャー: ログ マネージャーとロック マネージャーを使用して、非選択トランザクションを管理します。 また、先行書き込みログと遅延ライターの重要な実装も容易になります。