v kategorii 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.
kanálu