--Indeks je struktura na disku koja je pridružena tabeli ili prikazu i ubrzava dohvat redova iz tabele ili pogleda. Indeks sadrži ključeve izgrađene od jedne ili više kolona u tabeli ili prikazu. Ovi ključevi su pohranjeni u uravnoteženoj strukturi stabla koja podržava brzo pretraživanje redova prema vrijednostima njihovih ključeva u SQL Serveru.

-- Klasterisani indeksi sortiraju i pohranjuju redove podataka u tabelama ili pogledima na osnovu njihovih ključnih vrijednosti. Ove vrijednosti su stupci uključeni u definiciju indeksa. Postoji samo jedan grupirani indeks po tabeli jer se redovi podataka mogu sortirati samo u jednom redosledu.
--Redovi podataka u tabeli se pohranjuju u sortiranom redosledu samo ako tabela sadrži grupirani indeks. Ako tabela ima klasterizovani indeks, tada se tabela naziva klasterizovana. Ako tabela nema grupirani indeks, redovi podataka se pohranjuju u neuređenoj strukturi koja se naziva hrpa.

--Neklasterovani indeks ima potpuno istu strukturu kao klasterizovani indeks, ali sa dve važne razlike:
--neklasterirani indeks ne mijenja fizički redoslijed redova u tabeli, a lisne stranice u neklasteriranom indeksu sastoje se od indeksnih ključeva i oznaka.

-- Klasterisani indeksi obezbeđuju brže pronalaženje podataka od neklasterizovanih indeksa. Obično se ispostavi da su brži i kada se ažuriraju, ali ne i kada se mnoga ažuriranja dešavaju na istom mjestu usred odnosa.

--Iz nekog razloga, grupirani indeks ima tendenciju da radi brže od neklasterizovanog indeksa. Kada sistem skenira klasterizovani indeks, nema potrebe da napuštate strukturu B-stabla za skeniranje stranica podataka jer su takve stranice već prisutne na nivou lista stabla.

--Neklasterirani indeks također zahtijeva više I/O operacija nego odgovarajući klasterirani indeks.

--Neklasterirani indeks mora pročitati stranice podataka nakon skeniranja B-stabla ili, ako postoji grupirani indeks na drugom stupcu(ima) tablice, neklasterirani indeks mora pročitati strukturu B-stabla klasteriziranog indeksa .

-- Dakle, grupirani indeks će biti znatno brži od skeniranja tablice, čak i ako je njegova selektivnost prilično loša (upit vraća puno redova)

CREATE TABLE tsql.dbo.NI
ID int NOT NULL,
T char(8) NULL
);

CREATE TABLE tsql.dbo.NCI
ID int NOT NULL,
T char(8) NULL
);

--Kreirajte grupirani indeks

KREIRAJ GLASTERIRANI INDEKS IX_1
ON tsql.dbo.NCI(ID);

--Kreirajte neklasterirani indeks na tabeli

KREIRAJ NEKLASTERIRAN INDEKS IX_2
ON tsql.dbo.NCI(T);

--Dodajte testne podatke
DECLARE @i INT = 100000;
DECLARE @t CHAR(1) = "T";

DOK @i > 0
POČNI
umetnite u tsql.dbo.NI vrijednosti(@i, @t + CAST(@i AS char(6)));
umetnite u tsql.dbo.NCI vrijednosti(@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
KRAJ

--Upita na tabeli sa indeksima
SELECT ID, T FROM tsql.dbo.NCI
NARUČI PO ID-u, T

SELECT ID, COUNT(*) AS C FROM tsql.dbo.NCI
GRUPA PO ID-u, T

SELECT ID, T FROM tsql.dbo.NCI
GDJE ID > 4000 I ID< 55000 AND T LIKE "T%"

--Upit koristeći oba indeksa
USE tsql;
ODABIR CAST(dbo.NCI.ID KAO VARCHAR)
IZ dbo.NCI
GROUP BY dbo.NCI.ID
UNION ALL
ODABIR dbo.NCI.T
IZ dbo.NCI
GROUP BY dbo.NCI.T

--Informacije o indeksima
SELECT index_type_desc, index_depth, index_level,
page_count, record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL, "DETAILED");

--Brisanje indeksa
AKO POSTOJI (IZABIR ime IZ sys.indexes
WHERE naziv = N"IX_1")
DROP INDEX IX_1 NA tsql.dbo.NCI;

AKO POSTOJI (IZABIR ime IZ sys.indexes
GDJE naziv = N"IX_2")
DROP INDEX IX_2 NA tsql.dbo.NCI;

U prethodnom članku predstavili smo načine za optimizaciju relacijskih baza podataka i razgovarali o tome kako klasterirani i neklasterizirani indeksi rade u kontekstu optimizacije vremena izvršavanja upita baze podataka. Sada je vrijeme da ovo znanje primijenite u praksu tako što ćete naučiti kako kreirati indekse optimizacije za MS SQL bazu podataka.

Dozvolite mi da vas podsjetim na definiciju sheme tablice Staffs sa kojom ćemo raditi:

Stol za osoblje

Recimo da treba da kreiramo negrupisani indeks za tabelu Staffs, koji će optimizovati sledeći upit:

ODABERITE ID, ime, posao OD stvari GDJE PLATA > 1000 I slika NIJE NULL

Ključ indeksa će biti kolone PLATA i Fotografija, budući da je odabir filtriran prema ovim poljima. I kolone Id, Name i Job bit će kolone uključene u indeks.

Opća sintaksa komande je sljedeća:

KORISTI GO

KREIRAJTE NEKLASTERIRAN INDEKS ON (ASC -- kolone ključeva indeksa)

UKLJUČITI ( -- uključene kolone) GO

U našem slučaju, zahtjev će izgledati ovako:

(Plata, slika) UKLJUČUJE (ID, ime, posao) GO

Napravili smo negrupisani indeks. Ili bolje rečeno, negrupisani indeks pokrivanja. To znači da indeks sadrži sva polja potrebna za izvršavanje upita i SQL Server neće pristupiti osnovnoj tabeli prilikom izvršavanja upita.

Kad bi naš kod bio ovakav:

CREATE NENCLUSTERED INDEX IDX_StaffsSearch NA Stuffs

(Plata, slika) UKLJUČUJE (Id) GO

U ovom slučaju, indeks prestaje biti indeks pokrivanja, jer ne uključuje sve kolone korištene u upitu. Optimizator će i dalje koristiti ovaj indeks prilikom izvršavanja upita, ali će njegova efikasnost biti smanjena za red veličine jer će zahtijevati pristup osnovnoj tabeli.

Grupirani indeks se kreira pomoću sljedeće naredbe:

CREATE CLUSTERED INDEX IDX_Stsffsid ON Stuffs (Id)

Ovdje je kreiran jedinstveni klasterirani indeks na osnovu primarnog ključa tabele (Id kolona).

Pravi primjer

Hajde da sada razvijemo scenario u kojem možemo realno proceniti stepen povećanja performansi u slučaju korišćenja indeksa.

Kreirajmo novu bazu podataka:

CREATE DATABASE TestDB;

I jednu tabelu kupaca, koja će se sastojati od četiri kolone:

KREIRAJ TABELU .(

NOT NULL, NULL, NULL, NULL) GO

Sada popunimo našu tabelu slučajnim podacima. Stupac Id će se povećavati u petlji, a preostale tri kolone tablice će biti popunjene slučajnim brojevima koristeći osebujnu verziju slučajne funkcije:

DECLARE @i int = 0;

DOK (@i< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(kontrolna zbir(novi())), abs(kontrolna suma(novi())), abs(kontrolna zbir(novi())) SET @i = @i + 1; KRAJ

Ova skripta dodaje pola miliona zapisa u tabelu, pa budite strpljivi, skripta će raditi najmanje 3 minuta.

Sve je spremno za test. Procijenit ćemo karakteristike performansi upita. Budući da vrijeme izvršenja upita može ovisiti o specifičnoj mašini, analiziraćemo nezavisniji indikator - broj logičkih čitanja.

Da biste omogućili način prikupljanja statistike, morate pokrenuti sljedeću naredbu:

Sada, nakon izvršenja svakog zahtjeva, na kartici Poruke imat ćemo pristup statistici o izvršenju ovog zahtjeva, kao što je prikazano u nastavku:

Zanima nas samo vrijednost parametra logičkog čitanja.

Dakle, u našoj tabeli još nema indeksa. Pokrenimo sljedeća tri upita i zabilježimo broj logičkih čitanja za svaki upit u tabeli rezultata ispod:

1) ODABERITE Id, Num1, Num2 OD kupaca GDJE Id = 2000

