PA152: Efektivní využívání DB
9. Ladění dotazů
Vlastislav Dohnal
PA152, Vlastislav Dohnal, FI MUNI, 2009 2
Poděkování
Zdrojem materiálů tohoto předmětu jsou:
Přednášky CS245, CS345, CS345
Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer
Widom
Stanford University, California
Database Tuning (slides)
Dennis Shasha, Philippe Bonnet
Morgan Kaufmann, 1st edition, 440 pages, 2002
ISBN-13: 978-1558607538
http://www.databasetuning.org/
PA152, Vlastislav Dohnal, FI MUNI, 2009 3
Příklad statistik PostgreSQL
Připojte se k fakultní DB PostgreSQL
Návod viz první přednáška
Ve schématu xdohnal jsou tabulky
predmet, skupina, hotel
Statistiky jak na relacích, tak i atributech.
Významy jednotlivých polí
http://www.postgresql.org/docs/8.1/interactive/view-pg-stats.html
PA152, Vlastislav Dohnal, FI MUNI, 2009 4
Příklad statistik PostgreSQL
Tabulka hotel
PA152, Vlastislav Dohnal, FI MUNI, 2009 5
Příklad statistik PostgreSQL
Atribut hotel.id
Atribut hotel.name
PA152, Vlastislav Dohnal, FI MUNI, 2009 6
Příklad statistik PostgreSQL
Atribut hotel.state
Atribut hotel.distance_to_center
PA152, Vlastislav Dohnal, FI MUNI, 2009 7
Ladění dotazů
SELECT s.RESTAURANT_NAME, t.TABLE_SEATING, to_char(t.DATE_TIME,'Dy, Mon FMDD') AS THEDATE,
to_char(t.DATE_TIME,'HH:MI PM') AS THETIME,to_char(t.DISCOUNT,'99') || '%' AS AMOUNTVALUE,t.TABLE_ID,
s.SUPPLIER_ID, t.DATE_TIME, to_number(to_char(t.DATE_TIME,'SSSSS')) AS SORTTIME
FROM TABLES_AVAILABLE t, SUPPLIER_INFO s,
(SELECT s.SUPPLIER_ID, t.TABLE_SEATING, t.DATE_TIME, max(t.DISCOUNT) AMOUNT, t.OFFER_TYPE
FROM TABLES_AVAILABLE t, SUPPLIER_INFO
WHERE t.SUPPLIER_ID = s.SUPPLIER_ID
and (TO_CHAR(t.DATE_TIME, 'MM/DD/YYYY') != TO_CHAR(sysdate, 'MM/DD/YYYY')
or TO_NUMBER(TO_CHAR(sysdate, 'SSSSS')) < s.NOTIFICATION_TIME - s.TZ_OFFSET)
and t.NUM_OFFERS > 0 and t.DATE_TIME > SYSDATE and s.CITY = 'SF'
and t.TABLE_SEATING = '2' and t.DATE_TIME between sysdate and (sysdate + 7)
and to_number(to_char(t.DATE_TIME, 'SSSSS')) between 39600 and 82800
and t.OFFER_TYPE = 'Discount`
GROUP BY s.SUPPLIER_ID, t.TABLE_SEATING, t.DATE_TIME, t.OFFER_TYP) u
WHERE t.SUPPLIER_ID=s.SUPPLIER_ID and u.SUPPLIER_ID=s.SUPPLIER_ID and t.SUPPLIER_ID=u.SUPPLIER_ID
and t.TABLE_SEATING = u.TABLE_SEATING and t.DATE_TIME = u.DATE_TIME
and t.DISCOUNT = u.AMOUNT and t.OFFER_TYPE = u.OFFER_TYPE
and (TO_CHAR(t.DATE_TIME, 'MM/DD/YYYY') != TO_CHAR(sysdate, 'MM/DD/YYYY')
or TO_NUMBER(TO_CHAR(sysdate, 'SSSSS')) < s.NOTIFICATION_TIME - s.TZ_OFFSET)
and t.NUM_OFFERS > 2 and t.DATE_TIME > SYSDATE and s.CITY = 'SF'
and t.TABLE_SEATING = '2' and t.DATE_TIME between sysdate and (sysdate + 7)
and to_number(to_char(t.DATE_TIME, 'SSSSS')) between 39600 and 82800 and t.OFFER_TYPE = 'Discount'
ORDER BY AMOUNTVALUE DESC, t.TABLE_SEATING ASC, upper(s.RESTAURANT_NAME) ASC,
SORTTIME ASC, t.DATE_TIME ASC
Provedení je příliš pomalé ...
1) Jak je dotaz vyhodnocován?
2) Jak jej lze urychlit?
PA152, Vlastislav Dohnal, FI MUNI, 2009 8
Plán dotazu
Výstup příkazu EXPLAIN v Oracle
Operátor
Přístupová metoda Cena provedení
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=165 Card=1 Bytes=106)
1 0 SORT (ORDER BY) (Cost=165 Card=1 Bytes=106)
2 1 NESTED LOOPS (Cost=164 Card=1 Bytes=106)
3 2 NESTED LOOPS (Cost=155 Card=1 Bytes=83)
4 3 TABLE ACCESS (FULL) OF 'TABLES_AVAILABLE' (Cost=72 Card=1 Bytes=28)
5 3 VIEW
6 5 SORT (GROUP BY) (Cost=83 Card=1 Bytes=34)
7 6 NESTED LOOPS (Cost=81 Card=1 Bytes=34)
8 7 TABLE ACCESS (FULL) OF 'TABLES_AVAILABLE' (Cost=72 Card=1 Bytes=24)
9 7 TABLE ACCESS (FULL) OF 'SUPPLIER_INFO' (Cost=9 Card=20 Bytes=200)
10 2 TABLE ACCESS (FULL) OF 'SUPPLIER_INFO' (Cost=9 Card=20 Bytes=460)
PA152, Vlastislav Dohnal, FI MUNI, 2009 9
Monitorování dotazů
Možnosti, jak objevit pomalý dotaz:
Vyžaduje příliš mnoho přístupů na disk
Např. dotaz na přesnou shodu používá table-scan.
Nevhodný plán dotazu
Vhodné (existující) indexy nejsou použity
Databáze umožňují logování ,,dlouhých"
dotazů
PA152, Vlastislav Dohnal, FI MUNI, 2009 10
Ladění dotazu
První přístup ke zrychlení jsou lokální
změny
Globální změna
Vytvoření indexu
Změna schéma
Rozdělení transakcí
...
Lokální změna
Přepsání dotazu ovlivní pouze daný dotaz
PA152, Vlastislav Dohnal, FI MUNI, 2009 11
Přepisování dotazů
Příklad:
Employee(ssnum, name, manager, dept,
salary, numfriends)
Shlukovaný index na ssnum
Tj. určuje uspořádání souboru
Neshlukované indexy: (i) name a (ii) dept
Student(ssnum, name, degree_sought, year)
Shlukovaný index na ssnum
Neshlukovaný index na name
Tech(dept, manager, location)
Shlukovaný index na dept
PA152, Vlastislav Dohnal, FI MUNI, 2009 12
Přepisování dotazů
Techniky
Použití indexů
Rušení nadbytečných DISTINCT
(Korelované) poddotazy
Dočasné tabulky
Podmínky spojení
Používání HAVING
Používání pohledů (VIEW)
Uložené pohledy (materialized views)
PA152, Vlastislav Dohnal, FI MUNI, 2009 13
Používání indexů
Optimalizace dotazů nemusí použít index
pokud jsou používány:
Aritmetické výrazy
WHERE salary/12 >= 4000;
Podřetězce
SELECT * FROM employee
WHERE SUBSTR(name, 1, 1) = `G';
Porovnávání atributů různých datových typů
Víceatributové indexy
Porovnání na NULL
PA152, Vlastislav Dohnal, FI MUNI, 2009 14
Rušení nadbytečných DISTINCT
Dotaz:
Najdi zaměstnance pracující v oddělení
informační systémy. Ve výsledku nechme
duplicity.
SELECT DISTINCT ssnum
FROM employee
WHERE dept = `information systems'
DISTINCT není nutný
ssnum je primární klíč v employee
PA152, Vlastislav Dohnal, FI MUNI, 2009 15
Rušení nadbytečných DISTINCT
Dotaz:
Vypiš čísla ssnum všech zaměstnanců z
technického oddělení. Ve výsledku nechme
opakování.
SELECT DISTINCT ssnum
FROM employee, tech
WHERE employee.dept = tech.dept
Je DISTINCT nutný?
PA152, Vlastislav Dohnal, FI MUNI, 2009 16
Rušení nadbytečných DISTINCT
Dotaz:
SELECT DISTINCT ssnum
FROM employee, tech
WHERE employee.dept = tech.dept
Je DISTINCT nutný?
ssnum je primární klíč v employee
dept je primární klíč v tech
 každý zaměstnanec se spojí s nejvýše
