Databasteknik II: Inlämningsuppgift 3 - Frågeoptimering i Mimer

Mål

• Att förstå vad exekveringsplaner är, och hur de relaterar till SQL-frågor.
• Att förstå en del av principerna för frågeoptimering.
• Att få erfarenhet av hur en riktig databashanterare optimerar frågor.

Uppgift

(I Mimer fungerar "SET EXPLAIN ON" och "SET EXECUTE OFF" inte i dbVisualizer, utan man måste använda Batch SQL.)

Läs i respektive manual om vad förklaringarna betyder. Experimentera med olika frågor, tabeller och index, och försök förstå hur databashanteraren optimerar frågorna.

Exempel på frågeställningar som man kan fundera över och experimentera med:

• Tar optimeraren hänsyn till index? Hur kan man se det?
• Tar optimeraren hänsyn till antalet rader i tabellerna?
• Kan man se på exekveringsplanerna om det är en kostnadsbaserad eller en heuristisk optimerare?
• Kan det bli olika exekveringsplaner beroende på vilka konstanter man har i frågan? I så fall, varför?
• Hur optimeras frågor med and, or och not?
• Hur optimeras frågor med order by?
• Hur används sammansatta index (dvs ett enda index på flera kolumner)?
• Väljer optimeraren ibland att inte gå via ett index, trots att det vore möjligt? I så fall, varför?
  Ledtrådar:
  • Är det skillnad på hur index används med frågor som innehåller en likhetsjämförelse (=), en storleksjämförelse (< eller >) respektive en olikhet (<> eller !=)?
  • Hur hanteras olika typer av like-frågor, till exempel namn like 'Olle%' och namn like '%Olle'?
• Spelar det någon roll hur man formulerar frågorna?
• Vilka olika join-algoritmer används, och hur väljer optimeraren mellan dem?
• När används temporära tabeller, och varför?
• Finns det någon fråga där optimeraren misslyckas, dvs genererar en mycket sämre exekveringsplan än den borde kunna? Och i så fall, kan man hjälpa optimeraren på något sätt med den frågan?

Lästips

• Thomas Padron-McCarthy och Tore Risch: Databasteknik, andra upplagan, kapitel 26 (kapitel 25 i första upplagan)
• Ramez Elmasri och Shamkant B. Navathe: Fundamentals of Database Systems, 7th edition, kapitel 18 och 19
• Peter Gulutzan, Trudy Pelzer: SQL Performance Tuning, Addison Wesley, 2002. ISBN 0-201-79169-2. 592 sidor. Man brukar få lära sig att det inte spelar någon roll hur man formulerar sina SQL-frågor, för databashanterarens frågeoptimerare räknar ut det snabbaste sättet att köra frågan innan den kör den. Men i verkligheten är frågeoptimerare inte perfekta, och den här boken beskriver hur olika sätt att skriva samma SQL-fråga kan ge väldigt olika prestanda.

• Mimer SQL - Documentation Set Version 11.0
• Läs i manualen om kommandona "SET EXPLAIN ON", "SET EXECUTE OFF" och "UPDATE STATISTICS"
• Om exekveringsplaner i Mimer 10 och tidigare: Mimer SQL Engine Explain
• Om exekveringsplaner i Mimer 11: Explain for Mimer SQL 11
• Mimer SQL 10.0 Technical Description
• En annan sorts "optimering" som inte är vad den här uppgiften handlar om: Optimizing Transactions

Några exempeltabeller

Om man vill provköra på sin egen dator behöver man även datafilen: fyll-tabeller.zip. Man kan behöva fylla tabellerna först och skapa indexen sen, för annars kan Mimer ge ett fel. Glöm inte kommandot update statistics sist!

Obs! Det verkar som att med den nya Mimer-klienten för Windows (med Mimer 10.1) så får man inte se frågeplanerna från den lite äldre Mimer-servern som jag kör (10.0). Det funkar med Linux-klienten, och det funkar om man kör mot en lokal databas på Windows. Om man gör labben på en egen Windows-dator, är det nog enklast att skapa en lokal databas och skapa tabellerna där, enligt ovan.

• Tabellen exempel.personer innehåller 100000 rader (10⁵), och har inga index.
• Tabellen exempel.kunder innehåller 10000 rader (10⁴), och har index på alla kolumner.
• Tabellen exempel.personal innehåller också 10000 rader (10⁴), men har index på id, personalnummer, namn och gatuadress.

** här nedan betyder att kolumnen har ett primärindex.
* betyder att kolumnen har ett sekundärindex.
Ett understruket kolumnnamn betyder att den kolumnen är deklarerad som unik (antingen med primary key eller med unique).

exempel.personer

exempel.kunder

exempel.personal

Enkla sökningar går snabbt i alla tabellerna, även på oindexerade kolumner i tabellen exempel.personer, men joinar mellan två oindexerade kolumner kan bli mycket långsamma om det ingår många rader i båda av joinens operander.

Redovisning

Arbeta i grupper om en eller två studenter. I undantagsfall kan man arbeta i grupper om tre, men fråga läraren först.

Det är tillåtet att samarbeta i större grupper än så, men varje grupp om 1-3 studenter måste fortfarande redovisa separat, och det måste också tydligt framgå (i rapporten eller på annat sätt) vilka som deltog i samarbetet.