Tie laiki, kad nebija jāuztraucas par datu bāzes veiktspējas optimizēšanu, ir pagājuši. Laiks nestāv uz vietas. Katrs jaunais tehnoloģiju uzņēmējs vēlas izveidot nākamo Facebook, vienlaikus cenšoties savākt visus pieejamos datus. Uzņēmumiem šie dati ir nepieciešami, lai labāk apmācītu modeļus, kas palīdz pelnīt naudu. Šādos apstākļos programmētājiem ir jāizveido API, kas ļauj ātri un droši strādāt ar milzīgu informācijas apjomu.
Ja kādu laiku esat izstrādājis lietojumprogrammu vai datu bāzes aizmugursistēmas, iespējams, esat uzrakstījis kodu, lai palaistu vaicājumus ar lappusēm. Piemēram, šādi:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Kā tas ir?
Bet, ja šādi veicāt lappušu lapu, atvainojos, ka to izdarījāt ne visefektīvākajā veidā.
Vai vēlaties man iebilst? . , и Viņi jau izmanto paņēmienus, par kuriem es šodien vēlos runāt.
Nosauciet vismaz vienu aizmugursistēmas izstrādātāju, kurš nekad nav izmantojis OFFSET и LIMIT lai veiktu lappušu vaicājumus. MVP (minimālais dzīvotspējīgais produkts) un projektos, kur tiek izmantots neliels datu apjoms, šī pieeja ir diezgan piemērota. Tas, tā sakot, "tikai darbojas".
Bet, ja jums ir jāizveido uzticamas un efektīvas sistēmas no nulles, jums iepriekš jāparūpējas par vaicājumu efektivitāti šādās sistēmās izmantotajās datu bāzēs.
Šodien mēs runāsim par problēmām, kas saistītas ar bieži lietotām (pārāk sliktiem) lappušu vaicājumu dzinēju implementācijām, un to, kā sasniegt augstu veiktspēju, izpildot šādus vaicājumus.
Kas vainas OFFSET un LIMIT?
Kā jau teikts, OFFSET и LIMIT Tie labi darbojas projektos, kuriem nav nepieciešams strādāt ar lielu datu apjomu.
Problēma rodas, kad datu bāze izaug līdz tādam izmēram, ka tā vairs neietilpst servera atmiņā. Tomēr, strādājot ar šo datu bāzi, jums ir jāizmanto vaicājumi ar lappusēm.
Lai šī problēma izpaustos, ir jābūt situācijai, kurā DBVS izmanto neefektīvu Full Table Scan operāciju katrā lappušu vaicājumā (kamēr var notikt ievietošanas un dzēšanas darbības, un mums nav vajadzīgi novecojuši dati!).
Kas ir “pilna tabulas skenēšana” (vai “secīgā tabulas skenēšana”, secīgā skenēšana)? Šī ir darbība, kuras laikā DBVS secīgi nolasa katru tabulas rindu, tas ir, tajā ietvertos datus, un pārbauda to atbilstību noteiktajam nosacījumam. Ir zināms, ka šāda veida tabulas skenēšana ir vislēnākā. Fakts ir tāds, ka, kad tas tiek izpildīts, tiek veiktas daudzas ievades/izvades darbības, kas ietver servera diska apakšsistēmu. Situāciju pasliktina latentums, kas saistīts ar darbu ar diskos saglabātajiem datiem, kā arī tas, ka datu pārsūtīšana no diska uz atmiņu ir resursietilpīga darbība.
Piemēram, jums ir ieraksti par 100000000 XNUMX XNUMX lietotāju un jūs izpildāt vaicājumu ar konstrukciju OFFSET 50000000. Tas nozīmē, ka DBVS būs jāielādē visi šie ieraksti (un tie mums pat nav vajadzīgi!), jāievieto atmiņā un pēc tam jāņem, teiksim, 20 rezultāti, par kuriem ziņots LIMIT.
Pieņemsim, ka tas varētu izskatīties šādi: "atlasīt rindas no 50000 līdz 50020 no 100000". Tas nozīmē, ka sistēmai vispirms būs jāielādē 50000 XNUMX rindu, lai pabeigtu vaicājumu. Vai redzat, cik daudz lieku darbu viņai būs jādara?
Ja neticat, apskatiet piemēru, ko es izveidoju, izmantojot funkcijas .
Piemērs vietnē db-fiddle.com
Tur, pa kreisi, laukā Schema SQL, ir kods, kas datu bāzē ievieto 100000 XNUMX rindu, un labajā pusē laukā Query SQL, tiek parādīti divi vaicājumi. Pirmais, lēnais, izskatās šādi:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Un otrais, kas ir efektīvs tās pašas problēmas risinājums, ir šāds:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Lai izpildītu šos pieprasījumus, vienkārši noklikšķiniet uz pogas Run lapas augšdaļā. Pēc tam mēs salīdzinām informāciju par vaicājuma izpildes laiku. Izrādās, ka neefektīva vaicājuma izpilde prasa vismaz 30 reizes ilgāku laiku nekā otrā (šis laiks atšķiras atkarībā no palaišanas; piemēram, sistēma var ziņot, ka pirmā vaicājuma izpilde prasīja 37 ms, bet otrais — 1 ms).
Un, ja būs vairāk datu, tad viss izskatīsies vēl sliktāk (lai par to pārliecinātos, apskatiet manu ar 10 miljoniem rindu).
Tas, ko mēs tikko apspriedām, sniedz jums ieskatu par to, kā faktiski tiek apstrādāti datu bāzes vaicājumi.
Lūdzu, ņemiet vērā, ka jo lielāka vērtība OFFSET — jo ilgāks laiks prasīs pieprasījuma aizpildīšanai.
Ko man vajadzētu izmantot OFFSET un LIMIT kombinācijas vietā?
Kombinācijas vietā OFFSET и LIMIT Ir vērts izmantot struktūru, kas veidota saskaņā ar šādu shēmu:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Šī ir vaicājuma izpilde ar uz kursoru balstītu lappušu kārtošanu.
Tā vietā, lai lokāli glabātu pašreizējos OFFSET и LIMIT un pārsūtīt tos ar katru pieprasījumu, jums ir jāsaglabā pēdējā saņemtā primārā atslēga (parasti tā ir ID) Un LIMIT, kā rezultātā tiks iegūti iepriekšminētajiem līdzīgi vaicājumi.
Kāpēc? Lieta ir tāda, ka, skaidri norādot pēdējās lasītās rindas identifikatoru, jūs norādāt savai DBVS, kur tai jāsāk meklēt nepieciešamie dati. Turklāt meklēšana, pateicoties atslēgas izmantošanai, tiks veikta efektīvi, sistēmai nebūs jānovērš līnijas ārpus noteiktā diapazona.
Apskatīsim tālāk sniegto dažādu vaicājumu veiktspējas salīdzinājumu. Šeit ir neefektīvs vaicājums.
Lēns pieprasījums
Un šeit ir šī pieprasījuma optimizēta versija.
Ātrs pieprasījums
Abi vaicājumi atgriež tieši tādu pašu datu apjomu. Bet pirmā pabeigšana prasa 12,80 sekundes, bet otrā - 0,01 sekundi. Vai jūtat atšķirību?
Iespējamās problēmas
Lai piedāvātā vaicājuma metode darbotos efektīvi, tabulā ir jābūt kolonnai (vai kolonnām), kas satur unikālus, secīgus indeksus, piemēram, vesela skaitļa identifikatoru. Dažos īpašos gadījumos tas var noteikt šādu vaicājumu izmantošanas panākumus, lai palielinātu darba ātrumu ar datu bāzi.
Protams, veidojot vaicājumus, ir jāņem vērā tabulu specifiskā arhitektūra un jāizvēlas tie mehānismi, kas vislabāk darbosies esošajās tabulās. Piemēram, ja jums ir jāstrādā ar vaicājumiem ar lielu saistīto datu apjomu, tas var būt interesanti rakstu.
Ja mēs saskaramies ar problēmu, ka trūkst primārās atslēgas, piemēram, ja mums ir tabula ar relāciju daudzi pret daudziem, tad tradicionālā pieeja OFFSET и LIMIT, ir garantēts, ka tas mums ir piemērots. Taču tā izmantošana var izraisīt potenciāli lēnus vaicājumus. Šādos gadījumos es ieteiktu izmantot automātiski pieaugošu primāro atslēgu, pat ja tā ir nepieciešama tikai lappušu vaicājumu apstrādei.
Ja jūs interesē šī tēma - , и - vairāki noderīgi materiāli.
Rezultāti
Galvenais secinājums, ko varam izdarīt, ir tāds, ka neatkarīgi no datu bāzu lieluma mēs runājam, vienmēr ir nepieciešams analizēt vaicājuma izpildes ātrumu. Mūsdienās risinājumu mērogojamība ir ārkārtīgi svarīga, un, ja viss ir pareizi izstrādāts jau no paša sākuma, strādājot pie noteiktas sistēmas, tas nākotnē var glābt izstrādātāju no daudzām problēmām.
Kā jūs analizējat un optimizējat datu bāzes vaicājumus?
Avots: www.habr.com