2) ODABERITE Id, Num1, Num2 OD kupaca GDJE Id >= 0 I Id< 1000

3) ODABERITE Id, Num1, Num2 OD kupaca GDJE Id >= 0 I Id< 5000

Ovi upiti će vratiti 1 red, 1000 redova i 5000 redova, respektivno. Bez indeksa, indikator učinka (broj logičkih čitanja) za sve upite je isti i jednak je 1621. Unesimo podatke u tabelu rezultata:

Vidimo da za drugi i treći upit, kada se vrati prilično veliki broj redova, indeks koji smo kreirali nije poboljšao performanse. Međutim, za upit koji vraća jedan red, ubrzanje je bilo ogromno. Dakle, možemo zaključiti da ima smisla kreirati indekse koji ne pokrivaju prilikom optimizacije upita koji vraćaju jedan rezultat.

Sada kreirajmo indeks pokrivanja, čime ćemo postići maksimalne performanse.

Prvo, izbrišemo prethodni indeks:

KORISTITE TestDB GO DROP INDEX Customers.TestIndex1

I napravimo novi indeks:

CREATE NENCLUSTERED INDEX TestIndex2 NA dbo.Customers(Id) INCLUDE (Num1, Num2);

Sada pokrenimo naše upite treći put i zapišemo rezultate u tabelu:

Nema indeksa
Indeks koji ne pokriva
Indeks pokrivanja

Lako je uočiti da je povećanje performansi bilo ogromno. Time smo povećali brzinu izvršavanja upita desetine puta. Prilikom pokretanja baze podataka koja pohranjuje milione redova, ovo povećanje performansi će biti prilično primjetno.

U ovom članku smo pogledali primjer optimizacije baze podataka kreiranjem indeksa. Vrijedi napomenuti da je kreiranje indeksa čisto individualan proces za svaki zahtjev. Da biste izgradili indeks koji će zaista optimizirati performanse upita, morate pažljivo analizirati sam upit i njegov plan izvršenja.

Efikasna izgradnja indeksa jedan je od najboljih načina za poboljšanje performansi aplikacije baze podataka. Bez upotrebe indeksa, SQL Server je poput čitača koji pokušava pronaći riječ u knjizi gledajući svaku stranicu. Ako knjiga ima predmetni indeks (indeks), čitalac može mnogo brže tražiti potrebne informacije.

U nedostatku indeksa, SQL server, prilikom preuzimanja podataka iz tablice, skenirat će cijelu tablicu i provjeriti svaki red da vidi da li su kriteriji upita ispunjeni. Ovakvo potpuno skeniranje može biti pogubno za performanse cijelog sistema, posebno ako ima puno podataka u tabelama.

Jedan od najvažnijih zadataka pri radu sa bazom podataka je izgradnja optimalnog indeksa za poboljšanje performansi sistema. Većina velikih baza podataka pruža alate za pregled plana izvršenja upita i pomaže vam da podesite i optimizirate indekse. Ovaj članak ističe nekoliko dobrih pravila koja se primjenjuju pri kreiranju ili modificiranju indeksa u bazi podataka. Prvo, pogledajmo situacije u kojima indeksiranje poboljšava performanse i gdje indeksiranje može naštetiti.

Korisni indeksi

Dakle, indeksiranje tabele će biti korisno kada tražite određeni zapis u tabeli pomoću naredbe Where. Takvi upiti uključuju, na primjer, upite koji traže raspon vrijednosti, upite koji odgovaraju točnoj vrijednosti određenoj vrijednosti i upite koji spajaju dvije tablice.

Na primjer, sljedeći upiti prema bazi podataka Northwind radit će efikasnije kada se gradi indeks na stupcu UnitPrice.

Izbriši iz proizvoda gdje je jedinična cijena=1
Odaberite * od proizvoda Gdje Jedinična cijena između 14 I 16

Budući da se stavke indeksa pohranjuju sortirane, indeksiranje je također korisno kada se pravi upit pomoću klauzule Order by. Bez indeksa, zapisi se učitavaju i sortiraju dok je upit pokrenut. Indeks zasnovan na jediničnoj cijeni će vam omogućiti da jednostavno skenirate indeks i dohvatite redove prema referenci prilikom obrade sljedećeg zahtjeva. Ako želite sortirati redove u opadajućem redoslijedu, možete jednostavno skenirati indeks obrnutim redoslijedom.

Odaberite * Od proizvoda poredajte po jediničnoj cijeni ASC

Grupiranje zapisa pomoću naredbe Grupiraj po također često zahtijeva sortiranje, tako da će izgradnja indeksa na stupcu UnitPrice također biti korisna za sljedeći upit koji broji broj jedinica proizvoda po svakoj određenoj cijeni

Odaberite broj(*), jediničnu cijenu iz grupe proizvoda po jediničnoj cijeni

Indeksi su korisni za održavanje jedinstvene vrijednosti za kolonu, budući da DBMS može lako pogledati indeks da vidi da li vrijednost već postoji. Iz tog razloga, primarni ključevi su uvijek indeksirani.

Nedostaci indeksiranja

Indeksi degradiraju performanse sistema tokom promjena zapisa. Svaki put kada se izvrši upit za promjenu podataka u tablici, indeks se također mora promijeniti. Da biste odabrali optimalan broj indeksa, morate testirati bazu podataka i pratiti njenu izvedbu. Statički sistemi, gdje se baze podataka koriste prvenstveno za pronalaženje podataka, kao što je izvještavanje, mogu sadržavati više indeksa za podršku upitima samo za čitanje. Baze podataka sa velikim brojem transakcija za promjenu podataka trebat će mali broj indeksa kako bi se osigurala veća propusnost.

Indeksi zauzimaju dodatni prostor na disku iu RAM-u. Tačna veličina zavisiće od broja zapisa u tabeli, kao i od broja i veličine kolona u indeksu. U većini slučajeva, ovo nije veliki problem jer je prostor na disku sada lako žrtvovati za bolje performanse.

Izgradnja optimalnog indeksa

Jednostavan indeks

Jednostavan indeks je indeks koji koristi vrijednosti jednog polja u tabeli. Upotreba jednostavnog indeksa je korisna iz dva razloga. Prvo, pokretanje baze podataka stavlja veliki stres na vaš čvrsti disk. Veliki indeksni ključevi će primorati bazu podataka da izvrši više I/O operacija, što ograničava performanse.

Drugo, pošto su elementi indeksa često uključeni u poređenje, manje indekse je lakše upoređivati. Iz ova dva razloga, jedan cjelobrojni stupac je bolji indeks jer je mali i lak za upoređivanje. S druge strane, nizovi znakova zahtijevaju poređenje karaktera po znak i pažnju na rukovanje parametrima.

Selektivni indeks

Najefikasniji indeksi su oni sa niskim procentom duplih vrijednosti. Na primjer, telefonski imenik za grad u kojem se gotovo svi prezivaju Smith neće biti toliko koristan ako se unosi u njemu sortiraju po prezimenu.

Indeks s visokim postotkom jedinstvenih vrijednosti naziva se i selektivni indeks. Očigledno, jedinstveni indeks ima najveću selektivnost, jer ne sadrži duple vrijednosti. Mnogi DBMS-ovi mogu pratiti statistiku o svakom indeksu i mogu prepoznati koliko nedupliciranih vrijednosti svaki indeks sadrži. Ova statistika se koristi kada se generira plan izvršenja upita.

Indeksi pokrivanja

