Jak optimalizovat SQL dotazy praktickým a efektivním způsobem

Když aplikace začne běžet pomalu, mnoho lidí se podívá na server, síť nebo dokonce na počítač uživatele… ale V mnoha případech je skutečným viníkem špatně navržené SQL dotazy.Jednoduchá změna v klauzuli WHERE nebo indexu může znamenat rozdíl mezi odpovědí v milisekundách nebo několika sekundách.

Záludná část je, že Zjištění, proč je dotaz pomalý, může trvat hodiny nebo dny.Projděte si plány provedení, testujte varianty, analyzujte blokování, statistiky atd. Dobrou zprávou je, že existují velmi typické návrhové vzory, které téměř vždy způsobují problémy s výkonem, a pokud je rychle rozpoznáte, ušetří vám obrovské množství času stráveného diagnostikou.

SQL vzory, které obvykle fungují velmi špatně

Existují určité vzory SQL kódu, které jsou skutečnými výkonnostními bombami. a které by měly být pečlivě sledovány: mimo jiné složité OR, vyhledávání zástupných znaků v řetězcích, masivní zápisy, dotazy s příliš mnoha tabulkami nebo zneužívání nápověd.

Praktickým cílem je, že když narazíte na aplikaci, která je poháněna pedály, můžete prohledat dotazy a rychle identifikovat tyto nebezpečné vzorce zaměřit testy přesně tam, kde je problém nejpravděpodobnější.

Vždy byste měli ověřit pomocí reálných měření (běhová doba, logické čtení, využití CPU atd.), ale Vědět, kde začít s vyšetřováním, celý proces výrazně zkracuje..

Problémy s OR v operacích JOIN a WHERE ve více sloupcích

Logické operátory se zdají být nevinné, ale Použití operátoru OR na více sloupcích nebo tabulkách může snížit efektivitu operátoru., zejména v systémech jako SQL Server, ačkoli tato myšlenka platí i pro jiné enginy.

I když se podmínky spojené operátorem AND vzájemně vylučují a umožňují postupné zredukování datové sady, Operace OR je inkluzivní a nutí engine vyhodnocovat každou větev zvlášť.Z hlediska plánu provádění se to promítá do více průchodů tabulkami a často i do přemrštěného počtu čtení.

Nejzávažnější případ nastává, když se OR spojí více sloupců nebo sloupců z více tabulekOptimalizátor je nucen sledovat každou cestu operace OR a poté spojit výsledky. V tabulkách se stovkami tisíc řádků to může vést k milionům logických čtení, i když tabulky nejsou tak velké.

Velmi běžnou taktikou pro zlepšení těchto situací je Odstraňte operátor OR přepsáním dotazu do více příkazůkaždý s vlastním SELECT a následně spojen pomocí UNION nebo UNION ALL. Každý SELECT pak může být individuálně optimalizován enginem.

V praxi to znamená, že Jednoduché dotazy s operátorem OR lze transformovat do několika mírně delších dotazů.Na oplátku je však plán obvykle stabilnější, s menším počtem čtení a kratší dobou provádění. Cenou je, že někdy čtete stejné tabulky vícekrát; i tak zlepšení obvykle převáží náklady, zejména když vám OR brání ve využití vhodných indexů.

Klíčem je, že Neměli byste se spoléhat na podmínky OR v různých sloupcích nebo ve více tabulkách.Pokud auditujete pomalé dotazy a v klauzuli JOIN nebo WHERE vidíte příkazy OR roztroušené po celé klauzuli, považujte je za hlavního kandidáta na viníka a otestujte je samostatně ve více dotazech.

Vyhledávání zástupných znaků a vyhledávání v celém textu

Textové vyhledávání je dalším klasickým příkladem problémů s výkonem. Hledání libovolných podřetězců ve sloupcích textu je ze své podstaty nákladné.Engine nemá způsob, jak „skočit“ na správný bod a nakonec skenuje řádek po řádku a znak po znaku.

