Ärge kasutage küsitlustes OFFSET ja LIMIT lehekülgede jagamise korral

Aeg on möödas, kus ei pidanud muretsema andmebaaside jõudluse optimeerimise üle. Aeg ei seisa paigal. Iga uus tehnoloogiatehnoloogia ärimees soovib luua järgmist Facebooki, püüdes samal ajal koguda kõiki andmeid, millele nad ligi pääsevad. Need andmed on ettevõttele vajalikud kvaliteetsemate mudelite õpetamiseks, mis aitavad kasumit teenida. Sellistes tingimustes peavad arendajad looma selliseid API-sid, mis võimaldavad kiiresti ja usaldusväärselt töötada hiiglaslike andmemahtudega.

Kui olete juba mõnda aega tegelenud rakenduste serveriplokkide või andmebaaside projekteerimisega, olete tõenäoliselt kirjutanud koodi leheküljepõhiste päringute täitmiseks. Näiteks selline:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Kas tõesti?

Kuid kui te tegite leheküljepõhise jagamise just nii, siis pean kurbusega märkima, et tegite seda kaugel mitte kõige tõhusamal viisil.

Kas soovite mul vastu vaielda? Saate ei kulutama aega. Slack, Shopify ja Mixmax juba kasutavad tehnikaid, millest ma täna rääkida tahan.

Nimetage vähemalt üks taustaarendaja, kes pole kunagi kasutanud CHECKSUM_AGG ja LIMIT leheküljepõhiste päringute täitmiseks. MVP (Minimum Viable Product, minimaalne elujõuline toode) ja projektides, kus kasutatakse väikseid andmemahte, on see lähenemine täiesti rakendatav. See, nii öelda, "lihtsalt töötab".

Kuid kui on vaja nullist luua usaldusväärseid ja tõhusaid süsteeme, tuleb eelnevalt hoolitseda andmebaaside päringute tõhususe eest, mida sellistes süsteemides kasutatakse.

Täna räägime probleemidest, mis on seotud laialdaselt kasutatavate (kahjuks) leheküljepõhiste päringumehanismidega, ja kuidas saavutada kõrget jõudlust selliste päringute täitmisel.

Mis on valesti OFFSETi ja LIMITiga?

Kuidas juba mainitud, CHECKSUM_AGG ja LIMIT näitavad end väga hästi projektides, kus ei ole vaja töötada suurte andmemahutega.

Probleem tekib siis, kui andmebaas kasvab nii suureks, et see ei mahu enam serveri mällu. Kuid samal ajal tuleb sellise andmebaasiga töötamisel kasutada leheküljepõhiseid päringuid.

Probleemi ilmenemiseks peab olema olukord, kus andmebaas kasutab iga päringu täitmiseks ebaefektiivset täielikku tabeli skaneerimist (Full Table Scan) leheküljed jagades (samal ajal võivad toimuda andmete sisestamis- ja kustutamisoperatsioonid ning me ei vaja aegunud andmeid!).

Mis on "täielik tabeli skaneerimine" (või "järjestikune tabeli vaatamine", Sequential Scan)? See on operatsioon, mille käigus andmebaas loeb järjestikku iga tabeli rida, st sisaldatud andmeid, ja kontrollib neid antud tingimusele vastavuse suhtes. On teada, et see tüüp tabeli skaneerimist on aeglasem. Selle põhjuseks on see, et selle täitmise käigus toimub palju sisend-/väljundoperatsioone, mis hõlmavad serveri kettasüsteemi. Seisundit halvendavad viivitused, mis kaasnevad kettal hoitud andmete töötlemisega, ja see, et andmete edastamine kettalt mällu on ressursimahukas operatsioon.

Näiteks, teil on kirjed 100000000 kasutajast ja teete päringu koos konstruktsiooniga OFFSET 50000000. See tähendab, et andmebaas peab laadima kõik need kirjed (ja need ei ole meile isegi vajalikud!), pannakse need mällu ja seejärel, oletame, võtab 20 tulemust, millest on teavitatud LIMIT.

Öeldes, et see võiks välja näha nii: "valige read 50000 kuni 50020 100000-st". See tähendab, et süsteem peab päringu täitmiseks esmalt laadima 50000 rida. Näete, kui palju tarbetut tööd tal tuleb teha?

Kui ei usu — vaadake näidet, mille ma lõin, kasutades võimalusi db-fiddle.com. 

Näidis db-fiddle.com-is

