Å bruke indekser og utføre tabellskanninger er to grunnleggende tilnærminger til å få tilgang til data i en database, hver med distinkte avveininger avhengig av situasjonen.
En indeks i en database er en datastruktur som muliggjør raskere oppslag ved å la systemet raskt finne rader uten å skanne hver rad i tabellen. De fleste relasjonsdatabaser bruker B+ trestrukturer for indekser, som organiserer nøkler og pekere i en treform. Dette tillater søk, innsettinger og slettinger i logaritmisk tidskompleksitet $$ o (\ log n) $$, som vanligvis er mye raskere enn å skanne hele tabellen med en kompleksitet på $$ o (n) $$. Indekser kan grupperes eller ikke-klynge, med grupperte indekser som lagrer data i fysisk sortert rekkefølge, noe som forbedrer rekkevidde-skanningsytelsen på bekostning av ekstra overhead på datamodifikasjoner. Indekser kan også være sammensatte, delvis, filtrert eller hasjbasert, innstilt for spesifikke spørringsmønstre.
Derimot leser en tabellskanning (eller full tabellskanning) hver rad i tabellen sekvensielt, uavhengig av spørringens selektivitet. Dette innebærer å skanne alle datablokkene i tabellen og regnes ofte som den dyreste tilgangsmetoden fordi den behandler mer data enn nødvendig. Imidlertid kan bordskanninger prestere godt i visse tilfeller. For eksempel, når spørsmål henter en stor prosentandel av rader, kan overhead ved bruk av en indeks (som ofte krever ytterligere oppslag for de faktiske radene) overstige kostnadene for å skanne hele tabellen en gang. Tabellskanninger kan benytte seg av multi-block-avlesninger, som tillater å lese store biter av data med færre I/O-operasjoner, og dermed redusere latensen sammenlignet med å lese mange individuelle blokker som er tilfeldig påkrevd av indeksskanninger.
En større avveining innebærer selektiviteten og størrelsen på datasettet som er returnert av spørringen. Hvis spørringen filtrerer ned til et lite antall rader (høy selektivitet), overgår indekser generelt tabellskanninger fordi de bare trenger å få tilgang til de aktuelle dataene. Men når prosentandelen av rader som returneres øker, øker kostnadene for indeksskanningen siden flere nøkkeloppslag kan være nødvendige, og databasemotoren må utføre ytterligere tilfeldige I/O -operasjoner. Ved en eller annen terskel, ofte rundt 10-20% av tabellens rad, men avhengig av databredde og maskinvare, blir en full tabellskanning mer effektiv. Dette er fordi skanningskostnadene forblir konstante uavhengig av selektiviteten, og bare leser tabellen sekvensielt en gang.
Indeksskanninger leser typisk færre sider enn en tabellskanning når de overbygde kolonnene er færre eller mer kompakte enn de fulle tabellradene. For eksempel kan en indeks bare omfatte de indekserte kolonnene uten de fulle tabellrekkedataene, noe som gjør den tynnere og lar flere rader passe på hver databaseside. Dette reduserer I/O -overhead når du skanner indeksen sammenlignet med å skanne hele tabelldataene. I tillegg kan noen indekser filtreres (delvise indekser) for å ekskludere irrelevante rader, noe som reduserer skanneavtrykket ytterligere.
På den annen side skriver fulle bordskanninger mindre belastning på vedlikeholdssiden for databasen. Indekser introduserer overhead under datamodifiseringsoperasjoner som innsats, oppdatering og sletting. Hver endring i tabellen krever oppdatering av indekser, noe som noen ganger fører til økt skriveforsinkelse og lagringskostnader, spesielt hvis det finnes mange indekser på bordet. Denne overhead kan også påvirke samtidigheten og føre til strid i tunge skrivemiljøer. Dermed unngår tabellskanninger, som ganske enkelt leser dataene i sin naturlige rekkefølge uten ytterligere vedlikehold av strukturer, denne kostnaden.
En annen viktig vurdering er effekten av hurtigbufring og maskinvareegenskaper. Tabellskanninger drar nytte av sekvensiell I/O og forhåndshentning, slik at systemet kan lese flere sammenhengende blokker effektivt, ofte fra minnet hvis hurtigbufret. Motsatt, indeksskanninger pådrar seg tilfeldige I/O til å hente forskjellige datablokker, spesielt hvis indeksskanningen må slå opp radpekere til heaplagring. Dette kan gjøre indeksskanninger tregere på systemer med langsommere disk tilfeldig I/O -ytelse, selv om SSD -er og store minnebassenger begrenser dette gapet. Situasjonen kan også avhenge av spesifikasjoner som parallellisme og flertrådende evner til databasemotoren, der parallelle tabellskanninger kan øke gjennomstrømningen betydelig.
I tillegg påvirker den interne fragmenteringen og den fysiske lagringsoppsettet resultatene. Bordskanninger på haugorganiserte tabeller kan lide av videresendte poster, der rader har flyttet til forskjellige sider på grunn av oppdateringer, forverret skanningseffektivitet. Klyngede indekser, som lagrer data sortert etter tast, kan unngå dette problemet og noen ganger lage en "tabellskanning" som tilsvarer en gruppert indeksskanning. Fordelene kommer imidlertid med kostnadene for dyre radbestillinger under tunge datakurr.
Fra et spørringsoptimisatorperspektiv er beslutningen mellom en indeksskanning og en tabellskanning vanligvis tatt av kostnadsbaserte estimeringsmodeller, med hensyn til statistikk over datadistribusjon, radtelling og maskinvarekostnader. Optimaliseren balanserer CPU, I/O og minnekostnader for å velge den mest effektive tilgangsbanen. Disse beslutningene kan påvirkes av faktorer som tilgjengelig minne, hurtigbufring og spørringsmønstre. Det er ingen fast terskel mellom når du skal bruke den ene eller den andre; Crossover -punktet varierer per system og arbeidsmengde.
Oppsummert inkluderer avveiningene mellom bruk av indekser og tabellskanninger:
- Ytelsen kontra datavolum: Indekser overgår når du filtrerer til få rader; Tabellskanninger kan være bedre for stor datainnhenting.
- I/O -mønstre: Indeksskanninger forårsaker tilfeldige I/O -leser; Tabellskanninger drar nytte av sekvensielle I/O- og multi-block-avlesninger.
- Vedlikeholdskostnader: Indekser Øker skriveoperasjonskostnader på grunn av oppdateringer om indeksstrukturene; Bordskanninger pådrar seg ikke dette.
- Lagringseffektivitet: Indekser kan kompakte ved å dekke færre kolonner; Tabellskanninger behandler fulle rader og potensielt mer data.
- Cacheeffekter: Bordskanninger kan bruke data -hurtigbufring effektivt, spesielt med store sekvensielle avlesninger; Indeksskanninger har kanskje ikke så mye på grunn av tilfeldig tilgang.
- Optimaliseringsavgjørelse: Kostnadsbasert spørring Optimizers velger dynamisk mellom disse alternativene basert på spørringsstatistikk og spesifikasjoner for arbeidsmengde.
- Effekten av datalayout: HAP -tabeller kan pådra seg straffer som videresendte poster under skanninger; Klyngede indekser organiserer data fysisk, men øker oppdateringskostnadene.