Effektive Datenbankindizierung

Eine effektive Datenbankindizierung in einer normalisierten Datenbank wirkt sich stärker auf die Leistung aus, als einfach zusätzliche Ressourcen auf das System zu werfen

Was ist eine normalisierte Datenbank?

Einfach ausgedrückt ist Normalisierung der Prozess, relationale Datenbanken so zu strukturieren, dass die Datenredundanz reduziert wird, indem Daten in kleinere Blöcke aktualisierbarer Daten aufgeteilt und verknüpft werden.

Dieser Artikel konzentriert sich hauptsächlich auf Datenbanken, die in einer normalisierten Struktur arbeiten, und untersucht einen Bereich, mit dem die meisten Menschen vertraut sind (oder sich vorstellen können), nämlich Finanztransaktionen, Kunden und Kontakte.

Warum normalisiert?

Eine gewisse Ebene oder Normalisierung kann die meisten Datensätze erheblich verbessern, und während Data Lakes und nicht normalisierte Datenverarbeitung in einigen Aspekten der geschäftlichen Nutzung an Bedeutung gewinnen, würden die meisten Unternehmen wahrscheinlich davon profitieren, wenn ihre Hauptdaten in einer Art normaler Form gespeichert würden wie es kann;

Updates beschleunigen (siehe unten)
Vereinfachen Sie die Datenabfrage
Bietet normalerweise einen geringeren Datenbedarf
Entspricht den Industrienormen

Unser Vorgehen

Unser Standardansatz besteht darin, Daten so zu betrachten, als ob sie auf drei verschiedene Arten gespeichert würden, und beim Aufbau neuer SQL Server-basierter Systeme versuchen wir, sie in verschiedenen Schemas zu halten.

Dieser Ansatz hat bei früheren Kunden von uns funktioniert, und wir haben sogar erhebliche Geschwindigkeitsverbesserungen auf ihre Systemanbieter ausgedehnt.

Wir werden zu gegebener Zeit einen separaten Unterartikel für jeden Abschnitt hinzufügen und einen Abschnitt hinzufügen, um Konzepte rund um systemneutrale Berichterstattung zwischen mehreren Datenbanken zu untersuchen.

Indexübersicht

Während SQL Server sich auf SQL Server konzentriert, gelten die gleichen Prinzipien für viele verschiedene Systeme. Die Anzahl und Typen von Indizes können unabhängig voneinander die Lese- und Schreibleistung verbessern oder verringern.

Geclustert

Sie sind auf eine pro Tabelle beschränkt, und dies definiert, wie die Daten auf der Festplatte gespeichert werden.

Tabellen, die einen Index dieses Typs haben, werden Clustered Table genannt, und Tabellen, die keinen Index haben, werden als Heap bezeichnet.

Nicht geclustert

Sie können sich das fast als separate Tabelle vorstellen, die auf jede Zeile verweist, aber in SQL Server ändert sich der tatsächliche Speicher je nach Tabellentyp (Cluster/Heap).

Einzigartigkeit

Beide Indizes können eindeutig sein, und wenn sie richtig verwendet werden, kann dies einige echte Verbesserungen bei der Speicherung Ihrer Daten bringen.

Zusammengesetzte Indizes

Alle Indizes können eine oder mehrere Spalten verwenden, ein gruppierter Index muss jedoch weniger als 900 Bytes umfassen.

Warte mal, was ist mit dem Primärschlüssel?

Wenn sich Leute auf einen "Primärschlüssel" beziehen, sprechen sie ziemlich oft von einem "Unique Clustered Index", und nicht wenige Leute speichern diesen automatisch in einer Tabelle innerhalb eines ganzzahlbasierten Identitätsfelds, das jedes Mal um eins erhöht wird Datensatz erstellt wird, kann dieser dann von einer anderen Tabelle über einen Fremdschlüssel referenziert werden.

Ein Fremdschlüssel kann tatsächlich auf jeden eindeutigen Index und sogar auf mehrere Spalten verweisen.

Vergleichsdaten

Dieser Bereich sollte alle Informationen der obersten Ebene enthalten, Dinge wie Kontotypen und Zahlungstypen, auf die dann von einer anderen Tabelle weiter unten in der Kette verwiesen wird. Der Vorteil hier ist, dass eine einzelne Aktualisierung verwendet werden kann, um mehrere Zeilen in einer normalisierten Datenbank zu ändern, während eine nicht normalisierte Datenbank jede Zeile aktualisieren müsste.

Standardnutzung

Im Allgemeinen verwenden wir idealerweise eine Identitätsspalte als eindeutigen gruppierten Index. Wir werden unten vier Tabellen und ein Schema erstellen.

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

Geschäftsdaten

Diese mittlere Bereichsebene würde Konten, Kunden und Kontakte oder andere Bereiche umfassen, auf die durch etwas anderes verwiesen werden kann, und auch auf die Typinformationen verweisen.

Mit dieser Ebene ist es normalerweise am schwierigsten zu entscheiden, wo Sie Ihren Hauptindex platzieren, da es wahrscheinlich eine Mischung aus verschiedenen Ansätzen sein wird.

Nachfolgend finden Sie die Tabelle zum Erstellen von Adress-, Kunden- und Kontakttabellen. In diesem Code gibt es eine zusätzliche (Joining-)Tabelle, die Client-, Adress- und Adresstypfelder verbindet, und hier haben wir einen gruppierten Index erstellt, der anders als die anderen Tabellen ausgeführt wird. Dies liegt daran, dass dies in den meisten Anwendungen eine leseintensive Tabelle wäre und wir eine minimale Steigerung der Einfügeleistung akzeptieren können. Wenn dies eine von uns erstellte Anwendung wäre, würden wir wahrscheinlich die Kontaktdaten der Kunden auf ähnliche Weise herausfiltern.

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

Transaktionsdaten

Dieser Bereich umfasst Dinge wie Notizen, Zahlungen und Bestellungen und weist im Allgemeinen sowohl auf den Geschäfts- als auch auf den Referenzbereich hin.

Während eindeutige Schlüssel zur Identifizierung gut sind, ist es im allgemeinen Gebrauch wahrscheinlich nicht so, wie Sie die Daten auf der Festplatte ordnen möchten, da die Lesezeiten beeinträchtigt würden. Unten wird nur eine Tabelle erstellt, aber sie sollte Ihnen eine Vorstellung geben.

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)

Beitritte und Berichterstattung

In der obigen fiktiven Datenbank haben wir versucht, das wirkliche Leben so genau wie möglich darzustellen. Dies ist keineswegs ein zwingender Ansatz, und Sie sind letztendlich dafür verantwortlich, wie Sie die oben genannten Informationen verwenden.

Da die Daten in die dritte Schicht übergegangen sind, hat sich der Schwerpunkt der Indizierung darauf verlagert, wie die Daten aus einer Anwendung oder einem Bericht gelesen werden, und dies würde ausnahmslos die Verknüpfungen zwischen Tabellen und alle Punkte beinhalten, die in der enthalten sein könnten oder würden WHERE-Klauseln.

Weiterführende Lektüre

Indizes neu erstellen oder reorganisieren