Att använda index och utföra tabellskanningar är två grundläggande metoder för att komma åt data i en databas, var och en med distinkta avvägningar beroende på situationen.
Ett index i en databas är en datastruktur som gör det möjligt för snabbare uppslagning genom att låta systemet snabbt hitta rader utan att skanna varje rad i tabellen. De flesta relationsdatabaser använder B+ trädstrukturer för index, som organiserar nycklar och pekare i en trädform. Detta tillåter sökningar, insertioner och borttagningar i logaritmisk tidskomplexitet $$ o (\ log n) $$, vilket vanligtvis är mycket snabbare än att skanna hela tabellen med en komplexitet på $$ o (n) $$. Index kan klusteras eller inte klusteras, med klusterindex som lagrar data i fysiskt sorterad ordning, vilket förbättrar intervallskanningsprestanda till kostnaden för extra omkostnader vid datamodifieringar. Index kan också vara sammansatta, partiella, filtrerade eller hashbaserade, inställda för specifika frågemönster.
Däremot läser en tabellscanning (eller full tabellskanning) varje rad i tabellen i följd, oavsett frågaens selektivitet. Detta innebär att skanna alla datablock i tabellen och anses ofta vara den dyraste åtkomstmetoden eftersom den bearbetar mer data än nödvändigt. Tabellskanningar kan dock fungera bra i vissa fall. Till exempel, när frågor hämtar en stor andel rader, kan omkostnaderna för att använda ett index (som ofta kräver ytterligare uppslagning för de faktiska raderna) överstiga kostnaden för att skanna hela tabellen en gång. Tabellskanningar kan använda multi-blockläsningar, som tillåter läsning av stora bitar av data med färre I/O-operationer, vilket minskar latensen jämfört med att läsa många enskilda block som slumpmässigt krävs av indexskanningar.
En stor avvägning innebär selektivitet och storlek på datauppsättningen som returneras av frågan. Om frågan filtrerar ner till ett litet antal rader (hög selektivitet) överträffar index i allmänhet tabellskanningar eftersom de bara behöver komma åt relevant information. Men när procentandelen av rader som återlämnats ökar ökar kostnaden för indexskanningar eftersom flera nyckeluppslagning kan krävas, och databasmotorn måste utföra ytterligare slumpmässiga I/O -operationer. Vid någon tröskel, ofta cirka 10-20% av tabellens rader men beroende på databredd och hårdvara, blir en fullständig tabellskanning effektivare. Detta beror på att skanningskostnaderna förblir konstant oavsett selektivitet och helt enkelt läser tabellen i följd en gång.
Indexskanningar läser vanligtvis färre sidor än en tabellskanning när de täckta kolumnerna är färre eller mer kompakta än de fullständiga tabellraderna. Till exempel kan ett index endast inkludera de indexerade kolumnerna utan hela tabellraddata, vilket gör det tunnare och tillåter fler rader att passa på varje databassida. Detta minskar I/O -omkostnaden när du skannar indexet jämfört med att skanna hela tabelldata. Dessutom kan vissa index filtreras (partiella index) för att utesluta irrelevanta rader, vilket ytterligare reducerar skanningsavtrycket.
Å andra sidan skriver fullständiga tabellskanningar mindre börda på databasunderhållssidan. Index introducerar omkostnader under datamodifieringsoperationer som infoga, uppdatering och radering. Varje ändring av tabellen kräver uppdateringsindex, vilket ibland leder till ökad skrivfördröjning och lagringsomkoppling, särskilt om många index finns på tabellen. Denna overhead kan också påverka samtidighet och leda till strid i tunga skrivmiljöer. Således undviker tabellskanningar, som helt enkelt läser uppgifterna i sin naturliga ordning utan ytterligare strukturunderhåll, denna kostnad.
En annan viktig övervägning är effekten av cache- och hårdvaruegenskaper. Tabellskanningar drar nytta av sekventiell I/O och prefetching, vilket gör att systemet kan läsa flera sammanhängande block effektivt, ofta från minnet om det är cachat. Omvänt har indexskanningar slumpmässiga I/O för att hämta olika datablock, särskilt om indexskanningen måste leta upp radpekare till höglagring. Detta kan göra indexskanningar långsammare på system med långsammare skiva slumpmässiga I/O -prestanda, även om SSD: er och stora minnespooler begränsar detta gap. Situationen kan också bero på detaljer som parallellism och multi-threading-kapacitet för databasmotorn, där parallella tabellskanningar kan öka genomströmningen avsevärt.
Dessutom påverkar den interna fragmenteringen och fysisk lagringslayout prestandavvägningarna. Tabellskanningar på högorganiserade tabeller kan drabbas av vidarebefordrade poster, där rader har flyttat till olika sidor på grund av uppdateringar, förvärrade skanningseffektivitet. Klusterindex, som lagrar data sorterade efter nyckel, kan undvika detta problem och ibland göra en "tabellskanning" motsvarande en klusterindexskanning. Fördelarna kommer emellertid med kostnaden för dyra raden ombeställning under tung datakurn.
Ur ett frågeformulärperspektiv görs beslutet mellan en indexskanning och en tabellskanning vanligtvis av kostnadsbaserade uppskattningsmodeller, med hänsyn till statistik om datadistribution, radräkningar och hårdvarukostnader. Optimizer balanserar CPU, I/O och minneskostnader för att välja den mest effektiva åtkomstvägen. Dessa beslut kan påverkas av faktorer som tillgängligt minne, cachningstillstånd och frågemönster. Det finns ingen fast tröskel mellan när man ska använda det ena eller det andra; Crossover -punkten varierar per system och arbetsbelastning.
Sammanfattningsvis inkluderar avvägningarna mellan att använda index och tabellskanningar:
- Prestanda kontra datavolym: Index överträffar vid filtrering till några rader; Tabellskanningar kan vara bättre för stor datainhämtning.
- I/O -mönster: Indexskanningar orsakar slumpmässiga I/O -läsningar; Tabellskanningar drar nytta av sekventiella I/O och multi-blockläsningar.
- Underhållskostnad: Index ökar skrivkostnaderna på grund av uppdateringar av indexstrukturerna; Tabellskanningar har inte detta.
- Lagringseffektivitet: Index kan vara kompakta genom att täcka färre kolumner; Tabellskanningar bearbetar fulla rader och potentiellt mer data.
- Cacheffekter: Tabellskanningar kan använda data caching effektivt, särskilt med stora sekventiella läsningar; Indexskanningar kanske inte gynnas lika mycket på grund av slumpmässig åtkomst.
- Optimeringsbeslut: Kostnadsbaserade frågeformulatorer väljer dynamiskt mellan dessa alternativ baserat på frågestatistik och arbetsbelastningsspecifikationer.
- Effekten av datlayout: HEAP -tabeller kan medföra påföljder som vidarebefordrade poster under skanningar; Klusterindex organiserar data fysiskt men ökar uppdateringskostnaderna.