Indeksi se sastoje od stupca podataka na kojem je izgrađen sam indeks i pokazivača na odgovarajući red. To je poput indeksa knjige: sadrži samo ključne riječi i vezu do stranice na koju možete otići za više informacija. Obično će DBMS pratiti pokazivače na red iz indeksa kako bi prikupio sve informacije potrebne za upit. Međutim, ako indeks sadrži sve stupce potrebne u upitu, informacije se mogu dohvatiti bez pristupa samoj tablici.

Razmotrimo indeks na stupcu UnitPrice, koji je već spomenut gore. DBMS može koristiti samo indeksne stavke za izvršavanje sljedećeg upita.

Odaberite Count(*), UnitPrice iz grupe proizvoda prema jediničnoj cijeni

Ovaj tip upita naziva se upit za pokrivanje jer se svi stupci za koje se postavlja upit mogu dohvatiti iz jednog indeksa. Za najvažnije upite, možda biste trebali razmisliti o kreiranju indeksa pokrivanja za najbolje moguće performanse. Takvi indeksi će vjerovatno biti složeni (koristeći više od jedne kolone), što je suprotno prvom principu: kreirajte jednostavne indekse. Očigledno, izbor optimalnog broja kolona u indeksu može se procijeniti samo kroz testiranje i praćenje performansi baze podataka u različitim situacijama.

Indeks klastera

Mnoge baze podataka imaju jedan poseban indeks na tabeli, gde su svi podaci iz reda sadržani u indeksu. U SQL Serveru, takav indeks se naziva klasterirani indeks. Grupirani indeks se može usporediti s telefonskim imenikom jer svaki element indeksa sadrži sve informacije koje su vam potrebne i ne sadrži veze za dobivanje dodatnih podataka.

Postoji opšte pravilo - svaka netrivijalna tabela mora imati klasterizovani indeks. Ako je moguće kreirati samo jedan indeks u tabeli, učinite je klasterizovanom. U SQL Serveru, kada se kreira primarni ključ, automatski će se kreirati grupirani indeks (ako ga već ne sadrži), koristeći kolonu primarnog ključa kao ključ za indeksiranje. Grupirani indeks je najefikasniji indeks (ako se koristi, pokriva cijeli upit) i u mnogim DBMS-ovima takav indeks pomaže u efikasnom upravljanju prostorom koji se traži za pohranjivanje tabela, budući da se u suprotnom (bez izgradnje klasteriranog indeksa) redovi tablice pohranjuju u neuređenu strukturu, koja se naziva hrpa.

Budite oprezni pri odabiru stupaca za grupirani indeks. Ako promijenite zapis i promijenite vrijednost stupca u grupisanom indeksu, baza podataka će biti prisiljena ponovo izgraditi indeksne stavke (kako bi ih zadržala u sortiranom redoslijedu). Zapamtite, stavke indeksa za grupirani indeks sadrže sve vrijednosti stupaca, tako da je promjena vrijednosti stupca uporediva sa izvršavanjem naredbe Delete nakon čega slijedi izraz Insert, što će očito uzrokovati probleme u performansama ako se radi često. Iz tog razloga, grupirani indeksi se često sastoje od primarnog ključa i kolone stranog ključa. Ako se ključne vrijednosti mijenjaju, mijenjaju se vrlo rijetko.

Zaključak

Određivanje ispravnih indeksa za korištenje u bazi podataka zahtijeva pažljivu analizu i testiranje sistema. Praksa predstavljena u ovom članku su dobra pravila za konstruisanje indeksa. Nakon primjene ovih metoda, morat ćete ponovo testirati svoju specifičnu aplikaciju pod vašim specifičnim hardverskim, memorijskim i operativnim uvjetima.

Jedan od najvažnijih načina za postizanje visoke produktivnosti SQL Server je upotreba indeksa. Indeks ubrzava proces upita pružajući brz pristup redovima podataka u tabeli, slično kao što vam indeks u knjizi pomaže da brzo pronađete informacije koje su vam potrebne. U ovom članku ću dati kratak pregled indeksa u SQL Server i objasniti kako su organizirani u bazi podataka i kako pomažu da se ubrzaju upiti baze podataka.

Indeksi se kreiraju na tablicama i kolonama pogleda. Indeksi pružaju način za brzo pretraživanje podataka na osnovu vrijednosti u tim stupcima. Na primjer, ako kreirate indeks na primarnom ključu, a zatim tražite red podataka koristeći vrijednosti primarnog ključa, tada SQL Serverće prvo pronaći vrijednost indeksa, a zatim koristiti indeks da brzo pronađe cijeli red podataka. Bez indeksa, izvršiće se potpuno skeniranje svih redova u tabeli, što može imati značajan uticaj na performanse.
Možete kreirati indeks za većinu kolona u tabeli ili prikazu. Izuzetak su uglavnom kolone sa tipovima podataka za pohranjivanje velikih objekata ( LOB), kao što je slika, tekst ili varchar(max). Također možete kreirati indekse na stupcima dizajniranim za pohranjivanje podataka u formatu XML, ali su ovi indeksi strukturirani nešto drugačije od standardnih i njihovo razmatranje je izvan okvira ovog članka. Također, članak ne raspravlja columnstore indeksi. Umjesto toga, fokusiram se na one indekse koji se najčešće koriste u bazama podataka SQL Server.
Indeks se sastoji od skupa stranica, indeksnih čvorova, koji su organizovani u strukturu stabla - balansirano drvo. Ova struktura je hijerarhijske prirode i počinje s korijenskim čvorom na vrhu hijerarhije i listovima, listovima, na dnu, kao što je prikazano na slici:


Kada postavljate upit prema indeksiranoj koloni, mašina za upite počinje na vrhu korijenskog čvora i radi svoj put prema dolje kroz međučvorove, pri čemu svaki međusloj sadrži detaljnije informacije o podacima. Mehanizam upita nastavlja da se kreće kroz indeksne čvorove sve dok ne dostigne donji nivo sa listovima indeksa. Na primjer, ako tražite vrijednost 123 u indeksiranoj koloni, mašina za upite će prvo odrediti stranicu na prvom srednjem nivou na osnovnom nivou. U ovom slučaju, prva stranica ukazuje na vrijednost od 1 do 100, a druga od 101 do 200, tako da će mašina za upite pristupiti drugoj stranici ovog srednjeg nivoa. Zatim ćete vidjeti da biste trebali okrenuti treću stranicu sljedećeg srednjeg nivoa. Odavde će podsistem upita pročitati vrijednost samog indeksa na nižem nivou. Listovi indeksa mogu sadržavati ili same podatke tablice ili jednostavno pokazivač na redove s podacima u tablici, ovisno o tipu indeksa: klasterirani indeks ili neklasterirani indeks.

Grupirani indeks

Grupirani indeks pohranjuje stvarne redove podataka u listovima indeksa. Vraćajući se na prethodni primjer, to znači da će red podataka pridruženih ključnoj vrijednosti 123 biti pohranjen u samom indeksu. Važna karakteristika grupisanog indeksa je da su sve vrijednosti sortirane u određenom redoslijedu, bilo uzlazno ili opadajuće. Stoga, tablica ili pogled može imati samo jedan klasterizirani indeks. Osim toga, treba napomenuti da se podaci u tablici pohranjuju u sortiranom obliku samo ako je na ovoj tablici kreiran klasterirani indeks.
Tabela koja nema grupirani indeks naziva se hrpa.

Neklasterirani indeks

Za razliku od grupiranog indeksa, listovi indeksa koji nisu grupirani sadrže samo te stupce ( ključ) kojim se ovaj indeks određuje, a sadrži i pokazivač na redove sa stvarnim podacima u tabeli. To znači da sistem podupita zahtijeva dodatnu operaciju za lociranje i dohvaćanje potrebnih podataka. Sadržaj pokazivača podataka ovisi o tome kako se podaci pohranjuju: klasterirana tablica ili hrpa. Ako pokazivač pokazuje na grupisanu tabelu, on pokazuje na klasterizovani indeks koji se može koristiti za pronalaženje stvarnih podataka. Ako se pokazivač odnosi na hrpu, onda pokazuje na određeni identifikator reda podataka. Neklasterirani indeksi se ne mogu sortirati kao klasterirani indeksi, ali možete kreirati više od jednog neklasteriziranog indeksa na tablici ili prikazu, do 999. To ne znači da biste trebali kreirati što je više moguće indeksa. Indeksi mogu poboljšati ili degradirati performanse sistema. Osim što možete kreirati više indeksa koji nisu grupirani, možete uključiti i dodatne stupce ( uključena kolona) u svoj indeks: listovi indeksa će pohraniti ne samo vrijednost samih indeksiranih stupaca, već i vrijednosti ovih neindeksiranih dodatnih stupaca. Ovaj pristup će vam omogućiti da zaobiđete neka ograničenja postavljena na indeks. Na primjer, možete uključiti stupac koji se ne može indeksirati ili zaobići ograničenje dužine indeksa (900 bajtova u većini slučajeva).

