Evite itilize OFFSET ak LIMIT nan demann paj

Gone jou yo lè ou pa t 'gen enkyete sou optimize pèfòmans baz done. Tan pa kanpe. Chak nouvo antreprenè teknoloji vle kreye pwochen Facebook la, pandan y ap eseye kolekte tout done yo ka jwenn men yo sou. Biznis yo bezwen done sa yo pou pi byen fòme modèl ki ede yo fè lajan. Nan kondisyon sa yo, pwogramasyon yo bezwen kreye API ki pèmèt yo travay byen vit ak seryezman ak gwo kantite enfòmasyon.

Si ou te desine aplikasyon oswa baz done backend pou nenpòt ki kantite tan, pwobableman ou te ekri kòd pou kouri demann paj. Pou egzanp, tankou sa a:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Jan li ye?

Men, si se konsa ou te fè pagination ou, mwen regrèt mwen di ke ou pa t 'fè li nan fason ki pi efikas.

Ou vle fè objeksyon sou mwen? Ou kapab pa gen okenn depanse tan. Kanson, Shopify и Mixmax Yo deja itilize teknik ke mwen vle pale sou jodi a.

Nonmen omwen yon pwomotè backend ki pa janm itilize OFFSET и LIMIT pou fè rechèch paj. Nan MVP (Minimum Viable Product) ak nan pwojè kote yo itilize ti kantite done, apwòch sa a se byen aplikab. Li "jis travay", se konsa pale.

Men, si ou bezwen kreye sistèm serye ak efikas nan grafouyen, ou ta dwe pran swen davans sou efikasite nan rechèch baz done yo itilize nan sistèm sa yo.

Jodi a nou pral pale sou pwoblèm ki genyen ak aplikasyon souvan itilize (twò move) nan motè rechèch paj, ak ki jan yo reyalize pèfòmans segondè lè egzekite demann sa yo.

Ki sa ki mal ak OFFSET ak LIMIT?

Kòm deja di, OFFSET и LIMIT Yo fè byen nan pwojè ki pa bezwen travay ak gwo kantite done.

Pwoblèm nan rive lè baz done a ap grandi nan yon gwosè konsa ke li pa anfòm ankò nan memwa sèvè a. Sepandan, lè w ap travay ak baz done sa a, ou bezwen sèvi ak demann paj.

Pou pwoblèm sa a manifeste poukont li, fòk genyen yon sitiyasyon kote DBMS a sèvi ak yon operasyon inefikas Full Table Scan sou chak demann paj (pandan ke operasyon ensèsyon ak efase ka rive, epi nou pa bezwen done demode!).

Ki sa ki se yon "eskanè tab konplè" (oswa "eskanè tab sekans", eskanè sekans)? Sa a se yon operasyon pandan ki DBMS la sekans li chak ranje nan tablo a, se sa ki, done ki genyen nan li, epi tcheke yo pou konfòmite ak yon kondisyon yo bay yo. Sa a ki kalite eskanè tab konnen yo dwe pi dousman an. Reyalite a se ke lè li egzekite, anpil operasyon antre / pwodiksyon yo fèt ki enplike sous-sistèm disk sèvè a. Sitiyasyon an vin pi mal pa latansi ki asosye ak travay ak done ki estoke sou disk, ak lefèt ke transfere done ki soti nan disk nan memwa se yon operasyon ki gen anpil resous.

Pou egzanp, ou gen dosye sou 100000000 itilizatè epi ou kouri yon rechèch ak konstriksyon an. OFFSET 50000000. Sa vle di ke DBMS a pral oblije chaje tout dosye sa yo (e nou pa menm bezwen yo!), mete yo nan memwa, epi apre sa pran, di, 20 rezilta rapòte nan. LIMIT.

Ann di li ta ka sanble sa a: "chwazi ranje soti nan 50000 a 50020 soti nan 100000". Sa vle di, sistèm nan ap bezwen premye chaje 50000 ranje pou konplete rechèch la. Èske w wè konbyen travay li pa nesesè pou l fè?

Si ou pa kwè m ', gade nan egzanp mwen te kreye lè l sèvi avèk karakteristik yo db-fiddle.com. 

Egzanp nan db-fiddle.com

La, sou bò gòch, nan jaden an Schema SQL, gen kòd ki mete 100000 ranje nan baz done a, ak sou bò dwat la, nan jaden an Query SQL, de demann yo montre. Premye a, ralanti, sanble sa a:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Ak dezyèm lan, ki se yon solisyon efikas nan menm pwoblèm nan, se tankou sa a:

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

