PA152: Efektivní využívání DB
9. Ladění dotazů
Vlastislav Dohnal
PA152, Vlastislav Dohnal, FI MUNI, 2012 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, 2012 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, 2012 4
Příklad statistik PostgreSQL
 Tabulka hotel
PA152, Vlastislav Dohnal, FI MUNI, 2012 5
Příklad statistik PostgreSQL
 Atribut hotel.id
 Atribut hotel.name
PA152, Vlastislav Dohnal, FI MUNI, 2012 6
Příklad statistik PostgreSQL
 Atribut hotel.state
 Atribut hotel.distance_to_center
PA152, Vlastislav Dohnal, FI MUNI, 2012 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, 2012 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, 2012 9
Monitorování dotazů
 Možnosti, jak objevit pomalý dotaz:
Vyžaduje příliš mnoho přístupů na disk
 Tj. costs v plánu (explain) jsou vysoké
 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, 2012 10
Ladění dotazu
 Lokální změna
První přístup ke zrychlení dotazu
Přepsání dotazu ovlivní pouze daný dotaz
 Globální změna
Vytvoření indexu
Změna schéma
Rozdělení transakcí
…
PA152, Vlastislav Dohnal, FI MUNI, 2012 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, 2012 12
Přepisování dotazů
 Techniky
Použití indexů
Rušení nadbytečných DISTINCT
(Korelované) poddotazy
Dočasné tabulky
Používání HAVING
Používání pohledů (VIEW)
Uložené pohledy (materialized views)
PA152, Vlastislav Dohnal, FI MUNI, 2012 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, 2012 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, 2012 15
Rušení nadbytečných DISTINCT
 Dotaz:
Vypiš čísla ssnum všech zaměstnanců
nějakého 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, 2012 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, 2012 17
Rušení nadbytečných DISTINCT
 Vztah mezi DISTINCT, primárními klíči a
spojeními lze popsat:
Definice (privilegovanost):
 Relace T je privilegovaná, pokud atributy vracené
příkazem SELECT obsahují její primární klíč.
Definice (záviset na):
 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, 2012 18
Rušení nadbytečných DISTINCT
 Tvrzení:
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, 2012 19
Nadbytečný DISTINCT (1)
 Dotaz:
SELECT DISTINCT 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, 2012 20
Nadbytečný DISTINCT (2)
 Dotaz:
SELECT DISTINCT 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, 2012 21
Nadbytečný DISTINCT (3)
 Dotaz:
SELECT DISTINCT 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ý.
Přepisování vnořených dotazů
 Důvod:
Většina optimalizátorů dotazů nefunguje
správně na některých vnořených dotazech
Typy těchto problematických dotazů
 Nekorelované dotazy s agregační funkcí
 Nekorelované dotazy bez agregační funkce
 Korelované s agregační funkcí
 Korelované bez agregační funkce
PA152, Vlastislav Dohnal, FI MUNI, 2012 22
PA152, Vlastislav Dohnal, FI MUNI, 2012 23
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, 2012 24
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, 2012 25
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, 2012 26
Přepsání nekorelovaných 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, 2012 27
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 = tech.dept
AND e2.dept = e1.dept);
PA152, Vlastislav Dohnal, FI MUNI, 2012 28
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, 2012 29
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, 2012 30
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, 2012 31
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, 2012 32
Přepisování dotazů
 Techniky
Použití indexů
Rušení nadbytečných DISTINCT
(Korelované) poddotazy
Dočasné tabulky
Používání HAVING
Používání pohledů (VIEW)
Uložené pohledy (materialized views)
PA152, Vlastislav Dohnal, FI MUNI, 2012 33
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 v employee
 Optimalizátor dotazů takový index na temp nemá.
PA152, Vlastislav Dohnal, FI MUNI, 2012 34
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, 2012 35
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, 2012 36
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, 2012 37
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, 2012 38
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, 2012 39
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, 2012 40
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, 2012 41
Přepisování dotazů: výkonnostní vliv
-10
0
10
20
30
40
50
60
70
80
Throughputratio(%)
SQLServer 2000
Oracle 8i
DB2 V7.1
100k zaměstnanců, 100k studentů, 10 tech. oddělení
>10 000
PA152, Vlastislav Dohnal, FI MUNI, 2012 42
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, 2012 43
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, 2012 44
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, 2012 45
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, 2012 46
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, 2012 47
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, 2012 48
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, 2012 49
Uložené (materializované) pohledy
 Příklad
SQLServer, implementováno pomocí triggerů
1m objednávek – 5 obchodů, 20 výrobců
10k položek
- 62.2
21900
31900
-5000
0
5000
10000
15000
20000
25000
30000
35000
insert vendor total purchaser total
gain with aggregate maintenance (%)
PA152, Vlastislav Dohnal, FI MUNI, 2012 50
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, 2012 51
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, 2012 52
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, 2012 53
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, 2012 54
Vytváření indexů
 Sekvenční čtení tabulky (table scan / seq scan)
Všechny záznamy jsou kontrolovány
 pomalé
 Vytvoření indexu (index scan)
Zrychlí SELECT
Zpomalí INSERT, UPDATE, DELETE
 Index se musí aktualizovat
PA152, Vlastislav Dohnal, FI MUNI, 2012 55
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, 2012 56
Vliv indexu na náklady: příklad
 Relace
 StarsIn(movieTitle, movieYear, starName)
 Qmovies
 SELECT movieTitle, movieYear FROM StarsIn
WHERE starName=‘name’;
 Qstars
 SELECT starName FROM StarsIn
WHERE movieTitle=‘title’ AND movieYear=year;
 Insert
 INSERT INTO StarsIn VALUES (‘title’, year, ‘name’);
PA152, Vlastislav Dohnal, FI MUNI, 2012 57
Vliv indexu na náklady: příklad
 Předpoklady:
 B(StarsIn) = 10 bloků
 Každý herec 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
 Prohledání indexu
 1 čtení
 Vkládání do relace
 1 čtení a 1 zápis bloku
 Tj. nehledáme volný blok (jak s indexem, tak bez)
PA152, Vlastislav Dohnal, FI MUNI, 2012 58
Vliv indexu na náklady: pří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, 2012 59
Optimalizace indexů
1. Stanovit dávku příkazů
 Tj. způsob vytížení
 Analýzou logů zjistit typy dotazů a 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, 2012 60
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
 používej jej v dalších iteracích
 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) & the best 10-year paper in 2007!
 Oracle 10g (http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php)
PA152, Vlastislav Dohnal, FI MUNI, 2012 61
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, 2012 62
Referenční integrita
 Dotaz
 Bez indexu (výstup z EXPLAIN SELECT…)
 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 room_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 room_hotel_id_fkey (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, 2012 63
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
Index s reverzním klíčem
 Specialita Oracle
 Zvýšení průchodnosti indexu
počet vkládání / aktualizací za čas
 Idea:
Hodnoty klíče v indexu používat reverzně
 hodnoty ze sekvencí jsou rozptýleny
 Např. 12345 a 12346  54321 a 64321
 nižší kolize při souběžné aktualizaci indexu
 CREATE INDEX idx ON tab(attr) REVERSE;
PA152, Vlastislav Dohnal, FI MUNI, 2012 64
PA152, Vlastislav Dohnal, FI MUNI, 2012 65
Globální změny
 Vytvoření indexu
 Změna schéma
Viz další přednáška
 Rozdělení relací
Viz další přednáška