Indeksi- to je prva stvar, ki jo morate pri svojem delu dobro razumeti SQL Server, vendar nenavadno osnovna vprašanja na forumih niso postavljena prav pogosto in ne dobijo veliko odgovorov.
Rob Sheldon odgovarja na ta vprašanja, ki povzročajo zmedo v strokovnih krogih glede indeksov v SQL Server: nekatere nas je preprosto sram vprašati, druge pa bomo najprej dvakrat premislili, preden jih vprašamo.

Uporabljena terminologija:

kazalo	kazalo
kup	kup
tabela	tabela
pogled	uspešnost
B-drevo	uravnoteženo drevo
gručasti indeks	gručasti indeks
negručasti indeks	negručasti indeks
sestavljeni indeks	sestavljeni indeks
pokrivni indeks	pokrivni indeks
omejitev primarnega ključa	omejitev primarnega ključa
edinstvena omejitev	omejitev edinstvenosti vrednot
poizvedbo	zahteva
poizvedovalni mehanizem	poizvedovalni podsistem
zbirka podatkov	zbirka podatkov
pogon baze podatkov	podsistem za shranjevanje
faktor polnjenja	faktor polnjenja indeksa
nadomestni primarni ključ	nadomestni primarni ključ
optimizator poizvedb	optimizator poizvedb
selektivnost indeksa	selektivnost indeksa
filtriran indeks	filtrirni indeks
izvedbeni načrt	izvedbeni načrt

Osnove indeksov v SQL Serverju.

Eden najpomembnejših načinov za doseganje visoke produktivnosti SQL Server je uporaba indeksov. Indeks pospeši postopek poizvedbe, saj omogoča hiter dostop do vrstic podatkov v tabeli, podobno kot vam kazalo v knjigi pomaga hitro najti informacije, ki jih potrebujete. V tem članku bom podal kratek pregled indeksov v SQL Server in pojasnite, kako so organizirani v bazi podatkov in kako pomagajo pospešiti poizvedbe po bazi podatkov.

Indeksi so ustvarjeni v stolpcih tabele in pogleda. Indeksi omogočajo hitro iskanje podatkov na podlagi vrednosti v teh stolpcih. Na primer, če ustvarite indeks na primarnem ključu in nato iščete vrstico podatkov z uporabo vrednosti primarnega ključa, potem SQL Server bo najprej našel vrednost indeksa in nato uporabil indeks za hitro iskanje celotne vrstice podatkov. Brez indeksa bo izveden popoln pregled vseh vrstic v tabeli, kar lahko pomembno vpliva na zmogljivost.
Ustvarite lahko indeks za večino stolpcev v tabeli ali pogledu. Izjema so predvsem stolpci s podatkovnimi tipi za shranjevanje velikih objektov ( LOB), kot npr slika, besedilo oz varchar(max). Prav tako lahko ustvarite indekse na stolpcih, namenjenih shranjevanju podatkov v formatu XML, vendar so ti indeksi strukturirani nekoliko drugače kot standardni in njihovo obravnavanje presega obseg tega članka. Poleg tega članek ne razpravlja columnstore indeksi. Namesto tega se osredotočam na tiste indekse, ki se najpogosteje uporabljajo v zbirkah podatkov SQL Server.
Indeks je sestavljen iz niza strani, indeksnih vozlišč, ki so organizirana v drevesni strukturi – uravnoteženo drevo. Ta struktura je hierarhične narave in se začne s korenskim vozliščem na vrhu hierarhije in listnimi vozlišči, listi, na dnu, kot je prikazano na sliki:

Ko poizvedujete po indeksiranem stolpcu, se poizvedovalni mehanizem začne na vrhu korenskega vozlišča in se prebija navzdol skozi vmesna vozlišča, pri čemer vsaka vmesna plast vsebuje podrobnejše informacije o podatkih. Mehanizem poizvedb se še naprej premika skozi vozlišča indeksa, dokler ne doseže spodnje ravni z listi indeksa. Če na primer iščete vrednost 123 v indeksiranem stolpcu, bo mehanizem poizvedb najprej določil stran na prvi vmesni ravni na korenski ravni. V tem primeru prva stran kaže na vrednost od 1 do 100, druga pa od 101 do 200, zato bo poizvedovalnik dostopal do druge strani te vmesne ravni. Nato boste videli, da bi morali obrniti na tretjo stran naslednje vmesne stopnje. Od tu bo poizvedovalni podsistem prebral vrednost samega indeksa na nižji ravni. Listi indeksa lahko vsebujejo same podatke tabele ali preprosto kazalec na vrstice s podatki v tabeli, odvisno od vrste indeksa: gručasti indeks ali negručasti indeks.

Clustered Index

Gručni indeks shranjuje dejanske vrstice podatkov v listih indeksa. Če se vrnemo k prejšnjemu primeru, to pomeni, da bo vrstica podatkov, povezana z vrednostjo ključa 123, shranjena v samem indeksu. Pomembna značilnost gručastega indeksa je, da so vse vrednosti razvrščene v določenem vrstnem redu, bodisi naraščajoče bodisi padajoče. Zato ima lahko tabela ali pogled samo en gručasti indeks. Poleg tega je treba upoštevati, da so podatki v tabeli shranjeni v razvrščeni obliki samo, če je bil v tej tabeli ustvarjen gručasti indeks.
Tabela, ki nima gručastega indeksa, se imenuje kopica.

Indeks brez gruč

Za razliko od gručastega indeksa listi negručastega indeksa vsebujejo samo tiste stolpce ( ključ), s katerim se določi ta indeks, vsebuje pa tudi kazalec na vrstice z realnimi podatki v tabeli. To pomeni, da sistem podpoizvedb zahteva dodatno operacijo za iskanje in pridobivanje zahtevanih podatkov. Vsebina podatkovnega kazalca je odvisna od tega, kako so podatki shranjeni: gručasta tabela ali kopica. Če kazalec kaže na gručasto tabelo, kaže na gručasti indeks, ki ga je mogoče uporabiti za iskanje dejanskih podatkov. Če se kazalec nanaša na kopico, potem kaže na določen identifikator podatkovne vrstice. Negručenih indeksov ni mogoče razvrstiti kot gručaste indekse, lahko pa ustvarite več kot en negručen indeks v tabeli ali pogledu, do 999. To ne pomeni, da bi morali ustvariti čim več indeksov. Indeksi lahko izboljšajo ali poslabšajo delovanje sistema. Poleg tega, da lahko ustvarite več negručastih indeksov, lahko vključite tudi dodatne stolpce ( vključen stolpec) v vaš indeks: listi indeksa ne bodo shranili le vrednosti samih indeksiranih stolpcev, ampak tudi vrednosti teh neindeksiranih dodatnih stolpcev. Ta pristop vam bo omogočil, da zaobidete nekatere omejitve, ki veljajo za indeks. Vključite lahko na primer stolpec, ki ga ni mogoče indeksirati, ali obidete omejitev dolžine indeksa (v večini primerov 900 bajtov).

Vrste indeksov

Poleg tega, da je indeks gručast ali negručen, ga je mogoče dodatno konfigurirati kot sestavljeni indeks, edinstven indeks ali pokrivni indeks.

Sestavljeni indeks

Takšen indeks lahko vsebuje več kot en stolpec. V indeks lahko vključite do 16 stolpcev, vendar je njihova skupna dolžina omejena na 900 bajtov. Tako gručasti kot negručasti indeksi so lahko sestavljeni.

Edinstveni indeks

Ta indeks zagotavlja, da je vsaka vrednost v indeksiranem stolpcu edinstvena. Če je indeks sestavljen, velja edinstvenost za vse stolpce v indeksu, ne pa za vsak posamezen stolpec. Na primer, če ustvarite edinstven indeks za stolpce IME in PRIIMEK, potem mora biti polno ime enolično, možni pa so dvojniki v imenu ali priimku.
Enolični indeks se samodejno ustvari, ko definirate omejitev stolpca: primarni ključ ali omejitev edinstvene vrednosti:

• Primarni ključ
  Ko določite omejitev primarnega ključa za enega ali več stolpcev, potem SQL Server samodejno ustvari unikaten gručasti indeks, če gručasti indeks še ni bil ustvarjen (v tem primeru se na primarnem ključu ustvari unikaten negručni indeks)
• Edinstvenost vrednot
  Ko določite omejitev za edinstvenost vrednosti, potem SQL Server samodejno ustvari edinstven negručast indeks. Določite lahko, da se ustvari enolični gručasti indeks, če v tabeli še ni bil ustvarjen noben gručni indeks

Pokrivni indeks

Takšen indeks omogoča, da posebna poizvedba takoj pridobi vse potrebne podatke iz listov indeksa brez dodatnega dostopa do zapisov same tabele.

Oblikovanje indeksov

Indeksi so še tako uporabni, vendar jih je treba skrbno oblikovati. Ker lahko indeksi zavzamejo veliko prostora na disku, ne želite ustvariti več indeksov, kot je potrebno. Poleg tega se indeksi samodejno posodobijo, ko se posodobi sama podatkovna vrstica, kar lahko privede do dodatnih stroškov virov in poslabšanja zmogljivosti. Pri oblikovanju indeksov je treba upoštevati več vidikov v zvezi z bazo podatkov in poizvedbami po njej.

Baza podatkov

Kot smo že omenili, lahko indeksi izboljšajo delovanje sistema, ker poizvedovalniku zagotavljajo hiter način iskanja podatkov. Vendar morate upoštevati tudi, kako pogosto nameravate vnašati, posodabljati ali brisati podatke. Ko spremenite podatke, je treba spremeniti tudi indekse, da odražajo ustrezna dejanja na podatkih, kar lahko znatno zmanjša zmogljivost sistema. Pri načrtovanju strategije indeksiranja upoštevajte naslednje smernice:

• Za tabele, ki se pogosto posodabljajo, uporabite čim manj indeksov.
• Če tabela vsebuje veliko količino podatkov, vendar so spremembe manjše, potem uporabite toliko indeksov, kot je potrebno, da izboljšate učinkovitost svojih poizvedb. Vendar dobro premislite, preden uporabite indekse na majhnih tabelah, ker ... Možno je, da iskanje po indeksu traja dlje kot preprosto pregledovanje vseh vrstic.
• Pri gručastih indeksih poskusite ohraniti polja čim krajša. Najboljši pristop je uporaba gručastega indeksa za stolpce, ki imajo edinstvene vrednosti in ne dovoljujejo NULL. Zato se primarni ključ pogosto uporablja kot gručni indeks.
• Edinstvenost vrednosti v stolpcu vpliva na uspešnost indeksa. Na splošno velja, da več kot imate dvojnikov v stolpcu, slabše deluje indeks. Po drugi strani pa več kot je edinstvenih vrednosti, boljša je uspešnost indeksa. Kadar koli je to mogoče, uporabite edinstven indeks.
• Pri sestavljenem indeksu upoštevajte vrstni red stolpcev v indeksu. Stolpci, ki se uporabljajo v izrazih KJE(na primer, WHERE FirstName = 'Charlie') mora biti prvi v indeksu. Naslednje stolpce je treba navesti glede na edinstvenost njihovih vrednosti (na prvem mestu so stolpci z največjim številom edinstvenih vrednosti).
• Določite lahko tudi indeks za izračunane stolpce, če izpolnjujejo določene zahteve. Na primer, izrazi, uporabljeni za pridobitev vrednosti stolpca, morajo biti deterministični (vedno vrnejo isti rezultat za dani niz vhodnih parametrov).

Poizvedbe po bazi podatkov

Pri načrtovanju indeksov je treba upoštevati še to, katere poizvedbe se izvajajo v bazi podatkov. Kot smo že omenili, morate upoštevati, kako pogosto se podatki spreminjajo. Poleg tega je treba uporabiti naslednja načela:

• Poskusite vstaviti ali spremeniti čim več vrstic v eni poizvedbi, namesto da to storite v več posameznih poizvedbah.
• Ustvarite negručasti indeks za stolpce, ki se pogosto uporabljajo kot iskalni izrazi v vaših poizvedbah. KJE in povezave v PRIDRUŽI SE.
• Razmislite o indeksiranju stolpcev, ki se uporabljajo v poizvedbah za iskanje vrstic za natančna ujemanja vrednosti.