jedním záznamem z relace tech.
 DISTINCT není potřeba
PA152, Vlastislav Dohnal, FI MUNI, 2009 17
Rušení nadbytečných DISTINCT
Vztah mezi DISTINCT, primárními klíči a
spojeními lze popsat:
Relace T je privilegovaná, pokud atributy
vracené příkazem SELECT obsahují primární
klíč.
Nechť R není privilegovaná relace.
Když R je spojena s relací S podle rovnosti
primárního klíče R a nějakého atribut(ů) z S,
pak R je závislá na S.
Relace ,,záviset na" je tranzitivní:
R1 závisí na R2 a R2 závisí na R3,
pak R1 závisí na R3.
PA152, Vlastislav Dohnal, FI MUNI, 2009 18
Rušení nadbytečných DISTINCT
Ve výsledku příkazu SELECT nebudou
duplicity (bez DISTINCT), pokud platí
alespoň jedno z:
Každá relace ve FROM je privilegovaná.
Každá neprivilegovaná relace závisí na
nějaké privilegované.
PA152, Vlastislav Dohnal, FI MUNI, 2009 19
Nadbytečný DISTINCT (1)
Dotaz:
SELECT ssnum
FROM employee, tech
WHERE employee.manager = tech.manager
Employee je privilegovaná
Je tech privilegovaná?
Ne.
Závisí tech na employee?
Ne, protože atribut manager není primárním
klíčem tech.
PA152, Vlastislav Dohnal, FI MUNI, 2009 20
Nadbytečný DISTINCT (2)
Dotaz:
SELECT ssnum, tech.dept
FROM employee, tech
WHERE employee.manager = tech.manager
Employee je privilegovaná
Je tech privilegovaná?
Ano.
Výsledky se neopakují
PA152, Vlastislav Dohnal, FI MUNI, 2009 21
Nadbytečný DISTINCT (3)
Dotaz:
SELECT student.ssnum
FROM student, employee, tech
WHERE student.name = employee.name
AND employee.dept = tech.dept;
Student je privilegovaná
Employee není privilegovaná a nezávisí
na žádné z ostatních relací.
 DISTINCT je nutný.
