--En indeks er en struktur på disk som er assosiert med en tabell eller visning og øker hastigheten på henting av rader fra tabellen eller visningen. En indeks inneholder nøkler bygget fra én eller flere kolonner i en tabell eller visning. Disse nøklene er lagret i en balansert trestruktur som støtter raskt søk av rader etter nøkkelverdiene deres i SQL Server.

--Klyngede indekser sorterer og lagrer rader med data i tabeller eller visninger basert på nøkkelverdiene deres. Disse verdiene er kolonnene som er inkludert i indeksdefinisjonen. Det er bare én gruppert indeks per tabell fordi dataradene bare kan sorteres i en enkelt rekkefølge.
--Rader med data i en tabell lagres bare i sorteringsrekkefølge hvis tabellen inneholder en gruppert indeks. Hvis en tabell har en klynget indeks, kalles tabellen klynget. Hvis en tabell ikke har en gruppert indeks, lagres dataradene i en uordnet struktur kalt en heap.

--En ikke-klynget indeks har nøyaktig samme struktur som en klynget indeks, men med to viktige forskjeller:
--en ikke-klynget indeks endrer ikke den fysiske rekkefølgen til radene i tabellen, og bladsider i en ikke-klynget indeks består av indeksnøkler og bokmerker.

--Klyngede indekser gir raskere datainnhenting enn ikke-klyngede indekser. De viser seg vanligvis å være raskere når de oppdaterer også, men ikke når mange oppdateringer skjer på samme sted midt i forholdet.

--Av en eller annen grunn har en klynget indeks en tendens til å kjøre raskere enn en ikke-klynget indeks. Når systemet skanner en gruppert indeks, er det ikke nødvendig å forlate B-trestrukturen for å skanne datasider fordi slike sider allerede finnes på bladnivået til treet.

--En ikke-klynget indeks krever også flere I/O-operasjoner enn den tilsvarende clustered-indeksen.

--Den ikke-klyngede indeksen må lese datasidene etter skanning av B-treet, eller hvis det er en klynget indeks i en annen kolonne(r) i tabellen, må den ikke-klyngede indeksen lese B-trestrukturen til den klyngede indeksen .

--Så en gruppert indeks vil være betydelig raskere enn en tabellskanning, selv om selektiviteten er ganske dårlig (spørringen returnerer mange rader)

LAG TABELL tsql.dbo.NI
ID int NOT NULL,
T char(8) NULL
);

LAG TABELL tsql.dbo.NCI
ID int NOT NULL,
T char(8) NULL
);

--Opprett en gruppert indeks

OPPRETT KLUSTERET INDEKS IX_1
PÅ tsql.dbo.NCI(ID);

--Opprett en ikke-klynget indeks på en tabell

OPPRETT IKKE-KLUSTERET INDEKS IX_2
PÅ tsql.dbo.NCI(T);

--Legg til testdata
DECLARE @i INT = 100000;
DECLARE @t CHAR(1) = "T";

MENS @i > 0
BEGYNNE
sett inn i tsql.dbo.NI verdier(@i, @t + CAST(@i AS char(6)));
sett inn i tsql.dbo.NCI-verdier(@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
SLUTT

--Forespørsler på en tabell med indekser
VELG ID, T FRA tsql.dbo.NCI
BESTILL ETTER ID, T

VELG ID, COUNT(*) SOM C FRA tsql.dbo.NCI
GRUPPE ETTER ID, T

VELG ID, T FRA tsql.dbo.NCI
HVOR ID > 4000 OG ID< 55000 AND T LIKE "T%"

--Søk med begge indeksene
BRUK tsql;
VELG CAST(dbo.NCI.ID SOM VARCHAR)
FRA dbo.NCI
GRUPPE ETTER dbo.NCI.ID
UNION ALLE
VELG dbo.NCI.T
FRA dbo.NCI
GRUPPE AV dbo.NCI.T

--Indikerer informasjon
SELECT index_type_desc, index_depth, index_level,
sideantall, rekordantall
FRA sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL , "DETALJERT");

--Slette indekser
HVIS FINNES (VELG navn FRA sys.indexes
WHERE-navn = N"IX_1")
DROP INDEX IX_1 ON tsql.dbo.NCI;

HVIS FINNES (VELG navn FRA sys.indexes
WHERE-navn = N"IX_2")
DROP INDEX IX_2 ON tsql.dbo.NCI;

I den forrige artikkelen introduserte vi måter å optimalisere relasjonsdatabaser på og diskuterte hvordan klyngede og ikke-klyngede indekser fungerer i sammenheng med å optimalisere utførelsestiden for databasespørringer. Nå er det på tide å omsette denne kunnskapen i praksis ved å lære hvordan du lager optimaliseringsindekser for en MS SQL-database.

La meg minne deg på definisjonen av Staffs-tabellskjemaet som vi skal jobbe med:

Stabsbord

La oss si at vi må lage en ikke-klynget indeks for Staffs-tabellen, som vil optimalisere følgende spørring:

VELG ID, navn, jobb FRA Stuffs HVOR LØNN > 1000 OG Bildet IKKE ER NULL

Indeksnøkkelen vil være kolonnene LØNN og Foto, siden utvalget er filtrert av disse feltene. Og kolonnene Id, Name og Job vil være kolonnene som er inkludert i indeksen.

Den generelle kommandosyntaksen er som følger:

BRUK GÅ

LAG IKKE-KLYNGERT INDEKS PÅ (ASC -- indeksnøkkelkolonner)

INKLUDERE ( -- inkluderte kolonner) GO

I vårt tilfelle vil forespørselen se slik ut:

(Lønn, bilde) INKLUDERE (Id, navn, jobb) GO

Vi har opprettet en ikke-gruppert indeks. Eller rettere sagt, en ikke-gruppert dekkende indeks. Dette betyr at indeksen inneholder alle feltene som er nødvendige for å utføre spørringen og SQL Server vil ikke få tilgang til basistabellen når spørringen utføres.

Hvis koden vår var slik:

LAG IKKE-KLUSTERET INDEKS IDX_StaffsSearch ON Stuffs

(Lønn, foto) INKLUDERE (Id) GO

I dette tilfellet slutter indeksen å være en dekkende indeks, siden den ikke inkluderer alle kolonnene som brukes i spørringen. Optimalisatoren vil fortsatt bruke denne indeksen når den utfører spørringen, men effektiviteten vil reduseres med en størrelsesorden siden den vil kreve tilgang til basistabellen.

Den grupperte indeksen opprettes ved hjelp av følgende kommando:

LAG KLUSTERET INDEKS IDX_Stsffsid ON Stuffs (Id)

Her ble det laget en unik klynget indeks basert på tabellens primærnøkkel (Id-kolonnen).

Virkelig eksempel

La oss nå utvikle et scenario der vi realistisk kan evaluere graden av ytelsesgevinst ved bruk av indekser.

La oss lage en ny database:

LAG DATABASE TestDB;

Og en enkelt kundetabell, som vil bestå av fire kolonner:

LAG TABELL .(

IKKE NULL, NULL, NULL, NULL) GÅ

La oss nå fylle tabellen med tilfeldige data. Id-kolonnen vil økes i en løkke, og de resterende tre kolonnene i tabellen vil bli fylt med tilfeldige tall ved å bruke en særegen versjon av den tilfeldige funksjonen:

DEKLARE @i int = 0;

Mens jeg< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(sjekksum(nyvid())), abs(sjekksum(nyvid())), abs(sjekksum(nyvid())) SET @i = @i + 1; SLUTT

Dette skriptet legger til en halv million poster til tabellen, så vær tålmodig, skriptet vil kjøre i minst 3 minutter.