Zakaj tabela ne more imeti dveh gručastih indeksov?

Želite kratek odgovor? Gručni indeks je tabela. Ko v tabeli ustvarite gručasti indeks, mehanizem za shranjevanje razvrsti vse vrstice v tabeli v naraščajočem ali padajočem vrstnem redu glede na definicijo indeksa. Gručni indeks ni ločena entiteta kot drugi indeksi, temveč mehanizem za razvrščanje podatkov v tabeli in omogočanje hitrega dostopa do podatkovnih vrstic.
Predstavljajmo si, da imate tabelo, ki vsebuje zgodovino prodajnih transakcij. Tabela Prodaja vključuje informacije, kot so ID naročila, položaj izdelka v naročilu, številka izdelka, količina izdelka, številka in datum naročila itd. Ustvarite gručasti indeks na stolpcih OrderID in LineID, razvrščenih v naraščajočem vrstnem redu, kot je prikazano spodaj T-SQL koda:

USTVARI UNIQUE CLUSTERED INDEX ix_oriderid_lineid ON dbo.Sales(OrderID, LineID);

Ko zaženete ta skript, bodo vse vrstice v tabeli fizično razvrščene najprej po stolpcu OrderID in nato po LineID, vendar bodo sami podatki ostali v enem samem logičnem bloku, tabeli. Iz tega razloga ne morete ustvariti dveh indeksov v gručah. Obstaja lahko samo ena tabela z enimi podatki in ta tabela je lahko razvrščena samo enkrat v določenem vrstnem redu.

Če gručasta tabela nudi številne prednosti, zakaj potem uporabljati kopico?

imaš prav Gručaste tabele so odlične in večina vaših poizvedb bo bolje delovala na tabelah, ki imajo gručast indeks. Toda v nekaterih primerih boste morda želeli pustiti mize v njihovem naravnem, neokrnjenem stanju, tj. v obliki kopice in ustvarite samo negručne indekse, da se vaše poizvedbe izvajajo.
Kopica, kot se spomnite, shranjuje podatke v naključnem vrstnem redu. Običajno podsistem za shranjevanje dodaja podatke v tabelo v vrstnem redu, v katerem so vstavljeni, vendar podsistem za shranjevanje rad tudi premika vrstice za učinkovitejše shranjevanje. Posledično ne morete predvideti, v kakšnem vrstnem redu bodo podatki shranjeni.
Če mora poizvedovalni mehanizem najti podatke brez prednosti negručenega indeksa, bo izvedel popoln pregled tabele, da bi našel vrstice, ki jih potrebuje. Na zelo majhnih mizah to običajno ni problem, a ko se kopica poveča, zmogljivost hitro pade. Seveda lahko negručasti indeks pomaga z uporabo kazalca na datoteko, stran in vrstico, kjer so shranjeni zahtevani podatki - to je običajno veliko boljša alternativa skeniranju tabele. Kljub temu je težko primerjati prednosti gručastega indeksa, če upoštevamo zmogljivost poizvedbe.
Vendar lahko kup pomaga izboljšati zmogljivost v določenih situacijah. Predstavljajte si tabelo z veliko vstavitvami, a malo posodobitvami ali izbrisanji. Na primer, tabela, ki shranjuje dnevnik, se uporablja predvsem za vstavljanje vrednosti, dokler ni arhivirana. Na kopici ne boste videli stranjenja in fragmentacije podatkov, kot bi to storili z indeksom v gručah, ker so vrstice preprosto dodane na konec kopice. Preveč razdeljenih strani lahko pomembno vpliva na učinkovitost, a ne v dobrem smislu. Na splošno vam kopica omogoča relativno neboleče vstavljanje podatkov in ne bo vam treba ukvarjati s stroški shranjevanja in vzdrževanja, kot bi se morali ukvarjati z indeksom v gručah.
Vendar pomanjkanje posodabljanja in brisanja podatkov ne sme biti edini razlog. Pomemben dejavnik je tudi način vzorčenja podatkov. Na primer, kopice ne smete uporabljati, če pogosto poizvedujete po obsegih podatkov ali če je treba podatke, po katerih poizvedujete, pogosto razvrstiti ali združiti.
Vse to pomeni, da morate o uporabi kopice razmisliti le, ko delate z zelo majhnimi tabelami ali če je vsa vaša interakcija s tabelo omejena na vstavljanje podatkov in so vaše poizvedbe izjemno preproste (in uporabljate negručne indekse vseeno). V nasprotnem primeru se držite dobro zasnovanega gručastega indeksa, kot je tisti, ki je definiran na preprostem naraščajočem ključnem polju, kot je pogosto uporabljen stolpec z IDENTITETA.

Kako spremenim privzeti faktor polnjenja indeksa?

Spreminjanje privzetega faktorja polnjenja indeksa je ena stvar. Razumevanje delovanja privzetega razmerja je druga stvar. Toda najprej naredite nekaj korakov nazaj. Faktor polnjenja indeksa določa količino prostora na strani za shranjevanje indeksa na spodnji ravni (raven listov), ​​preden se začne polnjenje nove strani. Na primer, če je koeficient nastavljen na 90, potem ko indeks raste, bo zasedel 90% strani in se nato premaknil na naslednjo stran.
Privzeto je vključena vrednost faktorja polnjenja indeksa SQL Server je 0, kar je enako kot 100. Posledično vsi novi indeksi samodejno podedujejo to nastavitev, razen če v kodi izrecno podate vrednost, ki se razlikuje od sistemske standardne vrednosti, ali spremenite privzeto vedenje. Lahko uporabite SQL Server Management Studio da prilagodite privzeto vrednost ali zaženete sistemsko shranjeno proceduro sp_configure. Na primer, naslednji niz T-SQL ukazi nastavi vrednost koeficienta na 90 (najprej morate preklopiti v način naprednih nastavitev):

EXEC sp_configure "pokaži napredne možnosti ", 1; POJDI ZNOVA KONFIGURIRAJ; POJDI IZVEDI sp_configure " faktor polnjenja", 90; GO RECONFIGURE; GO

Po spremembi vrednosti faktorja polnjenja indeksa morate znova zagnati storitev SQL Server. Zdaj lahko preverite nastavljeno vrednost tako, da zaženete sp_configure brez podanega drugega argumenta:

EXEC sp_configure "faktor polnjenja" GO

Ta ukaz bi moral vrniti vrednost 90. Posledično bodo vsi na novo ustvarjeni indeksi uporabljali to vrednost. To lahko preizkusite tako, da ustvarite indeks in poizvedujete za vrednost faktorja polnjenja:

UPORABA AdventureWorks2012; -- svojo bazo podatkov POJDI CREATE NENCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); POJDI IZBERI fill_factor FROM sys .indexes WHERE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

V tem primeru smo ustvarili negručast indeks v tabeli oseba v bazi podatkov AdventureWorks 2012. Po izdelavi indeksa lahko pridobimo vrednost faktorja polnjenja iz sistemskih tabel sys.indexes. Poizvedba bi morala vrniti 90.
Predstavljajmo si, da smo izbrisali indeks in ga znova ustvarili, zdaj pa smo določili določeno vrednost faktorja polnjenja:

CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); POJDI IZBERI fill_factor FROM sys .indexes WHERE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

Tokrat smo dodali navodila Z in možnost faktor polnjenja za našo operacijo ustvarjanja indeksa USTVARI INDEKS in podali vrednost 80. Operator IZBERI zdaj vrne ustrezno vrednost.
Doslej je bilo vse precej preprosto. V celotnem procesu se lahko resnično opečete, ko ustvarite indeks, ki uporablja privzeto vrednost koeficienta, ob predpostavki, da to vrednost poznate. Na primer, nekdo se poigrava z nastavitvami strežnika in je tako trmast, da je faktor polnjenja indeksa nastavil na 20. Medtem pa vi nadaljujete z ustvarjanjem indeksov, ob predpostavki, da je privzeta vrednost 0. Na žalost ne morete ugotoviti polnila faktor, dokler ne ustvarite indeksa in nato preverite vrednost, kot smo storili v naših primerih. V nasprotnem primeru boste morali počakati na trenutek, ko se zmogljivost poizvedb toliko zmanjša, da začnete nekaj sumiti.
Druga težava, ki se je morate zavedati, je vnovična izgradnja indeksov. Tako kot pri izdelavi indeksa lahko podate vrednost faktorja polnjenja indeksa, ko ga znova sestavite. Za razliko od ukaza create index pa rebuild ne uporablja privzetih nastavitev strežnika, ne glede na to, kako se morda zdi. Še več, če izrecno ne določite vrednosti faktorja polnjenja indeksa SQL Server bo uporabil vrednost koeficienta, s katerim je ta indeks obstajal pred njegovim prestrukturiranjem. Na primer naslednja operacija SPREMENI INDEKS ponovno zgradi indeks, ki smo ga pravkar ustvarili:

ALTER INDEX ix_people_lastname ON Person.Person REBUILD; POJDI IZBERI fill_factor FROM sys .indexes WHERE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

Ko preverimo vrednost faktorja polnjenja, bomo dobili vrednost 80, ker smo to določili, ko smo nazadnje ustvarili indeks. Privzeta vrednost je prezrta.
Kot lahko vidite, spreminjanje vrednosti faktorja polnjenja indeksa ni tako težko. Veliko težje je poznati trenutno vrednost in razumeti, kdaj je uporabljena. Če vedno določite koeficient pri ustvarjanju in ponovni gradnji indeksov, potem vedno poznate določen rezultat. Razen če vas mora skrbeti, da nekdo drug spet ne pokvari nastavitev strežnika, zaradi česar bodo vsi indeksi ponovno izdelani s smešno nizkim faktorjem polnjenja indeksa.

Ali je mogoče ustvariti gručasti indeks v stolpcu, ki vsebuje dvojnike?

Da in ne. Da, ustvarite lahko gručasti indeks na ključnem stolpcu, ki vsebuje podvojene vrednosti. Ne, vrednost ključnega stolpca ne more ostati v needinstvenem stanju. Naj pojasnim. Če v stolpcu ustvarite needinstven gručasti indeks, mehanizem za shranjevanje podvojeni vrednosti doda poenotelnik, da zagotovi edinstvenost in tako lahko identificira vsako vrstico v gručasti tabeli.
Na primer, lahko se odločite ustvariti gručasti indeks v stolpcu, ki vsebuje podatke o strankah LastName ohranitev priimka. Stolpec vsebuje vrednosti Franklin, Hancock, Washington in Smith. Nato znova vstavite vrednosti Adams, Hancock, Smith in Smith. Toda vrednost stolpca ključa mora biti edinstvena, zato bo mehanizem za shranjevanje spremenil vrednost dvojnikov, tako da bodo videti nekako takole: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 in Smith5678.
Na prvi pogled se ta pristop zdi v redu, vendar celoštevilska vrednost poveča velikost ključa, kar lahko postane težava, če obstaja veliko število dvojnikov, te vrednosti pa bodo postale osnova negručenega indeksa ali tujega ključna referenca. Zaradi teh razlogov morate vedno poskušati ustvariti edinstvene indekse v gručah, kadar koli je to mogoče. Če to ni mogoče, poskusite vsaj uporabiti stolpce z zelo visoko vsebino edinstvene vrednosti.

Kako je tabela shranjena, če ni bil ustvarjen gručasti indeks?

SQL Server podpira dve vrsti tabel: gručaste tabele, ki imajo gručast indeks, in kopične tabele ali samo kopice. Za razliko od gručastih tabel podatki na kupu niso razvrščeni na noben način. V bistvu je to kup (kup) podatkov. Če v takšno tabelo dodate vrstico, jo bo mehanizem za shranjevanje preprosto dodal na konec strani. Ko bo stran napolnjena s podatki, bodo dodani na novo stran. V večini primerov boste želeli ustvariti gručast indeks v tabeli, da boste izkoristili razvrščanje in hitrost poizvedb (poskusite si predstavljati, da iščete telefonsko številko v nerazvrščenem imeniku). Če pa se odločite, da ne boste ustvarili gručastega indeksa, lahko še vedno ustvarite negručasti indeks na kopici. V tem primeru bo imela vsaka indeksna vrstica kazalec na vrstico kopice. Indeks vključuje ID datoteke, številko strani in številko podatkovne vrstice.

