Jos olet lukenut MySQL:n käsikirjaa, olet ehkä nähnyt ORDER BY RAND()-toiminnon satunnaistamaan rivejä ja käyttämään LIMIT 1:tä vain yhden rivin ottamiseksi.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Tämä esimerkki toimii hyvin ja on nopea, jos olet vain silloin, kun vaikkapa 1000 riviä.Heti kun sinulla on 10000 riviä, rivien lajittelun yleiskustannus muuttuu merkitykselliseksi. Älä unohda: lajittelemme vain heittääksemme lähes kaikki rivit pois.
En ole koskaan pitänyt siitä. Ja on parempiakin tapoja tehdä se. Ilman lajittelua.Kunhan meillä on numeerinen primääriavain.
Ensimmäisissä esimerkeissä oletamme, että be ID alkaa 1:stä ja meillä on noholes välillä 1 ja ID:n maksimiarvo.
Ensimmäinen idea: Voimme yksinkertaistaa koko työtä, jos laskemme ID:n etukäteen sovelluksessa.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Koska MAX(id) == COUNT(id), generoimme vain satunnaisluvun väliltä1 ja MAX(id) ja välitämme sen tietokantaan satunnaisen rivin hakemista varten.
Ensimmäinen SELECT on NO-OP ja optimoidaan pois. Toinen on eq_refvakioarvoa vastaan ja myös erittäin nopea.
siirrä työ tietokantaan
Mutta onko se todella tarpeen tehdä sovelluksessa ? Emmekö voi tehdä sitä tietokannassa?
Okei, nyt tiedämme, miten satunnainen tunnus generoidaan, mutta miten saada rivi?
Ei, ei, ei. Älkää menkö tätä tietä. Tämä on ilmeisin, mutta myös väärä tapa tehdä se.Syy: WHERE-lausekkeessa oleva SELECT suoritetaan jokaiselle riville, jonka ulompi SELECT hakee.Tämä johtaa 0-4091 riviin, riippuen tuuristasi.
Me tarvitsemme tavan varmistaa, että satunnaistunnus luodaan vain kerran:
Sisempi SELECT luo jatkuvan TEMPORARY-taulukon ja JOIN valitsee vain yksittäisen rivin. Täydellistä.
Ei lajittelua, ei sovellusta, suurin osa kyselystä optimoitu pois.
Lukujen reikien lisääminen
Yleistääksemme viimeistä ratkaisua lisäämme reikien mahdollisuuden, kuten silloin kun DELETE riviä.
JOIN lisää nyt kaikki ID:t, jotka ovat suuremmat tai yhtäsuuremmat kuin satunnaisarvomme ja valitsee vain suoran naapurinjos välitön täsmäytys ei ole mahdollista. MUTTA heti kun yksi rivi on löydetty, lopetamme (LIMIT 1). Ja luemme rivit indeksin mukaan (ORDER BY id ASC). Koska käytämme >=:tä =:n sijasta, voimme päästä eroon CEIL:stä ja saada saman tuloksen hieman vähemmällä työllä.
Tasajakauma
Heti kun tunnusten jakauma ei ole enää tasainen, rivien valintamme ei ole enää aivan satunnaista.
Funktio RAND tuottaa tunnuksia, kuten 9:stä aina 15:een asti, jotka kaikki johtavat siihen, että seuraavaksi suuremmaksi valitaan tunnus 16.
Tälle ongelmalle ei ole varsinaista ratkaisua, mutta datasi on enimmäkseen vakio, voit lisätä mapping-taulukon, joka kartoittaa rivinumeron id:ksi:
row_id on nyt taas reikäinen ja voimme suorittaa satunnaiskyselymme uudelleen:
Tuhannen haun jälkeen näemme jälleen tasaisen jakauman:
Aukkojen ylläpito
Pitäämme taulukot ennallaan:
Kun aina kun muutamme jotain r2:ssa haluamme, että myös r2_equi_dist päivitetään.
INSERT on melko yksinkertainen, DELETE:ssa meidän täytyy päivittää equi-dist-id, jotta reikävapaa asetelma säilyy:
UPDATE on taas suoraviivainen. Meidän on vain ylläpidettävä Foreign Key -rajoitusta:
Multiple Rows at once
Jos haluat saada useamman kuin yhden rivin takaisin, se onnistuu:
- silmukat
- ohjausrakenteet
- proseduurit
- …
Tässä tehtävässä tarvitsemme vain LOOP:ia:
Lukijan tehtäväksi jätän asioiden korjaamisen:
- käytä dynaamista SQL:ää ja anna väliaikaisen taulun nimi
- käytä UNIQUE-indeksiä taulussa ja nappaa UNIQUE-avaimen rikkominen poistaaksesi mahdolliset kaksoiskappaleet tulosjoukosta
Suorituskyky
Katsotaan nyt, mitä suorituskyvyllemme tapahtuu. Meillä on 3 erilaista kyselyä ongelmiemme ratkaisemiseksi.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Q1:n odotetaan maksavan N * log2(N), Q2:n ja Q3:n kustannukset ovat lähes vakiot.
Todellisten arvojen saamiseksi täytimme taulukon N:llä rivillä ( tuhannesta miljoonaan)ja suoritimme jokaisen kyselyn 1000 kertaa.