Hvis du har læst MySQL-manualen, har du måske set ORDER BY RAND() til at randomisere rækkerne og bruge LIMIT 1 til kun at tage en af rækkerne.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Dette eksempel virker fint og er hurtigt, hvis du kun når du kun når lad os sige 1000 rækker.Så snart du har 10000 rækker bliver overhead for sortering af rækkerne vigtig. Glem ikke: vi sorterer kun for at smide næsten alle rækkerne væk.
Jeg har aldrig kunnet lide det. Og der er bedre måder at gøre det på. Uden en sortering. så længe vi har en numerisk primærnøgle.
For de første eksempler antager vi, at be ID starter ved 1, og vi har ingenhuller mellem 1 og den maksimale værdi af ID.
Første idé: Vi kan forenkle hele opgaven, hvis vi beregner ID’et på forhånd i programmet.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Som MAX(id) == COUNT(id) genererer vi bare et tilfældigt tal mellem1 og MAX(id) og sender det ind i databasen for at hente den tilfældige række.
Den første SELECT er en NO-OP og optimeres væk. Den anden er en eq_refmod en konstant værdi og også meget hurtig.
flyt jobbet ind i databasen
Men er det virkelig nødvendigt at gøre det i applikationen ? Kan vi ikke gøre det i databasen?
Okay, nu ved vi, hvordan vi kan generere det tilfældige ID, men hvordan får vi rækken?
NEJ, NEJ, NEJ, NEJ. Lad være med at gå denne vej. Dette er den mest indlysende, men også den mest forkerte måde at gøre det på. grunden: SELECT i WHERE-klausulen udføres for hver række den ydre SELECT henter. dette fører til 0 til 4091 rækker, afhængigt af dit held.
Vi har brug for en måde at sikre, at random-id’et kun genereres én gang:
Den indre SELECT genererer en konstant TEMPORARY tabel og JOIN’et vælger kun på singlerow. Perfekt.
Ingen sortering, ingen anvendelse, de fleste dele af forespørgslen optimeres væk.
tilføjelse af huller til tallene
For at generalisere den sidste løsning tilføjer vi muligheden for huller, som når du DELETE rækker.
Joinen tilføjer nu alle de ID’er, der er større eller lig med vores tilfældige værdi og vælger kun den direkte nabo, hvis et direkte match ikke er muligt. MEN så snart der er fundet en række, stopper vi (LIMIT 1). Og vi læser rækkerne i henhold til indekset (ORDER BY id ASC). Da vi bruger >= i stedet for en = kan vi slippe for en den CEIL og få det samme resultat med lidt mindre arbejde.
Ligelig fordeling
Så snart fordelingen af id’erne ikke længere er ligelig, er vores udvælgelse af rækker heller ikke rigtig tilfældig.
Funktionen RAND genererer id’er som 9 til 15, der alle fører til id’et 16, der skal vælges som det næste højere nummer.
Der er ikke nogen rigtig løsning på dette problem, men dine data er for det meste konstante, du kan tilføje en mapping tabel, som mapper række-nummeret til id:
Den row_id er nu fri for huller igen, og vi kan køre vores tilfældige forespørgsel igen:
Efter 1000 hentninger ser vi igen en ligelig fordeling:
Holdelse af hullerne
Lad os tage tabellerne som før:
Når vi ændrer noget i r2, vil vi gerne have at r2_equi_dist også opdateres.
INSERT er ret simpelt, ved DELETE skal vi opdatere equi-dist-id’et for at opretholde den hulfri opsætning:
UPDATE er igen ligetil. Vi skal kun vedligeholde Foreign Key constraint:
Multiple Rows at once
Hvis du ønsker at få mere end én række returneret, kan du det:
- udføre forespørgslen flere gange
- skrive en stored procedure, som udfører forespørgslen og gemmer resultatet i en temp-table
- lave en UNION
en stored procedure
Stored procedures giver dig de strukturer, du kender fra dit foretrukne programmeringssprog:
- sløjfer
- styringsstrukturer
- procedurer
- …
Til denne opgave har vi kun brug for en LOOP:
Jeg overlader det til læseren at løse problemerne:
- bruge dynamisk SQL og indtaste navnet på den midlertidige tabel
- bruge et UNIQUE-indeks på tabellen og fange UNIQUE-nøgleovertrædelsen for at fjerne de mulige dubletter i resultatmængden
Performance
Nu skal vi se, hvad der sker med vores performance. Vi har 3 forskellige forespørgsler til løsning af vores problemer.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 forventes at koste N * log2(N), Q2 og Q3 er næsten konstante.
For at få reelle værdier fyldte vi tabellen med N rækker (tusind til en million) og udførte hver forespørgsel 1000 gange.