Alt er klart for prøven. Vi vil evaluere ytelsesegenskapene til spørringen. Siden utførelsestiden for spørringen kan avhenge av den spesifikke maskinen, vil vi analysere en mer uavhengig indikator - antall logiske lesninger.

For å aktivere statistikkinnsamlingsmodus må du kjøre følgende kommando:

Nå, etter å ha utført hver forespørsel, på Meldinger-fanen vil vi ha tilgang til statistikk om utførelsen av denne forespørselen, som vist nedenfor:

Vi er kun interessert i verdien av parameteren logical reads.

Så det er ingen indekser i tabellen vår ennå. La oss kjøre følgende tre spørringer og registrere antall logiske lesninger for hver spørring i resultattabellen nedenfor:

1) VELG ID, Num1, Num2 FRA Kunder HVOR ID = 2000

2) VELG ID, Num1, Num2 FRA Kunder HVOR ID >= 0 OG ID< 1000

3) VELG ID, Num1, Num2 FRA Kunder HVOR ID >= 0 OG ID< 5000

Disse spørringene vil returnere henholdsvis 1 rad, 1000 rader og 5000 rader. Uten indekser er ytelsesindikatoren (antall logiske avlesninger) for alle søk den samme og lik 1621. La oss legge inn dataene i resultattabellen:

Vi ser at for andre og tredje spørringer, når et ganske stort antall rader returneres, forbedret ikke indeksen vi opprettet ytelsen. Men for et søk som returnerer en enkelt rad, var hastighetsøkningen enorm. Dermed kan vi konkludere med at det er fornuftig å lage ikke-dekkende indekser når du optimaliserer spørringer som returnerer et enkelt resultat.

La oss nå lage en dekkende indeks, og dermed oppnå maksimal ytelse.

Først, la oss slette forrige indeks:

BRUK TestDB GO DROP INDEX Customers.TestIndex1

Og la oss lage en ny indeks:

LAG IKKE-KLYGERET INDEKS TestIndex2 PÅ dbo.Customers(Id) INCLUDER (Num1, Num2);

La oss nå kjøre søkene våre en tredje gang og skrive resultatene inn i en tabell:

Ingen indekser
Ikke-dekkende indeks
Dekkende indeks

Det er lett å se at ytelsesøkningen har vært enorm. Dermed har vi økt hastigheten på utførelse av spørringer titalls ganger. Når du kjører en database som lagrer millioner av rader, vil denne ytelsesgevinsten være ganske merkbar.

I denne artikkelen har vi sett på et eksempel på å optimalisere en database ved å lage indekser. Det er verdt å merke seg at opprettelsen av indekser er en rent individuell prosess for hver forespørsel. For å bygge en indeks som virkelig vil optimere søkeytelsen, må du analysere selve spørringen og dens utførelsesplan nøye.

Effektiv indeksbygging er en av de beste måtene å forbedre ytelsen til en databaseapplikasjon på. Uten bruk av indekser er SQL Server som en leser som prøver å finne et ord i en bok ved å se på hver side. Hvis boken har en emnekartotek (indeks), kan leseren søke etter nødvendig informasjon mye raskere.

I mangel av en indeks vil SQL-serveren, når den henter data fra en tabell, skanne hele tabellen og sjekke hver rad for å se om søkekriteriene er oppfylt. En slik full skanning kan være katastrofal for ytelsen til hele systemet, spesielt hvis det er mye data i tabellene.

En av de viktigste oppgavene når du arbeider med en database er å bygge en optimal indeks for å forbedre systemytelsen. De fleste større databaser tilbyr verktøy for å se utførelsesplanen for spørringer og hjelpe deg med å justere og optimalisere indekser. Denne artikkelen fremhever flere gode tommelfingerregler som gjelder når du oppretter eller endrer indekser i en database. La oss først se på situasjoner der indeksering forbedrer ytelsen og hvor indeksering kan skade.

Nyttige indekser

Så, tabellindeksering vil være nyttig når du søker etter en spesifikk post i en tabell ved å bruke Where-setningen. Slike spørringer inkluderer for eksempel spørringer som søker etter en rekke verdier, spørringer som matcher en eksakt verdi med en bestemt verdi, og spørringer som slår sammen to tabeller.

For eksempel vil følgende spørringer mot Northwind-databasen kjøre mer effektivt når du bygger en indeks på UnitPrice-kolonnen.

Slett fra produkter der UnitPrice=1
Velg * fra produkter hvor enhetspris mellom 14 OG 16

Fordi indekselementer lagres sortert, er indeksering også nyttig når du bygger en spørring ved å bruke Ordre etter-klausulen. Uten en indeks lastes og sorteres poster mens spørringen kjører. En indeks basert på UnitPrice vil tillate deg å skanne indeksen og hente rader ved referanse når du behandler neste forespørsel. Hvis du ønsker å sortere radene i synkende rekkefølge, kan du ganske enkelt skanne indeksen i omvendt rekkefølge.

Velg * Fra produkter bestilles etter UnitPrice ASC

Å gruppere en post ved å bruke Group by-setningen krever også ofte sortering, så å bygge en indeks på UnitPrice-kolonnen vil også være nyttig for neste spørring som teller antall enheter av et produkt til hver spesifikk pris

Velg antall(*), UnitPrice Fra produktgruppe etter UnitPrice

Indekser er nyttige for å opprettholde en unik verdi for en kolonne, siden DBMS enkelt kan se på indeksen for å se om verdien allerede eksisterer. Av denne grunn blir primærnøkler alltid indeksert.

Ulemper med indeksering

Indekser forringer systemytelsen under registreringsendringer. Hver gang en spørring utføres for å endre data i en tabell, må indeksen også endres. For å velge det optimale antallet indekser, må du teste databasen og overvåke ytelsen. Statiske systemer, der databaser primært brukes til datainnhenting, for eksempel rapportering, kan inneholde flere indekser for å støtte skrivebeskyttede spørringer. Databaser med et stort antall transaksjoner for å endre data vil trenge et lite antall indekser for å gi høyere gjennomstrømning.

Indekser tar opp ekstra plass på disk og i RAM. Den nøyaktige størrelsen vil avhenge av antall poster i tabellen, samt antall og størrelse på kolonner i indeksen. I de fleste tilfeller er ikke dette et stort problem siden diskplass nå er lett å ofre for bedre ytelse.

Bygge en optimal indeks

Enkel indeks

En enkel indeks er en indeks som bruker verdiene til et enkelt felt i en tabell. Å bruke en enkel indeks er fordelaktig av to grunner. For det første legger det mye stress på harddisken din å kjøre en database. Store indeksnøkler vil tvinge databasen til å utføre flere I/O-operasjoner, noe som begrenser ytelsen.

For det andre, fordi indekselementer ofte er involvert i sammenligninger, er mindre indekser lettere å sammenligne. Av disse to grunnene er en enkelt heltallskolonne en bedre indeks fordi den er liten og enkel å sammenligne. Tegnstrenger, derimot, krever tegn-for-tegn-sammenligninger og oppmerksomhet på parameterhåndtering.

Selektiv indeks

De mest effektive indeksene er de med en lav prosentandel av dupliserte verdier. For eksempel vil en telefonkatalog for en by der nesten alle har etternavnet Smith ikke være like nyttig hvis oppføringene i den er sortert etter etternavn.

En indeks med en høy prosentandel unike verdier kalles også en selektiv indeks. Det er klart at en unik indeks har størst selektivitet, siden den ikke inneholder dupliserte verdier. Mange DBMS-er kan spore statistikk om hver indeks og kan gjenkjenne hvor mange ikke-dupliserte verdier hver indeks inneholder. Denne statistikken brukes når du genererer en plan for utførelse av spørringer.

