Ayaw gamita ang OFFSET ug LIMIT sa paginated nga mga pangutana

Wala na ang mga adlaw nga dili ka kinahanglan mabalaka bahin sa pag-optimize sa pasundayag sa database. Ang panahon dili mohunong. Ang matag bag-ong tech entrepreneur gusto nga maghimo sa sunod nga Facebook, samtang naningkamot sa pagkolekta sa tanan nga datos nga ilang makuha sa ilang mga kamot. Gikinahanglan sa mga negosyo kini nga datos aron mas maayo nga magbansay sa mga modelo nga makatabang kanila nga makakwarta. Sa ingon nga mga kahimtang, ang mga programmer kinahanglan nga maghimo mga API nga nagtugot kanila nga dali ug kasaligan nga magtrabaho uban ang daghang kasayuran.

Kung nagdesinyo ka sa aplikasyon o mga backend sa database sa bisan unsang gitas-on sa panahon, lagmit nagsulat ka og code aron magpadagan sa mga paginated nga pangutana. Pananglitan, sama niini:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Unsa kini?

Apan kung ingon niini ang imong paghimo sa imong pagination, pasayloa ako nga isulti nga wala nimo kini nahimo sa labing episyente nga paagi.

Gusto ka ba nga mosupak kanako? Mahimo nimo dili mogahin ang panahon. nga tapulan, Shopify и Mixmax Gigamit na nila ang mga teknik nga gusto nakong hisgutan karon.

Pagngalan ug bisan usa ka backend developer nga wala pa makagamit OFFSET и LIMIT sa pagbuhat sa mga pangutana sa pahina. Sa MVP (Minimum Viable Product) ug sa mga proyekto diin gamay nga kantidad sa datos ang gigamit, kini nga pamaagi magamit. Kini "motrabaho lang," ingnon ta.

Apan kung kinahanglan nimo nga maghimo kasaligan ug episyente nga mga sistema gikan sa wala, kinahanglan nga mag-amping ka daan bahin sa pagkaepektibo sa pagpangutana sa mga database nga gigamit sa ingon nga mga sistema.

Karon maghisgot kita bahin sa mga problema sa kasagarang gigamit (grabe kaayo) nga mga pagpatuman sa mga paginated nga mga makina sa pangutana, ug kung giunsa pagkab-ot ang taas nga pasundayag kung gipatuman ang ingon nga mga pangutana.

Unsa ang sayup sa OFFSET ug LIMIT?

Ingon sa giingon na, OFFSET и LIMIT Maayo ang ilang nahimo sa mga proyekto nga dili kinahanglan nga magtrabaho sa daghang mga datos.

Ang problema motungha kung ang database motubo sa ingon nga gidak-on nga dili na mohaum sa panumduman sa server. Bisan pa, kung nagtrabaho kauban kini nga database, kinahanglan nimo nga gamiton ang paginated nga mga pangutana.

Aron kini nga problema magpakita sa iyang kaugalingon, kinahanglan adunay usa ka sitwasyon diin ang DBMS modangop sa usa ka dili episyente nga Full Table Scan nga operasyon sa matag paginated nga pangutana (samtang ang pagsal-ot ug pagtangtang nga mga operasyon mahimong mahitabo, ug wala kami magkinahanglan og mga outdated data!).

Unsa ang "full table scan" (o "sequential table scan", Sequential Scan)? Kini usa ka operasyon diin ang DBMS nagsunud-sunod nga nagbasa sa matag laray sa lamesa, nga mao, ang datos nga naa niini, ug gisusi kini alang sa pagsunod sa gihatag nga kondisyon. Kini nga matang sa pag-scan sa lamesa nahibal-an nga labing hinay. Ang kamatuoran mao nga kung kini gipatuman, daghang input/output nga mga operasyon ang gihimo nga naglambigit sa disk subsystem sa server. Ang sitwasyon gipasamot tungod sa latency nga nalangkit sa pagtrabaho uban sa mga data nga gitipigan sa mga disk, ug ang kamatuoran nga ang pagbalhin sa data gikan sa disk ngadto sa memorya usa ka resource-intensive nga operasyon.

Pananglitan, ikaw adunay mga rekord sa 100000000 ka mga tiggamit ug ikaw nagpadagan sa usa ka pangutana uban sa pagtukod OFFSET 50000000. Kini nagpasabot nga ang DBMS kinahanglan nga magkarga sa tanan niini nga mga rekord (ug dili gani nato kini kinahanglan!), ibutang kini sa panumduman, ug human niana pagkuha, ingnon ta, 20 ka mga resulta nga gitaho sa LIMIT.

Ingnon ta nga ingon niini ang hitsura: "pagpili sa mga laray gikan sa 50000 hangtod 50020 gikan sa 100000". Kana mao, ang sistema kinahanglan una nga mag-load sa 50000 nga mga laray aron makompleto ang pangutana. Nakita ba nimo kung unsa ka daghang wala kinahanglana nga trabaho ang kinahanglan niyang buhaton?

Kung dili ka motuo kanako, tan-awa ang pananglitan nga akong gibuhat gamit ang mga bahin db-fiddle.com. 

Pananglitan sa db-fiddle.com

Didto, sa wala, sa uma Schema SQL, adunay code nga nagsal-ot sa 100000 ka laray sa database, ug sa tuo, sa field Query SQL, duha ka pangutana ang gipakita. Ang una, hinay, ingon niini:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Ug ang ikaduha, nga usa ka epektibo nga solusyon sa parehas nga problema, sama niini:

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

