Efektywne indeksowanie baz danych

Efektywne indeksowanie bazy danych w znormalizowanej bazie danych będzie miało większy wpływ na wydajność niż zwykłe rzucanie dodatkowych zasobów w systemie

Co to jest znormalizowana baza danych?

Mówiąc prościej, normalizacja to proces strukturyzacji relacyjnych baz danych w sposób, który zmniejsza nadmiarowość danych poprzez dzielenie i łączenie danych w mniejsze fragmenty danych, które można aktualizować.

Ten artykuł koncentruje się przede wszystkim na bazach danych, które działają w znormalizowanej strukturze i obejmuje obszar, który większość ludzi zna (lub może sobie wyobrazić), czyli transakcje finansowe, klienci i kontakty.

Dlaczego znormalizowane?

Pewien poziom lub normalizacja może wnieść ogromną ilość ulepszeń do większości zbiorów danych, a chociaż jeziora danych i nienormalizowane przetwarzanie danych zyskują na znaczeniu w niektórych aspektach zastosowań biznesowych, większość firm prawdopodobnie skorzystałaby na przechowywaniu ich głównych danych w jakiejś normalnej formie jak to możliwe;

Przyspiesz aktualizacje (patrz poniżej)
Ułatw przeszukiwanie danych
Zwykle zapewnia mniejszy ślad danych
Zgodny z normami branżowymi

Nasze podejście

Nasze standardowe podejście polega na spojrzeniu na dane tak, jakby były przechowywane na trzy różne sposoby, a podczas tworzenia nowych systemów opartych na SQL Server staramy się trzymać je w różnych schematach.

To podejście sprawdziło się w przypadku naszych poprzednich klientów, a nawet wprowadziliśmy znaczne ulepszenia szybkości u ich dostawców systemów.

Zamierzamy w odpowiednim czasie dodać osobny podpunkt dla każdej sekcji oraz dodać sekcję, która pozwoli zbadać koncepcje dotyczące raportowania neutralnego dla systemu między wieloma bazami danych.

Przegląd indeksu

Chociaż skoncentrowano się na SQL Server, te same zasady dotyczą wielu różnych systemów. Liczba i typy indeksów mogą niezależnie poprawić lub zmniejszyć wydajność odczytu i zapisu.

Zgrupowane

Jesteś ograniczony do jednego na tabelę, a to definiuje sposób przechowywania danych na dysku.

Tabele z indeksem tego typu nazywane są tabelami klastrowymi, a te, które ich nie mają, nazywane są stertą.

Nieklastrowy

Można to prawie traktować jako oddzielną tabelę, która odwołuje się do każdego wiersza, jednak w SQL Server rzeczywisty magazyn zmienia się w zależności od typu tabeli (klastrowana / sterta)

Wyjątkowość

Oba te indeksy mogą być unikalne, a ich prawidłowe użycie może przynieść pewne ulepszenia w sposobie przechowywania danych.

Indeksy złożone

Wszystkie indeksy mogą używać jednej lub więcej kolumn, jednak indeks klastrowy musi mieć mniej niż 900 bajtów.

Poczekaj, a co z kluczem podstawowym?

Kiedy ludzie odnoszą się do „klucza podstawowego”, dość często mówią o „unikalnym indeksie klastrowym” i sporo osób automatycznie zapisuje go w tabeli w polu tożsamości opartym na liczbach całkowitych, które zwiększa się o jeden za każdym razem, gdy rekord, do którego może się odwoływać inna tabela przy użyciu klucza obcego.

Klucz obcy może w rzeczywistości odwoływać się do dowolnego unikalnego indeksu, a nawet do wielu kolumn.

Dane referencyjne

Ten obszar powinien zawierać wszystkie informacje najwyższego poziomu, takie jak typy kont i typy płatności, do których odwołuje się kolejna tabela w dalszej części łańcucha. Zaletą jest to, że pojedyncza aktualizacja może być używana do zmiany wielu wierszy w znormalizowanej bazie danych, podczas gdy nienormalizowana baza danych musiałaby aktualizować każdy wiersz.

Standardowe użycie

Ogólnie najlepiej byłoby używać kolumny tożsamości jako unikalnego indeksu klastrowego. Stworzymy cztery tabele i schemat poniżej.

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

Dane biznesowe

Ten średni poziom obszaru obejmowałby konta, klientów i kontakty lub inne obszary, do których może się odwoływać coś innego, a także informacje o typie.

Ten poziom jest zwykle najtrudniejszy do pracy przy podejmowaniu decyzji, gdzie umieścić główny indeks, ponieważ prawdopodobnie będzie to mieszanka różnych podejść.

Poniżej znajduje się tabela do tworzenia tabel adresów, klientów i kontaktów. W tym kodzie znajduje się dodatkowa (łącząca) tabela, która łączy pola typu Klient, Adres i Adres, a tutaj utworzyliśmy indeks klastrowy, który działa inaczej niż inne tabele. Dzieje się tak, ponieważ w większości aplikacji byłaby to tabela intensywnie odczytująca i możemy zaakceptować minimalny wzrost wydajności wstawiania. Gdyby to była zbudowana przez nas aplikacja, prawdopodobnie w podobny sposób wyodrębnilibyśmy dane kontaktowe klientów.

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

Dane transakcyjne

Ten obszar obejmuje takie rzeczy, jak notatki, płatności i zamówienia, i ogólnie wskazuje zarówno obszar biznesowy, jak i referencyjny.

Chociaż unikalne klucze są dobre do identyfikacji, w ogólnym zastosowaniu prawdopodobnie nie jest to sposób, w jaki chcesz porządkować dane na dysku, ponieważ wpłynie to na czas odczytu. Poniżej utworzono tylko jedną tabelę, ale powinna dać ci pomysł.

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)

Połączenia i raportowanie

W powyższej fikcyjnej bazie danych staraliśmy się jak najdokładniej przedstawić prawdziwe życie. W żadnym wypadku nie jest to podejście, które należy przyjąć, a to Ty ponosisz ostateczną odpowiedzialność za sposób wykorzystania powyższych informacji.

Ponieważ dane przeszły na trzecią warstwę, nacisk na indeksowanie został przeniesiony na sposób, w jaki dane będą odczytywane z aplikacji lub raportu, co niezmiennie wiązałoby się z połączeniami między tabelami i wszystkimi punktami, które mogłyby lub byłyby uwzględnione w Klauzule WHERE.