効果的なデータベースのインデックス作成

正規化されたデータベースでの効果的なデータベースインデックス作成は、単にシステムに追加のリソースを投入するよりも、パフォーマンスに大きな影響を与えます。

正規化されたデータベースとは何ですか？

素人の言葉で言えば、正規化とは、データを分割して更新可能なデータの小さなチャンクにリンクすることにより、データの冗長性を減らす方法でリレーショナルデータベースを構造化するプロセスです。

この記事では、主に正規化された構造で機能するデータベースに焦点を当て、ほとんどの人が精通している（または想像できる）領域である金融取引、クライアント、および連絡先について説明します。

なぜ正規化されたのですか？

ある程度のレベルまたは正規化は、ほとんどのデータセットに大幅な拡張をもたらす可能性があります。データレイクと正規化されていないデータ処理は、ビジネス使用のいくつかの側面で注目を集めていますが、ほとんどの企業は、メインデータをある種の通常の形式で保存することでおそらく恩恵を受けるでしょう。できる限り;

更新をスピードアップします（以下を参照）
データの問い合わせを容易にします
通常、データフットプリントは小さくなります
業界の基準に準拠

私たちのアプローチ

私たちの標準的なアプローチは、データが3つの異なる方法で格納されているかのように見ることであり、新しいSQL Serverベースのシステムを構築するときは、それらを異なるスキーマに保持しようとします。

このアプローチは、以前のクライアントと連携しており、システムプロバイダーの速度も大幅に向上しています。

やがて、セクションごとに個別のサブ記事を追加し、複数のデータベース間のシステムニュートラルレポートに関する概念を探求するセクションを追加することを目指します。

インデックスの概要

SQL Serverに焦点を当てていますが、同じ原則が多くの異なるシステムに適用されます。インデックスの数とタイプは、読み取りと書き込みのパフォーマンスを個別に改善または低下させる可能性があります。

クラスター化

テーブルごとに1つに制限されており、これにより、データがディスクに保存される方法が定義されます。

このタイプのインデックスを持つテーブルはクラスター化テーブルと呼ばれ、ないテーブルはヒープと呼ばれます。

非クラスター化

これは、各行を参照する個別のテーブルとほぼ考えることができますが、SQL Serverでは、実際のストレージはテーブルの種類（クラスター化/ヒープ）に応じて変化します。

独自性

これらのインデックスは両方とも一意である可能性があり、適切に使用すると、データの保存方法に実際の機能強化をもたらすことができます。

複合インデックス

すべてのインデックスは1つ以上の列を使用できますが、クラスター化インデックスは900バイト未満である必要があります。

ちょっと待ってください、主キーはどうですか？

人々が「主キー」について言及しているとき、彼らは「一意のクラスター化インデックス」について話していることがよくあり、かなりの数の人々がこれを整数ベースのIDフィールド内のテーブルに自動的に格納します。レコードが作成されると、外部キーを使用して別のテーブルから参照できます。

外部キーは、実際には任意の一意のインデックスを参照でき、複数の列を参照することもできます。

参照データ

この領域には、アカウントタイプや支払いタイプなど、チェーンのさらに下流にある別のテーブルによって参照されるすべてのトップレベル情報を含める必要があります。ここでの利点は、単一の更新を使用して正規化されたデータベースの複数の行を変更できる一方で、正規化されていない場合はすべての行を更新する必要があることです。

標準的な使用法

一般に、ID列を一意のクラスター化インデックスとして使用するのが理想的です。以下に4つのテーブルとスキーマを作成します。

Reference Tables

CREATE SCHEMA RefGOCREATE TABLE Ref.AddressType(AddressTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_AddressType PRIMARY KEY CLUSTERED,AddressTypeName NVARCHAR(100))CREATE TABLE Ref.ClientType(ClientTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_Client PRIMARY KEY CLUSTERED,ClientTypeName NVARCHAR(100))CREATE TABLE Ref.ContactType(ContactTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_ContactType PRIMARY KEY CLUSTERED,ContactTypeName NVARCHAR(100))CREATE TABLE Ref.TransactionType(TransactionTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_TransactionType PRIMARY KEY CLUSTERED,TransactionTypeName NVARCHAR(100))

ビジネスデータ

この中間レベルの領域には、アカウント、クライアント、連絡先、または他の何かによって参照される可能性のあるその他の領域が含まれ、タイプ情報も参照されます。

