Měl bych pokrýt následující témata:

-Popis optimizéru, části rozhodující, jak bude daný dotaz proveden. Když víte, jak pracuje, můžete tvořit lepší dotazy.

-Diskuse o rychlostech operací

Před tím, než začnete optimalizovat svoje SQL dotazy, je důležité udělat krok zpět a vidět je jako část většího systému skládajícího se z následujících částí:

-Jeden nebo více programů

-Jeden nebo víc počítačů

-Jeden nebo více správců

Lidé zodpovědní za programy.

-Jeden nebo více uživatelů

-Jeden nebo více organizací

V každém případě je důležité rozlišovat dva body o systému jako celku.

1) cíl systému je sloužit uživateli.

2) SQL výrazy tvoří pouze malou část systému, často se nejefektivnější zásahy do systému týkají jiné části.

 Následující část je obecný postup pro analýzu problému výkonnosti. Smysl postupu je ukázat možná, i netechnická, řešení.

Uvažujte celý systém. Řešení může být ve změně plánování nebo řízení zdrojů 

Naučte se vše o porozumění aplikaci jako celku. Databázová aplikace má obvykle mnoho částí- uložené dotazy,obrazovkové formuláře, reporty, programy. Uvažujte je dohromady a ptejte se:

Nemůžete dělat významné pokroky v problému, pokud ho nezměříte. Musíte najít způsob jak opakovaně změřit pomalé části aplikace. Důvody jsou takovéto:

-Bez čísel nemůžete popsat problém uživatelům a organizaci.

-Bez čísel nemůžete dosáhnout významných pokroků, nemůžete vyjádřit číselně míru zrychlení a vytyčit si jasné cíle.

-Bez čísel nemůžete vyjádřit své pokroky

Čísla potřebujete pro změření malých úspěchů a pro rozhodování mezi alternativními možnostmi.

 Př:Zjišťování času ve 4gl

 Jedním z lepších zvyků je, že ve většině programů je malá část kódu(20 %nebo méně) měří čas běhu jednotlivých částí programu.

Optimizér je část databáze, která rozhoduje jak provádět dotazy. Jeho nejdůležitější práce je rozhodovat pořadí tabulek. K provedení takového rozhodnutí se rozhoduje k nejefektivnějšímu přístupu k jednotlivým tabulkám(sekvenčně, přes index, přes dočasný index) a musí rozhodnout, kolik řádků každé tabulky se bude podílet na konečné odpovědi.

Návrh optimizéru není vědecký a optimizér je částí databáze, která se pořád vyvíjí a zlepšuje.

My bereme v potaz verzi 4.1 a starší. 

Pořadí tabulek má velký vliv na rychlost joinů, což nejlépe uvidíme na příkladech

SELECT C.customer_num, O.order_num,sum(items.total_price)

FROM customer C,orders o, items I

WHERE C.customer_num=O.customer_num

AND O.order_num=I.order_num

GROUP BY C.customer_num, O.order_num

Představte si tabulky bez indexů. Bez indexů se nemá DBE jinou volbu, než jednoduché hnízděné cykly, takže by vyhodnocování vypadalo nějak takto:

For each row in the customer table do:

End For

Celkem to tedy vypadá

Toto není jediný možný plán dotazu. Jiný může pouze přehodit role customer a orders.

Číslo řádku v každé tabulce je vzato z systémové katalogové tabulky systables. Ve skutečnosti můžeme napsat dotaz počítající počet řádků, který může být použit v dotazovém plánu výše uvedeného:

SELECT C.nrows+C.nrows*O.nrows+O.nrows*I.nrows

FROM systables C, systables O, systables I

WHERE C.tabname="customer"

AND O.tabname="orders"

AND I.tabname="items"

V předchozím případě nebyl vidět rozdíl v rozsahu práce dvou možných plánů. Účast sloupcového filtru to mění. Sloupcový filtr je WHERE výraz, který redukuje počet řádek, kterými tabulky přisůívají. Toto je předchozí dotaz s filtrem:

SELECT C.customer_num, O.order_num,sum(items.total_price)

FROM customer C,orders o, items I

WHERE C.customer_num=O.customer_num

AND O.order_num=I.order_num

AND O.paid_date IS NULL

GROUP BY C.customer_num, O.order_num

Výraz O.paid_date IS NULL vyfiltruje některé řádky, které budou použity z tabulky orders. Jako předtím jsou zde dva plány. Plán začínající orders:

For each row in the orders table do:

Read row into O

If O.paid_date is null then

For each row in the customer table do:

Read row into C

If O.customer_num=C.customer_num then

let Sum=0

For each row in the items table do:

Read row into I

If I.order_num=O.order_num then

Let Sum=Sum+I.total_price

End If

End For

Prepare an output row from C,I ,and Sum

End If

End For

End If

