Lumipas ang mga araw na hindi mo kailangang mag-alala tungkol sa pag-optimize ng pagganap ng database. Ang oras ay hindi tumitigil. Nais ng bawat bagong tech na negosyante na lumikha ng susunod na Facebook, habang sinusubukang kolektahin ang lahat ng data na maaari nilang makuha. Kailangan ng mga negosyo ang data na ito para mas mahusay na sanayin ang mga modelong makakatulong sa kanilang kumita ng pera. Sa ganitong mga kundisyon, kailangan ng mga programmer na lumikha ng mga API na nagbibigay-daan sa kanila na mabilis at mapagkakatiwalaang magtrabaho kasama ang malaking halaga ng impormasyon.
Kung nagdidisenyo ka ng mga application o database backend para sa anumang haba ng panahon, malamang na nagsulat ka ng code upang magpatakbo ng mga paginated na query. Halimbawa, tulad nito:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Ang paraan ito ay?
Ngunit kung ganito ang ginawa mo sa iyong pagination, ikinalulungkot kong sabihin na hindi mo ito nagawa sa pinakamabisang paraan.
Gusto mo bang tumutol sa akin? . , ΠΈ Ginagamit na nila ang mga technique na gusto kong pag-usapan ngayon.
Pangalan ng kahit isang backend developer na hindi pa nagagamit OFFSET ΠΈ LIMIT upang magsagawa ng paginated na mga query. Sa MVP (Minimum Viable Product) at sa mga proyekto kung saan maliit na halaga ng data ang ginagamit, ang diskarte na ito ay lubos na naaangkop. Ito ay "gumagana lang," wika nga.
Ngunit kung kailangan mong lumikha ng maaasahan at mahusay na mga system mula sa simula, dapat kang mag-ingat nang maaga tungkol sa kahusayan ng pag-query sa mga database na ginagamit sa mga naturang sistema.
Ngayon ay pag-uusapan natin ang tungkol sa mga problema sa karaniwang ginagamit (napakasama) na mga pagpapatupad ng mga paginated query engine, at kung paano makamit ang mataas na pagganap kapag nagsasagawa ng mga naturang query.
Ano ang mali sa OFFSET at LIMIT?
Tulad ng nasabi na, OFFSET ΠΈ LIMIT Ang mga ito ay mahusay na gumaganap sa mga proyekto na hindi kailangang gumana sa malaking halaga ng data.
Ang problema ay lumitaw kapag ang database ay lumaki sa isang sukat na hindi na ito magkasya sa memorya ng server. Gayunpaman, kapag nagtatrabaho sa database na ito, kailangan mong gumamit ng mga paginated na query.
Para lumitaw ang problemang ito, dapat mayroong sitwasyon kung saan ang DBMS ay gumagamit ng hindi mahusay na operasyon ng Full Table Scan sa bawat paginated na query (habang maaaring mangyari ang mga pagpapatakbo ng pagpasok at pagtanggal , at hindi namin kailangan ng lumang data!).
Ano ang "full table scan" (o "sequential table scan", Sequential Scan)? Ito ay isang operasyon kung saan sunud-sunod na binabasa ng DBMS ang bawat hilera ng talahanayan, iyon ay, ang data na nakapaloob dito, at sinusuri ang mga ito para sa pagsunod sa isang partikular na kundisyon. Ang ganitong uri ng table scan ay kilala bilang ang pinakamabagal. Ang katotohanan ay kapag ito ay naisakatuparan, maraming input/output na operasyon ang ginaganap na may kinalaman sa disk subsystem ng server. Ang sitwasyon ay pinalala ng latency na nauugnay sa pagtatrabaho sa data na nakaimbak sa mga disk, at ang katotohanan na ang paglilipat ng data mula sa disk patungo sa memorya ay isang resource-intensive na operasyon.
Halimbawa, mayroon kang mga talaan ng 100000000 user at nagpapatakbo ka ng query na may construct OFFSET 50000000. Nangangahulugan ito na ang DBMS ay kailangang i-load ang lahat ng mga rekord na ito (at hindi na namin kailangan ang mga ito!), ilagay ang mga ito sa memorya, at pagkatapos na kumuha, sabihin, 20 resulta na iniulat sa LIMIT.
Sabihin nating maaaring ganito ang hitsura nito: "pumili ng mga row mula 50000 hanggang 50020 mula 100000". Ibig sabihin, kakailanganin muna ng system na mag-load ng 50000 row para makumpleto ang query. Nakikita mo ba kung gaano karaming hindi kinakailangang trabaho ang kailangan niyang gawin?
Kung hindi ka naniniwala sa akin, tingnan ang halimbawang ginawa ko gamit ang mga feature .
Halimbawa sa db-fiddle.com
Doon, sa kaliwa, sa field Schema SQL, mayroong code na naglalagay ng 100000 row sa database, at sa kanan, sa field Query SQL, ipinapakita ang dalawang query. Ang una, mabagal, ganito ang hitsura:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
At ang pangalawa, na isang epektibong solusyon sa parehong problema, ay ganito:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Upang matupad ang mga kahilingang ito, i-click lamang ang pindutan Run sa tuktok ng pahina. Matapos magawa ito, inihahambing namin ang impormasyon tungkol sa oras ng pagpapatupad ng query. Lumalabas na ang pagsasagawa ng isang hindi mahusay na query ay tumatagal ng hindi bababa sa 30 beses na mas mahaba kaysa sa pagpapatupad ng pangalawa (sa oras na ito ay nag-iiba mula sa run hanggang run; halimbawa, maaaring iulat ng system na ang unang query ay tumagal ng 37 ms upang makumpleto, ngunit ang pagpapatupad ng pangalawa - 1 ms).
At kung mayroong higit pang data, kung gayon ang lahat ay magiging mas masahol pa (upang kumbinsihin ito, tingnan ang aking na may 10 milyong hanay).
Ang napag-usapan lang namin ay dapat magbigay sa iyo ng ilang insight sa kung paano aktwal na pinoproseso ang mga query sa database.
Pakitandaan na mas mataas ang halaga OFFSET β mas matagal bago makumpleto ang kahilingan.
Ano ang dapat kong gamitin sa halip na kumbinasyon ng OFFSET at LIMIT?
Sa halip na kumbinasyon OFFSET ΠΈ LIMIT Ito ay nagkakahalaga ng paggamit ng isang istraktura na binuo ayon sa sumusunod na pamamaraan:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Ito ay query execution na may cursor based pagination.
Sa halip na mag-imbak ng mga kasalukuyan nang lokal OFFSET ΠΈ LIMIT at ipadala ang mga ito sa bawat kahilingan, kailangan mong iimbak ang huling natanggap na pangunahing key (kadalasan ito ay ID) At LIMIT, bilang resulta, ang mga query na katulad ng nasa itaas ay makukuha.
Bakit? Ang punto ay sa pamamagitan ng tahasang pagtukoy sa identifier ng huling row na nabasa, sasabihin mo sa iyong DBMS kung saan kailangan nitong simulan ang paghahanap para sa kinakailangang data. Bukod dito, ang paghahanap, salamat sa paggamit ng susi, ay isasagawa nang mahusay; ang system ay hindi kailangang magambala ng mga linya sa labas ng tinukoy na saklaw.
Tingnan natin ang sumusunod na paghahambing ng pagganap ng iba't ibang mga query. Narito ang isang hindi epektibong query.
Mabagal na kahilingan
At narito ang isang naka-optimize na bersyon ng kahilingang ito.
Mabilis na kahilingan
Ang parehong mga query ay nagbabalik ng eksaktong parehong dami ng data. Ngunit ang una ay tumatagal ng 12,80 segundo upang makumpleto, at ang pangalawa ay tumatagal ng 0,01 segundo. Nararamdaman mo ba ang pagkakaiba?
Posibleng mga problema
Para epektibong gumana ang iminungkahing paraan ng query, ang talahanayan ay dapat na may column (o mga column) na naglalaman ng mga natatanging, sequential index, gaya ng integer identifier. Sa ilang partikular na kaso, maaaring matukoy nito ang tagumpay ng paggamit ng mga naturang query para mapabilis ang pagtatrabaho sa database.
Naturally, kapag gumagawa ng mga query, kailangan mong isaalang-alang ang partikular na arkitektura ng mga talahanayan at piliin ang mga mekanismong iyon na pinakamahusay na gagana sa mga umiiral na talahanayan. Halimbawa, kung kailangan mong gumawa ng mga query na may malalaking volume ng kaugnay na data, maaari mong makitang kawili-wili ito artikulo.
Kung tayo ay nahaharap sa problema ng pagkawala ng isang pangunahing susi, halimbawa, kung mayroon tayong isang talahanayan na may maraming-sa-maraming relasyon, kung gayon ang tradisyonal na diskarte ng paggamit OFFSET ΠΈ LIMIT, ay garantisadong angkop sa amin. Ngunit ang paggamit nito ay maaaring magresulta sa potensyal na mabagal na mga query. Sa ganitong mga kaso, inirerekumenda ko ang paggamit ng isang auto-incrementing primary key, kahit na ito ay kinakailangan lamang upang mahawakan ang mga paginated na query.
Kung interesado ka sa paksang ito - , ΠΈ - ilang mga kapaki-pakinabang na materyales.
Mga resulta ng
Ang pangunahing konklusyon na maaari nating iguhit ay, anuman ang laki ng mga database na pinag-uusapan natin, palaging kinakailangan upang pag-aralan ang bilis ng pagpapatupad ng query. Sa ngayon, ang scalability ng mga solusyon ay napakahalaga, at kung ang lahat ay idinisenyo nang tama mula sa simula ng pagtatrabaho sa isang tiyak na sistema, ito, sa hinaharap, ay makakapagligtas sa developer mula sa maraming problema.
Paano mo sinusuri at ino-optimize ang mga query sa database?
Pinagmulan: www.habr.com