Als je de MySQL handleiding hebt gelezen, heb je misschien de ORDER BY RAND() gezien om de rijen te randomiseren en de LIMIT 1 te gebruiken om slechts een van de rijen te nemen.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Dit voorbeeld werkt prima en is snel als je maar laten we zeggen 1000 rijen hebt. Zodra je 10000 rijen hebt, wordt de overhead voor het sorteren van de rijen belangrijk. Vergeet niet: we sorteren alleen om bijna alle rijen weg te gooien.
Ik heb het nooit leuk gevonden. En er zijn betere manieren om het te doen. Zonder sorteren.Als we maar een numerieke primaire sleutel hebben.
Voor de eerste voorbeelden gaan we ervan uit dat de be ID begint bij 1 en dat we geen gaten hebben tussen 1 en de maximale waarde van de ID.
Eerste idee: We kunnen het hele werk vereenvoudigen als we de ID van tevoren in de applicatie berekenen.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Als MAX(id) == COUNT(id) genereren we gewoon een willekeurig getal tussen1 en MAX(id) en geven dit door aan de database om de willekeurige rij op te halen.
De eerste SELECT is een NO-OP en wordt weg geoptimaliseerd. De tweede is een eq_reftegen een constante waarde en ook zeer snel.
de opdracht in de database
Maar is het echt nodig om dit in de applicatie te doen ? Kunnen we het niet in de database doen?
Ok, nu weten we hoe we de willekeurige ID moeten genereren, maar hoe krijgen we de rij?
NEE, NEE, NEE. Ga niet deze kant op. Dit is de meest voor de hand liggende, maar ook de meest foute manier om het te doen.De reden: de SELECT in de WHERE clause wordt uitgevoerd voor elke rij die de outer SELECT ophaalt.Dit leidt tot 0 tot 4091 rijen, afhankelijk van je geluk.
We hebben een manier nodig om ervoor te zorgen dat de random-id slechts eenmaal wordt gegenereerd:
De inner SELECT genereert een constante TEMPORARY tabel en de JOIN selecteert alleen op singlerow. Perfect.
Geen sortering, geen toepassing, de meeste delen van de query weg geoptimaliseerd.
gaatjes toevoegen aan de getallen
Om de laatste oplossing te veralgemenen voegen we de mogelijkheid van gaatjes toe, zoals wanneer je DELETE rijen.
De JOIN voegt nu alle ID’s toe die groter of gelijk zijn aan onze willekeurige waarde en selecteert alleen de directe buren als een directe overeenkomst niet mogelijk is. MAAR zodra één rij is gevonden, stoppen we (de LIMIT 1). En we lezen de rijen volgens de index (ORDER BY id ASC). Aangezien we >= gebruiken in plaats van een = kunnen we ons ontdoen van een CEIL en krijgen we hetzelfde resultaat met een beetje minder werk.
Gelijke verdeling
Zodra de verdeling van de ID’s niet meer gelijk is, is onze selectie van rijen ook niet meer echt willekeurig.
De RAND functie genereert ID’s zoals 9 tot 15 die allemaal leiden tot de id 16 om te worden geselecteerd als het volgende hogere nummer.
Er is geen echte oplossing voor dit probleem, maar je data is meestal constant je kunt een mapping tabel toevoegen die het rij-nummer aan de id koppelt:
De row_id is nu weer vrij van gaten en we kunnen onze willekeurige query weer uitvoeren:
Na 1000 fetches zien we weer een gelijke verdeling:
Het behouden van de gaten
Laten we de tabellen nemen zoals voorheen:
Wanneer we ooit iets veranderen in r2 willen we dat r2_equi_dist ook wordt bijgewerkt.
INSERT is vrij eenvoudig, bij DELETE moeten we de equi-dist-id bijwerken om de hole-free setup te handhaven:
UPDATE is weer rechttoe-rechtaan. We hoeven alleen de Foreign Key constraint te handhaven:
Meerdere rijen tegelijk
Als je meer dan één rij geretourneerd wilt krijgen, kan dat:
- de query meerdere malen uitvoeren
- een opgeslagen procedure schrijven die de query uitvoert en het resultaat in een temp-tabel opslaat
- een UNION
een opgeslagen procedure
Opgeslagen procedures bieden u de structuren die u kent uit uw favoriete programmeertaal:
- loops
- controlestructuren
- procedures
- …
Voor deze taak hebben we alleen een LOOP nodig:
Ik laat het aan de lezer over om de problemen op te lossen:
- gebruik dynamische SQL en geef de naam van de tijdelijke tabel op
- gebruik een UNIQUE index op de tabel en vang de UNIQUE key violation op om de mogelijke duplicaten in de result-set te verwijderen
Prestaties
Nu laten we eens kijken wat er met onze prestaties gebeurt. We hebben 3 verschillende queries om onze problemen op te lossen.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 zal naar verwachting N * log2(N) kosten, Q2 en Q3 zijn bijna constant.
Om echte waarden te krijgen hebben we de tabel gevuld met N rijen (duizend tot een miljoen) en elke query 1000 keer uitgevoerd.