Kakšno je razmerje med omejitvami edinstvenosti vrednosti in primarnim ključem z indeksi tabele?

Primarni ključ in edinstvena omejitev zagotavljata, da so vrednosti v stolpcu edinstvene. Za tabelo lahko ustvarite samo en primarni ključ, ki ne more vsebovati vrednosti NULL. Ustvarite lahko več omejitev glede edinstvenosti vrednosti za tabelo in vsaka od njih ima lahko en zapis z NULL.
Ko ustvarite primarni ključ, mehanizem za shranjevanje ustvari tudi edinstven indeks v gručah, če indeks v gručah še ni bil ustvarjen. Vendar pa lahko preglasite privzeto vedenje in ustvarjen bo negručast indeks. Če ob ustvarjanju primarnega ključa obstaja indeks v gručah, bo ustvarjen edinstveni indeks brez gruč.
Ko ustvarite edinstveno omejitev, mehanizem za shranjevanje ustvari edinstven, negručen indeks. Vendar pa lahko določite ustvarjanje edinstvenega indeksa v gručah, če še niste bili ustvarjeni.
Na splošno sta omejitev edinstvene vrednosti in edinstveni indeks ista stvar.

Zakaj se gručasti in negručasti indeksi v SQL Serverju imenujejo B-drevo?

Osnovni indeksi v SQL Serverju, v gručah ali brez njih, so porazdeljeni po naborih strani, imenovanih indeksna vozlišča. Te strani so organizirane v določeni hierarhiji z drevesno strukturo, imenovano uravnoteženo drevo. Na zgornji ravni je korensko vozlišče, na dnu so listna vozlišča z vmesnimi vozlišči med zgornjim in spodnjim nivojem, kot je prikazano na sliki:

Korensko vozlišče zagotavlja glavno vstopno točko za poizvedbe, ki poskušajo pridobiti podatke prek indeksa. Od tega vozlišča poizvedovalni mehanizem sproži navigacijo po hierarhični strukturi do ustreznega listnega vozlišča, ki vsebuje podatke.
Na primer, predstavljajte si, da je bila prejeta zahteva za izbiro vrstic, ki vsebujejo vrednost ključa 82. Poizvedbeni podsistem začne delovati iz korenskega vozlišča, ki se nanaša na ustrezno vmesno vozlišče, v našem primeru 1-100. Iz vmesnega vozlišča 1-100 poteka prehod v vozlišče 51-100, od tam pa v končno vozlišče 76-100. Če je to indeks v gručah, potem list vozlišča vsebuje podatke vrstice, povezane s ključem, ki je enak 82. Če je to indeks brez gruč, potem list indeksa vsebuje kazalec na tabelo v gručah ali določeno vrstico v kup.

Kako lahko indeks celo izboljša zmogljivost poizvedbe, če morate prečkati vsa ta vozlišča indeksa?

Prvič, indeksi ne izboljšajo vedno učinkovitosti. Preveč nepravilno ustvarjenih indeksov spremeni sistem v močvirje in poslabša zmogljivost poizvedb. Natančneje je reči, da lahko indeksi, če jih previdno uporabimo, zagotovijo znatno povečanje učinkovitosti.
Pomislite na ogromno knjigo, posvečeno uglaševanju zmogljivosti SQL Server(papirnata, ne elektronska). Predstavljajte si, da želite najti informacije o konfiguriranju regulatorja virov. S prstom lahko vlečete stran za stranjo skozi celotno knjigo ali pa odprete kazalo in ugotovite točno številko strani z iskanimi informacijami (pod pogojem, da je knjiga pravilno indeksirana in ima vsebina pravilna kazala). To vam bo zagotovo prihranilo precej časa, čeprav morate najprej dostopati do popolnoma drugačne strukture (indeksa), da dobite informacije, ki jih potrebujete iz primarne strukture (knjige).
Kot knjižno kazalo, kazalo v SQL Server omogoča izvajanje natančnih poizvedb po podatkih, ki jih potrebujete, namesto popolnega skeniranja vseh podatkov v tabeli. Pri majhnih tabelah popoln pregled običajno ni težava, vendar velike tabele zavzamejo veliko strani podatkov, kar lahko povzroči precejšen čas izvajanja poizvedbe, razen če obstaja indeks, ki omogoča mehanizmu poizvedb, da takoj pridobi pravilno lokacijo podatkov. Predstavljajte si, da se izgubite na večnivojskem križišču pred veliko metropolo brez zemljevida in dobili boste idejo.

Če so indeksi tako odlični, zakaj ne bi preprosto ustvarili enega za vsak stolpec?

Nobeno dobro dejanje ne sme ostati nekaznovano. Vsaj pri indeksih je tako. Seveda indeksi delujejo odlično, dokler izvajate poizvedbe za pridobivanje operaterja IZBERI, a takoj, ko se začnejo pogosti klici operaterjem VSTAVITE, POSODOBITEV in IZBRIŠI, zato se pokrajina zelo hitro spreminja.
Ko sprožite zahtevo po podatkih s strani operaterja IZBERI, poizvedovalni mehanizem najde indeks, se premika po njegovi drevesni strukturi in odkrije podatke, ki jih išče. Kaj bi lahko bilo bolj preprosto? Toda stvari se spremenijo, če sprožite izjavo o spremembi, kot je POSODOBITEV. Da, za prvi del izjave lahko poizvedovalni mehanizem spet uporabi indeks za iskanje vrstice, ki se spreminja - to je dobra novica. In če pride do preproste spremembe podatkov v vrstici, ki ne vpliva na spremembe v ključnih stolpcih, bo postopek spreminjanja popolnoma neboleč. Toda kaj, če sprememba povzroči razdelitev strani, ki vsebujejo podatke, ali se spremeni vrednost ključnega stolpca, zaradi česar se ta premakne v drugo vozlišče indeksa – zaradi tega bo indeks verjetno potreboval reorganizacijo, ki bo vplivala na vse povezane indekse in operacije , kar ima za posledico obsežno zmanjšanje produktivnosti.
Podobni procesi se zgodijo pri klicu operaterja IZBRIŠI. Indeks lahko pomaga najti podatke, ki se brišejo, vendar lahko brisanje samih podatkov povzroči prerazporeditev strani. Glede operaterja VSTAVITE, glavni sovražnik vseh indeksov: začnete dodajati veliko količino podatkov, kar povzroči spremembe v indeksih in njihovo reorganizacijo, pri čemer trpijo vsi.
Zato upoštevajte vrste poizvedb v vaši bazi podatkov, ko razmišljate o vrsti indeksov in koliko ustvariti. Več ne pomeni boljše. Pred dodajanjem novega indeksa v tabelo upoštevajte stroške ne samo osnovnih poizvedb, ampak tudi količino porabljenega prostora na disku, stroške vzdrževanja funkcionalnosti in indeksov, kar lahko povzroči domino učinek na druge operacije. Vaša strategija oblikovanja indeksa je eden najpomembnejših vidikov vaše implementacije in mora vključevati veliko premislekov, od velikosti indeksa, števila edinstvenih vrednosti do vrste poizvedb, ki jih bo indeks podpiral.

Ali je treba ustvariti gručasti indeks v stolpcu s primarnim ključem?

Indeks v gručah lahko ustvarite v katerem koli stolpcu, ki izpolnjuje zahtevane pogoje. Res je, da sta gručasti indeks in omejitev primarnega ključa ustvarjena drug za drugega in se ujemata v nebesih, zato razumejte dejstvo, da ko ustvarite primarni ključ, bo samodejno ustvarjen gručni indeks, če še ni bil ustvarjen prej. Lahko pa se odločite, da bi indeks v gručah deloval bolje drugje, in pogosto bo vaša odločitev upravičena.
Glavni namen gručastega indeksa je razvrstiti vse vrstice v vaši tabeli glede na ključni stolpec, določen pri definiranju indeksa. To omogoča hitro iskanje in enostaven dostop do podatkov tabele.
Primarni ključ tabele je lahko dobra izbira, saj enolično identificira vsako vrstico v tabelah, ne da bi bilo treba dodati dodatne podatke. V nekaterih primerih bo najboljša izbira nadomestni primarni ključ, ki ni samo edinstven, ampak tudi majhen in katerega vrednosti se zaporedno povečujejo, zaradi česar so negručasti indeksi, ki temeljijo na tej vrednosti, učinkovitejši. Optimizatorju poizvedb je prav tako všeč ta kombinacija gručastega indeksa in primarnega ključa, ker je združevanje tabel hitrejše od združevanja na drug način, ki ne uporablja primarnega ključa in z njim povezanega gručastega indeksa. Kot sem rekel, je to tekma v nebesih.
Nazadnje pa velja omeniti, da je pri ustvarjanju gručastega indeksa treba upoštevati več vidikov: koliko negručastih indeksov bo temeljilo na njem, kako pogosto se bo spreminjala vrednost stolpca ključnega indeksa in kako velik. Ko se vrednosti v stolpcih gručastega indeksa spremenijo ali indeks ne deluje po pričakovanjih, lahko to vpliva na vse druge indekse v tabeli. Indeks v gručah mora temeljiti na najbolj obstojnem stolpcu, katerega vrednosti naraščajo v določenem vrstnem redu, vendar se ne spreminjajo naključno. Indeks mora podpirati poizvedbe glede na najpogosteje dostopane podatke tabele, tako da poizvedbe v celoti izkoristijo dejstvo, da so podatki razvrščeni in dostopni v korenskih vozliščih, listih indeksa. Če primarni ključ ustreza temu scenariju, ga uporabite. Če ne, izberite drug niz stolpcev.

Kaj če indeksirate pogled, ali je še vedno pogled?

Pogled je navidezna tabela, ki generira podatke iz ene ali več tabel. V bistvu gre za poimenovano poizvedbo, ki pridobi podatke iz osnovnih tabel, ko poizvedujete po tem pogledu. Zmogljivost poizvedb lahko izboljšate tako, da v tem pogledu ustvarite gručast indeks in negručne indekse, podobno kot ustvarite indekse v tabeli, vendar je glavno opozorilo, da najprej ustvarite gručast indeks, nato pa lahko ustvarite negručastega.
Ko je ustvarjen indeksiran pogled (materializiran pogled), potem sama definicija pogleda ostane ločena entiteta. Navsezadnje je to le trdo kodiran operater IZBERI, shranjeno v bazi podatkov. Indeks pa je povsem druga zgodba. Ko pri ponudniku ustvarite gručast ali negručast indeks, se podatki fizično shranijo na disk, tako kot običajni indeks. Poleg tega se ob spremembi podatkov v osnovnih tabelah samodejno spremeni indeks pogleda (to pomeni, da se boste morda želeli izogniti indeksiranju pogledov na tabelah, ki se pogosto spreminjajo). V vsakem primeru pogled ostaja pogled - pogled na tabele, vendar v tem trenutku izveden, z ustreznimi indeksi.
Preden lahko ustvarite indeks v pogledu, mora izpolnjevati več omejitev. Pogled se lahko na primer sklicuje samo na osnovne tabele, ne pa tudi na druge poglede, te tabele pa morajo biti v isti bazi podatkov. Pravzaprav obstaja veliko drugih omejitev, zato preverite dokumentacijo za SQL Server za vse umazane podrobnosti.

Zakaj uporabljati pokrivni indeks namesto sestavljenega indeksa?

Najprej se prepričajmo, da razumemo razliko med obema. Sestavljeni indeks je preprosto navaden indeks, ki vsebuje več kot en stolpec. Uporabite lahko več stolpcev s ključi, da zagotovite, da je vsaka vrstica v tabeli edinstvena, ali imate morda več stolpcev, da zagotovite, da je primarni ključ edinstven, ali pa poskušate optimizirati izvajanje pogosto zagnanih poizvedb v več stolpcih. Na splošno velja, da več ključnih stolpcev kot vsebuje indeks, manj učinkovit bo indeks, kar pomeni, da je treba sestavljene indekse uporabljati preudarno.
Kot rečeno, lahko poizvedba zelo koristi, če se vsi zahtevani podatki takoj nahajajo na listih indeksa, tako kot sam indeks. To ni problem za gručasti indeks, ker vsi podatki so že tam (zato je tako pomembno, da dobro premislite, ko ustvarite indeks v gručah). Toda negručasti indeks na listih vsebuje samo ključne stolpce. Za dostop do vseh drugih podatkov optimizator poizvedb zahteva dodatne korake, ki lahko povzročijo precejšnje stroške pri izvajanju vaših poizvedb.
Tu na pomoč priskoči indeks kritja. Ko definirate negručni indeks, lahko podate dodatne stolpce k svojim ključnim stolpcem. Na primer, recimo, da vaša aplikacija pogosto poizveduje po podatkih stolpcev OrderID in Datum naročila v tabeli Prodaja:

SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345 ;

V obeh stolpcih lahko ustvarite sestavljen indeks brez gruč, vendar bo stolpec OrderDate samo dodal dodatne stroške vzdrževanja indeksa, ne da bi služil kot posebno uporaben ključni stolpec. Najboljša rešitev bi bila ustvariti pokrivni indeks na ključnem stolpcu OrderID in dodatno vključen stolpec Datum naročila:

CREATE NENCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);

S tem se izognemo pomanjkljivostim indeksiranja odvečnih stolpcev, hkrati pa ohranimo prednosti shranjevanja podatkov v listih pri izvajanju poizvedb. Vključen stolpec ni del ključa, vendar so podatki shranjeni točno na listnem vozlišču, indeksnem listu. To lahko izboljša zmogljivost poizvedb brez dodatnih stroškov. Poleg tega za stolpce, vključene v pokrivni indeks, velja manj omejitev kot za ključne stolpce indeksa.

Ali je število dvojnikov v ključnem stolpcu pomembno?

Ko ustvarite indeks, morate poskusiti zmanjšati število dvojnikov v ključnih stolpcih. Ali natančneje: poskušajte ohraniti čim manjšo stopnjo ponavljanja.
Če delate s sestavljenim indeksom, se podvajanje nanaša na vse ključne stolpce kot celoto. Posamezen stolpec lahko vsebuje veliko podvojenih vrednosti, vendar mora biti med vsemi stolpci indeksa minimalno ponavljanje. Ustvarite na primer sestavljeni negručasti indeks na stolpcih FirstName in LastName, lahko imate veliko vrednosti John Doe ​​in veliko vrednosti Doe, vendar želite imeti čim manj vrednosti John Doe ali po možnosti samo eno vrednost John Doe.
Razmerje edinstvenosti vrednosti ključnega stolpca se imenuje selektivnost indeksa. Več kot je edinstvenih vrednosti, večja je selektivnost: edinstveni indeks ima največjo možno selektivnost. Mehanizem poizvedb ima zelo rad stolpce z visokimi selektivnimi vrednostmi, še posebej, če so ti stolpci vključeni v člene WHERE vaših najpogosteje izvedenih poizvedb. Bolj ko je indeks selektiven, hitreje lahko poizvedovalni mehanizem zmanjša velikost nastalega niza podatkov. Slaba stran je seveda ta, da bodo stolpci z relativno malo edinstvenimi vrednostmi le redko dobri kandidati za indeksiranje.

Ali je mogoče ustvariti negručasti indeks samo na določeni podnaboru podatkov ključnega stolpca?

Privzeto vsebuje negručni indeks eno vrstico za vsako vrstico v tabeli. Seveda lahko isto rečete za gručasti indeks, ob predpostavki, da je tak indeks tabela. Ko pa gre za indeks brez gruč, je razmerje ena proti ena pomemben koncept, ker se začne z različico SQL Server 2008, imate možnost ustvariti indeks, ki ga je mogoče filtrirati in omejuje vrstice, vključene v njem. Filtrirani indeks lahko izboljša učinkovitost poizvedb, ker ... je manjše velikosti in vsebuje filtrirane, natančnejše statistike kot vse tabelarične – to vodi k ustvarjanju izboljšanih izvedbenih načrtov. Filtriran indeks zahteva tudi manj prostora za shranjevanje in nižje stroške vzdrževanja. Indeks se posodobi le, ko se spremenijo podatki, ki ustrezajo filtru.
Poleg tega je enostavno ustvariti indeks, ki ga je mogoče filtrirati. V operaterju USTVARI INDEKS morate le navesti v KJE stanje filtra. Na primer, iz indeksa lahko filtrirate vse vrstice, ki vsebujejo NULL, kot je prikazano v kodi:

CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber IS NOT NULL;

Pravzaprav lahko filtriramo vse podatke, ki niso pomembni pri kritičnih poizvedbah. Vendar bodite previdni, saj... SQL Server nalaga več omejitev indeksom, ki jih je mogoče filtrirati, kot je nezmožnost ustvarjanja indeksa, ki ga je mogoče filtrirati, v pogledu, zato natančno preberite dokumentacijo.
Mogoče je tudi, da lahko dosežete podobne rezultate z ustvarjanjem indeksiranega pogleda. Vendar ima filtrirani indeks več prednosti, kot je možnost zmanjšanja stroškov vzdrževanja in izboljšanje kakovosti vaših izvedbenih načrtov. Filtrirane indekse je mogoče obnoviti tudi na spletu. Poskusite to z indeksiranim pogledom.

6. Indeksi in optimizacija delovanja

Indeksi v bazah podatkov: namen, vpliv na delovanje, principi izdelave indeksov

6.1 Čemu služijo indeksi?

Indeksi so posebne strukture v zbirkah podatkov, ki omogočajo hitrejše iskanje in razvrščanje po določenem polju ali nizu polj v tabeli, uporabljajo pa se tudi za zagotavljanje unikatnosti podatkov. Kazala najlažje primerjamo s kazali v knjigah. Če kazala ni, bomo morali pregledati celotno knjigo, da bi našli pravo mesto, z indeksom pa je isto dejanje mogoče izvesti veliko hitreje.

Običajno je več indeksov, boljša je zmogljivost poizvedb po bazi podatkov. Če pa se število indeksov pretirano poveča, se učinkovitost operacij spreminjanja podatkov (vstavljanje/sprememba/brisanje) zmanjša in poveča velikost baze podatkov, zato je treba dodajanje indeksov obravnavati previdno.

Nekaj ​​splošnih načel, povezanih z ustvarjanjem indeksov:

· indekse je treba ustvariti za stolpce, ki se uporabljajo v spojih, ki se pogosto uporabljajo za operacije iskanja in razvrščanja. Upoštevajte, da se indeksi vedno samodejno ustvarijo za stolpce, za katere velja omejitev primarnega ključa. Najpogosteje so ustvarjeni za stolpce s tujim ključem (v Accessu - samodejno);

· indeks mora biti samodejno ustvarjen za stolpce, za katere velja omejitev edinstvenosti;

· Najbolje je ustvariti indekse za tista polja, v katerih je minimalno število ponavljajočih se vrednosti in so podatki enakomerno porazdeljeni. Oracle ima posebne bitne indekse za stolpce z velikim številom podvojenih vrednosti; SQL Server in Access ne nudita te vrste indeksa;

· če se iskanje stalno izvaja po določenem naboru stolpcev (hkrati), je v tem primeru morda smiselno ustvariti sestavljeni indeks (samo v SQL Serverju) - en indeks za skupino stolpcev;

· Ko se tabele spremenijo, se indeksi, ki so prekriti s to tabelo, samodejno spremenijo. Posledično je lahko indeks zelo razdrobljen, kar vpliva na zmogljivost. Občasno preverite stopnjo razdrobljenosti indeksov in jih defragmentirajte. Pri nalaganju večje količine podatkov je včasih smiselno najprej izbrisati vse indekse in jih po končani operaciji znova ustvariti;

· indekse je mogoče ustvariti ne samo za tabele, ampak tudi za poglede (samo v SQL Serverju). Prednosti - možnost izračuna polj ne v času zahteve, ampak v trenutku, ko se v tabelah pojavijo nove vrednosti.

Ta članek obravnava indekse in njihovo vlogo pri optimizaciji časa izvajanja poizvedbe. Prvi del članka obravnava različne oblike indeksov in kako jih shraniti. Nato preučimo tri glavne stavke Transact-SQL, ki se uporabljajo za delo z indeksi: CREATE INDEX, ALTER INDEX in DROP INDEX. Nato se upošteva razdrobljenost indeksov njegovega vpliva na delovanje sistema. Nato nudi nekaj splošnih smernic za ustvarjanje indeksov in opisuje več posebnih vrst indeksov.

Splošne informacije

Sistemi baz podatkov običajno uporabljajo indekse za zagotavljanje hitrega dostopa do relacijskih podatkov. Indeks je ločena fizična podatkovna struktura, ki omogoča hiter dostop do ene ali več vrstic podatkov. Tako je pravilna nastavitev indeksov ključni vidik izboljšanja zmogljivosti poizvedb.

Kazalo zbirke podatkov je v marsičem podobno kazalu (abecednemu kazalu) knjige. Ko moramo v knjigi na hitro najti temo, najprej pogledamo v kazalo, katere strani knjige obravnavajo to temo, nato pa takoj odpremo želeno stran. Podobno, ko išče določeno vrstico v tabeli, Database Engine dostopa do indeksa, da poišče njeno fizično lokacijo.

Toda med indeksom knjig in indeksom baze podatkov sta dve bistveni razliki:

Bralec knjige ima možnost, da se sam odloči, ali bo kazalo uporabil v vsakem konkretnem primeru ali ne. Uporabnik baze podatkov te možnosti nima, to odločitev pa namesto njega sprejme sistemska komponenta, imenovana optimizator poizvedb. (Uporabnik lahko manipulira z uporabo indeksov prek indeksnih namigov, vendar so ti namigi priporočljivi za uporabo le v omejenem številu posebnih primerov.)

Indeks za določen delovni zvezek je ustvarjen skupaj z delovnim zvezkom, nato pa ni več spremenjen. To pomeni, da bo kazalo za določeno temo vedno kazalo na isto številko strani. Nasprotno pa se lahko indeks baze podatkov spremeni vsakič, ko se spremenijo ustrezni podatki.

Če tabela nima ustreznega indeksa, sistem za pridobivanje vrstic uporabi metodo skeniranja tabele. Izraz skeniranje tabele pomeni, da sistem zaporedno pridobi in pregleda vsako vrstico tabele (od prve do zadnje) in postavi vrstico v nabor rezultatov, če izpolnjuje iskalni pogoj v klavzuli WHERE. Tako so vse vrstice pridobljene glede na njihovo fizično lokacijo v pomnilniku. Ta metoda je manj učinkovita kot dostop z uporabo indeksov, kot je razloženo spodaj.

Indeksi so shranjeni v dodatnih strukturah baze podatkov, imenovanih indeksne strani. Za vsako indeksirano vrstico obstaja vpis v kazalo, ki je shranjen na indeksni strani. Vsak element indeksa je sestavljen iz ključa indeksa in indeksa. Zato je element indeksa bistveno krajši od vrstice tabele, na katero kaže. Zaradi tega je število indeksnih elementov na vsaki indeksni strani veliko večje od števila vrstic na podatkovni strani.

Ta lastnost indeksov je zelo pomembna, ker je število V/I operacij, potrebnih za prehod po indeksnih straneh, znatno manjše od števila V/I operacij, potrebnih za prehod po ustreznih podatkovnih straneh. Z drugimi besedami, skeniranje tabele bi verjetno zahtevalo veliko več V/I operacij kot skeniranje indeksa tabele.

Indeksi Database Engine so ustvarjeni z uporabo drevesne podatkovne strukture B+. Drevo B+ ima drevesno strukturo, v kateri so vsa spodnja vozlišča enako število ravni oddaljena od vrha (korenskega vozlišča) drevesa. Ta lastnost se ohrani tudi, ko so podatki dodani ali odstranjeni iz indeksiranega stolpca.

Spodnja slika prikazuje drevesno strukturo B+ za tabelo Employee in neposreden dostop do vrstice v tej tabeli z vrednostjo 25348 za stolpec Id. (Predvidevamo, da je tabela Employee indeksirana s stolpcem Id.) Na tej sliki lahko vidite tudi, da je drevo B+ sestavljeno iz korenskega vozlišča, drevesnih vozlišč in nič ali več vmesnih vozlišč:

