Programování

Unleashed SQL: 17 způsobů, jak zrychlit vaše dotazy SQL

Vývojáři SQL na každé platformě se potýkají, zdánlivě uvízli v DĚLAT, ZATÍMCO smyčka, která je nutí opakovat stejné chyby znovu a znovu. Je to proto, že pole databáze je stále relativně nezralé. Jistě, prodejci dělají nějaké kroky, ale stále se potýkají s většími problémy. Souběžnost, správa zdrojů, správa prostoru a rychlost stále trápí vývojáře SQL, ať už kódují na SQL Server, Oracle, DB2, Sybase, MySQL nebo na jakékoli jiné relační platformě.

Část problému spočívá v tom, že neexistuje žádná kouzelná kulka a pro téměř každý nejlepší postup vám mohu ukázat alespoň jednu výjimku. Vývojář obvykle najde své vlastní oblíbené metody - i když obvykle neobsahují žádné konstrukty pro výkon nebo souběžnost - a neobtěžuje se zkoumat další možnosti. Možná je to příznak nedostatečného vzdělání, nebo jsou vývojáři příliš blízko procesu, aby rozpoznali, když dělají něco špatně. Možná dotaz běží dobře na místní sadě testovacích dat, ale ve výrobním systému selže.

Neočekávám, že se vývojáři SQL stanou správci, ale při psaní kódu musí brát v úvahu produkční problémy. Pokud to neudělají během počátečního vývoje, DBA je prostě přimějí, aby se vrátili a udělali to později - a uživatelé zatím trpí.

Existuje důvod, proč říkáme, že vyladění databáze je umění i věda. Je to proto, že existuje velmi málo přísných pravidel, která platí plošně. Problémy, které jste vyřešili v jednom systému, nejsou problémy v jiném systému a naopak. Pokud jde o ladění dotazů, neexistuje správná odpověď, ale to neznamená, že byste se měli vzdát.

Můžete se řídit některými dobrými zásadami, které by měly přinést výsledky v té či oné kombinaci. Zapouzdřil jsem je do seznamu SQL dos a nedělá, co je často přehlíženo nebo je těžko rozpoznatelné. Tyto techniky by vám měly poskytnout trochu lepší vhled do myslí vašich DBA, stejně jako schopnost začít myslet na procesy výrobně orientovaným způsobem.

1. Nepoužívejte AKTUALIZACE namísto PŘÍPAD

Tento problém je velmi běžný, a ačkoli to není těžké odhalit, mnoho vývojářů jej často přehlíží, protože používá AKTUALIZACE má přirozený dluh, který vypadá logicky.

Vezměte si například tento scénář: Vkládáte data do dočasné tabulky a potřebujete ji k zobrazení určité hodnoty, pokud existuje jiná hodnota. Možná táhnete od tabulky zákazníků a chcete, aby byl kdokoli s objednávkami nad 100 000 $ označen jako „Preferovaný“. Vložíte tedy data do tabulky a spustíte AKTUALIZACE prohlášení pro nastavení sloupce CustomerRank na „Preferováno“ pro každého, kdo má v objednávkách více než 100 000 USD. Problém je v tom, že AKTUALIZACE výpis je protokolován, což znamená, že musí zapisovat dvakrát za každý jeden zápis do tabulky. Způsob, jak to obejít, je samozřejmě použít inline PŘÍPAD v samotném dotazu SQL. Tím se testuje u každého řádku podmínka množství objednávky a před zápisem do tabulky se nastaví štítek „Preferovaný“. Zvýšení výkonu může být ohromující.

2. Nepoužívejte slepě opakovaně kód

Tento problém je také velmi častý. Kopírování kódu někoho jiného je velmi snadné, protože víte, že získává data, která potřebujete. Problém je v tom, že poměrně často stáhne mnohem více dat, než potřebujete, a vývojáři se zřídka obtěžují ořezávat je, takže skončí s obrovskou nadmnožinou dat. To obvykle přichází ve formě zvláštního vnějšího spojení nebo zvláštního stavu v KDE doložka. Můžete získat obrovské zvýšení výkonu, pokud upravíte znovu použitý kód podle svých přesných potřeb.