PA152, Vlastislav Dohnal, FI MUNI, 2009 22
Typy vnořených dotazů
Nekorelované dotazy s agregační funkcí
uvnitř
SELECT ssnum FROM employee
WHERE salary > (SELECT avg(salary) FROM
employee)
Nekorelované dotazy bez agregační
funkce
SELECT ssnum FROM employee
WHERE dept in (SELECT dept FROM tech)
PA152, Vlastislav Dohnal, FI MUNI, 2009 23
Typy vnořených dotazů
Korelované s agregační funkcí
SELECT ssnum FROM employee e1
WHERE salary =
(SELECT avg(e2.salary)
FROM employee e2, tech
WHERE e2.dept = e1.dept
AND e2.dept = tech.dept)
Korelované bez agregační funkce
Neobvyklé (resp. lze napsat pomocí spojení)
PA152, Vlastislav Dohnal, FI MUNI, 2009 24
Přepsání nekorelovaných dotazů bez agregace
1. Relace z obou FROM dej dohromady
2. IN nahraď rovností (=)
3. Vybírané atributy se nemění
SELECT ssnum FROM employee
WHERE dept in (select dept from tech)
SELECT ssnum
FROM employee, tech
WHERE employee.dept = tech.dept
PA152, Vlastislav Dohnal, FI MUNI, 2009 25
Přepsání nekorelovaný dotazů bez agregace
Problém s duplicitami:
SELECT avg(salary) FROM employee
WHERE manager in (select manager from tech)
SELECT avg(salary) FROM employee, tech
WHERE employee.manager = tech.manager
Druhý dotaz může vracet zaměstnance
vícekrát
Pokud stejný manažer vede více oddělení.
Řešením je pomocná tabulka
Kde pomocí DISTINCT eliminujeme duplicity.
PA152, Vlastislav Dohnal, FI MUNI, 2009 26
Přepsání korelovaných dotazů
Dotaz:
Najdi zaměstnance technických oddělení,
kteří vydělávají průměrnou mzdu svého
oddělení.
SELECT ssnum
FROM employee e1
WHERE salary = (SELECT avg(e2.salary
FROM employee e2, tech
WHERE e2.dept = e1.dept
AND e2.dept = tech.dept);
PA152, Vlastislav Dohnal, FI MUNI, 2009 27
Přepsání korelovaných dotazů
INSERT INTO temp
SELECT avg(salary) as avsalary, employee.dept
FROM employee, tech
WHERE employee.dept = tech.dept
GROUP BY employee.dept;
SELECT ssnum
FROM employee, temp
WHERE salary = avsalary
AND employee.dept = temp.dept
PA152, Vlastislav Dohnal, FI MUNI, 2009 28
Přepsání korelovaných dotazů
Dotaz:
Najdi zaměstnance technických oddělení,
kteří mají stejně kamarádů jako kolegů ve
svém oddělení.
SELECT ssnum
FROM employee e1
WHERE numfriends = COUNT(
SELECT e2.ssnum
FROM employee e2, tech
WHERE e2.dept = tech.dept
AND e2.dept = e1.dept);
PA152, Vlastislav Dohnal, FI MUNI, 2009 29
Přepsání korelovaných dotazů
INSERT INTO temp
SELECT COUNT(ssnum) as numcolleagues,
employee.dept
FROM employee, tech
WHERE employee.dept = tech.dept
GROUP BY employee.dept;
SELECT ssnum
FROM employee, temp
WHERE numfriends = numcolleagues
AND employee.dept = temp.dept;
Vznikl zde problém v COUNT?
PA152, Vlastislav Dohnal, FI MUNI, 2009 30
Problém v COUNT?
Příklad:
Helena nepracuje v technickém oddělení.
V původním dotazu by se její přátelé
porovnávali s COUNT()=0. V případě, že
Helena nemá přátele, zůstane ve výběru.
V přepsaném dotazu by se záznam Heleny ve
výsledku neobjevil.
Pomocná tabulka bude obsahovat pouze počty pro
technická oddělení.
Toto je omezení při přepisování
korelovaných dotazů s COUNT.
PA152, Vlastislav Dohnal, FI MUNI, 2009 31
Používání pomocných tabulek
Dotaz:
Pro zaměstnance oddělení informačních systémů,
kteří mají plat > 40000, vypiš jejich číslo ssnum a
umístění.
INSERT INTO temp
SELECT *
FROM employee
WHERE salary >= 40000
SELECT ssnum, location
FROM temp
WHERE temp.dept = `information systems'
Toto řešení nebude optimální
Nelze využít index na dept
Optimalizátor dotazů takový index na temp nemá.
PA152, Vlastislav Dohnal, FI MUNI, 2009 32
Používání HAVING
Důvod zavedení
Zkrácení dotazů, které filtrují podle výsledku
agregačních funkcí
Ve WHERE nelze použít agregační funkci
V klauzuli HAVING ano
Příklad
SELECT avg(salary), dept
FROM employee
GROUP BY dept
HAVING avg(salary) > 10 000;
PA152, Vlastislav Dohnal, FI MUNI, 2009 33
Používání HAVING
Jiný příklad
SELECT avg(salary), dept
FROM employee
GROUP BY dept
HAVING count(ssnum) > 100;
PA152, Vlastislav Dohnal, FI MUNI, 2009 34
Používání HAVING
Nepoužívat HAVING
Pokud lze zapsat ve WHERE.
SELECT avg(salary) as avgsalary, dept
FROM employee
WHERE dept= `information systems'
GROUP BY dept;
SELECT avg(salary) as avgsalary, dept
FROM employee
GROUP BY dept
HAVING dept = `information systems';
PA152, Vlastislav Dohnal, FI MUNI, 2009 35
Používání pohledů
Optimalizátor dotazů provede
nahrazení pohledu jeho definicí
CREATE VIEW techlocation AS
SELECT ssnum, tech.dept, location
FROM employee, tech
WHERE employee.dept = tech.dept;
SELECT location
FROM techlocation
WHERE ssnum = 43253265;
PA152, Vlastislav Dohnal, FI MUNI, 2009 36
Používání pohledů
Výsledkem dostaneme:
SELECT location
FROM employee, tech
WHERE employee.dept = tech.dept
AND ssnum = 43253265;
PA152, Vlastislav Dohnal, FI MUNI, 2009 37
Používání pohledů
Příklad v PostgreSQL:
CREATE VIEW hotels_in_city AS
SELECT city, COUNT(*) AS count
FROM hotel
GROUP BY city;
Použití pohledu
SELECT * FROM hotels_in_city
WHERE count > 8;
PA152, Vlastislav Dohnal, FI MUNI, 2009 38
Používání pohledů
Příkaz EXPLAIN
EXPLAIN SELECT * FROM hotels_in_city;
EXPLAIN SELECT * FROM hotels_in_city
WHERE count > 8;
Porovnejte s
EXPLAIN SELECT city, COUNT(*)
FROM hotel GROUP BY city
HAVING COUNT(*) > 8;
PA152, Vlastislav Dohnal, FI MUNI, 2009 39
Přepisování dotazů: výkonnostní vliv
>10000
-10
0
10
20
30
40
50
60
70
80
distinct
subqueries
correlated
subquery
join
and
num
eric
attribute
join
and
clustered
index
having
view
Throughputratio
SQLServer 2000
Oracle 8i
DB2 V7.1
100k zaměstnanců, 100k studentů, 10 tech. oddělení
PA152, Vlastislav Dohnal, FI MUNI, 2009 40
Optimalizace agregačních funkcí
Příklad:
Evidence objednávek obchodního řetězce
Order(ordernum, itemnum, quantity, purchaser,
vendor)
Item(itemnum, description, price)
Shlukované indexy nad itemnum pro Order a Item
Každých 5 minut se provádí dotazy:
Celková cena objednaného zboží jistého výrobce
(vendor).
Celková cena objednaného zboží nějakým
obchodem (purchaser).
PA152, Vlastislav Dohnal, FI MUNI, 2009 41
Optimalizace agregačních funkcí
Dotazy:
SELECT vendor, sum(quantity*price)
FROM order, item
WHERE order.itemnum = item.itemnum
GROUP BY vendor;
SELECT purchaser, sum(quantity*price)
FROM order, item
WHERE order.itemnum = item.itemnum
GROUP BY purchaser;
Cena dotazů?
 jsou drahé
PA152, Vlastislav Dohnal, FI MUNI, 2009 42
Optimalizace agregačních funkcí
Jak zrychlit?
Definice pohledů?
 nepomůže
Ukládat výsledky do pomocných tabulek?
 pomůže
PA152, Vlastislav Dohnal, FI MUNI, 2009 43
Optimalizace agregačních funkcí
Vytvoříme tabulky
OrdersByVendor(vendor, amount)
OrdersByPurchaser(purchaser, amount)
Tabulky se musí aktualizovat
Kdy aktualizovat?
Po každé změně order, popř. item?
Realizovat pomocí triggerů (spouští)
Periodicky po určitém čase znovu vytvořit
Náklady na aktualizaci
Musí být menší než náklady na původní dotazy.
PA152, Vlastislav Dohnal, FI MUNI, 2009 44
Uložené (materializované) pohledy
Výsledek pohledu je uložený v tabulce
Automatická aktualizace databází
Obvykle...
Použití i v dotazech, které daný pohled
nepoužívají
Optimalizátor dotazů přepisuje dotaz
PA152, Vlastislav Dohnal, FI MUNI, 2009 45
Uložené (materializované) pohledy
Např. Oracle
CREATE MATERIALIZED VIEW
OrdersByVendor
BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT vendor, sum(quantity*price) AS amount
FROM order, item
WHERE order.itemnum = item.itemnum
GROUP BY vendor;
PA152, Vlastislav Dohnal, FI MUNI, 2009 46
Uložené (materializované) pohledy
Příklad
QUERY REWRITE
Dotaz:
SELECT vendor, sum(quantity*price) AS amount
FROM order, item
WHERE order.itemnum = item.itemnum
AND vendor=`Apple'
GROUP BY vendor;
Použije se pohled OrdersByVendor
SELECT vendor, amount FROM OrdersByVendor
WHERE vendor=`Apple';
PA152, Vlastislav Dohnal, FI MUNI, 2009 47
Uložené (materializované) pohledy
Příklad
SQLServer, implementováno pomocí triggerů
1m objednávek ­ 5 obchodů, 20 výrobců
10k položek
gain with aggregate maintenance (%)
31900
21900
- 62.2
-5000
0
5000
10000
15000
20000
25000
30000
35000
insert vendor total store total
PA152, Vlastislav Dohnal, FI MUNI, 2009 48
Databázové spouště (triggers)
Spoušť je uložené procedura
Kód používající SQL příkazy prováděný při
výskytu nějaké události.
Události:
DML ­ insert, update, delete
Časové (nebývá časté)
DDL ­ definice tabulek, ...
PA152, Vlastislav Dohnal, FI MUNI, 2009 49
Databázové spouště (triggers)
Nezávislé na aplikaci
Protože jsou prováděny samotným DB
serverem.
Bez spouští musí být vše řešeno aplikací
Přinášejí dodatečné náklady
Mohou vkládat do dalších tabulek, ...
Spouštění omezovat podmínkami
Např. při aktualizaci ceny, počtu objednaných
položek
Ne při aktualizaci popisu položky, ...
PA152, Vlastislav Dohnal, FI MUNI, 2009 50
Globální změny
Vytvoření indexu
Změna schéma
Viz další přednáška
Rozdělení relací
Viz další přednáška
...
PA152, Vlastislav Dohnal, FI MUNI, 2009 51
Používání indexů
Malá tabulka
Indexy jsou vytvořeny
Přesto nejsou používány
Příklad
predmet(kod, nazev, kredity)
SELECT COUNT(*) FROM predmet;
Výsledek 3
SELECT * FROM predmet
WHERE kod=`MA102';
Použije se table-scan.
PA152, Vlastislav Dohnal, FI MUNI, 2009 52
Vytváření indexů
Sekvenční čtení tabulky
Všechny záznamy jsou kontrolovány
 pomalé
Vytvoření indexu
Zrychlí SELECT
Zpomalí INSERT, UPDATE, DELETE
Index se musí aktualizovat
PA152, Vlastislav Dohnal, FI MUNI, 2009 53
Vliv indexu na náklady
Neplatí:
Čím více indexů, tím rychlejší zpracování!
Teoreticky platné pouze pro SELECT.
Každý index zpomaluje aktualizace
Nutné aktualizovat kromě relace i index
Pozor:
INSERT INTO tabulka SELECT ...
DELETE FROM tabulka WHERE ...
PA152, Vlastislav Dohnal, FI MUNI, 2009 54
Příklad odhadu nákladů
Relace
StarsIn(movieTitle, movieYear, starName)
Qmovies
SELECT movieTitle, movieYear FROM StarsIn WHERE
starName=`jméno';
Qstars
SELECT starName FROM StarsIn WHERE
movieTitle=`název' AND movieYear=`rok';
Insert
INSERT INTO StarsIn VALUES (`název', `rok', `jméno');
PA152, Vlastislav Dohnal, FI MUNI, 2009 55
Příklad odhadu nákladů
Předpoklady:
B(StarsIn) = 10 bloků
Každá hvězda hraje průměrně ve 3 filmech.
Každý film má průměrně 3 hvězdy.
Relace není nijak uspořádaná.
Pokud je index, pak 3 čtení z disku.
Aktualizace indexu ­ 1 čtení a 1 zápis bloku
Vkládání do relace ­ 1 čtení a 1 zápis bloku
I bez indexu nehledáme volný blok.
PA152, Vlastislav Dohnal, FI MUNI, 2009 56
Příklad odhadu nákladů
Počty čtení a zápisů pro jednotlivé situace
Pravděpodobnost provádění operací
Qmovies=p1, Qstars=p2, Insert=1 - p1 - p2
Situace1: p1 = p2 = 0.1  bez indexů
Situace2: p1 = p2 = 0.4  oba indexy
Akce Žádný
index
Index
starName
Index
movieTitle, movieYear
Oba indexy
Qmovies 10 4 10 4
Qstars 10 10 4 4
Insert 2 4 4 6
Prům.
náklady
2 + 8p1 + 8p2 4 + 6p2 4 + 6p1 6 - 2p1 - 2p2
PA152, Vlastislav Dohnal, FI MUNI, 2009 57
Optimalizace indexů
1. Stanovit dávku příkazů
Tj. způsob vytížení
Analýzou logů zjistit typy dotazů, aktualizací
a jejich četnosti
2. Navrhnout různé indexy
Optimalizátor nechat odhadnou cenu
vyhodnocení dávky příkazů
Vybrat konfiguraci s nejmenší cenou
3. Vytvořit indexy, které minimalizují cenu
PA152, Vlastislav Dohnal, FI MUNI, 2009 58
Optimalizace indexů
Ad bod 2
Mám sadu možných indexů
Začni bez indexů
Opakuj
Pro každý navrhovaný index, vypočítej cenu
Vytvoř index s nejvyšším vylepšením ceny
A používej jej v další iteraci
Opakuj, dokud byl nějaký index vytvořený.
Celý proces lze dělat i automaticky
MS AutoAdmin (http://research.microsoft.com/en-us/projects/autoadmin/default.aspx)
MS Index Tuning Wizard (S. Chaudhuri, V. Narasayya: An efficient, Cost-Driven
Index Selection Tool for Microsoft SQL Server. Proceedings of VLDB Conference, 1997)
Oracle 10g (http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php)
PA152, Vlastislav Dohnal, FI MUNI, 2009 59
Referenční integrita
Vytvoření cizího klíče neznamená index
na atributech
Příklad v PostgreSQL (db.fi.muni.cz)
Hotel ­ primární klíč id
Room ­ primární klíč id, cizí klíč hotel_id
V(Room, hotel_id) = 6
Dotazy (zajímá nás výsledek EXPLAIN)
SELECT * FROM hotel WHERE id=2;
SELECT * FROM room WHERE hotel_id=2 AND number=1;
PA152, Vlastislav Dohnal, FI MUNI, 2009 60
Referenční integrita
Dotaz
Bez indexu
Vytvoříme index nad hotel_id
Seq Scan on room (cost=0.00..6741.89 rows=103 width=22)
Filter: ((hotel_id = 2) AND (number = 1))
CREATE INDEX hotel_id_fkey ON room (hotel_id);
Bitmap Heap Scan on room (cost=981.00..3784.38 rows=103 width=22)
Recheck Cond: (hotel_id = 2)
Filter: (number = 1)
-> Bitmap Index Scan on hotel_id_fket (cost=0.00..980.97 rows=52892 width=0)
Index Cond: (hotel_id = 2)
SELECT * FROM room WHERE hotel_id=2 AND number=1;
PA152, Vlastislav Dohnal, FI MUNI, 2009 61
Referenční integrita
Cizí klíče mohou velmi zpomalit i mazání
Příklad:
DELETE FROM hotel WHERE id=500;
Cizí klíč v room odkazuje na tabulku hotel
Při mazání se musí v tabulce room
kontrolovat přítomnost záznamů hotel_id=500
Doporučení
Vytvářet na cizích klíčích indexy