Dekker indekser

Indekser består av en datakolonne som selve indeksen er bygget på og en peker til den tilsvarende raden. Det er som en bokindeks: den inneholder bare nøkkelordene og en lenke til en side du kan gå til for mer informasjon. Vanligvis vil DBMS følge pekere til en rad fra indeksen for å samle inn all informasjonen som trengs for spørringen. Men hvis indeksen inneholder alle kolonnene som trengs i spørringen, kan informasjonen hentes uten tilgang til selve tabellen.

La oss vurdere en indeks på UnitPrice-kolonnen, som allerede ble nevnt ovenfor. DBMS kan bare bruke indekselementene til å utføre neste spørring.

Velg Count(*), UnitPrice Fra produktgruppe etter UnitPrice

Denne typen spørring kalles en dekkende spørring fordi alle kolonnene som spørres kan hentes fra en enkelt indeks. For de viktigste spørsmålene kan det være lurt å vurdere å lage en dekkende indeks for best mulig ytelse. Slike indekser er sannsynligvis sammensatte (bruker mer enn én kolonne), som er det motsatte av det første prinsippet: lag enkle indekser. Å velge det optimale antallet kolonner i en indeks kan selvsagt bare vurderes gjennom testing og overvåking av ytelsen til databasen i ulike situasjoner.

Klyngeindeks

Mange databaser har én spesiell indeks på en tabell, der alle data fra en rad er inneholdt i indeksen. I SQL Server kalles en slik indeks en klynget indeks. En gruppert indeks kan sammenlignes med en telefonkatalog fordi hvert indekselement inneholder all informasjonen du trenger og ikke inneholder lenker for å få ytterligere data.

Det er en generell regel - hver ikke-triviell tabell må ha en gruppert indeks. Hvis det er mulig å lage bare én indeks på en tabell, gjør den gruppert. I SQL Server, når en primærnøkkel opprettes, vil en gruppert indeks automatisk opprettes (hvis den ikke allerede inneholder en), ved å bruke primærnøkkelkolonnen som indekseringsnøkkel. En klynget indeks er den mest effektive indeksen (hvis den brukes, dekker den hele spørringen) og i mange DBMS-er hjelper en slik indeks til å effektivt administrere plassen som kreves for lagring av tabeller, siden ellers (uten å bygge en klynget indeks) tabellrader lagres i en uordnet struktur, som kalles en haug.

Vær forsiktig når du velger kolonner for en gruppert indeks. Hvis du endrer en post og endrer verdien til en kolonne i en gruppert indeks, vil databasen bli tvunget til å gjenoppbygge indekselementene (for å holde dem i sortert rekkefølge). Husk at indekselementene for en gruppert indeks inneholder alle kolonneverdiene, så å endre verdien til en kolonne kan sammenlignes med å utføre en Delete-setning etterfulgt av en Insert-setning, som åpenbart vil forårsake ytelsesproblemer hvis det gjøres ofte. Av denne grunn består grupperte indekser ofte av en primærnøkkel og en fremmednøkkelkolonne. Hvis nøkkelverdier endres, endres de svært sjelden.

Konklusjon

Å finne de riktige indeksene som skal brukes i en database krever nøye analyse og testing av systemet. Praksisen som presenteres i denne artikkelen er gode regler for å konstruere indekser. Etter å ha brukt disse metodene, må du teste din spesifikke applikasjon på nytt under dine spesifikke maskinvare-, minne- og driftsforhold.

En av de viktigste måtene å oppnå høy produktivitet på SQL Server er bruk av indekser. En indeks fremskynder spørringsprosessen ved å gi rask tilgang til rader med data i en tabell, omtrent som en indeks i en bok hjelper deg raskt å finne informasjonen du trenger. I denne artikkelen vil jeg gi en kort oversikt over indekser i SQL Server og forklare hvordan de er organisert i databasen og hvordan de bidrar til å øke hastigheten på databaseforespørsler.

Indekser opprettes på tabell- og visningskolonner. Indekser gir en måte å raskt søke i data basert på verdiene i disse kolonnene. Hvis du for eksempel oppretter en indeks på en primærnøkkel og deretter søker etter en rad med data ved å bruke primærnøkkelverdiene, SQL Server vil først finne indeksverdien og deretter bruke indeksen for raskt å finne hele raden med data. Uten en indeks vil en full skanning av alle rader i tabellen bli utført, noe som kan ha en betydelig ytelseseffekt.
Du kan opprette en indeks på de fleste kolonnene i en tabell eller visning. Unntaket er hovedsakelig kolonner med datatyper for lagring av store objekter ( LOB), som for eksempel bilde, tekst eller varchar(maks). Du kan også lage indekser på kolonner designet for å lagre data i formatet XML, men disse indeksene er strukturert litt annerledes enn de vanlige, og deres vurdering ligger utenfor rammen av denne artikkelen. Artikkelen diskuterer heller ikke kolonnebutikk indekser. I stedet fokuserer jeg på de indeksene som er mest brukt i databaser SQL Server.
En indeks består av et sett med sider, indeksnoder, som er organisert i en trestruktur - balansert tre. Denne strukturen er hierarkisk i naturen og starter med en rotnode øverst i hierarkiet og bladnodene, bladene, nederst, som vist i figuren:


Når du spør etter en indeksert kolonne, starter spørringsmotoren på toppen av rotnoden og jobber seg ned gjennom de mellomliggende nodene, med hvert mellomlag inneholder mer detaljert informasjon om dataene. Spørringsmotoren fortsetter å bevege seg gjennom indeksnodene til den når bunnnivået med indeksbladene. For eksempel, hvis du ser etter verdien 123 i en indeksert kolonne, vil søkemotoren først bestemme siden på det første mellomnivået på rotnivået. I dette tilfellet peker den første siden til en verdi fra 1 til 100, og den andre fra 101 til 200, så søkemotoren vil få tilgang til den andre siden på dette mellomnivået. Deretter vil du se at du bør gå til den tredje siden på neste mellomnivå. Herfra vil spørringsdelsystemet lese verdien av selve indeksen på et lavere nivå. Indeksblader kan inneholde enten selve tabelldataene eller ganske enkelt en peker til rader med data i tabellen, avhengig av indekstypen: klynget indeks eller ikke-klynget indeks.

Klynget indeks

En gruppert indeks lagrer de faktiske radene med data i bladene i indeksen. Tilbake til forrige eksempel betyr dette at raden med data knyttet til nøkkelverdien 123 vil bli lagret i selve indeksen. En viktig egenskap ved en gruppert indeks er at alle verdier er sortert i en bestemt rekkefølge, enten stigende eller synkende. Derfor kan en tabell eller visning bare ha én gruppert indeks. I tillegg bør det bemerkes at data i en tabell lagres i sortert form bare hvis det er opprettet en gruppert indeks på denne tabellen.
En tabell som ikke har en gruppert indeks kalles en haug.

Ikke-klynget indeks