Vrste indeksa

Pored toga što je klasterizovan ili neklaster, indeks se može dalje konfigurisati kao kompozitni indeks, jedinstveni indeks ili indeks pokrivanja.

Kompozitni indeks

Takav indeks može sadržavati više od jedne kolone. Možete uključiti do 16 kolona u indeks, ali njihova ukupna dužina je ograničena na 900 bajtova. I klasterirani i neklasterirani indeksi mogu biti složeni.

Jedinstveni indeks

Ovaj indeks osigurava da je svaka vrijednost u indeksiranoj koloni jedinstvena. Ako je indeks kompozitni, tada se jedinstvenost primjenjuje na sve stupce u indeksu, ali ne i na svaki pojedinačni stupac. Na primjer, ako kreirate jedinstveni indeks na stupcima NAME I PREZIME, tada puno ime mora biti jedinstveno, ali su mogući duplikati u imenu ili prezimenu.
Jedinstveni indeks se automatski kreira kada definirate ograničenje stupca: primarni ključ ili ograničenje jedinstvene vrijednosti:

• Primarni ključ
  Kada definirate ograničenje primarnog ključa na jednom ili više stupaca tada SQL Server automatski kreira jedinstveni klasterirani indeks ako klasterirani indeks nije prethodno kreiran (u ovom slučaju, jedinstveni ne-klasterirani indeks se kreira na primarnom ključu)
• Jedinstvenost vrijednosti
  Kada definirate ograničenje na jedinstvenost vrijednosti onda SQL Server automatski kreira jedinstveni neklasterirani indeks. Možete odrediti da se kreira jedinstveni klasterirani indeks ako u tablici još nije kreiran klasterirani indeks

Indeks pokrivanja

Takav indeks omogućava specifičnom upitu da odmah dobije sve potrebne podatke iz listova indeksa bez dodatnog pristupa zapisima same tabele.

Dizajniranje indeksa

Koliko god indeksi bili korisni, moraju biti pažljivo dizajnirani. Budući da indeksi mogu zauzeti značajan prostor na disku, ne želite da kreirate više indeksa nego što je potrebno. Osim toga, indeksi se automatski ažuriraju kada se ažurira sam red podataka, što može dovesti do dodatnih troškova resursa i degradacije performansi. Prilikom dizajniranja indeksa, nekoliko razmatranja u vezi sa bazom podataka i upitima prema njoj mora se uzeti u obzir.

Baza podataka

Kao što je ranije navedeno, indeksi mogu poboljšati performanse sistema jer oni pružaju mašini za upite brz način za pronalaženje podataka. Međutim, također biste trebali uzeti u obzir koliko često namjeravate umetnuti, ažurirati ili izbrisati podatke. Kada promijenite podatke, indeksi se također moraju promijeniti da odražavaju odgovarajuće radnje na podacima, što može značajno smanjiti performanse sistema. Uzmite u obzir sljedeće smjernice kada planirate svoju strategiju indeksiranja:

• Za tabele koje se često ažuriraju, koristite što manje indeksa.
• Ako tabela sadrži veliku količinu podataka, ali su promjene male, onda koristite onoliko indeksa koliko je potrebno da poboljšate performanse vaših upita. Međutim, dobro razmislite prije korištenja indeksa na malim tablicama, jer... Moguće je da korištenje indeksnog pretraživanja može potrajati duže od jednostavnog skeniranja svih redova.
• Za grupisane indekse, pokušajte da polja budu što kraća. Najbolji pristup je korištenje grupisanog indeksa na stupcima koji imaju jedinstvene vrijednosti i ne dozvoljavaju NULL. Zbog toga se primarni ključ često koristi kao klasterirani indeks.
• Jedinstvenost vrijednosti u stupcu utiče na performanse indeksa. Općenito, što više duplikata imate u koloni, indeks je lošiji. S druge strane, što je više jedinstvenih vrijednosti, to je bolji učinak indeksa. Koristite jedinstveni indeks kad god je to moguće.
• Za složeni indeks, uzmite u obzir redoslijed stupaca u indeksu. Kolone koje se koriste u izrazima GDJE(Na primjer, WHERE FirstName = "Charlie") mora biti prvi u indeksu. Sledeće kolone treba da budu navedene na osnovu jedinstvenosti njihovih vrednosti (prve su kolone sa najvećim brojem jedinstvenih vrednosti).
• Također možete specificirati indeks za izračunate stupce ako ispunjavaju određene zahtjeve. Na primjer, izrazi koji se koriste za dobivanje vrijednosti stupca moraju biti deterministički (uvijek vraćaju isti rezultat za dati skup ulaznih parametara).

Upiti baze podataka

Još jedno razmatranje prilikom dizajniranja indeksa je koji se upiti pokreću prema bazi podataka. Kao što je ranije rečeno, morate uzeti u obzir koliko često se podaci mijenjaju. Osim toga, treba koristiti sljedeće principe:

• Pokušajte umetnuti ili izmijeniti što više redova u jednom upitu, umjesto da to radite u nekoliko pojedinačnih upita.
• Kreirajte negrupisani indeks na stupcima koji se često koriste kao pojmovi za pretraživanje u vašim upitima. GDJE i veze u PRIDRUŽITE SE.
• Razmislite o indeksiranju stupaca koji se koriste u upitima za traženje reda za tačna podudaranja vrijednosti.

A sada, zapravo:

14 pitanja o indeksima u SQL Serveru koja vam je bilo neugodno postaviti

Zašto tabela ne može imati dva grupirana indeksa?

Želite kratak odgovor? Grupirani indeks je tabela. Kada kreirate grupirani indeks na tablici, mehanizam za pohranu sortira sve redove u tablici uzlaznim ili silaznim redoslijedom, prema definiciji indeksa. Grupirani indeks nije poseban entitet kao drugi indeksi, već mehanizam za sortiranje podataka u tabeli i omogućavanje brzog pristupa redovima podataka.
Zamislimo da imate tabelu koja sadrži istoriju prodajnih transakcija. Tabela prodaje uključuje informacije kao što su ID narudžbe, pozicija proizvoda u narudžbi, broj proizvoda, količina proizvoda, broj i datum narudžbe itd. Na stupcima kreirate grupirani indeks OrderID I LineID, sortirano uzlaznim redoslijedom kao što je prikazano u nastavku T-SQL kod:
KREIRAJTE JEDINSTVEN CLUSTERED INDEX ix_oriderid_lineid NA dbo.Sales(OrderID, LineID);
Kada pokrenete ovu skriptu, svi redovi u tabeli će biti fizički sortirani prvo po OrderID koloni, a zatim po LineID-u, ali sami podaci će ostati u jednom logičkom bloku, tabeli. Iz tog razloga ne možete kreirati dva klasterirana indeksa. Može postojati samo jedna tabela sa jednim podacima i ta tabela se može sortirati samo jednom određenim redosledom.

Ako klasterirana tabela pruža mnoge prednosti, zašto onda koristiti hrpu?

