Evitu uzi OFFSET kaj LIMIT en paĝigitaj demandoj

Forpasis la tagoj, kiam vi ne devis zorgi pri optimumigo de datumbaza rendimento. La tempo ne haltas. Ĉiu nova teknika entreprenisto volas krei la sekvan Facebook, dum ili provas kolekti ĉiujn datumojn, kiujn ili povas akiri. Komercoj bezonas ĉi tiujn datumojn por pli bone trejni modelojn, kiuj helpas ilin gajni monon. En tiaj kondiĉoj, programistoj bezonas krei API-ojn, kiuj ebligas al ili rapide kaj fidinde labori kun grandegaj kvantoj da informoj.

Se vi desegnis aplikaĵon aŭ datumbazajn backends dum iu longeco, vi verŝajne skribis kodon por ruli paĝitajn demandojn. Ekzemple, tiel:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Kiel ĝi estas?

Sed se tiel vi faris vian paĝigon, mi bedaŭras diri, ke vi ne faris ĝin en la plej efika maniero.

Ĉu vi volas kontraŭi min? Vi povas ne elspezi время. slack, Shopify и Mixmax Ili jam uzas la teknikojn pri kiuj mi volas paroli hodiaŭ.

Nomu almenaŭ unu ellaboranton, kiu neniam uzis OFFSET и LIMIT por fari paĝitajn demandojn. En MVP (Minimuma Realigebla Produkto) kaj en projektoj kie malgrandaj kvantoj da datumoj estas uzataj, ĉi tiu aliro estas sufiĉe aplikebla. Ĝi "nur funkcias", por tiel diri.

Sed se vi bezonas krei fidindajn kaj efikajn sistemojn de nulo, vi devus antaŭe zorgi pri la efikeco de konsultado de la datumbazoj uzataj en tiaj sistemoj.

Hodiaŭ ni parolos pri la problemoj kun ofte uzataj (domaĝe) efektivigoj de paĝigitaj demandmotoroj, kaj kiel atingi altan rendimenton dum ekzekuto de tiaj demandoj.

Kio malbonas kun OFFSET kaj LIMIT?

Kiel jam dirite, OFFSET и LIMIT Ili funkcias bone en projektoj, kiuj ne bezonas labori kun grandaj kvantoj da datumoj.

La problemo ekestas kiam la datumbazo kreskas al tia grandeco, ke ĝi ne plu taŭgas en la memoro de la servilo. Tamen, kiam vi laboras kun ĉi tiu datumbazo, vi devas uzi paĝitajn demandojn.

Por ke ĉi tiu problemo manifestiĝu, devas ekzisti situacio en kiu la DBMS recurre al neefika Plena Tabla Skanado-operacio sur ĉiu paĝigita demando (dum enigo kaj forigo operacioj povas okazi, kaj ni ne bezonas malnoviĝintajn datumojn!).

Kio estas "plena tablo-skanado" (aŭ "sinsekva tablo-skanado", Sequential Scan)? Ĉi tio estas operacio dum kiu la DBMS sinsekve legas ĉiun vicon de la tabelo, tio estas, la datumoj enhavitaj en ĝi, kaj kontrolas ilin por plenumado de donita kondiĉo. Ĉi tiu speco de tabloskanado estas konata kiel la plej malrapida. La fakto estas, ke kiam ĝi estas ekzekutita, multaj enig/eligoperacioj estas faritaj, kiuj implikas la disksubsistemon de la servilo. La situacio plimalboniĝas pro la latenteco asociita kun laboro kun datumoj stokitaj sur diskoj, kaj la fakto, ke translokado de datumoj de disko al memoro estas rimedo-intensa operacio.

Ekzemple, vi havas rekordojn de 100000000 uzantoj kaj vi rulas demandon kun la konstruo OFFSET 50000000. Ĉi tio signifas, ke la DBMS devos ŝargi ĉiujn ĉi tiujn registrojn (kaj ni eĉ ne bezonas ilin!), meti ilin en memoron, kaj post tio preni, ekzemple, 20 rezultojn raportitajn en LIMIT.

Ni diru, ke ĝi povus aspekti jene: "elektu vicojn de 50000 ĝis 50020 de 100000". Tio estas, la sistemo unue devos ŝarĝi 50000 XNUMX vicojn por kompletigi la demandon. Ĉu vi vidas kiom da nenecesa laboro ŝi devos fari?

Se vi ne kredas min, rigardu la ekzemplon, kiun mi kreis uzante la funkciojn db-fiddle.com. 

Ekzemplo ĉe db-fiddle.com