U sloupců textu, které jsou často konzultovány, je vhodné zvážit několik základních věcí: Existují indexy pro tyto sloupce? Umožňuje vyhledávací vzorec použití těchto indexů? Můžeme použít indexy typu FULLTEXT nebo nějaké alternativní řešení, jako jsou hashe nebo n-gramy?

V enginech jako SQL Server nebo MySQL, Umístění zástupného znaku % na začátek vzoru narušuje použití konvenčních indexů B-stromu.Jinými slovy, klauzule WHERE pro příjmení LIKE '%For%' vynutí úplné prohledávání, i když máte index pro příjmení. Totéž platí pro vzory končící na %, ale v sestupném pořadí: ztrácíte výhodu indexového pořadí.

Na malých pódiích to nemusí být problém, ale V tabulkách s miliony řádků je sekvenční prohledávání každého vyhledávání obrovským úzkým hrdlem.Proto je třeba pečlivě zvážit návrh těchto konzultací.

Než se pustíme do složitých optimalizací, je velmi užitečné přehodnotit samotný funkční požadavek: Opravdu uživatel potřebuje hledat kdekoli v řetězci? Někdy postačí vyhledávání podle prefixu („For%“ místo „%For%“), nebo můžete vynutit použití jiných filtrů (podle data, stavu, kategorie atd.), které drasticky sníží počet řádků, které je třeba zkontrolovat.

Kromě úpravy funkčního designu, Fulltextové indexy nabízejí velmi účinnou alternativu když je textové vyhledávání časté nebo složité. Umožňují vám vyhledávat slova, fráze a provádět pokročilejší lingvistické vyhledávání pomocí specifických operátorů a obecně pracovat s datovými strukturami optimalizovanými pro text.

K dispozici je však fulltextové indexování. Je to další funkce: je třeba ji nainstalovat, nakonfigurovat a udržovat.V aplikacích silně zaměřených na textový obsah se obvykle jedná o velmi ziskovou investici, ale zvyšuje složitost a náklady na údržbu.

Pro relativně krátké řetězce, jako jsou jména nebo kódy, můžete také použít n-gramové technikyKaždá hodnota je rozdělena na malé fragmenty s pevnou délkou (např. 3 znaky), které jsou uloženy v samostatné tabulce spolu s odkazem na původní řádek.

Tímto způsobem, místo prohledat obrovskou tabulku NVARCHARV tabulce n-gramů, která může být dobře indexovaná, se provede přesné vyhledávání. Poté se pomocí identifikátoru načtou původní řádky. Náklady na tuto techniku ​​jsou dvojí: na jedné straně, Tabulka n-gramů může velmi rychle růstNa druhou stranu musí být udržován při každém vložení, aktualizaci nebo smazání, což ho činí vhodným pouze pro krátké texty.

Stručně řečeno k této části: Vyhledávání pomocí interních zástupných znaků je ze své podstaty nákladné.A nejlepší, co můžete udělat, je upravit design aplikace (odstranit počáteční zástupné znaky, přidat filtry) nebo použít specializované struktury jako FULLTEXT nebo n-gramy, pokud opravdu neexistuje žádná alternativa.

Indexy: typy, návrh a údržba

Pokud existuje jeden ukazatel výkonu, který se opakuje znovu a znovu, je to indexování. Nejpřímějším způsobem, jak zrychlit dotaz, je vytvořit vhodné indexy pro sloupce, které se objevují v klauzuli WHERE a v podmínkách JOIN.Ale, jako všechno v databázíJe tu háček: příliš mnoho indexů je také problém.

Index je v podstatě struktura (obvykle B-strom), která Umožňuje rychle najít řádky, které splňují zadanou podmínku. aniž by bylo nutné číst celou tabulku. Například MySQL ukládá většinu svých indexů do B-stromů: PRIMARY KEY, UNIQUE, INDEX a také FULLTEXT (ačkoli ty mají své vlastní interní charakteristiky).