Seal, vasakul, valdkonnas Schema SQL, on kood, mis sisestab andmebaasi 100000 rida, ja paremal, valdkonnas Query SQL, on näidatud kaks päringut. Esimene, aeglane, näeb välja nii:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Ja teine, mis on sama probleemi efektiivne lahendus, on nii:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Nende päringute täitmiseks piisab, kui vajutada nuppu Käita lehe ülaosas. Kui te seda teete, saame võrrelda päringute täitmise aega. Selgub, et ebaefektiivse päringu täitmiseks kulub vähemalt 30 korda rohkem aega kui teise (käivituse vahel võib see aeg varieeruda, näiteks süsteem võib teatada, et esimese päringu täitmine kestis 37 ms, samas kui teise oma - 1 ms).

Ja kui andmeid on rohkem, siis näeb see veel hullem välja (selle kinnitamiseks vaadake minu näide 10 miljoni reaga).

See, millest me just rääkisime, peaks andma teile arusaamise, kuidas päringud tegelikult andmebaasidesse jõuavad.

Pidage meeles, et mida suurem on väärtus CHECKSUM_AGG seda kauem päring kestab.

Mida peaks kasutama OFFSET-i ja LIMIT-i kombinatsiooni asemel?

OFFSET-i ja LIMIT-i kombinatsiooni asemel CHECKSUM_AGG ja LIMIT on parem kasutada konstruktsiooni järgmisel viisil:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

See on päringu täitmine, mis põhineb lehejaotusel (Cursor based pagination).

Ahnem kusagil kohapeal hoida praeguseid CHECKSUM_AGG ja LIMIT ja edastada neid iga päringuga, peaksite hoidma viimasena saadud peavõtit (tavaliselt on see ID) ja LIMIT, mis viib selleni, et päringud sarnanevad eespool toodud näiteks.

Miks? Asi on selles, et kui te näitate selgesõnaliselt viidatud viimase ridade identifikaatorit, anname oma andmebaasihaldus süsteemile teada, kust alustada vajalike andmete otsimist. Ja see otsing, kasutades võtit, toimub tõhusalt, süsteem ei pea kõrvalistele ridadele tähelepanu pöörama, mis jäävad määratud vahemikust välja.

Vaadakem järgmisi erinevate päringute jõudluse võrdlusi. Siin on ebaefektiivne päring.

Aeglane päring

Aga siin on selle päringu optimeeritud versioon.

Kiire päring

Mõlemad päringud tagastavad täpselt sama hulga andmeid. Kuid esimese täitmine kestab 12,80 sekundit, samas kui teisel vaid 0,01 sekundit. Kas märkate vahet?

Võimalikud probleemid

Ettepaneku meetodi tõhusaks toimimiseks peab tabelis olema veerg (või veerud), mis sisaldavad ainulaadseid, järjestikuseid indekse, näiteks täisarvulist identifikaatorit. Mõnedes spetsiifilistes olukordades võib see määrata taoliste päringute kasutamise edu andmebaasi töö kiirusel.

Muidugi tuleb päringute koostamisel arvesse võtta tabelite arhitektuuri eripära ja valida need mehhanismid, mis toovad parima tulemuse olemasolevates tabelites. Näiteks, kui peate töötama suurte seotud andmahulkadega päringutes, võib teile huvi pakkuda see artikkel.

Kui seisame silmitsi esmase võtme puudumise probleemiga, näiteks kui tabelis on "palju-palju" suhe, siis traditsiooniline lähenemine, mis hõlmab CHECKSUM_AGG ja LIMIT, sobib meile kindlasti. Kuid selle rakendamine võib viia potentsiaalselt aeglaste päringute täitmiseni. Niisugustes olukordades soovitaksin kasutada automaatse suurenemisega esmase võtit, isegi kui see on vajalik ainult päringute leheküntede kaupa täitmiseks.

Kui see teema teid huvitab — siin on, siin on ja siin on — mõned kasulikud materjalid.

Summary

Peamine järeldus, mille võime teha, on see, et olenemata andmebaaside suurusest, tuleb alati analüüsida päringute täitmise kiirus. Tänapäeval on lahenduste skaleeritavus ülioluline, ning kui juba süsteemi tööplaneerimise alguses on kõik õigesti projekteeritud, võib see edaspidi aidata arendajal kokku puutuda paljude probleemidega.

Kuidas te analüüsite ja optimeerite andmebaaside päringuid?

Allikas: habr.com