Upravu si. Grupirane tabele su sjajne i većina vaših upita će imati bolji učinak na tabelama koje imaju klasterizovani indeks. Ali u nekim slučajevima možda želite da ostavite stolove u njihovom prirodnom, netaknutom stanju, tj. u obliku hrpe i kreirajte samo indekse koji nisu grupirani da bi vaši upiti ostali aktivni.
Hrpa, kao što se sjećate, pohranjuje podatke nasumičnim redoslijedom. Tipično, podsistem za skladištenje dodaje podatke u tabelu redosledom kojim su umetnuti, ali podsistem skladištenja takođe voli da pomera redove radi efikasnijeg skladištenja. Kao rezultat toga, nemate priliku predvidjeti kojim će redoslijedom podaci biti pohranjeni.
Ako mehanizam za upite treba da pronađe podatke bez prednosti neklasteriranog indeksa, izvršit će potpuno skeniranje tablice kako bi pronašao redove koji su mu potrebni. Na vrlo malim tablicama to obično nije problem, ali kako hrpa raste u veličini, performanse brzo opadaju. Naravno, negrupisani indeks može pomoći korištenjem pokazivača na datoteku, stranicu i red gdje su pohranjeni potrebni podaci - ovo je obično mnogo bolja alternativa skeniranju tablice. Čak i tako, teško je uporediti prednosti grupisanog indeksa kada se razmatra performanse upita.
Međutim, hrpa može pomoći u poboljšanju performansi u određenim situacijama. Uzmite u obzir tabelu sa puno umetanja, ali malo ažuriranja ili brisanja. Na primjer, tablica koja pohranjuje dnevnik prvenstveno se koristi za umetanje vrijednosti dok se ne arhivira. Na hrpi nećete vidjeti stranice i fragmentaciju podataka kao što biste to vidjeli kod grupisanog indeksa jer se redovi jednostavno dodaju na kraj hrpe. Previše dijeljenja stranica može imati značajan utjecaj na performanse, i to ne na dobar način. Općenito, hrpa vam omogućava da ubacite podatke relativno bezbolno i nećete morati da se bavite troškovima skladištenja i održavanja kao što biste radili sa klasteriranim indeksom.
Ali nedostatak ažuriranja i brisanja podataka ne treba smatrati jedinim razlogom. Način na koji su podaci uzorkovani je takođe važan faktor. Na primjer, ne biste trebali koristiti hrpu ako često postavljate upite za opsege podataka ili podatke koje tražite često treba sortirati ili grupirati.
Sve to znači da biste trebali razmisliti o korištenju hrpe samo kada radite s vrlo malim tablicama ili je sva vaša interakcija s tablicom ograničena na umetanje podataka, a vaši upiti su izuzetno jednostavni (i koristite ne-klasterirane indekse u svakom slučaju). U suprotnom, držite se dobro dizajniranog grupisanog indeksa, kao što je onaj definiran na jednostavnom rastućem ključnom polju, poput široko korištene kolone sa IDENTITET.

Kako mogu promijeniti zadani faktor popunjavanja indeksa?

Promjena zadanog faktora popunjavanja indeksa je jedna stvar. Razumevanje načina na koji radi podrazumevani odnos je druga stvar. Ali prvo se vratite nekoliko koraka unazad. Faktor popunjavanja indeksa određuje količinu prostora na stranici za pohranjivanje indeksa na donjem nivou (nivo lista) prije početka popunjavanja nove stranice. Na primjer, ako je koeficijent postavljen na 90, onda kada indeks raste, on će zauzeti 90% stranice, a zatim će se preći na sljedeću stranicu.
Prema zadanim postavkama, vrijednost faktora popunjavanja indeksa je in SQL Server je 0, što je isto kao 100. Kao rezultat toga, svi novi indeksi automatski nasljeđuju ovu postavku osim ako posebno ne navedete vrijednost u svom kodu koja se razlikuje od standardne vrijednosti sistema ili promijenite zadano ponašanje. Možeš koristiti SQL Server Management Studio za podešavanje zadane vrijednosti ili pokretanje sistemske pohranjene procedure sp_configure. Na primjer, sljedeći set T-SQL naredba postavlja vrijednost koeficijenta na 90 (prvo se morate prebaciti na mod naprednih postavki):
EXEC sp_configure "prikaži napredne opcije", 1; GO RECONFIGURE; GO EXEC sp_configure "faktor popunjavanja", 90; GO RECONFIGURE; GO
Nakon promjene vrijednosti faktora popunjavanja indeksa, morate ponovo pokrenuti uslugu SQL Server. Sada možete provjeriti postavljenu vrijednost pokretanjem sp_configure bez navedenog drugog argumenta:
EXEC sp_configure "faktor popunjavanja" GO
Ova naredba bi trebala vratiti vrijednost od 90. Kao rezultat, svi novokreirani indeksi će koristiti ovu vrijednost. Ovo možete testirati kreiranjem indeksa i upitom za vrijednost faktora popunjavanja:
USE AdventureWorks2012; -- vaša baza podataka GO CREATE NENCLUSTERED INDEX ix_people_lastname NA Person.Person(Prezime); IDI SELECT fill_factor FROM sys.indexes GDJE object_id = object_id("Person.Person") AND name="ix_people_lastname";
U ovom primjeru kreirali smo neklasterizirani indeks na tablici Osoba u bazi podataka AdventureWorks2012. Nakon kreiranja indeksa, možemo dobiti vrijednost faktora popunjavanja iz sistemskih tablica sys.indexes. Upit bi trebao vratiti 90.
Međutim, zamislimo da smo izbrisali indeks i ponovo ga kreirali, ali sada smo naveli specifičnu vrijednost faktora popunjavanja:
CREATE NENCLUSTERED INDEX ix_people_lastname ON Person.Person(Prezime) WITH (filfactor=80); IDI SELECT fill_factor FROM sys.indexes GDJE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Ovaj put smo dodali uputstva WITH i opciju fillfactor za našu operaciju kreiranja indeksa CREATE INDEX i specificirao vrijednost 80. Operator SELECT sada vraća odgovarajuću vrijednost.
Do sada je sve bilo prilično jednostavno. Ono u čemu se zaista možete izgorjeti u cijelom ovom procesu je kada kreirate indeks koji koristi zadanu vrijednost koeficijenta, pod pretpostavkom da znate tu vrijednost. Na primjer, neko petlja sa postavkama servera i toliko je tvrdoglav da je postavio faktor popunjavanja indeksa na 20. U međuvremenu, nastavljate da kreirate indekse, pod pretpostavkom da je zadana vrijednost 0. Nažalost, nemate načina da saznate popunjenost faktor sve dok ne kreirate indeks, a zatim provjerite vrijednost kao što smo radili u našim primjerima. U suprotnom, moraćete da sačekate trenutak kada performanse upita toliko opadnu da počnete da sumnjate u nešto.
Još jedan problem kojeg biste trebali biti svjesni je ponovna izgradnja indeksa. Kao i kod kreiranja indeksa, možete odrediti vrijednost faktora popunjavanja indeksa kada ga ponovo izgradite. Međutim, za razliku od naredbe create index, rebuild ne koristi zadane postavke servera, uprkos tome kako to može izgledati. Čak i više, ako ne navedete posebno vrijednost faktora popunjavanja indeksa, onda SQL Server koristiće vrijednost koeficijenta s kojim je ovaj indeks postojao prije njegovog restrukturiranja. Na primjer, sljedeća operacija ALTER INDEX ponovo gradi indeks koji smo upravo kreirali:
ALTER INDEX ix_people_lastname ON Person.Person REBUILD; IDI SELECT fill_factor FROM sys.indexes GDJE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Kada provjerimo vrijednost faktora popunjavanja, dobićemo vrijednost od 80, jer je to ono što smo naveli kada smo zadnji put kreirali indeks. Zadana vrijednost se zanemaruje.
Kao što vidite, promjena vrijednosti faktora popunjavanja indeksa nije tako teška. Mnogo je teže znati trenutnu vrijednost i razumjeti kada se ona primjenjuje. Ako uvijek posebno specificirate koeficijent pri kreiranju i ponovnoj izgradnji indeksa, tada uvijek znate konkretan rezultat. Osim ako ne morate da brinete o tome da neko drugi ponovo ne zezne podešavanja servera, uzrokujući da se svi indeksi ponovo izgrade sa smešno niskim faktorom popunjavanja indeksa.

Da li je moguće kreirati grupirani indeks na koloni koja sadrži duplikate?

