PA152: Efektivní využívání DB
9. Ladění dotazů
Vlastislav Dohnal
PA152, Vlastislav Dohnal, FI MUNI, 2018 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, 2018 3
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 s
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, 2018 4
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, 2018 5
Monitorování dotazů
◼ Co je pomalý dotaz?
Vyžaduje příliš mnoho přístupů na disk
◼ vysoké costs v plánu (explain)
◼ Např. dotaz na přesnou shodu používá table-scan.
Nevhodný plán dotazu
◼ Vhodné (existující) indexy nejsou použity
◼ Jak je objevit?
Databáze umožňují logování „dlouhých“
dotazů
…
PA152, Vlastislav Dohnal, FI MUNI, 2018 6
Ladění dotazu
◼ Lokální změna = přepsání dotazu
První přístup ke zrychlení dotazu
Ovlivní pouze daný dotaz
◼ Globální změna
Vytvoření indexu
Změna schématu
Rozdělení transakcí
…
PA152, Vlastislav Dohnal, FI MUNI, 2018 7
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; (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, 2018 8
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, 2018 9
Používání indexů
◼ Optimalizátor dotazů nemusí použít index,
pokud jsou používány:
 Aritmetické výrazy
WHERE salary/12 >= 4000;
WHERE inserted + 1 = current_date;
 Funkce
SELECT * FROM employee
WHERE SUBSTR(name, 1, 1) = ‘G’;
… WHERE to_char(inserted, 'YYYYMM') = '201704'
 Porovnávání atributů různých datových typů
 Víceatributové indexy
 Porovnání na NULL
Používání indexů (pokr.)
 Agregační funkce MAX(A), MIN(A)
◼ resp. ORDER BY A LIMIT 1
◼ použití funkcí v A
◼ Např.
 conn_log ( log_key, sim_imsi, time, car_key, pda_imei,
gsmnet_id, method, program_ver )
A. SELECT max(time AT TIME ZONE 'UTC') AS time
FROM conn_log
WHERE sim_imsi=‘23001234567890123’ AND
time> '2016-02-28 10:50:00.122 UTC' AND
method='U' AND program_ver IS NOT NULL;
B. SELECT time AT TIME ZONE 'UTC‘
FROM (SELECT max(time) AS time
FROM conn_log
WHERE sim_imsi=‘23001234567890123’ AND
time>'2016-02-28 10:50:00.122 UTC' AND
method='U' AND program_ver IS NOT NULL) AS x;
C. SELECT max(time) AT TIME ZONE 'UTC' AS time …
(cont. from query A.)
PA152, Vlastislav Dohnal, FI MUNI, 2018 10
Plus a secondary index on
(sim_imsi,time)
Používání indexů (pokr.)
PA152, Vlastislav Dohnal, FI MUNI, 2018 11
QUERY PLAN (QUERY A.)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=19412.69..19412.70 rows=1 width=8) (actual time=36.415..36.415 rows=1 loops=1)
-> Append (cost=0.00..19385.45 rows=5448 width=8) (actual time=36.410..36.410 rows=0 loops=1)
-> Seq Scan on conn_log (cost=0.00..0.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((program_ver IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone) AND (sim_imsi = '23001234567890123'::bpchar) AND
(method = 'U'::bpchar))
-> Index Scan using conn_log_imsi_time_y2016m02 on conn_log_y2016m02 (cost=0.56..8.58 rows=1 width=8) (actual time=28.464..28.464 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Bitmap Heap Scan on conn_log_y2016m03 (cost=194.11..14125.36 rows=3969 width=8) (actual time=2.586..2.586 rows=0 loops=1)
Recheck Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Bitmap Index Scan on conn_log_imsi_time_y2016m03 (cost=0.00..193.12 rows=4056 width=0) (actual time=2.584..2.584 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
-> Bitmap Heap Scan on conn_log_y2016m04 (cost=71.87..5243.35 rows=1476 width=8) (actual time=5.346..5.346 rows=0 loops=1)
Recheck Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Bitmap Index Scan on conn_log_imsi_time_y2016m04 (cost=0.00..71.50 rows=1507 width=0) (actual time=5.342..5.342 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
-> Index Scan using conn_log_imsi_time_y2016m05 on conn_log_y2016m05 (cost=0.14..8.16 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
Planning time: 4.159 ms
Execution time: 36.535 ms
Používání indexů (pokr.)
PA152, Vlastislav Dohnal, FI MUNI, 2018 12
QUERY PLAN (QUERY B.)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on x (cost=5.98..6.01 rows=1 width=8) (actual time=0.162..0.163 rows=1 loops=1)
-> Result (cost=5.98..5.99 rows=1 width=0) (actual time=0.159..0.160 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=1.87..5.98 rows=1 width=8) (actual time=0.158..0.158 rows=0 loops=1)
-> Merge Append (cost=1.87..22424.61 rows=5449 width=8) (actual time=0.156..0.156 rows=0 loops=1)
Sort Key: conn_log."time"
-> Index Scan Backward using conn_log_imsi_time on conn_log (cost=0.12..8.15 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m02 on conn_log_y2016m02 (cost=0.56..8.58 rows=1 width=8)
(actual time=0.069..0.069 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m03 on conn_log_y2016m03 (cost=0.56..16225.91 rows=3969 width=8)
(actual time=0.046..0.046 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m04 on conn_log_y2016m04 (cost=0.43..6033.60 rows=1477 width=8)
(actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m05 on conn_log_y2016m05 (cost=0.14..8.17 rows=1 width=8)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
Planning time: 3.137 ms
Execution time: 0.317 ms
Používání indexů (pokr.)
PA152, Vlastislav Dohnal, FI MUNI, 2018 13
QUERY PLAN (QUERY C.)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=5.98..5.99 rows=1 width=0) (actual time=0.186..0.186 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=1.87..5.98 rows=1 width=8) (actual time=0.182..0.182 rows=0 loops=1)
-> Merge Append (cost=1.87..22424.63 rows=5450 width=8) (actual time=0.181..0.181 rows=0 loops=1)
Sort Key: conn_log."time"
-> Index Scan Backward using conn_log_imsi_time on conn_log (cost=0.12..8.15 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m02 on conn_log_y2016m02 (cost=0.56..8.58 rows=1 width=8)
(actual time=0.070..0.070 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m03 on conn_log_y2016m03 (cost=0.56..16225.91 rows=3969 width=8)
(actual time=0.064..0.064 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m04 on conn_log_y2016m04 (cost=0.43..6033.60 rows=1478 width=8)
(actual time=0.037..0.037 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
-> Index Scan Backward using conn_log_imsi_time_y2016m05 on conn_log_y2016m05 (cost=0.14..8.17 rows=1 width=8)
(actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((sim_imsi = '23001234567890123'::bpchar) AND ("time" IS NOT NULL) AND ("time" > '2016-02-28 11:50:00.122+01'::timestamp with time zone))
Filter: ((program_ver IS NOT NULL) AND (method = 'U'::bpchar))
Planning time: 3.094 ms
Execution time: 0.309 ms
PA152, Vlastislav Dohnal, FI MUNI, 2018 14
Rušení nadbytečných DISTINCT
◼ Dotaz:
Najdi zaměstnance pracující v oddělení
informační systémy. Ve výsledku nechceme
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, 2018 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 nechceme opakování.
SELECT DISTINCT ssnum
FROM employee, tech
WHERE employee.dept = tech.dept
◼ Je DISTINCT nutný?
Employee(ssnum, name, manager, dept, salary, numfriends)
Tech(dept, manager, location)
PA152, Vlastislav Dohnal, FI MUNI, 2018 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
Employee(ssnum, name, manager, dept, salary, numfriends)
Tech(dept, manager, location)
PA152, Vlastislav Dohnal, FI MUNI, 2018 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 vrácené
příkazem SELECT obsahují její primární klíč.
Definice relace „záviset na“
◼ Nechť R není privilegovaná relace.
◼ Když R je spojena s relací S podle rovnosti
primárního klíče R a odpovídajících atributu(ů) z S,
pak R je závislá na S. (např. S má cizí klíč do R)
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, 2018 18
Rušení nadbytečných DISTINCT
◼ Tvrzení:
Ve výsledku příkazu SELECT nebudou
duplicity (bez DISTINCT),
pokud pro každou relaci ve FROM platí
alespoň jedno z:
◼ relace je privilegovaná.
◼ relace závisí na nějaké privilegované.
PA152, Vlastislav Dohnal, FI MUNI, 2018 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.
Employee(ssnum, name, manager, dept, salary, numfriends)
Tech(dept, manager, location)
PA152, Vlastislav Dohnal, FI MUNI, 2018 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í
Employee(ssnum, name, manager, dept, salary, numfriends)
Tech(dept, manager, location)
PA152, Vlastislav Dohnal, FI MUNI, 2018 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ý.
Employee(ssnum, name, manager, dept, salary, numfriends)
Student(ssnum, name, degree_sought, year)
Tech(dept, manager, location)
Vnořené dotazy
◼ Příkaz SELECT obsahující další SELECT jako svoji
část
 SELECT employee_number, name
FROM employees AS X
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = X.department );
 SELECT employee_number, name,
(SELECT AVG(salary) FROM employees
WHERE department = X.department ) AS department_average
FROM employees AS X;
PA152, Vlastislav Dohnal, FI MUNI, 2018 22
Přepisování vnořených dotazů
◼ Důvod:
Optimalizátor dotazů nemusí vždy správně
fungovat na některých vnořených dotazech
Typicky:
◼ Nekorelované dotazy bez agregační funkce
◼ Korelované dotazy
PA152, Vlastislav Dohnal, FI MUNI, 2018 23
PA152, Vlastislav Dohnal, FI MUNI, 2018 24
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)
◼ Tzv. semi-join
PA152, Vlastislav Dohnal, FI MUNI, 2018 25
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)
PA152, Vlastislav Dohnal, FI MUNI, 2018 26
Typy vnořených dotazů
◼ Korelované bez agregační funkce
Neobvyklé (resp. lze napsat pomocí spojení)
◼ Semi-join dotazy mohou být vyhodnoceny
neefektivně
Příklad semi-join dotazů (2 různé):
◼ SELECT ssnum FROM employee
WHERE dept IN
(SELECT dept FROM tech
WHERE tech.manager=employee.manager)
◼ SELECT ssnum FROM employee
WHERE EXISTS (SELECT 1 FROM tech WHERE
employee.manager = tech.manager)
PA152, Vlastislav Dohnal, FI MUNI, 2018 27
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, 2018 28
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, 2018 29
Přepsání korelovaných dotazů
◼ Dotaz:
Najdi zaměstnance technických oddělení,
kteří vydělávají alespoň 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, 2018 30
Přepsání korelovaných dotazů
CREATE TEMPORARY TABLE temp ( … ) ON COMMIT DROP;
INSERT INTO temp
SELECT avg(salary) as avsalary, tech.dept
FROM tech, employee
WHERE tech.dept = employee.dept
GROUP BY tech.dept;
SELECT ssnum
FROM employee, temp
WHERE salary >= avsalary
AND employee.dept = temp.dept
PA152, Vlastislav Dohnal, FI MUNI, 2018 31
Přepsání korelovaných dotazů
SELECT ssnum
FROM employee as E,
(SELECT avg(salary) as avsalary, tech.dept
FROM tech, employee
WHERE tech.dept = employee.dept
GROUP BY tech.dept) as AVG
WHERE salary >= avsalary AND E.dept = AVG.dept
PA152, Vlastislav Dohnal, FI MUNI, 2018 32
Přepsání korelovaných dotazů
◼ Dotaz:
Najdi zaměstnance technických oddělení,
kteří kamarádí s celým oddělením
(mají stejně kamarádů jako pracovníků ve svém oddělení).
SELECT ssnum
FROM employee e1
WHERE numfriends = (
SELECT COUNT(e2.ssnum)
FROM employee e2, tech
WHERE e2.dept = tech.dept
AND e2.dept = e1.dept);
PA152, Vlastislav Dohnal, FI MUNI, 2018 33
Přepsání korelovaných dotazů
INSERT INTO temp
SELECT COUNT(ssnum) as numworkers,
employee.dept
FROM tech, employee
WHERE tech.dept = employee.dept
GROUP BY tech.dept;
SELECT ssnum
FROM employee, temp
WHERE numfriends = numworkers
AND employee.dept = temp.dept;
Vznikl zde problém v COUNT?
PA152, Vlastislav Dohnal, FI MUNI, 2018 34
Problém v COUNT?
◼ Příklad:
Helena pracuje v ekonomické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.
Přepsání korelovaných dotazů
◼ Anti-joins
SELECT * FROM Tech WHERE dept NOT IN
(SELECT dept FROM employee)
◼ Problém s případnou NULL v employee.dept
SELECT * FROM Tech WHERE NOT EXISTS
(SELECT 1 FROM employee.dept=tech.dept)
◼ Problémy
Nepoužívání join algoritmu
Používání index join na příliš velkém množství
záznamů
PA152, Vlastislav Dohnal, FI MUNI, 2018 35
PA152, Vlastislav Dohnal, FI MUNI, 2018 36
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, 2018 37
Používání pomocných tabulek
◼ Dotaz:
 Pro zaměstnance oddělení informačních systémů,
kteří mají plat > 40000, vypiš jejich 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, 2018 38
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, 2018 39
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, 2018 40
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, 2018 41
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, 2018 42
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, 2018 43
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;
SELECT * FROM hotels_in_city
WHERE city='city174'
PA152, Vlastislav Dohnal, FI MUNI, 2018 44
Používání pohledů
◼ Příkaz EXPLAIN
EXPLAIN SELECT * FROM hotels_in_city;
EXPLAIN SELECT * FROM hotels_in_city
WHERE city='city174’;
◼ Porovnejte s:
EXPLAIN SELECT * FROM
(SELECT lower(city) as city, COUNT(*) AS cnt
FROM hotel GROUP BY city HAVING COUNT(*) > 3) x
WHERE city='city174';
PA152, Vlastislav Dohnal, FI MUNI, 2018 45
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, 2018 46
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, 2018 47
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, 2018 48
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, 2018 49
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, 2018 50
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, 2018 51
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, 2018 52
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’;
◼ Použije se pohled OrdersByVendor
 SELECT vendor, amount FROM OrdersByVendor
WHERE vendor=‘Apple’;
PA152, Vlastislav Dohnal, FI MUNI, 2018 53
Uložené (materializované) pohledy
◼ Příklad
SQLServer, implementováno pomocí triggerů
1m orders – 5 purchasers and 20 vendors
10k items
- 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, 2018 58
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, 2018 59
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, 2018 60
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, 2018 61
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: 5
SELECT * FROM predmet
WHERE kod=‘MA102’;
◼ Použije se table-scan (seq scan)
PA152, Vlastislav Dohnal, FI MUNI, 2018 62
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, 2018 63
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, 2018 64
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, 2018 65
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 1 čtení z disku pro každý záznam.
 Prohledání indexu
◼ 1 čtení
 Aktualizace indexu
◼ 1 čtení a 1 zápis bloku
 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, 2018 66
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 = pi = 1 - p1 - p2
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
◼ Situace1: p1 = p2 = 0.1 → bez indexů
◼ Situace2: p1 = p2 = 0.4 → oba indexy
PA152, Vlastislav Dohnal, FI MUNI, 2018 67
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
 Vytvořit odpovídající indexy
PA152, Vlastislav Dohnal, FI MUNI, 2018 68
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)
 pgAnalyze, HypoPg
PA152, Vlastislav Dohnal, FI MUNI, 2018 69
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, 2018 70
Referenční integrita
◼ Dotaz
◼ Bez indexu (výstup z EXPLAIN SELECT…)
◼ Vytvoříme index nad hotel_id
Seq Scan on room (cost=0.00..8750.89 rows=105 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=974.87..5782.99 rows=105 width=22)
Recheck Cond: (hotel_id = 2)
Filter: (number = 1)
-> Bitmap Index Scan on room_hotel_id_fkey (cost=0.00..974.84 rows=52608 width=0)
Index Cond: (hotel_id = 2)
SELECT * FROM room WHERE hotel_id=2 AND number=1;
PA152, Vlastislav Dohnal, FI MUNI, 2018 71
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
Kombinace indexů
◼ Dotaz
◼ Pouze index nad hotel_id
◼ Pouze index nad number
PA152, Vlastislav Dohnal, FI MUNI, 2018 72
SELECT * FROM room WHERE hotel_id=2 AND number=1;
"Bitmap Heap Scan on room (cost=960.80..5756.77 rows=103 width=22)"
" Recheck Cond: (hotel_id = 2)"
" Filter: (number = 1)"
" -> Bitmap Index Scan on room_hotel_id_fkey (cost=0.00..960.77 rows=51798 width=0)"
" Index Cond: (hotel_id = 2)"
"Bitmap Heap Scan on room (cost=13.02..1688.30 rows=103 width=22)"
" Recheck Cond: (number = 1)"
" Filter: (hotel_id = 2)"
" -> Bitmap Index Scan on room_number_idx (cost=0.00..12.99 rows=628 width=0)"
" Index Cond: (number = 1)"
Kombinace indexů
◼ Dotaz
◼ Index nad hotel_id, number
◼ Dva indexy nad hotel_id a number
PA152, Vlastislav Dohnal, FI MUNI, 2018 73
SELECT * FROM room WHERE hotel_id=2 AND number=1;
"Bitmap Heap Scan on room (cost=5.34..366.14 rows=103 width=22)"
" Recheck Cond: ((hotel_id = 2) AND (number = 1))"
" -> Bitmap Index Scan on room_hotel_id_number_fkey (cost=0.00..5.31 rows=103 width=0)"
" Index Cond: ((hotel_id = 2) AND (number = 1))"
"Bitmap Heap Scan on room (cost=974.07..1334.86 rows=103 width=22)"
" Recheck Cond: ((number = 1) AND (hotel_id = 2))"
" -> BitmapAnd (cost=974.07..974.07 rows=103 width=0)"
" -> Bitmap Index Scan on room_number_idx (cost=0.00..12.99 rows=628 width=0)"
" Index Cond: (number = 1)"
" -> Bitmap Index Scan on room_hotel_id_fkey (cost=0.00..960.77 rows=51798 width=0)"
" Index Cond: (hotel_id = 2)"
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, 2018 74
PA152, Vlastislav Dohnal, FI MUNI, 2018 75
Globální změny
◼ Vytvoření indexu
◼ Změna schématu
Viz další přednáška
◼ Rozdělení relací
Viz další přednáška