Vi som arbetar med databaser känner alla till att en databas fungerar utmärkt i början, när alla förutsättningar är som förväntade och antalet användare är lågt. Vi har alla fått erfara att med tiden så brukar databasen kännas slöare och slöare. Men hav förtröstan, det finns lösningar på dina problem. Och de behöver inte kosta skjortan.
Se det kompletta utbudet för dig som vill lära dig mer om Databaser.
För att börja från början så innefattar inte optimering av databasservrar bara mer och mer minne, och mer och mer processorkraft, och snabbare och större disksystem. Även fast dessa faktorer är av stor vikt. Du behöver mer minne när du vill kunna bearbeta fler frågor, hantera fler samtidiga användare och större datamängder. Du behöver likväl mer processorkraft när du vill arbeta med fler och mer komplexa frågor och när antalet användare växer. Och snabbare och större disksystem när informationsmängden ökar och när man vill förbättra svarstider. När man diskuterar hårdvara så är det ju synd om man glömmer nätverket. För att öka genomströmningen av frågor och svar till servern så bör man även komplettera med ytterligare nätverksinterface, kanske dedicera nätverkskort till ett backbone för replikering och så vidare.
Det som verkligen avgör om databasen fungerar bra är hur den är organiserad. Microsoft SQL Server 2005 använder sig av index; hierarkiskt ordnade binära trädstrukturer, för att lokalisera data i datafilerna, tillika tabellerna. Det finns två typer av tabeller; heap och klustrade index.
Ett heap är en tabell som fysiskt är organiserad med kedjor av datasidor i datafilen, där den första datasidan länkar vidare till den andra och så vidare. Enkelt beskrivet fungerar ett heap på samma sätt som filsystemet där datasidorna då är sektorer. Raderna i ett heap lagras fysiskt efter hur de läggs till i tabellen.
Ett klustrat index däremot lagrar informationen fysiskt organiserat utifrån den indexerade kolumnen och använder sig av ett hierarkiskt träd för att lokalisera de datasidor som innehåller de eftersökta tabellraderna.
För att förenkla det hela, tänk dig ett personregister med en kolumn för Ort. Om du väljer att skapa ett klustrat index baserat på kolumnen Ort kommer alla personuppgifter som befinner sig i samma Ort också fysiskt placeras tillsammans i datafilen. Om du väljer att söka på alla personer som finns i Ludvika så kommer det klustrade indexet att traverseras och generera en lista på de datasidor som innehåller rader där orten Ludvika ingår. Dessa datasidor kommer också fysiskt vara organiserade tillsammans. Det här gör att databasservern kan läsa dessa datasidor effektivt i block om 64 kB, det vill säga åtta datasidor i varje läsoperation, samt att dessa block kommer kunna läsas i en serie från disksubsystemet.
Det ger en snabb läsning och datat kommer returneras till användaren på nolltid. Om nu däremot denna tabell varit ett heap så hade databasservern varit tvungen att läsa samtliga datasidor för denna tabell för att sedan kunna returnera de önskade raderna.
Om det här registret skulle innehålla hela Sveriges befolkning så skulle databasservern behöva läsa 9 000 000 rader för att returnera cirka 30 000 rader. Med ett klustrat index på den önskade kolumnen skulle då diskoperationen minska till ca 3 procent och därmed kunna returnera data både mycket effektivare och snabbare. Det finns även något som heter icke-klustrade index. Det är samma typ av hierarkiska trädstruktur som i ett klustrat index, som istället för att peka på de fysiska datasidorna har så kallade lövobjekt som innehåller pekare till de fysiska datasidorna och tabellraderna alternativt en dirigering in i det klustrade indexets trädstruktur. Det som avgör vad lövobjekten pekar på är om tabellen är ett heap eller ett klustrat index. Icke-klustrade index är speciellt effektiva på att lokalisera unika, eller relativt unika, rader eftersom de genererar en lista på datasidor som ska läsas igenom. Eftersom det icke-klustrade indexet inte återspeglar den fysiskt sorteringen av data kan det i vissa fall vara effektivare att databasservern istället läser igenom hela tabellen för att lokalisera ditt urval.
För att få hjälp med att bygga en effektiv indexstruktur finns det två verktyg till du kan använda. Dels så har du Database Engine Tuning Advisor, som används för att scanna igenom databasen och titta på hur tabellerna hänger samman samt att den analyserar resultatet och ger dig förslag på optimal indexering. Du har även SQL Server Profiler, en "sniffer" som kan spela in alla händelser på databasservern till en så kallad fångstfil. Denna fångstfil kan du använda tillsammans med Database EngineTuning Advisor för att kunna få en realistisk bild av det faktiska nyttjandet av databasen. När Database Engine Tuning Advisor analyserar databasen kommer den att använda fångstfilen för att se hur dina användare arbetar med databasen och därmed kunna göra en verklighetsanpassad analys och ge dig den effektivaste indexstrategin. För att databasservern sedan ska kunna välja ut de bästa indexen till just din fråga gör den en analys på dels vilka index som finns och refereras i din fråga tillsammans med indexens statistikinformation. Resultatet från denna process innefattar en lista på de utvalda indexen som kommer att användas. Denna statistik måste underhållas tillsammans med indexträden allteftersom data i tabellerna tillkommer eller förändras. Till detta finns ytterligare en fin funktion med i de administrativa verktygen. Maintenence Plan Wizard som hjälper dig att schemalägga indexeringar och uppdatering av statistikinformationen tillsammans med säkerhetskopiering och annat underhåll.
Ytterligare värt att nämna är bland annat att du bör ange en så kallad fyllnadsfaktor för indexstrukturen. En hög fyllnadsprocent ger ett mer kompakt index som ger snabba svar vid sökning men som däremot kan svara långsamt vid hög nivå av förändrande operationer. En låg fyllnadsprocent ger däremot en något långsammare traversering. Men det skapar istället förallokerat utrymme för förändrande operationer. Även ett utbyggt disksubsystem är av fördel när man vill arbeta med flera index och tabeller. Till exempel kan man dela upp databasen på två disksubsystem där data för tabell A och index för tabell B placeras på det ena disksubsystemet och data för tabell B och index för tabell A på det andra. Det medför att du kan arbeta med parallell diskåtkomst och på så sätt slippa köbildning mot disksubsystemet och därmed få ökad prestanda.
Givetvis är inte enbart index den optimala metoden för att öka prestanda i databasapplikationen. Till detta rekommenderas en genomtänkt normalisering tillsammans med viss denormalisering. Som ett exempel tänker jag på ett adressregister över 100 städer, med 100 adresser per stad och 100 personer per adress. En totalt flat struktur för detta data är då en tabell innehållande tre kolumner; Namn, Gata och Ort. Tänk dig att namnet är 100 byte, gatan behöver 50 byte och att staden behöver 25 byte. Det innebär 175 B data per rad och 1 000 000 rader. En datasida rymmer 8060 B och således 45 rader. (Radoverhead på 4 B plus kolumnerna 175 B.) Det ger oss 22 223 datasidor á 8 Kb som totalt blir nära 174 Mb.
Om du däremot bryter upp denna tabell i fyra tabeller; Individer (FörnamnsID (4B), EfternamnsID (4B), GatuID (4B)), Gator (GatuID(4B), GatunamnsID (4B), OrtID (4B)) och Orter (OrtID (4B), OrtnamnsID (4B)) samt Namn (NamnID (4B), Namnet (50B)). Det gör att Ortstabellen blir 8 Kb, Gatutabellen 160 Kb och Individtabellen ca 16 Mb. Sedan förutsätter vi att vi har 75 000 för-, efter-, gatu och ortsnamn i Namn-tabeller, den blir då ca 5 Mb. Bara genom denna normalisering har vi minskat lagringsutrymmet till ca 12,7 procent. Nu är varken 124 Mb eller 22 Mb mycket data för en databasserver att hantera men tänk er att ni bakar in ytterligare data och ni vill ha mer funktionalitet än just bara ett rent adressregister så blir lätt datamängden oerhört mycket mer.
Nu bör man även utifrån affärsnyttan göra en denormalisering för att få enklare och snabbare frågor. I ovanstående exempel kanske du vill kunna välja ut individer baserat på ort. Genom att integrera en kolumn för OrtID i Individtabellen kan du dra nytta av en indexstruktur för att göra snabba urval. Ett exempel på det skulle kunna vara att vi gör ovanstående lösning baserad på två tabeller; Personer (FörnamnsID (4B), EfternamnsID (4B), GatunamnsID (4B) och OrtnamnsID (4B)) samt Namn (NamnID (4B), Namnet (50B)). Personer blir då 23 Mb och Namn blir 5 Mb, totalt 28 Mb. Denna denormalisering kostar då 27 procent av den fullständigt normaliserade strukturen men är ändå bara 16 procent av den ursprungliga tabellen.
Så långt optimering med fokus på snabb hämtning av data. För snabb lagring av data, till exempel i en mätapplikation där det är väldigt hög nivå av tillägg av nytt data, är däremot en komplex och komplett indexstruktur snarare till förtret. Det kan slöa ner applikationen. Där vill man istället använda en flatare tabellstruktur där data kan lagras snabbt och enkelt. Det här kan innebära att man behöver för-lagra mätvärdena i en egen tabell för att i ett andra skede tanka över informationen till uppslagstabellerna. Nackdelen med att arbeta med denna teknik är att realtidsdata ligger i en tabell och historikdata i en annan tabell. Det kräver då en mer komplex frågestruktur för att kunna arbeta med allt data i realtid såtillvida en fördröjning på de absolut senaste mätvärdena inte spelar större roll.
Den här artikeln har bara berört en bråkdel av allt som finns att veta om optimering av databaser. Vill du veta mer? Välkommen till någon av Informators utbildningar.
Mattias Lind | vd fandes ab | microsoft mct