Da i ne. Da, možete kreirati grupirani indeks na ključnom stupcu koji sadrži duple vrijednosti. Ne, vrijednost stupca ključa ne može ostati u nejedinstvenom stanju. Dopusti mi da objasnim. Ako kreirate nejedinstveni grupirani indeks na stupcu, mehanizam za pohranu dodaje unifikator dupliranoj vrijednosti kako bi osigurao jedinstvenost i stoga mogao identificirati svaki red u grupisanoj tablici.
Na primjer, možete odlučiti da kreirate grupirani indeks na stupcu koji sadrži podatke korisnika Prezime zadržavanje prezimena. Kolona sadrži vrijednosti Franklin, Hancock, Washington i Smith. Zatim ponovo ubacujete vrijednosti Adams, Hancock, Smith i Smith. Ali vrijednost ključnog stupca mora biti jedinstvena, tako da će mehanizam za pohranu promijeniti vrijednost duplikata tako da izgledaju otprilike ovako: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 i Smith5678.
Na prvi pogled, ovaj pristup izgleda u redu, ali cjelobrojna vrijednost povećava veličinu ključa, što može postati problem ako postoji veliki broj duplikata, a te vrijednosti će postati osnova neklasteriranog indeksa ili stranog ključna referenca. Iz ovih razloga, uvijek biste trebali pokušati kreirati jedinstvene klasterizirane indekse kad god je to moguće. Ako to nije moguće, pokušajte barem koristiti stupce s vrlo visokom jedinstvenom vrijednošću sadržaja.

Kako se tabela pohranjuje ako nije kreiran grupirani indeks?

SQL Server podržava dvije vrste tablica: klasterirane tablice koje imaju grupirani indeks i hrpe tablica ili samo hrpe. Za razliku od klasteriranih tabela, podaci na hrpi nisu sortirani ni na koji način. U suštini, ovo je gomila (gomila) podataka. Ako takvoj tabeli dodate red, mehanizam za skladištenje će ga jednostavno dodati na kraj stranice. Kada se stranica popuni podacima, bit će dodana na novu stranicu. U većini slučajeva, poželećete da kreirate grupirani indeks na tabeli da biste iskoristili prednost sortiranja i brzine upita (pokušajte da zamislite da tražite telefonski broj u nesortiranom adresaru). Međutim, ako odlučite da ne kreirate klasterizovani indeks, i dalje možete kreirati neklasterizovani indeks na hrpi. U ovom slučaju, svaki red indeksa će imati pokazivač na red hrpe. Indeks uključuje ID datoteke, broj stranice i broj reda podataka.

Kakav je odnos između ograničenja jedinstvenosti vrijednosti i primarnog ključa s indeksima tablice?

Primarni ključ i jedinstveno ograničenje osiguravaju da su vrijednosti u stupcu jedinstvene. Možete kreirati samo jedan primarni ključ za tabelu i ne može sadržavati vrijednosti NULL. Možete kreirati nekoliko ograničenja za jedinstvenost vrijednosti za tablicu, a svako od njih može imati jedan zapis sa NULL.
Kada kreirate primarni ključ, mehanizam za pohranu također kreira jedinstveni klasterirani indeks ako klasterirani indeks već nije kreiran. Međutim, možete nadjačati zadano ponašanje i kreirat će se neklasterirani indeks. Ako klasterirani indeks postoji kada kreirate primarni ključ, kreirat će se jedinstveni neklasterirani indeks.
Kada kreirate jedinstveno ograničenje, mehanizam za skladištenje kreira jedinstveni, neklasterizovani indeks. Međutim, možete specificirati kreiranje jedinstvenog klasteriranog indeksa ako nije prethodno kreiran.
Općenito, ograničenje jedinstvene vrijednosti i jedinstveni indeks su ista stvar.

Zašto se grupirani i neklasterizovani indeksi u SQL Serveru nazivaju B-stablo?

Osnovni indeksi u SQL Serveru, grupisani ili neklasterisani, distribuiraju se preko skupova stranica koje se nazivaju indeksni čvorovi. Ove stranice su organizirane u specifičnoj hijerarhiji sa strukturom stabla koja se naziva uravnoteženo stablo. Na gornjem nivou nalazi se korijenski čvor, na dnu su čvorovi lista, sa međučvorovima između gornjeg i donjeg nivoa, kao što je prikazano na slici:


Korijenski čvor pruža glavnu ulaznu tačku za upite koji pokušavaju da dohvate podatke kroz indeks. Počevši od ovog čvora, mašina za upite pokreće navigaciju niz hijerarhijsku strukturu do odgovarajućeg lisnog čvora koji sadrži podatke.
Na primjer, zamislite da je primljen zahtjev za odabir redova koji sadrže ključnu vrijednost 82. Podsistem upita počinje raditi od korijenskog čvora, koji se odnosi na odgovarajući međučvor, u našem slučaju 1-100. Od srednjeg čvora 1-100 dolazi do prijelaza na čvor 51-100, a odatle u konačni čvor 76-100. Ako je ovo klasterizirani indeks, tada list čvora sadrži podatke reda pridruženog ključu jednak 82. Ako je ovo neklasterizirani indeks, onda list indeksa sadrži pokazivač na klasteriziranu tablicu ili određeni red u gomila.

Kako indeks može čak poboljšati performanse upita ako morate proći kroz sve ove čvorove indeksa?

Prvo, indeksi ne poboljšavaju uvijek performanse. Previše pogrešno kreiranih indeksa pretvara sistem u blato i degradira performanse upita. Tačnije je reći da ako se indeksi pažljivo primjenjuju, oni mogu pružiti značajne dobitke u performansama.
Zamislite veliku knjigu posvećenu podešavanju performansi SQL Server(papirna verzija, ne elektronska verzija). Zamislite da želite pronaći informacije o konfiguriranju Resource Governora. Možete prstom prevlačiti stranicu po stranicu kroz cijelu knjigu ili otvoriti sadržaj i saznati tačan broj stranice sa informacijama koje tražite (pod uslovom da je knjiga ispravno indeksirana i sadržaj ima ispravne indekse). Ovo će vam svakako uštedjeti značajno vrijeme, iako prvo morate pristupiti potpuno drugoj strukturi (indeksu) da biste dobili informacije koje su vam potrebne iz primarne strukture (knjige).
Kao indeks knjige, indeks u SQL Server omogućava vam da pokrenete precizne upite o podacima koji su vam potrebni umjesto da potpuno skenirate sve podatke sadržane u tabeli. Za male tabele, potpuno skeniranje obično nije problem, ali velike tabele zauzimaju mnogo stranica podataka, što može rezultirati značajnim vremenom izvršenja upita osim ako ne postoji indeks koji bi omogućio mašini za upite da odmah dobije tačnu lokaciju podataka. Zamislite da se izgubite na raskrsnici na više nivoa ispred velike metropole bez mape i shvatit ćete.

Ako su indeksi tako sjajni, zašto jednostavno ne napravite po jedan na svakoj koloni?

Nijedno dobro djelo ne smije ostati nekažnjeno. Barem je to slučaj sa indeksima. Naravno, indeksi rade odlično sve dok pokrećete upite za dohvaćanje operatora SELECT, ali čim počnu česti pozivi operaterima INSERT, UPDATE I IZBRIŠI, tako da se pejzaž vrlo brzo mijenja.
Kada pokrenete zahtjev za podacima od strane operatera SELECT, mehanizam upita pronalazi indeks, kreće se kroz njegovu strukturu stabla i otkriva podatke koje traži. Šta može biti jednostavnije? Ali stvari se mijenjaju ako pokrenete izjavu o promjeni kao što je UPDATE. Da, za prvi dio izjave, mehanizam upita ponovo može koristiti indeks da locira red koji se mijenja - to je dobra vijest. A ako postoji jednostavna promjena podataka u redu koja ne utječe na promjene u ključnim stupcima, tada će proces promjene biti potpuno bezbolan. Ali šta ako promjena uzrokuje podjelu stranica koje sadrže podatke ili se promijeni vrijednost ključnog stupca zbog čega se premješta na drugi indeksni čvor - to će rezultirati da će indeksu možda biti potrebna reorganizacija koja će utjecati na sve povezane indekse i operacije , što dovodi do široko rasprostranjenog pada produktivnosti.
Slični procesi se dešavaju prilikom pozivanja operatera IZBRIŠI. Indeks može pomoći u lociranju podataka koji se brišu, ali brisanje samih podataka može dovesti do ponovnog premještanja stranica. Što se tiče operatera INSERT, glavni neprijatelj svih indeksa: počnete da dodajete veliku količinu podataka, što dovodi do promjena u indeksima i njihove reorganizacije i svi trpe.
Stoga uzmite u obzir tipove upita vašoj bazi podataka kada razmišljate o tome koju vrstu indeksa i koliko ih kreirati. Više ne znači bolje. Prije dodavanja novog indeksa u tablicu, uzmite u obzir troškove ne samo osnovnih upita, već i količinu potrošenog prostora na disku, troškove održavanja funkcionalnosti i indeksa, što može dovesti do domino efekta na druge operacije. Vaša strategija dizajna indeksa jedan je od najvažnijih aspekata vaše implementacije i trebala bi uključiti mnoga razmatranja, od veličine indeksa, broja jedinstvenih vrijednosti, do vrste upita koje će indeks podržavati.