End For

Nechť pdnull bude počet řádků, které určí filtr:

SELECT COUNT(*) FROM orders WHERE paid_date IS NULL

Pak to bude vypadat:

Alternativní plán by vypadal:

For each row in the customer table do:

End For

Tyto dva plány mají stejný výsledek, přestože se jejich postup liší. Tento rozdíl však může představovat tisíce diskových přístupů.

Předchozí případy nepoužívali indexy. To bylo nerealistické. Skoro všechny tabulky mají jeden nebo více indexů, které se projevují při plánování.

S indexy by plán vypadal takto:

For each row in the customer table do:

End For

To už je velká redukce.

Ale každý plán užívající indexy musí index načíst. Je obtížné předvídat, jak mnoho stránek indexu bude třeba načíst.

Fyzické uložení tabulky může také ovlivnit cenu užití indexu. Předchozí dotaz načítá customery v fyzickém pořadí.

Když je tedy výstup v pořadí customer_number, budou se záznamy slévat, takže budou načítány pouze jednou. Pokud je fyzické uložení náhodné, může vést vyzvedání každé položky na novou stránku, což bude také dost zpomalovat.

Katalogy podporované INFORMIX-ONLINE nahrazují doplňkový vstup:

Optimizér nejdříve zkouší výrazy ve WHERE a kouká se po filtru. Oceňuje zvlášť pro každý filtr. Selektivita je číslo mezi 0 a 1, které značí část řádků, o kterých si optimizér myslí, že projdou. Hodně vybíravý filtr dostane 0, filtr málo filtrující dostane 1.

Optimizér si tedy poznamenává tyto fakta o dotazu

Pak vybere optimizér nejlepší způsob přístupu do tabulek z dotazu. Má tři možnosti

Volba mezi prvními dvěmi způsoby závisí na tom, kolik odfiltrují filtry. Když je dostatečný filtr na indexovaném sloupci, přistupuje se pomocí indexu.

 Když zde není filtr, musí přečíst všechny řádky. Je obvykle rychlejší jednoduše číst řádky než čtení řádků přes index. Když se však řádky očekávají seřazené, můžeme ušetřit přímým čtením méně než řazením.

Dočasný index vytváří ve dvou případech

Se všemi těmito dostupnými informacemi, optimizér generuje všechny možné dotazy pro joinování tabulek v páru. Potom, když jsou joinovány více než 2 tabulky, užije optimizér nejlepší dvoutabulkové plány k připojení dvou tabulek ke třetí, třech ke čtvrté a tak dál.

Optimizér přidá cenu koncových prací pro kompletní joinovací plány. (ORDER BY,GROUP BY)

Konečně optimizér vybere nejmíň náročný plán a předá ho hlavní části k zpracování.

Volba optimizéru nemusí být tajemstvím. Můžete si vyhledat přesně, co je plán. Zapněte volbu SET EXPLAIN ON před tím, než vykonáváte dotaz. Na počátku dalšího dotazu optimizér vypíše vysvětlení jeho plánu do konkrétního souboru (jeho jméno a umístění závisí na operačním systému)(sqexplain.out). Ceny vypisuje v jednotlivých přístupech na disk, jiné akce jsou na to přepočítávány.

Nejvíce času enginovi zabírají při provádění dotazů dvě věci

Z těchto je čtení daleko pomalejší.

Tato část bude o tom, kde engin tráví čas, bude prozkoumávat vztahy pro tvorbu rychlejších dotazů.

Engin může s daty pracovat pouze v paměti. Musí je načíst do paměti před jejich porovnáváním s filtry. Musí načíst řádky z obou tabulek, než začne testovat joinovací podmínku. Engin si připravuje výstup v paměti konstrukcí vybraných sloupců z jiných řádků v paměti.

Většina z těchto aktivit jsou velmi rychlé. V závislosti na počítači engin může provést stovky nebo dokonce tisíce porovnání za sekundu. Čas strávený prací v paměti je obvykle zlomkem z celého času provedení dotazu.

-třídění

-porovnávání pomocí LIKE a MATCHES.

Trvá déle číst řádky z disku než testovat řádky přímo v paměti. Hlavním cílem optimizéru je redukovat data, která musí být čtena z disku, ale může eliminovat pouze nejvíce zřejmé neefiktivity.

V INFORMIX-ONLINE je velikost bloku nastavena při inicializaci. (Obvykle 4 kB). U Jiných engininů INFORMIXU,které využívají cizí souborový operační systém, velikost bloku záleží na něm (typicky 1kB). Je možné definovat tabulky široké jako blok (některé enginy dovolují řádkům přesahovat velikost bloku ). Typiská velikost řádku je mezi 50 a 200 B,takže typisky blok obsahuje 5-50 řádků. Indexová položka obsahuje klíč a 4Btový pointer, takže blok indexu typicky obsahuje 50-500 položek.

