Pryč jsou doby, kdy jste se nemuseli starat o optimalizaci výkonu databáze. Čas se nezastaví. Každý nový technologický podnikatel chce vytvořit další Facebook a zároveň se snaží shromáždit všechna data, která se jim dostane pod ruku. Firmy potřebují tato data k lepšímu trénování modelů, které jim pomohou vydělávat peníze. V takových podmínkách potřebují programátoři vytvořit API, která jim umožní rychle a spolehlivě pracovat s obrovským množstvím informací.
Pokud jste navrhovali aplikační nebo databázové backendy jakkoli dlouho, pravděpodobně jste napsali kód pro spouštění stránkovaných dotazů. Například takto:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Tak jak to je?
Ale pokud jste stránkování provedli takto, je mi líto, že jste to neudělali tím nejefektivnějším způsobem.
Chceš proti mně něco namítat? . , и Už používají techniky, o kterých chci dnes mluvit.
Uveďte alespoň jednoho backendového vývojáře, který nikdy nepoužil OFFSET и LIMIT provádět stránkované dotazy. V MVP (Minimum Viable Product) a v projektech, kde se používá malé množství dat, je tento přístup docela použitelný. Tak říkajíc „prostě funguje“.
Pokud však potřebujete vytvořit spolehlivé a efektivní systémy od začátku, měli byste se předem postarat o efektivitu dotazování v databázích používaných v takových systémech.
Dnes si povíme o problémech s běžně používanými (škoda) implementacemi stránkovaných dotazovacích strojů a o tom, jak dosáhnout vysokého výkonu při provádění takových dotazů.
Co je špatného na OFFSET a LIMIT?
Jak již bylo řečeno, OFFSET и LIMIT Dobře fungují v projektech, které nepotřebují pracovat s velkým množstvím dat.
Problém nastává, když databáze naroste do takové velikosti, že se již nevejde do paměti serveru. Při práci s touto databází je však potřeba používat stránkované dotazy.
Aby se tento problém projevil, musí nastat situace, kdy se DBMS u každého stránkovaného dotazu uchýlí k neefektivní operaci Full Table Scan (zatímco může dojít k operacím vkládání a mazání a nepotřebujeme zastaralá data!).
Co je „skenování celé tabulky“ (nebo „sekvenční skenování tabulky“, sekvenční skenování)? Jedná se o operaci, během které DBMS sekvenčně čte každý řádek tabulky, tedy data v něm obsažená, a kontroluje je, zda splňují danou podmínku. Tento typ skenování tabulek je známý jako nejpomalejší. Faktem je, že při jeho spuštění se provede mnoho vstupních/výstupních operací, které zahrnují diskový subsystém serveru. Situaci zhoršuje latence spojená s prací s daty uloženými na discích a skutečnost, že přenos dat z disku do paměti je operace náročná na zdroje.
Máte například záznamy o 100000000 XNUMX XNUMX uživatelích a spustíte dotaz s konstrukcí OFFSET 50000000. To znamená, že DBMS bude muset načíst všechny tyto záznamy (a my je ani nepotřebujeme!), uložit je do paměti a poté vzít řekněme 20 výsledků hlášených v LIMIT.
Řekněme, že by to mohlo vypadat takto: „vyberte řádky od 50000 50020 do 100000 50000 z XNUMX XNUMX“. To znamená, že systém bude muset k dokončení dotazu nejprve načíst XNUMX XNUMX řádků. Vidíte, kolik zbytečné práce bude muset udělat?
Pokud mi nevěříte, podívejte se na příklad, který jsem vytvořil pomocí funkcí .
Příklad na db-fiddle.com
Tam, vlevo, v poli Schema SQL, existuje kód, který vloží 100000 XNUMX řádků do databáze a vpravo do pole Query SQL, zobrazí se dva dotazy. První, pomalý, vypadá takto:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
A druhý, který je účinným řešením stejného problému, je tento:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Pro splnění těchto požadavků stačí kliknout na tlačítko Run v horní části stránky. Poté porovnáme informace o době provádění dotazu. Ukazuje se, že provedení neefektivního dotazu trvá nejméně 30krát déle než provedení druhého (tato doba se liší běh od běhu; systém může například hlásit, že dokončení prvního dotazu trvalo 37 ms, ale provedení sekunda - 1 ms).
A pokud bude více údajů, bude vše vypadat ještě hůř (abyste se o tom přesvědčili, podívejte se na můj s 10 miliony řádků).
To, co jsme právě probrali, by vám mělo poskytnout určitý přehled o tom, jak jsou databázové dotazy skutečně zpracovávány.
Upozorňujeme, že čím vyšší je hodnota OFFSET — tím déle bude trvat vyřízení žádosti.
Co mám použít místo kombinace OFFSET a LIMIT?
Místo kombinace OFFSET и LIMIT Vyplatí se použít strukturu postavenou podle následujícího schématu:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Toto je provádění dotazu se stránkováním na základě kurzoru.
Místo ukládání aktuálních lokálně OFFSET и LIMIT a přenést je s každou žádostí, musíte uložit poslední přijatý primární klíč (obvykle to je ID) A LIMITv důsledku toho budou získány dotazy podobné výše uvedeným.
Proč? Jde o to, že explicitním uvedením identifikátoru posledního přečteného řádku sdělíte svému DBMS, kde má začít hledat potřebná data. Navíc vyhledávání díky použití klíče bude probíhat efektivně, systém nebude muset být rozptylován linkami mimo zadaný rozsah.
Podívejme se na následující srovnání výkonu různých dotazů. Zde je neúčinný dotaz.
Pomalý požadavek
A zde je optimalizovaná verze tohoto požadavku.
Rychlá žádost
Oba dotazy vracejí přesně stejné množství dat. Ale dokončení prvního trvá 12,80 sekundy a druhého 0,01 sekundy. Cítíte ten rozdíl?
Možné problémy
Aby navrhovaná metoda dotazu fungovala efektivně, musí mít tabulka sloupec (nebo sloupce) obsahující jedinečné sekvenční indexy, jako je celočíselný identifikátor. V některých specifických případech to může určovat úspěšnost použití takových dotazů ke zvýšení rychlosti práce s databází.
Při konstrukci dotazů je samozřejmě potřeba vzít v úvahu specifickou architekturu tabulek a vybrat ty mechanismy, které budou nejlépe fungovat na stávajících tabulkách. Pokud například potřebujete pracovat v dotazech s velkými objemy souvisejících dat, může vás to zajímat článek.
Pokud se potýkáme s problémem chybějícího primárního klíče, například pokud máme tabulku se vztahem many-to-many, pak tradiční přístup používání OFFSET и LIMIT, nám bude zaručeně vyhovovat. Jeho použití však může mít za následek potenciálně pomalé dotazy. V takových případech bych doporučil použít automaticky se zvyšující primární klíč, i když je potřeba pouze pro zpracování stránkovaných dotazů.
Pokud vás toto téma zajímá - , и - několik užitečných materiálů.
Výsledky
Hlavním závěrem, který můžeme vyvodit, je, že bez ohledu na to, o jaké velikosti databází mluvíme, je vždy nutné analyzovat rychlost provádění dotazu. V dnešní době je škálovatelnost řešení nesmírně důležitá a pokud je vše správně navrženo od samého začátku práce na určitém systému, může to v budoucnu vývojáře ušetřit mnoha problémů.
Jak analyzujete a optimalizujete databázové dotazy?
Zdroj: www.habr.com