AllYouNeedIsBackend

Jsem velkým fanouškem databází. Když jsem byl na univerzitě, chtěl jsem si dokonce vytvořit vlastní DBMS. Nyní pracuji jak s RDBMS, tak s řešeními NoSQL, a jsem tím velmi nadšený. Víte, žádné zlaté kladivo neexistuje, každý problém má vlastní řešení. Případně podmnožinu řešení.

V sérii blogových příspěvků The SQL I Love <3 vás provedu některými problémy řešenými pomocí SQL, které mi přišly obzvlášť zajímavé. Řešení jsou testována na tabulce s více než 100 miliony záznamů. Všechny příklady používají MySQL, ale nápady se dají aplikovat i na jiná relační datová úložiště, jako je PostgreSQL, Oracle a SQL Server.

Tato kapitola je zaměřena na efektivní skenování velké tabulky pomocí stránkování s offsetna primárním klíči. Tato metoda je také známá jako stránkování pomocí sady klíčů.

Pozadí

V kapitole použijeme pro příklad následující strukturu databáze. Kanonický příklad o uživatelích by měl vyhovovat jakékoli doméně.

CREATE TABLE `users` ( `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `external_id` varchar(32) NOT NULL, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `metadata` text COLLATE utf8_unicode_ci, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), UNIQUE KEY `uf_uniq_external_id` (`external_id`), UNIQUE KEY `uf_uniq_name` (`name`), KEY `date_created` (`date_created`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Několik poznámek ke struktuře:

• external_id sloupec ukládá odkaz na stejného uživatele v jiném systému ve formátu UUID
• name představuje Firstname Lastname
• metadata sloupec obsahuje blob JSON s nejrůznějšími nestrukturovanými daty

Tabulka je poměrně velká a obsahuje přibližně 100 000 000 záznamů. Pojďme se pustit do učení.

Skenování velké tabulky

Problém: Je třeba projít tabulku, extrahovat jednotlivé záznamy, transformovat je uvnitř kódu aplikace a vložit na jiné místo. V příspěvku se zaměříme na první fázi – skenování tabulky.

Očividné a špatné řešení

SELECT user_id, external_id, name, metadata, date_createdFROM users;

V mém případě se 100 000 000 záznamy není dotaz nikdy dokončen. DBMS ho prostě zabije. Proč? Pravděpodobně proto, že vedl k pokusu o načtení celé tabulky do paměti RAM. Před vrácením dat klientovi. Další domněnka – před načtením dat před odesláním to trvalo příliš dlouho a dotaz byl časován. Každopádně náš pokus získat všechny záznamy včas selhal. Musíme najít nějaké jiné řešení.

Řešení č. 2

Můžeme se pokusit získat data ve stránkách. Protože není zaručeno, že záznamy budou v tabulce seřazeny na fyzické nebo logické úrovni – musíme je seřadit na straně DBMS pomocí klauzule ORDER BY.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 0, 10 000;10 000 rows in set (0.03 sec)

Sladké. Funguje to. Zeptali jsme se na první stránku s 10 000 záznamy a její vrácení trvalo jen 0.03 sec. Jak by to však fungovalo pro 5000. stránku?“

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size10 000 rows in set (40.81 sec)

Jistě, je to velmi pomalé. Podívejme se, kolik času je potřeba na získání dat pro poslední stránku.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size10 000 rows in set (1 min 20.61 sec)

To je šílené. Může však být v pořádku pro řešení, která běží na pozadí. Ještě jeden skrytý problém tohoto přístupu lze odhalit, pokud se pokusíte odstranit záznam z tabulky uprostřed jejího skenování. Řekněme, že jste dokončili desátou stránku (100 000 záznamů je již navštíveno) a chystáte se skenovat záznamy mezi 100 001 a 110 000 záznamy. Záznamy 99 998 a 99 999 jsou však odstraněny před dalším SELECT provedením. V takovém případě následující dotaz vrátí neočekávaný výsledek:

 SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 100 000, 10 000; N, id, ... 1, 100 003, ... 2, 100 004, ...

Jak vidíte, dotaz přeskočil záznamy s id 100 001 a 100 002. Záznamy s id 100 001 a 100 002 byly přeskočeny. Ty kód aplikace s tímto přístupem nezpracuje, protože se po dvou operacích smazání objeví v prvních 100 000 záznamech. Proto je metoda nespolehlivá, pokud je datová sada proměnlivá.

Řešení č. 3 – pro dnešek poslední

Přístup je velmi podobný předchozímu, protože stále používá stránkování, ale nyní místo spoléhání na počet skenovaných záznamů používáme jako offset user_id posledního navštíveného záznamu.

Zjednodušený algoritmus:

1. Z tabulky získáme PAGE_SIZEpočet záznamů. Počáteční hodnota offsetu je 0.
2. Použijeme maximální vrácenou hodnotu pro user_id v dávce jako offset pro další stránku.
3. Získáme další dávku ze záznamů, které mají hodnotu user_id vyšší než aktuální offset.

Dotaz v akci pro 5 000. stránku, každá stránka obsahuje údaje o 10 000 uživatelích:

SELECT user_id, external_id, name, metadata, date_createdFROM usersWHERE user_id > 51 234 123 --- value of user_id for 50 000 000th recordORDER BY user_id ASCLIMIT 10 000;10 000 rows in set (0.03 sec)

Všimněte si, že hodnoty user_id nejsou po sobě jdoucí a mohou mít mezery, například 25 348 je hned za 25 345. Řešení funguje i v případě, že jsou smazány některé záznamy z budoucích stránek – ani v tomto případě dotaz nepřeskočí záznamy. Sladké, že?“

Vysvětlení výkonu

Pro další učení doporučuji prozkoumat výsledky EXPLAIN EXTENDED pro každou verzi dotazu, abyste získali dalších 10 000 záznamů po 50 000 000.

Soustřeďme se na klíčový rozdíl mezi prováděcími plány pro 2. a 3. řešení, protože 1. řešení není pro velké tabulky prakticky použitelné.

• Typ Join: index vs range. První z nich znamená, že se při hledání záznamů prohledává celý strom indexů. Typ range nám říká, že index se používá pouze k nalezení odpovídajících řádků v zadaném rozsahu. Typ range je tedy rychlejší než index.
• Možné klíče: NULL vs PRIMARY. Ve sloupci jsou uvedeny klíče, které může MySQL použít. BTW, při pohledu do sloupce klíčů vidíme, že nakonec je pro oba dotazy použit klíč PRIMARY.
• Řádky: 50 010 000 vs 50 000 000. Hodnota zobrazuje počet analyzovaných záznamů před vrácením výsledku. U 2. dotazu hodnota závisí na tom, jak hluboké je naše rolování. Pokud se například snažíme získat další 10 000 záznamů po 9999. stránce, pak se zkoumá 99 990 000 záznamů. Naopak 3. dotaz má konstantní hodnotu; nezáleží na tom, zda načítáme data pro 1. stránku úplně poslední. Vždy se jedná o polovinu velikosti tabulky.
• Filtrováno: 200.00 vs 100.00. Sloupec udává odhadované procento tabulky, které má být před zpracováním filtrováno. Mít vyšší hodnotu je lepší. Hodnota 100.00 znamená, že dotaz prochází celou tabulku. U 2. dotazu není hodnota konstantní a závisí na čísle stránky: pokud se ptáme na 1. stránku, hodnota filtrovaného sloupce by byla 1000000.00. Pro úplně poslední stránku by byla 100.00.
• Extra: NULL vs Using where. Poskytuje další informace o tom, jak MySQL řeší dotaz. Použití WHERE na klíči PRIMARY zrychluje provádění dotazu.

Předpokládám, že typ spojení je parametr dotazu, který nejvíce přispěl k výkonu, aby byl 3. dotaz rychlejší. Další důležitou věcí je, že 2. dotaz je extrémně závislý na počtu stránek k procházení. Hlubší stránkování je v tomto případě pomalejší.

Další návod, jak pochopit výstup příkazu EXPLAIN, najdete v oficiální dokumentaci k vašemu RDBMS.

Shrnutí

Hlavní téma příspěvku na blogu se týkalo skenování velké tabulky se 100 000 000 záznamy pomocí offset s primárním klíčem (stránkování podle klíče). Celkem byly přezkoumány 3 různé přístupy, které byly testovány na odpovídajícím souboru dat. Pokud potřebujete skenovat proměnlivou velkou tabulku, doporučuji pouze jeden z nich.

Také jsme revidovali použití příkazu EXPLAIN EXTENDED pro analýzu plánu provádění dotazů MySQL. Jsem si jist, že i jiné RDBMS mají pro tuto funkci obdobu.

V příští kapitole budeme věnovat pozornost agregaci dat a optimalizaci ukládání. Zůstaňte naladěni!

Jaká je vaše metoda skenování velkých tabulek?

Pamatujete si na nějaký další účel použití stránkování sad klíčů jako v řešení č. 3?

.