V tem drevesu lahko iščete vrednost 25348 na naslednji način. Začne pri korenu drevesa in išče najmanjšo vrednost ključa, ki je večja ali enaka zahtevani vrednosti. Tako bo v korenskem vozlišču ta vrednost 29346, zato se izvede prehod na vmesno vozlišče, povezano s to vrednostjo. V tem vozlišču vrednost 28559 izpolnjuje navedene zahteve, zaradi česar se izvede prehod v drevesno vozlišče, povezano s to vrednostjo. To vozlišče vsebuje želeno vrednost 25348. Ko določimo zahtevani indeks, lahko z ustreznimi kazalci izvlečemo njegovo vrstico iz podatkovne tabele. (Drugi enakovredni pristop bi bil iskanje vrednosti, ki je manjša ali enaka indeksu.)

Indeksirano iskanje je običajno prednostna metoda za iskanje tabel z velikim številom vrstic zaradi očitnih prednosti. Z uporabo indeksiranega iskanja lahko najdemo katero koli vrstico v tabeli v zelo kratkem času z le nekaj V/I operacijami. In zaporedno iskanje (tj. skeniranje tabele od prve do zadnje vrstice) traja več časa, bolj ko je zahtevana vrstica oddaljena.

V naslednjih razdelkih si bomo ogledali dve obstoječi vrsti indeksov, v gručah in brez njih, in se naučili, kako ustvariti indekse.

Gručasti indeksi

Clustered Index določa fizični vrstni red podatkov v tabeli. Database Engine vam omogoča, da ustvarite samo en gručasti indeks za tabelo, ker Vrstic v tabeli ni mogoče fizično razvrstiti na več kot en način. Iskanje z uporabo gručastega indeksa se izvaja od korenskega vozlišča drevesa B+ proti vozliščem v drevesu, ki so med seboj povezana v dvojno povezanem seznamu, imenovanem veriga strani.

Pomembna lastnost gručastega indeksa je, da njegova drevesna vozlišča vsebujejo strani s podatki. (Vse druge ravni gručastih indeksnih vozlišč vsebujejo indeksne strani.) Tabela, ki ima definiran gručast indeks (bodisi eksplicitno ali implicitno), se imenuje tabela z gručami. Drevesna struktura B+ gručastega indeksa je prikazana na spodnji sliki:

Gručni indeks je privzeto ustvarjen v vsaki tabeli, ki ima primarni ključ, določen z omejitvijo primarnega ključa. Poleg tega je vsak indeks v gručah privzeto edinstven, tj. V stolpcu, ki ima definiran indeks v gručah, se lahko vsaka podatkovna vrednost pojavi samo enkrat. Če je gručasti indeks ustvarjen v stolpcu, ki vsebuje podvojene vrednosti, sistem baze podatkov uveljavi nedvoumnost z dodajanjem štiribajtnega identifikatorja vrsticam, ki vsebujejo podvojene vrednosti.

Indeksi v gručah zagotavljajo zelo hiter dostop do podatkov, ko poizvedba išče obseg vrednosti.

Negručasti indeksi

Struktura negručastega indeksa je popolnoma enaka strukturi gručastega indeksa, vendar z dvema pomembnima razlikama:

negručasti indeks ne spremeni fizičnega vrstnega reda vrstic tabele;

Strani indeksnih vozlišč brez gruče so sestavljene iz ključev indeksa in zaznamkov.

Če v tabeli definirate enega ali več negručastih indeksov, se fizični vrstni red vrstic tabele ne bo spremenil. Za vsak negručasti indeks Database Engine ustvari dodatno strukturo indeksa, ki je shranjena na straneh indeksa. Drevesna struktura B+ negručastega indeksa je prikazana na spodnji sliki:

Zaznamek v negručnem indeksu označuje, kje se nahaja vrstica, ki ustreza ključu indeksa. Komponenta zaznamka ključa indeksa je lahko dveh vrst, odvisno od tega, ali je tabela gručasta tabela ali kopica. (V terminologiji strežnika SQL Server je kopica tabela brez gručastega indeksa.) Če gručasti indeks obstaja, je na zavihku negručastega indeksa prikazano drevo B+ gručastega indeksa tabele. Če tabela nima gručastega indeksa, je zaznamek enak identifikator vrstice (RID - Row Identifier), ki je sestavljen iz treh delov: naslova datoteke, v kateri je shranjena tabela, naslova fizičnega bloka (strani), v katerem je shranjena vrstica, in odmika vrstice na strani.

Kot smo že omenili, lahko iskanje podatkov z uporabo negručenega indeksa izvedemo na dva različna načina, odvisno od vrste tabele:

kopica - prečka iskalno strukturo negručastega indeksa, po katerem se vrstica pridobi z uporabo identifikatorja vrstice;

gručasta tabela – iskalno prečkanje negručne strukture indeksa, ki mu sledi prečkanje ustreznega gručastega indeksa.

V obeh primerih je količina V/I operacij precej velika, zato morate previdno oblikovati negručasti indeks in ga uporabljati le, če ste prepričani, da bo njegova uporaba znatno izboljšala zmogljivost.

Jezik Transact-SQL in indeksi

Zdaj, ko smo seznanjeni s fizično strukturo indeksov, si bomo v tem razdelku ogledali, kako ustvariti, spremeniti in izbrisati indekse ter kako pridobiti informacije o fragmentaciji indeksa in urediti informacije indeksa. Vse to nas bo pripravilo na nadaljnjo razpravo o uporabi indeksov za izboljšanje zmogljivosti sistema.

Ustvarjanje indeksov

Indeks v tabeli se ustvari z uporabo stavka USTVARI INDEKS. To navodilo ima naslednjo sintakso:

CREATE INDEX index_name ON table_name (column1 ,...) [ INCLUDE (column_name [ ,... ]) ] [[, ] PAD_INDEX = (ON | OFF)] [[, ] DROP_EXISTING = (ON | OFF)] [[ , ] SORT_IN_TEMPDB = (ON | OFF)] [[, ] IGNORE_DUP_KEY = (ON | OFF)] [[, ] ALLOW_ROW_LOCKS = (ON | OFF)] [[, ] ALLOW_PAGE_LOCKS = (ON | OFF)] [[, ] STATISTICS_NORECOMPUTE = (ON | OFF)] [[, ] ONLINE = (ON | OFF)]] Pravila sintakse

Parameter index_name podaja ime indeksa, ki ga želite ustvariti. Indeks je mogoče ustvariti v enem ali več stolpcih ene tabele, ki jo identificira parameter table_name. Stolpec, v katerem je ustvarjen indeks, je določen s parametrom column1. Številska pripona tega parametra označuje, da je indeks mogoče ustvariti v več stolpcih tabele. Database Engine podpira tudi ustvarjanje indeksov na pogledih.

Indeksirate lahko kateri koli stolpec tabele. To pomeni, da je mogoče indeksirati tudi stolpce, ki vsebujejo vrednosti podatkovnega tipa VARBINARY(max), BIGINT in SQL_VARIANT.

Indeks je lahko preprost ali sestavljen. Preprost indeks je ustvarjen v enem stolpcu, medtem ko je sestavljeni indeks ustvarjen v več stolpcih. Sestavljeni indeks ima določene omejitve glede velikosti in števila stolpcev. Indeks ima lahko največ 900 bajtov in največ 16 stolpcev.

UNIQUE parameter določa, da lahko indeksirani stolpec vsebuje samo vrednosti z eno vrednostjo (to je, ki se ne ponavljajo). V sestavljenem indeksu z eno vrednostjo mora biti edinstvena kombinacija vrednosti vseh stolpcev vsake vrstice. Če ključna beseda UNIQUE ni podana, so dovoljene podvojene vrednosti v indeksiranih stolpcih.

Parameter CLUSTERED podaja gručasti indeks in parameter NONCLUSTERED(privzeto) določa, da indeks ne spremeni vrstnega reda vrstic v tabeli. Database Engine omogoča največ 249 negručenih indeksov v tabeli.

Database Engine je bil izboljšan tako, da podpira indekse z padajočim vrstnim redom vrednosti stolpcev. Parameter ASC za imenom stolpca določa, da je indeks ustvarjen z naraščajočim vrstnim redom vrednosti stolpca, parameter DESC pa padajoči vrstni red vrednosti stolpca indeksa. To zagotavlja večjo prilagodljivost pri uporabi indeksa. Z padajočim vrstnim redom bi morali ustvariti sestavljene indekse v stolpcih, katerih vrednosti so razvrščene v nasprotnih smereh.

Parameter INCLUDE Omogoča vam, da določite neključne stolpce, ki se dodajo na strani vozlišča negručenega indeksa. Imena stolpcev na seznamu INCLUDE se ne smejo ponavljati in stolpca ni mogoče uporabiti kot ključni in neključni stolpec.

Če želite resnično razumeti uporabnost parametra INCLUDE, morate razumeti, kaj je pokrivni indeks. Če so vsi stolpci poizvedbe vključeni v indeks, lahko dobite pomembne izboljšave zmogljivosti, ker Optimizator poizvedb lahko poišče vse vrednosti stolpcev na straneh indeksa brez dostopa do podatkov v tabeli. Ta zmožnost se imenuje pokrivni indeks ali pokrivna poizvedba. Zato vam bo vključitev dodatnih neključnih stolpcev v strani vozlišč indeksa brez gruč omogočila pridobitev več poizvedb pokritosti in znatno izboljšala njihovo zmogljivost.

Parameter FILLFACTOR določa odstotek vsake strani v indeksu v času, ko je ustvarjen. Vrednost parametra FILLFACTOR lahko nastavite v razponu od 1 do 100. Pri vrednosti n=100 je vsaka indeksna stran zapolnjena do 100 %, tj. obstoječa stran z vozliščem in stran brez vozlišča ne bodo imeli prostega prostora za vstavljanje novih vrstic. Zato je priporočljivo, da to vrednost uporabite samo za statične tabele. (Privzeta vrednost, n=0, pomeni, da so strani indeksnih vozlišč polne in vsaka od vmesnih strani vsebuje prosti prostor za en vnos.)

Če je parameter FILLFACTOR nastavljen na vrednosti med 1 in 99, bodo strani vozlišč ustvarjene strukture indeksa vsebovale prosti prostor. Večja kot je vrednost n, manj je prostega prostora na straneh indeksnih vozlišč. Na primer, pri n=60 bo imela vsaka stran indeksnega vozlišča 40 % prostega prostora za prihodnje vstavljanje vrstic indeksa. (Indeksne vrstice so vstavljene s stavkom INSERT ali UPDATE.) Tako bi bila vrednost n=60 razumna za tabele, katerih podatki se dokaj pogosto spreminjajo. Za vrednosti FILLFACTOR med 1 in 99 vsebujejo vmesne indeksne strani prosti prostor za vsak vnos.

Ko je indeks ustvarjen, vrednost FILLFACTOR med uporabo ni podprta. Z drugimi besedami, označuje samo količino prostora, rezerviranega z razpoložljivimi podatki, ko nastavljate odstotek nezasedenega prostora. Če želite obnoviti parameter FILLFACTOR na prvotno vrednost, uporabite stavek ALTER INDEX.

Parameter PAD_INDEX je tesno povezan s parametrom FILLFACTOR. Parameter FILLFACTOR v bistvu podaja količino prostega prostora kot odstotek skupne velikosti strani vozlišč indeksa. In parameter PAD_INDEX podaja, da vrednost parametra FILLFACTOR velja tako za strani indeksa kot za strani s podatki v indeksu.

DROP_EXISTING parameter Omogoča izboljšanje zmogljivosti pri reprodukciji gručastega indeksa v tabeli, ki ima tudi negručasti indeks. Za več informacij glejte spodnji razdelek »Obnova indeksa«.

parameter SORT_IN_TEMPDB uporablja se za postavitev podatkov iz operacij vmesnega razvrščanja, uporabljenih pri ustvarjanju indeksa, v sistemsko bazo podatkov tempdb. To lahko izboljša zmogljivost, če se tempdb nahaja na drugem disku kot podatki.

