Indexação Eficaz de Banco de Dados
O que é um banco de dados normalizado?
Em termos leigos, a normalização é o processo de estruturar bancos de dados relacionais de uma forma que reduza a redundância de dados separando e vinculando dados em pedaços menores de dados atualizáveis.
Este artigo se concentra principalmente em bancos de dados que funcionam em uma estrutura normalizada e explorará uma área com a qual a maioria das pessoas está familiarizada (ou pode imaginar) que é transações financeiras, clientes e contatos.
Por que normalizado?
Algum nível ou normalização pode trazer uma grande quantidade de aprimoramento para a maioria dos conjuntos de dados, e enquanto lagos de dados e processamento de dados não normalizados estão ganhando força em alguns aspectos do uso comercial, a maioria das empresas provavelmente se beneficiaria em ter seus dados principais armazenados em algum tipo de forma normal como pode;
- Acelere as atualizações (veja abaixo)
- Facilite a interrogação de dados
- Normalmente fornece uma pegada de dados menor
- Está em conformidade com as normas da indústria
Nossa abordagem
Nossa abordagem padrão é ver os dados como se estivessem armazenados de três maneiras diferentes e, ao construir novos sistemas baseados em SQL Server, tentamos mantê-los em esquemas diferentes.
Essa abordagem funcionou com clientes anteriores nossos, e até estendemos melhorias substanciais de velocidade para seus fornecedores de sistema.
Nosso objetivo é adicionar um subartigo separado para cada seção no devido tempo, e adicionar uma seção para explorar os conceitos em torno do sistema de relatórios neutro entre vários bancos de dados.
Visão geral do índice
Embora o SQL Server se concentre, os mesmos princípios se aplicam a muitos sistemas diferentes. O número e os tipos de índices podem melhorar ou reduzir o desempenho de leitura e gravação de forma independente.
Aglomerado
Você está limitado a um por tabela e isso define como os dados são armazenados no disco.
As tabelas que têm um índice desse tipo são chamadas de Tabela agrupada e as que não têm são chamadas de Heap.
Não agrupado
Você quase pode pensar nisso como uma tabela separada que faz referência a cada linha, no entanto, no SQL Server, as mudanças reais de armazenamento dependem do tipo de tabela (cluster / heap)
Singularidade
Ambos os índices podem ser únicos e, quando usados corretamente, podem trazer algumas melhorias reais em como você armazena seus dados.
Índices Compostos
Todos os índices podem usar uma ou mais colunas, no entanto, um índice clusterizado deve ter menos de 900 bytes.
Espere um pouco, e a chave primária?
Quando as pessoas estão se referindo a uma "chave primária", muitas vezes estão falando sobre um "Índice agrupado único", e algumas pessoas armazenam isso automaticamente em uma tabela dentro de um campo de identidade baseado em inteiro que sobe um a cada vez que um novo registro é criado, ele pode então ser referenciado por outra tabela usando uma chave estrangeira.
Uma chave estrangeira pode de fato fazer referência a qualquer índice exclusivo e até mesmo fazer referência a várias colunas.
Data de referência
Esta área deve incluir todas as informações de nível superior, coisas como Tipos de conta e Tipos de pagamento que são então referenciados por outra tabela mais abaixo na cadeia. O benefício aqui é que uma única atualização pode ser usada para alterar várias linhas em um banco de dados normalizado, enquanto o não normalizado precisaria atualizar todas as linhas.
Uso Padrão
Em geral, o ideal é usar uma coluna de identidade como um índice clusterizado exclusivo. Vamos criar quatro tabelas e um esquema a seguir.
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))
Dados de Negócios
Este nível médio de área incluiria contas, clientes e contatos ou outras áreas que podem ser referenciadas por outra coisa e também referencia as informações de tipo.
Normalmente, esse nível é o mais difícil de trabalhar em termos de decidir onde colocar seu índice principal, pois provavelmente será uma mistura de abordagens diferentes.
Abaixo está a tabela para criar as tabelas de Endereço, Cliente e Contato. Nesse código, há uma tabela adicional (junta) que junta os campos Cliente, Endereço e Tipo de endereço, e aqui criamos um índice clusterizado que é executado de maneira diferente das outras tabelas. Isso ocorre porque, na maioria dos aplicativos, esta seria uma tabela de leitura intensiva e podemos aceitar um aumento mínimo para inserir o desempenho. Se este fosse um aplicativo desenvolvido por nós, provavelmente separaríamos os detalhes de contato do cliente de maneira semelhante.
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...)
Dados Transacionais
Esta área inclui coisas como notas, pagamentos e pedidos e geralmente aponta para as áreas de negócios e de referência.
Embora as chaves exclusivas sejam boas para identificação, no uso geral provavelmente não é como você deseja ordenar os dados no disco, pois os tempos de leitura seriam afetados. Existe apenas uma tabela criada abaixo, mas ela deve dar uma ideia.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)
Associações e relatórios
No banco de dados fictício acima, tentamos representar a vida real o mais próximo possível. Esta não é de forma alguma uma abordagem que deve ser tomada, e você é o responsável final por como usar as informações acima.
Como os dados foram para a terceira camada, o foco da indexação foi movido para como os dados seriam lidos de um aplicativo ou relatório, e isso invariavelmente envolveria as junções entre as tabelas e quaisquer pontos que poderiam ou seriam incluídos no Cláusulas WHERE.