Da li je potrebno kreirati grupirani indeks na koloni s primarnim ključem?

Možete kreirati grupirani indeks na bilo kojoj koloni koja ispunjava tražene uslove. Istina je da su klasterirani indeks i ograničenje primarnog ključa napravljeni jedno za drugo i da se podudaraju na nebu, pa shvatite činjenicu da kada kreirate primarni ključ, onda će klasterirani indeks biti automatski kreiran ako nije bio kreiran ranije. Međutim, možete odlučiti da bi klasterirani indeks bio bolji na drugim mjestima, a često će vaša odluka biti opravdana.
Glavna svrha grupisanog indeksa je da sortira sve redove u vašoj tabeli na osnovu ključne kolone koja je navedena prilikom definisanja indeksa. Ovo omogućava brzu pretragu i lak pristup podacima tabele.
Primarni ključ tabele može biti dobar izbor jer jedinstveno identifikuje svaki red u tabelama bez potrebe za dodavanjem dodatnih podataka. U nekim slučajevima, najbolji izbor će biti surogat primarni ključ, koji nije samo jedinstven, već je i male veličine i čije se vrijednosti uzastopno povećavaju, čineći neklasterirane indekse zasnovane na ovoj vrijednosti efikasnijim. Optimizator upita također voli ovu kombinaciju klasteriranog indeksa i primarnog ključa jer je spajanje tabela brže od spajanja na drugi način koji ne koristi primarni ključ i njegov pridruženi klasterirani indeks. Kao što sam rekao, to je šibica napravljena na nebu.
Na kraju, međutim, vrijedno je napomenuti da prilikom kreiranja grupisanog indeksa postoji nekoliko aspekata koje treba uzeti u obzir: koliko će neklasteriranih indeksa biti zasnovano na njemu, koliko često će se mijenjati vrijednost stupca ključnog indeksa i koliko velika. Kada se vrijednosti u stupcima grupiranog indeksa promijene ili indeks ne radi kako se očekivalo, to može utjecati na sve ostale indekse u tablici. Grupirani indeks bi trebao biti zasnovan na najtrajnijoj koloni čije se vrijednosti povećavaju određenim redoslijedom, ali se ne mijenjaju na slučajan način. Indeks mora podržavati upite prema podacima tablice kojima se najčešće pristupa, tako da upiti u potpunosti iskorištavaju činjenicu da su podaci sortirani i dostupni na korijenskim čvorovima, listovima indeksa. Ako primarni ključ odgovara ovom scenariju, onda ga upotrijebite. Ako ne, odaberite drugi skup kolona.

Šta ako indeksirate pogled, da li je to još uvijek pogled?

Pogled je virtuelna tabela koja generiše podatke iz jedne ili više tabela. U suštini, to je imenovani upit koji dohvaća podatke iz osnovnih tabela kada postavljate upit za taj pogled. Možete poboljšati performanse upita kreiranjem grupisanog indeksa i neklasteriziranih indeksa na ovom prikazu, slično kao što kreirate indekse na tablici, ali glavno upozorenje je da prvo kreirate klasterirani indeks, a zatim možete kreirati neklasterirani indeks.
Kada se kreira indeksirani pogled (materijalizirani pogled), onda sama definicija pogleda ostaje zaseban entitet. Ovo je, na kraju krajeva, samo tvrdo kodirani operator SELECT, pohranjen u bazi podataka. Ali indeks je sasvim druga priča. Kada kreirate grupirani ili neklasterizovani indeks na dobavljaču, podaci se fizički spremaju na disk, baš kao i običan indeks. Osim toga, kada se podaci promijene u osnovnim tabelama, indeks pogleda se automatski mijenja (to znači da biste mogli izbjeći indeksiranje pogleda na tabelama koje se često mijenjaju). U svakom slučaju, pogled ostaje pogled - pogled na tabele, ali onaj koji se izvršava u ovom trenutku, sa indeksima koji mu odgovaraju.
Prije nego što možete kreirati indeks na prikazu, on mora zadovoljiti nekoliko ograničenja. Na primjer, pogled može referencirati samo osnovne tablice, ali ne i druge poglede, a te tablice moraju biti u istoj bazi podataka. Zapravo postoje mnoga druga ograničenja, pa svakako provjerite dokumentaciju SQL Server za sve prljave detalje.

Zašto koristiti indeks pokrivanja umjesto kompozitnog indeksa?

Prvo, hajde da se uverimo da razumemo razliku između to dvoje. Složeni indeks je jednostavno običan indeks koji sadrži više od jedne kolone. Više kolona ključeva može se koristiti da bi se osiguralo da je svaki red u tabeli jedinstven, ili možete imati više kolona kako biste osigurali da je primarni ključ jedinstven, ili možda pokušavate optimizirati izvršavanje često pozivanih upita na više kolona. Općenito, međutim, što više ključnih kolona indeks sadrži, to će indeks biti manje efikasan, što znači da bi kompozitne indekse trebalo razumno koristiti.
Kao što je navedeno, upit može imati velike koristi ako se svi potrebni podaci odmah nalaze na listovima indeksa, baš kao i sam indeks. Ovo nije problem za klasterizovani indeks jer svi podaci su već tamo (zbog čega je toliko važno da pažljivo razmislite kada kreirate klasterizovani indeks). Ali negrupisani indeks na listovima sadrži samo ključne stupce. Da bi pristupio svim ostalim podacima, optimizator upita zahtijeva dodatne korake, koji mogu dodati značajne troškove izvršavanju vaših upita.
Tu u pomoć dolazi indeks pokrivanja. Kada definirate neklasterirani indeks, možete specificirati dodatne stupce svojim ključnim stupcima. Na primjer, recimo da vaša aplikacija često postavlja upite o podacima stupaca OrderID I OrderDate u tabeli Prodaja:
SELECT OrderID, OrderDate FROM Sales WHERE ID naloga = 12345;
Možete kreirati složeni negrupisani indeks na oba stupca, ali stupac OrderDate će samo dodati dodatne troškove održavanja indeksa bez da služi kao posebno korisna ključna kolona. Najbolje rješenje bi bilo kreirati indeks pokrivanja na ključnoj koloni OrderID i dodatno uključena kolona OrderDate:
CREATE NENCLUSTERED INDEX ix_orderid NA dbo.Sales(OrderID) INCLUDE (OrderDate);
Time se izbjegavaju nedostaci indeksiranja suvišnih kolona dok se i dalje održavaju prednosti pohranjivanja podataka u listovima prilikom pokretanja upita. Uključena kolona nije dio ključa, ali podaci se pohranjuju na lisni čvor, indeksni list. Ovo može poboljšati performanse upita bez ikakvih dodatnih troškova. Osim toga, stupci uključeni u indeks pokrivanja podliježu manjem ograničenju od ključnih stupaca indeksa.

Da li je važan broj duplikata u ključnoj koloni?