parameter IGNORE_DUP_KEY Omogoča sistemu, da prezre poskus vstavljanja podvojenih vrednosti v indeksirane stolpce. To možnost uporabite le, da preprečite prekinitev dolgotrajne transakcije, ko stavek INSERT vstavi podvojene podatke v indeksirani stolpec. Ko je ta možnost omogočena, ko stavek INSERT poskuša vstaviti vrstice v tabelo, ki kršijo edinstvenost indeksa, sistem zbirke podatkov preprosto izda opozorilo, namesto da bi zrušil celoten stavek. V tem primeru Database Engine ne vstavi vrstic s podvojenimi vrednostmi ključev, ampak jih preprosto ignorira in doda pravilne vrstice. Če ta parameter ni nastavljen, se bo izvedba celotnega ukaza nenormalno končala.

kdaj parameter ALLOW_ROW_LOCKS aktivirano (nastavljeno na vklopljeno), sistem uporabi zaklepanje vrstic. Enako, ko je aktiviran parameter ALLOW_PAGE_LOCKS, sistem uporabi zaklepanje strani med sočasnim dostopom. Parameter STATISTICS_NORECOMPUTE določa stanje samodejnega ponovnega izračuna statistike za navedeni indeks.

Aktivirano ONLINE parameter omogoča ustvarjanje, ponovno ustvarjanje in brisanje indeksa v pogovornem načinu. Ta možnost vam omogoča istočasno spreminjanje podatkov glavne tabele ali gručastega indeksa in vseh povezanih indeksov med spreminjanjem indeksa. Na primer, medtem ko se indeks v gručah znova gradi, lahko še naprej posodabljate njegove podatke in izvajate poizvedbe za te podatke.

Parameter VKLOP ustvari podani indeks v privzeti datotečni skupini (privzeta vrednost) ali podani datotečni skupini (vrednost datotečne_skupine).

Spodnji primer prikazuje, kako ustvariti negručast indeks v stolpcu Id tabele Employee:

UPORABA SampleDb; CREATE INDEX ix_empid ON Employee(Id);

Ustvarjanje sestavljenega indeksa z eno vrednostjo je prikazano v spodnjem primeru:

UPORABA SampleDb; CREATE UNIQUE INDEX ix_empid_prnu ON Works_on (EmpId, ProjectNumber) WITH FILLFACTOR= 80;

V tem primeru morajo biti vrednosti v vsakem stolpcu enomestne. Ko je indeks ustvarjen, je zapolnjenih 80 % prostora na vsaki strani vozlišča indeksa.

Za stolpec ne morete ustvariti edinstvenega indeksa, če stolpec vsebuje podvojene vrednosti. Takšen indeks je mogoče ustvariti samo, če se vsaka vrednost (vključno z vrednostmi NULL) pojavi natanko enkrat v stolpcu. Poleg tega bo sistem zavrnil vsak poskus vstavljanja ali spreminjanja obstoječe podatkovne vrednosti v stolpec, ki je vključen v obstoječem edinstvenem indeksu, če je vrednost podvojena.

Pridobivanje informacij o fragmentaciji indeksa

Med življenjsko dobo indeksa lahko postane razdrobljen, zaradi česar je postopek shranjevanja podatkov na straneh indeksa neučinkovit. Obstajata dve vrsti fragmentacije indeksa: notranja fragmentacija in zunanja fragmentacija. Notranja fragmentacija določa količino podatkov, shranjenih na vsaki strani, medtem ko do zunanje fragmentacije pride, ko strani niso v logičnem redu.

Za pridobitev informacij o fragmentaciji notranjega indeksa se kliče pogled dinamičnega upravljanja DMV sys.dm_db_index_physical_stats. Ta DMV vrne informacije o obsegu in razdrobljenosti podatkov in indeksov določene strani. Za vsako stran je vrnjena ena vrstica za vsako raven drevesa B+. Z uporabo tega DMV lahko pridobite informacije o stopnji razdrobljenosti vrstic na podatkovnih straneh, na podlagi katerih se lahko odločite, ali želite reorganizirati podatke.

Uporaba pogleda sys.dm_db_index_physical_stats je prikazana v spodnjem primeru. (Preden zaženete paketni primer, morate opustiti vse obstoječe indekse v tabeli Works_on. Če želite opustiti indekse, uporabite stavek DROP INDEX, ki je prikazan pozneje.)

UPORABA SampleDb; DECLARE @dbId INT; DECLARE @tabId INT; DECLARE @indId INT; SET @dbId = DB_ID("SampleDb"); SET @tabId = OBJECT_ID("Zaposleni"); IZBERITE avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (@dbId, @tabId, NULL, NULL, NULL);

Kot lahko vidite iz primera, ima pogled sys.dm_db_index_physical_stats pet parametrov. Prvi trije parametri določajo ID-je trenutne baze podatkov, tabele oziroma indeksa. Četrti parameter določa ID particije, zadnji parameter pa raven skeniranja, ki se uporablja za pridobivanje statističnih informacij. (Privzeto vrednost za določen parameter lahko podate z vrednostjo NULL.)

Najpomembnejša stolpca v tem pogledu sta stolpca avg_fragmentation_in_percent in avg_page_space_used_in_percent. Prvi označuje povprečno stopnjo razdrobljenosti v odstotkih, drugi pa določa količino zasedenega prostora v odstotkih.

Urejanje informacij o indeksu

Ko se seznanite z informacijami o fragmentaciji indeksa, kot je opisano v prejšnjem razdelku, lahko uredite te in druge informacije indeksa z naslednjimi sistemskimi orodji:

pogledi imenikov sys.indexes;

pogledi kataloga sys.index_columns;

sistemska procedura sp_helpindex;

lastnosti lastnosti objekta;

okolje za upravljanje SQL Server Management Studio;

Pogled dinamičnega upravljanja DMV sys.dm_db_index_usage_stats;

Pogled dinamičnega upravljanja DMV sys.dm_db_missing_index_details.

Pogled kataloga sys.indexes vsebuje vrstico za vsak indeks in vrstico za vsako tabelo brez gručastega indeksa. Najpomembnejši stolpci tega pogleda kataloga so stolpci object_id, name in index_id. Stolpec object_id vsebuje ime objekta baze podatkov, ki je lastnik indeksa, stolpca name in index_id pa vsebujeta ime oziroma ID tega indeksa.

Pogled kataloga sys.index_columns vsebuje vrstico za vsak stolpec, ki je del indeksa ali kopice. Te informacije je mogoče uporabiti v povezavi z informacijami, pridobljenimi prek kataloškega pogleda sys.indexes, da pridobite dodatne informacije o lastnostih navedenega indeksa.

Sistemski postopek sp_helpindex vrne informacije o indeksih tabel in statistične podatke za stolpce. Ta postopek ima naslednjo sintakso:

sp_helpindex [@db_object = ] "ime"

Tu spremenljivka @db_object predstavlja ime tabele.

V zvezi z indeksi, funkcija lastnosti objekta ima dve lastnosti: IsIndexed in IsIndexable. Prva lastnost zagotavlja informacije o tem, ali ima tabela ali pogled indeks, druga lastnost pa označuje, ali je tabelo ali pogled mogoče indeksirati.

Če želite urediti obstoječe informacije o indeksu s programom SQL Server Management Studio, izberite želeno bazo podatkov v mapi Databases, razširite vozlišče Tables in v tem vozlišču razširite želeno tabelo in njeno mapo Indexes. V mapi Indeksi tabele bo prikazan seznam vseh obstoječih indeksov za to tabelo. Dvoklik na indeks bo odprl pogovorno okno Lastnosti indeksa z lastnostmi tega indeksa. (Prav tako lahko ustvarite nov indeks ali izbrišete obstoječega z uporabo programa Management Studio.)

Učinkovitost sys.dm_db_index_usage_stats vrne število različnih vrst operacij indeksa in čas, ko je bila posamezna vrsta operacije nazadnje izvedena. Vsaka ločena operacija iskanja, iskanja ali posodabljanja na določenem indeksu v eni poizvedbi se šteje za uporabo indeksa in poveča ustrezni števec v tem DMV za eno. Na ta način lahko dobite splošne informacije o tem, kako pogosto se indeks uporablja, tako da lahko na podlagi tega ugotovite, kateri indeksi se uporabljajo bolj in kateri manj.

Učinkovitost sys.dm_db_missing_index_details Vrne podrobne informacije o stolpcih tabele, za katere ni indeksov. Najpomembnejša stolpca tega DMV sta stolpca index_handle in object_id. Vrednost v prvem stolpcu določa določen manjkajoči indeks, vrednost v drugem stolpcu pa tabelo, v kateri indeks manjka.

Spreminjanje indeksov

Database Engine je eden redkih sistemov baz podatkov, ki podpira to izjavo SPREMENI INDEKS. Ta stavek se lahko uporablja za izvajanje operacij vzdrževanja indeksa. Sintaksa stavka ALTER INDEX je zelo podobna sintaksi stavka CREATE INDEX. Z drugimi besedami, ta stavek vam omogoča spreminjanje vrednosti parametrov ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY in STATISTICS_NORECOMPUTE, ki so bili opisani prej v stavku CREATE INDEX.

Poleg zgornjih možnosti stavek ALTER INDEX podpira še tri druge možnosti:

Parameter REBUILD, ki se uporablja za ponovno ustvarjanje indeksa;

Parameter REORGANIZE, ki se uporablja za reorganizacijo strani indeksnih vozlišč;

DISABLE parameter, ki se uporablja za onemogočanje indeksa. Te tri možnosti so obravnavane v naslednjih pododdelkih.

Ponovna izdelava indeksa

Vsaka sprememba podatkov z uporabo stavkov INSERT, UPDATE ali DELETE lahko povzroči razdrobljenost podatkov. Če so ti podatki indeksirani, je možna tudi fragmentacija indeksa, pri čemer so informacije indeksa razpršene po različnih fizičnih straneh. Zaradi fragmentacije podatkov indeksa bo motor baze podatkov morda prisiljen izvesti dodatne operacije branja podatkov, kar zmanjša splošno zmogljivost sistema. V tem primeru morate PONOVNO ZGRADITI vse fragmentirane indekse.

To je mogoče storiti na dva načina:

prek parametra REBUILD stavka ALTER INDEX;

prek parametra DROP_EXISTING stavka CREATE INDEX.

Parameter REBUILD se uporablja za ponovno gradnjo indeksov. Če namesto imena indeksa za ta parameter podate VSE, bodo znova ustvarjeni vsi indeksi v tabeli. (Če omogočite dinamično ponovno ustvarjanje indeksov, vam jih ne bo treba pustiti in znova ustvarjati.)

Možnost DROP_EXISTING stavka CREATE INDEX lahko izboljša zmogljivost pri vnovičnem ustvarjanju gručastega indeksa v tabeli, ki ima tudi negručne indekse. Določa, da je treba obstoječi gručasti ali negručasti indeks opustiti in podani indeks znova ustvariti. Kot smo že omenili, vsak negručasti indeks v gručasti tabeli vsebuje v svojih drevesnih vozliščih ustrezne vrednosti gručastega indeksa tabele. Iz tega razloga, ko spustite gručasti indeks v tabelo, morate znova ustvariti vse njegove negručne indekse. Z uporabo parametra DROP_EXISTING se izognete ponovnemu ustvarjanju negručastih indeksov.

Možnost DROP_EXISTING je močnejša od možnosti REBUILD, ker je bolj prilagodljiva in ponuja več možnosti, kot je spreminjanje stolpcev, ki sestavljajo indeks, in spreminjanje negručenega indeksa v gručastega.

Reorganizacija strani indeksnih vozlišč

Parameter REORGANIZE stavka ALTER INDEX reorganizira strani vozlišč v podanem indeksu, tako da se fizični vrstni red strani ujema z njihovim logičnim vrstnim redom, od leve proti desni. To odstrani določeno količino razdrobljenosti indeksa in izboljša učinkovitost indeksa.

Onemogoči indeks

Možnost DISABLE onemogoči navedeni indeks. Onemogočen indeks ni na voljo za uporabo, dokler ni znova omogočen. Upoštevajte, da se onemogočen indeks ne spremeni, ko se spremenijo povezani podatki. Zaradi tega je treba za ponovno uporabo onemogočenega indeksa v celoti ponovno ustvariti. Če želite omogočiti onemogočen indeks, uporabite možnost REBUILD stavka ALTER TABLE.

Ko je gručasti indeks v tabeli onemogočen, podatki tabele ne bodo dostopni, ker so vse podatkovne strani tabele z gručnim indeksom shranjene v njenih drevesnih vozliščih.

