Om du har läst MySQL-manualen har du kanske sett ORDER BY RAND() för att slumpmässigt välja ut raderna och LIMIT 1 för att bara ta en av raderna.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Detta exempel fungerar bra och är snabbt om du bara har låt oss säga 1000 rader.Så snart du har 10000 rader blir overheadkostnaden för att sortera raderna viktig. Glöm inte: vi sorterar bara för att kasta bort nästan alla rader.
Jag har aldrig gillat det. Och det finns bättre sätt att göra det på. Utan sortering.Så länge vi har en numerisk primärnyckel.
För de första exemplen antar vi att be ID börjar på 1 och att vi inte har några hål mellan 1 och det maximala värdet av ID:et.
Första idén: Vi kan förenkla hela jobbet om vi beräknar ID i förväg i programmet.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Som MAX(id) == COUNT(id) genererar vi bara ett slumpmässigt nummer mellan1 och MAX(id) och skickar det till databasen för att hämta den slumpmässiga raden.
Den första SELECT är en NO-OP och optimeras bort. Den andra är en eq_refmot ett konstant värde och är också mycket snabb.
flyttar jobbet till databasen
Men är det verkligen nödvändigt att göra det i applikationen ? Kan vi inte göra det i databasen?
Okej, nu vet vi hur vi genererar det slumpmässiga ID:t, men hur får vi fram raden?
NEJ, NEJ, NEJ, NEJ. Gör inte så här. Detta är det mest uppenbara, men också det mest felaktiga sättet att göra det på. anledningen: SELECT i WHERE-klausulen utförs för varje rad som den yttre SELECT hämtar. detta leder till 0 till 4091 rader, beroende på din tur.
Vi behöver ett sätt att se till att det slumpmässiga ID:et bara genereras en gång:
Den inre SELECT:en genererar en konstant TEMPORARY-tabell och JOIN:en väljer bara på singlerow. Perfekt.
Ingen sortering, ingen tillämpning, de flesta delar av förfrågan optimeras bort.
Hål till siffrorna
För att generalisera den senaste lösningen lägger vi till möjligheten till hål, som när du DELETE rader.
Joinen lägger nu till alla ID:n som är större eller lika med vårt slumpmässiga värde och väljer bara den direkta grannen om en direkt matchning inte är möjlig. MEN så snart en rad har hittats slutar vi (LIMIT 1). Och vi läser raderna enligt indexet (ORDER BY id ASC). Eftersom vi använder >= i stället för = kan vi göra oss av med CEIL och få samma resultat med lite mindre arbete.
Jämn fördelning
Så snart fördelningen av ID:erna inte längre är lika är vårt val av rader inte heller riktigt slumpmässigt.
Funktionen RAND genererar ID:er som 9 till 15, som alla leder till att id:et 16 väljs som det nästa högre numret.
Det finns ingen riktig lösning på det här problemet, men om dina data är mestadels konstanta kan du lägga till en mappningstabell som mappar radnumret till id:
Den row_id är nu fri från hål igen och vi kan köra vår slumpmässiga fråga igen:
Efter 1000 hämtningar ser vi en jämn fördelning igen:
Underhålla hålen
Låt oss ta tabellerna som tidigare:
När vi ändrar något i r2 vill vi att r2_equi_dist också uppdateras.
INSERT är ganska enkelt, vid DELETE måste vi uppdatera equi-dist-id för att bibehålla den hålfria inställningen:
UPDATE är enkelt igen. Vi behöver bara upprätthålla Foreign Key-begränsningen:
Flera rader på en gång
Om du vill få mer än en rad returnerad kan du göra det:
- utför frågan flera gånger
- skriv en lagrad procedur som utför frågan och lagrar resultatet i en temp-tabell
- gjord en UNION
en lagrad procedur
Lagrade procedurer ger dig de strukturer som du känner till från ditt favoritprogrammeringsspråk:
- slingor
- kontrollstrukturer
- procedurer
- …
För denna uppgift behöver vi bara en LOOP:
Jag överlåter till läsaren att lösa problemen:
- använd dynamisk SQL och ange namnet på den tillfälliga tabellen
- använd ett UNIQUE-index på tabellen och fånga upp UNIQUE-nyckelöverträdelsen för att ta bort eventuella dubbletter i resultatuppsättningen
Prestanda
Nu ska vi se vad som händer med vår prestanda. Vi har tre olika frågor för att lösa våra problem.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 förväntas kosta N * log2(N), Q2 och Q3 är nästan konstanta.
För att få fram de verkliga värdena fyllde vi tabellen med N rader (tusen till en miljon) och utförde varje fråga 1000 gånger.