Pou satisfè demann sa yo, jis klike sou bouton an Run nan tèt paj la. Lè w fin fè sa, nou konpare enfòmasyon sou tan egzekisyon demann lan. Li sanble ke egzekite yon rechèch ki pa efikas pran omwen 30 fwa pi long pase egzekite dezyèm lan (fwa sa a varye de kouri ale nan kouri; pou egzanp, sistèm nan ka rapòte ke premye rechèch la te pran 37 ms pou konplete, men ekzekisyon an nan dezyèm - 1 ms).

Men, si gen plis done, Lè sa a, tout bagay ap gade menm vin pi mal (yo dwe konvenki nan sa a, pran yon gade nan mwen egzanp ak 10 milyon ranje).

Sa nou te jis diskite ta dwe ba ou kèk insight sou fason demann baz done yo aktyèlman trete.

Tanpri sonje ke pi wo valè a OFFSET — plis tan demann lan pral pran pou konplete.

Kisa mwen ta dwe itilize olye de konbinezon OFFSET ak LIMIT?

Olye de yon konbinezon OFFSET и LIMIT Li vo sèvi ak yon estrikti ki bati dapre konplo sa a:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Sa a se ekzekisyon rechèch ak pagination ki baze sou kurseur.

Olye pou yo estoke aktyèl yo lokalman OFFSET и LIMIT epi transmèt yo ak chak demann, ou bezwen sere dènye kle prensipal la resevwa (anjeneral sa a se ID) ak LIMIT, kòm yon rezilta, demann ki sanble ak pi wo a yo pral jwenn.

Poukisa? Pwen an se ke lè w presize klèman idantifyan dènye ranje li a, ou di DBMS ou a ki kote li bezwen kòmanse chèche done ki nesesè yo. Anplis, rechèch la, gras a itilizasyon kle a, yo pral fèt avèk efikasite sistèm nan pa pral distrè pa liy ki deyò seri a espesifye.

Ann pran yon gade nan konparezon pèfòmans sa a nan demann divès kalite. Men yon rechèch ki pa efikas.

Demann dousman

Ak isit la se yon vèsyon optimize nan demann sa a.

Demann rapid

Tou de demann retounen egzakteman menm kantite done. Men, premye a pran 12,80 segonn pou konplete, ak dezyèm lan pran 0,01 segonn. Èske w santi diferans lan?

Pwoblèm posib

Pou metòd rechèch yo pwopoze a fonksyone byen, tab la dwe genyen yon kolòn (oswa kolòn) ki genyen endèks inik, sekans, tankou yon idantifyan nonb antye relatif. Nan kèk ka espesifik, sa ka detèmine siksè nan lè l sèvi avèk demann sa yo ogmante vitès la nan travay ak baz done a.

Natirèlman, lè w ap konstwi demann, ou bezwen pran an kont achitekti espesifik tab yo epi chwazi mekanis sa yo ki pral travay pi byen sou tab ki egziste deja yo. Pou egzanp, si ou bezwen travay nan demann ak volim gwo done ki gen rapò, ou ka jwenn li enteresan sa a atik.

Si nou fè fas ak pwoblèm nan manke yon kle prensipal, pou egzanp, si nou gen yon tab ak yon relasyon anpil-a-anpil, Lè sa a, apwòch la tradisyonèl nan lè l sèvi avèk OFFSET и LIMIT, se garanti kostim nou. Men, itilizasyon li yo ka lakòz demann potansyèlman dousman. Nan ka sa yo, mwen ta rekòmande pou itilize yon kle prensipal oto-enkreman, menm si li nesesè sèlman pou okipe demann paj.

Si w enterese nan sijè sa a - isit la, isit la и isit la - plizyè materyèl itil.

Rezilta

Konklizyon prensipal la ke nou ka tire se ke, kèlkeswa gwosè baz done nou ap pale, li toujou nesesè analize vitès la nan ekzekisyon demann. Sèjousi, évolutivité nan solisyon yo trè enpòtan, epi si tout bagay fèt kòrèkteman depi nan konmansman an nan travay sou yon sèten sistèm, sa a, nan lavni an, ka sove pwomotè a soti nan anpil pwoblèm.

Ki jan ou analize ak optimize demann baz done?

Sous: www.habr.com