Odstranjevanje in preimenovanje indeksov

Če želite odstraniti indekse v trenutni bazi podatkov, uporabite Navodilo DROP INDEX. Upoštevajte, da je spuščanje gručastega indeksa v tabeli lahko operacija, ki zahteva zelo veliko virov, ker Vse negručne indekse bo treba znova ustvariti. (Vsi negručasti indeksi uporabljajo indeksni ključ gručastega indeksa kot kazalec na svojih straneh vozlišča.) Uporaba stavka DROP INDEX za izbris indeksa je prikazana v spodnjem primeru:

UPORABA SampleDb; DROP INDEX ix_empid ON Employee;

Navodilo DROP INDEX ima dodatno PREMAKNI NA parameter, katerega pomen je enak kot parameter ON stavka CREATE INDEX. Z drugimi besedami, s tem parametrom lahko podate, kam premakniti podatkovne vrstice, ki so na straneh indeksnih vozlišč v gručah. Podatki se premaknejo na novo lokacijo kot kup. Za novo lokacijo shranjevanja podatkov lahko določite privzeto datotečno skupino ali poimenovano datotečno skupino.

Stavka DROP INDEX ni mogoče uporabiti za izbris indeksov, ki jih implicitno izdela sistem za omejitve integritete, kot so indeksi PRIMARY KEY in UNIQUE. Če želite odstraniti takšne indekse, morate odstraniti ustrezno omejitev.

Indekse lahko preimenujete s sistemsko proceduro sp_rename.

Indekse je mogoče ustvariti, spremeniti in izbrisati tudi v programu Management Studio z uporabo diagramov baze podatkov ali raziskovalca objektov. Toda najlažji način je uporaba mape Indeksi zahtevane tabele. Upravljanje indeksov v programu Management Studio je podobno upravljanju tabel v programu Management Studio.

Čeprav Database Engine praktično ne omejuje števila indeksov, obstaja nekaj razlogov, zakaj bi morali omejiti število. Prvič, vsak indeks zasede določeno količino prostora na disku, tako da obstaja možnost, da skupno število strani indeksa baze podatkov preseže število podatkovnih strani v bazi podatkov. Drugič, v nasprotju s koristjo uporabe indeksa za pridobivanje podatkov vstavljanje in brisanje podatkov ne zagotavlja takšne koristi zaradi potrebe po vzdrževanju indeksa. Več kot ima tabela indeksov, več dela je potrebno za njeno reorganizacijo. Splošno pravilo je, da je pametno izbrati indekse za pogoste poizvedbe in nato oceniti njihovo uporabo.

V tem razdelku je na voljo nekaj smernic za ustvarjanje in uporabo indeksov. Naslednja priporočila so le splošna pravila. Navsezadnje bo njihova učinkovitost odvisna od tega, kako se baza podatkov uporablja v praksi in vrste poizvedb, ki se najpogosteje izvajajo. Indeksiranje stolpca, ki ne bo nikoli uporabljen, ne bo koristilo.

Indeksi in pogoji stavka WHERE

Če klavzula WHERE stavka SELECT vsebuje iskalni pogoj z enim stolpcem, je treba ustvariti indeks v tem stolpcu. To je še posebej priporočljivo v pogojih visoke selektivnosti. S selektivnostjo pogoja razumemo razmerje med številom vrstic, ki izpolnjujejo pogoj, in skupnim številom vrstic v tabeli. Visoka selektivnost ustreza nižji vrednosti tega razmerja. Obdelava iskanja z uporabo indeksiranega stolpca bo najuspešnejša, če je selektivnost pogoja manjša od 5 %.

Stolpca ne bi smeli indeksirati, če je stopnja selektivnosti pogoja konstantna pri 80 % ali več. V tem primeru bodo indeksne strani zahtevale dodatne V/I operacije, kar bo zmanjšalo vse prihranke časa, dosežene z uporabo indeksov. V tem primeru je hitreje izvesti iskanje s skeniranjem tabele, kar običajno izbere optimizator poizvedb, zaradi česar postane indeks neuporaben.

Če iskalni pogoj pogosto uporabljene poizvedbe vsebuje operatorje IN, je najbolje, da ustvarite sestavljeni indeks za vse stolpce tabele, podane v členu WHERE stavka SELECT. Ustvarjanje takega sestavljenega indeksa je prikazano v spodnjem primeru:

Ta primer ustvari sestavljeni indeks za vse stolpce stavka WHERE. V tej poizvedbi sta dva pogoja združena z IN, zato bi morali v teh pogojih ustvariti sestavljen negručen indeks v obeh stolpcih.

Indeksi in operator pridružitve

Za operacijo združevanja je priporočljivo ustvariti indeks za vsak stolpec, ki se združuje. Stolpci, ki so združeni, pogosto predstavljajo primarni ključ ene tabele in ustrezen tuji ključ druge tabele. Če podate omejitve celovitosti PRIMARY KEY in FOREIGN KEY za ustrezne spojne stolpce, ustvarite samo negručni indeks v stolpcu tujega ključa, ker sistem bo implicitno ustvaril gručasti indeks v stolpcu primarnega ključa.

Spodnji primer prikazuje, kako ustvariti indekse, ki bi bili uporabljeni, če bi imeli poizvedbo z operacijo združevanja in dodatnim filtrom:

Pokrivni indeks

Kot smo že omenili, lahko vključitev vseh stolpcev poizvedbe v indeks znatno izboljša zmogljivost poizvedbe. Ustvarjanje takšnega indeksa, imenovanega pokrivanje, je prikazano v spodnjem primeru:

UPORABA AdventureWorks2012; GO DROP INDEX Person.Address.IX_Address_StateProvinceID; GO CREATE INDEX ix_address_zip ON Person.Address(PostalCode) INCLUDE(City, StateProvinceID); POJDI SELECT City, StateProvinceID FROM Person.Address WHERE PostalCode = 84407;

Ta primer najprej odstrani indeks IX_Address_StateProvinceID iz tabele naslovov. Nato se ustvari nov indeks, ki poleg stolpca PostalCode vključuje še dva dodatna stolpca. Končno stavek SELECT na koncu primera prikazuje poizvedbo, ki jo pokriva indeks. Za to poizvedbo sistemu ni treba iskati podatkov na straneh s podatki, ker lahko optimizator poizvedb najde vse vrednosti stolpcev na straneh vozlišč indeksa brez gruč.

Priporočeni so pokrivni indeksi, ker indeksne strani običajno vsebujejo veliko več vnosov kot ustrezne podatkovne strani. Poleg tega morajo biti za uporabo te metode stolpci, ki se filtrirajo, prvi ključni stolpci v indeksu.

Indeksi na izračunanih stolpcih

Database Engine vam omogoča ustvarjanje naslednjih posebnih vrst indeksov:

indeksirani pogledi;

indeksi, ki jih je mogoče filtrirati;

indeksi na izračunanih stolpcih;

particionirani indeksi;

indeksi obstojnosti stolpcev;

XML indeksi;

kazala celotnega besedila.

Ta razdelek obravnava izračunane stolpce in njihove povezane indekse.

Izračunani stolpec je stolpec tabele, v katerem so shranjeni rezultati izračunov podatkov tabele. Takšen stolpec je lahko navidezen ali obstojen. Ti dve vrsti stolpcev sta obravnavani v naslednjih pododdelkih.

Virtualni izračunani stolpci

Izračunani stolpec, ki nima ustreznega gručastega indeksa, je logični stolpec, tj. ni fizično shranjen na trdem disku. Tako se ovrednoti vsakič, ko se dostopa do vrstice. Uporaba navideznih izračunanih stolpcev je prikazana v spodnjem primeru:

UPORABA SampleDb; CREATE TABLE Naročila (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate));

Tabela Naročila v tem primeru ima dva virtualna izračunana stolpca: skupno in datum odpreme. Stolpec skupnega zneska se izračuna z uporabo dveh drugih stolpcev, cene in količine, stolpec z datumom odpreme pa se izračuna s funkcijo DATEADD in stolpcem z datumom naročila.

Konstantni izračunani stolpci

Database Engine vam omogoča ustvarjanje indeksov na determinističnih izračunanih stolpcih, kjer imajo osnovni stolpci natančne vrste podatkov. (Izračunani stolpec velja za determinističnega, če vedno vrne enake vrednosti za iste podatke tabele.)

Indeksirani izračunani stolpec je mogoče ustvariti samo, če so naslednji parametri stavka SET nastavljeni na ON (ti parametri zagotavljajo, da je stolpec determinističen):

QUOTED_IDENTIFIER

CONCAT_NULL_YIELDS_NULL

Poleg tega mora biti parameter NUMERIC_ROUNDABORT izklopljen.

Če ustvarite gručasti indeks v izračunanem stolpcu, bodo vrednosti stolpca fizično obstajale v ustreznih vrsticah v tabeli, ker strani vozlišč gručastega indeksa vsebujejo podatkovne vrstice. Naslednji primer ustvari gručasti indeks v skupnem izračunanem stolpcu iz tabele Naročila:

UPORABA SampleDb; CREATE CLUSTERED INDEX ix1 ON naročila (skupaj);

Po izvedbi stavka CREATE INDEX bo izračunani skupni stolpec fizično prisoten v tabeli. To pomeni, da bodo vse posodobitve temeljnih stolpcev izračunanega stolpca povzročile njegovo posodobitev.

Stolpec lahko naredite konstanten na drug način z uporabo VZTRAJNI parameter. Ta možnost vam omogoča, da določite fizično prisotnost izračunanega stolpca, ne da bi sploh ustvarili ustrezen indeks v gručah. Ta zmožnost je potrebna za ustvarjanje fizičnih izračunanih stolpcev, ki so ustvarjeni na stolpcih s približnim podatkovnim tipom (float ali real). (Kot je bilo že omenjeno, je mogoče indeks ustvariti samo v izračunanem stolpcu, če so njegovi temeljni stolpci točno določene vrste podatkov.)

V tem članku za začetnike si bom ogledal, kako določiti potrebne indekse za povečanje hitrosti izvajanja sql poizvedb.

Pravzaprav je z indeksi povezanih veliko tankosti, ki lahko pomembno vplivajo na uspešnost tako v eni kot v nasprotni smeri. Na internetu lahko najdete veliko člankov o tem. Obsežni članki, ki pojasnjujejo razlike v naslavljanju, shranjevanju pomnilnika in mnogih drugih stvareh.

To so seveda res uporabne stvari, vendar pogosto spregledajo eno majhno nianso - količine podatkov, pri katerih imajo vse te lastnosti res opazen učinek. In ta številka se običajno meri v sto tisočih zapisih. Preprosto povedano, če imate v svojih tabelah približno 1-30 tisoč zapisov in govorimo o spletni strani (ali podobnem viru) in ne o nekakšnem vmesnem shranjevanju podatkov za naložene sisteme, potem je najpogosteje bolj pomembno, da preprosto zgradite pravilne indekse. Tukaj je pomembno opozoriti, da vam ni treba biti zelo tehnično podkovani. Veliko uporabnih indeksov je mogoče zgraditi z uporabo preproste logike.

Opomba: To predpostavlja, da so same poizvedbe sestavljene bolj ali manj optimalno, na primer, v select ni dodatnih polj itd.

Indeks za polja s celoštevilskimi identifikatorji.

Če imate polje s celoštevilskim identifikatorjem (ni pomembno, ali je to identifikator same tabele ali identifikator, ki kaže na vrstico v drugi tabeli), potem zanj zgradite ločen indeks.

Bistvo je to. Če je polje identifikator zapisov v sami tabeli, potem govorimo o primarnem ključu (je tudi indeks). Prednosti takega indeksa so številne, saj spletna mesta najpogosteje delujejo z identifikatorji. Če je to identifikator vrstice iz imeniške tabele, je potreben tudi indeks. Ker če potrebujete filtrirane podatke, potem brez indeksov ti imeniki niso veliko uporabni (no, morda samo velikost baze podatkov).

Če je v prvem primeru vse precej preprosto in jasno, potem bom za drugi primer (z referenčno knjigo) dal preprost primer.