Kromě indexů založených na B-stromech, MySQL používá R-tree pro prostorová data a hash pro tabulky v paměti.Každá struktura má své výhody v závislosti na datovém typu a přístupovém vzoru: B-stromy jsou ideální pro rozsahy a řazení; R-stromy pro prostorové dotazy; a hašovací indexy pro velmi rychlé vyhledávání rovnosti v paměti.

Pokud jde o typy logických indexů, nejběžnější jsou: primární klíče, cizí klíče, jedinečné indexy, normální indexy, vícesloupcové indexy, fulltextové indexy a funkční indexyKaždý z nich naplňuje jinou potřebu a je důležité pochopit jejich důsledky pro čtení i psaní.

Například složený index pro (příjmení_kontaktu, jméno_kontaktu) bude Užitečné při vyhledávání pouze podle příjmení nebo společně podle příjmení a křestního jménaAle nepomůže, pokud budete filtrovat pouze podle názvu. Tyto druhy detailů hrají při navrhování indexů tabulek velký vliv.

K dispozici je také možnost indexovat pouze jeden prefix textového sloupcePro zmenšení velikosti indexu, pokud je `nombre_cliente` nastaveno na `VARCHAR(50)`, může stačit indexovat prvních 20 nebo 25 znaků, za předpokladu, že většinu hodnot lze v rámci těchto znaků rozumně rozlišit. Cílem je najít rovnováhu mezi selektivitou a velikostí indexu.

V MySQL, počínaje verzí 8.0.13, můžete vytvářet funkční indexy na výsledku výrazuTo je obzvláště užitečné pro dotazy, které v klauzuli WHERE používají funkce jako YEAR(payment_date): místo přerušení použití indexu ve sloupci payment_date vytvoříte přímý index ve sloupci YEAR(payment_date) a optimalizátor toho může využít.

Indexy lze vytvořit pomocí CREATE INDEX, ALTER TABLE nebo přímo v definici tabulky pomocí CREATE TABLEKaždá možnost je užitečná v různých okamžicích: při počátečním vytvoření schématu, následných refaktoringech nebo specifických úpravách výkonu.

Je také nezbytné být schopen prozkoumat, jaké indexy existují a jak se používajíV MySQL můžete k zobrazení indexů a typů klíčů použít příkazy SHOW INDEX nebo DESCRIBE a v příkazy například EXPLAIN pro kontrolu, zda dotaz skutečně používá tyto indexy, nebo stále provádí úplné skenování (typ = ALL, velmi vysoké řádky atd.).

Co se týče údržby, máte k dispozici nástroje, jako například OPTIMALIZACE TABULKY a ANALÝZA TABULKYOPTIMIZE pomáhá defragmentovat tabulku a reorganizovat indexy, zatímco ANALYZE přepočítává distribuci klíčů, která je základem pro mnoho rozhodnutí optimalizátoru (pořadí spojení, výběr indexu atd.). Jejich spuštění po masivním načtení nebo velkých změnách pomáhá udržovat plány provádění rozumné.

Existují však běžné chyby, kterým je třeba se vyhnout: Nadměrné indexování tabulky, ponechání téměř bez indexů nebo chybějící klastrovaný index/primární klíč.Příliš mnoho indexů penalizuje zápisy (každá operace INSERT, UPDATE nebo DELETE musí aktualizovat všechny indexy) a zabírá mnoho místa na disku a zálohovacího prostoru. Příliš málo indexů naopak nutí k nepřetržitému čtení celé tabulky.

V SQL Serveru samotný engine také navrhuje chybějící indexy na základě plánů realizaceTo lze provést pomocí Management Studia, XML souboru plánu nebo dynamických zobrazení. Tato doporučení jsou užitečná jako výchozí bod, ale měla by být kriticky zhodnocena: často navrhují nadměrně velké indexy s mnoha sloupci INCLUDE nebo duplicitní podobné indexy.