Tie, maldekstre, sur la kampo Schema SQL, estas kodo, kiu enmetas 100000 vicojn en la datumbazon, kaj dekstre, en la kampon Query SQL, du demandoj estas montritaj. La unua, malrapida, aspektas jene:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Kaj la dua, kiu estas efika solvo al la sama problemo, estas jena:

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

Por plenumi ĉi tiujn petojn, simple alklaku la butonon Run ĉe la supro de la paĝo. Farinte tion, ni komparas informojn pri la demanda ekzekutotempo. Rezultas, ke ekzekuti neefika demando daŭras almenaŭ 30 fojojn pli longe ol ekzekuti la duan (ĉi tiu tempo varias de kuro al kuro; ekzemple, la sistemo povas raporti, ke la unua demando daŭris 37 ms por kompletigi, sed la ekzekuto de la dua - 1 ms).

Kaj se estas pli da datumoj, tiam ĉio aspektos eĉ pli malbona (por konvinkiĝi pri tio, rigardu mian ekzemplo kun 10 milionoj da vicoj).

Kion ni ĵus diskutis devus doni al vi iom da kompreno pri kiel datumbazaj demandoj estas efektive prilaboritaj.

Bonvolu noti, ke ju pli alta estas la valoro OFFSET — des pli longe la peto daŭros por plenumi.

Kion mi uzu anstataŭ la kombinaĵo de OFFSET kaj LIMIT?

Anstataŭ kombinaĵo OFFSET и LIMIT Indas uzi strukturon konstruitan laŭ la sekva skemo:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Ĉi tio estas demanda ekzekuto kun kursoro bazita paĝigo.

Anstataŭ stoki nunajn loke OFFSET и LIMIT kaj transdoni ilin kun ĉiu peto, vi devas konservi la lastan ricevitan ĉefan ŝlosilon (kutime ĉi tio estas ID) kaj LIMIT, kiel rezulto, demandoj similaj al la supre estos akiritaj.

Kial? La punkto estas, ke eksplicite specifante la identigilon de la lasta vico legita, vi diras al via DBMS kie ĝi devas komenci serĉi la necesajn datumojn. Krome, la serĉo, danke al la uzo de la ŝlosilo, efektiviĝos efike; la sistemo ne devos esti distrita per linioj ekster la specifita intervalo.

Ni rigardu la sekvan rendimentan komparon de diversaj demandoj. Jen neefika demando.

Malrapida peto

Kaj jen optimumigita versio de ĉi tiu peto.

Rapida peto

Ambaŭ demandoj resendas precize la saman kvanton da datumoj. Sed la unua daŭras 12,80 sekundojn por kompletigi, kaj la dua daŭras 0,01 sekundojn. Ĉu vi sentas la diferencon?

Eblaj problemoj

Por ke la proponita demandmetodo funkciu efike, la tabelo devas havi kolumnon (aŭ kolumnojn) enhavantajn unikajn, sinsekvajn indeksojn, kiel ekzemple entjeridentigilo. En iuj specifaj kazoj, ĉi tio povas determini la sukceson uzi tiajn demandojn por pliigi la rapidecon labori kun la datumbazo.

Kompreneble, kiam vi konstruas demandojn, vi devas konsideri la specifan arkitekturon de la tabeloj kaj elekti tiujn mekanismojn, kiuj plej bone funkcios sur la ekzistantaj tabeloj. Ekzemple, se vi bezonas labori en demandoj kun grandaj volumoj de rilataj datumoj, vi eble trovos ĝin interesa ĉi tio artikolo.

Se ni alfrontas la problemon manki ĉefan ŝlosilon, ekzemple, se ni havas tablon kun multaj-al-multaj rilato, tiam la tradicia aliro uzi OFFSET и LIMIT, estas garantiita por konveni al ni. Sed ĝia uzo povas rezultigi eble malrapidajn demandojn. En tiaj kazoj, mi rekomendus uzi aŭtomate-inkrementan ĉefan ŝlosilon, eĉ se ĝi nur bezonas por trakti paĝitajn demandojn.

Se vi interesiĝas pri ĉi tiu temo - jen, jen и jen - pluraj utilaj materialoj.

Rezultoj

La ĉefa konkludo, kiun ni povas tiri, estas, ke ĉiam necesas analizi la rapidecon de demanda ekzekuto, negrave pri kiaj grandeco datumbazoj ni parolas. Nuntempe, la skaleblo de solvoj estas ege grava, kaj se ĉio estas desegnita ĝuste ekde la komenco de laboro sur certa sistemo, ĉi tio, estontece, povas savi la programiston de multaj problemoj.

Kiel vi analizas kaj optimumigas datumbazajn demandojn?

fonto: www.habr.com