Möödas on ajad, mil te ei pidanud muretsema andmebaasi jõudluse optimeerimise pärast. Aeg ei seisa paigal. Iga uus tehnoloogiaettevõtja soovib luua järgmise Facebooki, püüdes samal ajal koguda kõiki andmeid, mida nad saavad kätte saada. Ettevõtted vajavad neid andmeid, et paremini koolitada mudeleid, mis aitavad neil raha teenida. Sellistes tingimustes peavad programmeerijad looma API-d, mis võimaldavad neil kiiresti ja usaldusväärselt töötada tohutu hulga teabega.
Kui olete pikka aega rakendusi või andmebaasi taustaprogramme kujundanud, olete tõenäoliselt kirjutanud koodi leheküljeliste päringute käitamiseks. Näiteks nii:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Kuidas see on?
Aga kui te oma lehekülgi nii tegite, siis pean kahjuks tõdema, et te ei teinud seda kõige tõhusamal viisil.
Kas sa tahad mulle vastu vaielda? . , и Nad juba kasutavad tehnikaid, millest ma täna rääkida tahan.
Nimetage vähemalt üks taustaarendaja, kes pole kunagi kasutanud OFFSET и LIMIT lehekülgedega päringute tegemiseks. MVP-s (minimaalne elujõuline toode) ja projektides, kus kasutatakse väikeseid andmemahtusid, on see lähenemisviis üsna rakendatav. See "lihtsalt töötab", niiöelda.
Kuid kui teil on vaja luua töökindlaid ja tõhusaid süsteeme nullist, peaksite eelnevalt hoolitsema sellistes süsteemides kasutatavate andmebaaside päringute tõhususe eest.
Täna räägime probleemidest, mis on seotud sageli kasutatavate (liiga halbade) lehekülgedega päringumootorite juurutustega ja kuidas selliste päringute täitmisel kõrget jõudlust saavutada.
Mis on OFFSET-il ja LIMIT-il viga?
Nagu juba öeldud, OFFSET и LIMIT Nad toimivad hästi projektides, mis ei pea töötama suurte andmemahtudega.
Probleem tekib siis, kui andmebaas kasvab nii suureks, et see ei mahu enam serveri mällu. Selle andmebaasiga töötades peate siiski kasutama lehekülgede lehtedega päringuid.
Selle probleemi ilmnemiseks peab olema olukord, kus DBMS kasutab iga lehekülgedega päringu puhul ebaefektiivset täielikku tabelikontrolli (samal ajal võib esineda sisestamise ja kustutamise toiminguid ning me ei vaja aegunud andmeid!).
Mis on "täielik tabelikontroll" (või "järjestikune tabelikontroll", järjestikune skannimine)? See on toiming, mille käigus DBMS loeb järjestikku iga tabeli rida, st selles sisalduvaid andmeid, ja kontrollib nende vastavust antud tingimusele. Seda tüüpi tabelite skannimine on teadaolevalt kõige aeglasem. Fakt on see, et selle käivitamisel tehakse palju sisend-/väljundoperatsioone, mis hõlmavad serveri ketta alamsüsteemi. Olukorda halvendavad kettale salvestatud andmetega töötamise viivitused ja asjaolu, et andmete ülekandmine kettalt mällu on ressursimahukas toiming.
Näiteks teil on kirjeid 100000000 XNUMX XNUMX kasutaja kohta ja käivitate päringu koos konstruktsiooniga OFFSET 50000000. See tähendab, et DBMS peab laadima kõik need kirjed (ja meil pole neid isegi vaja!), salvestama need mällu ja pärast seda võtma näiteks 20 tulemust, mis on esitatud LIMIT.
Oletame, et see võib välja näha selline: "valige read vahemikus 50000 50020 kuni 100000 50000 vahemikus XNUMX XNUMX". See tähendab, et päringu täitmiseks peab süsteem esmalt laadima XNUMX XNUMX rida. Kas näete, kui palju tarbetut tööd ta peab tegema?
Kui te mind ei usu, vaadake näidet, mille funktsioonide abil lõin .
Näide aadressil db-fiddle.com
Seal, vasakul, põllul Schema SQL, on kood, mis lisab andmebaasi 100000 XNUMX rida, ja paremal väljal Query SQL, kuvatakse kaks päringut. Esimene, aeglane, näeb välja selline:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Ja teine, mis on sama probleemi tõhus lahendus, on järgmine:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Nende taotluste täitmiseks klõpsake lihtsalt nuppu Run lehe ülaosas. Pärast seda võrdleme teavet päringu täitmise aja kohta. Selgub, et ebaefektiivse päringu täitmine võtab vähemalt 30 korda kauem aega kui teise täitmine (see aeg erineb käitamise lõikes; näiteks võib süsteem teatada, et esimese päringu täitmiseks kulus 37 ms, kuid päringu täitmine võtab aega 1 ms teine - XNUMX ms).
Ja kui andmeid on rohkem, näeb kõik veelgi hullem välja (selles veendumiseks vaadake minu 10 miljoni reaga).
See, mida me just arutasime, peaks andma teile ülevaate sellest, kuidas andmebaasipäringuid tegelikult töödeldakse.
Pange tähele, et mida suurem on väärtus OFFSET — seda kauem võtab taotluse täitmine aega.
Mida peaksin kasutama OFFSET ja LIMIT kombinatsiooni asemel?
Kombinatsiooni asemel OFFSET и LIMIT Tasub kasutada järgmise skeemi järgi ehitatud konstruktsiooni:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
See on päringu täitmine kursoripõhise lehekülgedega.
Selle asemel, et praeguseid kohapeal hoida OFFSET и LIMIT ja edastama need iga päringuga, peate salvestama viimati vastuvõetud primaarvõtme (tavaliselt on see ID) Ja LIMIT, selle tulemusel saadakse ülaltooduga sarnased päringud.
Miks? Asi on selles, et määrates selgesõnaliselt viimase loetud rea identifikaatori, ütlete oma DBMS-ile, kust ta peab alustama vajalike andmete otsimist. Lisaks toimub otsing tänu võtme kasutamisele tõhusalt, süsteemi ei pea häirima määratud vahemikust väljapoole jäävad liinid.
Vaatame järgmist erinevate päringute toimivuse võrdlust. Siin on ebaefektiivne päring.
Aeglane taotlus
Ja siin on selle taotluse optimeeritud versioon.
Kiire taotlus
Mõlemad päringud tagastavad täpselt sama koguse andmeid. Kuid esimese jaoks kulub 12,80 sekundit ja teise jaoks 0,01 sekundit. Kas tunnete erinevust?
Võimalikud probleemid
Pakutud päringumeetodi tõhusaks toimimiseks peab tabelis olema veerg (või veerud), mis sisaldavad kordumatuid järjestikuseid indekseid, näiteks täisarvu identifikaatorit. Mõnel konkreetsel juhul võib see määrata selliste päringute kasutamise edukuse andmebaasiga töötamise kiiruse suurendamiseks.
Loomulikult tuleb päringute koostamisel arvestada tabelite spetsiifilise arhitektuuriga ja valida need mehhanismid, mis olemasolevatel tabelitel kõige paremini töötavad. Näiteks kui teil on vaja töötada suure hulga seotud andmetega päringutega, võib see teile huvi pakkuda artiklit.
Kui seisame silmitsi primaarvõtme puudumise probleemiga, näiteks kui meil on mitu-mitmele seosega tabel, siis kasutatakse traditsioonilist lähenemist OFFSET и LIMIT, sobib meile garanteeritult. Kuid selle kasutamine võib põhjustada potentsiaalselt aeglaseid päringuid. Sellistel juhtudel soovitaksin kasutada automaatselt suurenevat primaarvõtit, isegi kui seda on vaja ainult lehekülgedega päringute käsitlemiseks.
Kui olete sellest teemast huvitatud - , и - mitu kasulikku materjali.
Tulemused
Peamine järeldus, mille saame teha, on see, et olenemata sellest, millise suurusega andmebaasidest me räägime, on alati vaja analüüsida päringu täitmise kiirust. Tänapäeval on lahenduste mastaapsus äärmiselt oluline ja kui kõik on teatud süsteemiga töötamise algusest peale õigesti kavandatud, võib see tulevikus säästa arendajat paljudest probleemidest.
Kuidas analüüsite ja optimeerite andmebaasipäringuid?
Allikas: www.habr.com