PA152: Efektivní využívání DB 9. Ladění dotazů Vlastislav Dohnal PA152, Vlastislav Dohnal, FI MUNI, 2013 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, 2013 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/9.2/interactive/view-pg-stats.html PA152, Vlastislav Dohnal, FI MUNI, 2013 4 Příklad statistik PostgreSQL  Tabulka hotel PA152, Vlastislav Dohnal, FI MUNI, 2013 5 Příklad statistik PostgreSQL  Atribut hotel.id  Atribut hotel.name PA152, Vlastislav Dohnal, FI MUNI, 2013 6 Příklad statistik PostgreSQL  Atribut hotel.state  Atribut hotel.distance_to_center PA152, Vlastislav Dohnal, FI MUNI, 2013 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, 2013 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, 2013 9 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  Možnosti, jak objevit Databáze umožňují logování „dlouhých“ dotazů … PA152, Vlastislav Dohnal, FI MUNI, 2013 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, 2013 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; (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, 2013 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, 2013 13 Používání indexů  Optimalizátor 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, 2013 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, 2013 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ý? PA152, Vlastislav Dohnal, FI MUNI, 2013 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, 2013 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. 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, 2013 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, 2013 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, 2013 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, 2013 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 bez agregační funkce  Korelované dotazy PA152, Vlastislav Dohnal, FI MUNI, 2013 22 PA152, Vlastislav Dohnal, FI MUNI, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 28 Přepsání korelovaných dotazů INSERT INTO temp SELECT avg(salary) as avsalary, employee.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, 2013 29 Přepsání korelovaných dotazů  Dotaz: Najdi zaměstnance technických oddělení, kteří mají stejně kamarádů jako pracovníků 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, 2013 30 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 49 Uložené (materializované) pohledy  Příklad SQLServer, implementováno pomocí triggerů 1m orders – 5 purchaser 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 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 (3 záznamy).  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, 2013 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 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, 2013 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  Vytvořit odpovídající indexy PA152, Vlastislav Dohnal, FI MUNI, 2013 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, 2013 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, 2013 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..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, 2013 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, 2013 64 PA152, Vlastislav Dohnal, FI MUNI, 2013 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