Recimo, da obstajata dve tabeli: članki (članek - id, ime, besedilo) in komentarji (komentar - id, article_id, besedilo). Prva tabela vsebuje 200 zapisov (člankov), druga tabela vsebuje 2000 zapisov (10 komentarjev za vsak članek). V skladu s tem se, ko vsak uporabnik odpre kateri koli članek, izvede naslednja poizvedba:

Če se poizvedba sql izvede brez indeksa za polje article_id, bo celotna tabela s komentarji (vseh 2000 zapisov) vsakokrat v celoti pregledana. Če je za polje article_id dodan indeks, potem baza podatkov ne bo morala pregledati več kot 20 zapisov (če smo natančni, približno 18 v najslabšem primeru). Tu je izračun preprost. V najslabšem primeru se indeksno iskanje izvede s približno hitrostjo binarnega logaritma števila zapisov + število zapisov z isto vrednostjo indeksnega polja. V tem primeru ima vsak članek 10 zapisov (njihove vrednosti se ponavljajo) + log2 od 200 (ker je samo 200 člankov = 2000/10) = 10 + 8 (zaokroženo) = 18.

Seveda pa vsak tak indeks poleg prostora na disku, ki ga zasede, uvaja tudi dodatne stroške baze podatkov za vstavljanja, posodabljanja in brisanja. Navsezadnje je treba poleg spreminjanja podatkov same tabele obnoviti tudi njene indekse. A kot sem že rekel, za obseg navadnih spletnih strani to ni velik zalogaj. In tudi če ustvarite indeks na tabeli, ki je ne uporabljate v svojih poizvedbah sql, to ne bo povzročilo opaznih težav. Poleg tega je vedno možno, da z namestitvijo dodatnega modula ali lastnim dodajanjem poizvedb ta indeks pride zelo prav.

Opomba: Vendar ne pozabite, da to velja posebej za celoštevilske indekse in ne za možnost »naj naredim indekse za vsa možna polja«.

Enostavni in sestavljeni indeksi za najpogostejše poizvedbe.

Številne zbirke podatkov imajo predpomnilnik rezultatov za poizvedbe. Poskusite izvesti isto zahtevo dvakrat zaporedoma - v prvem primeru bo zahteva trajala dolgo, drugič pa hitro. Prvič bodo podatki izračunani, drugič bodo podatki zagotovljeni iz predpomnilnika. Vendar to ne pomaga veliko v primerih, ko predpomnilnik ni zgrajen za poizvedbe (na primer, ko filter vsebuje izračunane pogoje z uporabo vgrajenih funkcij baze podatkov), v primerih, ko se poizvedbe, čeprav iste vrste, uporabljajo z različnimi parametrov in v tistih primerih, ko je zahtev veliko in so zato podatki shranjeni v predpomnilniku za zelo kratek čas.

Zato je občasno morda smiselno dodatno zgraditi običajne in sestavljene indekse za pogosto izvajane poizvedbe. Poglejmo si dva primera.

Enostavno kazalo.

Recimo, da imate tabelo - izdelki (izdelek - id, šifra, ime, besedilo). In tako se zgodi, da uporabniki spletnega mesta pogosto iščejo izdelke po njihovih alfanumeričnih kodah (artikli - kodno polje). V skladu s tem je zahteva videti nekako takole:

V tej situaciji je smiselno ustvariti ločen indeks za polje "koda", saj z njim bazi podatkov ne bo treba v celoti pregledati vseh zapisov v tabeli. Vendar upoštevajte, da imajo baze podatkov lahko omejitve glede vrst in velikosti polj. Zato morate najprej preveriti, ali je mogoče ustvariti indeks za takšna polja.

Sestavljeni indeks.

Preden navedem primer s sestavljenim indeksom, bi rad nekoliko razjasnil eno pomembno točko - vrstni red polj v indeksu je pomemben. Ker se iskanje najprej izvede po prvem polju, nato pa po naslednjem (in tako naprej). Če torej poznate določeno vrednost le zadnjega polja, potem tak indeks ne bo primeren, saj brez poznavanja specifične vrednosti prvega polja ni mogoče določiti, kateri niz zapisov je treba preveriti, kar je zakaj bo baza podatkov morala pregledati vse zapise v tabeli. Preprosto povedano, indeks (stolpec_1, stolpec_2) ni enak indeksu (stolpec_2, stolpec_1).

Zdaj pa predpostavimo naslednjo situacijo. Obstajajo tri tabele: uporabnik (user - id, ime), kategorija (cat - id, ime) in članek (članek - id, cat_id, user_id, ime, besedilo). In to ste storili na spletnem mestu - na dnu članka je prikazan celoten seznam člankov istega uporabnika iz določene kategorije. Hkrati so se uporabniki izkazali za tako plodne, da pišejo veliko člankov, čeprav v različnih kategorijah (na primer majhne zgodbe, kratke opombe in tako naprej). V tem primeru bo zahteva videti takole:

Če ste naredili indekse za identifikatorska polja, vam bo to pomagalo, vendar ne veliko. Prvič, obstajata dva enako verjetna indeksa. Ena za kategorije in druga za uporabnike. Kateri bo boljši, se na splošno ne ve. Poleg tega to morda ne bo veliko pomagalo, saj imajo lahko uporabniki 1000 člankov, kategorije pa 1000 člankov. Drugič, tudi če zmanjšate zapise za določenega uporabnika (ali kategorijo), jih bo še vedno treba skenirati z uporabo drugega polja, to je popolnega skeniranja (čeprav za manjši obseg zapisov). Na primer, če imajo uporabniki 1000 zapisov, boste morali za vseh 1000 zapisov preveriti, ali pripadajo kategoriji ali ne.

Za veliko število zapisov in pogoste klice je to zelo draga sql poizvedba. Zato se v tem primeru splača narediti sestavljeni indeks, na primer (user_id, cat_id). V tem primeru bo po iskanju po uporabniku nadaljnje iskanje po kategoriji hitrejše, saj bo na voljo tudi indeks za dobljeno vrednost. zapisi. Skladno s tem bo namesto 1000 preverjenih zapisov bistveno manj (preverjanja se izračunajo enako kot pri navadnem indeksu - logaritem + število zapisov).

Kako lahko v takih situacijah določite vrstni red polj? Tukaj je vse precej preprosto in podobno kot sem opisal v članku o filtriranju (glej povezavo na začetku). Naj vas spomnim, da gre za to, da z vsakim uporabljenim filtrom število zapisov postane čim manjše. Zato je smiselno preveriti povprečno število zapisov na vsako vrednost polja v tabeli. In polje s to številko manj mora biti prvo. Na primer, za dano poizvedbo SQL je vredno preveriti naslednje:

Izračunajte povprečno število zapisov za uporabnike izberite -- Povprečno število zapisov avg(data.count) kot avg from -- Združite vse zapise po identifikatorju (izberite count(*) kot `count` iz članka -- Združite po uporabnikih združite po user_id) kot podatek; -- Izračunajte povprečno število zapisov za kategorije izberite -- Povprečno število zapisov avg(data.count) kot avg from -- Združite vse zapise po ID (izberite count(*) kot `count` iz članka -- Združite po kategoriji združi po cat_id) kot podatek;

V skladu s tem, če je povprečno število uporabnikov manjše, mora biti to polje na prvem mestu, saj bo po prvem iskanju malo zapisov za preverjanje. V nasprotnem primeru mora biti ID kategorije na prvem mestu.

Vendar je vredno razumeti, da je v takšni situaciji vredno preveriti tudi, ali so zapisi bolj ali manj enakomerno porazdeljeni. Konec koncev se lahko izkaže, da je 1 uporabnik napisal 2000 člankov, ostali pa le 100. V takšni situaciji je morda boljši filter po kategorijah, saj si bo večina bralcev ogledala članke tega določenega uporabnika. Zato se včasih splača izračunati samo združevanje po identifikatorjih (brez izračuna povprečja) in si hitro ogledati rezultate.

Če morate ustvariti indeks za tri ali več polj, potem morate storiti enako, samo povečati število polj, za katera se združevanje izvaja z identifikatorjem. Preprosto povedano, najprej preverite prvo polje in določite najmanjšo številko, nato pa namesto "skupina po stolpcu_1" navedite različne možnosti s preostalimi polji v obliki "skupina po stolpcu_1, stolpec_2", nato "skupina po stolpcu_1, stolpec_3" in tako dalje. V tem primeru vsakdo izbere tiste kombinacije, v katerih povprečno število zapisov postaja vse manjše.

In indeksi, to posebne iskalne tabele, ki ga lahko iskalnik baze podatkov uporabi za pospešitev iskanja podatkov. Preprosto povedano, indeks je kazalec na podatke v tabeli. Indeks v bazi podatkov je zelo podoben kazalu na zadnji strani knjige.

Na primer, če želite povezave do vseh strani v knjigi o določeni temi, najprej preglejte indeks, ki navaja vse teme po abecednem vrstnem redu in nato preide na eno ali več določenih številk strani.

Indeks pomaga pospešiti poizvedbe in stavke, vendar upočasni vnos podatkov z izjavami POSODOBITEV in VSTAVITE. Indekse je mogoče ustvariti ali izbrisati, ne da bi to vplivalo na podatke.

Ustvarjanje indeksa vključuje izjavo USTVARI INDEKS, ki vam omogoča, da poimenujete indeks, da določite tabelo in kateri stolpec ali stolpce želite indeksirati, ter označite, ali je indeks v naraščajočem ali padajočem vrstnem redu.

Indeksi so lahko tudi unikatni, z omejitvijo UNIKATNO, tako da indeks prepreči podvojene vnose v stolpcu ali kombinaciji stolpcev, ki imajo na sebi indeks.

Ukaz CREATE INDEX

Osnovna sintaksa USTVARI INDEKS izgleda takole:

CREATE INDEX ime_indeksa ON ime_tabele;

Indeksi v enem stolpcu

Indeks z enim stolpcem se ustvari samo v enem stolpcu v tabeli. Osnovna sintaksa je naslednja.

CREATE INDEX index_name ON table_name(column_name);

Edinstveni indeksi

Enolični indeksi se ne uporabljajo samo za delovanje, ampak tudi za zagotavljanje celovitosti podatkov. Enolični indeks ne dovoljuje vstavljanja podvojenih vrednosti v tabelo. Osnovna sintaksa je naslednja.

CREATE UNIQUE INDEX index_name na table_name(column_name);

Sestavljeni indeksi

Sestavljeni indeks je indeks dveh ali več stolpcev tabele. Njegova osnovna sintaksa je naslednja.

CREATE INDEX ime_indeksa na ime_tabele (stolpec1, stolpec2);

Ne glede na to, ali ustvarite indeks na enem stolpcu ali sestavljeni indeks, upoštevajte stolpce, ki jih morda zelo pogosto uporabljate v poizvedbi WHERE kot pogoj filtra.

Če je v uporabi samo en stolpec, je treba izbrati indeks v enem stolpcu. Če obstajata dva ali več stolpcev, ki se pogosto uporabljajo kot filtri v stavku WHERE, bi bil sestavljeni indeks boljša izbira.

Implicitni indeksi

Implicitni indeksi so indeksi, ki so samodejno ustvarjeni na strežniku baze podatkov, ko je ustvarjen objekt. Indeksi se samodejno ustvarijo na primarnem ključu in unikatni omejitvi.

Ukaz DROP INDEX

Indeks lahko izbrišete z ukazom SQL DROP. Pri brisanju indeksa bodite previdni, ker je lahko delovanje počasnejše ali boljše.

Osnovna sintaksa je naslednja:

DROP INDEX ime_indeksa;

Če si želite ogledati nekaj resničnih primerov indeksov, si lahko ogledate primer omejitve INDEX.

Kdaj se morate izogibati indeksom?

Čeprav so indeksi namenjeni izboljšanju zmogljivosti baze podatkov, se jim je včasih treba izogibati.

Naslednja navodila kažejo, kdaj je treba ponovno razmisliti o uporabi indeksa.

• Indeksov se ne sme uporabljati na majhnih tabelah.
• Tabele, ki imajo pogosto velike operacije posodabljanja ali vstavljanja.
• Indeksov ne smete uporabljati za stolpce, ki vsebujejo veliko število ničelnih vrednosti.
• Stolpcev, s katerimi se pogosto manipulira, ne bi smeli indeksirati.