Než přijmete návrh na chybějící index, je vhodné se sami sebe zeptat: Existuje již podobný index, který by se dal rozšířit? Potřebuji všechny sloupce INCLUDE? Jaký je odhadovaný dopad na zlepšení? Spouští se tento dotaz dostatečně často, aby to odůvodnilo jeho použití?

A konečně, pokud vaše aplikace obsahuje tabulky bez klastrovaného indexu nebo primárního klíče, To by mělo spustit všechny varovné signály.Čisté haldy obecně fungují hůře pro mnoho úloh a ztěžují vytváření efektivních neklastovaných indexů. Definování dobrého primárního klíče a klastrovaného indexu je obvykle vysokou prioritou před jemným doladěním.

Masivní zápis, bloky a růst logů

Ne všechno je SELECT. Rozsáhlé operace zápisu mohou také vést k vážným problémům s výkonem a konflikty.Rozsáhlé aktualizace, vkládání nebo mazání mohou na dlouhou dobu uzamknout celé tabulky, drasticky zvětšit velikost transakčního protokolu a nechat ostatní uživatele čekat.

Pokaždé, když upravíte data, engine Umístěte bloky tak, aby byla zajištěna konzistence a zabránilo se interferenciTo je dobré pro integritu, ale když operace trvá příliš dlouho, stává se úzkým hrdlem: ostatní dotazy jsou blokovány, objevují se časové limity a brzy následují stížnosti na to, že „databáze je hrozná“.

Co se považuje za „rozsáhlou operaci“? Hodně záleží na schématu: počet indexů, triggerů, cizích klíčů atd.V jednoduché tabulce může být v jedné transakci zvládnutelných 100 000 řádků; v tabulce s mnoha omezeními by již 2 000 řádků mohlo být problémem. Jediný spolehlivý způsob, jak to zjistit, je otestovat to za reálných nebo velmi podobných podmínek.

Kromě blokád, Masivní zápisy způsobují rychlý růst transakčního protokolu.Pokud nesledujete jeho velikost, můžete zjistit, že protokol nebo samotný disk jsou plné. To je obzvláště důležité během úloh údržby, načítání ETL nebo migrací, kde dochází k velkému počtu zápisů v krátkém časovém období.

Docela rozumný přístup je rozdělit velké operace na menší dávkyMísto aktualizace milionu řádků najednou to děláte v dávkách (například 10 000) s mezilehlými commity, čímž se zkracuje doba trvání uzamčení a velikost každé transakce v protokolu. Pro procesy mimo špičku (údržbová okna) si můžete dovolit větší dávky; v produkčním prostředí budete možná muset být poměrně konzervativní.

Také stojí za to zkontrolovat, které operace generují masivní zápisy: Přidávání a naplňování nových sloupců, změna datových typů, importy, historické soubory, pravidelné čištěníPochopení jeho dopadu vám pomůže plánovat časová okna, upravovat velikosti dávek a vyhnout se nepříjemným překvapením uprostřed nasazení nebo kritické údržby.

Dotazy s mnoha tabulkami a explozí plánu

Dalším vzorem, který způsobuje nemalé bolesti hlavy, je použití gigantických dotazů, které spojují obrovské množství tabulekOptimalizátory SQL (SQL Server, Oracle, DB2, MySQL atd.) jsou navrženy tak, aby rychle našly „dobrý“ plán, ale prostor pro vyhledávání dramaticky roste s každou další tabulkou.

V dotazu s mnoha tabulkami se musí optimalizátor rozhodnout v jakém pořadí je spojit, jaký typ spojení použít v každém případě, kdy použít filtry a agregaceatd. Počet možných plánů roste faktoriálně nebo i hůře v závislosti na tvaru stromu spojení (lineárnější nebo rozvětvenější).