このレベルは、さまざまなアプローチが混在している可能性があるため、通常、メインインデックスを配置する場所を決定するという点で最も扱いにくいレベルです。

以下は、アドレス、クライアント、および連絡先テーブルを作成するためのテーブルです。このコードには、Client、Address、およびAddressタイプのフィールドを結合する追加の（結合）テーブルがあり、ここでは、他のテーブルとは異なる方法で実行されるクラスター化インデックスを作成しました。これは、ほとんどのアプリケーションで、これは読み取りを多用するテーブルであり、挿入パフォーマンスの最小限の向上を受け入れることができるためです。これが私たちによって構築されたアプリケーションである場合、おそらく同様の方法でクライアントの連絡先の詳細を分離します。

Business Tables

CREATE SCHEMA BusGOCREATE TABLE Bus.[Address](AddressID INT CONSTRAINT PK_Bus_Address PRIMARY KEY CLUSTERED,AddressName NVARCHAR(100),AddressTypeID INT CONSTRAINT FK_Bus_Client_AddressTypeID FOREIGN KEY REFERENCES Ref.AddressType(AddressTypeID),AddressLine1 NVARCHAR(MAX)--Use more detail as required...)CREATE TABLE Bus.Client(ClientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ClientName NVARCHAR(100),ClientType INT CONSTRAINT FK_Bus_Client_ClientType FOREIGN KEY REFERENCES Ref.ClientType(ClientTypeID))--Use one table to handle all client addressesCREATE TABLE Bus.ClientAddress(ClientAddressID INT IDENTITY(1,1) CONSTRAINT PK_Bus_ClientAddressID PRIMARY KEY NONCLUSTERED,AddressTypeID INT,ClientID INT,AddressID INT,CONSTRAINT UQ_Bus_ClientAddress UNIQUE NONCLUSTERED (ClientID,AddressTypeID)--This ensures one type per client, can slow down inserts slightly)CREATE UNIQUE CLUSTERED INDEX CDX_Bus_ClientAddress ON Bus.ClientAddress(ClientID,AddressTypeID,AddressID)CREATE TABLE Bus.Contact(ContactID INT IDENTITY(1,1) CONSTRAINT PK_Bus_Contact PRIMARY KEY CLUSTERED,ContactName NVARCHAR(100),ContactTypeID INT CONSTRAINT FK_Bus_Contact_ContactTypeID FOREIGN KEY REFERENCES Ref.ContactType(ContactTypeID)--Could be broken out into a joining table if desired--Use more detail as required...)

トランザクションデータ

この領域には、メモ、支払い、注文などが含まれ、通常、ビジネス領域と参照領域の両方を指します。

一意のキーは識別に適していますが、読み取り時間が影響を受けるため、一般的な使用法では、ディスク上のデータを並べ替える方法ではない可能性があります。以下に作成されたテーブルは1つだけですが、それはあなたにアイデアを与えるはずです。

Transactional Tables

CREATE SCHEMA TraGOCREATE TABLE Tra.[Transaction](TransactionID INT IDENTITY(1,1) CONSTRAINT PK_Tra_TransactionID PRIMARY KEY NONCLUSTERED,TransactionDate DATETIME CONSTRAINT DF_Tra_Transaction_TransactionDate DEFAULT GETUTCDATE(),--Use GETDATE() for local time.TransactionTypeID INT CONSTRAINT FK_Tra_Transaction_TransactionTypeID FOREIGN KEY REFERENCES Ref.TransactionType(TransactionTypeID),ClientID INT CONSTRAINT FK_Tra_Transaction_ClientID FOREIGN KEY REFERENCES Bus.Client(ClientID),ContactID INT CONSTRAINT FK_Tra_Transaction_ContactID FOREIGN KEY REFERENCES Bus.Contact(ContactID),TransactionAmount DECIMAL(18,2)--Use more detail as required...)CREATE CLUSTERED INDEX CDX_Tra_Transaction ON Tra.[Transaction](TransactionDate,TransactionTypeID,ClientID,ContactID)

参加とレポート

上記の架空のデータベースでは、現実の生活を可能な限り忠実に表現しようとしています。これは決して取らなければならないアプローチではなく、あなたは上記の情報をどのように使用するかについて最終的に責任があります。

データが第3層に入ると、インデックス作成の焦点は、アプリケーションまたはレポートからデータを読み取る方法に移りました。これには、テーブル間の結合、およびテーブルに含まれる可能性のある、または含まれる可能性のあるポイントが必ず含まれます。 WHERE句。