Engine má kus paměti vyhražen pro kopie diskových bloků, které byly v poslední době čteny. Zůstává v naději, že tyto stránky budou znovu potřeba. Když se jeho očekávání splní, nemusí znovu přistupovat na disk.

Podobně jako u bloků, počet vyrovnávacích pamětí závisí na enginovi a souborovém operačním systému.

Když si engin potřebuje otestovat řádek, musí ho přečíst z disku. Při tom nečte jeden řádek, ale blok, který ho obsahuje (Když je řádek delší než blok, přečte tolik bloků, kolik je nezbytné.). Cena přečtení bloku je základní jednotka práce, kterou optimizér užívá pro své výpočty. Skutečná cena načtení bloku je proměnná a těžko předvídatelná. Je to kombinace následujících faktorů:

Cena načtení bloku může kolísat od mikrosekund (když je ve vyrovnávací paměti) přes několika milisekund do stovek milisekund.

Disková cena je nejmenší, když se čte v pořadí, v kterém jsou data uložena na disku (každý blok načten pouze 1x).

Pokud je engin jediný program na disku, cena seeku je také minimalizovaná. Souvislé řádky jsou obvykle v souvislých blocích, takže se hlavy posouvají málo o málo. Dokonce když není důvod, groupování souvislých bloků probíhá souvisle, takže nejsou potřeba dlouhé posuny.

Dokonce reakční doba může být při sekvenčním čtení nižší. To závisí na HW a na souborovém operačním systému. Disk je obvykle nastaven k minimalizaci reakční doby, ale je možné, že disk potřebuje celou otáčku, než je schopen načíst další blok, což drasticky zpomaluje dobu přístupu.

Cena disku je větší, když jsou řádky vyzvedávány v jiném pořadí, než je jejich fyzické uložení. Normální tabulky jsou mnohem větší, než vyrovnávací paměť, takže jen malá část tabulky může být v paměti. Když je tabulka čtena zpřeházeně, jen málo řádků najdeme ve vyrovnávací paměti. Obvykle je pro každý řádek čten blok.

Když čteme zpřeházeně, je zde obvykle velké časové zdržení i rotační prodleva. Zkrátka při nesekvenčním čtení je přístup mnohem pomalejší než při sekvenčním.

Nejjednodušší forma nesekvenčního přístupu je přístup pomocí rowid. Rowid specifikuje fyzické uložení řádku a jeho blok, takže jeho cena je podobná jako při sekvenčním.

Zde je přidaná cena vážící se k nalezení řádky přes index. Index sám je uložen na disku a musí být načten do paměti.

Engin užívající index má dvě možnosti. Jedna je vyzvednout řádek podle klíčové hodnoty. To vypadá asi takto:

SELECT company,order_num

FROM customer,orders

WHERE customer.customer_num=orders.customer_num

Jedna tabulka, pravděpodobně customer, bude čtena sekvenčně. Její hodnota customer_num je použita k prohledání indexu na sloupec customer_num v tabulce orders. Když matchuje, je řádek přečten.

Engin může také číst index sekvenčně (r+i, i počet listů).

Každá tabulka, která se vejde do vyrovnávací paměti je malá (cca 4 bloky a méně).

Přesun dat přes síť je, co se týče prodlev, ošidný.

Rozeznáváme tu dva přístupy

Obecně

Prvně vyberte jeden dotaz, který je příliš pomalý. Potom nastavte prostředí, v kterém ho chcete odhadovat, schopné opakovat dotaz. Bez tohoto prostředí si nikdy nebudete jisti, zda změna pomohla či nikoliv.

Když užíváte víceuživatelský systém nebo síť, kde se podmínky mění v závislosti na denní době, je třeba provádět experimenty ve stejnou dobu. Můžete třeba vždy pracovat v noci.

Pokud je dotaz součástí složitého programu, vytáhněte z něj SELECT a spouštějte ho interaktivně, nebo ho zabudujte do jednoduchého programu.

Skutečným dotazům trvá dlouho, než se provedou, tak si můžete připravit menší model DB, v které můžete dotazy testovat rychleji. Může to pomoci, ale musíte si být vědomi následujících problémů:

Proto každé vaše rozhodnutí na malém modelu musí být prozatimní dokud si ho neověříte na velké.

Užijte SET EXPLAIN ON k rozluštění užitého dotazového plánu. Zde je pár motivů:

 Nyní, když rozumíte, co dotaz dělá, se koukneme na způsoby jak vyhodnocování zlehčit. Následující návrhy odpovídají předchozímu seznamu.

Třídění není nezbytně špatná věc. Enginovy třídící algoritmy jsou vylaďěny k extrémní výkonnosti. Jsou určitě tak rychlé jako některá externí třídění, která můžete aplikovat na stejná data. Pokud je třídění děláno příležitostně, nebo na málo výstupních řádcích, není potřeba se mu vyhýbat.

