Indexation efficace de la base de données

L'indexation efficace de la base de données dans une base de données normalisée aura plus d'impact sur les performances que le simple fait de jeter des ressources supplémentaires sur le système

Qu'est-ce qu'une base de données normalisée?

En termes simples, la normalisation est le processus de structuration des bases de données relationnelles de manière à réduire la redondance des données en décomposant et en reliant les données en de plus petits morceaux de données pouvant être mises à jour.

Cet article se concentre principalement sur les bases de données qui fonctionnent dans une structure normalisée et explorera un domaine que la plupart des gens connaissent (ou peuvent imaginer) que sont les transactions financières, les clients et les contacts.

Pourquoi normalisé?

Un certain niveau ou une normalisation peut apporter d'énormes améliorations à la plupart des ensembles de données, et alors que les lacs de données et le traitement de données non normalisé gagnent du terrain dans certains aspects de l'utilisation commerciale, la plupart des entreprises gagneraient probablement à stocker leurs données principales sous une forme normale. comme il peut;

Accélérez les mises à jour (voir ci-dessous)
Facilitez l'interrogation des données
Fournit normalement une plus petite empreinte de données
Conforme aux normes de l'industrie

Notre approche

Notre approche standard consiste à examiner les données comme si elles étaient stockées de trois manières différentes, et lors de la création de nouveaux systèmes basés sur SQL Server, nous essayons de les conserver dans des schémas différents.

Cette approche a fonctionné avec nos anciens clients, et nous avons même étendu des améliorations de vitesse substantielles à leurs fournisseurs de systèmes.

Nous allons viser à ajouter un sous-article distinct pour chaque section en temps voulu, et ajouter une section pour explorer les concepts autour des rapports neutres du système entre plusieurs bases de données.

Aperçu de l'index

Bien que SQL Server se concentre, les mêmes principes s'appliquent à de nombreux systèmes différents. Le nombre et les types d'index peuvent améliorer ou réduire les performances de lecture et d'écriture indépendamment.

Clustered

Vous êtes limité à un par table, ce qui définit la manière dont les données sont stockées sur le disque.

Les tables qui ont un index de ce type sont appelées une table en cluster, et celles qui n'en ont pas sont appelées un tas.

Non groupé

Vous pouvez presque considérer cela comme une table séparée qui fait référence à chaque ligne, mais dans SQL Server, le stockage réel change en fonction du type de table (en cluster / tas)

Unicité

Ces deux index peuvent être uniques et, lorsqu'ils sont utilisés correctement, cela peut apporter de réelles améliorations à la façon dont vous stockez vos données.

Index composés

Tous les index peuvent utiliser une ou plusieurs colonnes, mais un index clusterisé doit être inférieur à 900 octets.

Attendez, qu'en est-il de la clé primaire?

Lorsque les gens font référence à une "clé primaire", ils parlent assez souvent d'un "index clusterisé unique", et de nombreuses personnes le stockent automatiquement sur une table dans un champ d'identité basé sur un entier qui augmente de un à chaque fois qu'un nouveau enregistrement est créé, il peut ensuite être référencé par une autre table à l'aide d'une clé étrangère.

Une clé étrangère peut en fait référencer n'importe quel index unique, et même référencer plusieurs colonnes.

Donnée de référence

Cette zone doit inclure toutes les informations de niveau supérieur, des éléments tels que les types de compte et les types de paiement qui sont ensuite référencés par un autre tableau plus bas dans la chaîne. L'avantage ici est qu'une seule mise à jour peut être utilisée pour modifier plusieurs lignes dans une base de données normalisée, tandis qu'une mise à jour non normalisée aurait besoin de mettre à jour chaque ligne.

Utilisation standard

En général, nous utilisons idéalement une colonne d'identité comme un index clusterisé unique. Nous allons créer quatre tableaux et un schéma ci-dessous.

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

Données d'entreprise

Ce niveau intermédiaire de zone inclurait les comptes, les clients et les contacts ou d'autres zones qui peuvent être référencées par autre chose, et ferait également référence aux informations de type.

Ce niveau est normalement le plus difficile à utiliser pour décider où placer votre index principal, car il s'agira probablement d'un mélange d'approches différentes.

Vous trouverez ci-dessous le tableau pour créer des tables d'adresse, de client et de contact. Dans ce code, il existe une table supplémentaire (de jointure) qui joint les champs de type Client, Adresse et Adresse, et ici, nous avons créé un index cluster qui s'exécute différemment des autres tables. En effet, dans la plupart des applications, ce serait une table intensive en lecture, et nous pouvons accepter une augmentation minimale des performances d'insertion. S'il s'agissait d'une application créée par nous, nous séparerions probablement les coordonnées des clients de la même manière.

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

Données transactionnelles

Cette zone comprend des éléments tels que les notes, les paiements et les commandes, et pointe généralement à la fois vers les domaines d'activité et de référence.

Bien que les clés uniques soient bonnes pour l'identification, en général, ce n'est probablement pas la façon dont vous voulez classer les données sur le disque, car les temps de lecture seraient affectés. Il n'y a qu'un seul tableau créé ci-dessous, mais cela devrait vous donner une idée.

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)

Jointures et rapports

Dans la base de données fictive ci-dessus, nous avons essayé de représenter la vie réelle aussi fidèlement que possible. Il ne s'agit en aucun cas d'une approche à adopter, et vous êtes en dernier lieu responsable de la manière dont vous utilisez les informations ci-dessus.

Au fur et à mesure que les données sont entrées dans le troisième niveau, le focus de l'indexation a été déplacé vers la façon dont les données seraient lues à partir d'une application ou d'un rapport, ce qui impliquerait invariablement les jointures entre les tables et tous les points qui pourraient ou seraient inclus dans le Clauses WHERE.