I motsetning til en klynget indeks, inneholder bladene til en ikke-klynget indeks bare disse kolonnene ( nøkkel) som denne indeksen bestemmes av, og inneholder også en peker til rader med reelle data i tabellen. Dette betyr at underspørringssystemet krever en ekstra operasjon for å finne og hente de nødvendige dataene. Innholdet i datapekeren avhenger av hvordan dataene er lagret: gruppert tabell eller heap. Hvis en peker peker til en klynget tabell, peker den til en klynget indeks som kan brukes til å finne de faktiske dataene. Hvis en peker refererer til en haug, peker den til en spesifikk dataradidentifikator. Ikke-klyngede indekser kan ikke sorteres som klyngede indekser, men du kan opprette mer enn én ikke-klyngede indeks på en tabell eller visning, opptil 999. Dette betyr ikke at du skal lage så mange indekser som mulig. Indekser kan enten forbedre eller forringe systemytelsen. I tillegg til å kunne lage flere ikke-klyngede indekser, kan du også inkludere flere kolonner ( inkludert kolonne) inn i sin indeks: bladene i indeksen vil lagre ikke bare verdien av selve de indekserte kolonnene, men også verdiene til disse ikke-indekserte tilleggskolonnene. Denne tilnærmingen lar deg omgå noen av begrensningene som er lagt på indeksen. Du kan for eksempel inkludere en ikke-indekserbar kolonne eller omgå indekslengdegrensen (900 byte i de fleste tilfeller).

Typer indekser

I tillegg til å være enten en klynget eller ikke-klynget indeks, kan den videre konfigureres som en sammensatt indeks, en unik indeks eller en dekkende indeks.

Sammensatt indeks

En slik indeks kan inneholde mer enn én kolonne. Du kan inkludere opptil 16 kolonner i en indeks, men deres totale lengde er begrenset til 900 byte. Både klyngede og ikke-klyngede indekser kan være sammensatte.

Unik indeks

Denne indeksen sikrer at hver verdi i den indekserte kolonnen er unik. Hvis indeksen er sammensatt, gjelder unikhet for alle kolonner i indeksen, men ikke for hver enkelt kolonne. For eksempel hvis du oppretter en unik indeks på kolonnene NAVN Og ETTERNAVN, da må det fulle navnet være unikt, men duplikater i for- eller etternavnet er mulig.
En unik indeks opprettes automatisk når du definerer en kolonnebegrensning: primærnøkkel eller unik verdibegrensning:

• Primærnøkkel
  Når du definerer en primærnøkkelbegrensning på en eller flere kolonner da SQL Server oppretter automatisk en unik klynget indeks hvis en klynget indeks ikke har blitt opprettet tidligere (i dette tilfellet opprettes en unik ikke-klynget indeks på primærnøkkelen)
• Unikt av verdier
  Når du definerer en begrensning på det unike med verdier da SQL Server oppretter automatisk en unik ikke-klynget indeks. Du kan spesifisere at en unik gruppert indeks skal opprettes hvis ingen gruppert indeks ennå er opprettet på tabellen

Dekkende indeks

En slik indeks lar en spesifikk spørring umiddelbart hente alle nødvendige data fra bladene i indeksen uten ytterligere tilgang til postene til selve tabellen.

Designe indekser

Så nyttige som indekser kan være, må de utformes nøye. Fordi indekser kan ta opp betydelig diskplass, vil du ikke lage flere indekser enn nødvendig. I tillegg oppdateres indekser automatisk når selve dataraden oppdateres, noe som kan føre til ytterligere ressursoverhead og ytelsesforringelse. Ved utforming av indekser må flere hensyn vedrørende databasen og spørringer mot denne tas i betraktning.

Database

Som nevnt tidligere kan indekser forbedre systemytelsen fordi de gir søkemotoren en rask måte å finne data på. Du bør imidlertid også ta hensyn til hvor ofte du har tenkt å sette inn, oppdatere eller slette data. Når du endrer data, må indeksene også endres for å gjenspeile de tilsvarende handlingene på dataene, noe som kan redusere systemytelsen betydelig. Vurder følgende retningslinjer når du planlegger indekseringsstrategien:

• For tabeller som oppdateres ofte, bruk så få indekser som mulig.
• Hvis tabellen inneholder en stor mengde data, men endringene er små, bruker du så mange indekser som nødvendig for å forbedre ytelsen til søkene dine. Tenk deg imidlertid nøye om før du bruker indekser på små tabeller, fordi... Det er mulig at bruk av et indekssøk kan ta lengre tid enn bare å skanne alle rader.
• For grupperte indekser, prøv å holde felt så korte som mulig. Den beste tilnærmingen er å bruke en gruppert indeks på kolonner som har unike verdier og ikke tillater NULL. Dette er grunnen til at en primærnøkkel ofte brukes som en klynget indeks.
• Det unike til verdiene i en kolonne påvirker ytelsen til indeksen. Generelt, jo flere duplikater du har i en kolonne, jo dårligere presterer indeksen. På den annen side, jo mer unike verdier det er, desto bedre er ytelsen til indeksen. Bruk en unik indeks når det er mulig.
• For en sammensatt indeks, ta hensyn til rekkefølgen på kolonnene i indeksen. Kolonner som brukes i uttrykk HVOR(For eksempel, WHERE FirstName = "Charlie") må være først i indeksen. Påfølgende kolonner bør listes basert på unike verdiene deres (kolonner med det høyeste antallet unike verdier kommer først).
• Du kan også angi en indeks på beregnede kolonner hvis de oppfyller visse krav. For eksempel må uttrykk som brukes for å få verdien til en kolonne være deterministiske (gi alltid samme resultat for et gitt sett med inngangsparametere).

Databasespørringer

En annen vurdering når du designer indekser, er hvilke spørringer som kjøres mot databasen. Som nevnt tidligere, må du vurdere hvor ofte dataene endres. I tillegg bør følgende prinsipper brukes:

• Prøv å sette inn eller endre så mange rader som mulig i ett søk, i stedet for å gjøre det i flere enkeltspørringer.
• Opprett en ikke-gruppert indeks over kolonner som ofte brukes som søkeord i søkene dine. HVOR og tilkoblinger i BLI MED.
• Vurder å indeksere kolonner som brukes i radoppslagsspørringer for eksakte verdisamsvar.

Og nå, faktisk:

14 spørsmål om indekser i SQL Server som du var flau over å stille

Hvorfor kan ikke en tabell ha to grupperte indekser?

Vil du ha et kort svar? En gruppert indeks er en tabell. Når du oppretter en gruppert indeks på en tabell, sorterer lagringsmotoren alle rader i tabellen i stigende eller synkende rekkefølge, i henhold til indeksdefinisjonen. En gruppert indeks er ikke en egen enhet som andre indekser, men en mekanisme for å sortere data i en tabell og forenkle rask tilgang til datarader.
La oss forestille oss at du har en tabell som inneholder historien til salgstransaksjoner. Salgstabellen inneholder informasjon som ordre-ID, vareposisjon i ordren, varenummer, vareantall, ordrenummer og dato, etc. Du oppretter en gruppert indeks på kolonner Bestillings ID Og LineID, sortert i stigende rekkefølge som vist i det følgende T-SQL kode:
LAG UNIK KLUSTERET INDEKS ix_oriderid_lineid PÅ dbo.Sales(OrderID, LineID);
Når du kjører dette skriptet, vil alle rader i tabellen først bli fysisk sortert etter OrderID-kolonnen og deretter etter LineID, men selve dataene vil forbli i en enkelt logisk blokk, tabellen. Av denne grunn kan du ikke opprette to grupperte indekser. Det kan bare være én tabell med én data, og den tabellen kan bare sorteres én gang i en bestemt rekkefølge.

Hvis en klyngetabell gir mange fordeler, hvorfor bruke en haug?

