Hraní kostek v databázi

cs v kategorii code • 4 min. čtení
Mind the age! Most likely, its content is outdated. Especially if it’s technical.

Jednou jsem řešil získání náhodného záznamu z databáze. Moc jsem se nad tím nezamýšlel – jednoduše jsem si vytáhl všechny záznamy, z kterých chci vybírat, a vybral náhodný index. Použil jsem to s databází MongoDB, kde zatím ani random není podporováno, takže mi vlastně ani jiná možnost nezbyla. Mohl jsem si takové naivní řešení dovolit z důvodu jistoty malé kolekce, která nebude růst. (Kód.)

V nedávné době jsem se dostal k zajímavému problému – jak něco takového udělat v tabulce, která bude obrovská a bude se i nadále zvětšovat? Řešení jsem nakonec nepotřeboval, ale nedalo mi to…

Získat si veškerá data do aplikace, z nich vybrat jeden záznam a zbytek zahodit může znamenat pád aplikace kvůli nedostatku paměti. Zkusil jsem tedy vzít funkci RAND a nějak sestavit dotaz. První co jsem měl bylo:

SELECT * FROM table ORDER BY RAND() LIMIT 1

Překvapilo mne však náročnost takového dotazu. Použil jsem tedy EXPLAIN a podíval se, co se děje. Samozřejmě – sortování celé tabulky bez indexu a vyhodnocením funkce RAND pro každý řádek. Sice je to lepší řešení, protože už mi nebude padat aplikace kvůli nedostatku paměti, ale efektivní to stále není. Navíc já chci jen jeden záznam – nepotřebuji seřadit celou tabulku, abych zahodil vše, krom prvního záznamu.

Začal jsem hledat na internetu, jestli je nějaká efektivní cesta, aby se používal index. Našel jsem řešení přes JOIN – zjistit si rozmezí IDček, vybrat jedno náhodné a to propojit s mojí tabulkou.

SELECT t1.* FROM table AS t1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM table)) AS rand_id) AS t2 ON t1.id = t2.rand_id

Efektivní, trochu nejasné na první pohled a hlavně nesmí být v tabulce díra. Musí existovat všechna IDčka. Tedy ne úplně vhodné řešení. S myšlenkou, že v části JOIN se funkce RAND vyhodnocuje pouze jednou, lze vymyslet další řešení, která problém s chybějícími záznamy vyřeší. Osobně mi to ale přijde takové řešení těžce čitelné. Bez popisu by mi chvíli trvalo zjistit, co takový dotaz dělá.

Zkusil jsem tedy pokračovat. MySQL a PostgreSQL mají OFFSET, tak mě napadlo využít ten.

SELECT * FROM table LIMIT (SELECT CEIL(RAND() * COUNT(*)) FROM table), 1

Bohužel takový dotaz nebude fungovat, subquery nelze v klauzuli LIMIT použít. Musíme tedy dotaz rozdělit na dvě části – nejprve vypočítat OFFSET a poté provést SELECT. Je možné tyto dvě operace provést jedním dotazem, otázkou však je, zda není lepší použít předchozí řešení s JOINem.

To mi však stále nestačilo. Hledáním jsem narazil na knihu SQL antipatterns. Jelikož je od The Pragmatic Programmers, obsahuje kapitolu o této problematice a další zajímavé kapitoly, přidal jsem si ji na wishlist a nedávno mi přišla domů. Z knihy jsem se už nic nového k hledání náhodných dat nedozvěděl; jsou tam podobné ukázky v jiném pořadí.

Pomohly mi ale jiné závěry z jiných kapitol. Například v kapitole o Spaghetti Query se dozvíte, že není dobré se snažit vytáhnout vše jedním monstrózním dotazem. Bude těžce čitelný pro vás, bude těžké ho udržovat a rozšiřovat a databáze může mít problémy ho správně zoptimalizovat. V kapitole o fulltextu se zase píše o použití správných nástrojů na správnou věc, nesnažit se za každou cenu použít databázi.

Proto kdybych dnes měl v aplikaci z velké tabulky tahat náhodná data, napsal bych dva dotazy.






1 reakce

u projektu, kde bylo potřeba náhodně vybírat několik položek z desítek milionů záznamů v podstatě při každé interakci jsem použil řešení s indexovou tabulkou.

Tabulka obsahovala pouze cizí klíč a číslo řádku pro náhodný výběr (AI bez díry). Tabulka byla generovaná a průběžně doplňovaná. Nemusel se řešit problém s dírami v primární tabulce a náhodný výběr x záznamů šel zajistit přes joiny.

Použití OFFSET má svoje velké úskalí. Databáze musí v takovém případě načíst data pro všechny předchozí řádky. Mysql třeba neumí na offset použít index, takže vše musí přečíst z disku. V případě velkých tabulek je to velice neefektivní řešení, u malých je to v pořádku.





Může se vám také líbit

en Makefile with Python, November 6, 2017
en Fast JSON Schema for Python, October 1, 2018
en Deployment of Python Apps, August 15, 2018
cs Jasně, umím Git…, August 6, 2014
cs Checklist na zabezpečení webových aplikací, March 1, 2016

Další články z kategorie code.
Nenechte si ujít nové články díky Atom/RSS kanálu.



Poslední příspěvky

en Canon EOS 6D or 90D?, December 7, 2021 in reviews
en Arduino Build: Page Turner, November 29, 2021 in code
en Can We Be More Tolerant?, November 24, 2021 in humanity
cs Lorem ipsum recept, November 15, 2021 in family
cs Zápisky z cest: České Švýcarsko, November 8, 2021 in travel