Finjustering och Optimering av SQL Databaser

Finjustering och optimering
av SQL databaser
Krister Karlström
Administration och Design av Databassystem V2003
Arcada Polytechnic
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
INNEHÅLLSFÖRTECKNING
1.
Introduktion till optimering.......................................................................................................... 5
1.1.
Bakgrund .............................................................................................................................. 5
1.2.
Finjustering och optimering ................................................................................................. 5
2. Enkla sökningar och operatorer ................................................................................................... 6
2.1.
Begränsningar av kapitlet..................................................................................................... 6
2.2.
Allmän optimering av villkor och sökningar ....................................................................... 6
2.3.
Transformationer.................................................................................................................. 7
2.4.
Datum................................................................................................................................... 8
2.5.
Sammanslagning av flere konstanter ................................................................................... 9
2.6.
Datatyper .............................................................................................................................. 9
2.7.
AND..................................................................................................................................... 9
2.8.
OR...................................................................................................................................... 10
2.9.
AND plus OR..................................................................................................................... 10
2.10.
NOT ............................................................................................................................... 11
2.11.
IN ................................................................................................................................... 12
2.12.
LIKE............................................................................................................................... 13
2.13.
SIMILAR ....................................................................................................................... 14
2.14.
UNION........................................................................................................................... 14
2.15.
EXCEPT......................................................................................................................... 15
2.16.
CASE ............................................................................................................................. 15
2.17.
Syntax............................................................................................................................. 16
2.18.
Datatypkonvertering (CAST)......................................................................................... 16
3. Sortering av data ........................................................................................................................ 17
3.1.
Allmänt om sortering ......................................................................................................... 17
3.2.
Sorteringseffektivitet.......................................................................................................... 17
3.3.
Sorteringshastighet hos olika datatyper ............................................................................. 18
3.4.
ORDER BY........................................................................................................................ 19
3.5.
Att sortera eller att inte sortera........................................................................................... 19
3.6.
Sorteringsnycklar ............................................................................................................... 20
3.7.
Sortering med index........................................................................................................... 21
4. Gruppering av data..................................................................................................................... 22
4.1.
GROUP BY ....................................................................................................................... 22
4.2.
Optimal gruppering ............................................................................................................ 22
4.3.
HAVING............................................................................................................................ 24
4.4.
Alternativ till GROUP BY ................................................................................................. 24
4.5.
Gruppering och sortering ................................................................................................... 25
4.6.
Gruppering med index ....................................................................................................... 25
4.7.
COUNT.............................................................................................................................. 26
4.8.
SUM och AVG................................................................................................................... 26
4.9.
MAX och MIN ................................................................................................................... 27
5. Joins (Kopplingar, Föreningar).................................................................................................. 28
5.1.
Bakgrundsinformation om föreningar av tabeller .............................................................. 28
5.2.
Nested-Loop Joins .............................................................................................................. 29
5.3.
Sort-Merge Joins ................................................................................................................ 31
5.4.
Hash Joins .......................................................................................................................... 32
5.5.
Undvikning av Joins .......................................................................................................... 32
5.6.
Gemensamma index för Joins ............................................................................................ 33
5.7.
Sammansatta tabeller ......................................................................................................... 34
5.8.
Kopplingar mellan tre eller flera tabeller ........................................................................... 35
Sida 2 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
5.9.
Outer Joins ......................................................................................................................... 35
Underförfrågningar (subqueries)................................................................................................ 36
6.1.
Bakgrund ............................................................................................................................ 36
6.2.
Föreningar eller underförfrågningar - Joins eller Subqueries? .......................................... 37
6.3.
IN och ANY....................................................................................................................... 38
6.4.
Flattening ........................................................................................................................... 39
6.5.
DISTINCT ......................................................................................................................... 40
6.6.
EXISTS .............................................................................................................................. 40
6.7.
TOP .................................................................................................................................... 40
7. Kolumner ................................................................................................................................... 41
7.1.
Datatyper ............................................................................................................................ 41
7.2.
Fixerad eller varierbar längd? ............................................................................................ 41
7.3.
Ögonblicksinformation ...................................................................................................... 42
7.4.
Heltal.................................................................................................................................. 42
7.5.
Flyttal ................................................................................................................................. 43
7.6.
Decimaltal.......................................................................................................................... 44
7.7.
Serienummer ...................................................................................................................... 44
7.8.
Bitar.................................................................................................................................... 45
7.9.
Stora objekt (LOB)............................................................................................................. 45
7.10.
NULL............................................................................................................................. 46
7.11.
Kolumnernas ordningsföljd i en tabell........................................................................... 46
8. Tabeller ...................................................................................................................................... 47
8.1.
Bakgrund ............................................................................................................................ 47
8.2.
Lagringsarkitektur .............................................................................................................. 47
8.3.
Datahögar (heaps) .............................................................................................................. 49
8.4.
Migration............................................................................................................................ 49
8.5.
Fragmentering .................................................................................................................... 51
8.6.
Grupperade tabeller............................................................................................................ 51
8.7.
Normalisering..................................................................................................................... 51
8.8.
Vyer.................................................................................................................................... 52
9. Index........................................................................................................................................... 53
9.1.
Bakgrund ............................................................................................................................ 53
9.2.
Binära träd.......................................................................................................................... 54
9.3.
Vanliga index..................................................................................................................... 55
9.4.
Sammansatta index ............................................................................................................ 55
9.5.
Heltäckande index.............................................................................................................. 57
9.6.
Unika index........................................................................................................................ 57
9.7.
Grupperade index............................................................................................................... 57
9.8.
Bitmap index...................................................................................................................... 57
9.9.
Övriga index....................................................................................................................... 57
9.10.
Indexnycklar, trunkering och komprimering ................................................................. 57
10.
Begränsningar ........................................................................................................................ 57
10.1.
Bakgrund ........................................................................................................................ 57
10.2.
NOT NULL.................................................................................................................... 57
10.3.
CHECK .......................................................................................................................... 57
10.4.
FOREIGN KEY ............................................................................................................. 57
10.5.
PRIMARY KEY ............................................................................................................ 57
10.6.
UNIQUE ........................................................................................................................ 57
10.7.
Triggningar..................................................................................................................... 57
10.8.
Inaktivering av begränsningar........................................................................................ 57
10.9.
Onödiga begränsningar i SELECT satser ...................................................................... 57
6.
Sida 3 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
11.
Lagrade Procedurer (Stored Procedures) ............................................................................... 57
11.1.
Bakgrund ........................................................................................................................ 57
11.2.
Fördelar med Lagrade Procedurer.................................................................................. 57
12.
Dataförändringar .................................................................................................................... 57
12.1.
Bakgrund ........................................................................................................................ 57
12.2.
Loggningar..................................................................................................................... 57
12.3.
INSERT.......................................................................................................................... 57
12.4.
UPDATE........................................................................................................................ 57
12.5.
DELETE......................................................................................................................... 57
12.6.
COMMIT och ROLLBACK.......................................................................................... 57
13.
Källor ..................................................................................................................................... 57
Sida 4 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
1. Introduktion till optimering
1.1. Bakgrund
En dåligt designad databasprodukt kan orsaka fördröjningar för den enskilda användaren
och även påverka andra applikationer som körs på samma dator eller i samma nätverk.
Avsikten med denna föreläsning är att gå igenom de grundläggande detaljerna i
optimering av SQL databaser samt att visa vilka fel och brister databasprogrammerare ofta
gör och hur man kan förebygga dessa. Föreläsningen förutsätter grundläggande kunskap i
SQL och kännedom om relationsdatabasmodellen. De exempel och eventuella
rekommendationer som nämns i föreläsningen är främst SQL optimeringar och är inte
specifika för någon viss databashanterare, om ej annat framgår.
1.2. Finjustering och optimering
Det är möjligt att skilja på begreppen finjustering och optimering i databassammanhang.
Finjustering är vad som görs åt en databas (exempelvis justering av buffertstorlekar,
uppdatering av index) medan optimering är vad som oftast görs i ett program (exempelvis
justering av SQL förfrågningar, effektivering av tillgängliga resurser, omskrivning av
programkod).
Med ordet finjustering avses ofta i databassammanhang en hastighetsökning.
Hastighet kan definieras på två olika sätt:
− Svarstid: Den tid som löper sen dess att klienten skickat en förfrågan till
databashanteraren tills klienten fått svaret.
− Genomströmning: Antalet operationer och förfrågningar databashanteraren hinner
utföra under en viss tidsenhet.
I ett stort månganvändarsystem är det databashanterarens genomströmningshastighet av
förfrågningar som utgör grunden för svarstiden till de enskilda klienterna. En god
förhandsoptimering av alla SQL förfrågningar ökar genomströmningshastigheten i
databashanteraren och minskar samtidigt svarstiden till klienterna.
Oavsett vilken databashanterare som används så kan prestanda alltid höjas genom att alltid
göra de rätta sakerna och utföra de korrekta förfrågningarna.
”In our experience (confirmed by many industry experts) 80% of the performance gains on SQL Server
come from making improvements in SQL code, not from devising crafty configuration adjustments or
tweaking the operating system.”
- Kevin Kline et al., Transact-SQL Programming, O’Reilly & Associates
“Experience shows that 80 to 90 per cent of all tuning is done at the application level, not the database
level.”
- Thomas Kyte, Expert One on One: Oracle, Wrox Press
Sida 5 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Optimering av prestanda är ett brett område. Det omfattar bland annat:
− Att kunna bygga SQL satser utan att göra sånt som är vedertaget känt för att vara
resurskrävande
− Att förstå den fysiska strukturen och tillvägagångssättet i en typisk databas
− Att kunna lösa de verkliga problemen istället för de imaginära problemen.
2. Enkla sökningar och operatorer
2.1. Begränsningar av kapitlet
I detta kapitel berättas hur man kan syntaxoptimera SQL satser. Många
sökningsförfrågningar är det ingen idé att försöka optimera, eftersom det är endast vissa
sökningsförfrågningar som har sådana parametrar som går att optimera. Fastän sökningar
som består av flera tabeller med kopplingar (joins ) och underförfrågningar (subqueries) är
de mest resurskrävande, tas de inte upp i det här kapitlet. För optimering av joins och
subqueries finns det skilda kapitel, här tas endast enkla sökningar i en tabell upp.
Dessutom behandlar detta kapitel endast sökvillkor med WHERE satsen. Satserna
HAVING, IF och ON kommer senare.
Kapitlet börjar med optimeringar som utförs på en mera generell nivå och i slutet av
kapitlet finns specifika optimeringar för de vanligaste SQL kommandon som används.
På grund av portabilitetsproblem så är en del av de lösningar och tips som presenteras i
detta kapitel inte tillgängliga för alla databashanterare. Dessutom kan det finnas små
variationer mellan olika plattformer.
2.2. Allmän optimering av villkor och sökningar
I denna del av kapitlet nämns sådana saker som man bör hålla i minnet när man skriver
enkla sökningar.
De bästa sökningarna är de som påverkar det minsta antalet rader i en tabell och med
sökvillkor som har en låg kostnad. Med låg kostnad avses låg komplexitet, enkelt
utförande och snabb exekvering.
Tabellen nedan visar en typisk rangordning av sökvillkor (tabellen baserar sig på
tillverkarnas egna manualer och är vedertagen känd i databassammanhang). Desto högr e
poäng en operator eller en operand har, desto effektivare är de att använda i sökningar.
Operator Poäng
Operand
=
>
>=
<
<=
LIKE
<>
Ensam direkt given operand
Ensam kolumn
Ensam parameter
Multioperand
Exakt numerisk datatyp
Annan numerisk datatyp
Temporär datatyp
Textbaserad datatyp
NULL
10
5
5
5
5
3
0
Poäng
10
5
5
3
2
1
1
0
0
Sida 6 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Ur poängtabellen kan man läsa att den bästa och effektivaste sökningen skulle vara
någonting i den här stilen:
... WHERE smallint_column = 12345
Det här exemplet skulle enligt poängtabellen få hela 27 poäng!
Poängen har beräknats enligt följande:
−
−
−
−
5 poäng för att kolumnen (smallint_column) är ensam på vänster sida
2 poäng för kolumnens datatyp (exakt numerisk datatyp)
10 poäng för likhetsoperatorn (=)
10 poäng för den direkta operanden (12345) på höger sida
Här kommer ett annat exempel:
... WHERE char_column >= varchar_column || ‘x’
Det här sökvillkoret ger enligt samma tabell endast 13 poäng.
−
−
−
−
−
5 poäng för att kolumnen (char_column) är ensam på vänster sida
0 poäng för den textbaserade datatypen (CHAR) på vänster sida
5 poäng för operatorn större än eller lika med (>=)
3 poäng för multioperand-uttrycket (varchar_column || ’x’) på höger sida
0 poäng för den textbaserade datatypen (VARCHAR) på höger sida
Den exakta poängen för alla operatorer och operander varierar något mellan olika
tillverkare på databashanterare. Rangordningen är dock den samma för de flesta
tillverkare.
Med hjälp av dessa enkla kunskaper kan man bestämma sig för den mest optimala
ordningen i uttrycken och om det lönar sig att byta ut en del av uttrycket mot något annat
uttryck som gör samma sak. De flesta förfrågningar och sökningar kan skrivas på många
olika sätt, men de får alla olika poäng. En del databashanterare kan själva optimera en
dåligt skriven förfrågan innan den utförs, men om uttrycken är tämligen komplexa så kan
det nog hända att databashanteraren inte klarar av att omforma uttrycket effektivare.
2.3. Transformationer
En SQL förfrågan kan alltså oftast skrivas om på många olika sätt utan att resultatet
förändras. För detta finns det en formell regel som man bör följa:
IF
THEN
(A <operator> B) IS TRUE AND (B <operator> C) IS TRUE
(A <operator> C) IS TRUE AND NOT (A <operator> C) IS FALSE
Som operator duger en av de följande jämförelseoperatorerna: =, >, >=, <, <=
Dessa operatorer duger inte i transformationslagen: <> och LIKE
Transformationslagen säger alltså att man kan byta ut B till C utan att påverka resultatet.
Om en transformation innebär att en konstant flyttas kallas processen konstantöverföring.
Sida 7 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Följande två exempel ger samma resultat, men det senare exemplet ger en högre poäng
eftersom en kolumn har bytts ut till en konstant.
Uttryck #1
... WHERE column1 < column2
AND column2 = column3
AND column1 = 5
Uttryck #2
... WHERE 5 < column2
AND column2 = column3
AND column1 = 5
Uttryck ett och två är transformationer av varandra och ger således exakt samma resultat,
men uttryck två utför en effektivare och snabbare sökning, i alla fall i några
databashanterare. Som tidigare nämnts i detta kapitel, så kan de flesta databashanterare
göra enkla transformationer som denna. Däremot så försöker inte de flesta
databashanterare utföra transformationer om uttrycket innehåller en eller flera nivåer av
parenteser samt nyckelorder NOT.
Detta exempel kan vara ett långsamt uttryck:
SELECT * FROM Table1
WHERE column1 = 5
AND NOT (column3 = 7 OR column1 = column2)
Genom att tillämpa transformationsprinciperna uppstår detta uttryck:
SELECT * FROM Table1
WHERE column1 = 5
AND column3 <> 7
AND column2 <> 5
I de flesta databashanterare är det transformerade uttrycket betydligt snabbare. Det kan
med andra ord ibland löna sig att själv tillämpa transformationslagen. I vissa fall kan det
hända att konstantöverföringe n av ett flyttal inte medför någon ökning av prestandan i
uttrycket, eftersom det på grund av avrundningar ibland är möjligt att en jämförelse,
beroende på vad man jämför med, blir både större än och lika med samtidigt.
2.4. Datum
En SQL förfrågan som innehåller dagens datum kan göras snabbare genom att inte
använda databashanterarens globala variabel för dagens datum. Jämför förfrågan ett och
två nedan:
Förfrågan #1
SELECT * FROM Table1
WHERE date_column = CURRENT_DATE
AND amount * 5 > 100.00
Sida 8 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Förfrågan #2
SELECT * FROM Table1
WHERE date_column = DATE ’2003-03-26’
AND amount * 5 > 100.00
Denna optimering kräver att förfrågan skrivs om varje dag, och är således endast
användbar om förfrågan genereras dynamiskt av ett applikationsgränssnitt.
2.5. Sammanslagning av flere konstanter
Vid sammanslagning av flere konstanter i samma uttryck kan konstantöverföring uppstå.
Därför är sammanslagning av konstanter en lönsam operation. Det enkla uttrycket
... WHERE a – 3 = 5
skall alltså hellre skrivas som
... WHERE a = 8
/* a – 3 = 5
->
a = 5 + 3 */
för att uppnå en högre poäng.
2.6. Datatyper
I en 32-bitars processor fungerar aritmetiken snabbast om processorn kan jobba med 32bitars ord. Därför är datatypen INTEGER (32-bitar brett ord med tecken) den snabbaste
datatypen som kan användas av databashanterare vid jämföringar och aritmetiska uttryck.
Datatyperna SMALLINT, DECIMAL och FLOAT är alltså i de flesta databashanterare
långsammare eftersom databashanteraren inte kan arbeta med full ordlängd på 32-bitar.
2.7. AND
När SQL uttrycket endast innehåller lika med operatorer så kommer de flesta
databashanterare att utvärdera villkoren i den ordning de ges i SQL satsen.
Databashanteraren kommer med andra ord att ställa upp alla villkor i en lista som den
därefter går igenom från vänster till höger. Noteras bör att det inte finns några regler som
säger att så här måste en databashanterare göra – de gör bara på det viset (undantag är
Oracle som utvärderar villkoren från höger till vänster om den kostnadsbaserade
förfrågningsoptimeraren är aktiv). Detta beteende kan utnyttjas genom att först ge det
villkor som har minst sannolikhet att uppfyllas. Om alla villkor har samma sannolikhet
sätts det minst komplexa villkoret först. Om uttrycket som blivit satt längst till vänster är
falskt behövs således inga flera testningar av de övriga villkoren eftersom
databashanteraren vet redan i det här skedet att slutresultatet blir falskt – oavsett om de
övriga villkoren är falska eller sanna.
Exempeluttrycket
... WHERE column1 = ’A’ AND column2 = ’B’
omformas till
... WHERE column2 = ‘B’ AND column1 = ‘A’
om det är mindre sannolikt att column2 är lika med ’B’ än att column1 är lika med ’A’.
Sida 9 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
2.8. OR
När man skriver SQL satser med OR sätts det villkor som har störst sannolikhet att
uppfyllas först. Detta är den exakta motsatsen till de givna råden för AND operationen
eftersom OR operationen ger upphov till att flera tester utförs om det första villkoret är
falskt, medan AND orsakar flera tester om första villkoret är sant.
Exempeluttrycket
... WHERE column2 = ’B’ OR column1 = ’A’
omformas till
... WHERE column1 = ‘A’ OR column2 = ‘B’
om det är mera sannolikt att column1 är lika med ’A’ än att column2 är lika med ’B’.
OR operationer är även snabbare om villkoren omfattar så få kolumner som möjligt
eftersom det minskar på databashanterarens behov att slå upp i indextabeller. Därför bör
villkor i en längre SQL sats bestående av flera OR operationer som berör samma kolumn
komma efter varandra. Uttryck ett blir effektivare om det omformas till uttryck två i
nedanstående exempel:
Uttryck #1
... WHERE column1 = 1
OR column2 = 3
OR column1 = 2
Uttryck #2
... WHERE column1 = 1
OR column1 = 2
OR column2 = 3
2.9. AND plus OR
Den distributiva lagen konstaterar följande:
A AND (B OR C) = (A AND B) OR (A AND C)
Antag att en SQL förfrågan behöver utföras som består av både AND och OR villkor.
Förfrågningen är formulerad på sånt sätt att AND operationerna måste utföras före OR
operationen kan utföras:
SELECT * FROM Table1
WHERE (column1 = 1 AND column2 = ’A’)
OR (column1 = 1 AND column2 = ‘B’)
Sida 10 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
SQL förfrågningen görs mot följande tabell:
Rad #
1
2
3
column1
3
2
1
column2
A
B
C
När databashanteraren gör sökningar som slås upp i index i den ordningen de nämns i
SQL förfrågningen, kan det hända att den behöver gå igenom alla följande steg:
1.
2.
3.
4.
5.
6.
7.
Indexsökning: column1 = 1. Resultat = {rad #3}
Indexsökning: column2 = ’A’. Resultat = {rad #1}
AND operationen körs för att förena de bägge delresultaten. Resultat = { }
Indexsökning: column1 = 1. Resultat = {rad #3}
Indexsökning: column2 = ‘B’. Resultat = {rad # 2}
AND operationen körs för att förena de bägge delresultaten. Resultat = { }
OR operationen körs för att förena resultaten av bägge AND operationerna. Resultat =
{}
Genom att tillämpa den distributiva lagen baklänges erhålls följande SQL förfrågan:
SELECT * FROM Table1
WHERE column1 = 1
AND (column2 = ’A’ OR column2 = ’B’)
När databashanteraren gör indexbaserade sökningar enligt det senare uttrycket kan det
hända att endast följand e steg behövs:
1.
2.
3.
4.
5.
Indexsökning: column2 = ’A’. Resultat = {rad #1}
Indexsökning: column2 = ’B’. Resultat = {rad #2}
OR operationen körs för att förena de bägge delresultaten. Resultat = {rad #1, rad #2}
Indexsökning: column1 = 1. Resultat = {rad #3}
AND operationen körs för att förena delresultaten. Resultat = { }
Två delmoment kan alltså inbesparas genom att tillämpa den distributiva lagen baklänges.
En del av de vanligaste databashanterarna klarar av att göra det här själva, men det skadar
aldrig att färdigt optimera förfrågningarna.
Den här optimeringen gäller inte om det finns kopplingar med i förfrågningen.
2.10. NOT
Det är alltid bekvämt att skriva om NOT till något mera läsligare och lättförståeligare. Den
mänskliga hjärnan har som tendens att bli råddig om uttrycket innehåller allt för mycket
inverteringar av diverse delresultat. Ett enkelt uttryck kan förenklas genom att t.ex. byta
riktning på jämförelseoperatorn:
... WHERE NOT (column1 > 5)
transformeras enkelt till
... WHERE column1 <= 5
Sida 11 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Ett mera komplicerat uttryck kräver försiktighet vid transformeringen. DeMorgan’s
teorem kan tillämpas för att omforma uttryck med NOT.
DeMorgan’s teorem består av två regler:
NOT (A AND B) = (NOT A) OR (NOT B)
NOT (A OR B) = (NOT A) AND (NOT B)
Om DeMorgan’s teorem tillämpas på följande exempel
... WHERE NOT (column1 > 5 OR column2 = 7)
uppstår följande uttryck:
... WHERE column1 <= 5
AND column2 <> 7
Den uppmärksamme kommer nu ihåg att inte lika med operatorn bör undvikas och i de
flesta fall skulle en sådan här transformering ha en negativ effekt. På längre sikt, i vilken
som helst godtycklig mängd av spridda värden och när det är fler än två rader inblandade,
tar de förluster som uppstår av inte lika med operatorn ändå ut de vinster som erhålls a v
lika med operatorerna. På grund av detta så använder inte vissa databashanterare
indextabeller för att utföra jämförelser med inte lika med operatorn, men däremot
använder de nog indextabeller för att utföra jämförelser med mindre än och större än
operatorerna. Därför lönar sig följande transformation:
... WHERE NOT (bloodtype = ’O’)
Transformeras till:
... WHERE bloodtype < ’O’
OR bloodtype > ’O’
2.11. IN
Många tror att det inte är någon skillnad på dessa två uttryck:
Uttryck #1
... WHERE column1 = 5
OR column1 = 6
Uttryck #2
... WHERE column1 IN (5, 6)
Dessa personer har en aningen fel. Hos några databashanterare är IN snabbare än OR. Det
lönar sig alltså alltid att transformera OR till IN om möjligt. De databashanterare där IN
inte är snabbare än OR kommer ändå att transformera tillbaka uttrycket till OR, så inget
går förlorat på att använda IN.
Sida 12 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
När en IN operator har en större mängd heltal (integers) som parametrar så är det klokare
att fråga ”vad som är ute” än ”vad som är inne”. Därför bör uttrycket:
... WHERE column1 IN (1, 3, 4, 5)
transformeras till det något effektivare
... WHERE column1 BETWEEN 1 AND 5
AND column1 <> 2
2.12. LIKE
De flesta databashanterare använder index för att leta efter ett LIKE mönster om mönstret
börjar med ett konkret tecken, men kommer att undvika index om mönstret börjar med ett
jokertecken (antingen % eller _ ). Till exempel, om förfrågningen har ett sådant här
villkor:
... WHERE column1 LIKE ’C_F%’
kommer databashanteraren att försöka leta efter matchande data genom att första leta efter
alla indexnycklar som börjar med ett ‘C’. Därefter filtreras sådana nycklar bort som inte
har ett ’F’ i position tre. Det finns med andra ord inga optimeringstips för en sådan här
sökning.
I vissa specialfall kan LIKE bytas ut mot en vanlig lika med operator. Exempelvis så är
uttryck ett utbytbart mot uttryck två :
Uttryck #1
... WHERE column1 LIKE ’ABC’
Uttryck #2
... WHERE column1 = ’ABC’
Det finns en liten fallgrop här, de är nämligen inte exakt samma uttryck. I standard SQL
beaktar LIKE operatorn inledande tomrum medan lika med operatorn ignorerar inledande
extra tomrum. Dessutom kan det hända att LIKE och lika med operatorn inte använder sig
av samma teckentabell (collation).
Om en kolumn endast är två eller tre tecken bred kan det vara frestande att använda
SUBSTRING istället för LIKE. Hur som helst, så är SUBSTRING ett klassiskt exempel
på en funktion som inte är effektiv om den får en kolumn som parameter. LIKE operatorn
kommer alltid att klå multipla SUBSTRING funktioner. Uttryck ett skall med andra ord
alltid omformas såsom uttryck två lyder:
Uttryck #1
... WHERE SUBSTRING(column1 FROM 1 FOR 1) = ‘F’
OR SUBSTRING(column1 FROM 2 FOR 1) = ‘F’
OR SUBSTRING(column1 FROM 3 FOR 1) = ‘F’
Uttryck #2
... WHERE column1 LIKE ‘%F%’
Sida 13 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Inom en mycket snar framtid så kommer vissa sökningar som idag görs med LIKE att bli
onödiga, eftersom fulltextindex blir allt vanligare.
2.13. SIMILAR
Operatorn SIMILAR introducerades i SQL3 (SQL:1999). Operatorns syntax och agerande
påminner en aning om grep kommandot i Unix. I vissa fall kan användningen av
SIMILAR istället för LIKE ge en prestandaökning, SIMILAR är i alla fall inte
långsammare än LIKE och därför rekommenderas det ofta att använda SIMILAR istället
för LIKE. Exemplet nedan visar ett fall där SIMILAR avsevärt förbättrar förfrågningens
prestanda.
Uttryck #1
.. WHERE column1 = ’A’
OR column1 = ’B’
OR column1 = ’K’
Uttryck #2
... WHERE column1 SIMILAR TO ’[ABK]’
2.14. UNION
En UNION är i SQL ett sätt att förena två resultat till ett resultat. UNION operatorn tar
hand om att inga dubbla rader uppstår i resultatet även om föreningen består av två
sökningar från samma tabell. På grund av detta så är UNION en populär operator vid
förening av data från flera olika sökningar. Men är operatorn verkligen det bästa sättet att
göra detta på?
Ta följande två exempel:
Uttryck #1
SELECT * FROM Table1
WHERE column1 = 5
UNION
SELECT * FROM Table1
WHERE column2 = 5
Uttryck #2
SELECT DISTINCT * FROM Table1
WHERE column1 = 5
OR column2 = 5
Bägge uttrycken ger upphov till samma slutresultat. Uttryck två kommer i så gott som alla
fall att galant slå uttryck ett. Orsaken till detta varierar något mellan olika
databashanterare, men den ligger alltid i databashanterarnas automatiska
förfrågningsoptimerare.
Ena orsaken är att de flesta optimerare endast klarar av att optimera inom samma WHERE
sats. Operatorn UNION i uttryck ett kommer därför att separera de två WHERE satserna
från varandra, vilket leder till att databashanteraren först skannar column1 för värden som
är lika med fem och därefter skannar column2 för värden som är lika med fem.
Databashanteraren hamnar alltså att utföra dubbla skanningar! (Med skanning avses en
sökning genom hela tabellen, rad för rad.) Därför borde uttryck ett ta dubbelt så lång tid att
Sida 14 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
utföra än uttryck två, om kolumnerna inte är indexerade. Om kolumnerna är indexerade så
klarar de flesta databashanterare av att gottgöra denna prestandaförlust.
Andra orsaken, som delvis talar för UNION operatorns fördel, är att några optimerare
totalt kommer att vägra använda index om WHERE satsen innehåller OR. Detta försämrar
dock inte prestandan mera än vad som vinns på att inte använda UNION.
Rådet lyder alltså: Använd hellre OR istället för UNION, speciellt när kolumnerna inte är
indexerade.
2.15. EXCEPT
Vilket som helst uttryck i stilen A AND NOT B kan omformas till ett uttryck med
EXCEPT, och tvärt om. I likhet med UNION så kommer EXCEPT att ge upphov till
dubbla skanningar av en tabell eller flere. Dessutom är det väldigt få av de stora
databashanterarna som överhuvudtaget stöder EXCEPT, eftersom operatorn har så dålig
prestanda. Uttrycken nedan ger bägge samma resultat, uttryck ett rekommenderas.
Uttryck #1
SELECT * FROM Table1
WHERE column1 = 7
AND NOT column2 = 8
Uttryck #2
SELECT * FROM Table1
WHERE column1 = 7
EXCEPT
SELECT * FROM Table1
WHERE column2 = 8
2.16. CASE
Om en förfrågan innehåller mer än ett långsamt funktionsanrop så kan CASE användas för
att undvika att funktionsanropet händer två eller flera gånger. Uttryck två nedan visar hur
dubbla funktionsanrop kan undvikas med CASE:
Uttryck #1
... WHERE slow_function(column1) = 3
OR slow_function(column1) = 5
Uttryck #2
... WHERE 1 =
CASE slow_function(column1)
WHEN 3 THEN 1
WHEN 5 THEN 1
END
Sida 15 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
2.17. Syntax
Det är även viktigt att hålla en konsekvent stil när man skriver SQL förfrågningar. Ta
följande fyra uttryck som exempel:
SELECT column1*4 FROM Table1 WHERE COLUMN1 = COLUMN2 + 7
SELECT Column1 * 4 FROM Table1 WHERE column1=(column2 + 7)
Istället för att exekvera dessa två ovanstående uttryck bör man istället använda sig av
dessa uttryck:
SELECT column1 * 4 FROM Table1 WHERE column1 = column2 + 7
SELECT column1 * 4 FROM Table1 WHERE column1 = column2 + 7
Fastän alla dessa uttryck ger samma resultat, så kommer ibland det sista uttrycket att köra
snabbare. Detta beror på att vissa databashanterare sparar vad som kan kallas
förkompileringar av tidigare körda förfrågningar i en buffert, ibland även riktiga resultat
om datan ännu inte har ändrats sen den senaste förfrågningen. En grundförutsättning för
att denna buffert skall kunna utnyttjas är att förfrågningen ser exakt likadan ut som den
tidigare, inklusive alla mellanslag och stora och små bokstäver.
Förutom att förfrågningarna blir lättare att läsa och förstå, så kan alltså en konsekvent
skrivstil även bidra till att höja förfrågningens prestanda. Här kommer några tips för en
läsbar och entydig syntax:
− Nyckelord med stora bokstäver men kolumner med små bokstäver
− Tabellnamn har stor första bokstav
− Enkla mellanslag mellan varje ord och runtom varje aritmetisk operator
2.18. Datatypkonvertering (CAST)
Ibland hamnar man ut för att behöva konvertera en datatyp till en annan. Microsoft påstår i
sin online dokumentation över SQL Server 2000 att man aldrig skall använda CAST
funktioner om de verkligen inte är nödvändiga. Dessutom avråder de från att skriva ett
uttryck som har samma kolumn på bägge sidorna om operatorn om den ena sidan
innehåller en datakonvertering med CAST och kolumnen ingår som parameter.
Antag att vi har en kolumn innehållande priser, skapad som DECIMAL(7, 2) (7 positioner
för hela tal och två decimaler), och ett svar sökes på frågan ”Vilka priser är jämna
priser?”.
Nedan finns tre olika lösningar till detta problem:
Uttryck #1
... WHERE MOD(decimal_column, 1) = 0
Uttryck #2
... WHERE CAST(decimal_column AS CHAR(7))
LIKE ’%.00%’
Sida 16 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Uttryck #3
... WHERE decimal_column =
CAST(decimal_column AS INTEGER)
Så vilket uttryck är det bästa? Om den kunskap som tidigare presenterats i detta kapitel
tillämpas kan följande konstateras:
Uttryck ett är definitivt det sämsta alternativet. Fastän det inte finns någon CAST
operation så innehåller uttrycket en underförstådd och oundviklig datakonvertering från
DECIMAL till INTEGER, eftersom MOD operatorn enbart fungerar med INTEGER.
Uttrycket innehåller även en underförstådd division, i det här fallet division med ett.
Uttryck två är det näst bästa uttrycket. Vissa databashanterare lagrar datatypen DECIMAL
fysiskt som CHAR, vilket gör datakonverteringen vä ldigt enkel. Nackdelen är att LIKE
operatorn börjar med ett jokertecken, vilket gör den mindre effektiv.
Uttryck tre är den klara vinnaren, även om Microsoft troligtvis skulle hävda något annat.
Om man jämför alla tre uttrycken och beräknar deras poäng enligt tabellen i stycke 2.2 så
kommer uttryck tre att få den högsta prestandapoängen. Den höga poängen uppnås genom
att uttrycket använder sig av lika med operatorn och har de enklaste operanderna.
3. Sortering av data
3.1. Allmänt om sortering
Sortering av data är en av de vanligaste förekommande funktionerna som behövs i alla
sammanhang. Trots att en mängd olika algoritmer, den ena bättre än den andra, har
utvecklats är sortering än idag en resurs- och tidskrävande process. Man bör med andra
ord verkligen vara på det säkra med att sortering verkligen behövs innan man slänger in en
sorteringssats i SQL. Sortering av stora resultatmängder kräver enorma mängder
minnesutrymme och cpu kraft, vilket leder till att även andra användare av databasservern
känner av sorteringens bieffekter.
Ibland inträffar sortering i databashanteraren utan att man kanske tänker på det eller över
huvudtaget vet om det. SQL funktionerna GROUP BY, DISTINCT, CREATE [UNIQUE]
INDEX och UNION kan kalla på samma sorteringsalgoritmer som används av ORDER
BY. Mera sällsynt är de fall där databashanteraren väljer att sortera två listor när den
behöver utföra en inner join eller outer join, men enstaka fall finns. I dessa fall är
sorteringen endast en bieffekt, men det här kapitlet handlar främst om sorteringen som
händer vid ORDER BY.
3.2. Sorteringseffektivitet
Sortering har varit ett populärt ämne för forskning i flera decennier. Det är inte svårt att
hitta en lämplig sorteringsalgoritm för datan som behöver sorteras. Även
databashanterarna besitter en större repertoar över tillgängliga sorteringsalgoritmer.
Det som påverkar sorteringseffektiviteten i en databashanterare är ganska långt hur
komplext sorteringsvillkoret är och hur bred den data är som skall sorteras. Sist och
slutligen så påverkar den osorterade datans ordning ganska så lite hur snabbt sorteringen
blir klar.
Sida 17 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Det är först och främst tre saker som påverkar sorteringens hastighet. Nedan är de nämnda
i viktighetsordning.
1. Antalet rader sorteringen omfattar
2. Antalet kolumner som nämns i ORDER BY satsen
3. Databredden på de kolumner som nämns i ORDER BY satsen
Det lönar sig alltså att försöka omfatta så få rader som möjligt när man sorterar. Generellt
kan man säga att om antalet rader i sorteringen ökar tiofalt så ökar sorteringstiden
tjugofalt.
En ökning av antalet kolumner i ORDER BY satsen ökar behovet av sorteringstid mera än
vad motsvarande ökning i kolumnens databredd gör. Det lönar sig att slå ihop två
kolumner före sorteringen, om det bara är möjligt, för att undvika två kolumner i ORDER
BY satsen.
Sorteringskolumnens databredd påverkar även sorteringstiden. Om det är möjligt kan man
använda exempelvis SUBSTRING för att minska databredden på det data som skall
sorteras. Det hjälper även om några av de första tecknen är unika sinsemellan eller om det
redan finns någon form av ordning bland den osorterade datan.
Genom att lägga till flera processorer och mera minne i databasservern kan man försnabba
sorteringarna. De flesta sorteringsalgoritmer och databashanterare klarar nämligen av att
utnyttja flera processorer och kan dela upp sorteringen i flera parallella trådar. Om
dessutom centralminnet är tillräckligt stort så att all data ryms i minnet går det ännu
snabbare.
3.3. Sorteringshastighet hos olika datatyper
VARCHAR
Det är alltid den definierade databredd som bestämmer hur lång tid det tar att sortera. En
kolumn med varierbar databredd, såsom VARCHAR, har alltid en definierad bredd och en
aktuell bredd. Om en VARCHAR(30) kolumn innehåller strängen ’ABC’ så är dess
definierade bredd 30 tecken medan den aktuella bredden är tre tecken. Vid sortering så är
det den definierade bredden på 30 tecken som bestämmer sorteringstiden. Detta beror
troligen på att de flesta databashanterare redan på förhand allokerar det minnesutrymme
sorteringen behöver. Minnet allokeras enligt det största möjliga behovet, vilket i det här
fallet kan vara 30 tecken per rad.
SMALLINT
I Windows miljö, där en INTEGER är 32-bitar och en SMALLINT är 16-bitar, kunde man
kanske tro att sortering av 16-bitars ord skulle vara snabbare än sortering av 32-bitars ord.
Eftersom processorns ordstorlek i de flesta fall är 32-bitar, och databashanteraren kan
utnyttja fulla bredden, är det vanligen snabbare att jämföra integers.
Sida 18 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
CHAR
Datatypen CHAR är som bekant 8-bitar. Om man skapar en kolumn som CHAR(4) så blir
den totala bredden 4 x 8 = 32-bitar. INTEGER är också 32-bitar. Går det då lika snabbt att
sortera en CHAR(4) kolumn som en INTEGER kolumn? Tyvärr inte, åtminstone inte i de
flesta fall och hos de flesta databashanterare. Orsaken torde ligga i att en teckensträng inte
kan jämföras 4 byte i gången. Däremot går det bra att jämföra 4 byte i gången när man
jämför med INTEGER.
INTEGER
Den stora vinnaren är alltså datatypen INTEGER. Den sorteras alltid snabbast och passar
perfekt in i 32-bitars arkitektur. När dessutom databashanteraren har sina knep för att
utnyttja full databredd så är datatypen oslagbar i prestanda. Däremot tar datatypen fysiskt
upp mera plats än 16-bitars SMALLINT och 8-bitars CHAR och vill man optimera
databasens fysiska storlek skall man givetvis alltid välja så liten datatyp som möjligt.
3.4. ORDER BY
Syntaxen för en ORDER BY sats är följande:
SELECT <column list>
FROM <Table list>
ORDER BY <column expression> [ASC | DESC] [,...]
I SQL-92 var ORDER BY lite mera begränsad än vad den är idag i SQL:1999. I SQL-92
var man tvungen att ha ett kolumnnamn som dessutom måste ingå i SELECT listan. I
SQL:1999 är det tillåtet att ha ett helt uttryck som operand till ORDER BY. För att
exempelvis sortera nummer i fallande ordning finns det idag två olika alternativ att göra
detta på:
SQL-92
SELECT numeric_column, numeric_column * -1 AS num
FROM Table1
ORDER BY num
SQL:1999
SELECT numeric_column
FROM Table1
ORDER BY numeric_column * -1
Resultatet av ORDER BY numeric_column * -1 kan särskilja sig en aning från
resultatet av ORDER BY numeric_column DESC. Skillnaden beror på om NULL
värden existerar och hur databashanteraren behandlar dessa.
3.5. Att sortera eller att inte sortera
”Do not use ORDER BY if the query has a DISTINCT or GROUP BY on the same set of terms, because
they have the side effect of ordering rows.”
- Kevin Kline et al., Transact-SQL Programming, O’Reilly & Associates
Det är ingen idé att sortera data om den redan är sorterad. Det finns en mängd situationer
där så är fallet, där sådana operationer används vilka har som bieffekt att de sorterar data.
Sida 19 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Åtminstone följande satser returnerar data i sorterad ordning utan att innehålla en ORDER
BY sats:
− SELECT column1 FROM Table1
Returnerar sorterad data om Table1 är grupperad (clustered) och column1 är
grupperingsnyckeln (cluster key) eller om tabellen annars råkar vara färdigt sorterad.
− SELECT column1 FROM Table1 WHERE column1 > -32768
Returnerar sorterad data om column1 är indexerad i stigande ordning och
databashanteraren använder index.
− SELECT DISTINCT column1 FROM Table1
Returnerar sorterad data om column1 inte är UNIQUE.
Om ORDER BY fogas till någon utav dessa SQL förfrågningar blir resultatet endast en
försämring i prestanda. Databashanterarna tar heller inte automatiskt bort onödiga
ORDER BY satser.
3.6. Sorteringsnycklar
Utelämnande av ORDER BY satsen och påtvingande av sortering via teckentabeller och
ordlistor är två olika metoder som kan användas för att snabba upp en sortering. Bägge
alternativen är sådana lösningar som kanske inte stöds av alla databashanterare.
Det finns tre saker man kan göra som försnabbar sortering utan att kräva speciellt stöd från
databashanteraren: sorteringsnycklar, påtvingande av index och försortering.
Om man behöver sortera data enligt exotiska teckenuppsättningar, eller annars bara
behöver en djup sortering, sjunker sorteringshastigheten drastiskt. Bägge dessa problem är
lösbara – addera en kolumn som innehåller sorteringsnycklar till tabellen.
En sorteringsnyckel är en teckensträng bestående av en sekvens av en-bytes nummer.
Sorteringsnyckeln representerar den relativa ordningsföljden mellan tecknen.
Det finns en färdig funktion i MS Windows NT API för att konvertera vilken teckensträng
som helst till en användbar sorteringsnyckel. Funktionen heter LCMapString. Funktionens
indata kan vara vilka bokstäver som helst i någon av de teckenuppsättningar som stöds av
Windows NT och funktionens utdata är en sorteringsnyckel automatiskt viktad enligt de
lokaliserade inställningar som gjorts i systemet.
Sida 20 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Nedan finns ett skript för att skapa sorteringsnycklar för varje rad i en tabell.
locale_id = an ID representing country/collation/etc.
...
DECLARE Cursor1 CURSOR FOR
SELECT character_column FROM Table1;
OPEN Cursor1;
for(;;) {
FETCH Cursor1 INTO :character_string;
if(NO_DATA_FOUND)
break;
LCMapString(locale_id, character_string, sort_key);
UPDATE Table1
SET sort_key_column = :sort_key
WHERE CURRENT OF Cursor1;
}
...
För att skriptet skall fungera bör en kolumn sort_key_column ha skapats på förhand
med förhandsinställd binär sortering. Eftersom kolumnen använder sig utav binär sortering
går det alltså lika snabbt att sortera teckensträngarna som med vilken binär sortering som
helst, vilket är mycket snabbare än någon ordlistbaserad sortering. När tabellen innehåller
en kolumn med sorteringsnycklar kan teckensträngarna hämtas i sorterad ordning med
följande förfrågan:
SELECT * FROM Table1
ORDER BY sort_key_column
3.7. Sortering med index
Sorteringen kan snabbas upp om man kan uppmuntra databashanteraren att utnyttja
eventuella index. Nyckelordet WHERE orsakar i de flesta databashanterare ett försök att
hitta posten i ett index. Uttryck två kan vara snabbare än uttryck ett, om coulumn1 är
indexerad:
Uttryck #1
SELECT * FROM Table1
ORDER BY column1
Uttryck #2
SELECT * FROM Table1
WHERE column1 >= ’’
ORDER BY column1
Sida 21 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
I kapitel 3.5 konstaterades att en sökning med WHERE returnerar resultatet i sorterad
ordning om kolumnen är indexerad. Detta kan dock inte tas för en självklarhet och
ORDER BY kan därför inte lämnas bort. Hur som helst så blir hela sorteringen snabbare
eftersom ORDER BY utförs snabbare om raderna redan är något så när sorterade. Ett par
saker är värt att noteras om detta trick:
− ORDER BY satsen kan inte utelämnas eftersom det inte finns några garantier för att
databashanterare faktiskt använder indexsökningar. WHERE satsen skall närmast ses
som en uppmuntran till användning av index.
− WHERE satsen orsakar att alla NULL värden filtreras bort vilket kanske inte är
ändamålet alltid.
4. Gruppering av data
4.1. GROUP BY
Eftersom GROUP BY kan orsaka extra sorteringar så stämmer en stor del av de saker som
nämndes for ORDER BY i föregående kapitel även för GROUP BY. Det lönar sig med
andra ord att så långt som möjligt undvika GROUP BY och om de används bör de vara
uppbyggda så enkla som möjligt.
GROUP BY kan direkt användas i SQL förfrågningar:
SELECT column1 FROM Table1
GROUP BY column1
Gruppering inträffar ibland även underförstått utan att själva nyckelordet GROUP BY
finns med i klartext. För exempel, HAVING och dataihopsamlingsfunktioner såsom AVG,
COUNT, MAX, MIN, SUM och andra ger upphov till gruppering av data:
SELECT COUNT(*) FROM Table1
HAVING COUNT(*) = 5
Vissa databashanterare stöder extra parametrar och uttryck till GROUP BY och en del kan
ha extra inbyggda funktioner för dataanalys, till exempel STDEV för standardavvikelse.
4.2. Optimal gruppering
Precis som för ORDER BY så blir GROUP BY långsammare för varje extra kolumn som
läggs till i listan över grupperingskolumner. Grupperingslistan kan hållas så kort som
möjligt genom att se till att inga överflödiga kolumner sätts till. Följande exempel visar ett
fall där en överflödig kolumn finns i grupperingslistan:
SELECT secondary_key_column, primary_key_column, COUNT(*)
FROM Table1
GROUP BY secondary_key_column, primary_key_column
Sida 22 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Eftersom kolumnen innehållande primärnyckeln är unik och inte kan innehålla NULL
värden, är secondary_key_column i grupperingslistan överflödig och kunde
uteslutas. Problemet är att om secondary_key_column tas bort ut grupperingslistan
genereras ett felmeddelande. Alla databashanterare (förutom MySQL och Sybase)
kommer att vägra ha secondary_key_column i selectlistan om den inte är med i
grupperingslistan. En lösning, som är snabbare, kunde vara följande:
SELECT MIN(secondary_key_column), primary_key_column,
COUNT(*)
FROM Table1
GROUP BY primary_key_column
GROUP BY har som tendens att resultera i ett färre antal rader och JOIN tenderar att
utöka antalet rader i ett resultat. Den korrekta ordningen vore alltså att första utföra alla
grupperingsfunktioner (GROUP BY) innan föreningsfunktionerna (JOIN) utförs.
Databashanteraren är tvungen att första utvärdera resultaten från FROM och WHERE
innan grupperingsfunktioner kan inledas, vilket gör det svårt att utnyttja detta tänkande.
I vissa fall är det möjligt att påverka ordningsföljden och således optimera förfrågningen.
Om en databashanterare med stöd för mängdfunktioner (UNION, EXCEPT,
INTERSECT) används så kan en SQL förfrågan som består av både GROUP BY och
JOIN skrivas om till två förfrågningar som enbart utför GROUP BY, förenade av
INTERSECT.
Uttryck #1
SELECT SUM(Table1.column2), SUM(Table2.column2)
FROM Table1
INNER JOIN Table2
ON Table1.column1 = Table2.column1
GROUP BY Table1.column1
Uttryck #2
SELECT column1, SUM(column2), 0
FROM Table1
GROUP BY column1
INTERSECT
SELECT column1, 0, SUM(column2)
FROM Table2
GROUP BY column1
Uttryck två utförs snabbare på grund av att ingen JOIN behöver utföras. Dessutom sparar
operationen den mängd arbetsminne som behövs för att utföra förfrågningen. Här finns
dock ett stort portabilitetsproblem. Många utav de stora tillverkarna för databashanterare
stöder inte INTERSECT. Dessa är åtminstone Informix, Ingres, InterBase, Microsoft,
MySQL och Sybase. För dessa databashanterare fungerar alltså inte den optimering som
här presenterades.
När man grupperar resultat som uppstått via en JOIN är det effektivast om
grupperingskolumnen är från samma tabell som kolumnen på vilken en eventuell
mängdfunktion tillämpas på. Det här tipset nämns i en del tillverkares manualer.
Sida 23 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Prestandan kan som bekant ökas genom att undvika JOIN. Eftersom GROUP BY inträffar
vid användning av mängdfunktioner så kan uttryck ett skrivas om till uttryck två, om
Table1.column1 är unik, för att undvika en JOIN.
Uttryck #1
SELECT COUNT(*) FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
Uttryck #2
SELECT COUNT(*) FROM Table2
WHERE Table2.column1
IN (SELECT Table1.column1 FROM Table1)
4.3. HAVING
De flesta databashanterare sammanfogar inte WHERE och HAVING satser. Följande två
uttryck ger exakt samma resultat, men uttryck två är snabbare.
Uttryck #1
SELECT column1 FROM Table1
WHERE column2 = 5
GROUP BY column1
HAVING column1 > 6
Uttryck #2
SELECT column1 FROM Table1
WHERE column2 = 5
AND column1 > 6
GROUP BY column1
Endast sådana fall där jämförelsen av column1 är svår och tidskrävande kan dra nytta av
att utföra jämföringen först i HAVING skedet.
4.4. Alternativ till GROUP BY
Om man skriver en förfrågan som inte involverar mängdfunktioner kan man ofta använda
DISTINCT som ett alternativ till GROUP BY. DISTINCT har tre fördelar framom
GROUP BY:
1. Operatorn är mycket enkel
2. Operatorn kan användas i mera komplicerade uttryck
3. Databashanterare kör ofta DISTINCT snabbare än GROUP BY
Det är effektivare att använda uttryck två istället för uttryck ett:
Uttryck #1
SELECT column1 FROM Table1
GROUP BY column1
Uttryck #2
SELECT DISTINCT column1 FROM Table1
Sida 24 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
4.5. Gruppering och sortering
När en GROUP BY utförs så kommer databashanteraren att behöva leta efter dubbletter av
data. Därför utförs nästan alltid en sortering före grupperingen kan utföras. Tabellen nedan
visar två listor, en osorterad och en sorterad.
Osorterad lista
Belgrad
Sofia
Budapest
Sorterad lista
Belgrad
Budapest
Sofia
Antag att en gruppering enligt ”Belgrad” skall utföras i den osorterade tabellen. Hur vet
databashanteraren om det finns dubbletter av ”Belgrad”? Jo, databashanteraren är tvungen
att gå igenom hela tabellen och jämför ”Belgrad” med både ”Sofia” och ”Budapest” innan
den vet om det finns dubbletter. Den kan inte vara säker på att inga dubbletter finns förrän
den har gått igenom hela tabellen, eftersom datan ligger i slumpmässig ordning.
Om man däremot utför en gruppering enligt ”Belgrad” i den sorterade tabellen räcker det
med att databashanteraren jämför ”Belgrad” med nästa rad, eftersom datan ligger i
stigande alfabetisk ordning. Så fort som databashanteraren jämfört [n] med [n + 1] är den
klar. Detta betyder alltså att en gruppering går snabbt om tabellen är sorterad.
Ett annat effektivt sätt att utföra grupperingar är att använda en hash-lista för att snabbt
kunna kontrollera om det finns likadana poster. Informix använder sig (enligt uppgifter)
utav hash tekniken.
Eftersom en GROUP BY föregås av en ORDER BY kan man alltså effektivera
grupperingen genom att ge kolumnerna i samma ordningsföljd både i ORDER BY satsen
och i GROUP BY satsen. Skriv inte en SQL förfrågan på det här viset:
SELECT * FROM Table1
GROUP BY column1, column2
ORDER BY column1
Skriv den istället på det här viset:
SELECT * FROM Table1
GROUP BY column1, column2
ORDER BY column1, column2
Den här optimeringen gäller för databashanterare som inte använder sig utav hash listor.
Har man en sådan hanterare skall man använda sig utav den första varianten, alternativ två
blir långsammare i ett hash-baserat system.
4.6. Gruppering med index
De flesta databashanterare använder index vid datagrupperingar. Som redan tidigare
nämndes, så underlättas en gruppering om tabellen är sorterad. Att kunna utnyttja index är
egentligen samma sak som att ha tabellen sorterad, inte riktigt samma sak, men i alla fall
är beteendet i det här sammanhanget väldigt lika. Här är det även värt att tänka på att
GROUP BY har en lägre prioritet än JOIN och WHERE satser. GROUP BY kommer med
andra ord att utföras efter en eventuell JOIN eller WHERE sats.
Sida 25 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Följande exempel kan inte utnyttja ett index för att snabba upp grupperingen:
SELECT column1 FROM Table1
WHERE column2 = 55
GROUP BY column1
Orsaken till att index inte kan användas av GROUP BY torde vara ganska så klar.
Databashanteraren utför WHERE satsen först och den jämförelsen görs via index över
column2. Efter att detta är gjort fortsätter den med GROUP BY satsen, me n nu är det
ingen idé längre att utnyttja index över column1, eftersom indexet är ett index över hela
tabellen och inte över den data som WHERE satsen filtrerat fram.
På grund av detta fenomen är index endast lämpliga för små och enkla grupperingar.
Lyckligtvis är de flesta grupperingar som görs så pass enkla att ett index kan utnyttjas.
Grupperingar där GROUP BY står ensamt och grupperingskolumnerna är i samma
ordningsföljd som de indexerade kolumnerna i selectlistan kommer att utnyttja ett index.
Följande exempel utför grupperingen snabbare, om column1 har ett index:
SELECT column1 FROM Table1
GROUP BY coumn1
4.7. COUNT
Funktionen COUNT kommer att utnyttja ett index om endast en kolumn (eller *) finns i
selectlistan och om inga flera satser följer FROM satsen. Följande förfrågan utförs
snabbare av vissa databashanterare om tabellen har ett index:
SELECT COUNT(*) FROM Table1
COUNT har dessutom väldigt ofta databasspecifika specialoptimeringar, vilket kan göra
det svårt att försöka tillföra ytterligare optimering.
4.8. SUM och AVG
Index
Även funktionerna SUM och AVG kommer att utnyttja ett index om endast en kolumn
finns med i selectlistan och den kolumnen är indexerad. Tyvärr utförs inte funktionerna
snabbare med hjälp av indexet – tvärtom, det tar ofta längre tid att utföra funktionerna om
de utförs på indexerade kolumner. På längre sikt kan man ändå anse att de förluster som
uppstår är väldigt små i förhållande till de vinster som uppnås via index hos COUNT,
MAX och MIN.
Följande exempel utförs ofta långsammare om column1 är indexerad:
SELECT SUM(column1) FROM Table1
WHERE coumn1 > 5
Sida 26 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Precision
Precision kan även bli ett dilemma om man vill summera en större mängd flyttal av
datatypen FLOAT. Speciellt om följande två fall förekommer kan det bli problem med
precisionen: (a) talens exponenter varierar mycket sinsemellan och (b) när många
subtraktionen inträffar på grund av negativa tal. Förståeliga förluster i precision kan även
inträffa när man lagrar tal som avses vara exakta som FLOAT. Ta till exempel det
”exakta” talet 0.1 och lagra det i en FLOAT kolumn. Addition tio gånger av den kolumnen
behöver nödvändigtvis inte ge slutresultatet 1.0, på grund av precisionsförluster.
Högre flyttalsprecision kan uppnås genom att konvertera talen till en datatyp med högre
precision innan summeringsfunktioner tillämpas, exempelvis till datatypen DECIMAL
eller DOUBLE. Märk väl att det är viktigt att konvertera varje enskilt tal före additionen,
inte själva additionsresultatet. Uttryck ett ger alltså ingen bättre precision, men uttryck två
kan förbättra precisionen hos slutresultatet:
Uttryck #1
SELECT CAST(SUM(column1) AS DECIMAL(10)) FROM Table1
Uttryck #2
SELECT SUM(CAST(column1 AS DECIMAL(10))) FROM Table1
Precisionen blir även en aning bättre om man använder SUM(x + y) istället för SUM(x) +
SUM(y) eftersom det totala antalet additioner blir färre. Däremot blir precisionen bättre
om man använder SUM(x) – SUM(y) istället för SUM(x - y) därför att det totala antalet
subtraktioner blir färre.
Med heltal kan precisionsförluster aldrig uppstå. Däremot kan man i långsökta fall få
problem med begränsningar och spill (overflow) kan uppstå. Om man försöker addera två
celler som bägge innehåller talet 2 miljarder så uppstår spill eftersom summan 4 miljarder
kan inte representeras med 32-bitar. Problemet kan lösas med datatypkonvertering. För
system som stöder datatypen BIGINT kan alternativ ett användas. Vill man hålla sig till
standard SQL skall alternativ två användas.
Alternativ #1
SELECT SUM(CAST(column1 AS BIGINT)) FROM Table1
Alternativ #2
SELECT SUM(CAST(column1 AS DECIMAL(10))) FROM Table1
4.9. MAX och MIN
Funktionerna MAX och MIN kommer att utnyttja ett index om funktionen är ensam i
selectlistan och inga andra satser följer efter FROM satsen. Om ett index existerar blir
dessa två funktioner väldigt enkla och snabba att utföra. Funktionen MIN kan direkt läsa
det minimala värdet från det första indexvärdet och funktionen MAX får direkt det
maximala värdet genom att avläsa det sista indexet (eller tvärtom i ett fallande index, som
i IBM).
Databashanterarna löser följande problem väldigt lätt om tabellen har ett index:
SELECT MIN(column1) FROM Table1
Sida 27 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Men följande exempel är en betydligt svårare nöt att knäcka och tar betydligt längre tid att
utföra:
SELECT MIN(column1), MAX(column1) FROM Table1
Villkoret för indexanvändning var ju att funktionen skulle vara ensam i selectlistan, därför
tar detta exempel väsentligt mera tid på sig att utföras. I sådana här situationer är det bättre
att skriva förfrågningen som två skilda SELECT satser, eller förena dem med en UNION.
Följande exempel rekommenderas:
SELECT MIN(column1) FROM Table1
SELECT MAX(column1) FROM Table1
5. Joins (Kopplingar, Föreningar)
5.1. Bakgrundsinformation om föreningar av tabeller
En gång i tiden fanns det en enkel regel för joins – indexera alla kolumner och föreningar
av tabeller kommer att gå snabbt. Medan databashanterarna har utvecklats och blivit mera
sofistikerade har flera olika strategier för olika typer av föreningar tagits fram. De flesta
databashanterare innehar en ganska stor repertoar utav dessa och optimering av föreningar
är idag mera raffinerande än förut.
Det första man bör hålla i färskt minne när man planerar förfrågningar som förenar flera
tabeller är att man inte vill utföra föreningen själv. Vad man däremot vill göra är att ge
databashanteraren de bästa förutsättningarna för att själv kunna välja den mest lämpliga
och effektivaste föreningsplanen, i slutändan kommer detta att påverka effektiviteten en
hel del.
I den förenklade modellen av en förening av två tabeller blir resultattabellens storlek
produkten av de tabeller som förenades. Den förenar alltså alla rader i ena tabellen med
alla rader i den andra tabellen. Om Tabell1 innehåller värdena {A, B} och Tabell2
innehåller {C, D} så blir produkten { (A, C) (A, D) (B, C) (B, D) }. Realisten märker att
en del av dessa rader troligtvis kommer att filtreras bort av ett villkor, som dessutom körs
först efter att tabellerna multiplicerats. Det väsentliga i detta exempel är att visa på den
enorma mängden med tillfälligt minne som behövs för att lagra den temporära tabellen.
Det behövs alltså någon bättre algoritm för att utföra en förening i praktiken.
Valet av vilken algoritm och plan som väljs beror på om tabellen har index, tabellens
storlek samt på indexets selektivitet. Selektivitet är ett mått på olikheten mellan unika
värden i ett index.
Sida 28 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
5.2. Nested-Loop Joins
”Nested-Loop joins” kan översättas till ”förening med inbäddade upprepningar”, eller nåt i
den stilen. Principen är två eller flera upprepningar inuti varandra där varje upprepning går
igenom en specifik tabell. Alla algoritmer enligt ”Nested-Loop joins” principen baserar sig
på någon variant av pseudokoden nedan:
for (each row in Table1) {
for (each row in Table2) {
if (Table1 join column matches Table2 join column) pass
else fail
}
}
− Ordet “matches” betyder vanligtvis “lika med” eftersom majoriteten av alla föreningar
är “lika- med-föreningar”, alltså sådana som använder lika med operatorn. Ett känt
namn på dessa i det engelska språket är ”equijoins”.
− Ordet ”pass” syftar på att raderna från bägge tabellerna skall tillfogas den temporära
resultattabellen. Vanligtvis så sparar databashanterarna endast det ID som förknippas
med raderna.
Redan den här algoritmen är en stor förbättring jämfört med det grundscenario som
beskrevs i introduktionen till detta kapitel. Nu behöver den temporära tabellen inte vara
fullt så stor som produkten av bägge tabellerna. Men fortfarande så behövs ett gigantiskt
antal jämförelser.
Jämförelser är dock snabba operationer, speciellt om bägge talen som skall jämföras ligger
i databashanterarens buffertminne. Genom att utöka den algoritm som nyss presenterades
så att den tar sig an en sida i gången från en tabell kan prestandan höjas betydligt.
Sidstorleken bestäms i databasserverns konfiguration, men typiska värden är 4 KB, 8 KB
eller 16 KB. Genom att öka denna buffert så kan större sidor rymmas i buffertminnet och
algoritmen körs snabbare.
for (each page in Table1) {
for (each page in Table2) {
for (each row in Table1-page) {
for (each row in Table2-page) {
if (join column matches) pass
else fail
}
}
}
}
Denna algoritm utnyttjar buffertminnet genom att dela upp tabellerna i sidor och behandla
en sida från varje tabell i gången. Om dessutom de kolumner som jämförs har index så
behövs ingen skanning av hela sidan, det räcker med att slå upp värdet ur indexet. Den
innersta upprepningen bör bestå av den enklare, alternativt den mindre, tabellen eller
åtminstone ha ett index för att algoritmen skall bli ännu effektivare.
Sida 29 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Oftast finns det ett villkor med i spelet när man förenar tabeller med varandra, förutom det
villkor som definierar föreningen. Den tabell som har ett filtreringsvillkor bör placeras
som den yttre tabellen i upprepningen så att det totala antalet upprepningar av den inre
upprepningen minskas, annars upprepas filtreringsvillkoret om och om igen på tok för
många gånger.
Databashanteraren väljer vilken tabell som blir den yttre och vilken som blir den inre på
basen av följande punkter:
− Den minsta tabellen skall vara i den inre upprepningen, speciellt om den är mycket
liten.
− Tabellen med det bästa indexet skall vara i den inre upprepningen.
− Den tabell som har ett filtreringsvillkor skall vara i den yttre upprepningen.
De här punkterna bör man tänka på när man skriver förfrågningar som förenar tabeller.
Motverka inte databashanterarens vilja genom att placera ett filtreringsvillkor på fel tabell!
Om tabell ett är mindre och har ett bättre index, placera då ett eventuellt filtreringsvillkor
på tabell två istället – tabell två passar ju ändå bättre i den yttre upprepningen.
Så här långt har allt handlat om enkla föreningar av typen Table1.column1 =
Table2.column1. Förståss kan det bli lite mera komplicerat än så här. Det kan till
exempel finns två föreningsvillkor som i följande exempel:
SELECT * FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
AND Table1.column2 = Table2.column2
I det här fallet räcker det inte med att försäkra att column1 och column2 i den inre
upprepningen är indexerade. Om ett sammansatt index skapas för column1 och
column2 i den inre upprepningen, istället för två individuella index, sker den totala
föreningen betydligt snabbare! Det är dessutom mycket viktigt att bägge kolumnerna som
jämförs i den inre upprepningen har exakt samma datatyp och exakt samma storlek –
annars kan det hända att databashanteraren inte alls utnyttjar indexet.
Sida 30 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
5.3. Sort-Merge Joins
En ”Sort-Merge Join” är en aning mera komplicerad än en ”Nested-Loop Join”, men den
går fortfarande att beskriva med ett par rader pseudokod:
sort (Table1)
sort (Table2)
get first row (Table1)
get first row (Table2)
for (;;until no more rows in tables) {
if(join-column in Table1 < join-column in Table2)
get next row (Table1)
elseif (join-column in Table1 > join-column in Table2)
get next row (Table2)
elseif (join-column in Table1 = join-column in Table2) {
pass
get next row (Table1)
get next row (Table2)
}
}
Algoritmen börjar med att sortera de bägge tabellerna som skall förenas. Skedet kallas
”sorteringsskedet”, därifrån kommer den första halvan i namnet ”Sort-Merge”. Andra
halvan kommer givetvis från nästa skede. I föreningsskedet (”Merge” skedet) av en “SortMerge Join” går databashanteraren alltid framåt i bägge tabellerna. Den behöver aldrig gå
igenom en viss rad i en tabell mer än en gång – en stor förbättring jämfört med ”NestedLoop Join”.
Nackdelen är den sortering som krävs innan den fina algoritmen kan uträtta sitt arbete.
Effektiva sorteringsalgoritmer borde ändå vara betydligt snabbare än jämförelser av allt
mot allt (som är fallet i ”Nested-Loop Joins”). Det är bevisbart att ”Sort-Merge Join” är
betydligt snabbare än ”Nested- Loop Join” om bägge tabellerna är väldigt stora.
Trots alla fördelar så väljer databashanterarna väldigt ofta att hellre använda ”NestedLoop Joins” än ”Sort-Merge Joins” därför att de kräver mindre mängd minne, är flexiblare
och kräver inga extra tunga databehandlingar i startögonblicket. Följande exempel visar en
idealisk situation för att utföra en ”Sort-Merge Join”:
SELECT * FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
Det som gör denna förening ideal för ”Sort-Merge” är lika med operatorn och avsaknaden
av filtreringsvillkor. En typisk SQL förfrågan för att generera en rapport. Om dessutom
Table1 och Table2 är sorterade på förhand enligt samma nyckel så är det den perfekta
situationen för en ”Sort-Merge”.
Sida 31 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Användningen av ”Sort-Merge Joins” kan innebära lite extra jobb för
databasadministratorn. Hos vissa databashanterare måste nämligen ”Sort-Merge Joins”
aktiveras manuellt. Dessutom kan det vara en god idé att se över storleken på serverns
centralminne om man tänkt använda sig utav den här föreningstekniken.
5.4. Hash Joins
En ”Hash-Join” är ännu en algoritm för att skapa en förenad tabell. Processen att förena
Table1 och Table2 går enligt följande modell:
− Beräkna ett hash värde för varje rad i Table1. Spara hash värdet i en tillfällig tabell i
minnet.
− Beräkna ett hash värde för varje rad i Table2. Kontrollera om hash värdet redan
finns i den tillfälliga tabellen i minnet. Om det finns, så finns det en koppling. Om det
inte finns, så finns det ingen koppling.
En ”Hash-Join” är med andra ord en variant av en ”Nested-Loop Join” vars inre tabells
rader är uppslagna via hash värden istället för via index. De flesta databashanterare
upprätthåller inga statiska tabeller med hash värden, så en temporär tabell med hash
värden för Table1 behöver skapas innan föreningen kan inledas. Hash tabellen kan
frigöras när den förenade tabellen är klar.
Följande fyra krav måste alla vara uppfyllda för att en ”Hash-Join” skall fungera vettigt:
1. Det finns tillräckligt med minne reserverat för den temporära tabellen innehållande
alla hash värden. Inställningen är oftast konfigurerbar.
2. Föreningen är en ”equijoin” (den använder lika med operatorn).
3. Värdena i den inre tabellens föreningskolumn är unika och producerar unika hash
värden.
4. Den inre tabellen genomsöks många gånger eftersom den yttre tabellen är betydligt
större och innehåller många rader som inte filtrerats ut utav något filtreringsvillkor.
Krav nummer ett kan kompenseras för genom att dela upp den yttre tabellen i flera bitar
och beräkna hash värden för varje bit i gången. Därefter utförs föreningensmomentet och
när det är klart beräknas nya hash värden för nästa bit av den yttre tabellen o.s.v. Detta kan
ge upphov till att den inre tabellen skannas många gånger, men det är i alla fall bättre än
att ha en gigantisk hash tabell för den yttre tabellen, som dessutom kanske mappas ner på
hårddisken om den inte ryms i centralminnet.
5.5. Undvikning av Joins
En förening av två tabeller kan ibland undvikas genom att transformera en SQL förfrågan
till en enklare, utan förening, men ändå likvärdig. Följande exempel utnyttjar en av
grundprinciperna i SQL optimering, spridning av konstanta värden. Uttryck ett kräver en
förening av Table1 och Table2 medan uttryck två inte behöver någon förening. Ett
krav är dock att bägge kolumnerna är indexerade.
Uttryck #1
SELECT * FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
AND Table1.column1 = 55
Sida 32 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Uttryck #2
SELECT * FROM Table1, Table2
WHERE Table1.column1 = 55
AND Table2.column1 = 55
En sådan här situation uppstår alltid om lika med operatorn utgör filtreringsvillkoret och
samma kolumn även ingår i föreningsvillkoret. Det är trots allt en väldigt liten del av alla
föreningar som kan omformas till icke- föreningar. För att uttryck två över huvudtaget
skall vara snabbare än uttryck ett krävs det att bägge tabellerna har index över column1.
5.6. Gemensamma index för Joins
En del databashanterare har stöd för något som kallas för ”Join Indexes”, bland annat
Microsoft och Oracle stöder dem. Principen är att ha flera tabellers index i samma fysiska
index.
Index 1
A
Tabell 1
Rad 1.7
Index 2
B
Tabell 2
Rad 14.53
B
Rad 6.3
C
Rad 16.02
C
Rad 8.8
F
Rad 19.08
Bilderna ovan visar två vanliga index för Tabell ett och Tabell två.
Bilden nedan visar ett gemensamt index (”Join Index”) för både Tabell ett och Tabell två:
Index
Tabell 1
A
Rad 1.7
B
Rad 6.3
B
Rad 8.8
Tabell 2
C
Rad 14.53
C
Rad 16.02
F
Rad 19.08
Antag att följande SQL förfrågan körs, med tillgång till ett gemensamt index för Tabell ett
och två:
SELECT * FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
Sida 33 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Med tillgång till ett gemensamt index är uppgiften mer eller mindre trivial.
Databashanteraren behöver endast skanna igenom den gemensamma indextabellen och för
varje värde som pekar på en rad i tabell ett, kontrollera om nästa indexvärde är exakt
likadant men pekar på en rad i tabell två. Om så är fallet har den hittat en koppling. Det
här är faktiskt en ”Sort-Merge Join”, där redan både sorteringen och föreningen har utförts
på förhand!
Gemensamma index kan vara mycket användbara och kan underlätta föreningar av
tabeller avsevärt, men det finns alltid en risk för att det blir råddigt när man börjar blanda
ihop data från flera tabeller i samma fil. Om dessutom man behöver utföra enskilda
sökningar i kolumnerna så går det långsammare på grund av all ”skräpdata” från den andra
tabellen som ligger i indexet.
En tumregel för gemensamma index är:
Om mer än hälften av alla förfrågningar på två tabeller innebär en förening av dessa, och
föreningskolumnerna ändrar nästan aldrig, och ingen av tabellerna innehåller interna
grupperingar, skapa då ett gemensamt index för dessa två tabeller.
5.7. Sammansatta tabeller
Ett annat sätt att undvika föreningar (eller ”joins”) är att utnyttja något som ofta kallas för
sammansatta tabeller (composite tables). Principen är mycket enkel, den sammansatta
tabellen innehåller helt enkelt resultatet från en förening utförd på förhand. Principen
beskrivs lättast med lite SQL:
CREATE TABLE Table1 (
column1 INTEGER PRIMARY KEY,
column2 CHARACTER(50),
...)
CREATE TABLE Table2 (
column3 INTEGER PRIMARY KEY,
column4 CHARACTER(50)
...)
CREATE TABLE
column1
column3
column2
column4
...)
Composite (
INTEGER,
INTEGER,
CHARACTER(50),
CHARACTER(50),
CREATE UNIQUE INDEX Index1
ON Composite (column1, column3, column2, column4)
Om någon rad i Table1 eller i Table2 ändras, eller om rader adderas eller raderas, så är
den gemensamma tabellen inte längre synkroniserad. För att förhindra detta så måste även
den gemensamma tabellen uppdateras, vilket är enkelt att göra med en triggningsfunktion.
Sida 34 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
När man behöver förenad information från Table1 och Table2 skall man istället för att
exekvera det här uttrycket:
SELECT * FROM Table1, Table2
WHERE Table1.column1 = Table2.column3
använda det här uttrycket istället:
SELECT * FROM Composite
WHERE column1 = column3
Det senare uttrycket är helt enkelt en vanlig sökning i en enda tabell, som dessutom är
indexerad (Index1) för att snabba upp sökningen!
Den här tekniken är ett första steg mot denormalisering. Denormalisering innebär att man
avsiktligt bryter mot alla normaliseringsregler, vanligtvis för att vinna i hastighet eller för
att spara utrymme. En del personer och tillverkare påstår att en tabell kan dra direkt nytta
av denormalisering ”om nästan alla förfrågningar som görs är sådana förfrågningar som
innebär en förening av två tabeller”. Men som detta kapitel redan konstaterat flera gånger,
det finns många olika varianter av föreningsalgoritmer, för att inte nämna en mängd olika
situationer, och en del påverkar inte märkbart hastigheten av en sökning. När dessutom en
denormalisering innebär extra arbete vid uppdatering av tabellerna så vinner man inte
alltid så mycket, om man inte nästan enbart utför förfrågningar och inga ändringar. Det är
även klokt att, åtminstone i design skedet, fundera efter om man faktiskt vill frångå allt
vad som normalisering av data normalt innebär.
5.8. Kopplingar mellan tre eller flera tabeller
När fler än två tabeller förenas så sker föreningen i flera etapper. Det finns ingenting som
en ”Nested-Loop” inuti en annan ”Nested-Loop”. Däremot kan det hända att vissa
databashanterare klarar av att fördela arbetet över flera processorer och på så sätt avklara
en större dataförening på samma tid som en mindre förening av två tabeller. Strategin är
enkel: först förenas två tabeller och sen förenas resultatet med den tredje tabellen o.s.v.
Databashanterarnas automatiska förfrågningsoptimerare ser till att tabellerna förenas i en
vettig och effektiv ordningsföljd.
5.9. Outer Joins
Den vanligaste varianten av en ”outer join” är en ”left join”. Den kan exempelvis se ut så
här:
SELECT Table1.column1 FROM Table1
LEFT JOIN Table2
ON Table1.column1 = Table2.column1
En ”outer join” kan vara mycket långsammare än en ”inner join” eftersom Table1 måste
vara den yttre tabellen och Table2 måste vara den inre tabellen – annars fungerar inte
föreningen som en ”left join” skall göra. Även om Table1 är mindre och har ett bättre
index kan den fortfarande inte vara den inre tabellen. Det betyder att databashanteraren
inte kan välja den optimala strategiplanen för att utföra föreningen.
Sida 35 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Om databashanteraren får utföra en begränsande filtrering (med exempelvis IS NULL)
efter den yttre föreningen uppstår inga förvirrande problem med värden som [NOT]
NULL.
Det finns åtminstone en situation där en ”outer- left join” är betydligt snabbare och det är
istället för UNION. Innan ”outer-joins” introducerades i SQL måste en enkel ”left-join”
skrivas på det här sättet:
SELECT Table1.column1, Table2.column1
FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
UNION ALL
SELECT column1, CAST(NULL AS INTEGER)
FROM Table1
WHERE Table1.column1 NOT IN
(SELECT Table2.column1 FROM Table2)
Det här långa uttrycket är mycket långsamt men var det ända sättet att utföra en “left-join”
på innan riktiga “left-joins” introducerades. Idag kan man ersätta den gamla syntaxen med
följande, korta och snabba, uttryck:
SELECT Table1.column1, Table2.column1
FROM Table1
LEFT JOIN Table2
ON Table1.column1 = Table2.column1
6. Underförfrågningar (subqueries)
6.1. Bakgrund
En subquery, eller underförfrågan på svenska, är en förfrågan som innehåller en annan
förfrågan, med andra ord en SELECT stats inuti en annan SELECT sats.
SELECT ...
(SELECT ...)
...
/* Yttre förfrågan */
/* Inre förfrågan */
Det finns tre möjliga planer för att bearbeta en underförfrågan:
− flattened: Transformera förfrågan till en förening istället och utför denna.
− out-to-in: För varje rad i den yttre förfrågan, slå upp i den inre förfrågan.
− in-to-out: För varje rad i den inre förfrågan, slå upp i den yttre förfrågan.
Underförfrågningar tycks ha ett envist dåligt rykte som aldrig riktigt vill släppa. I
relationsdatabasens tidiga dagar gjorde man allt för att undgå underförfrågningar, och
Sybase till och med transformerade om dem till föreningar (joins) istället. IBM sade rakt
ut hur dåliga underförfrågningarna var. För MySQL tog det sju hela år innan första
versionen med stöd för underförfrågningar kom ut (dock fortfarande vid dagens datum en
beta version). Underförfrågningarnas dåliga rykt tycks alltså fortfarande hänga med,
åtminstone delvis, fastän hela konceptet mognat betydligt.
Sida 36 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Idag är underförfrågningar både snabba och pålitliga. Detta kapitel skall försöka ta fram
några svar till de vanligaste frågorna som berör underförfrågningar. När är det bättre att
använda en förening istället för en underförfrågan? Hur kan man bryta upp en
underförfrågan till endast en nivå (flatten)? Vad är det som framkallar olika strategier för
underförfrågningar i databashanteraren? Vilken specifik syntax kan man utnyttja för att
skriva de effektivaste underförfrågningarna?
6.2. Föreningar eller underförfrågningar - Joins eller Subqueries?
När man skriver en förening låter man oftast databashanterarens optimerare välja den
bästa strategin för den förfrågan som skall utföras. Alternativen är de redan bekanta ”SortMerge”, ”Nested-Loop”, och ”Hash”. Men när man skriver en underförfrågan har man
redan valt strategin för databashanteraren. Både ”out-to-in” och ”in-to-out” är realiseringar
av ”Nested-Loop” strategin, de skiljer knappt alls beroende på riktning. Redan det här är 1
– 0 till föreningarna – det är bättre att själv inte bestämma hur databashanteraren skall lösa
ett visst problem. Det bästa är alltid att förse databashanteraren med så många bra
alternativ som möjligt.
”Nested-Loops” för underförfrågningar kan avvika från ”Nested-Loops” för föreningar av
tabeller. Exemplet nedan visar skillnaden:
Nested-Loop för Joins:
for (each row in outer) {
for (each row in inner) {
if “match” add to matchlist
}
}
Nested-Loop för Subqueries:
for (each row in outer) {
for (each row in inner) {
if “match” EXIT LOOP
}
}
Skillnaden mellan dessa två är att för en underförfrågan räcker det med att endast finna en
matchande post, medan en förening behöver finna alla poster som matchar.
Underförfrågningen behöver alltså inte gå igenom alla poster och kan således avbryta
upprepningen är en matchande post har hittats. Det här är en klar vinst för
underförfrågningarna jämfört med föreningarna därför att underförfrågningen inte behöver
iterera lika mycket. Om man kan specifikt dra nytta av det här så vinner man nog mera än
vad man förlorar på att inte låta databashanteraren själv välja strategisk plan.
Nedan finns två exempel som bägge utför samma sak. Uttryck ett utför en förening av två
tabeller och uttryck två utför samma sak användandes en underförfrågan istället:
Uttryck #1
SELECT MIN(Table1.column1) FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
Sida 37 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Uttryck #2
SELECT MIN(Table1.column1) FROM Table1
WHERE Table1.column1 IN
(SELECT Table2.column1 FROM Table2)
Vetenskapligt utförda tester på de sju mest populära databashanterarna visar att uttryck två
alltid galant slår uttryck ett! Rådet är alltså: Använd underförfrågningar i sådana
situationer där det resultatmässigt är möjligt.
Nedan följer en kort sammanfattning av fördelarna för både föreningar och
underförfrågningar.
Argument som talar för föreningarnas (joins) fördel:
− Om den yttre frågans WHERE sats består av ett sammansatt uttryck av flera AND och
OR operationer, så kan databashanterarens automatiska optimerare bättre optimera
villkoret om förfrågningen är flat, d.v.s. utan underförfrågan.
− Några databashanterare kan utnyttja parallellexekvering effektivare med föreningar än
underförfrågningar.
− När man förenar två tabeller kan kolumner från bägge tabellerna ingå i det slutgiltiga
svaret. Det går inte med underförfrågningar.
− Eftersom folk tycks tro att föreningar är bättre så används de oftare, vilket leder till att
tillverkarna sätter mera resurser på att förbättra dem.
Argument som talar för underförfrågningarnas (subqueries) fördel:
− Jämförelsereglerna är inte lika strikta när man använder underförfrågningar. En
SMALLINT kan jämföras med en INTEGER i underförfrågningen, utan att man lider
av några prestandaförluster.
− Underförfrågningar kan innehålla GROYP BY, HAVING och mängdfunktioner. Det
är mycket svårare att skriva en bra förening om man behöver använda sig utav dessa,
speciellt om en del av syntaxen är gömd bakom en vy.
− Man kan skriva en UPDATE sats som använder en underförfrågan.
6.3. IN och ANY
När man skriver underförfrågningar skall man vara försiktig med att använda lika med
operatorn i sådana fall som ... = (SELECT <förfrågan>). Det orsakar att
iterationen inte kan avbrytas, eftersom man inte kan vara säker på att det finns endast en
matchande post innan alla poster är genomgångna. Använd istället nyckelorden ANY eller
IN för att möjliggöra avbrott vid första matchande posten. Undvik uttryck ett, skriv istället
som uttryck två eller tre:
Uttryck #1
SELECT column1 FROM Table1
WHERE column1 =
(SELECT column1 FROM Table2)
Sida 38 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Uttryck #2
SELECT column1 FROM Table1
WHERE column1 = ANY
(SELECT column1 FROM Table2)
Uttryck #3
SELECT column1 FROM Table1
WHERE column1 IN
(SELECT column1 FROM Table2)
Speciellt om tabellerna är stora är uttryck två och tre avsevärt snabbare än uttryck ett. Det
finns förstås en hastighetsskillnad i alla avseenden, men den kanske märks först efter en
längre tid när tabellerna börjat bli stora. Oftast spelar det heller ingen roll om det finns en
eller flera matchande poster i den inre förfrågan, och då finns det heller ingen vettigt
motivering till att använda lika med operatorn!
6.4. Flattening
Flattening, eller att göra en förfrågan flat, innebär att man bryter upp en underförfrågan till
ett uttryck bestående av endast en nivå. Istället för en underförfrågan uppstår då en
förening av tabellerna. Många databashanterare utför detta automatiskt, om det finns goda
grunder för det. Ibland kan en utflatningsoperation innebära att slutresultatet förändras.
Vanligast är att man i slutändan plötsligt har för många dubbletter av en dubbel post.
Genom att tillfoga DISTINCT kan man bli av med dessa, men då kan det hända att man
plötsligt har för få dubbletter i resultatet. En sådan här situation kan uppstå om de två
tabellerna innehåller ganska så lika data, och det är helt naturligt att slutresultatet
innehåller två identiska rader. Exemplet nedan visar hur man kan omforma uttryck ett till
uttryck två genom utflatning. Uttryck två lider av det problem som nyss beskrevs.
Uttryck #1
SELECT * FROM Table1
WHERE Table1.column1 IN
(SELECT Table2.column1 FROM Table2
WHERE Table2.column1 = 5)
Uttryck #2
SELECT Table1.* FROM Table1, Table2
WHERE Table1.column1 = Table2.column1
AND Table2.column1 = 5
Här är ett exempel på utflatning av en underförfrågan som innehåller NOT IN:
Underförfrågan med NOT IN:
SELECT Table1.column1 FROM Table1
WHERE Table1.column1 NOT IN
(SELECT Table2.column1 FROM Table2)
Utflatad motsvarighet:
SELECT Table1.column1 FROM Table1
LEFT JOIN Table2
ON Table1.column1 = Table2.column1
WHERE Table2.column1 IS NULL
Sida 39 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Den här typen av utflatning är mycket sällsynt och har två små buggar: (1) Utflatningen
antar att ON utförs före WHERE, vilket är den korrekta ordningsföljden enligt SQL
standard, (2) Kolumn ett i tabell två kan inte innehålla NULL värden, jämförelsen IS
NULL behövs för att hitta de rader som produceras av LEFT JOIN.
6.5. DISTINCT
DISTINCT skall användas med största försiktighet i underförfrågningar. Som redan
tidigare nämnts, så orsakar DISTINCT en extra sortering. Denna sortering går visserligen
snabbt om man bara listar en enda kolumn i selectlistan, och om den kolumnen är en
heltalskolumn går det väldigt snabbt. Exemplet nedan visar en situation där DISTINCT
hos vissa databashanterare förkortar exekveringstiden:
SELECT * FROM Table1
WHERE Table1.column1 IN
(SELECT DISTINCT Table2.column1 FROM Table2
WHERE Table2.column1 = 5)
6.6. EXISTS
Ibland kan EXISTS vara snabbare än IN. En transformering från ett uttryck med IN till ett
uttryck med EXISTS kan se ut så här:
IN:
SELECT * FROM Table1
WHERE Table1.column1 IN
(SELECT Table2.column1 FROM Table2)
EXISTS:
SELECT * FROM Table1
WHERE EXISTS
(SELECT * FROM Table2
WHERE Table1.column1 = Table2.column1)
Vilken av dessa två är då bättre? Svårt att säga genom att endast titta på dem. Det finns ett
par tumregler som visar i vilket sammanhang vilken är bättre:
− Om Table1 har många rader medan Table2 har få rader – använd IN.
− Om den yttre förfrågan har ett extra filtrerande villkor (exempelvis WHERE
Table1.column2 = 5) – använd EXISTS.
− Om den yttre förfrågan är en WHERE NOT ... – använd NOT EXISTS.
6.7. TOP
Vissa databashanterare kan begränsa hur många rader i resultatet som behövs vid körning
av en förfrågan. Exempelvis kan satsen TOP 5 infogas efter vilket SELECT kommando
som helst för att begränsa antalet svar till fem stycken. Det betyder att förfrågningen kan
avbrytas när fem stycken svar har hittats. TOP är speciellt användbart för
underförfrågningar där man vill ha så få rader som möjligt i den inre förfrågningen.
TOP operatorn kan ha många olika namn hos olika databashanterare. Bland annat
förekommer namnen FETCH, FIRST, TOP, LIMIT och SAMPLE.
Sida 40 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
7. Kolumner
7.1. Datatyper
När man definierar en kolumn bör man ha klart för sig vilken typ av data den kommer att
innehålla, och vilken data som är möjligt och vilken data som är omöjlig. På basen av den
kända dataformen kan man välja en lämplig datatyp för kolumnen. Till exempel så kan en
kolumn för förfluten tid kanske hellre bestå av INTEGER istället för INTERVAL, därför
att en INTEGER är mindre och är enklare att konvertera till en C-kompatibel datatyp.
När man väljer datatyp står man ofta inför ett val – skall databasen (och kolumnen)
optimeras för hastighet eller skall databasens fysiska storlek optimeras (så liten som
möjligt)? Valet av datatyp påverkar vilka förfrågningsoptimeringar som går att utföra –
och hur effektivt dessa utförs. Medan en del datatyper erbjuder fina möjligheter för
förfrågningsoptimering kanske andra datatyper erbjuder en flexiblare användning. Det är
svårt att kunna jämföra dessa två fördelar.
Hårddiskar är nu för tiden billiga och stora. Optimering av databasers storlek är kanske
därför inte lika viktigt idag som för 10 till 20 år sedan. Att alltid använda så liten datatyp
som möjligt innebär inte en lägre exekveringstid av databasförfrågningar. Det som
däremot påverkar exekveringstiden är antalet sidor databashanteraren behöver läsa ur
databasen. Och det är klart, är kolumnerna mindre till sin fysiska storlek, så ryms det
kanske flera rader i en sida. Det betyder att antalet I/O operationer, för att läsa en hel
tabell, minskar en aning. Den teoretiska minskningen är dock högst 10% och i praktiken
blir den högst 6%.
7.2. Fixerad eller varierbar längd?
När skall en kolumn ha en fixerad storlek och när skall den tillåtas variera i storlek? Det
bästa exemplet på en sådan fråga är huruvida man skall använda CHARACTER (CHAR)
eller CHARACTER VARYING (VARCHAR). Det finns även andra datatyper som
erbjuder två alternativ, en fixerad och en varierbar. ANSI SQL specificerar BIT och BIT
VARYING, IBM specificerar GRAPHIC och VARGRAPHIC och Sybase specificerar
BINARY och VARBINARY. Ibland måste man även ta andra kolumner i samma tabell i
beaktande, samt förmågan att lagra NULL värden, när man skall bestämma sig i den här
frågan.
I Microsoft SQL 6.5 och i Sybase är en kolumn som kan innehålla ett NULL värde alltid
av varierbar längd. Det betyder att en kolumn definierad som VARCHAR(15) NOT
NULL är varierbar längd – men även en kolumn definierad som CHAR(15) NULL.
I MySQL är alla kolumner av varierbar längd om någon av kolumnerna i tabellen är
definierad som varierbar längd. Även en kolumn definierad som CHAR(15) blir varierbar
om en annan kolumn definieras som VARCHAR(15).
Alla databashanterare sparar längden för den varierande cellen i ett separat värde på högst
fyra byte. På grund av denna extra data som måste sparas, är det onödigt att definiera en
kolumn som VARCHAR(4) om medellängden på den data som skall lagras ändå inte är
högre än fyra. I slutändan blir ändå den varierbara kolumnens totala storlek (summan av
alla rader) oftast mindre än om den vore statisk. Detta beror på att när man lagrar strängar
som är kortare än den maximala längden behövs inga utfyllnadstecken om kolumnen är
varierbar. Den statiska kolumnen tar alltid upp lika mycket utrymme – det maximala.
Sida 41 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Det enda argumentet som talar mot en användning av kolumner med varierande bredd är
att det tar längre tid att uppdatera dessa. Det tar alltså inte längre tid att läsa en kolumn
med varierbar bredd, eftersom posten innehåller längden på den lagrade teckensträngen.
Så är även fallet för kolumner med fixerad bredd – där vet databashanteraren längden på
teckensträngen just på grund av att kolumnens bredd är fixerad. Man använder alltså inte
null- terminator tecken i databassammanhang (som i C), utan det finns alltid någon
information som säger hur lång teckensträngarna är. Därför tar det inte längre tid att läsa
en teckensträng med varierbar längd.
Det tar däremot längre tid att uppdatera dessa, orsaken är skiftningar. Med skiftningar
avses förflyttningar av rader, orsakad av en rads storleksförändring. När längden av en rad
ändrar på grund av en UPDATE eller en DELETE sats, kan det hända att den uppdaterade
raden och alla andra underliggande rader måste flyttas eller skiftas. Om kolumner med
fixerad bredd hade använts så skulle vilken annan rad som helst passa in i det hål som
uppstod (om raden blev raderad).
Skiftningar är tidskrävande operationer. De är så tidskrävande att man helst av allt vill
undvika dessa. Desto snävare gränser man sätter på kolumnerna, desto större är risken för
att skiftningar skall inträffa. Skiftningar beror inte på att en enda kolumn ändrar storlek –
det är radens totala storlek som bestämmer om en skiftning inträffar. Det är ingen idé att
försöka sätta in en kolumn med fixerad längd, bara för att undvika skiftningar, om man
ändå senare även behöver ha en kolumn med varierbar bredd.
Ett försök att undvika skiftningar är att inte indexera kolumner av varierbar bredd samt att
så långt som möjligt använda kolumner med fixerad bredd.
7.3. Ögonblicksinformation
SQL standarden specificerar tre olika typer av ögonblicksinformation: DATE, TIME och
TIMESTAMP. Alla dessa är fält med fixerad bredd. Det enda noterbara är att alla dessa
lagras på samma sätt och kräver lika mycket fysiskt utrymmer. Därför kan man
rekommendera att alltid använda TIMESTAMP, eftersom en DATE och en TIME ändå
skulle ta upp samma utrymme. Om man nödvändigtvis endast vill ha datum eller tid, så
kan man välja att visa antingen eller när man skriver databasförfrågningen.
Den största fördelen med att alltid konsekvent använda TIMESTAMP är den att det aldrig
uppstår några problem när man vill räkna till exempel en tidsskillnad, eftersom bägge
posterna innehåller samma typ av information. Dessutom är det lä ttare att skriva
jämförelsefrågor mellan olika tidstämplar när alla har samma dataformat.
7.4. Heltal
SQL standarden specificerar två typer av heltal: INTEGER (INT) och SMALLINT.
Dessutom specificerar vissa databaser även BIGINT och TINYINT. Storleken är följande:
Datatyp
TINYINT
SMALLINT
INTEGER
BIGINT
Storlek
1 byte
2 byte
4 byte
8 byte
Största heltalet som kan representeras
255
65 535
4 294 967 295
18 446 744 073 709 551 615
Sida 42 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Alla tal som inte behöver decimaler skall alltid lagras som heltal. Heltalsaritmetik är alltid
snabbare än flyttalsaritmetik eller aritmetik med fixerade decimalpunkter. Den snabbaste
aritmetiken uppstår faktiskt med SQL datatypen INTEGER, när man använder en 32bitars processor, som till exempel en Pentium. Eftersom 32-bitar är dess naturliga
ordlängd så kan den bearbeta mest data om den får använda sig av fulla kapaciteten på 32bitar.
Rekommendationen är att alltid använda INTEGER, om talrymden räcker till.
7.5. Flyttal
SQL standarden preciserar tre olika datatyper för att lagra flyttal: REAL, FLOAT och
DOUBLE PRECISION (DOUBLE). DOUBLE är alltid 64-bitar hos alla databashanterare,
men REAL och FLOAT kan variera mellan 32-bitar och 64-bitar. Det är bäst att
kontrollera i databashanterarens manual före man väljer flyttalsdatatyp.
Med flyttal avses att kommatecknet ”flyter” och är inte fixerat vid samma punkt. Ett flyttal
kan representera väldigt stora värden, betydligt större värden än INTEGER och BIGINT.
Exponenten i tio-potensen kan röra sig kring området -307 till +308.
När man skriver SQL förfrågningar skall man alltid sträva till att ha samma datatyper på
bägge sidan om operatorn i en jämförelse. Detta gäller givetvis även när operanden är en
flyttalskolumn. Nedan finns ett exempel på hur man skall skriva en SQL sats som utför en
jämförelse på en flyttalskolumn. Skriv inte som i uttryck två, utan skriv som i uttryck ett:
Uttryck #1
UPDATE Table1
SET float_column = 1.25E02
Uttryck #2
UPDATE Table1
SET float_column = 125
Flyttalsoperationer utförs i regel snabbt om de utförs av flyttalsprocessorn. Om de däremot
emuleras av den vanliga processorn är de långsamma. Vid installation av databasservern
borde installationsprogrammet automatiskt känna av om en flyttalsprocessor finns i
systemet och installera sådan kod som kan utnyttja den.
Flyttalens precision måste även beaktas. När skall man använda REAL eller FLOAT (32bitar) och när skall DOUBLE (64-bitar) användas? I följande exempel har samma tal
lagrats med vardera datatypen. Tabellen har skapats så här:
CREATE TABLE Table1 (
real_column REAL,
double_column DOUBLE PRECISION)
Följande tal skrevs in i tabellen:
INSERT INTO Table1
VALUES (0.01, 0.01)
Sida 43 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Bägge kolumnerna innehåller alltså samma flyttal. De flesta databashanterare reagerar på
det här viset när en sökning gö rs mot dessa kolumner:
SELECT * FROM Table1
WHERE real_column = 0.01
Resultat: inga rader
SELECT * FROM Table1
WHERE double_column = 0.01
Resultat: en rad
Sökningarna gav olika resultat fastän samma tal från början hade lagrats i bägge
kolumnerna. Orsaken är att REAL kolumnens precision inte räcker till för att tillräckligt
approximera värdet 0,01 så att det kan användas vid en jämförelse med lika med
operatorn. Däremot har DOUBLE tillräckligt hög precision för att klara av jämförelsen.
Kolumner med högre precision har som tendens att oftare ge ett korrekt svar vid
databasförfrågningar.
Rekommendationen är att DOUBLE PRECISION alltid skall användas.
7.6. Decimaltal
SQL standarden erbjuder två datatyper för flyttal med fixerad decimalpunkt: DECIMAL
och NUMERIC. Med fixerad decimalpunkt avses att antalet decimaler är bestämt på
förhand, och således även precisionen. DECIMAL(7, 2) innebär att sju tecken är
tillgängliga för heltalsdelen och två tecken för decimaler. På så sätt kan man själv
precisera hur stor precision som behövs. I praktiken är det ingen skillnad på DECIMAL
och NUMERIC – teoretiska skillnader finns men databashanterarna följer dem inte.
Decimaltal lagras oftast packade i strängar. Vanligtvis ryms två decimala siffror i varje
byte. Detta medför att decimaltal är väldigt enkla att konvertera till teckensträngar
eftersom de inte lagras i ren binär form, men betydligt svårare att utföra aritmetiska
operationer på, eftersom de först måste konverteras till binära tal.
7.7. Serienummer
Enligt SQL standard finns inga kolumner för lagring av serienummer, sådana kolumner
som själva antar ett värde vid inmatning av en ny rad. De flesta databashanterarna erbjuder
ändå datatyper som automatiska räknare eller som genererar entydiga värden. Dessa
kolumner är va nligtvis 4-bytes heltalskolumner, alltså INTEGER. Serienummer är en bra
egenskap för att försäkra att varje inskriven rad får en unik identifikator, och tryggar
samtidigt radens unikhet.
Problem kan dock uppstå om väldigt många rader sätts in samtidigt av flera olika
användare. Olika låsningsmekanismer kan då stänga ut andra användare för en viss tid,
eller till och med orsaka att inskrivningen misslyckas.
Rekommendationen är att serienummerkolumner inte används. Använd hellre en
INTEGER kolumn och generera själv de unika serienummer som behövs.
Sida 44 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
7.8. Bitar
SQL standarden erbjuder två olika datatyper för att lagra värden på bit nivå: BIT och BIT
VARYING. Dessutom finns en speciell datatyp för att lagra sanningsvärden (som även är
BIT värden): BOOLEAN.
Tyvärr följer inte databastillverkarna SQL standarden när det gäller bitar, utan har ofta
egna specifika datatyper för det som BIT egentligen var avsett för. Microsoft kallar sin
BOOLEAN datatyp för BIT – fastän den inte har någonting att göra med SQL datatypen
BIT.
I MySQL lönar det sig att inte alls använda BIT datatypen, den är en synonym för
CHAR(1). Istället lönar det sig att skapa en CHAR(1) kolumn och addera en CHECK
begränsning för att strypa den till sanningsvärden. Detta ger en bättre och mera portabel
BOOLEAN.
7.9. Stora objekt (LOB)
I SQL standarden ingår tre olika datatyper för att lagra stora objekt: BINARY LARGE
OBJECT (BLOB) för binär data, CHARACTER LARGE OBJECT (CLOB) för
teckendata och NATIONAL CHARACTER LARGE OBJECT (NCLOB). Den sista stöds
knappt alls utav någon databastillverkare, förutom Oracle.
Den normala situationen när data lagras i tabeller är att en rad ryms inom en sida.
Eftersom sidstorleken är fixerad, kan inte en rad vara större än en enda sida. Exempelvis
så använder Microsoft en sidstorlek på 8 KB, och helt riktigt är CHAR och VARCHAR
kolumnerna begränsade till max 8 KB.
En LOB kan vara större än en sida, eftersom en LOB kolumn endast innehåller ett
pekarvärde till en annan sida (eller en pekare till en serie av andra sidor). Sidorna, som
utpekas av pekaren i LOB kolumnen, är oftast lagrade någonstans långt ifrån den övriga
tabellen, exempelvis i en egen fil. Om ett LOB är större än en sida krävs det flera sidor för
att lagra det. Dessa sidor länkas till varandra med hjälp av en länkad lista. Det finns inga
teoretiska gränser på hur stort ett LOB objekt får vara, men i praktiken begränsas de ofta
kring 2 – 4 GB. Eftersom objekten kan vara enorma, måste databashanteraren erbjuda
specialverktyg för att kunna läsa och skriva ett LOB. Vanligtvis finns det metoder med
vilka man kan skriva och läsa en sida i gången och på så vis undvika att skicka hela
objektet över ett nätverk. I praktiken är det så gott som omöjligt att hantera ett enda objekt
på två gigabyte, och dessutom kanske behöva skicka det över ett nätverk.
LOB kan ta upp onödigt utrymme, speciellt om ett NULL värde sparas. Även om
kolumnen innehåller NULL, så kommer ändå en hel sida att allokeras. Och även om man
endast lagrar till exempel en kortare text så kommer ändå hela sidan att upptas av objektet.
Följande nackdelar förknippas med LOB:
−
−
−
−
De slösar utrymme.
De kräver extra sidläsningar (mera I/O).
De stöder inte samma stränghanteringsfunktioner som CHAR och VARCHAR tillåter.
Vissa verktyg kan ha samarbetssvårigheter med stora objekt.
Sida 45 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Stora objekt (LOB) lämpar sig för följande situationer:
− När en kolumn behöver större databredd än andra datatyper kan erbjuda.
− När de data som lagras inte representerar teckensträngar utan är rå binär information,
som till exempel en bild.
Man kan även dra nytta av det faktum att en LOB inte sparas i själva tabellen, utan i en
skild fil. Med hjälp av den här kunskapen kan en LOB utnyttjas för optimering när alla
fyra punkter i följande fall uppfylls:
−
−
−
−
LOB kolumnen ingår sällan i SELECT satser.
Kolumnens bredd varierar ofta.
Kolumnens medellängd är åtminstone ett par hundra byte.
Kolumnen används aldrig i WHERE, GROUP BY eller ORDER BY satser.
Om alla dessa krav är uppfyllda är det lämpligast att använda en BLOB eller CLOB. Då
kommer fler rader att rymmas på en sida i tabellen, vilket snabbar upp alla operationer
som läser eller skriver tabellen. Dessutom kommer alla skiftningar att försvinna, som
normalt uppstår när en VARCHAR kolumn ändrar storlek, eftersom själva objektet som
varierar i storlek inte är lagrat i tabellen.
7.10. NULL
När man skapar en kolumn med varierbar bredd kan man alltid räkna med att åtminstone
en extra byte (och upp till fyra) kommer att behövas för att lagra kolumnens bredd. Det
andra fallet när en extra byte behövs är när man behö ver spara NULL värden. Eftersom
NULL inte är lika med något annat värde (inte ens ett annat NULL) så kan det inte lagras i
själva datakolumnen. Oftast lagras NULL värden som en extra flagga i början på
kolumnen som säger att ”detta är NULL” eller ”detta är inte NULL”. Flaggan kan
antingen ensam ta upp en hel byte eller så finns en gemensam byte för alla NULL flaggor
på den raden. På grund av detta är det omöjligt att efteråt byta en kolumn från NULL till
NOT NULL. NULL begräns ningar är den enda begränsning som påverkar den fysiska
lagringsstrukturen.
7.11. Kolumnernas ordningsföljd i en tabell
Den interna ordningsföljden på kolumnerna i en tabell kan påverka den allmänna
prestandan vid sökningar från tabellen. Rekommendationen är att alla kolumner med
varierande bredd eller NULL värden sparas sista (längst till höger) i tabellen. Oftast
behöver man inte fundera på ordningsföljden själv, eftersom databashanteraren
automatiskt kommer att skapa dem i den bästa ordningsföljden.
Det underlättar dock om man skapar dem i den ordning som oftast förekommer i SELECT
sökningar, med primära nyckeln längst till vänster följd av de mest populära kolumnerna
för sökningar.
Sida 46 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
8. Tabeller
8.1. Bakgrund
Prestanda beror på tid. Tiden beror på fysiska egenskaper. För att förstå hur tabellåtkomst
är beroende av tid måste man först förstå hur tabeller fysiskt lagras. Den fysiska lagringen
sker i minnet eller i filer. Den fysiska lagringen kan påverkas när tabeller skapas eller
omdefinieras, och databasförfrågningar kan påverkas av den fysiska lagr ingsarkitekturen.
8.2. Lagringsarkitektur
Lagringsarkitekturen beskriver hur rader grupperas: i sidor (pages), block (extents), filer
(files) och filgrupper (tablespaces). Bilden nedan visar hur dessa relaterar till varandra.
Databas
Filgrupper
Filer
Block
Sidor
Sidor
En sida är en samling av rader, fixerad till en viss storlek. Valet av denna storlek är
begränsad till multipler av 1024 (1 KB) inom ett område från 1024 (1 KB) till 65536 byte
(64 KB). Den optimala sidstorleken är relaterad till klusterstorleken på hårddisken. I takt
med att hårddiskarna blir större, stiger även klusterstorleken. Idag är den vanligaste
sidstorleken 8 KB, men snart höjs den troligen till 16 KB.
Alla rader i en sida hör alltid till samma tabell, åtminstone i teorin. Vissa
databastillverkare tillåter att rader från olika tabeller kombineras i samma sida, exempelvis
Microsoft och IBM.
En sida är den minsta datamängd som kan läsas eller skrivas mot en hårddisk. Det betyder
inte att en databashanterare endast läser en sida i gången. De flesta databashanterare läser
flera sidor på en gång, men en sida är den minsta mängden data som den kan läsa och är
den minsta mängden data som den vanligen skrivs till disken. Det finns ingenting som
”läsning av en rad”. Målet är att synkronisera sidstorleken med klusterstorleken på
hårddisken.
Sida 47 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Sidor är de som lagras i buffertar. När en läsning utförs (läsning är en flyttning av data
från hårddisken till minnet) lagras den lästa sidan i en buffert och kommer troligen att
ligga kvar en tid i bufferten. Databashanteraren väljer vilka sidor som skall ligga kvar i
bufferten med hjälp av en algoritm kallad ”Least-Recently- Used (LRU)”. Fördelen med
buffertar är att när samma data nästa gång behövs (eller ett närliggande data) så finns det
redan i buffertminnet och behöver inte läsas från disken.
Block
Ett block är en grupp av efterföljande sidor. Blocken existerar för att minska
allokeringsproblemen. Allokeringsproblem innebär att när en fil är full måste
databashanteraren öka dess storlek. Om detta görs varje gång en ny sida skapas så är det
ett enormt slöseri av tid därför att:
− Operativsystemet måste uppdatera filallokeringstabellen (FAT). Arbetet för att utföra
en uppdatering är det samma även om en sida adderas eller om åtta sidor adderas.
− Om först fil A utökas, sedan fil B, och sedan igen fil A och så vidare, måste
operativsystemet hålla reda på en massa små bitar som länkas om varandra fastän de
egentligen hör till olika filer. Sidorna skulle då lagras på disken i följande ordning:
ABABABAB... Detta kallas fragmentering och är inte bra. Fragmentering fördröjer
den tid som behövs för att läsa en hel fil.
Om sidorna kan hanteras som sammansatta block så minskas detta problem. Vid en
filökning på åtta sidor i gången så ryms ännu sju sidor till innan filen måste ökas på nytt.
Blockstorleken kan faktiskt påverkas i vissa databashanterare när en tabell skapas. Här är
ett exempel på hur blockstorleken definieras i Informix:
CREATE TABLE Table1 (
column1 INTEGER,
column2 VARCHAR(15),
column3 FLOAT )
EXTENT SIZE 20 NEXT SIZE 16
Filer
En fil är en grupp av efterföljande block. En fil behöver nödvändigtvis inte bestå av block
från enbart en tabell. De flesta databashanterare blandar ihop block från flera olika tabeller
och lagrar dessa i samma fil, även om de kanske inte tillåter blandning av sidor från olika
tabeller i samma block. Filer kan fördelas över flera partitioner, antingen för att de är stora
eller för att förbättra effekten av partitionering.
Partitioner
En partition är en grupp av sammanhängande block. Oftast är en partition en fil, men inte
nödvändigtvis. En fil kan fördelas på flera partitioner, exempelvis för att fysiskt sprida på
filen över flera fysiska enheter. Partitioner är dåliga om det finns väldigt få block och
väldigt få databaser och användare, men effektiva om det finns många databaser och
användare och många datablock. I sådana fall kan större nytta dras av parallella processer.
Sida 48 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Filgrupper
En filgrupp är en fil, eller en grupp av filer, som innehåller data. En filgrupp kan innehå lla
endast en tabell eller så kan den delas med tabellens indexfiler. Även andra tabeller kan
ingå i samma filgrupp. Det är med andra ord möjligt att blanda block från olika objekt i
samma filgrupp. Mixning kan ge vissa fördelar för administrering, men ger ingen ökning i
prestandan.
8.3. Datahögar (heaps)
En heap, eller ”datahög” på svenska, är en struktur för att på ett ostrukturerat sätt lagra
data. När något adderas till en hög sätts det in var det råkar finnas plats – oftast i slutet.
Ingen data behöver alltså flyttas för att tillfoga ny data. Enklare än så kan det inte bli. På
grund av den här fördelen så är datahögarna den populäraste strukturen för att bygga en
databas.
I en datahög har varje rad ett unikt rad-ID. Ett rad-ID byggs typiskt upp från sidnummer i
filen, radnummer i sidan och filens nummer i databasen. Alla databashanterare håller reda
på rad-ID:n och de kan utnyttjas i SELECT satser, om man bara vet vad rad-ID variabeln
heter. Sökningar som direkt använder ett rad-ID som villkor är överlägset de snabbaste i
alla sammanhang, eftersom rad-ID är den exakta adressen var posten hittas. Man bör
komma ihåg att ett rad-ID kan byta när som helst, om databashanteraren bestämmer sig för
att omorganisera en aning.
Nedan finns en tabell över olika namn som olika tillverkare använder för rad-ID variabeln.
DBMS
IBM
Informix
Ingres
Microsoft
MySQL
Oracle
Sybase
Rad-ID
RID
ROWID
tid
RID
_rowid
ROWID
RID
Rad-ID:n är användbara speciellt för specialsituationer i transaktioner.
8.4. Migration
Kolumner med varierbar bredd är mycket vanliga. Ibland uppstår de utan att man tänker
på det. Antag att följande situation uppstår: En kolumn med varierbar längd lagras överst
på en sida på rad #1 i sida #1. För tillfället har kolumnen värdet NULL. Följande
UPDATE sats körs:
UPDATE Table1
SET column1 = ’Kalles Kaviar’
Detta kallas för en expanderande uppdatering, eftersom raden helt klart blir större.
Problemet är att det inte finns något extra utrymme mellan rad #1 och rad #2 i samma sida
(på grund av att column1 är en kolumn med varierbar bredd och inte fixerad). Därför
måste alla rader börjandes från rad #2 skiftas nedåt i sidan innan rad #1 kan expanderas
(därför tar det längre tid att uppdatera en kolumn med varierbar bredd än en med fixerad
Sida 49 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
storlek). Men vad händer om sida #1 redan är full? Sidor har en fixerad storlek –
databashanteraren kan inte göra dem större. Och databashanteraren börjar inte heller att
skifta rad efter rad genom sida efter sida – ända tills sista sidan är nådd och utrymme
skapats. En sådan uppdatering kan ta en mindre evighet att utföra. Databashanteraren
kommer att placera den uppdaterade (något större) raden på första lediga plats i någon
annan sida (så nära som möjligt) och istället placera en pekare på radens gamla plats.
Den rad som flyttas ut till en annan sida är den rad som håller på och uppdateras – inte den
rad som i normala fall skulle bli utskiftad från sidan. Denna lösning sparar tid, eftersom
om databashanteraren skulle bli tvungen att flytta en annan rad så måste den först placera
en låsning på den innan uppdateringen kan gå vidare.
Den nya raden kommer troligtvis att placeras så nära som möjligt den gamla platsen. De
flesta databashanterare letar först efter en sida med ledigt utrymme inom samma block
eller letar genom en lista över sidor som har ledigt utrymme. Om inga lediga sidor hittas
måste databashanteraren skapa ett nytt block med nya lediga sidor dit den uppdaterade
raden kan sparas.
Databashanteraren ersätter den gamla raden med en pekare till den nya raden. Pekaren
innehåller den nya radens rad-ID. Även om den nya uppdaterade radens position är sida #7
rad #7 så kan den ännu kommas åt från sin gamla position på sida #1 rad #1
(databashanteraren följer pekarna automatiskt). Det betyder att om radens rad-ID har
använts för att komma åt raden, så kan fortfarande samma rad-ID användas för att nå
samma rad (raden har i princip två olika rad-ID, ett riktigt ID och en pekare). Det finns
heller ingen anledning att bygga om något index, eftersom indexet kan gott och väl
fortfarande peka på orginalpositione n, innehållande pekaren till den nya raden. En nackdel
är dock att alla försök att nå raden via den gamla positionen kommer att ta dubbelt så lång
tid som att nå den direkt på den nya positionen, eftersom en extra pekare måste följas för
att nå raden. Detta problem försvinner om tabellen omorganiserad.
Denna process som ovan beskrevs, att hitta utrymme för en rad som expanderar via en
uppdatering, kallas för migration. Förutom att migration förlänger åtkomsttiden för en viss
post, orsakar den även slöseri med utrymme. Kvar på den plats där raden en gång i tiden
fanns, finns nu endast en pekare och ett fönster med ledigt utrymme. Den sida som en
gång var väl fylld och utnyttjad till 100%, har nu plötsligt mera ledigt utrymme än
tidigare. Detta utrymme är svårt att utnyttja och beroende på storlek och placering kan det
hända att det aldrig ens kommer att utnyttjas.
Några tips för att undvika migration:
− Addera till lite extra utfyllnadstecken när den inledande INSERT satsen utförs.
− Utför inte en DELETE plus INSERT operation, använd UPDATE istället.
− Utför DELETE eller krympande uppdateringar före expanderande uppdateringar eller
INSERT satser.
− Om kolumnen kommer att ändras ofta, använd då istället en fixerad längd istället för
varierbar längd.
Sida 50 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
8.5. Fragmentering
Efter att en databas har utsatts för datauppdateringar under en längre tid, börjar den visa
tecken på fragmentering. En grupp av sidor har drabbats av fragmentering om:
− Onödigt mycket ledigt utrymme har uppstått på många sidor på grund av raderingar,
krympande uppdateringar eller migration.
− Den logiska ordningen av raderna inte längre är den samma som den fysiska på grund
av alla pekare som migrationen efterlämnat sig.
− Sidor och block för olika tabeller har blandats ihop.
Fragmentering är lätt att identifiera, en SELECT sats som listar alla rad-ID:n är ofta
tillräckligt. Rad-ID:ns sidnummerkomponent skvallrar om fragmenteringens grad. Om
några sidor har väldigt få rader finns det orsak till misstänksamhet. Program som kan
analysera sånt här följer vanligtvis med databasservern.
En databashanterare kan automatiskt försöka minska på fragmenteringen genom att så
långt som möjligt göra anspråk på och fylla ledigt utrymme i sidorna. Det finns dock inga
verktyg eller metoder för att förhindra fragmentering. För att minska fragmenteringen kan
man bli tvungen till att återuppbygga databasen eller köra ett verktyg som omorganiserar
sidorna. Även operativsystemets disk defragmenterare minskar på databasens
fragmentering och den bör köras med jämna mellanrum för att hålla fragmenteringen nere.
Fragmentering slösar med utrymmet och förlänger den totala åtkomsttiden. Faktum är att
fragmentering inte märks särskilt mycket på en kraftfull dator, fastän den är tungt belastad.
Däremot kan fragmenteringen kännas irriterande på en bärbar dator, som endast är avsedd
för en användare i gången (detta beror givetvis på de långsamma hårddiskarna i bärbara
datorer). Om databasservern underhålls regelbundet, och prestandan i en-användard- läge
inte spelar någon roll, så behöver man inte oroa sig för fragmentering. Däremot borde man
oroa sig över att läsning av migrerade rader tar dubbelt så lång tid att utföra.
8.6. Grupperade tabeller
Grupperade tabeller är sådana tabeller där raderna strävas till att hållas sorterade enligt en
viss kolumn som valts som grupperingsnyckel, vanligtvis den primära nyckeln. En fil,
innehållande sorterad sekventiell information, har klara fördelar jämfört med vanliga filer,
som till synes innehåller slumpmässigt placerad information (datahögsstrukturen).
Nackdelen är att INSERT satser tar längre tid att utföra, eftersom databashanteraren måste
skapa utrymme mellan två existerande rader för den nya raden. Däremot finns det en
chans att två efterföljande INSERT operationer inte behöver sätta in data på samma sida,
vilket minskar tävlan om en viss sida.
8.7. Normalisering
Normalisering innebär att göra raderna så små som möjligt och att endast lagra ett enda
värde i varje cell. Ibland kan det innebära att en del tabeller delas upp till två tabeller.
Mindre rader ger alltid snabbare sökningar, eftersom fler rader ryms på en sida i minnet.
Dessutom är det mindre chans att relevant information i operativsystemets buffertminne
knuffas ut om mängden ny information är så liten som möjligt.
Sida 51 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Normalisering kan även innebära att fragmentering lättare uppstår. Det är heller inte
möjligt att efter en normalisering matematiskt beräkna var en viss rad börjar (formeln
radstorlek * radnummer fungerar inte längre). Normalisering kan underlätta
utförandet av parallella processer samt minska låsningstider.
Normalisering är ett försök till en generell optimering på en allmän nivå, medan denormalisering är ett försök till optimering för en viss applikation.
Normaliseringsteori är en hel vetenskap och det är optimeringsmässigt ingen id é att gå
djupare än så här i den.
8.8. Vyer
En vy är en virtuell tabell som kan bestå av en vitt spriden samling av kolumner från olika
tabeller. En vy definieras av en underliggande SQL sats som bestämmer vilka kolumner
som ingår i vyn. I övrigt så kan en vy användas nästa precis som en tabell, när den en gång
är skapad.
Vyer har en tendens att göra förfrågningar långsammare, eftersom vyn inte innehåller
någon fysisk data vilket innebär en extra länk på vägen till den sökta posten. Exemplet
nedan skapar en enkel vy på en enkel tabell:
CREATE VIEW View1 AS
SELECT * FROM Table1
Sökningar via en vy tar faktiskt längre tid att utföra, uttryck två nedan utförs snabbare än
uttryck ett:
Uttryck #1
SELECT * FROM View1
Uttryck #2
SELECT * FROM Table1
Det är snabbare att utföra en förberedd förfrågan på en materialiserad vy två gånger, än att
materialisera vyn två gånger. (En materialiserad vy är en vy vars rader tar upp ett visst
fysiskt utrymme.) Följande materialiserade vy ger ett snabbt resultat:
CREATE VIEW View1 AS
SELECT SUM(column1) AS sum_column1
FROM Table1
Av följande två förfrågningar är uttryck två snabbare än uttryck ett:
Uttryck #1
SELECT SUM(column1) FROM Table1
Uttryck #2
SELECT * FROM View1
Uttryck två är snabbare för att vyn är färdigt förberedd på att hämta summan för
column1, därför behövs ingen förkompilering som för uttryck ett.
Sida 52 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Det är vansinne att använda en vy som består av en JOIN eller en UNION, om man inte
har för avsikt att använda alla kolumner. Om det dessutom existerar ett primärnyckel-tillfrämmande-nyckel- förhållande är det ännu värre. Följande exempel beskriver en sådan
situation:
CREATE TABLE Table1 (
column1 INT PRIMARY KEY,
column2 INT )
CREATE TABLE Table2 (
column1 INT REFERENCES Table1,
column2 INT )
CREATE VIEW View1 AS
SELECT Table1.column1 AS column1,
Table2.column2 AS column2
FROM Table1, Table2
WHERE Table2.column1 = Table1.column1
Om man nu utför följande två förfrågningar så kommer uttryck nummer två att utföras
betydligt snabbare än uttryck nummer ett:
Uttryck #1
SELECT DISTINCT column1 FROM View1
Uttryck #2
SELECT DISTINCT column1 FROM Table1
Databashanterarna lägger inte märke till vad som föregår sig och slänger inte bort den
onödiga föreningen som ingår i vyn. Rekommendationen är alltså att inte använda vyer
som innehåller föreningar av flera tabeller, om man endast behöver kolumner från ena
sidan av tabellföreningen.
9. Index
9.1. Bakgrund
Utnyttjandet av index är det vanligaste sättet att göra alla sökningar i en databas snabbare.
Samtidigt som sökningarna blir snabbare, så blir alla sorters uppdateringar långsammare
eftersom även indexet behöver uppdateras. Det här är nog ett ganska så vagt påstående,
om än korrekt i vissa sammanhang.
Indexen har funnits med i databaserna sedan länge tillbaka i tiden. Algoritmerna som
används för att bygga ett index har funnits ännu längre. Man vågar idag påstå att indexen
är välutvecklade och har uppnått en hög mognad. De flesta databashanterare använder sig
utav binära träd för att bygga index. Den binära träd teorin är över 30 år gammal, är
välbeprövad, och alla vet hur man drar nytta av dem. Det finns även index som baserar sig
på ”bitmap” teknik, inte heller någon nymodig teknik. Vissa databaser föredrar att
använda hash värden för att bygga index. Oavsett vilken teknik indexet består av så beter
de sig alla likadant gentemot databasprogrammerare.
Sida 53 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Syntaxen för att skapa ett index är följande:
CREATE [UNIQUE] INDEX <Index name>
ON <Table> ( <column> [, ...] )
Nyckelordet UNIQUE kan användas om man inte vill tillåta dubbla värden ingå i indexet.
Indexen måste oftast skapas utav databasadministratorn, men det är ofta programmerarnas
uppgift att informera denne om vilka kolumner och tabeller som behöver index.
I tidigare kapitel har indexen nämnts där de kommer in i bild, därför behövs inte de otaliga
situationer, där indexen underlättar förfrågningar, nämnas i detta kapitel.
9.2. Binära träd
Det finns två möjliga typer av sökningsoperationer när man hämtar data ur en databas. En
intern sökning inträffar när man hämtar information ur en fil som i sin helhet ryms i
datorns centralminne. När man behöver hämta data ur en väldigt stor fil, som inte ryms i
sin helhet i datorns centralminne, kallas sökningen för en extern sökning. Binära träd
passar bra ihop med externa sökningar. De möjliggör effektiva läs- och skrivoperationer
mot stora filer, med garanterad effektivitet.
Ett binärt träd är en sorterad lista av nycklar, ordnade enligt ett visst mönster.
Informationen ligger inuti så kallade noder. Varje nod innehåller, förutom informationen,
pekare till andra noder. Rotnoden är alltid den första noden, varifrån alla sökningar i trädet
startar ifrån. Noder som inte har flera noder under sig kallas för löv.
Exempelträdet ovan har en ordning på fyra. Det betyder att trädet har fyra nivåer med
data. Det maximala antalet jämförelser som behövs för att hitta en viss post är alltså högst
fyra stycken. Om datamängden i det binära trädet fördubblas, stiger ordningen ändå inte
mera än med ett. Om datamängden fyrdubblas stiger ordningen med två. Fastän
datamängden ökar i exponentiell takt, stiger ändå ordningen linjärt.
Sida 54 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Söktid mot data
70
60
data
50
40
ordning
30
data
20
10
0
1
2
3
4
5
6
7
söktid
Som diagrammet ovan visar så ökar inte den maximala söktiden nämnvärt fastän
datamängden fördubblas. Därför är det så viktigt att alltid försöka utnyttja index!
Binära träd är mest effektiva om de är balanserade. Med balansering innebär att ordningen
på trädet är den samma till alla löv. Databashanterare strävar till att hålla träden i balans
och använder sig utav specialiserade algoritmer för att utföra balansering.
Detta stycke är inte avsett att vara en kurs om binära träd, det finns skilda böcker i det
ämnet. Den grundläggande teorin presenterades för att förmedla en insikt i varför
indexsökningar är så snabba.
9.3. Vanliga index
Med vanliga index avses ett ”normalt” och enkelt index, skapade utan extra parametrar
och finesser. Index byggs vanligtvis upp med binära träd och hjälper till att snabbt hitta en
viss post.
Ett index är en sorters tabell bestående av nycklar från en vis s kolumn i en databastabell,
kombinerat med en pekare till den rad i databastabellen vilken den representerar. Via den
nyckeln kan databashanteraren snabbt läsa övriga kolumner från den raden.
Databashanteraren kan alltså snabbt hitta den data som relaterar till den nyckel som ingår i
indextabellen, utan att behöva gå igenom hela databastabellen.
9.4. Sammansatta index
Ett sammansatt index är ett index vars nycklar består av data ihopsamlade från flera
kolumner, vanliga index har nycklar från en enda kolumn. Sammansatta index kan
utnyttjas till att göra söktiden kortare för sådana kolumner som ofta jämförs tillsammans i
samma förfrågan, alltså inte mot varandra – utan tillsammans, oftast med AND mellan
dem.
Sida 55 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
I följande exempel finns det två separata vanliga index, ett för förnamn och ett för
efternamn. Följande situation kan uppstå vid sökning efter en viss person:
1. Det finns två index, ett för förnamn och ett för efternamn.
2. Databasförfrågan lyder:
SELECT * FROM Table1
WHERE surname = ‘smith’
AND given_name = ‘willy’
3. Databashanteraren letar genom det första indexet och finner följande matchande rader
för ‘smith’:
7, 35, 102, 448, 930
4. Databashanteraren genomsöker det andra indexet och finner följande matchande rader
för ’willy’:
16, 102, 137
5. Databashanteraren förenar dessa två svar och kommer fram med det slutgiltiga
resultatet:
102
Det är värt att lägga märke till att bägge delresultaten ger svaren färdigt sorterade. Det här
är ett naturligt beteende när databashanteraren använder binära träd för att bygga index.
Exemplet ovan är en aning förenklat, riktigt så här dumt går det inte till i praktiken. Det
praktiska beteendet börjar avvika från och med punkt fyra. Databashanteraren behöver inte
gå igenom det andra indexet, indexet för förnamnen. Eftersom databashanteraren redan
har fått en lista på alla personer som heter ’smith’ i efternamn, räcker det med att
kontrollera om någon av dem heter ’willy’. Det finns alltså ingen nytta med indexet över
alla förnamn, om man söker på det här viset.
Många databasprogrammerare tänker inte alls på det här, utan indexerar gladeligen alla
kolumner som ofta ingår på något sätt i förfrågningar. Detta kallas för överindexering och
är värdelöst – dessutom kan det leda till att alla uppdateringar blir onödigt tröga.
Det är med andra ord viktigt att man ordentligt tänker efter var index behövs och hurudana
index som behövs för att få full prestandaökning. Fallet som tidigare nämndes i detta
stycke är ett klart fall där ett sammansatt index behövs. Så här kan en sökning gå till i
samma databas, men med ett sammansatt index den här gången:
Sida 56 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
1. Det finns ett sammansatt index för både efternamn och förnamn.
2. Databasförfrågan lyder:
SELECT * FROM Table1
WHERE surname = ‘smith’
AND given_name = ‘willy’
3. Databashanteraren slår upp i det sammansatta indexet och hittar ett enda svar:
102
Den här gången räckte alltså med en enda titt i det sammansatta indexet för att hitta den
enda matchande posten. Såhär kan samma index utnyttjas för att både kontrollera
efternamn och förnamn. Ett sammansatt index är faktiskt det enda sättet att effektivt
utnyttja index för bägge villkoren i ovanstående exempel. Några tumregler för
sammansatta index:
− Använd sammansatta index om förfrågningar ofta innehåller samma kolumner,
förenade med AND.
− Sätt inte mera än högst fem kolumner i ett sammansatt index.
− Kolumnen som sätts längst till vänster i kolumnlistan när indexet skapas skall vara den
kolumn som oftast förekommer i databasförfrågningar. Dessutom får den gärna ha den
högsta selektiviteten.
− Sökningen blir effektivast om ordningen på kolumnerna i WHERE villkoret är den
samma som ordningen i det sammansatta indexet (samma ordning som vid skapandet
av indexet.)
9.5. Heltäckande index
Ett heltäckande index är egentligen samma sak som ett sammansatt index, åtminstone
fysiskt. Ett sammansatt index kallas för ett heltäckande index om det skapas på sådant sätt
att all data som söks finns i indexet. Följande situation är ett bra exempel:
Ett sammansatt index existerar för kolumnerna namn (name) och kön (sex), i den
ordningsföljden. Följande databasförfrågan utförs:
SELECT sex FROM Table1
WHERE name = ’SAM’
Databashanteraren kommer att välja det sammansatta indexet som index för den här
sökningen, eftersom indexet är skapat med namn kolumnen längst till vä nster, och är
således ett lämpligt index för den här sökningen. Efter att databashanteraren funnit
nyckeln {SAM, M} i indexet märker den att svaret redan finns i indexnyckeln. Det finns
alltså inget behov att gå till tabellen och slå upp motsvarande rad! Vid en sådan här
situation kallas alltså det sammansatta indexet för ett heltäckande index, eftersom all
information som behövdes fanns i indexet. Det här sparar en hel del tid eftersom ingen
ytterligare läsning från hårddisken behövs utföras.
Sida 57 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Vinsten av ett heltäckande index uppstår endast om alla kolumner som ingår i selectlistan
finns i det sammansatta indexet. Om någon kolumn som inte ingår i det sammansatta
indexet finns med i selectlistan, måste ändå en diskläsning utföras för att hämta den
kolumnen, och då har ingen prestandaökning uppstått. Även fel ordning på kolumnerna i
selectlistan kan innebära att det heltäckande indexet inte kan utnyttjas.
Heltäckande index utnyttjas automatiskt (under förutsättning att de existerar och
sökningen utförs på ett korrekt sätt) när man söker efter någonting. När man inte söker
efter någonting, utan kanske endast vill utföra en listning, är det väldigt få
databashanterare som förstår att utnyttja heltäckande index, fastän alla kolumner i
selectlistan finns i det sammansatta indexet. Även här, som med alla index, kan man
uppmuntra databashanteraren till att utnyttja index genom att tillfoga en extra WHERE
sats. Uttryck ett nedan kommer troligtvis att utföra en tabellskanning, fastän ett
sammansatt index av kolumnerna name och sex existerar. Uttryck två borde däremot
utnyttja det sammansatta indexet, som i det här fallet är ett heltäckande index, och inte
utför en tabellskanning.
Uttryck #1
SELECT name FROM Table1
ORDER BY name
Uttryck #2
SELECT name FROM Table1
WHERE name > ’’
ORDER BY name
För att exempelvis lista kön (sex) kolumnen skall man inte skriva följande förfrågan:
SELECT sex FROM Table1
Denna förfrågan kommer att orsaka en skanning av hela tabellen, trots att ett heltäckande
index existerar. Problemet är att sex inte är kolumnen längst till vänster i det
sammansatta indexet, och därför kan det heller inte användas för kolumnen sex ensam.
Skriv istället så här:
SELECT name, sex FROM Table1
Fastän den extra kolumnen name inte behövs i svaret, så utförs listningen snabbare om
den är med. Orsaken är givetvis att det heltäckande indexet kan utnyttjas när name
kolumnen är med (och är placerad längs till vänster i selectlistan) och ingen tabellskanning
behöver utföras (både name och sex ingår i indexet.)
Sida 58 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
9.6. Unika index
Selektiviteten i ett index kan beräknas med hjälp av följande enkla formel:
(antalet unika värden) / (totala antalet värden)
Selektiviteten kan bli mindre än ett om NULL värden är indexerade eller om dubbletter av
likadana värden är indexerade. Perfekt selektivitet uppstår när förhållandet mellan unika
värden och totala antalet värden är ett. Ett index som har perfekt selektivitet är ett unikt
index. Namnet kan vara missvisande – det är inte indexet som är unikt, utan nycklarna i
indexet är unika (vilket i sig betyder att den kolumn som är indexerad innehåller unika
poster).
Unika index är ett av de absolut bästa indexen man kan ha, och alla automatiska
optimerare i databashanterarna blir helt ivriga att få optimera förfrågningarna om de kan
använda ett unikt index.
Några goda skäl att skapa unika index istället för vanliga index:
− Microsoft och Sybase ger högre poäng för ett index som har en god selektivitet, d.v.s.
ett eller nästan ett i alla fall. Index med en lägre selektivitet än 0,1 bry de sig inte ens
om att använda. (De kostnadsbaserade optimerarna använder statistik för att avläsa
selektiviteten, medan regelbaserade optimerare endast kontrollerar om indexet har en
unikflagga.)
− Om databashanteraren redan på förhand känner till att indexet är unikt, kan den
avbryta sökningen när den har hittat det värde den sökte utan att behöva fortsätta till
slutet på indexet.
Ett index skall alltså helst vara unikt, om det är möjligt. Man förlorar aldrig något på att
göra ett index unikt, om den data som indexeras är unik. Det finns ett par fall där unika
index automatiskt skapas av så gott som alla databashanterare:
− Alla primära nycklar får automatiskt ett unikt index.
− Alla fält som deklareras unika med UNIQUE satsen får automatiskt ett unikt index.
(Kolumner som deklareras unika tillsammans får ett sammansatt unikt index.)
Om databasen är i ett tidigt utvecklingsstadie, och all eventuell data inte ännu är känd, så
medför ett unikt index en begränsning som kanske inte är önskad. Unika index bör skapas
först då man verkligen vet att all data verkligen kommer att vara unik.
De automatiskt skapade indexen kan orsaka problem och förvirrning om användaren
skapar ett eget index. Överflödiga index förslöar sökningar i de kolumner som berörs, men
framförallt förvirrar de databashanterarnas automatiska förfrågningsoptimerare.
Kontrollera alltid att inget index existerar från tidigare innan ett index läggs till manuellt,
speciellt när indexet gäller kolumner som har någon form av begränsning genom
PRIMARY KEY, FOREIGN KEY eller UNIQUE.
Sida 59 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
9.7. Grupperade index
Grupperade index stöds av de största databastillverkarna och har redan funnits med en
längre tid än många av de andra indexvarianterna. Egentligen är det kanske lite fel att kalla
den här egenskapen för ett index, eftersom den egentligen håller koll på var en ny rad
placeras i en tabell, samtidigt som den upprätthåller ett sorterad index. Indexets uppgift är
alltså att försöka hålla tabellens data så lik indexet som möjligt.
Grundidén går ut på att lagra all identisk, eller nästan identisk data, så nära varandra som
möjligt i tabellen. Data grupperas alltså i grupper med likadana, eller nästan likadana,
värden. Eftersom all data, som vid en sökning pekas ut av det sorterade indexet, ligger
nära varandra, finns det en större chans för att all data som behövs till resultatet finns med
på en sida ur tabellen. (En databashanterare läser typiskt en hel sida i gången från en
tabell, använder den, och läser eventuellt ännu nästa sida o.s.v. En sida kan exempelvis
bestå av 4 – 16 KB data.) Om alla poster som behövs finns på en sida, så behöver
databashanteraren inte läsa flera sidor för att sammanställa svaret, utan kan avbryta när
alla sökta poster är funna betydligt tidigare än i en slumpmässigt ordnad tabell.
Detta betyder att förfrågningar som denna exekveras snabbare med hjälp av ett grupperat
index:
SELECT * FROM Table1
WHERE column1 = ’Bruno’
Även denna förfrågan utförs snabbare eftersom tabellen redan på förhand är sorterat till en
viss nivå:
SELECT * FROM Table1
ORDER BY column1
Man kan väl säga att i de fall där det inte är möjligt att använda ett unikt index, så är
grupperade index det bästa alternativet. I exemplet ovan kan inte ett unikt index användas
om det finns flera poster som innehåller ’Bruno’.
En tabell kan endast ha en kolumn som grupperingsnyckel. Detta är väl en självklarhet,
eftersom man endast kan sortera på ett sätt i gången.
Det finns två varianter av grupperade index, svaga grupperingar och starka grupperingar.
De flesta grupperade index har svaga grupperingar. Det innebär att indexet inte garanterar
att tabellen är perfekt sorterad, men gör allt det kan för att så långt som möjligt uppnå en
perfekt ordning. Två av de stora databastillverkarna stöder starka grupperingar. Dessa
håller ordning med våld, även om det innebär mycket arbete med uppdateringar av både
index och förflyttningar av rader i tabeller. Hur dessa två utför sitt arbete i praktiken och
hur de urskiljer varandra är ett ämne för sig som inte tas upp i det här sammanhanget.
Sida 60 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
9.8. Bitmap index
Även om alla databashanterare använder binära träd för att bygga index så erbjuder ändå
ca 50% av dem ett annat val – bitmap index. Dessa index är bra för att få statistik utav ett
stort antal rader innehållande statiska data. Den bitmap index teknik som beskrivs i detta
stycke stöds av Informix, Oracle och Sybase. IBM använder en annan teknik, men idén är
den samma.
Ett bitmap index består utav flera rader innehållande bitar. En rad med bitar kallas för en
bit vektor (en vektor är en endimensionell tabell). Ett exempel beskriver bitmap index
tekniken bäst. Antag att tabellen Table1 har en kolumn som heter sex och ett bitmap
index över sex kolumnen. Kolumnen kan ha tre olika värden: M, F eller NULL. Tabellen
innehåller följande data:
Table1.sex
F
M
F
F
Eftersom varje cell i tabellen kan ha tre olika värden, så kommer bitmap indexet att bestå
av tre bit vektorer. Varje rad i bit vektorn är en bit lång. Eftersom Table1 består av fyra
rader, så består även varje bit vektor av fyra rader. Varje bit i varje vektor kan vara satt till
ett eller noll, ett för sant och noll för falskt. (Om alla vektorer innehåller en nolla på
samma position innebär det att motsvarande rad i tabellen har raderats.) Bitarna i alla
vektorer är ordnade enligt radnummer i tabellen och positionen för en viss bit i en vektor
motsvarar samma rad i tabellen. Här är bitmap indexet för tabellen ovan:
M-vektor
0
1
0
0
F-vektor
1
0
1
1
NULL-vektor
0
0
0
0
Bitmap index lagrar alltså mycket onödig data. För exempelvis rad två är M- vektorn sann,
F-vektorn falsk och NULL- vektorn falsk. Trots denna onödiga lagring är det totala
lagringsbehovet mycket litet – endast 12 bit, eller 1½ byte! I teorin är tre stycken 8 KB
index sidor tillräckligt för att spara indexinformation på 65536 rader av data. Antag att
Table1 sväller ut till just 65536 rader med data. För att kontrollera om det finns några
män i tabellen körs följande förfrågan:
SELECT * FROM Table1
WHERE EXISTS
(SELECT * FROM Table1
WHERE sex = ’M’)
För att kontrollera om det finns några män i tabellen räcker det med att kontrollera om
någon bit i M-vektorn är sann. Och nu kommer det trevliga: Att skanna igenom var tredje
bit i ett 24 KB block går löjligt snabbt. En 6 – 10 raders assemblerrutin utför detta jobb på
någon bråkdels mikrosekund, om den utförs på en dator i gigahertz klassen. Ingen kan väl
påstå att skanning av ett 65000 raders index på någon bråkdels mikrosekund inte är
Sida 61 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
snabbt! Även om ingen databashanterare använder sig utav optimerade assemblerrutiner så
går det ändå snabbt att söka i ett bitmap index.
Bitmap index är speciellt användbara i följande situationer:
− Om kolumnen som behöver indexeras har en selektivitet lägre än 0,1 är det bästa
indexet ett bitmap index.
− Bitmap index underlättar speciellt förfrågningar som innehåller [NOT] EXISTS,
UNIQUE eller GROUP BY.
− Bitmap index lämpar sig bäst för stora (eftersom bit lagring är effektivt och tar lite
utrymme), onormaliserade (många likadana poster), statiska (uppdatering av en rad
innebär uppdatering av en massa bitar) tabeller.
Bitmap index är inte effektiva på kolumner som kan anta tusentals olika värden. Om man
exempelvis vill lagra färgnummer i kolumnen colour, vilket kan vara vilken färgkod
som helst mellan noll och 256 eller NULL, så bör radantalet överskrida 25700 rader innan
det lönar sig att skapa ett bitmap index för den kolumnen.
9.9. Övriga index
Förutom de index som nämnts så här långt (vilka alla stöds av de flesta databashanterare),
finns det även andra varianter av index som är mera databasspecifika. De största
varianterna under rubriken ”övriga” är hash index och full-text index.
Hash index stöds bland annat av Ingres, Informix, Oracle och PostgreSQL. Hash index
konkurrerar hårt mot binära träd och det är förvånande att så få databastillverkare stöder
dem.
Full- text index stöds av Microsoft SQL och MySQL. Dessa index är relativt enkla att
använda, men väldigt tunga att uppdatera. Sökmotorer på Internet använder sig typiskt av
full-text index.
9.10. Indexnycklar, trunkering och komprimering
Den allmänna regeln för indexnycklar är att de skall vara en exakt kopia av den data som
är indexerad, både innehållsmässigt och datatypsmässigt (det finns ett par undantag i
Oracle). Om den indexerade kolumnens datatyp är en SMALLINT på 16-bitas, så är även
indexnyckelns datatyp SMALLINT på 16-bitar. Och om kolumnens bredd är varierbar –
så är även indexnyckelns bredd varierbar.
Ett undantag finns dock. En indexnyckel kan (undantagsvis) vara trunkerad. Med
trunkerad avses att slutet fattas. Trunkering kan ibland ske helt automatiskt utan att
användaren märker det, eller på uppmaning av användaren. Trunkering kan vara en god
egenskap, eftersom det är en god egenskap att ha så korta indexnycklar som möjligt. Och
ett par, kanske tiotals, tecken borde oftast vara tillräckligt för att urskilja den sökta posten
från andra liknande poster. De enda bieffekterna som syns kan vara följande:
− Vid användning av en ORDER BY sats är kanske resultatet inte helt fullständigt
sorterat, om kolumnen inne håller sådana poster som är väldigt identiska i början och
har drabbats av trunkering.
Sida 62 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
− Vissa förfrågningar, speciellt de som innehåller jämförelser av typen column1 =
column2, kan ta längre tid att utföra därför att inte all information som kan behövas
vid jämförelsen finns i indexet.
Det är viktigt att komma ihåg att trunkering inte innebär att en del precision förloras.
Innehållet i tabellen är fortfarande det samma, fastän innehållet i indexet inte alltid är
tillräckligt för att utföra vissa sökningar utan att hämta data ur tabellen. Vanligtvis kan det
bli aktuellt att överväga trunkering när nycklarna börjar bli över 100 byte stora, vilket är
mycket sällsynt.
Komprimering kan utnyttjas för att hålla nere den fysiska storleken hos ett index. Det
lönar sig inte att implementera några tunga komprimeringsalgoritmer – orsaken till att
index används är ju fortfarande den att det skall gå snabbt när man söker data. Det finns ett
par enkla sätt att komprimera nycklarna i ett index, utan att det märkbart försämrar
indexets prestanda. Givetvis är alltid ett okomprimerat index det snabbaste indexet,
speciellt om binära sökningar skall utföras.
Indexkomprimering kan utföras på två olika sätt: främre komprimering och bakre
komprimering. Främre komprimering bygger på principen att alla nycklar i ett index ligger
i ordning. Det innebär att de n första byte i en nyckel troligtvis är de samma som i
föregående nyckel. De n första byte kan därför bytas ut till en byte, vilken berättar att de n
första byte är de samma som hos föregående nyckel. Nedan finns fyra nycklar:
Johnson
Johnson
Jonathan
Jones
(total 27 byte)
Om varje nyckel börjar med en inledande byte, vilken berätta hur många byte som är de
samma som hos föregående nyckel, kan de fyra indexnycklarna lagras på fö ljande sätt:
0Johnson
7
2nathan
3es
(totalt 19 byte)
Redan i detta enkla exempel, med endast fyra nycklar, kunde en komprimering från 100%
(okomprimerat) ner till ca 70% uppnås. Komprimering av indexnycklar kan alltså spara
mycket utrymme om indextabellerna är stora.
Bakre komprimering innebär att slutet av nycklarna trunkeras. De trunkeras på ett sådant
sätt att tillräckligt med data blir kvar för att skilja på alla nycklar. Och om det räcker med
en del av tecknen för att nycklarna skall kunna identifieras – varför då lagra mera
information? Bakre komprimering innebär alltså en viss förlust i det data som
komprimeras, men det är inte så farligt för tabellens data finns ju fortfarande kvar.
Samtidigt medför detta att om mycket likartad data matas in i tabellen, så kan ett behov av
mindre komprimerade nycklar i indexet uppstå. I det fallet är det bara att bygga om hela
indexet på nytt. Om samma nycklar som ovan skulle komprimeras med den bakre
komprimeringstekniken skulle de se ut så här:
Joh
Joh
Jona
Jone
(totalt 14 byte)
I det här fallet uppnåddes högre komprimering med den bakre komprimeringstekniken. En
komprimering på ner till ca 50% av orginalstorleken uppnåddes.
Sida 63 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Bägge komprimeringsteknikerna har sina för- och nackdelar. Ett bakre komprimerat index
är snabbare att söka i, men tyngre att uppdatera och underhålla. Ett främre komprimerat
index är långsammare att söka i, eftersom varje nyckel måste beräknas utgående från
föregående nyckel. Däremot är det lättare och enklare att infoga nya nycklar i ett främre
komprimerat index, eftersom inga problem med urskiljbarheten kan uppstå.
10. Begränsningar
10.1. Bakgrund
En programmerare skapar begränsningar i databasen så fort det finns den minsta lilla
möjlighet att införa en begränsning. Programmeraren lever i tron att om mindre skräpdata
går in i databasen, så kommer det även mindre skräpdata ut ur databasen. Men är skräpfri
data i sig själv en optimering? Kan det ge optimeraren och användaren bättre information
och hjälp vid utvecklingen av SELECT förfrågningar? Eller kommer den innerliga
felkontrolleringen att bromsa satser som INSERT, UPDATE eller DELETE?
Detta kapitel kan förhoppningsvis ge svar på dessa frågor samt ta fram de goda och de
dåliga effekterna av begränsningar, med optimering i tanken.
10.2. NOT NULL
NULL värdet är en egenskap som krävs av data integritetsreglerna i relationsteorin.
Relationsteorin upptäcker att i vissa situationer är till exempel ett datum ännu okänt, inte
tillgängligt eller ej tillämpningsbart. Värdet NULL finns till för att representera alla dessa
tre situationer. Det är viktigt att komma ihåg att NULL inte betyder att cellen är blank
eller en nolla – NULL är någonting helt annorlunda. Även om en blank cell är lika med en
annan blank cell och noll är lika med en annan nolla, så är aldrig ett NULL värde lika med
ett annat NULL värde eller med någonting annat. Det finns med andra ord ingenting som
är lika med ett NULL värde.
NOT NULL begränsningar används för att tvinga en kolumn att alltid innehålla ett giltigt
och tillämpningsbart data – att försäkra att den inte är NULL. I SQL standard är den här
syntaxen:
... <column> ... NOT NULL
endast en förkortning för följande syntax:
... CHECK ( <column> IS NOT NULL)
NOT NULL begränsningar är alltså endast CHECK begränsningar, enligt SQL standard.
Hos de flesta databashanterare finns det dock avvikelser från standarden. NOT NULL
avskiljer sig oftast från CHECK på följande sätt:
− NOT NULL begränsningar är vanligtvis inte namngivna och kan därför inte tas bort
utan att radera hela kolumnen.
− Att definiera en kolumn som NOT NULL påverkar hur kolumnens data fysiskt lagras.
− NOT NULL begränsningar är ibland obligatoriska.
Sida 64 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
NOT NULL begränsningar påverkar hur det fysiska data lagras, eftersom ett NULL värde
inte kan lagras i själva kolumnen. NULL värden lagras vanligtvis som flaggor utanför
kolumnen.
En kolumn som kan innehålla ett NULL värde tar en aning längre att läsa, eftersom en
extra kontroll på NULL flaggan måste utföras innan själva posten kan avläsas. Kolumner
som definierats som NOT NULL är alltså snabbare att hämta data från.
10.3. CHECK
En CHECK begränsning införs för att försäkra att en kolumn endast kan innehålla data
som ingår i en viss förhandsbestämd mängd. CHECK kan definieras på följande sätt:
... CHECK (column1 BETWEEN 0 AND 9)
... CHECK (column1 >= -200 AND column1 <= 200)
En CHECK begränsning skall inte definieras på följande sätt:
... CHECK (date_column > CURRENT_DATE)
... CHECK (column1 IN (SELECT column1 FROM Table1))
Främmande nycklar eller lagrade procedurer är bättre alternativ för att utföra kontrollerna i
exemplet ovan.
Det är tekniskt möjligt att en CHECK begränsning inte stoppar felaktig data att skrivas in i
cellerna vid en UPDATE, men felet kommer att upptäckas när SQL satsen kommit till sitt
slut. Denna situation kan uppstå på grund av CASCADE, TRIGGER eller om kontrollen
baseras på andra kolumner som uppdateras senare i samma sats. På grund av dessa tre
orsaker, kommer en databashanterare att vänta tills SQL satsen kört klart innan den börjar
kontrollera CHECK begränsningar. Detta medför att varje rad måste kommas åt två
gången: en gång för att uppdatera den och en gång för att kontrollera värdet. Om
uppdateringen drabbade hundratals rader, finns det inga garantier för att de uppdaterade
raderna fortfarande finns kvar i buffertminnet när den andra åtkomsten inträffar.
Ibland finns det situationer där man faktiskt behöver ha både CHECK och NOT NULL på
samma kolumn. Ta följande exempel:
CREATE Table1 (
column1 INTEGER NOT NULL,
...
CHECK (column1 < 10)
... )
Eftersom NULL inte är likt någonting annat så kan inte CHECK begränsningen avgöra om
NULL är ett giltigt värde eller inte. En CHECK begränsning måste alltså inte vara sann –
enligt definition räcker det med ”annat än falskt”. NULL är ”annat än falskt” – därför
behövs även NOT NULL begränsningen.
Sida 65 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
10.4. FOREIGN KEY
En främmande nyckel används för att påtvinga att en kolumn endast kan innehålla sådana
värden som ingår i en annan kolumn – eller NULL värden. Den främmande nyckeln måste
matcha en kolumn som är unik, vanligtvis primärnyckeln. Både datatyp och kolumnstorlek
måste stämma överens. När en begränsning med främmande nycklar mot primära nycklar
införs kallas begränsningen referensintegritet.
Referensintegriteten medför att alla begränsningar som gäller för primärnyckeln (utom
NOT NULL och UNIQUE) även gäller för den främmande nyckeln. I följande exempel
finns alltså en onödig CHECK begränsning för Table2:
CREATE TABLE Table1 (
column1 SMALLINT,
CHECK (column1 > 0),
PRIMARY KEY (column1))
CREATE TABLE Table2 (
column1 SMALLINT,
CHECK (column1 > 0),
FOREIGN KEY (column1) REFERENCES Table1)
Det är onödigt att kontrollera column1 i Table2 med en CHECK, eftersom kolumnen
är beroende av referensintegritet mot Table1 som redan har en CHECK för column1.
Många databashanterare utför ändå den dubbla kontrollen med den följden att det tar
onödigt lång tid att föra in nya värden och att uppdatera värden. En del databashanterare
upptäcker den dubbla kontrollen och ignorerar alla extra kontroller om kolumnen har
referensintegritet. Samma kolumn bör inte vara främmande nyckel till mer än en
primärnyckel, medan en primärnyckel kan ha många främmande nycklar i olika tabeller
(en för varje tabell). Exemplet nedan är oacceptabelt, eftersom det förvirrar hela
användningen av index:
CREATE TABLE
column1
FOREIGN
FOREIGN
Table3 (
INTEGER,
KEY (column1) REFERENCES Table1,
KEY (column1) REFERENCES Table2)
10.5. PRIMARY KEY
En primärnyckelsbegränsning införs för att påtvinga en kolumn att endast innehålla unik,
icke NULL, data. Begränsningens uppgift är att övervaka att alla rader i en tabell kan
identifieras unikt. Allt för ofta påträffas följande felaktiga definition av primärnyckeln:
CREATE TABLE Table1 (
column1 ... NOT NULL PRIMARY KEY,
... )
Sida 66 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Ingen större skada skedd, men enligt rekommendationer skall primär nyckeln definieras på
följande sätt:
CREATE TABLE Table1 (
column1 ... NOT NULL,
CONSTRAINT Constraint1 PRIMARY KEY (column1),
... )
Begränsningen NOT NULL är visserligen onödig, eftersom en primärnyckel inte kan anta
NULL värden, men den orsakar heller ingen skada.
Enligt SQL standarden borde alla tabeller ha en primärnyckel. Men det kan faktiskt finnas
tillfällen när en primärnyckel är helt onödig, som till exempel för en temporär tabell
innehållande endast en rad. I sådana fall är skapandet av en primärnyckel mera kostsamt
än vad man får ut av den. Den kolumn som blir utsedd till primärnyckel skall alltid vara
först i CREATE TABLE skriptet för att databashanteraren skall kunna dra full nytta utav
att känna primärnyckeln.
I en resonabel värld vore det möjligt att definiera primära och främmande nycklar efter att
tabellerna har skapats. Att skapa främmande nycklar efter att tabellerna är skapade löser
problemet med cykliska referenser, som man ibland kan råka ut för. Skriptet i exemplet
nedan är inte möjligt att utföra, eftersom tabellerna refererar till varandra och oavsett
vilken av dem som skapas först, så är ändå referenstabellen i det ögonblicket oskapad.
CREATE TABLE Table1 (
column1 INTEGER PRIMARY KEY,
column2 INTEGER FOREIGN KEY REFERENCES Table2)
CREATE TABLE Table2 (
column1 INTEGER PRIMARY KEY,
column2 INTEGER FOREIGN KEY REFERENCES Table1)
Lösningen till problemet med cykliska referenser är att addera alla begränsningar först
efter att tabellerna har skapats. Följande skript löser problemet på ett elegant sätt:
CREATE TABLE Table1 (
column1 INTEGER,
column2 INTEGER)
CREATE TABLE Table2 (
column2 INTEGER,
column1 INTEGER)
ALTER TABLE Table1
ADD CONSTRAINT Constraint1 PRIMARY KEY (column1)
ALTER TABLE Table2
ADD CONSTRAINT Constraint2 PRIMARY KEY (column2)
ALTER TABLE Table1
ADD CONSTRAINT Constraint3
FOREIGN KEY (column2) REFERENCES Table2
Sida 67 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
ALTER TABLE Table2
ADD CONSTRAINT Constraint4
FOREIGN KEY (column1) REFERENCES Table1
Inte nog med att ovanstående skript är en elegant och legal lösning till problemet med
cykliska referenser, så beskriver CREATE-then-ALTER modellen problemet på ett
mycket tydligare sätt. Efter att ha läst detta tydliga skript råder ingen som helst tvivel på
vilka referenser och begränsningar som existerar. Kolumner är en del av en tabell, därför
skall de skapas med CREATE TABLE satsen. Begränsningar är inte en del av en tabell,
därför borde de definieras skilt.
Ett problem finns dock med att införa primära nycklar efter att tabellerna har skapats. När
en tabell skapas och en kolumn samtidigt definieras som primärnyckel, kommer
databashanteraren att upptäcka detta och skapa ett index eller ett grupperat index och
eventuellt utse primära nyckeln till grupperingsnyckel. Om primära nyckeln påförs en
tabell i ett senare skede, inträffar inte detta. Databashanteraren måste veta vid det
ögonblick när tabellen skapas vilken kolumn som är primärnyckel för att ett index
automatiskt skall skapas för denne. Dessutom kommer valet av indextyp att påverka
lagringsstrukturen hos databasen. Därför är det för sent att lägga till eller ta bort en
primärnyckel efter att tabellen är skapad. Primärnycklar bör vara noga genomtänkta på
förhand samt även beakta prestandafrågor.
Alla databashanterare kommer alltså helt underförstått att skapa ett index när de ser en
PRIMARY KEY begränsning i en CREATE TABLE sats. Den bästa primärnyckeln och
det bästa indexet uppnås om primärnyckeln endast består av en enda kolumn och denna
kolumn dessutom är av datatypen INTEGER. Primärnyckeln får absolut inte bestå av en
FLOAT, eftersom två FLOAT värden kan se ut att vara olika fastän de inte är det, vilket
skulle leda till att unikhetskontrollen misslyckas.
För en enkel tabell, som inte innehå ller någon sorts naturlig unikhet, är den bästa
primärnyckeln en INTEGER kolumn med stigande värden. Seriella sekvenser som denna
kan orsaka så kallade ”heta punkter”. Heta punkter är en sida i ett index eller i en datafil
som alla samtidigt vill komma åt. Om flera användare samtidigt sätter in data i en tabell
med seriell primärnyckel, är det mycket troligt att alla vill ha åtkomst till samma sida i
indexet. Eftersom primärnyckeln oftast stegas upp med ett i gången, så kommer alla poster
som skrivs in under en kort tid att ligga bredvid varandra, åtminstone i indexet (eftersom
ett index är sorterat). Detta orsakar att andra användare får vänta så länge som den ena
användaren har låst den sida i indexet.
I ett system med många användare och där nya poster konsekvent skrivs in, är det bättre
att använda primärnyckelvärden som för varje post avviker så mycket som möjligt från
andra posters värden – speciellt från de poster som för närvarandet eller inom en snar
framtid kommer att skrivas in. Dessa nycklar kan genereras utgående från exempelvis en
klocka med millisekundsprecision eller från användarens sessionsnyckel. Huvudsaken är
att nyckeln blir så olik den förra varje gång en ny nyckel genereras. Nycklar av den här
typen lagras vanligtvis som CHAR(12) eller längre.
Sida 68 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
10.6. UNIQUE
Unika begränsningar kan införas på samma sätt som primärnyckelsbegränsningar. De kan
med andra ord införas i CREATE TABLE och ALTER TABLE satser. Alla
databastillverkare stöder unika begränsningar som inte är primärnycklar, men IBM,
Informix, Ingres, InterBase, Microsoft och Sybase har en besynnerlig begränsning – de
tillåter inte två NULL värden i en unik kolumn. Dessa tillverkare tycks inte förstå att
NULL inte är lika med NULL. Det borde vara tillåtet att ha hur många NULL värden som
helst i en unik kolumn eller i en främmande nyckel, eftersom data kan vara okänt eller
ospecificerat i början – som när cykliska referenser används.
Till skillnad för några år sedan, går det oftast idag bra att utföra följande operation:
UPDATE Table1
SET unique_column = unique_column + 1
Den unika kolumnen innehåller en sekventiell serie av värdena ett till sex. För att utföra
UPDATE satsen kommer databashanteraren först att uppdatera den första raden i
Table1. Om denna rad innehåller en etta så blir det uppdaterade värdet en tvåa. Därefter
kontrollerar databashanteraren det unika indexet och märker att en tvåa redan existerar i
kolumnen. Här avbryter fortfarande en del databashanterare. Hemligheten bakom att
utföra en sådan här operation är den att först måste alla rader uppdateras, därefter skall det
unika indexet kontrolleras för värden som strider mot den unika begränsningen. Indexet
skall alltså inte kontrolleras för varje rad som uppdateras, utan först efter att alla rader
blivit uppdaterade. Samma princip gäller även för PRIMARY KEY begränsningar,
eftersom PRIMARY KEY är en form av UNIQUE.
Om en kolumn av naturliga skäl innehåller unika data, definiera den då också som
UNIQUE så att databashanteraren vet om det! Regelbaserade förfrågningsoptimerare ger
företräde till unika index, eftersom de har en perfekt selektivitet.
10.7. Triggningar
Triggningar är små block av kod som utförs när en viss händelse inträffar i databasen.
Triggningar är speciellt användbara för att till exempel logga en händelse eller för att
returnera ett användarspecifikt felmeddelande. Triggningar har dock en stor nackdel – de
är väldigt långsamma. Följande exempel visar hur CHECK begränsningen i SQL satsen
kan skrivas om som en trigger:
CREATE TABLE Table1 (
column1 SMALLINT,
CHECK (column1 BETWEEN 1 AND 10)
... )
Sida 69 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
CREATE TRIGGER Trigger1
AFTER INSERT ON Table1
REFERENCING NEW ROW AS New
FOR EACH ROW
BEGIN
IF
New.column1 NOT BETWEEN 1 AND 10
THEN
SIGNAL SQLSTATE = ‘23000’
END IF
END
Den deklarativa begränsningen CHECK är betydligt snabbare än om motsvarande uppgift
utförs med en trigger. Orsaken till triggningarnas långsamhet ligger i deras strikta
definition. SQL standarden specificerar följande steg i en triggningsoperation:
A trigger must:
Make a savepoint.
For each row: {
Perform ”before each row” trigger code.
Copy the row as it exists before update, to a BEFORE IMAGE.
Update the row.
Copy the row as it exists after update, to an AFTER IMAGE.
}
At the end of statement (after constraints are checked): {
For each row: {
Perform “after each row” trigger code.
If (fatal error) restore to savepoint.
}
Cleanup.
}
Triggningar utför alltså två stycken “for each row” upprepningar. Idag stöds triggningar
endast för INSERT, UPDATE och DELETE händelser (förutom Informix som också
stöder SELECT triggning). Triggningar skall alltså så långt som möjligt undvikas – de är
alldeles för långsamma. Men om hastighet inte är det primära målet så kan
triggningsfunktioner erbjuda flexibla lösningar.
10.8. Inaktivering av begränsningar
DEFER
Det finns tre olika sätt att undvika att en begränsningskontroll inträffar: DEFER,
DISABLE och DROP. DEFER innebär att begränsningen tillfälligt inaktiveras, under
exempelvis utförandet av en viss SQL sats. För att begränsningar skall vara möjliga att
kringgå, måste de alltid vara namngivna och speciellt deklarerade som DEFERRABLE,
som i följande exempel:
Sida 70 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
CREATE TABLE Table1 (
column1 SMALLINT,
...
CONSTRAINT Constraint1 CHECK (
column1 < 1000) DEFERRABLE INITIALLY DEFERRED)
En begränsning definierad som DEFERRABLE tillåter att användaren kan bestämma när
kontrollen skall utföras. Valen är efter att SQL satsen har utförts eller efter att hela
transaktionen har utförts. Nyckelordet INITIALLY DEFERRED garanterar att
begränsningen är inaktiverad ända tills den manuellt aktiveras eller tills transaktionen tar
slut. Begränsningen kan när som helst aktiveras med följande kommando:
SET CONSTRAINTS ALL IMMEDIATE
Tillfällig inaktivering av begränsningskontroller är endast möjliga att utföra inne i en
transaktion. Genast när COMMIT utförs kommer databashanteraren att börja kontrollera
begränsningarna. Genom att tillfälligt kunna inaktivera begränsningar så kan man få
kontroll på när begränsningskontrollerna utförs, vilket verkligen är ett behov om cykliska
referenser ingår i tabelldefinitionen, eller om det finns misstankar om att transaktionen
kanske hur som helst måste avbrytas med ROLLBACK.
DISABLE
Ett icke-standardiserat sätt att kringgå en begränsning är att stänga av begränsningen.
Följande kod är Oracle specifik:
ALTER TABLE ...
DISABLE <constraint list>
Denna avstängning kan inträffa även utanför en transaktion. Att stänga av begränsningar
är användbart vid inladdning av större mängder data, eftersom det är effektivare att göra
1000 stycken INSERT utan kontroller och utföra kontrollerna när alla data är insatt, än att
utföra en INSERT följd av en kontroll - tusen gånger. Även vid utförandet av en
bulkkopiering från en replikerande databas eller vid återskapning av en databas, är det
onödigt att kontrollera begränsningar eftersom de redan är utförda på den data som skrivs
in. Det finns i alla fall mycket goda grunder för att tro att inga extra kontroller behövs.
Kom dock ihåg att om en begränsning av typen PRIMARY KEY eller UNIQUE stängs av,
så kommer heller inte deras index att uppdateras.
Begränsningar i Microsoft kan stängas av med följande kod:
ALTER TABLE ... NOCHECK CONSTRAINT
Inaktivering och avstängning av begränsningar kan användas för att finjustera
transaktioner och stora datakopieringar. När begränsningarna namnges kan de kontrolleras
i vilken ordning de utförs. Om den begränsning som har största sannolikheten för att
misslyckas (data hittas som strider mot begränsningen), så kan hela processen avbrytas i
ett tidiga re skede utan att övriga kontroller behöver utföras. På så sätt kan en hel del
exekveringstid inbesparas.
Sida 71 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
DROP
Ett sätt att kringgå begränsningar, dock ett klumpigt sätt, är att helt radera begränsningen.
Följande exempel raderar en begränsning och är SQL standard:
ALTER TABLE Table1
DROP CONSTRAINT Constraint1 CASCADE
Precis som vid avstängningen av begränsningar, hjälper även detta till att snabba upp stora
kopieringar av data, återskapandet av databaser samt stora mängder av INSERT satser.
DROP har även samma nackdelar som de övriga: Index för PRIMARY KEY och
UNIQUE begränsningar kommer inte att uppdateras och blir osynkroniserade. Men om
begränsningar inte går att inaktivera, så går de alltid att radera. Och de begränsningar som
är raderade kan skapas på nytt när insättningarna är klara (varefter även indexet kommer
att uppdateras).
10.9. Onödiga begränsningar i SELECT satser
Ibland kan det hända att man inför en begränsning i en förfrågan utan att märka att den
kanske är onödig. Om till exempel Table1 i exemplet nedan har en sammansatt
primärnyckel av (column1, column2) eller om kolumnerna är definierade som
UNIQUE, så är DISTINCT onödigt:
SELECT DISTINCT column1, column2 FROM Table1
När alla kolumner som ingår i en primärnyckel eller en unik begränsning finns med i
förfrågningens selectlista, och inga kopplingar av flera tabeller görs eller om den enda
kopplingen är en PRIMARY KEY till FOREIGN KEY – så är DISTINCT är överflödig
begränsning. Prestandan i ovanstående uttryck kan höjas genom att ta bort DISTINCT.
I vissa situationer kan DISTINCT ändå behövas:
− Om begränsningen är inaktiverbar (begränsningen kan ha inaktiverats tidigare i samma
transaktion).
− Om det finns en möjlighet att begränsningen kan stängas av eller raderas i framtiden.
− Om det finns en möjlighet att förfrågningen kan returnera dubbletter av rader,
innehållande NULL värden.
11. Lagrade Procedurer (Stored Procedures)
11.1. Bakgrund
Lagrade procedurer är något som både är lagrat (den är ett databasobjekt) och
procedurmässigt (den kan innehålla flödeskontroll som IF, WHILE, BEGIN, END). Som
en procedur i vilket programmeringsspråk som helst, så kan en lagrad procedur acceptera
parametrar, deklarera och använda variabler och returnera värden. En lagrad procedur kan
innehålla flera SQL satser, inklusive SELECT, vilket ger den möjlighet att returnera
resultat. Följande exempel visar syntaxen på en lagrad procedur:
Sida 72 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
CREATE PROCEDURE Sp_proc1
(param1 INT)
MODIFIES SQL DATA
BEGIN
DECLARE num1 INT;
IF param1 <> 0 THEN
SET param1 = 1;
END IF;
UPDATE Table1
SET column1 = param1;
END
Determinism
En funktion är deterministisk om den alltid ger samma resultat utgående från samma data.
För exempel, SQL funktionen UPPER är deterministisk om UPPER(’i’) alltid returnerar
’I’. Observera att fastän funktionen endast har en parameter, så är parametrarna trots allt
två stycken: själva bokstaven ’i’ och teckentabellen. Om en annan teckentabell hade varit
vald skulle kanske resultatet ha blivit ett annat.
En funktion är inte deterministisk om det finns en chans att den kan returnera olika resultat
varje gång den körs – trots att indata är det samma. Följande funktion är inte
deterministisk:
CREATE FUNCTION Sp_non_deterministic()
RETURNS INTEGER
BEGIN
IF CURRENT_TIME = TIME ’11:00:00’ THEN RETURN 1;
ELSE RETURN 2;
END IF
END
Funktionen Sp_non_deterministic kommer att returnera olika resultat beroende på
ett värde som inte är känt innan exekveringen av funktionen har inletts, nämligen tiden.
Icke deterministiska funktioner är inte bra och borde inte användas i lagrade procedurer.
11.2. Fördelar med Lagrade Procedurer
Det finns många fördelar med att använda lagrade procedurer. Ett par av de huvudsakliga
fördelarna är följande:
1. Proceduren finns färdigt på servern och utförs på servern vilket betyder att inga
meddelanden måste skickas mellan klienten och servern under tiden som proceduren
utförs.
2. Procedurer tolkas och förkompileras en enda gång och resultatet lagras på servern. Det
finns alltså inget behov av kompilering inför varje exekvering.
Det är även lättare att utföra sådana operationer som har ett mål som omfattar en hel
process med funktioner, som dessutom skall utföras i en given ordning. I sådana
situationer underlättar flödeskontrollen en hel del.
Sida 73 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Mindre trafik
Lagrade procedurer innebär en mindre mängd trafik mellan klienten och servern. Klienten
måste skicka någon sorts initialiseringsdata för att starta proceduren, och proceduren
behöver returnera något sorts svar till klienten, men det är allt som behöver skickas. Inga
meddelanden behöver utbytas mellan klienten och servern under tiden som proceduren
utförs. En lagrad procedur som behandlar [n] antal element behöver endast två
meddelanden, medan en ODBC applikation som består av [n] antal element behöver
(2 * n) antal meddelanden. Det är en signifikant skillnad på dessa två, eftersom varje
meddelande tar relativt sett ganska lång tid på sig att nå servern eller klienten.
Lagrade procedurer är inte det enda sättet att minska trafiken. Ibland kan liknande effekter
uppnås genom att införa begränsningar eller genom att använda vyer. Dessutom behöver
inte alla nätverksapplikationer skicka så här många meddelanden för att utföra en viss
funktion; funktionerna kan även vara CGI-program som anropas av applikationsservern.
Förkompilering
Lagrade procedurer är kompilerade på förhand. Det betyder att ingen kompilering behövs
när proceduren anropas. Därför kan de starta nästan omedelbart. Om dessutom samma
procedur nyligen kördes, kan det hända att koden för att utföra den fortfarande finns kvar i
någon buffert.
Det är bra att ha riktig data i tabellerna innan proceduren anropas för första gången. Om
proceduren innehåller förfrågningar som kan utnyttja index skall man se till att även
indexen existerar innan proceduren anropas första gången, och om proceduren i normala
fall tar en parameter skall även en parameter ges vid första anropet. Allt detta är viktigt
inför den första körningen, eftersom databashanteraren gör en strategisk plan för hur
proceduren skall utföras, på basen av den första körningen. Det finns även speciella
kommandon för att påtvinga omkompilering av en lagrad procedur. En omstart av servern
är även en mycket effektiv metod för detta.
12. Dataförändringar
12.1. Bakgrund
Med dataförändringar avses alla sådana operationer som orsakar en förändring av
posternas värde i en databas. De vanligaste operationerna är INSERT, UPDATE och
DELETE. Dataförändringar inträffar mera sällsynt än dataförfrågningar, SELECT. Jämför
följande två SQL satser:
SELECT column1 FROM Table1
WHERE column1 = 12345
UPDATE Table1
SET column2 = 10
WHERE column1 = 12345
Sida 74 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Bägge satserna måste gå igenom samma procedur för att finna den korrekta positionen för
den matchande raden. Ändå kommer UPDATE satsen att vara långsammare på grund av
följande orsaker:
−
−
−
−
Uppdateringen måste använda en exklusiv låsning istället för en mera tolerant låsning.
Uppdateringen måste kontrollera eventuella begränsningar och triggningar.
Uppdateringen måste addera åtminstone en, möjligtvis två, nya rader i loggfilen.
Uppdateringen måste eventuellt skifta ner eller upp efterföljande rader om
uppdateringen orsakar en förändring av radens storlek. En förstorad rad kan även
behövas flyttas ut till en annan sida.
− Uppdateringen måste radera och byta ut en eventuell indexnyckel för den uppdaterade
kolumnen.
− Om en senare COMMIT inträffar, orsakar denna diskaktivitet.
Att uppdatera en rad tar åtminstone tre gånger så länge som att hämta en rad, och i den
värsta tänkbara situationen kan den ta upp till 100 gånger längre tid.
12.2. Loggningar
Vissa databashanterare erbjuder en möjlighet att stänga av alla loggningsfunktioner. Det är
ändå väldigt sällan som en loggningsfunktion stängs av för att öka prestanda, så man kan
utgå från att loggning inträffar när en dataförändring inträffar.
Loggning inträffar innan en dataförändring blir permanent. Loggar kan användas för att
reparera en databas om något skulle inträffa. Därför måste loggningen ske innan
förändringen blir permanent, annars kan de hända att den sista förändringen inte finns med
i loggen.
Vad som skrivs in i loggfilen är olika för nästan alla databashanterare. Vissa skriver enbart
den SQL sats som orsakade dataförändringen, medan andra även spara en total kopia av
posten före uppdatering samt dessutom en eventuell indexnyckel för den posten - ganska
mycket diskaktivitet. Trots den höga diskaktiviteten loggning medför, så går loggningar
oftast väldigt snabbt. De är sekventiella skrivningar och oftast utförs de av en parallell
process som kan ha en loggningskö, och loggen kan skrivas i takt med belastningen på
databasservern. Dessutom kan loggfilerna ligga på en fysiskt skild enhet, oftast en separat
hårddisk, så att ingen tävlan om hårddisken uppstår mellan databaserna och loggningen.
Loggningar är en stor orsak till att dataförändringar är långsamma. Loggninge n kan tryggt
tillfälligt inaktiveras vid följande situationer:
− Under tiden som en bulkkopiering, eller en inladdning av data, utförs – eftersom det
redan finns en kopia av den data som skrivs in.
− När en indexuppdatering behöver utföras. Indexnycklar kan alltid härledas från de
riktiga posterna.
Sida 75 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
12.3. INSERT
Enligt SQL standard skrivs INSERT på följande sätt:
INSERT INTO <Table> [ ( <column> [,...] ) ]
<query expression>
Här följer två exempel på giltiga INSERT satser:
INSERT INTO Table1 (column1)
VALUES(’Kalles Kaviar’)
INSERT INTO Table1 (column1)
SELECT column1 FROM Table2
Om en kolumn förekommer ofta, och oftast antar samma värde, kan lite nätverkstrafik
sparas genom att göra det värdet till ett förhandsinställt värde:
CREATE TABLE Table1 (
column1 VARCHAR(40) DEFAULT ’Kalles Kaviar’
... )
De förhandsinställda värdena kan skrivas in på en ny rad i tabellen med följande
kommando:
INSERT INTO Table1 DEFAULT VALUES
Det här tricket förkortar den totala exekveringstiden om den förhandsinställda kolumnen
oftast är mer än hundra tecken lång.
Det spelar inte egentligen någon roll i vilken ordning kolumnerna räknas upp i en INSERT
sats, men vissa databashanterare visar en tendens att utföra operationen snabbare om den
primära nyckeln och unika kolumner kommer först i listan. Om flera INSERT operationer
utförs direkt efter varandra kan de utföras snabbare om de utförs i ordning enligt en
indexnyckel, det ökar sannolikheten för att den indexsida som behövs finns i en buffert.
Prestandan kan alltid höjas om fler värden med samma INSERT operation kan skrivas in
samtidigt. Enligt SQL standard kan en bulkladdning utföras på följande sätt:
INSERT INTO Table1
(column1, column2, column3, column4, column5)
VALUES (1, 2, 3, 4, 5),
(2, 3, 4, 5, 6),
(3, 4, 5, 6, 7)
Tyvärr är det endast IBM och MySQL som stöder denna SQL standardmetod för att skriva
in större mängder data. Microsoft (och ett par andra tillverkare) erbjuder ett synnerligen
intressant sätt att optimera flera INSERT:
Sida 76 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
BEGIN
INSERT INTO Table1 VALUES (1, 2, 3, 4, 5);
INSERT INTO Table1 VALUES (2, 3, 4, 5, 6);
INSERT INTO Table1 VALUES (3, 4, 5, 6, 7);
END
Efter att ha utfört en stor bulkkopiering av ny data in i databasen är det alltid bäst att köra
databasen statistikuppdaterare så att den känner till den nya situationen.
12.4. UPDATE
Enligt SQL standard skrivs UPDATE på följande sätt:
UPDATE <Table> SET
{ <column> = <column expression> [,...] |
ROW = <row expression> }
[ WHERE <search condition> ]
En laglig UPDATE kan se ut så här:
UPDATE Table1 SET
column1 = 1,
column2 = 2,
column3 = 3
WHERE column1 <> 1
OR column2 <> 2
OR column3 <> 3
Exemplet ovan uppdaterar tre kolumner samtidigt med samma SET sats, vilket är bättre än
att utföra tre skilda UPDATE satser – en för varje kolumn. Användningen av tre skilda
UPDATE satser skulle minska behovet av låsningar, men samtidigt öka behovet av
loggningar. Loggningar kan ändå anses som långsammare än låsningar.
När man uppdatera flera kolumner med samma SET sats så sker uppdateringarna från
vänster till höger (i exemplet ovanifrån nedåt). Det lönar sig alltså att sätta den kolumn
som har den största sannolikheten för att uppdateringen misslyckas (kanske på grund av
någon begränsning) först i listan. Om column3 i exemplet nedan har den största
sannolikheten för att misslyckas, så skall den placeras enligt följande:
UPDATE Table1 SET
column3 = 3,
column1 = 1,
column2 = 2
WHERE column1 <> 1
OR column2 <> 2
OR column3 <> 3
Sida 77 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
Om alla rader i en tabell skall uppdateras, med ett par olika värden beroende på ett villkor
mot en annan kolumn, så kan en UPDATE som utnyttjar satsvis bearbetning underlätta:
UPDATE Table1
SET column2 = ’X’
WHERE column1 < 100
UPDATE Table1
SET column2 = ’Y’
WHERE column1 >= 100
OR column1 IS NULL
Exemplet ovan visar en sådan situation där alla rader drabbas av uppdatering.
Nedanstående SQL uttryck utför exakta samma operation, men betydligt snabbare:
UPDATE Table1
SET column2 =
CASE WHEN column1 < 100 THEN ’X’
ELSE ’Y’
END
12.5. DELETE
Enligt SQL standard skrivs DELETE på följande sätt:
DELETE FROM <Table name>
[ WHERE <search condition> ]
En laglig DELETE kan se ut så här:
DELETE FROM Table1
WHERE column1 = 55
Om alla rader i en tabell skall raderas, är det effektivare att radera hela tabellen och skapa
den på nytt. Vissa databastillverkare erbjuder även specialverktyg för att tömma en hel
tabell. Till exempel så tillåter Microsoft, Informix och Sybase följande kommando att
utföras:
TRUNCATE TABLE <Table name>
Fördelen med dessa specialkommandon är att de inte orsakar loggning eller triggningar.
Inte heller försvinner begränsningar, index eller optimeringsstatistik som i normala fall
raderas när en tabell slängs bort.
12.6. COMMIT och ROLLBACK
Efter att ha utfört en COMMIT operation måste man kunna lita på att all data är skriven på
hårddisken. Det betyder att det borde vara tryggt att knycka ur sladden till servern efter att
COMMIT utförts. Tyvärr så innebär utförandet av en COMMIT operation en kamp mot
operativsystemet, eftersom operativsystem vanligtvis utför ”lata” skrivningar och håller en
massa information i buffertar tills det är bekvämt att skriva ner dem på hårddisken. Därför
kommer COMMIT alltid att vara en långsam operation.
Sida 78 / 79
Finjustering och optimering av SQL databaser
Krister Karlström
02.04.2003
De flesta databashanterare har en icke standardiserad flagga för att indikera om COMMIT
utförs automatiskt efter varje dataförändringsoperation eller om programmeraren själv
manuellt måste utföra COMMIT. Denna flagga kan utnyttjas för att optimera
dataföränd ringsoperationer i följande situationer:
− Om transaktionen endast innehåller en enda dataförändringsoperation skall autocommit flaggan vara på.
− Om transaktionen innehåller multipla dataförändringsoperationer skall auto-commit
flaggan vara av. När operatio nerna är slutförda utförs en manuell COMMIT.
− Automatisk COMMIT är förhandsinställt när man använder ODBC eller JDBC API.
När man skriver transaktioner är skall de skrivas så att de är mest lämpliga för den
situation som troligtvis uppstår, d.v.s. kommer COMMIT att utföras eller kommer
ROLLBACK att behöva utföras. Om transaktionen troligtvis alltid lyckas skall den
optimeras för att ROLLBACK troligtvis aldrig behöver utföras. Om det finns en stor risk
för att en ROLLBACK operation behöver utföras, och denna operation är tung och
kostsam, så skall transaktionen börja med att utföra de moment som har största
sannolikheten för att orsaka en ROLLBACK. På så vis kan transaktionen avbrytas i ett
tidigt skede när det ännu inte finns så mycket som behöver ångras.
13. Källor
Detta arbete baserar sig ganska långt på ett sammandrag av följande enastående bok:
Peter Gulutzan, Trudy Pelzer: SQL Performance Tuning, Addison-Wesley 2003
Från denna bok är de flesta teorier, tester, påståenden och exempel hämtade. De tester so m
har utförts i boken, är alla utförda på de åtta största databastillverkarnas produkter, och
således borde de förslag till optimeringar och förbättringar som ges i detta arbete gälla för
samtliga SQL produkter.
Enstaka detaljer samt en del syntaxer och standarder har hämtats ur följande bok:
Connolly, Begg, Strachan: Database Systems, Second Edition, Addison-Wesley 1999
Sida 79 / 79