Du har rett. Gruppetabeller er flotte, og de fleste av søkene dine vil gi bedre resultater på tabeller som har en gruppert indeks. Men i noen tilfeller kan det være lurt å la bordene være i sin naturlige, uberørte tilstand, dvs. i form av en haug, og lag bare ikke-klyngede indekser for å holde spørringene i gang.
Haugen, som du husker, lagrer data i tilfeldig rekkefølge. Vanligvis legger lagringsundersystemet til data til en tabell i den rekkefølgen de er satt inn, men lagringsundersystemet liker også å flytte rundt på rader for mer effektiv lagring. Som et resultat har du ingen sjanse til å forutsi i hvilken rekkefølge dataene vil bli lagret.
Hvis spørringsmotoren trenger å finne data uten fordelen av en ikke-klynget indeks, vil den gjøre en fullstendig skanning av tabellen for å finne radene den trenger. På veldig små bord er dette vanligvis ikke et problem, men etter hvert som haugen vokser i størrelse, synker ytelsen raskt. Selvfølgelig kan en ikke-klynget indeks hjelpe ved å bruke en peker til filen, siden og raden der de nødvendige dataene er lagret - dette er vanligvis et mye bedre alternativ til en tabellskanning. Likevel er det vanskelig å sammenligne fordelene med en gruppert indeks når man vurderer søkeytelse.
Imidlertid kan haugen bidra til å forbedre ytelsen i visse situasjoner. Tenk på en tabell med mange innlegg, men få oppdateringer eller slettinger. For eksempel brukes en tabell som lagrer en logg primært til å sette inn verdier til den er arkivert. På heapen vil du ikke se personsøking og datafragmentering som du ville gjort med en gruppert indeks fordi radene ganske enkelt legges til på slutten av heapen. Å dele opp sider for mye kan ha en betydelig innvirkning på ytelsen, og ikke på en god måte. Generelt lar haugen deg sette inn data relativt smertefritt, og du slipper å håndtere lagrings- og vedlikeholdskostnadene du ville gjort med en gruppert indeks.
Men mangel på oppdatering og sletting av data bør ikke anses som den eneste grunnen. Måten dataene er samplet på er også en viktig faktor. Du bør for eksempel ikke bruke en haug hvis du ofte spør etter dataområder eller dataene du spør etter ofte må sorteres eller grupperes.
Alt dette betyr er at du bare bør vurdere å bruke heapen når du jobber med veldig små tabeller eller all interaksjon med tabellen er begrenset til å sette inn data og spørringene dine er ekstremt enkle (og du bruker ikke-klyngede indekser uansett). Ellers hold deg til en godt utformet gruppert indeks, for eksempel en definert på et enkelt stigende nøkkelfelt, som en mye brukt kolonne med IDENTITET.

Hvordan endrer jeg standard indeksfyllfaktor?

En ting er å endre standard indeksfyllfaktor. Å forstå hvordan standardforholdet fungerer er en annen sak. Men først, ta noen skritt tilbake. Indeksfyllfaktoren bestemmer hvor mye plass på siden for å lagre indeksen på nederste nivå (bladnivå) før du begynner å fylle en ny side. For eksempel, hvis koeffisienten er satt til 90, så når indeksen vokser, vil den oppta 90 % av siden og deretter gå til neste side.
Som standard er indeksfyllfaktorverdien i SQL Server er 0, som er det samme som 100. Som et resultat arver alle nye indekser automatisk denne innstillingen med mindre du spesifikt spesifiserer en verdi i koden som er forskjellig fra systemets standardverdi eller endrer standardoppførselen. Du kan bruke SQL Server Management Studio for å justere standardverdien eller kjøre en systemlagret prosedyre sp_configure. For eksempel følgende sett T-SQL kommandoer setter koeffisientverdien til 90 (du må først bytte til avanserte innstillinger):
EXEC sp_configure "vis avanserte alternativer", 1; GÅ REKONFIGURER; GO EXEC sp_configure "fyllfaktor", 90; GÅ REKONFIGURER; GÅ
Etter å ha endret indeksfyllfaktorverdien, må du starte tjenesten på nytt SQL Server. Du kan nå sjekke den angitte verdien ved å kjøre sp_configure uten det angitte andre argumentet:
EXEC sp_configure "fyllfaktor" GO
Denne kommandoen skal returnere en verdi på 90. Som et resultat vil alle nyopprettede indekser bruke denne verdien. Du kan teste dette ved å lage en indeks og spørre etter fyllfaktorverdien:
BRUK AdventureWorks2012; -- databasen din GÅ OPPRETT IKKE-KLUSTERET INDEKS ix_people_lastname ON Person.Person(LastName); GÅ VELG fill_factor FRA sys.indexes WHERE object_id = object_id("Person.Person") OG navn="ix_people_lastname";
I dette eksemplet opprettet vi en ikke-gruppert indeks på en tabell Person i databasen AdventureWorks 2012. Etter å ha opprettet indeksen, kan vi få fyllfaktorverdien fra sys.indexes-systemtabellene. Spørringen skal returnere 90.
La oss imidlertid forestille oss at vi slettet indeksen og opprettet den på nytt, men nå spesifiserte vi en spesifikk fyllfaktorverdi:
LAG IKKE-KLUSTERET INDEKS ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); GÅ VELG fill_factor FRA sys.indexes WHERE object_id = object_id("Person.Person") OG navn="ix_people_lastname";
Denne gangen har vi lagt til instruksjoner MED og alternativ fyllfaktor for vår indeksopprettingsoperasjon LAG INDEKS og spesifiserte verdien 80. Operatør PLUKKE UT returnerer nå den tilsvarende verdien.
Så langt har alt vært ganske enkelt. Der du virkelig kan bli brent i hele denne prosessen er når du oppretter en indeks som bruker en standard koeffisientverdi, forutsatt at du kjenner den verdien. For eksempel tømmer noen med serverinnstillingene og er så sta at de setter indeksfyllingsfaktoren til 20. I mellomtiden fortsetter du å lage indekser, forutsatt at standardverdien er 0. Dessverre har du ingen mulighet til å finne ut fyllingen faktor til så lenge du ikke oppretter en indeks og deretter sjekke verdien som vi gjorde i eksemplene våre. Ellers må du vente til øyeblikket når søkeytelsen synker så mye at du begynner å mistenke noe.
Et annet problem du bør være oppmerksom på er å gjenoppbygge indekser. Som med å lage en indeks, kan du spesifisere indeksfyllfaktorverdien når du bygger den på nytt. Imidlertid, i motsetning til create index-kommandoen, bruker ikke rebuild serverens standardinnstillinger, til tross for hvordan det kan virke. Enda mer, hvis du ikke spesifikt spesifiserer indeksfyllfaktorverdien, da SQL Server vil bruke verdien av koeffisienten som denne indeksen eksisterte med før restruktureringen. For eksempel følgende operasjon ENDRINGSINDEKS gjenoppbygger indeksen vi nettopp opprettet:
ALTER INDEX ix_people_lastname ON Person.Person REBUILD; GÅ VELG fill_factor FRA sys.indexes WHERE object_id = object_id("Person.Person") OG navn="ix_people_lastname";
Når vi sjekker fyllfaktorverdien vil vi få en verdi på 80, fordi det var det vi spesifiserte da vi sist opprettet indeksen. Standardverdien ignoreres.
Som du kan se, er det ikke så vanskelig å endre indeksfyllfaktorverdien. Det er mye vanskeligere å vite den nåværende verdien og forstå når den brukes. Hvis du alltid spesifikt spesifiserer koeffisienten når du oppretter og gjenoppbygger indekser, vet du alltid det spesifikke resultatet. Med mindre du må bekymre deg for å sørge for at noen andre ikke ødelegger serverinnstillingene igjen, noe som fører til at alle indeksene blir gjenoppbygd med en latterlig lav indeksfyllingsfaktor.

Er det mulig å lage en gruppert indeks på en kolonne som inneholder duplikater?

