Efektivní indexování databáze

Efektivní indexování databáze v normalizované databázi bude mít větší dopad na výkon než pouhé házení dalších zdrojů do systému

Co je normalizovaná databáze?

Laicky řečeno, normalizace je proces strukturování relačních databází způsobem, který omezuje redundanci dat tím, že je rozděluje a propojuje data na menší kousky aktualizovatelných dat.

Tento článek se zaměřuje především na databáze, které fungují v normalizované struktuře, a prozkoumá oblast, kterou většina lidí zná (nebo si dokáže představit), což jsou finanční transakce, klienti a kontakty.

Proč normalizováno?

Některá úroveň nebo normalizace může přinést obrovské množství vylepšení většiny datových souborů, a zatímco v některých aspektech obchodního využití získávají na síle datová jezera a nenormalizované zpracování dat, většině podniků by pravděpodobně prospělo, kdyby jejich hlavní data byla uložena v nějaké normální formě jak může;

Urychlete aktualizace (viz níže)
Usnadněte dotazování dat
Normálně poskytuje menší datovou stopu
Odpovídá průmyslovým normám

Náš přístup

Náš standardní přístup je dívat se na data, jako by byla uložena třemi různými způsoby, a při vytváření nových systémů založených na serveru SQL Server se je snažíme uchovávat v různých schématech.

Tento přístup fungoval u předchozích našich klientů a dokonce jsme rozšířili podstatná vylepšení rychlosti na jejich systémové poskytovatele.

Budeme se snažit v pravý čas přidat samostatný dílčí článek pro každou sekci a přidat sekci, která prozkoumá koncepty systémově neutrálního reportingu mezi více databázemi.

Přehled rejstříku

Zatímco se SQL Server soustředil, stejné principy platí pro mnoho různých systémů. Počet a typy indexů mohou nezávisle na sobě zlepšit nebo snížit výkon při čtení a zápisu.

Klastrovaný

Jste omezeni na jednoho na tabulku, což definuje, jak jsou data uložena na disk.

Tabulky, které mají index tohoto typu, se nazývají klastrovaná tabulka a ty, které nemají, se označují jako halda.

Neskupené

Můžete si to téměř představit jako samostatnou tabulku, která odkazuje na každý řádek, ale na serveru SQL Server se skutečné úložiště mění v závislosti na typu tabulky (seskupeno / halda)

Jedinečnost

Oba tyto indexy mohou být jedinečné a při správném použití to může přinést některá skutečná vylepšení způsobu ukládání dat.

Složené indexy

Všechny indexy mohou používat jeden nebo více sloupců, avšak seskupený index musí být menší než 900 bajtů.

Počkej, co primární klíč?

Když lidé odkazují na „primární klíč“, často mluví o „jedinečném klastrovaném indexu“ a docela málo lidí to automaticky uloží na stůl v poli identity založeném na celých číslech, které se zvýší vždy o jeden nový když je vytvořen záznam, může na něj potom odkazovat jiná tabulka pomocí cizího klíče.

Cizí klíč může ve skutečnosti odkazovat na jakýkoli jedinečný index a dokonce odkazovat na více sloupců.

Referenční údaje

Tato oblast by měla zahrnovat všechny informace na nejvyšší úrovni, například typy účtů a typy plateb, na které pak odkazuje další tabulka dále v řetězci. Výhodou je, že jednu aktualizaci lze použít ke změně více řádků v normalizované databázi, zatímco u normalizovaných bude potřeba aktualizovat každý řádek.

Standardní použití

Obecně platí, že v ideálním případě použijeme sloupec identity jako jedinečný seskupený index. Níže vytvoříme čtyři tabulky a schéma.

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

Obchodní údaje

Tato střední úroveň oblasti by zahrnovala účty, klienty a kontakty nebo jiné oblasti, na které může odkazovat něco jiného, a také odkazovala na informace o typu.

S touto úrovní je obvykle nejtěžší pracovat, pokud jde o rozhodování o tom, kam umístit hlavní index, protože to bude pravděpodobně kombinace různých přístupů.

Níže je tabulka pro vytvoření tabulek adres, klientů a kontaktů. V tomto kódu je další (spojovací) tabulka, která spojuje pole Klient, Adresa a Typ adresy, a zde jsme vytvořili seskupený index, který běží odlišně od ostatních tabulek. Důvodem je, že ve většině aplikací by se jednalo o tabulku náročnou na čtení, a abychom mohli vložit výkon, můžeme přijmout minimální zvýšení. Pokud by se jednalo o naši aplikaci, pravděpodobně bychom oddělili kontaktní údaje klientů podobným způsobem.

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

Transakční data

Tato oblast zahrnuje věci jako poznámky, platby a objednávky a obecně ukazuje na obchodní i referenční oblast.

Zatímco jedinečné klíče jsou dobré pro identifikaci, obecně se pravděpodobně nejedná o to, jak chcete objednat data na disku, protože by to ovlivnilo časy čtení. Níže je vytvořena pouze jedna tabulka, ale měla by vám poskytnout představu.

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)

Připojení a hlášení

Ve výše uvedené fiktivní databázi jsme se snažili co nejpřesněji představit skutečný život. V žádném případě nejde o přístup, který je třeba zaujmout, a vy jste v konečném důsledku odpovědní za to, jak použijete výše uvedené informace.

Vzhledem k tomu, že data přešla do třetí vrstvy, zaměření indexování bylo přesunuto na to, jak budou data čtena z aplikace nebo sestavy, což by vždy zahrnovalo spojení mezi tabulkami a všechny body, které by mohly nebo by byly zahrnuty do KDE klauzule.