Pokud jste četli příručku MySQL, mohli jste vidět ORDER BY RAND()pro náhodné uspořádání řádků a použití LIMIT 1 pro výběr pouze jednoho z řádků.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Tento příklad funguje dobře a je rychlý, pokud máte pouze řekněme 1000 řádků. jakmile máte 10000 řádků, režie pro třídění řádků se stávádůležitou. Nezapomeňte: třídíme jen proto, abychom téměř všechny řádky vyhodili.
Nikdy se mi to nelíbilo. A existují lepší způsoby, jak to udělat. Bez třídění. pokud máme číselný primární klíč.
Pro první příklady předpokládáme, že be ID začíná na 1 a mezi 1 a maximální hodnotou ID nemáme díry.
První myšlenka:
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Protože MAX(id) == COUNT(id), stačí vygenerovat náhodné číslo mezi1 a MAX(id) a předat ho do databáze pro získání náhodného řádku.
První SELECT je NO-OP a je optimalizován pryč. Druhý je eq_refproti konstantní hodnotě a také velmi rychlý.
přesunout úlohu do databáze
Ale je opravdu nutné to dělat v aplikaci ? Nemůžeme to udělat v databázi ?
Ok, teď už víme, jak vygenerovat náhodné ID, ale jak získat řádek ?
NO, NE, NE. Touto cestou nechoďte. Je to nejzřejmější, ale také nejšpatnější způsob, jak to udělat. důvod: SELECT v klauzuli WHERE se provede pro každý řádek, který načítá vnější SELECT. to vede k 0 až 4091 řádkům, záleží na vašem štěstí.
Potřebujeme způsob, jak zajistit, aby se náhodné ID vygenerovalo jen jednou:
Vnitřní SELECT generuje konstantní TEMPORARY tabulku a JOIN vybírá jen na singlerow. Perfektní.
Žádné třídění, žádná aplikace, většina částí dotazu optimalizována pryč.
přidání děr do čísel
Pro zobecnění posledního řešení přidáme možnost děr, jako když DELETEřádků.
Join nyní sečte všechna ID, která jsou větší nebo rovna naší náhodné hodnotě, a vybere jen přímé sousedy, pokud není možná přímá shoda. ALE jakmile je nalezen jeden řádek, zastavíme se (LIMIT 1). A načteme řádky podle indexu (ORDER BY id ASC). Protože místo = používáme >=, můžeme se zbavit CEIL a získat stejný výsledek s o něco menší pracností.
Rovnoměrné rozložení
Jakmile už rozložení ID není rovnoměrné, není ani náš výběr řádků úplně náhodný.
Funkce RAND generuje ID jako 9 až 15, které všechny vedou k tomu, že jako další vyšší číslo bude vybráno id 16. To znamená, že při výběru řádků je nutné použít RAND.
Tento problém nemá žádné skutečné řešení, ale vaše data jsou většinou konstantní, můžete přidat mapovací tabulku, která mapuje číslo řádku na id:
Příkaz row_id je nyní opět bez děr a můžeme opět spustit náš náhodný dotaz:
Po 1000 načteních opět vidíme rovnoměrné rozdělení:
Zachování děr
Vezměme tabulky jako předtím:
Kdykoli něco změníme v r2, chceme, aby se aktualizovalo i r2_equi_dist.
INSERT je celkem jednoduchý, při DELETE musíme aktualizovat equi-dist-id, abychom zachovali nastavení bez děr:
UPDATE je opět jednoduchý. Musíme pouze zachovat omezení cizího klíče:
Více řádků najednou
Pokud chcete získat zpět více než jeden řádek, můžete:
- vykonat dotaz několikrát
- napsat uloženou proceduru, která dotaz provede a výsledek uloží do temp-tabulky
- udělat UNION
uložená procedura
Uložené procedury poskytují struktury, které znáte ze svého oblíbeného programovacího jazyka:
- smyčky
- řídicí struktury
- procedury
- …
Pro tuto úlohu potřebujeme pouze LOOP:
Napravení problémů nechám na čtenáři:
- použijte dynamický SQL a předejte název dočasné tabulky
- použijte UNIQUE index na tabulce a zachyťte porušení UNIQUE klíče, abyste odstranili případné duplicity v souboru výsledků
Výkon
Nyní se podívejme, co se stane s naším výkonem. Máme 3 různé dotazypro řešení našich problémů.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 by měl stát N * log2(N), Q2 a Q3 jsou téměř konstantní.
Pro získání skutečných hodnot jsme tabulku naplnili N řádky ( tisíc až milion)a každý dotaz provedli 1000krát.