Ja og nei. Ja, du kan opprette en gruppert indeks på en nøkkelkolonne som inneholder dupliserte verdier. Nei, verdien til en nøkkelkolonne kan ikke forbli i en ikke-unik tilstand. La meg forklare. Hvis du oppretter en ikke-unik klynget indeks på en kolonne, legger lagringsmotoren til en uniquifier til duplikatverdien for å sikre unikhet og derfor være i stand til å identifisere hver rad i den klyngede tabellen.
For eksempel kan du bestemme deg for å opprette en gruppert indeks på en kolonne som inneholder kundedata Etternavn beholde etternavnet. Kolonnen inneholder verdiene Franklin, Hancock, Washington og Smith. Så setter du inn verdiene Adams, Hancock, Smith og Smith igjen. Men verdien av nøkkelkolonnen må være unik, så lagringsmotoren vil endre verdien på duplikatene slik at de ser omtrent slik ut: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 og Smith5678.
Ved første øyekast virker denne tilnærmingen fin, men en heltallsverdi øker størrelsen på nøkkelen, noe som kan bli et problem hvis det er et stort antall duplikater, og disse verdiene vil bli grunnlaget for en ikke-klynget indeks eller en fremmed nøkkelreferanse. Av disse grunnene bør du alltid prøve å lage unike grupperte indekser når det er mulig. Hvis dette ikke er mulig, prøv i det minste å bruke kolonner med svært høy unikt verdiinnhold.

Hvordan lagres tabellen hvis en klynget indeks ikke er opprettet?

SQL Server støtter to typer tabeller: grupperte tabeller som har en gruppert indeks og haugtabeller eller bare hauger. I motsetning til grupperte tabeller, er ikke dataene på heapen sortert på noen måte. I hovedsak er dette en haug (haug) med data. Hvis du legger til en rad i en slik tabell, vil lagringsmotoren ganske enkelt legge den til på slutten av siden. Når siden er fylt med data, blir den lagt til en ny side. I de fleste tilfeller vil du lage en gruppert indeks på en tabell for å dra fordel av sorterbarhet og spørringshastighet (prøv å se for deg å slå opp et telefonnummer i en usortert adressebok). Men hvis du velger å ikke opprette en klynget indeks, kan du fortsatt lage en ikke-klynget indeks på heapen. I dette tilfellet vil hver indeksrad ha en peker til en haugrad. Indeksen inkluderer fil-ID, sidenummer og datalinjenummer.

Hva er forholdet mellom verdi-unikitetsbegrensninger og en primærnøkkel med tabellindekser?

En primærnøkkel og en unik begrensning sikrer at verdiene i en kolonne er unike. Du kan bare opprette én primærnøkkel for en tabell, og den kan ikke inneholde verdier NULL. Du kan opprette flere begrensninger på det unike til en verdi for en tabell, og hver av dem kan ha en enkelt post med NULL.
Når du oppretter en primærnøkkel, oppretter lagringsmotoren også en unik klynget indeks hvis en klynget indeks ikke allerede er opprettet. Du kan imidlertid overstyre standardoppførselen og en ikke-klynget indeks vil bli opprettet. Hvis det eksisterer en klynget indeks når du oppretter primærnøkkelen, vil en unik ikke-klynget indeks bli opprettet.
Når du oppretter en unik begrensning, lager lagringsmotoren en unik, ikke-gruppert indeks. Du kan imidlertid spesifisere opprettelsen av en unik gruppert indeks hvis en ikke har blitt opprettet tidligere.
Generelt er en unik verdibegrensning og en unik indeks det samme.

Hvorfor kalles klyngede og ikke-klyngede indekser B-tre i SQL Server?

Grunnleggende indekser i SQL Server, grupperte eller ikke-klynger, er fordelt på sett med sider kalt indeksnoder. Disse sidene er organisert i et spesifikt hierarki med en trestruktur kalt et balansert tre. På toppnivået er det rotnoden, nederst er det bladnodene, med mellomnoder mellom topp- og bunnnivået, som vist i figuren:


Rotnoden gir hovedinngangspunktet for spørringer som forsøker å hente data gjennom indeksen. Fra denne noden starter spørringsmotoren en navigering nedover i den hierarkiske strukturen til den aktuelle bladnoden som inneholder dataene.
Tenk deg for eksempel at det er mottatt en forespørsel om å velge rader som inneholder en nøkkelverdi på 82. Spørringsdelsystemet begynner å jobbe fra rotnoden, som refererer til en passende mellomnode, i vårt tilfelle 1-100. Fra den mellomliggende noden 1-100 er det overgang til noden 51-100, og derfra til den endelige noden 76-100. Hvis dette er en klynget indeks, inneholder nodebladet dataene til raden knyttet til nøkkelen lik 82. Hvis dette er en ikke-klynget indeks, så inneholder indeksbladet en peker til den klyngede tabellen eller en spesifikk rad i haugen.

Hvordan kan en indeks til og med forbedre søkeytelsen hvis du må krysse alle disse indeksnodene?

For det første forbedrer ikke indekser alltid ytelsen. For mange feilskapte indekser gjør systemet til en hengemyr og forringer søkeytelsen. Det er mer nøyaktig å si at hvis indekser brukes nøye, kan de gi betydelige ytelsesgevinster.
Tenk på en enorm bok dedikert til ytelsesjustering SQL Server(papirversjon, ikke elektronisk versjon). Tenk deg at du vil finne informasjon om konfigurering av Resource Governor. Du kan dra fingeren side for side gjennom hele boken, eller åpne innholdsfortegnelsen og finne ut det eksakte sidetallet med informasjonen du leter etter (forutsatt at boken er riktig indeksert og innholdet har riktige indekser). Dette vil garantert spare deg for betydelig tid, selv om du først må få tilgang til en helt annen struktur (indeksen) for å få informasjonen du trenger fra primærstrukturen (boken).
Som en bokindeks, en indeks i SQL Server lar deg kjøre nøyaktige spørringer på dataene du trenger i stedet for å fullstendig skanne alle dataene i en tabell. For små tabeller er en full skanning vanligvis ikke et problem, men store tabeller tar opp mange sider med data, noe som kan resultere i betydelig utføringstid for spørringer med mindre det eksisterer en indeks som lar spørringsmotoren umiddelbart få riktig plassering av dataene. Tenk deg at du går deg vill i et veikryss på flere nivåer foran en stor metropol uten kart, så skjønner du ideen.

Hvis indekser er så gode, hvorfor ikke bare lage en på hver kolonne?

Ingen god gjerning skal gå ustraffet. Det er i hvert fall tilfelle med indekser. Indekser fungerer selvfølgelig utmerket så lenge du kjører operatørhentingsspørringer PLUKKE UT, men så snart hyppige samtaler til operatører begynner SETT INN, OPPDATER Og SLETT, så landskapet endrer seg veldig raskt.
Når du starter en dataforespørsel fra operatøren PLUKKE UT, finner spørringsmotoren indeksen, beveger seg gjennom trestrukturen og oppdager dataene den leter etter. Hva kan være enklere? Men ting endres hvis du starter en endringserklæring som OPPDATER. Ja, for den første delen av setningen kan spørringsmotoren igjen bruke indeksen for å finne raden som endres - det er gode nyheter. Og hvis det er en enkel endring i data på rad som ikke påvirker endringer i nøkkelkolonner, så vil endringsprosessen være helt smertefri. Men hva om endringen fører til at sidene som inneholder dataene deles, eller verdien til en nøkkelkolonne endres slik at den flyttes til en annen indeksnode - dette vil resultere i at indeksen muligens trenger en omorganisering som påvirker alle tilknyttede indekser og operasjoner , noe som resulterer i omfattende nedgang i produktiviteten.
Lignende prosesser oppstår når du ringer en operatør SLETT. En indeks kan hjelpe med å finne dataene som slettes, men sletting av selve dataene kan føre til omstokking av siden. Angående operatøren SETT INN, hovedfienden til alle indekser: du begynner å legge til en stor mengde data, noe som fører til endringer i indekser og deres omorganisering og alle lider.
Så vurder hvilke typer søk til databasen din når du tenker på hvilken type indekser og hvor mange du skal lage. Mer betyr ikke bedre. Før du legger til en ny indeks i en tabell, bør du vurdere kostnadene for ikke bare de underliggende spørringene, men også mengden diskplass som forbrukes, kostnadene for å vedlikeholde funksjonalitet og indekser, noe som kan føre til en dominoeffekt på andre operasjoner. Indeksdesignstrategien din er en av de viktigste aspektene ved implementeringen din og bør inkludere mange hensyn, fra størrelsen på indeksen, antall unike verdier, til typen søk indeksen vil støtte.

