Dacă ați citit manualul MySQL s-ar putea să fi văzut ORDER BY RAND()pentru a randomiza rândurile și folosind LIMIT 1 pentru a lua doar unul dintre rânduri.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Acest exemplu funcționează bine și este rapid dacă aveți doar 1000 de rânduri, să spunem 1000 de rânduri.De îndată ce aveți 10.000 de rânduri, cheltuielile generale pentru sortarea rândurilor devin importante. Nu uitați: sortăm doar pentru a arunca aproape toate rândurile.
Niciodată nu mi-a plăcut. Și există modalități mai bune de a face acest lucru. Fără o sortare. atâta timp cât avem o cheie primară numerică.
Pentru primele exemple presupunem că ID-ul be începe de la 1 și nu avem goluri între 1 și valoarea maximă a ID-ului.
Prima idee: Putem simplifica întreaga sarcină dacă calculăm în prealabil ID-ul în aplicație.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Cum MAX(id) == COUNT(id) generăm doar un număr aleatoriu între1 și MAX(id) și îl transmitem în baza de date pentru a prelua rândul aleatoriu.
Primul SELECT este un NO-OP și este optimizat. Al doilea este un eq_refîn raport cu o valoare constantă și, de asemenea, foarte rapid.
Mutați sarcina în baza de date
Dar este cu adevărat necesar să o faceți în aplicație ? Nu putem să o facem în baza de date?
Ok, acum știm cum să generăm ID-ul aleatoriu, dar cum să obținem rândul?
Nu, NU, NU, NU. Nu mergeți pe această cale. Acesta este cel mai evident, dar și cel mai greșit mod de a face acest lucru.Motivul: SELECT-ul din clauza WHERE este executat pentru fiecare rând pe care SELECT-ul exterior îl extrage.Acest lucru duce la 0 până la 4091 de rânduri, în funcție de noroc.
Avem nevoie de o modalitate de a ne asigura că ID-ul aleatoriu este generat doar o singură dată:
SELECT-ul interior generează un tabel TEMPORAR constant, iar JOIN-ul selectează doar pe un singur rând. Perfect.
Nici o sortare, nici o aplicație, cele mai multe părți ale interogării au fost optimizate.
adăugarea de găuri la numere
Pentru a generaliza ultima soluție adăugăm posibilitatea de găuri, ca atunci când DELETE rânduri.
Join-ul adaugă acum toate ID-urile care sunt mai mari sau egale cu valoarea noastră aleatorie și selectează doar vecinul direct dacă nu este posibilă o potrivire directă. DAR, de îndată ce se găsește un rând, ne oprim (LIMIT 1). Și citim rândurile în funcție de index (ORDER BY id ASC). Deoarece folosim >= în loc de =, putem scăpa de CEIL și să obținem același rezultat cu ceva mai puțină muncă.
Distribuție egală
În momentul în care distribuția ID-urilor nu mai este egală, nici selecția noastră de rânduri nu mai este cu adevărat aleatorie.
Funcția RAND generează ID-uri de la 9 la 15, care toate conduc la ID-ul 16 pentru a fi selectat ca următorul număr mai mare.
Nu există o soluție reală pentru această problemă, dar datele dvs. sunt în cea mai mare parte constante, puteți adăuga un tabel de corespondență care mapează numărul rândului cu id-ul:
Funcția row_id este acum din nou liberă de găuri și putem rula din nou interogarea noastră aleatorie:
După 1000 de căutări, vedem din nou o distribuție egală:
Menținerea găurilor
Să luăm tabelele ca înainte:
Când schimbăm ceva în r2, vrem ca și r2_equi_dist să fie actualizat.
INSERT este destul de simplu, la DELETE trebuie să actualizăm equi-dist-id-ul pentru a menține configurația fără găuri:
UPDATE este din nou simplu. Trebuie doar să menținem constrângerea Foreign Key:
Multiple Rows at once
Dacă doriți să obțineți mai mult de un rând returnat, puteți:
- executați interogarea de mai multe ori
- scrieți o procedură stocată care execută interogarea și stochează rezultatul într-o tabelă temporară
- faceți un UNION
o procedură stocată
Procedurile stocate vă oferă structurile pe care le cunoașteți din limbajul dumneavoastră preferat de programare:
- bucle
- structuri de control
- proceduri
- proceduri
- …
Pentru această sarcină avem nevoie doar de un LOOP:
Îl las pe cititor să rezolve problemele:
- utilizați SQL dinamic și introduceți numele tabelului temporar
- utilizați un index UNIQUE pe tabel și prindeți încălcarea cheii UNIQUE pentru a elimina posibilele duplicate din setul de rezultate
Performanță
Acum să vedem ce se întâmplă cu performanța noastră. Avem 3 interogări diferite pentru rezolvarea problemelor noastre.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Este de așteptat ca Q1 să coste N * log2(N), Q2 și Q3 sunt aproape constante.
Pentru a obține valori reale am umplut tabelul cu N rânduri ( de la o mie la un milion)și am executat fiecare interogare de 1000 de ori.
.