有效的数据库索引

规范化数据库中的有效数据库索引将带来更多的性能影响，而不仅仅是在系统上投入额外的资源

什么是规范化数据库？

用外行的术语来说，规范化是一种结构化关系数据库的过程，该过程通过分解数据并将其链接到较小的可更新数据块中来减少数据冗余。

本文主要关注以规范化结构工作的数据库，并将探讨大多数人熟悉（或可以想象）的领域，即金融交易，客户和联系人。

为什么要归一化？

某种程度或规范化可以为大多数数据集带来巨大的增强，并且虽然数据湖和非规范化数据处理在业务使用的某些方面越来越受青睐，但大多数业务可能会受益于将其主数据以某种正常形式存储尽可能

加快更新速度（请参见下文）
使数据查询更容易
通常提供较小的数据空间
符合行业规范

我们的方法

我们的标准方法是将数据视为以三种不同的方式存储，并且在构建基于SQL Server的新系统时，我们尝试将其保留在不同的架构中。

这种方法已经与我们以前的客户合作过，我们甚至已将实质性的速度改进扩展到他们的系统提供商。

我们打算在适当的时候为每个部分添加一个单独的子条款，并添加一个部分来探讨围绕多个数据库之间的系统中立报告的概念。

索引总览

在关注SQL Server的同时，相同的原理也适用于许多不同的系统。索引的数量和类型可以独立提高或降低读写性能。

聚类

每个表只能有一个，这定义了如何将数据存储在磁盘上。

具有这种类型索引的表称为群集表，而没有索引的表称为堆。

非集群

您几乎可以将其视为引用每一行的单独表，但是在SQL Server中，实际存储取决于表类型（群集/堆）而改变

独特性

这两个索引都可以是唯一的，并且在正确使用时，可以为存储数据带来一些真正的增强。

复合索引

所有索引都可以使用一个或多个列，但是聚集索引必须小于900字节。

等等，主键呢？

当人们指的是“主键”时，他们经常谈论的是“唯一聚集索引”，并且相当多的人自动将其存储在基于整数的标识字段中的表上，每次输入新的标识字段时该字段都会加一创建记录，然后可以使用外键由另一个表引用该记录。

实际上，外键可以引用任何唯一索引，甚至可以引用多个列。

参考资料

该区域应包括所有顶级信息，例如帐户类型和付款类型，然后由链中更下方的另一个表引用。这样做的好处是可以使用单个更新来更改规范化数据库中的多行，而非规范化则需要更新每一行。

标准用法

通常，理想情况下，我们理想情况下将标识列用作唯一的聚集索引。我们将在下面创建四个表和一个模式。

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 type字段联接在一起，在这里，我们创建了一个聚集索引，该索引的运行方式与其他表不同。这是因为在大多数应用程序中，这将是一个读取密集的表，并且我们可以接受插入性能的最小增加。如果这是我们构建的应用程序，我们可能会以类似的方式将客户的联系方式分开。

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...)

交易数据

该区域包括票据，付款和订单之类的内容，并且通常指向业务和参考区域。

尽管唯一密钥可以很好地识别，但是在一般情况下，这可能不是您想要在磁盘上订购数据的方式，因为会影响读取时间。下面仅创建了一个表，但是它应该可以给您一个想法。

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)

加入和报告

在上面的虚构数据库中，我们试图尽可能接近地表示现实生活。这绝不是必须采取的方法，您最终将对使用上述信息的方式负责。

随着数据进入第三层，建立索引的重点已经转移到如何从应用程序或报告中读取数据，这将不可避免地涉及表之间的联接，以及可能包含在表中的任何点。 WHERE子句。