Hraní kostek v databázi

cs in code

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.





You may also like