Er det nødvendig å lage en gruppert indeks på en kolonne med en primærnøkkel?

Du kan opprette en gruppert indeks på en hvilken som helst kolonne som oppfyller de nødvendige betingelsene. Det er sant at en gruppert indeks og en primærnøkkelbegrensning er laget for hverandre og er en match laget i himmelen, så forstå det faktum at når du oppretter en primærnøkkel, vil en gruppert indeks automatisk opprettes hvis en ikke har blitt opprettet før. Du kan imidlertid bestemme at en gruppert indeks vil gi bedre resultater andre steder, og ofte vil avgjørelsen din være berettiget.
Hovedformålet med en gruppert indeks er å sortere alle radene i tabellen din basert på nøkkelkolonnen som er spesifisert når du definerer indeksen. Dette gir raskt søk og enkel tilgang til tabelldata.
En tabells primærnøkkel kan være et godt valg fordi den identifiserer hver rad i tabeller unikt uten å måtte legge til ytterligere data. I noen tilfeller vil det beste valget være en surrogat primærnøkkel, som ikke bare er unik, men også liten i størrelse og hvis verdier øker sekvensielt, noe som gjør ikke-klyngede indekser basert på denne verdien mer effektive. Spørringsoptimereren liker også denne kombinasjonen av en klynget indeks og en primærnøkkel fordi sammenføyning av tabeller er raskere enn å slå sammen på en annen måte som ikke bruker en primærnøkkel og den tilhørende klyngeindeksen. Som jeg sa, det er en match made in heaven.
Til slutt er det imidlertid verdt å merke seg at når du oppretter en klynget indeks, er det flere aspekter å vurdere: hvor mange ikke-klyngede indekser vil være basert på den, hvor ofte verdien av nøkkelindekskolonnen vil endre seg, og hvor stor. Når verdiene i kolonnene til en gruppert indeks endres eller indeksen ikke fungerer som forventet, kan alle andre indekser på tabellen bli påvirket. En gruppert indeks bør være basert på den mest vedvarende kolonnen hvis verdier øker i en bestemt rekkefølge, men ikke endres på en tilfeldig måte. Indeksen må støtte spørringer mot tabellens mest brukte data, så spørringene drar full nytte av det faktum at dataene er sortert og tilgjengelig ved rotnodene, bladene i indeksen. Hvis primærnøkkelen passer til dette scenariet, bruk den. Hvis ikke, velg et annet sett med kolonner.

Hva om du indekserer en visning, er den fortsatt en visning?

En visning er en virtuell tabell som genererer data fra en eller flere tabeller. I hovedsak er det en navngitt spørring som henter data fra de underliggende tabellene når du spør etter den visningen. Du kan forbedre spørringsytelsen ved å opprette en klynget indeks og ikke-klyngede indekser i denne visningen, på samme måte som hvordan du oppretter indekser på en tabell, men hovedpåminnelsen er at du først oppretter en klynget indeks, og deretter kan du opprette en ikke-klynget.
Når en indeksert visning (materialisert visning) opprettes, forblir selve visningsdefinisjonen en egen enhet. Dette er tross alt bare en hardkodet operatør PLUKKE UT, lagret i databasen. Men indeksen er en helt annen historie. Når du oppretter en klynget eller ikke-klynget indeks på en leverandør, lagres dataene fysisk på disken, akkurat som en vanlig indeks. I tillegg, når data endres i underliggende tabeller, endres visningens indeks automatisk (dette betyr at du kanskje vil unngå å indeksere visninger på tabeller som endres ofte). I alle fall forblir visningen en visning - en visning av tabellene, men en utført for øyeblikket, med indekser som tilsvarer den.
Før du kan opprette en indeks på en visning, må den oppfylle flere begrensninger. For eksempel kan en visning bare referere til basistabeller, men ikke andre visninger, og disse tabellene må være i samme database. Det er faktisk mange andre restriksjoner, så sørg for å sjekke dokumentasjonen for SQL Server for alle de skitne detaljene.

Hvorfor bruke en dekkende indeks i stedet for en sammensatt indeks?

Først, la oss sørge for at vi forstår forskjellen mellom de to. En sammensatt indeks er ganske enkelt en vanlig indeks som inneholder mer enn én kolonne. Flere nøkkelkolonner kan brukes for å sikre at hver rad i en tabell er unik, eller du kan ha flere kolonner for å sikre at primærnøkkelen er unik, eller du prøver kanskje å optimalisere utførelsen av ofte påkalte spørringer på flere kolonner. Generelt er det imidlertid slik at jo flere nøkkelkolonner en indeks inneholder, desto mindre effektiv vil indeksen være, noe som betyr at sammensatte indekser bør brukes fornuftig.
Som nevnt kan en spørring ha stor nytte hvis alle nødvendige data umiddelbart er plassert på bladene av indeksen, akkurat som selve indeksen. Dette er ikke et problem for en gruppert indeks fordi alle dataene er der allerede (det er derfor det er så viktig å tenke nøye gjennom når du oppretter en gruppert indeks). Men en ikke-gruppert indeks på blader inneholder bare nøkkelkolonner. For å få tilgang til alle de andre dataene krever spørringsoptimereren ytterligere trinn, som kan legge til betydelige kostnader for å utføre spørringene dine.
Det er her dekningsindeksen kommer til unnsetning. Når du definerer en ikke-klynget indeks, kan du spesifisere flere kolonner til nøkkelkolonner. La oss for eksempel si at applikasjonen din ofte spør etter kolonnedata Bestillings ID Og Bestillingsdato i bordet Salg:
VELG OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
Du kan opprette en sammensatt ikke-klynget indeks på begge kolonnene, men OrderDate-kolonnen vil bare legge til indeksvedlikeholdskostnader uten å tjene som en spesielt nyttig nøkkelkolonne. Den beste løsningen ville være å lage en dekkende indeks på nøkkelkolonnen Bestillings ID og i tillegg inkludert kolonne Bestillingsdato:
LAG IKKE-KLUSTERET INDEKS ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);
Dette unngår ulempene med å indeksere overflødige kolonner, samtidig som fordelene med å lagre data i blader opprettholdes når du kjører spørringer. Den inkluderte kolonnen er ikke en del av nøkkelen, men dataene lagres på bladnoden, indeksbladet. Dette kan forbedre søkeytelsen uten ekstra overhead. I tillegg er kolonnene som inngår i den dekkende indeksen underlagt færre restriksjoner enn nøkkelkolonnene i indeksen.

Spiller antallet duplikater i en nøkkelkolonne noen rolle?