Např S přibližně 12 tabulkami můžete již eliminovat desítky miliard možných teoretických plánů. Pokud je dotaz velmi hustý, optimalizátor samozřejmě neprozkoumá všechny možnosti, ale musí rychle zúžit výběr z mnoha a někdy se usadí na kandidátovi, který není nejlepší, jednoduše proto, že nemůže věnovat více času hledání.

To neznamená, že všechny složité dotazy jsou špatné, ale znamená to, že Každá další tabulka, kterou přidáte, zvyšuje riziko, že zvolený plán nebude optimální.Navíc údržba a ladění SQL s 20, 30 nebo 40 tabulkami je noční můrou pro každý tým.

Mezi strategie pro zlepšení této situace patří: Přesouvání metadat nebo vyhledávacích tabulek k oddělení dotazů, které odesílají výsledky do dočasných tabulek, eliminace zbytečných spojení, rozdělení dotazu na několik menších a ve velmi běžných případech použití vytváření indexovaných zobrazení které předběžně vypočítají část práce.

Když rozdělíte velkou konzultaci na několik menších, musíte zajistit, aby mezi jedním a druhým nedošlo k žádným relevantním změnám dat, které by zneplatnily výsledek.To může vyžadovat použití transakcí, vhodných úrovní izolace nebo explicitních zámků, v závislosti na enginu a kritičnosti dat.

V mnoha případech je však možné reorganizovat sběr dat v menší, srozumitelnější logické jednotkyNejprve získáte podmnožinu klíčů (například ID, která splňují určité podmínky) a poté v druhém dotazu načtete podrobnosti. To vám také umožní vyčistit nepotřebné sloupce a zjednodušit logiku.

Obecné osvědčené postupy pro psaní dotazů

Kromě velmi specifických vzorců existuje řada obecných doporučení, která mají tendenci poměrně konzistentně zlepšovat výkon. Například Vyhněte se příkazu SELECT * a vyberte pouze sloupce, které skutečně potřebujete.Každý další sloupec znamená více dat přesouvaných po síti, více paměti, větší zatížení I/O a někdy brání optimálnímu využití určitých indexů.

Dalším důležitým bodem je Nepoužívejte nadměrně DISTINCT a UNION, pokud nejsou nutné.Oba operátory zahrnují operace řazení nebo deduplikace, které patří k nejnákladnějším částem dotazu. V mnoha situacích je postačující a mnohem rychlejší operace UNION ALL (která neprovádí deduplikaci).

Ohledně PŘIPOJIT SE, Je vhodnější použít INNER JOIN, když ve skutečnosti nepotřebujete „osiřelé“ řádky z jedné z tabulek.Externí spojení (LEFT/RIGHT OUTER JOIN) omezuje rozsah akcí optimalizátoru a často vede k méně efektivním plánům. Predikáty z externí tabulky by navíc měly být umístěny v klauzuli ON, nikoli v klauzuli WHERE, aby je optimalizátor mohl správně použít.

Rovněž se doporučuje duplicitní konstantní podmínky ve spojených sloupcích obou tabulek Pokud je to možné (například A.id = B.id a A.id IN (10,12) a B.id IN (10,12)). To poskytuje optimalizátoru další vodítka o rozsahu relevantních hodnot v každé tabulce a může zlepšit výběr indexu a pořadí provádění.

Klauzule ORDER BY by se měla používat pouze tehdy, když skutečně potřebujete seřadit výsledek. Bez ORDER BY není pořadí vrácené sady zaručeno.I když se to tak někdy může zdát, každá klauzule ORDER BY implikuje řazení a ve velkých sadách výsledků se může stát jedním z nejnákladnějších kroků v dotazu.

