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;
Lupa.cz: Hrajeme si s IoT - i v jedoucím autě

Hrajeme si s IoT - i v jedoucím autě

Měšec.cz: Udali ho na nelegální software a přišla Policie

Udali ho na nelegální software a přišla Policie

120na80.cz: Tipy pro odvodnění organismu

Tipy pro odvodnění organismu

Měšec.cz: Banky umí platby na kartu, jen to neříkají

Banky umí platby na kartu, jen to neříkají

Měšec.cz: Co s reklamací, když e-shop krachuje?

Co s reklamací, když e-shop krachuje?

Vitalia.cz: Je kočka riziko pro těhotnou ženu?

Je kočka riziko pro těhotnou ženu?

Vitalia.cz: Klíšťata letos řádí, skvrna se udělá jen někomu

Klíšťata letos řádí, skvrna se udělá jen někomu

Podnikatel.cz: Polská vejce na českém pultu Albertu

Polská vejce na českém pultu Albertu

Podnikatel.cz: 3 velké průšvihy obchodních řetězců

3 velké průšvihy obchodních řetězců

Vitalia.cz: Sobotní masakr žrádla, chlastu a zábavy

Sobotní masakr žrádla, chlastu a zábavy

Podnikatel.cz: Účtenky v rámci EET? Klidně emailem

Účtenky v rámci EET? Klidně emailem

DigiZone.cz: Kauza technik: oficiální vyjádření Novy

Kauza technik: oficiální vyjádření Novy

Měšec.cz: Kurzy platebních karet: vyplatí se platit? (TEST)

Kurzy platebních karet: vyplatí se platit? (TEST)

Měšec.cz: Investice do drahých kovů - znáte základní chyby?

Investice do drahých kovů - znáte základní chyby?

Podnikatel.cz: Nereaguje na výzvu ČOIky, zaplatí milion

Nereaguje na výzvu ČOIky, zaplatí milion

Lupa.cz: V Praze se otevřel první podnik s virtuální realitou

V Praze se otevřel první podnik s virtuální realitou

Lupa.cz: eIDAS: Nepřehnali jsme to s výjimkami?

eIDAS: Nepřehnali jsme to s výjimkami?

Vitalia.cz: Signál roztroušené sklerózy: brnění končetin

Signál roztroušené sklerózy: brnění končetin

Lupa.cz: Japonská invaze. Proč SoftBank kupuje ARM?

Japonská invaze. Proč SoftBank kupuje ARM?

Vitalia.cz: Pepsi Cola mění sirup za cukr

Pepsi Cola mění sirup za cukr