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: Olympiáda zakázala GIFy. Moc to nepomáhá

Olympiáda zakázala GIFy. Moc to nepomáhá

Vitalia.cz: Za její cukrovkou stojí rodiče

Za její cukrovkou stojí rodiče

Měšec.cz: Ceny PHM v Evropě. Finty na úspory

Ceny PHM v Evropě. Finty na úspory

Vitalia.cz: Očkování je nutné, říká homeopatka

Očkování je nutné, říká homeopatka

Vitalia.cz: „Sjíždět“ porno není bez rizika

„Sjíždět“ porno není bez rizika

Podnikatel.cz: OSA zdraží, ale taky přidá nový poplatek

OSA zdraží, ale taky přidá nový poplatek

Root.cz: Xiaomi má vlastní notebook podobný Macu

Xiaomi má vlastní notebook podobný Macu

Lupa.cz: Kdo vykrádá LinkedIn? Zjistit to má soud

Kdo vykrádá LinkedIn? Zjistit to má soud

Měšec.cz: Co když na dovolené přijdete o kartu?

Co když na dovolené přijdete o kartu?

Měšec.cz: TEST: Vyzkoušeli jsme pražské taxikáře

TEST: Vyzkoušeli jsme pražské taxikáře

120na80.cz: Kam umístit silikony?

Kam umístit silikony?

Lupa.cz: Měřičům síly hesla se nedá věřit. Víte proč?

Měřičům síly hesla se nedá věřit. Víte proč?

Lupa.cz: Samořídicí taxíky jsou tu. Začíná s nimi Uber

Samořídicí taxíky jsou tu. Začíná s nimi Uber

Lupa.cz: Elektronika tajemství zbavená. Jak s ní začít?

Elektronika tajemství zbavená. Jak s ní začít?

DigiZone.cz: AXN u FreeSatu měsíc zdarma

AXN u FreeSatu měsíc zdarma

120na80.cz: Lepší poporodní sexuální život? Žádný problém

Lepší poporodní sexuální život? Žádný problém

Lupa.cz: Co vzal čas: internetové kavárny a herny

Co vzal čas: internetové kavárny a herny

Podnikatel.cz: Česká pošta vycouvala ze služby ČP Cloud

Česká pošta vycouvala ze služby ČP Cloud

Měšec.cz: Se stavebkem k soudu už (většinou) nemusíte

Se stavebkem k soudu už (většinou) nemusíte

Podnikatel.cz: Kauza z Vinohrad pokračuje. Policie se omlouvá

Kauza z Vinohrad pokračuje. Policie se omlouvá