3. Vytáhněte pouze požadovaný počet sloupců

Toto vydání je podobné číslu 2, ale je specifické pro sloupce. Je až příliš snadné kódovat všechny vaše dotazy VYBRAT * namísto samostatného výpisu sloupců. Problém je znovu v tom, že získává více dat, než potřebujete. Tuto chybu jsem viděl desítky a desítkykrát. Vývojář dělá a VYBRAT * Dotaz na tabulku se 120 sloupci a miliony řádků, ale nakonec se použije pouze tři až pět z nich. V tomto okamžiku zpracováváte mnohem více dat, než potřebujete, je divu, že se dotaz vůbec vrátí. Zpracováváte nejen více dat, než potřebujete, ale také berete zdroje jiným procesům.

4. Nenamáčejte to dvakrát

Tady je další, který jsem viděl vícekrát, než jsem měl: Uložená procedura je zapsána, aby vytáhla data z tabulky se stovkami milionů řádků. Vývojář potřebuje zákazníky, kteří žijí v Kalifornii a mají příjmy přes 40 000 USD. Dotáže se tedy na zákazníky, kteří žijí v Kalifornii, a výsledky umístí do dočasné tabulky; poté se zeptá zákazníků s příjmy nad 40 000 $ a umístí tyto výsledky do jiné dočasné tabulky. Nakonec se připojí k oběma stolům a získá finální produkt.

Děláš si ze mě srandu? To by mělo být provedeno v jediném dotazu; místo toho dvakrát namáčíte velkou tabulku. Nebuďte blázni: Dotazujte se na velké tabulky pouze jednou, kdykoli je to možné - zjistíte, o kolik lépe fungují vaše postupy.

Trochu odlišný scénář je, když je podmnožina velké tabulky potřebná několika kroky v procesu, což způsobí, že bude velká tabulka dotazována pokaždé. Tomu se vyhnete dotazováním na podmnožinu a přetrváváním jinde a následným krokem směřujete k vaší menší datové sadě.

6. Proveďte předstupňová data

Toto je jedno z mých oblíbených témat, protože je to stará technika, která je často přehlížena. Pokud máte sestavu nebo postup (nebo ještě lépe jejich sadu), který provede podobné spojení s velkými tabulkami, může to být pro vás výhoda předem připravit data tak, že se k tabulkám připojíte předem a přetrváváte do stolu. Nyní mohou sestavy běžet proti této předem připravené tabulce a vyhnout se velkému spojení.

Tuto techniku ​​nemůžete vždy použít, ale pokud můžete, zjistíte, že je to vynikající způsob, jak ušetřit prostředky serveru.

Všimněte si, že mnoho vývojářů tento problém s připojením obchází tím, že se soustředí na samotný dotaz a vytvoří kolem připojení pouze zobrazení, aby nemuseli znovu a znovu zadávat podmínky připojení. Ale problém s tímto přístupem je, že dotaz stále běží pro každou sestavu, která to potřebuje. Předběžným uspořádáním dat spustíte spojení pouze jednou (řekněme 10 minut před sestavami) a všichni ostatní se velkému spojení vyhnou. Nemohu vám říci, jak moc miluji tuto techniku; ve většině prostředí existují populární tabulky, které se neustále připojují, takže není důvod, proč je nelze předem připravit.

7. Provádějte mazání a aktualizaci v dávkách

Tady je další snadná technika, která je hodně přehlížena. Odstranění nebo aktualizace velkého množství dat z velkých tabulek může být noční můrou, pokud to neděláte správně. Problém je v tom, že oba tyto příkazy běží jako jedna transakce, a pokud je potřebujete zabít nebo pokud se něco stane se systémem, zatímco oni pracují, musí systém vrátit celou transakci zpět. To může trvat velmi dlouho. Tyto operace mohou také blokovat další transakce po dobu jejich trvání, což v zásadě zužuje systém.

