Ha olvastad a MySQL kézikönyvet, akkor láthattad, hogy az ORDER BY RAND()véletlenszerűvé teszi a sorokat, és a LIMIT 1 használatával csak egy sort veszel ki.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Ez a példa jól működik és gyors, ha csak akkor, ha mondjuk 1000 sora van.Amint 10000 sora van, a sorok rendezésének többletköltsége fontossá válik. Ne felejtsük el: csak azért válogatunk, hogy majdnem az összes sort eldobjuk.
Soha nem szerettem. És vannak jobb módszerek is. Rendezés nélkül. amíg van numerikus elsődleges kulcsunk.
Az első példáknál feltételezzük, hogy a be azonosító 1-től kezdődik, és 1 és az azonosító maximális értéke között nincsenek lyukak.
Első ötlet: Az egész munkát leegyszerűsíthetjük, ha az alkalmazásban előre kiszámítjuk az ID-t.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Mivel MAX(id) == COUNT(id) csak véletlen számot generálunk1 és MAX(id) között, és átadjuk az adatbázisnak a véletlen sor kinyeréséhez.
Az első SELECT egy NO-OP és el van optimalizálva. A második egy eq_refegy konstans értékkel szemben és szintén nagyon gyors.
mozdítsuk a feladatot az adatbázisba
De tényleg szükség van erre az alkalmazásban ? Nem tehetjük meg az adatbázisban?
Oké, most már tudjuk, hogyan generáljuk a véletlenszerű azonosítót, de hogyan szerezzük meg a sort?
NEM, NEM, NEM. Ne menjünk erre az útra. Ez a legkézenfekvőbb, de egyben a legrosszabb megoldás.Az ok: a WHERE záradékban lévő SELECT minden sorra végrehajtódik, amit a külső SELECT lekérdez.Ez 0-4091 sorhoz vezet, a szerencsénktől függően.
Meg kell találnunk a módját annak, hogy a véletlen azonosítót csak egyszer generáljuk:
A belső SELECT egy állandó TEMPORARY táblát generál, a JOIN pedig csak egy soron választ. Tökéletes.
Nincs rendezés, nincs alkalmazás, a lekérdezés legtöbb része optimalizálva.
lyukak hozzáadása a számokhoz
Az utolsó megoldás általánosításához hozzáadjuk a lyukak lehetőségét, mint amikor DELETE sorok.
A JOIN most hozzáadja az összes ID-t, amely nagyobb vagy egyenlő a véletlen értékünknél, és csak a közvetlen szomszédot választja ki, ha a közvetlen egyezés nem lehetséges. DE amint egy sort találtunk, megállunk (a LIMIT 1). És az index (ORDER BY id ASC) szerint olvassuk be a sorokat. Mivel a = helyett >=-t használunk, megszabadulhatunk a CEIL-től, és kicsit kevesebb munkával ugyanazt az eredményt kapjuk.
Egyenletes eloszlás
Amint az azonosítók eloszlása már nem egyenlő, a sorok kiválasztása sem igazán véletlenszerű.
A RAND függvény olyan azonosítókat generál, mint 9-től 15-ig, amelyek mind a 16-os azonosítóhoz vezetnek, amelyet a következő magasabb számként választunk ki.
Ezekre a problémákra nincs igazi megoldás, de az adataid többnyire állandóak, hozzáadhatsz egy leképező táblát, amely a sorszámot leképezi az id-re:
A row_id most már ismét lyukmentes, és újra lefuttathatjuk a véletlenszerű lekérdezésünket:
1000 lekérdezés után ismét egyenlő eloszlást látunk:
A lyukak megtartása
Vegyük a táblákat úgy, mint korábban:
Amikor valamit megváltoztatunk az r2-ben, azt akarjuk, hogy az r2_equi_dist is frissüljön.
INSERT elég egyszerű, DELETE esetén frissítenünk kell az equi-dist-id-t, hogy a lyukmentes beállítás megmaradjon:
UPDATE megint egyszerű. Csak a Foreign Key constraintet kell fenntartanunk:
Multiple Rows at once
Ha egynél több sort szeretnénk visszakapni, megtehetjük:
- a lekérdezést többször is lefuttathatjuk
- írhatunk egy tárolt eljárást, amely végrehajtja a lekérdezést, és az eredményt egy ideiglenes táblázatban tárolja
- csinálhatunk egy UNION-t
tárolt eljárást
A tárolt eljárások a kedvenc programozási nyelvünkből ismert struktúrákat biztosítják:
- hurok
- vezérlési struktúrák
- eljárások
- …
Ezért a feladatért csak egy LOOP-ra van szükségünk:
Az olvasóra bízom a problémák megoldását:
- használjunk dinamikus SQL-t és adjuk meg az ideiglenes tábla nevét
- használjunk UNIQUE indexet a táblán és kapjuk el az UNIQUE kulcs megsértését, hogy eltávolítsuk az esetleges duplikátumokat az eredményhalmazból
Teljesítmény
Most nézzük meg, mi történik a teljesítményünkkel. A problémánk megoldásához 3 különböző lekérdezésünk van.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 várhatóan N * log2(N) költséggel jár, Q2 és Q3 közel állandó.
A valós értékek megszerzéséhez a táblázatot N sorral töltöttük fel ( ezer és egymillió között)és mindegyik lekérdezést 1000-szer hajtottuk végre.