ORDER BY RAND() je zlo

6. 6. 2011 9:52 (aktualizováno) | Tomas Matějíček

V nesčetném množství MySQL tutoriálů a manuálů je uvedeno jako příklad náhodného výběru řádku následující:

SELECT * FROM tbl ORDER BY RAND() LIMIT 1

S malými tabulkami žádný problém, ale podívejme se na EXPLAIN takového dotazu:

mysql: EXPLAIN select * from geoip order by rand() limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tbl type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 146219432 Extra: Using temporary; Using filesort

Bez ohledu na existenci jakýchkoli klíčů v tabulce tbl, výsledek je vždy ten samý: MySQL setřídí celou tabulku dle náhodné hodnoty vygenerované pro každý řádek, a pak z nich vybere jeden nejmenší. Zvlášť alarmující pro nás jsou poslední dva údaje z explain, tedy rows: 146219432 (zde vidíme, že MySQL musí analyzovat veškeré řádky tabulky) a Extra: Using temporary; Using filesort (vytváření dočasných souborů na disku je to nejhorší co vás může u mysql kdy potkat). Výběr jednoho náhodného řádku je tak přímo úměrný počtu záznamů v tabulce, a může trvat i minuty. Katastrofa.

Jak vybrat z tabulky náhodný řádek lépe a hlavně rychleji?
Obávám se, že úplně univerzální řešení neznám.
Ale za určitých počátečních předpokladů je optimalizace velmi účinná.

Požadavky na tabulku tbl:
- obsahuje unikátní auto increment ID identifikátor jako PRIMARY KEY
- z tabulky se příliš nemaže, tzn ID identifikátory jdou za sebou a nemají mezi sebou moc „děr“
- maximální ID identifikátor je přibližně roven počtu řádků tabulky

Princip optimalizace je celkem jednoduchý. Zjistit (přibližný) počet řádků v tabulce, vynásobit ho náhodnou hodnoutou mezi 0 a 1, tím získat přibližné ID náhodného řádku, a tento pak jednoduše vybrat podle primárního klíče.

SELECT @r:=RAND(); SELECT @i:= (SELECT MIN(id) FROM tbl WHERE id>= (SELECT (@r*(SELECT MAX(id) FROM tbl)))); SELECT * FROM tbl WHERE id=@i;

Takto formulovaný dotaz provede výběr náhodného řádku v rychlosti, nezávisle na počtu řádků v tabulce tbl. Pokud jsou v tabulce tbl často mazány řádky, nebude náhodný výběr úplně náhodný, pravděpodobnost výběru řádku těsně před „dírou“ v posloupnosti ID vlivem smazaných řádků bude právě tolikrát vyšší, kolik smazaných řádků za ním následuje. V praktickém životě je to ale obvykle fuk.

A ještě výstup explain (zkráceně) pro zvídavé:

mysql: explain SELECT @r:=RAND()\G *************************** 1. row *************************** :: Extra: No tables used mysql: SELECT @i:= (SELECT MIN(id) FROM tbl WHERE id>= (SELECT (@r*(SELECT MAX(id) FROM tbl))))\G *************************** 1. row *************************** :: Extra: No tables used *************************** 2. row *************************** :: select_type: UNCACHEABLE SUBQUERY Extra: Select tables optimized away *************************** 3. row *************************** :: select_type: SUBQUERY Extra: Select tables optimized away mysql: explain SELECT * FROM tbl WHERE id=@i\G *************************** 1. row *************************** :: type: const possible_keys: PRIMARY key: PRIMARY ref: const rows: 1 Extra:

Pro úplnost ještě jedna možnost, jak jde náhodný řádek z tabulky vybrat, a to díky prepared statements (v MySQL někdy od verze 4.1). Ovšem nemám praktické ani teoretické zkušenosti s performance takového dotazu, a pomocí explain se to moc analyzovat nedá:

SET @randrow:=FLOOR((SELECT COUNT(*) FROM tbl)*RAND()); PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, 1'; EXECUTE STMT USING @randrow;
DigiZone.cz: Nova opět stahuje „milionáře“

Nova opět stahuje „milionáře“

DigiZone.cz: Test LG 55UH750V aneb Cena/výkon

Test LG 55UH750V aneb Cena/výkon

Vitalia.cz: Inspekce našla nelegální sklad v SAPĚ. Zase

Inspekce našla nelegální sklad v SAPĚ. Zase

DigiZone.cz: Rapl: seriál, který vás smíří s ČT

Rapl: seriál, který vás smíří s ČT

Lupa.cz: Patička e-mailu závazná jako vlastnoruční podpis?

Patička e-mailu závazná jako vlastnoruční podpis?

DigiZone.cz: Ginx TV: pořad o počítačových hráčích

Ginx TV: pořad o počítačových hráčích

DigiZone.cz: DVB-T2 ověřeno: seznam TV zveřejněn

DVB-T2 ověřeno: seznam TV zveřejněn

Vitalia.cz: Tohle jsou nejlepší česká piva podle odborníků

Tohle jsou nejlepší česká piva podle odborníků

Vitalia.cz: Tahák, jak vyzrát nad zápachem z úst

Tahák, jak vyzrát nad zápachem z úst

Vitalia.cz: dTest odhalil ten nejlepší kečup

dTest odhalil ten nejlepší kečup

Lupa.cz: Jak levné procesory změnily svět?

Jak levné procesory změnily svět?

Lupa.cz: Jak se prodává firma za miliardu?

Jak se prodává firma za miliardu?

Vitalia.cz: Muž, který miluje příliš. Ženám neimponuje

Muž, který miluje příliš. Ženám neimponuje

DigiZone.cz: Parlamentní listy: kde končí PR...

Parlamentní listy: kde končí PR...

DigiZone.cz: Funbox 4K v DVB-T2 má ostrý provoz

Funbox 4K v DVB-T2 má ostrý provoz

Podnikatel.cz: Takhle se prodávají mražené potraviny

Takhle se prodávají mražené potraviny

DigiZone.cz: Numan Two: rozhlasový přijímač s CD

Numan Two: rozhlasový přijímač s CD

Podnikatel.cz: EET pro e-shopy? Postavené na hlavu

EET pro e-shopy? Postavené na hlavu

DigiZone.cz: Světový pohár v přímém přenosu na ČT

Světový pohár v přímém přenosu na ČT

Lupa.cz: Hackeři mají data z půlmiliardy účtů Yahoo

Hackeři mají data z půlmiliardy účtů Yahoo