Řešením je mazat nebo aktualizovat v menších dávkách. To váš problém vyřeší několika způsoby. Nejprve, pokud je transakce z jakéhokoli důvodu zabita, má pouze malý počet řádků, které se mají vrátit, takže se databáze vrátí mnohem rychleji online. Zadruhé, zatímco menší dávky se zavádějí na disk, ostatní se mohou vplížit a dělat nějakou práci, takže souběžnost je výrazně vylepšena.

V tomto duchu mnoho vývojářů uvízlo v hlavách, že tyto operace mazání a aktualizace musí být dokončeny ve stejný den. To není vždy pravda, zvláště pokud archivujete. Tuto operaci můžete natáhnout, jak dlouho budete potřebovat, a menších dávek to pomůže dosáhnout. Pokud vám tyto intenzivní operace mohou trvat déle, věnujte čas navíc a nepřerušujte práci systému.

8. Využijte dočasné tabulky ke zlepšení výkonu kurzoru

Doufám, že už všichni víme, že je nejlepší držet se od kurzorů, pokud je to možné. Kurzory trpí nejen problémy s rychlostí, což samo o sobě může být problémem mnoha operací, ale také mohou způsobit, že vaše operace zablokuje jiné operace na mnohem delší dobu, než je nutné. To výrazně snižuje souběžnost ve vašem systému.

Pouhým kurzorům se však nemůžete vždy vyhnout, a když nastanou tyto časy, můžete se dostat pryč od problémů s výkonem vyvolaných kurzorem tím, že místo toho provedete operace kurzoru proti dočasné tabulce. Vezměte si například kurzor, který prochází tabulkou a aktualizuje několik sloupců na základě některých výsledků srovnání. Místo toho, abyste provedli srovnání s aktivní tabulkou, můžete tato data vložit do dočasné tabulky a místo toho provést srovnání. Pak máte jeden AKTUALIZACE prohlášení proti živému stolu, který je mnohem menší a drží zámky jen na krátkou dobu.

Sniping vaše úpravy dat, jako je tento, může výrazně zvýšit souběžnost. Na závěr řeknu, že téměř nikdy nebudete muset používat kurzor. Téměř vždy existuje řešení založené na sadě; musíte se to naučit vidět.

9. Nevkládejte pohledy

Pohledy mohou být pohodlné, ale při jejich používání musíte být opatrní. Zatímco pohledy mohou pomoci zakrýt velké dotazy od uživatelů a standardizovat přístup k datům, můžete se snadno dostat do situace, kdy máte pohledy, které volají pohledy, které volají pohledy, které volají pohledy. Tomu se říká vnořené pohledy, a to může způsobit vážné problémy s výkonem, zejména dvěma způsoby:

  • Zaprvé budete mít velmi pravděpodobně vracet mnohem více dat, než potřebujete.
  • Zadruhé, optimalizátor dotazů se vzdá a vrátí plán špatných dotazů.

Kdysi jsem měl klienta, který miloval vnořené pohledy. Klient měl jeden pohled, který používal téměř pro všechno, protože měl dvě důležitá spojení. Problém byl v tom, že pohled vrátil sloupec s 2 MB dokumenty v něm. Některé dokumenty byly ještě větší. Klient tlačil alespoň 2 MB navíc v síti pro každý řádek v téměř každém dotazu, který spustil. Přirozeně byl výkon dotazů propastný.

A žádný z dotazů ve skutečnosti tento sloupec nepoužíval! Sloup byl samozřejmě pohřben v hloubce sedmi pohledů, takže i zjištění bylo obtížné. Když jsem odstranil sloupec dokumentu z pohledu, čas pro největší dotaz šel z 2,5 hodiny na 10 minut. Když jsem konečně rozluštil vnořené pohledy, které měly několik zbytečných spojení a sloupců, a napsal prostý dotaz, čas pro stejný dotaz klesl na podsekundy.