Kada kreirate indeks, morate pokušati smanjiti broj duplikata u ključnim stupcima. Ili preciznije: pokušajte da učestalost ponavljanja bude što niža.
Ako radite sa složenim indeksom, tada se dupliranje primjenjuje na sve ključne stupce u cjelini. Jedna kolona može sadržavati mnogo dupliciranih vrijednosti, ali bi trebalo biti minimalno ponavljanje među svim stupcima indeksa. Na primjer, kreirate složeni neklasterirani indeks na stupcima Ime I Prezime, možete imati mnogo John Doe vrijednosti i mnogo vrijednosti Doe, ali želite imati što manje vrijednosti John Doea, ili po mogućnosti samo jednu John Doe vrijednost.
Omjer jedinstvenosti vrijednosti ključne kolone naziva se selektivnost indeksa. Što je više jedinstvenih vrijednosti, veća je selektivnost: jedinstveni indeks ima najveću moguću selektivnost. Mehanizam upita zaista voli kolone sa visokim vrijednostima selektivnosti, posebno ako su te kolone uključene u klauzule WHERE vaših najčešće izvršavanih upita. Što je indeks selektivniji, to brže mašina za upite može smanjiti veličinu rezultirajućeg skupa podataka. Loša strana je, naravno, to što će stupci sa relativno malo jedinstvenih vrijednosti rijetko biti dobri kandidati za indeksiranje.

Da li je moguće kreirati negrupisani indeks samo na određenom podskupu podataka ključne kolone?

Prema zadanim postavkama, neklasterirani indeks sadrži jedan red za svaki red u tablici. Naravno, isto možete reći i o grupisanom indeksu, pod pretpostavkom da je takav indeks tabela. Ali kada je u pitanju indeks koji nije grupiran, odnos jedan-na-jedan je važan koncept jer, počevši od verzije SQL Server 2008, imate mogućnost kreiranja indeksa koji se može filtrirati i koji ograničava redove uključene u njega. Filtrirani indeks može poboljšati performanse upita jer... manja je po veličini i sadrži filtriranu, precizniju statistiku od svih tabelarnih - to dovodi do kreiranja poboljšanih planova izvršenja. Filtrirani indeks također zahtijeva manje prostora za skladištenje i niže troškove održavanja. Indeks se ažurira samo kada se promijene podaci koji odgovaraju filteru.
Osim toga, indeks koji se može filtrirati je lako kreirati. U operateru CREATE INDEX samo treba da ukažete GDJE stanje filtera. Na primjer, možete filtrirati sve redove koji sadrže NULL iz indeksa, kao što je prikazano u kodu:
CREATE NENCLUSTERED INDEX ix_trackingnumber NA Sales.SalesOrderDetail(CarrierTrackingNumber) GDJE CarrierTrackingNumber NIJE NULL;
U stvari, možemo filtrirati sve podatke koji nisu važni u kritičnim upitima. Ali budite oprezni, jer... SQL Server nameće nekoliko ograničenja na indekse koji se mogu filtrirati, kao što je nemogućnost kreiranja indeksa koji se može filtrirati na prikazu, pa pažljivo pročitajte dokumentaciju.
Takođe može biti da možete postići slične rezultate kreiranjem indeksiranog prikaza. Međutim, filtrirani indeks ima nekoliko prednosti, kao što je mogućnost smanjenja troškova održavanja i poboljšanja kvalitete vaših planova izvršenja. Filtrirani indeksi se također mogu ponovo izgraditi na mreži. Pokušajte ovo sa indeksiranim prikazom.

I opet malo od prevodioca

Svrha pojavljivanja ovog prijevoda na stranicama Habrahabra bila je da vas ispriča ili podsjeti na blog SimpleTalk od RedGate.
Objavljuje mnogo zabavnih i zanimljivih postova.
Nisam povezan sa proizvodima kompanije RedGate, niti njihovom prodajom.

Kao što sam obećao, knjige za one koji žele znati više
Preporučujem tri vrlo dobre knjige od sebe (linkovi vode do Kindle verzije u prodavnici Amazon):

U principu, možete otvoriti jednostavne indekse

za početnike

index

Dodaj oznake

Microsoft SQL Server 2012 T-SQL osnove (referenca za programere) Autor Itzik Ben-Gan Datum objave: 15. jul 2012 Autor, majstor svog zanata, daje osnovna znanja o radu sa bazama podataka. Ako ste sve zaboravili ili nikad niste znali, svakako vrijedi pročitati.

ROWID indeksi su objekti baze podataka koji pružaju prikaz svih vrijednosti u stupcu tablice, kao i ROWID-ove svih redova u tablici koji sadrže vrijednosti stupca.

ROWID je pseudo-stupac koji je jedinstveni identifikator za red u tabeli i zapravo opisuje tačnu fizičku lokaciju tog određenog reda. Na osnovu ovih informacija Oracle može naknadno pronaći podatke povezane s redom tablice. Svaki put kada se red pomiče, izvozi, uvozi ili bilo koja druga operacija koja mijenja njegovu lokaciju, ROWID linija jer zauzima drugačiju fizičku poziciju. Za skladištenje podataka ROWID Potrebno je 80 bita (10 bajtova). Identifikatori ROWID sastoji se od četiri komponente: broja objekta (32 bita), relativnog broja datoteke (10 bita), broja bloka (22 bita) i broja reda (16 bita). Ovi identifikatori se prikazuju kao sekvence od 18 znakova koje ukazuju na lokaciju podataka u bazi podataka, pri čemu je svaki znak predstavljen u formatu base-64 koji se sastoji od znakova A-Z, a-z, 0-9, + i /. Prvih šest znakova su broj objekta podataka, sljedeća tri su relativni broj datoteke, sljedećih šest su broj bloka, a posljednja tri su broj reda.

primjer:

ODABIR fam, ROWID OD studenta;

FAM ROWID

——————————————

IVANOV AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

U bazi podataka Oracle indeksi se koriste u različite svrhe: da osiguraju jedinstvenost vrijednosti u bazi podataka, da poboljšaju performanse pretraživanja zapisa u tabeli, itd. Performanse se poboljšavaju uključivanjem reference na indeksirani stupac ili stupce u kriterije pretraživanja za podatke u tabeli. IN Oracle indeksi se mogu kreirati na bilo kojoj koloni tabele osim DUGA kolona. Indeksi razlikuju aplikacije koje nisu osjetljive na brzinu i aplikacije visokih performansi, posebno kada se radi s velikim tablicama. Međutim, prije nego što se odlučite za kreiranje indeksa, morate odmjeriti prednosti i nedostatke u pogledu performansi sistema. Performanse se neće poboljšati ako jednostavno unesete indeks i zaboravite na njega.

Iako najveće poboljšanje performansi dolazi od kreiranja indeksa na stupcu gdje su sve vrijednosti jedinstvene, možete dobiti slične rezultate za stupce koji sadrže duple ili NULL vrijednosti. Nije neophodno da vrijednosti stupca budu jedinstvene za kreiranje indeksa. Evo nekoliko preporuka koje će vam pomoći da postignete željeno povećanje performansi kada koristite standardni indeks, a takođe ćemo razmotriti probleme u vezi sa ravnotežom između performansi i potrošnje prostora na disku prilikom kreiranja indeksa.

Korištenje indeksa za traženje informacija u tablicama može pružiti značajna poboljšanja performansi u odnosu na skeniranje tablica čije kolone nisu indeksirane. Međutim, odabir pravog indeksa nije nimalo lak. Naravno, kolona čije su sve vrijednosti jedinstvene je poželjna za indeksiranje s indeksom B-stabla, ali kolona koja ne ispunjava ove zahtjeve je dobar kandidat sve dok oko 10% njegovih redova sadrži identične vrijednosti i ne više. Stupci “Switch” ili “flag”, na primjer oni koji pohranjuju informacije o spolu osobe, nisu prikladni za indekse B-stabla. Kolone koje se koriste za pohranjivanje malog broja “pouzdanih vrijednosti”, kao i one koje pohranjuju određene vrijednosti, također nisu prikladne, onda znaci, na primjer, “pouzdanost” ili “nepouzdanost”, “aktivnost” ili “neaktivnost”, “da” ili “ne” itd., itd. Konačno, indeksi sa obrnutim ključevima su koristi se, po pravilu, tamo gde je instaliran i radi Oracle Paralelni server i potrebno je da povećate nivo paralelizma u bazi podataka do maksimuma.