Aron matuman kini nga mga hangyo, i-klik lang ang buton Run sa ibabaw sa panid. Kung nahuman na kini, gikumpara namon ang kasayuran bahin sa oras sa pagpatuman sa pangutana. Mopatim-aw nga ang pagpatuman sa usa ka dili episyente nga pangutana nagkinahanglan ug labing menos 30 ka pilo nga mas taas kay sa pagpatuman sa ikaduha (karong panahona lainlain gikan sa pagdagan ngadto sa pagdagan; pananglitan, ang sistema mahimong magreport nga ang unang pangutana mikuha ug 37 ms aron makompleto, apan ang pagpatuman sa ikaduha - 1 ms).

Ug kung adunay daghang datos, nan ang tanan tan-awon nga labi ka grabe (aron makombinsir niini, tan-awa ang akong usa ka panig-ingnan nga adunay 10 milyon nga mga laray).

Ang bag-o lang namon nga nahisgutan kinahanglan maghatag kanimo pipila ka panabut kung giunsa ang pagproseso sa mga pangutana sa database.

Palihug timan-i nga mas taas ang bili OFFSET — mas dugay ang hangyo aron makompleto.

Unsa ang akong gamiton imbes sa kombinasyon sa OFFSET ug LIMIT?

Imbes nga kombinasyon OFFSET и LIMIT Angayan nga gamiton ang usa ka istruktura nga gitukod sumala sa mosunud nga laraw:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Kini mao ang pagpahamtang sa pangutana nga adunay cursor based pagination.

Imbis nga tipigan ang mga kasamtangan sa lokal OFFSET и LIMIT ug ipadala kini sa matag hangyo, kinahanglan nimo nga tipigan ang katapusang nadawat nga panguna nga yawe (kasagaran kini ID) ug LIMIT, isip resulta, ang mga pangutana nga susama sa ibabaw makuha.

Ngano man? Ang punto mao nga pinaagi sa tin-aw nga pagpiho sa identifier sa katapusang linya nga gibasa, gisultihan nimo ang imong DBMS kung diin kinahanglan magsugod pagpangita alang sa kinahanglan nga datos. Dugang pa, ang pagpangita, salamat sa paggamit sa yawe, himuon nga epektibo; ang sistema dili kinahanglan nga mabalda sa mga linya sa gawas sa gitakda nga sakup.

Atong tan-awon ang mosunod nga pagtandi sa performance sa lain-laing mga pangutana. Ania ang usa ka dili epektibo nga pangutana.

Hinay nga hangyo

Ug ania ang usa ka na-optimize nga bersyon sa kini nga hangyo.

Dali nga hangyo

Ang duha ka pangutana nagbalik sa parehas nga kantidad sa datos. Apan ang una nagkinahanglan og 12,80 ka segundo aron makompleto, ug ang ikaduha mokabat ug 0,01 ka segundo. Gibati ba nimo ang kalainan?

Posible nga mga problema

Aron epektibong molihok ang gisugyot nga pamaagi sa pangutana, ang lamesa kinahanglan adunay kolum (o mga kolum) nga adunay talagsaon, sunud-sunod nga mga indeks, sama sa usa ka integer identifier. Sa pipila ka piho nga mga kaso, kini mahimong magtino sa kalampusan sa paggamit sa ingon nga mga pangutana aron madugangan ang katulin sa pagtrabaho kauban ang database.

Natural, kung magtukod mga pangutana, kinahanglan nimo nga tagdon ang piho nga arkitektura sa mga lamesa ug pilia ang mga mekanismo nga labing molihok sa naa na nga mga lamesa. Pananglitan, kung kinahanglan nimo nga magtrabaho sa mga pangutana nga adunay daghang mga volume nga may kalabutan nga datos, mahimo nimong makit-an nga makapaikag kini artikulo.

Kung nag-atubang kita sa problema sa pagkawala sa usa ka panguna nga yawe, pananglitan, kung kita adunay usa ka lamesa nga adunay daghang-sa-daghan nga relasyon, nan ang tradisyonal nga pamaagi sa paggamit OFFSET и LIMIT, gigarantiyahan nga mohaum kanamo. Apan ang paggamit niini mahimong moresulta sa posibleng hinay nga mga pangutana. Sa ingon nga mga kaso, akong irekomendar ang paggamit sa usa ka auto-incrementing nga panguna nga yawe, bisan kung kini gikinahanglan lamang sa pagdumala sa mga pangutana sa pahina.

Kung interesado ka niini nga hilisgutan - tan-awa, tan-awa и tan-awa - ubay-ubay nga mapuslanon nga mga materyales.

Mga resulta

Ang nag-unang konklusyon nga mahimo natong makuha mao nga, bisan unsa pa ang gidak-on sa mga database nga atong gihisgutan, gikinahanglan kanunay ang pag-analisar sa katulin sa pagpatuman sa pangutana. Karong panahona, ang scalability sa mga solusyon hinungdanon kaayo, ug kung ang tanan gidisenyo sa husto gikan sa sinugdanan sa pagtrabaho sa usa ka piho nga sistema, kini, sa umaabot, makaluwas sa developer gikan sa daghang mga problema.

Giunsa nimo pag-analisar ug pag-optimize ang mga pangutana sa database?

Source: www.habr.com