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