Zde je několik faktorů, která zabraňují optimizéru užít index:

Sekvenční přístup

Sekvenční přístup do tabulky vypadá na první pohled zlověstně, protože hrozí číst každý řádek tabulky pro všechny předchozí. Měli by jste být schopni rozhodnout, kolikrát to bude možná jen párkrát, ale možná stokrát či tisíckrát. Když je tabulka malá, nevadí číst ji znovu a znovu. Tabulka bude celá v paměti. Sekvenční čtení v paměti může být rychlejší než čtení stejné tabulky přes index, zvlášť když indexové stránky odstraní z paměti jiné používané stránky.

Když je tabulka větší než pár stránek, opakované sekvenční čtení je pro vykonávání smrtelné. Jedna cesta, jak tomu předcházet je nabídnout index na joinovací sloupec.

Index zabírá místo úměrně k šíři klíčových hodnot a počtu řádků. Tedy engin musí modifikovat index kdykoliv jsou řádky vkládány, mazány, nebo modifikovány. To zpomaluje tyto operace. Když je to nezbytné, můžete užít DROP INDEX k odstranění indexu po sérii dotazů, tak uvolnit místo a usnadnit modifikaci.

Jisté formy WHERE klauzule přinucují optimizér k sekvenčnímu přístupu, i když index na testovaném sloupci existuje. Následující dotaz si vynucuje sekvenční přístup do tabulky orders:

Tento dotaz můžete urychlit úpravou na UNION dotaz. Napište oddělené SELECTy pro každou množinu řádků a spojte je pomocí UNION. Zde je přepsaný předchozí případ:

Optimizér užije index pro oba dotazy.

Když dotazový plán zahrnuje autoindexy na velké tabulky, berte to jako doporučení optimizéru, že by na tom sloupci měl být index. Je možné nechat engin vystavět a zrušit index, když provádíte dotaz pouze příležitostně, ale když dotaz kladete často, ušetříte čas uděláním řádného dotazu.

Když vaše aplikace provádí některé dlouhé dotazy a všechny třídí podle stejných sloupců, můžete ušetřit čas vytvořením složeného indexu na tyto sloupce. V důsledku vykonáte třídění jednou a uložíte výstup pro užití v každém dotazu.

Po velkých updatech (po přemístění čtvrtiny nebo více řádků tabulky) se struktura indexu může stát neefektivní. Když se index zdá být méně efektivní, než by měl být a přesto bcheck nehlásí žádné chyby, zkuste ho zrušit a znovu vytvořit.

Korelované poddotazy jsou takové, které zahrnují vybraný seznam sloupců hlavního dotazu ve WHERE klauzuli poddotazu. Protože se odpovědi poddotazu mohou pro každý řádek lišit, je třeba poddotaz prováádět pro každý řádek. Toto může být velmi časově náročné. Naneštěstí bez vztažných poddotazů některé věci v SQL nejdou.

MATCHES a LIKE podporují wildcards, zámé jako regulární výrazy. Některé regulární výrazy jsou pro engin více obtížné než jiné. Wildcarda na počáteční pozici jako v následujícím příkladu (najdi zákazníky, jejichž jméno nekončí na y), nutí engin vyzkoušet každou hodnotu sloupce:

SELECT * FROM customer WHERE fname NOT LIKE "%y"

Optimizér nemůže použít index pro takový filtr, I kdyby nějaký měl. Toto ho nutí k sekvenčnímu postupu tabulkou. Když je tento dotaz druhý nebo později v joinu, dotaz je pomalý.

Když je obtížné testování regulárních výrazů nezbytné, vyhněte se kombinaci s joinem. Nejdříve zpracujte jednu tabulku pomocí regulárního výrazu a vyberte vysněné řádky. Uložte výsledek do dočasné tabulky a připojte k jiným.

Regulární výrazy s wildcardami jenom uprostřed a nakonci porovnávaných nevylučuje použití indexu, pokud existuje, ale stejně se jedná o dost pomalou operaci. V závislostech na datech ve sloupci, můžete upravit některé výrazy na porovnávání. Např:

SELECT * FROM customer WHERE zipcode LIKE "98_ _ _"

Můžete nahradit

SELECT * FROM customer WHERE (zipcode =>"98000")AND (zipcode <99000)

Filtry založené na nevýchozích podřetězcích sloupců také vyžadují, aby byla každá hodnota testovaná př:

SELECT * FROM customer

Optimizér užívá index pouze na výchozí hodnoty sloupce.

Vystavění dočasné setříděné podmnožiny tabulky může někdy urychlit dotaz. Může vám pomoci vyhnout se vícenásobnému třídění a může zjednodušit I práci optimizéru.