Se hai letto il manuale MySQL potresti aver visto l’ORDER BY RAND()per randomizzare le righe e usare il LIMIT 1 per prendere solo una delle righe.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Questo esempio funziona bene ed è veloce se hai solo, diciamo, 1000 righe; appena hai 10000 righe, l’overhead per ordinare le righe diventa importante. Non dimenticate: noi ordiniamo solo per buttare via quasi tutte le righe.
Non mi è mai piaciuto. E ci sono modi migliori per farlo. Senza un ordinamento.Finché abbiamo una chiave primaria numerica.
Per i primi esempi assumiamo che l’ID sia a partire da 1 e non abbiamo buchi tra 1 e il valore massimo dell’ID.
Prima idea: Possiamo semplificare l’intero lavoro se calcoliamo l’ID in anticipo nell’applicazione.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Come MAX(id) == COUNT(id) generiamo semplicemente un numero casuale tra1 e MAX(id) e lo passiamo nel database per recuperare la riga casuale.
La prima SELECT è una NO-OP e viene ottimizzata. La seconda è una eq_refcontro un valore costante e anche molto veloce.
Muovi il lavoro nel database
Ma è davvero necessario farlo nell’applicazione? Non possiamo farlo nel database?
Ok, ora sappiamo come generare l’ID casuale, ma come ottenere la riga?
NO, NO, NO. Non andare in questo modo. Questo è il modo più ovvio, ma anche il più sbagliato per farlo.Il motivo: la SELECT nella clausola WHERE viene eseguita per ogni riga che la SELECT esterna sta recuperando.Questo porta da 0 a 4091 righe, a seconda della vostra fortuna.
Abbiamo bisogno di un modo per assicurarci che l’ID casuale sia generato solo una volta:
La SELECT interna sta generando una tabella TEMPORANEA costante e la JOIN sta selezionando solo su singlerow. Perfetto.
Nessun ordinamento, nessuna applicazione, la maggior parte delle parti della query ottimizzata via.
aggiungendo dei buchi ai numeri
Per generalizzare l’ultima soluzione aggiungiamo la possibilità di buchi, come quando si DELETE righe.
La JOIN ora aggiunge tutti gli ID che sono maggiori o uguali al nostro valore casuale e seleziona solo il vicino diretto non è possibile una corrispondenza diretta. MA appena viene trovata una riga, ci fermiamo (il LIMIT 1). E leggiamo le righe secondo l’indice (ORDER BY id ASC). Dato che stiamo usando >= invece di un = possiamo sbarazzarci di un CEIL e ottenere lo stesso risultato con un po’ meno lavoro.
Distribuzione uguale
Non appena la distribuzione degli ID non è più uguale, la nostra selezione delle righe non è più veramente casuale.
La funzione RAND genera ID come 9 a 15 che portano tutti all’id 16 per essere selezionato come il prossimo numero superiore.
Non c’è una vera soluzione per questo problema, ma i tuoi dati sono per lo più costanti puoi aggiungere una tabella di mappatura che mappa il numero di riga all’id:
La row_id è ora di nuovo senza buchi e possiamo eseguire di nuovo la nostra query casuale:
Dopo 1000 prelievi vediamo di nuovo una distribuzione uguale:
Mantenere i buchi
Prendiamo le tabelle come prima:
Quando cambiamo qualcosa in r2 vogliamo che anche r2_equi_dist sia aggiornato.
INSERT è abbastanza semplice, su DELETE dobbiamo aggiornare l’equi-dist-id per mantenere la configurazione senza buchi:
UPDATE è di nuovo semplice. Dobbiamo solo mantenere il vincolo della Foreign Key:
Multiple righe in una volta
Se vuoi ottenere più di una riga restituita, puoi:
- eseguire la query più volte
- scrivere una stored procedure che esegue la query e memorizza il risultato in una tabella temporanea
- fare una UNION
una stored procedure
Le stored procedure ti forniscono le strutture che conosci dal tuo linguaggio di programmazione preferito:
- loop
- strutture di controllo
- procedure
- …
Per questo compito abbiamo bisogno solo di un LOOP:
Lascio al lettore di risolvere i problemi:
- utilizzare SQL dinamico e passare il nome della tabella temporanea
- utilizzare un indice UNIQUE sulla tabella e catturare la violazione della chiave UNIQUE per rimuovere i possibili duplicati nel set di risultati
Performance
Ora vediamo cosa succede alla nostra performance. Abbiamo 3 diverse query per risolvere i nostri problemi.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1 dovrebbe costare N * log2(N), Q2 e Q3 sono quasi costanti.
Per ottenere i valori reali abbiamo riempito la tabella con N righe (da mille a un milione) ed eseguito ogni query 1000 volte.