Takana ovat ajat, jolloin sinun ei tarvinnut huolehtia tietokannan suorituskyvyn optimoinnista. Aika ei pysähdy. Jokainen uusi teknologiayrittäjä haluaa luoda seuraavan Facebookin samalla kun yrittää kerätä kaiken käsiinsä pääsevän datan. Yritykset tarvitsevat näitä tietoja kouluttaakseen paremmin malleja, jotka auttavat niitä ansaitsemaan rahaa. Tällaisissa olosuhteissa ohjelmoijien on luotava sovellusliittymiä, joiden avulla he voivat työskennellä nopeasti ja luotettavasti valtavien tietomäärien kanssa.
Jos olet suunnitellut sovellusten tai tietokantojen taustaohjelmia jonkin aikaa, olet todennäköisesti kirjoittanut koodia sivutettujen kyselyjen suorittamista varten. Esimerkiksi näin:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Niin kuin se on?
Mutta jos teit sivutussi tällä tavalla, olen pahoillani, että et tehnyt sitä tehokkaimmalla tavalla.
Haluatko vastustaa minua? . , и He käyttävät jo tekniikoita, joista haluan puhua tänään.
Nimeä vähintään yksi taustakehittäjä, joka ei ole koskaan käyttänyt OFFSET и LIMIT sivuttujen kyselyjen suorittamiseen. MVP:ssä (Minimum Viable Product) ja projekteissa, joissa käytetään pieniä määriä dataa, tämä lähestymistapa on varsin käyttökelpoinen. Se "vain toimii", niin sanotusti.
Mutta jos haluat luoda luotettavia ja tehokkaita järjestelmiä tyhjästä, sinun tulee huolehtia etukäteen kyseisissä järjestelmissä käytettävien tietokantojen kyselyn tehokkuudesta.
Tänään puhumme ongelmista yleisesti käytettyjen (liian huonojen) sivuttujen kyselykoneiden toteutuksissa ja kuinka saavuttaa korkea suorituskyky tällaisten kyselyjen suorittamisessa.
Mitä vikaa OFFSET:issa ja LIMIT:issä on?
Kuten jo sanottu, OFFSET и LIMIT Ne toimivat hyvin projekteissa, joiden ei tarvitse työskennellä suurten tietomäärien kanssa.
Ongelma syntyy, kun tietokanta kasvaa niin suureksi, ettei se enää mahdu palvelimen muistiin. Kuitenkin, kun työskentelet tämän tietokannan kanssa, sinun on käytettävä sivuttuja kyselyitä.
Jotta tämä ongelma ilmenee, täytyy olla tilanne, jossa DBMS turvautuu tehottomaan Full Table Scan -toimintoon jokaisessa sivutussa kyselyssä (vaikka lisäys- ja poistotoimintoja voi tapahtua, emmekä tarvitse vanhentuneita tietoja!).
Mikä on "täyden taulukon skannaus" (tai "peräkkäinen taulukkoskannaus", peräkkäinen tarkistus)? Tämä on toiminto, jonka aikana DBMS lukee peräkkäin taulukon jokaisen rivin eli sen sisältämät tiedot ja tarkistaa, ovatko ne tietyn ehdon mukaisia. Tämän tyyppinen taulukkoskannaus on tunnetusti hitain. Tosiasia on, että kun se suoritetaan, suoritetaan monia syöttö-/tulostustoimintoja, jotka liittyvät palvelimen levyalijärjestelmään. Tilannetta pahentavat levyille tallennettujen tietojen käsittelyyn liittyvät viiveet sekä se, että tiedon siirtäminen levyltä muistiin on resurssivaltaista toimintaa.
Sinulla on esimerkiksi tietueita 100000000 XNUMX XNUMX käyttäjästä ja suoritat kyselyn konstruktilla OFFSET 50000000. Tämä tarkoittaa, että DBMS:n on ladattava kaikki nämä tietueet (emmekä edes tarvitse niitä!), tallennettava ne muistiin ja otettava sen jälkeen esimerkiksi 20 tulosta, jotka on raportoitu LIMIT.
Oletetaan, että se voi näyttää tältä: "valitse rivit 50000 50020:sta 100000 50000:een XNUMX XNUMX:sta". Eli järjestelmän on ensin ladattava XNUMX XNUMX riviä kyselyn suorittamiseksi. Näetkö kuinka paljon turhaa työtä hän joutuu tekemään?
Jos et usko minua, katso esimerkkiä, jonka loin ominaisuuksien avulla .
Esimerkki osoitteessa db-fiddle.com
Siellä, vasemmalla, pellolla Schema SQL, on koodi, joka lisää 100000 XNUMX riviä tietokantaan, ja oikealla kenttään Query SQL, näytetään kaksi kyselyä. Ensimmäinen, hidas, näyttää tältä:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Ja toinen, joka on tehokas ratkaisu samaan ongelmaan, on seuraava:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Voit täyttää nämä pyynnöt napsauttamalla painiketta Run sivun yläreunassa. Kun tämä on tehty, vertaamme tietoja kyselyn suoritusajasta. Osoittautuu, että tehottoman kyselyn suorittaminen kestää vähintään 30 kertaa kauemmin kuin toisen suorittaminen (tämä aika vaihtelee ajon mukaan; järjestelmä voi esimerkiksi raportoida, että ensimmäisen kyselyn suorittaminen kesti 37 ms, mutta toinen - 1 ms).
Ja jos tietoja on enemmän, kaikki näyttää vielä pahemmalta (varmistuaksesi tästä, katso minun 10 miljoonalla rivillä).
Juuri keskustelemamme pitäisi antaa sinulle käsitystä siitä, kuinka tietokantakyselyjä todella käsitellään.
Huomaa, että mitä suurempi arvo OFFSET — sitä kauemmin pyynnön täyttäminen kestää.
Mitä minun pitäisi käyttää OFFSET- ja LIMIT-yhdistelmän sijaan?
Yhdistelmän sijaan OFFSET и LIMIT Kannattaa käyttää seuraavan kaavion mukaan rakennettua rakennetta:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Tämä on kyselyn suorittaminen kursoripohjaisella sivutuksella.
Sen sijaan, että varastoisit nykyiset paikallisesti OFFSET и LIMIT ja lähettää ne jokaisen pyynnön yhteydessä, sinun on tallennettava viimeksi vastaanotettu ensisijainen avain (yleensä tämä on ID) Ja LIMIT, tuloksena saadaan yllä olevan kaltaisia kyselyitä.
Miksi? Asia on siinä, että määrittämällä nimenomaisesti viimeisen luetun rivin tunnisteen, kerrot DBMS:llesi, mistä sen on aloitettava tarvittavien tietojen etsiminen. Lisäksi haku tapahtuu avaimen käytön ansiosta tehokkaasti, järjestelmän ei tarvitse häiritä määritellyn alueen ulkopuolella olevilla linjoilla.
Katsotaanpa seuraavaa eri kyselyiden tehokkuusvertailua. Tässä on tehoton kysely.
Hidas pyyntö
Ja tässä on optimoitu versio tästä pyynnöstä.
Nopea pyyntö
Molemmat kyselyt palauttavat täsmälleen saman määrän tietoa. Mutta ensimmäinen kestää 12,80 sekuntia ja toinen kestää 0,01 sekuntia. Tunnetko eron?
Mahdolliset ongelmat
Jotta ehdotettu kyselymenetelmä toimisi tehokkaasti, taulukossa on oltava sarake (tai sarakkeita), joka sisältää yksilölliset peräkkäiset indeksit, kuten kokonaislukutunnisteen. Joissakin erityistapauksissa tämä voi vaikuttaa tällaisten kyselyiden käytön onnistumiseen tietokannan työskentelyn nopeuttamiseksi.
Tietysti kyselyitä tehdessä on otettava huomioon taulukoiden erityinen arkkitehtuuri ja valittava ne mekanismit, jotka toimivat parhaiten olemassa olevissa taulukoissa. Jos sinun on esimerkiksi työstettävä kyselyitä, joissa on suuria määriä aiheeseen liittyvää dataa, se saattaa olla kiinnostavaa artikkeli.
Jos kohtaamme ongelman, joka koskee esimerkiksi pääavaimen puuttumista, jos meillä on taulukko, jossa on monta moneen -suhde, niin perinteinen lähestymistapa käyttää OFFSET и LIMIT, sopii meille taatusti. Mutta sen käyttö voi johtaa mahdollisesti hitaisiin kyselyihin. Tällaisissa tapauksissa suosittelen käyttämään automaattisesti kasvavaa ensisijaista avainta, vaikka sitä tarvittaisiin vain sivuttujen kyselyjen käsittelyyn.
Jos olet kiinnostunut tästä aiheesta - , и - useita hyödyllisiä materiaaleja.
Tulokset
Tärkein johtopäätös, jonka voimme tehdä, on, että on aina tarpeen analysoida kyselyn suoritusnopeutta riippumatta siitä, minkä kokoisista tietokannoista puhumme. Nykyään ratkaisujen skaalautuvuus on erittäin tärkeää, ja jos kaikki on suunniteltu oikein tietyn järjestelmän työskentelyn alusta lähtien, tämä voi tulevaisuudessa säästää kehittäjän monilta ongelmilta.
Kuinka analysoit ja optimoit tietokantakyselyitä?
Lähde: will.com