V Oracle existuje také možnost použití běžné tabulkové výrazy (CTE) a specifická syntaxe regulárních výrazů aby optimalizátor mohl vytvářet efektivnější dočasné tabulky. Přepisování určitých dotazů pomocí promyšlených CTE může optimalizátoru umožnit „vkládat“ predikáty do zobrazení, filtrovat data dříve a zmenšovat velikost následných spojení.

VYSVĚTLENÍ, statistiky a návrhy pro konzultaci (nápovědy)

Jeden nástroj, který by ve vaší sadě nástrojů neměl chybět, je EXPLAIN (a varianty jako například EXPLAIN ANALYZE). EXPLAIN ukazuje, jak engine plánuje provést dotaz.: jaké indexy použít, typ přístupu (ALL, index, ref, range…), kolik řádků odhaduje načíst, pořadí spojení atd.

S těmito informacemi můžete například zjistit, zda používá se váš zbrusu nový index zemí, nebo ne, pokud dotaz stále provádí úplné skenování tabulky (typ = ALL), pokud je počet řádků směšně vysoký nebo pokud se místo vyhledávání LIKE používá FULLTEXT.

V MySQL byste po vytvoření indexu a opětovném spuštění příkazu EXPLAIN měli vidět změna ve sloupci typu směrem k selektivnějším hodnotám (ref, range atd.) a výrazné snížení odhadu řádkůTakovéto srovnání před/po je ryzím zlatem pro kontrolu, zda vaše optimalizace funguje.

Dalším klíčovým prvkem je statistiky o rozložení hodnot (ANALYZOVAT TABULKU, automatické aktualizace statistik atd.). Optimalizátor se do značné míry rozhoduje, který plán zvolí, na základě těchto statistik; pokud jsou zastaralé, můžete skončit s velmi špatnými plány. Po velkém načítání nebo masivních změnách je vhodné statistiky explicitně aktualizovat.

Pokud jde o návrhy na konzultaci nebo rady, je rozumný postoj Používejte je jako poslední možnost a s velkou mírouNápověda je explicitní instrukce pro optimalizátor: vynucuje typ spojení (MERGE, HASH, LOOP), hodnotu parametru k optimalizaci (OPTIMIZE FOR), úroveň izolace (NOLOCK), že se plán znovu nepoužije (RECOMPILE) atd.

Problém je v tom Nápověda, která dnes vyřeší hraniční případ, se zítra může stát překážkou. Když se změní data, schéma nebo vzorce používání. Navíc mohou maskovat hlubší problémy: chybějící indexy, zbytečný objem dat, špatně navrženou obchodní logiku atd.

Některá typická varování: NOLOCK může vracet nekonzistentní data. (nečisté čtení), takže by se neměl používat tam, kde záleží na kvalitě dat; RECOMPILE u velmi častého dotazu může generovat brutální přetížení; vynucení HASH/MERGE/LOOP omezuje možnosti optimalizátoru a může ve střednědobém horizontu vést k hrozným plánům; a OPTIMIZE FOR se může stát zastaralým, jakmile se změní vzorce používání aplikace.

Rozumný způsob práce je nejprve vyčerpejte „čisté“ alternativy (vhodné indexy, přepisování SQL, úpravy parametrů, aktualizace statistik) a pouze pokud neexistuje jiné řešení, použijte velmi specifickou radu, dobře zdokumentovanou a pravidelně kontrolovanou.

Celkově vzato je optimalizace SQL dotazů kombinací důkladného pochopení fungování enginu, rozpoznávání rizikových vzorců (složité OR, špatně umístěné zástupné znaky, nadměrné množství tabulek, špatně navržené indexy) a využití nástrojů, jako je EXPLAIN a systémové pohledy, k informovanému rozhodování. Pokud zkombinujete rozumný návrh indexů, čisté dotazy a určitý úsudek, abyste se vyhnuli typickým antivzorcům, Vaše databáze budou reagovat mnohem rychleji, aniž by bylo nutné technické vybavení monstrózní ani zázračné záplaty.