Hlavní obsah

Efektivnější SQL dotazy pomocí plánování a optimalizace

Když už umíme několik způsobů výběru dat z tabulky a umíme používat příkaz SELECT napříč několika tabulkami, je nejvhodnější doba zaměřit se na efektivitu SQL dotazů - tedy jak rychle jsou vykonány, a zda by je nešlo provádět dokonce rychleji.

SQL je deklarativní jazyk - každý dotaz deklaruje co chceme, aby SQL udělalo, ale už nedeklaruje jak. Jak se však ukázalo, toto jak - tedy plán, jak na to - je právě to, co ovlivňuje efektivitu dotazů. Je tedy důležité se zaměřit také na tento plán.

Proč SQL dotazy potřebují plánování?

Tak například mějme tento jednoduchý dotaz:

SELECT * FROM knihy WHERE autor = "J K Rowling";

Tento dotaz může SQL server provést 2 způsoby:

Provede se sken celé tabulky: každý řádek v tabulce je prohledán a odpovídající řádky jsou vráceny jako výsledek.
Vytvoří se index: Vytvoří se kopie tabulky uspořádané podle autora, poté se provede binární vyhledání řádku, kde je jako autor uvedena J K Rowling, naleznou se odpovídající ID, a poté se provede další binární hledání v původní tabulce, které vrátí jako výsledek řádky s odpovídajícími ID.

Který způsob je rychlejší? To záleží na datech jako takových, a také to záleží na tom, jak často bude tento dotaz prováděn. Pokud tabulka obsahuje pouze 10 řádků, pak sken celé tabulky musí prohledat pouze 10 řádků, tedy první způsob provedení dotazu by mohl fungovat rychle.

Kdyby však tabulka měla 10 milionů řádků, pak by sken celé tabulky musel prohledat 10 milionů řádků. V tomto případě by tak bylo binární hledání na uspořádané tabulce rychlejší - potřebovali bychom pouze 23 hledání k tomu, abychom prohledali 10 milionů řádků. Nicméně vytvoření takto uspořádané tabulky by také trvalo nějaký čas (cca 230 milionů operací, v závislosti na výpočetní síle hardware). Pokud bychom tento dotaz položili vícekrát (více jak 23 krát), nebo pokud bychom už tuto uspořádanou tabulku měli, druhý způsob byl byl zaručeně lepší.

Jak SQL rozhodne, který ze způsobů použije? O tomto důležitém kroku jsme se ještě nebavili, protože jsme se soustředili na syntax našich dotazů, zatímco implementaci těchto dotazů jsme neřešili. Jak se průběžně budeš dostávat hlouběji do SQL, krok plánování implementace dotazů bude čím dál více důležitější.

Životní cyklus SQL dotazu

Při každém dotazu SQL server prochází postupně těmito kroky:

Analyzátor dotazu ověří správnost syntaxe dotazu (např. správně použitou interpunkci) a správnost jeho sémantiky (tedy že např. ta tabulka existuje) a vypíše chyby, pokud dotaz není správně. Pokud je v pořádku, je tento dotaz přeměněn do algebraického výrazu a předán následujícímu kroku operace.
Plánovač a optimalizátor dotazu dělá tu náročnou myslící část. Nejprve provede jednoduché optimalizace (zlepšení, která zvýší výkon a rychlost dotazu, třeba takové zjednodušení výrazu 5*10 na 50). Poté zváží různé plány pro náš dotaz, které mohou mít různé optimalizace, odhadne náročnost (na základě CPU a času) každého dotazu na základě počtu řádků v dotčených tabulkách, poté vybere nejlepší plán a předá jej k dalšímu kroku operace.
Vykonavatel dotazu tento plán převede na operace pro databázi, a v případě, že existují nějaké výsledky, tak je vypíše.

Kdy do toho musí vstoupit lidé?

Plánování a optimalizace dotazů probíhá při každém dotazu. Můžeš zadávat SQL dotazy celý život a vůbec si toho nemusíš všimnout. Nicméně jakmile se začneš zabývat velkými objemy dat, bude ti na rychlosti dotazů záležet více, a možná začneš přemýšlet o tom, zda existuje nějaký způsob, jak zlepšit výkon tvých dotazů.

Zejména u složitých dotazů existuje několik způsobů jak zlepšit jejich výkon. Toto se označuje jako ladění dotazu.

Prvním krokem k optimalizaci je určení těch dotazů, které potřebují optimalizovat. Jsou to ty dotazy, které zabírají nejvíce zdrojů (RAM, CPU...). Můžeš je objevit třeba pomocí SQL profileru. Občas můžeš objevit špatný, nevyladěný dotaz tím, že trvá moc dlouho a nakonec způsobí pád celé databáze. Doufejme, že to zjistíš vždycky dříve, než dojde k pádu.

Dalším krokem je pochopení toho, jak konkrétní databázové systémy SQL zpracovávají dotazy, a každého tohoto systému se na to můžeš zeptat. V SQLite můžeme před každý dotaz předřadit EXPLAIN QUERY PLAN, abychom viděli, jak toto plánování probíhá. Pokud to budeš používat, připrav se na to, že si musíš důkladně prostudovat EXPLAIN QUERY PLAN dokumentaci, jelikož toto vysvětlení je velmi detailní a závislé na konkrétní implementaci. Pokud používáš jiné systémy, můžeš například vyhledat how do I get an execution plan in X.

Nyní přijde to nejtěžší: ruční optimalizace a zlepšení tohoto plánu. Tato část je často závislá na typu použitého databázového systému SQL a také závisí na typu vašich dat.

Tak například, vzpomínáš ještě na dotaz úplně na začátku? Kdybychom věděli dopředu, že budeme provádět stovky dotazů s příkazem WHERE pro sloupec s autory, mohli bychom přímo vytvořit index pomocí příkazu CREATE INDEX. Potom by SQL server tento index efektivně používal k vyhledávání řádků. Můžeš si přečíst tento návod na plánování dotazů v SQLite, který by ti mohl pomoci s pochopením fungování indexů a jejich užitečnosti.

Vytvářením indexů lze opakované dotazy zefektivnit. Ale existuje mnoho jiných způsobů ke zefektivnění dotazů. Pokud používáš SQLite, podívej se na jejich přehled plánovačů dotazů a určitě nevynechej sekci manuály.

Určitě nemůžeme projít všechny záludnosti optimalizace a ladění dotazů, takže doporučuji ponořit se hlouběji do studia toho, co právě používáš a potřebuješ.

(Pro hlubší studium plánovačů SQL mi právě tyto přišly užitečné: SQL Server Query Optimalizátor, Oracle SQL ladění, MSSQL základy plánů pro vykonání dotazu)

Chceš se zapojit do diskuze?

Přihlášení

Setřídit podle:

Zatím žádné příspěvky.

Umíš anglicky? Kliknutím zobrazíš diskuzi anglické verze Khan Academy.

Programování

Kurz: Programování > Kapitola 3

Proč SQL dotazy potřebují plánování?

Životní cyklus SQL dotazu

Kdy do toho musí vstoupit lidé?

Chceš se zapojit do diskuze?