Når du oppretter en indeks, må du prøve å redusere antall duplikater i nøkkelkolonnene dine. Eller mer presist: prøv å holde repetisjonsfrekvensen så lav som mulig.
Hvis du arbeider med en sammensatt indeks, gjelder dupliseringen for alle nøkkelkolonner som helhet. En enkelt kolonne kan inneholde mange dupliserte verdier, men det bør være minimal repetisjon blant alle indekskolonnene. For eksempel oppretter du en sammensatt ikke-klynget indeks på kolonner Fornavn Og Etternavn, du kan ha mange John Doe-verdier og mange Doe-verdier, men du vil ha så få John Doe-verdier som mulig, eller helst bare én John Doe-verdi.
Unikhetsforholdet til en nøkkelkolonnes verdier kalles indeksselektivitet. Jo flere unike verdier det er, jo høyere er selektiviteten: en unik indeks har størst mulig selektivitet. Spørringsmotoren liker veldig godt kolonner med høye selektivitetsverdier, spesielt hvis disse kolonnene er inkludert i WHERE-klausulene til de mest utførte spørringene. Jo mer selektiv indeksen er, desto raskere kan søkemotoren redusere størrelsen på det resulterende datasettet. Ulempen er selvfølgelig at kolonner med relativt få unike verdier sjelden vil være gode kandidater for indeksering.

Er det mulig å lage en ikke-klynget indeks på kun et spesifikt delsett av en nøkkelkolonnes data?

Som standard inneholder en ikke-klynget indeks én rad for hver rad i tabellen. Selvfølgelig kan du si det samme om en gruppert indeks, forutsatt at en slik indeks er en tabell. Men når det kommer til en ikke-klynget indeks, er en-til-en-forholdet et viktig konsept fordi, fra og med versjon SQL Server 2008, har du muligheten til å lage en filtrerbar indeks som begrenser radene som er inkludert i den. En filtrert indeks kan forbedre søkeytelsen fordi... den er mindre i størrelse og inneholder filtrert, mer nøyaktig statistikk enn alle tabeller - dette fører til opprettelse av forbedrede utførelsesplaner. En filtrert indeks krever også mindre lagringsplass og lavere vedlikeholdskostnader. Indeksen oppdateres bare når dataene som samsvarer med filteret endres.
I tillegg er en filtrerbar indeks enkel å lage. I operatøren LAG INDEKS du trenger bare å indikere HVOR filterets tilstand. For eksempel kan du filtrere ut alle rader som inneholder NULL fra indeksen, som vist i koden:
LAG IKKE-KLUSTERET INDEKS ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber IKKE ER NULL;
Vi kan faktisk filtrere ut alle data som ikke er viktige i kritiske spørsmål. Men vær forsiktig, for... SQL Server pålegger flere begrensninger på filtrerbare indekser, for eksempel manglende evne til å lage en filtrerbar indeks på en visning, så les dokumentasjonen nøye.
Det kan også være at du kan oppnå lignende resultater ved å lage en indeksert visning. En filtrert indeks har imidlertid flere fordeler, for eksempel muligheten til å redusere vedlikeholdskostnadene og forbedre kvaliteten på utførelsesplanene dine. Filtrerte indekser kan også gjenoppbygges online. Prøv dette med en indeksert visning.

Og igjen litt fra oversetteren

Hensikten med opptredenen av denne oversettelsen på sidene til Habrahabr var å fortelle eller minne deg om SimpleTalk-bloggen fra RedGate.
Den publiserer mange underholdende og interessante innlegg.
Jeg er ikke tilknyttet noen firmaprodukter RedGate, og heller ikke med deres salg.

Som lovet, bøker for de som vil vite mer
Jeg anbefaler tre veldig gode bøker fra meg selv (lenker fører til tenne versjoner i butikken Amazon):

I prinsippet kan du åpne enkle indekser

for nybegynnere

indeks

Legg til merkelapper

Microsoft SQL Server 2012 T-SQL Fundamentals (utviklerreferanse) Forfatter Itzik Ben-Gan Publiseringsdato: 15. juli 2012 Forfatteren, en mester i sitt håndverk, gir grunnleggende kunnskap om arbeid med databaser. Hvis du har glemt alt eller aldri visste, er det absolutt verdt å lese.

ROWID-indekser er databaseobjekter som gir en visning av alle verdiene i en tabellkolonne, samt ROWID-ene til alle radene i tabellen som inneholder kolonnens verdier.

ROWID er en pseudo-kolonne som er en unik identifikator for en rad i en tabell og faktisk beskriver den nøyaktige fysiske plasseringen til den bestemte raden. Basert på denne informasjonen Oracle kan deretter finne dataene knyttet til tabellraden. Hver gang en rad flyttes, eksporteres, importeres eller andre operasjoner som endrer plassering, vises ROWID linje fordi den inntar en annen fysisk posisjon. For datalagring ROWID 80 bits (10 bytes) kreves. Identifikatorer ROWID består av fire komponenter: objektnummer (32 bits), relativ filnummer (10 bits), blokknummer (22 bits) og linjenummer (16 bits). Disse identifikatorene vises som 18-tegnssekvenser som indikerer plasseringen av dataene i databasen, med hvert tegn representert i base-64-format bestående av tegnene A-Z, a-z, 0-9, + og /. De første seks tegnene er dataobjektnummeret, de neste tre er det relative filnummeret, de neste seks er blokknummeret, og de tre siste er linjenummeret.

Eksempel:

SELECT fam, ROWID FRA student;

FAM ROWID

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

IVANOV AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

I databasen Oracle indekser brukes til forskjellige formål: for å sikre unike verdier i databasen, for å forbedre ytelsen til å søke etter poster i en tabell, etc. Ytelsen forbedres ved å inkludere en referanse til den eller de indekserte kolonnene i søkekriteriene for data i tabellen. I Oracle indekser kan opprettes på alle tabellkolonnene bortsett fra LANGE kolonner. Indekser skiller mellom hastighetsufølsomme applikasjoner og applikasjoner med høy ytelse, spesielt når du arbeider med store tabeller. Før du bestemmer deg for å lage en indeks, må du imidlertid veie fordeler og ulemper når det gjelder systemytelse. Ytelsen blir ikke bedre hvis du bare skriver inn en indeks og glemmer den.

Selv om den største ytelsesforbedringen kommer fra å lage en indeks på en kolonne der alle verdier er unike, kan du få lignende resultater for kolonner som inneholder duplikat- eller NULL-verdier. Det er ikke nødvendig at kolonneverdiene er unike for å lage en indeks. Her er noen anbefalinger for å hjelpe deg med å oppnå ønsket ytelsesøkning når du bruker en standardindeks, og vi vil også se på problemer knyttet til balansen mellom ytelse og diskplassforbruk når du oppretter en indeks.

Å bruke indekser til å slå opp informasjon i tabeller kan gi betydelige ytelsesforbedringer i forhold til å skanne tabeller hvis kolonner ikke er indeksert. Men å velge riktig indeks er slett ikke lett. Selvfølgelig er en kolonne hvis verdier alle er unike å foretrekke for indeksering med en B-treindeks, men en kolonne som ikke oppfyller disse kravene er en god kandidat så lenge omtrent 10 % av radene inneholder identiske verdier og ikke mer. "Switch" eller "flag"-kolonner, for eksempel de som lagrer informasjon om en persons kjønn, er ikke egnet for B-tree-indekser. Kolonner som brukes til å lagre et lite antall "pålitelige verdier", samt de som lagrer visse verdier, er heller ikke egnet, da tegn, for eksempel "pålitelighet" eller "upålitelighet", "aktivitet" eller "inaktivitet", "ja" eller "nei", osv. osv. Til slutt er indekser med reverseringsnøkler brukes som regel der hvor den er installert og fungerer Oracle Parallel Server og du må øke nivået av parallellitet i databasen til det maksimale.