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;

Související články na blogu Tomas M

Související články na ostatních blozích

120na80.cz: Vyzrajte na návaly a pocení v přechodu

Vyzrajte na návaly a pocení v přechodu

DigiZone.cz: Budoucnost video služeb na internetu

Budoucnost video služeb na internetu

Vitalia.cz: Před, nebo po snídani? Kdy je lepší čistit si zuby

Před, nebo po snídani? Kdy je lepší čistit si zuby

Podnikatel.cz: Proměny stavebnice Seva. Znáte ji?

Proměny stavebnice Seva. Znáte ji?

Vitalia.cz: Mražené ryby z Makra byly falšované

Mražené ryby z Makra byly falšované

DigiZone.cz: ČT neskončí s nízkým rozlišením podle plánu

ČT neskončí s nízkým rozlišením podle plánu

Vitalia.cz: Ministerstvo: tyto příbory jsou nebezpečné

Ministerstvo: tyto příbory jsou nebezpečné

Vitalia.cz: Dnešní patolog o mrtvolu téměř nezavadí

Dnešní patolog o mrtvolu téměř nezavadí

DigiZone.cz: Šlágr TV: pokuta 100 tisíc za on-line

Šlágr TV: pokuta 100 tisíc za on-line

120na80.cz: Co jí dělá? Sklerotizaci

Co jí dělá? Sklerotizaci

DigiZone.cz: Šlágr TV dostala pokutu 100 000 Kč

Šlágr TV dostala pokutu 100 000 Kč

Lupa.cz: Zaplatíme ti, když ti seženeme práci

Zaplatíme ti, když ti seženeme práci

Lupa.cz: Babiš: nevím o návodu, jak obejít blokování webů

Babiš: nevím o návodu, jak obejít blokování webů

DigiZone.cz: DAB+ versus FM, ČRo a ČRa proti APSV

DAB+ versus FM, ČRo a ČRa proti APSV

Lupa.cz: Válka e-shopů. Alza končí s Heurekou

Válka e-shopů. Alza končí s Heurekou

Vitalia.cz: SÚKL: vakcíny jsou bezpečné a s autismem nesouvisí

SÚKL: vakcíny jsou bezpečné a s autismem nesouvisí

Lupa.cz: Kam si doma dáte internet věcí? Na polici?

Kam si doma dáte internet věcí? Na polici?

120na80.cz: 5 triků, jak zastavit krvácení po holení

5 triků, jak zastavit krvácení po holení

DigiZone.cz: UPC umí televizi sedm dní nazpět

UPC umí televizi sedm dní nazpět

Vitalia.cz: Proč máme prasklý chléb nejraději?

Proč máme prasklý chléb nejraději?