પૃષ્ઠ ક્રમાંકિત પ્રશ્નોમાં OFFSET અને LIMIT નો ઉપયોગ કરવાનું ટાળો

તે દિવસો ગયા જ્યારે તમારે ડેટાબેઝ પ્રદર્શનને ઑપ્ટિમાઇઝ કરવા વિશે ચિંતા કરવાની જરૂર ન હતી. સમય સ્થિર રહેતો નથી. દરેક નવા ટેક ઉદ્યોગસાહસિક આગામી Facebook બનાવવા માંગે છે, જ્યારે તેઓ તેમના હાથ મેળવી શકે તેવો તમામ ડેટા એકત્રિત કરવાનો પ્રયાસ કરે છે. વ્યવસાયોને આ ડેટાની જરૂર હોય છે જેથી તેઓને પૈસા કમાવવામાં મદદ કરતા મોડલને વધુ સારી રીતે તાલીમ આપવામાં આવે. આવી પરિસ્થિતિઓમાં, પ્રોગ્રામરોએ એપીઆઈ બનાવવાની જરૂર છે જે તેમને વિશાળ માત્રામાં માહિતી સાથે ઝડપથી અને વિશ્વસનીય રીતે કાર્ય કરવાની મંજૂરી આપે છે.

જો તમે કોઈપણ સમયગાળા માટે એપ્લિકેશન અથવા ડેટાબેઝ બેકએન્ડ્સ ડિઝાઇન કરી રહ્યાં છો, તો તમે કદાચ પૃષ્ઠ ક્રમાંકિત ક્વેરીઝ ચલાવવા માટે કોડ લખ્યો હશે. ઉદાહરણ તરીકે, આની જેમ:

SELECT * FROM table_name LIMIT 10 OFFSET 40

જે રીતે તે છે?

પરંતુ જો તમે તમારું પૃષ્ઠ ક્રમાંકન આ રીતે કર્યું છે, તો મને એ કહેતા દિલગીર છે કે તમે તે સૌથી અસરકારક રીતે કર્યું નથી.

શું તમે મારી સામે વાંધો લેવા માંગો છો? તમે કરી શકો છો નથી ખર્ચ время. સ્લેક, Shopify и મિક્સમેક્સ તેઓ પહેલેથી જ તે તકનીકોનો ઉપયોગ કરી રહ્યા છે જેના વિશે હું આજે વાત કરવા માંગુ છું.

ઓછામાં ઓછા એક બેકએન્ડ ડેવલપરનું નામ આપો જેણે ક્યારેય ઉપયોગ કર્યો નથી OFFSET и LIMIT પૃષ્ઠ ક્રમાંકિત પ્રશ્નો કરવા માટે. MVP (લઘુત્તમ વ્યવહારુ ઉત્પાદન) અને એવા પ્રોજેક્ટ્સમાં જ્યાં ઓછી માત્રામાં ડેટાનો ઉપયોગ કરવામાં આવે છે, આ અભિગમ તદ્દન લાગુ પડે છે. તે "ફક્ત કામ કરે છે," તેથી વાત કરવા માટે.

પરંતુ જો તમારે શરૂઆતથી વિશ્વસનીય અને કાર્યક્ષમ સિસ્ટમ્સ બનાવવાની જરૂર હોય, તો તમારે આવી સિસ્ટમ્સમાં ઉપયોગમાં લેવાતા ડેટાબેઝની ક્વેરી કરવાની કાર્યક્ષમતા વિશે અગાઉથી કાળજી લેવી જોઈએ.

આજે આપણે પેજીનેટેડ ક્વેરી એન્જીનનાં સામાન્ય રીતે વપરાતા (ખૂબ ખરાબ) અમલીકરણની સમસ્યાઓ વિશે વાત કરીશું અને આવી ક્વેરી એક્ઝિક્યુટ કરતી વખતે ઉચ્ચ પ્રદર્શન કેવી રીતે પ્રાપ્ત કરવું.

OFFSET અને LIMIT માં શું ખોટું છે?

પહેલેથી જ કહ્યું તેમ, OFFSET и LIMIT તેઓ એવા પ્રોજેક્ટ્સમાં સારું પ્રદર્શન કરે છે કે જેમાં મોટી માત્રામાં ડેટા સાથે કામ કરવાની જરૂર નથી.

સમસ્યા ત્યારે ઊભી થાય છે જ્યારે ડેટાબેઝ એવા કદમાં વધે છે કે તે સર્વરની મેમરીમાં બંધબેસતું નથી. જો કે, આ ડેટાબેઝ સાથે કામ કરતી વખતે, તમારે પૃષ્ઠ ક્રમાંકિત પ્રશ્નોનો ઉપયોગ કરવાની જરૂર છે.

આ સમસ્યા પોતાને પ્રગટ કરવા માટે, એવી પરિસ્થિતિ હોવી જોઈએ કે જેમાં DBMS દરેક પૃષ્ઠ ક્રમાંકિત ક્વેરી પર બિનકાર્યક્ષમ પૂર્ણ ટેબલ સ્કેન ઑપરેશનનો આશરો લે છે (જ્યારે નિવેશ અને કાઢી નાખવાની કામગીરી થઈ શકે છે, અને અમને જૂના ડેટાની જરૂર નથી!).

"સંપૂર્ણ ટેબલ સ્કેન" (અથવા "ક્રમિક કોષ્ટક સ્કેન", અનુક્રમિક સ્કેન) શું છે? આ એક ઑપરેશન છે જે દરમિયાન DBMS ક્રમશઃ કોષ્ટકની દરેક પંક્તિને વાંચે છે, એટલે કે તેમાં રહેલો ડેટા, અને આપેલ શરતના પાલન માટે તેમને તપાસે છે. આ પ્રકારનું ટેબલ સ્કેન સૌથી ધીમું માનવામાં આવે છે. હકીકત એ છે કે જ્યારે તે ચલાવવામાં આવે છે, ત્યારે ઘણી ઇનપુટ/આઉટપુટ કામગીરી કરવામાં આવે છે જેમાં સર્વરની ડિસ્ક સબસિસ્ટમ સામેલ હોય છે. ડિસ્ક પર સંગ્રહિત ડેટા સાથે કામ કરવા સાથે સંકળાયેલ લેટન્સી દ્વારા પરિસ્થિતિ વધુ ખરાબ થાય છે, અને હકીકત એ છે કે ડિસ્કમાંથી મેમરીમાં ડેટા ટ્રાન્સફર કરવું એ સંસાધન-સઘન કામગીરી છે.

ઉદાહરણ તરીકે, તમારી પાસે 100000000 વપરાશકર્તાઓનો રેકોર્ડ છે અને તમે રચના સાથે ક્વેરી ચલાવો છો OFFSET 50000000. આનો અર્થ એ છે કે DBMS એ આ બધા રેકોર્ડ્સ લોડ કરવા પડશે (અને અમને તેની જરૂર પણ નથી!), તેમને મેમરીમાં મૂકવા પડશે, અને તે પછી લો, કહો, 20 પરિણામોની જાણ કરવામાં આવી છે. LIMIT.

ચાલો કહીએ કે તે આના જેવું દેખાઈ શકે છે: "50000 માંથી 50020 થી 100000 સુધીની પંક્તિઓ પસંદ કરો". એટલે કે, ક્વેરી પૂર્ણ કરવા માટે સિસ્ટમને પહેલા 50000 પંક્તિઓ લોડ કરવાની જરૂર પડશે. શું તમે જુઓ છો કે તેણીએ કેટલું બિનજરૂરી કામ કરવું પડશે?

જો તમે મારા પર વિશ્વાસ ન કરતા હો, તો મેં સુવિધાઓનો ઉપયોગ કરીને બનાવેલ ઉદાહરણ પર એક નજર નાખો db-fiddle.com. 

db-fiddle.com પર ઉદાહરણ

ત્યાં, ડાબી બાજુએ, ક્ષેત્રમાં Schema SQL, ત્યાં કોડ છે જે ડેટાબેઝમાં 100000 પંક્તિઓ દાખલ કરે છે, અને જમણી બાજુએ, ક્ષેત્રમાં Query SQL, બે પ્રશ્નો દર્શાવેલ છે. પ્રથમ, ધીમો, આના જેવો દેખાય છે:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

અને બીજું, જે સમાન સમસ્યાનો અસરકારક ઉકેલ છે, તે આના જેવું છે:

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

આ વિનંતીઓને પરિપૂર્ણ કરવા માટે, ફક્ત બટન પર ક્લિક કરો Run પૃષ્ઠની ટોચ પર. આ કર્યા પછી, અમે ક્વેરી એક્ઝેક્યુશન સમય વિશેની માહિતીની તુલના કરીએ છીએ. તે તારણ આપે છે કે બિનઅસરકારક ક્વેરી ચલાવવામાં બીજી ક્વેરી ચલાવવા કરતાં ઓછામાં ઓછો 30 ગણો વધુ સમય લાગે છે (આ સમય રનથી રનમાં બદલાય છે; ઉદાહરણ તરીકે, સિસ્ટમ જાણ કરી શકે છે કે પ્રથમ ક્વેરી પૂર્ણ થવામાં 37 એમએસનો સમય લાગ્યો છે, પરંતુ એક્ઝેક્યુશન બીજું - 1 એમએસ).

અને જો ત્યાં વધુ ડેટા હશે, તો બધું વધુ ખરાબ દેખાશે (આની ખાતરી કરવા માટે, મારા પર એક નજર નાખો ઉદાહરણ 10 મિલિયન પંક્તિઓ સાથે).

અમે હમણાં જ જે ચર્ચા કરી છે તે તમને ડેટાબેઝ ક્વેરી ખરેખર કેવી રીતે પ્રક્રિયા કરવામાં આવે છે તેની થોડી સમજ આપવી જોઈએ.

મહેરબાની કરીને નોંધ કરો કે મૂલ્ય જેટલું ઊંચું છે OFFSET - વિનંતી પૂર્ણ થવામાં વધુ સમય લાગશે.

OFFSET અને LIMIT ના સંયોજનને બદલે મારે શું વાપરવું જોઈએ?

સંયોજનને બદલે OFFSET и LIMIT નીચેની યોજના અનુસાર બાંધવામાં આવેલી રચનાનો ઉપયોગ કરવો તે યોગ્ય છે:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

આ કર્સર આધારિત પૃષ્ઠ ક્રમાંક સાથે ક્વેરી એક્ઝેક્યુશન છે.

વર્તમાનને સ્થાનિક રીતે સંગ્રહિત કરવાને બદલે OFFSET и LIMIT અને તેમને દરેક વિનંતી સાથે પ્રસારિત કરો, તમારે છેલ્લી પ્રાપ્ત પ્રાથમિક કી સંગ્રહિત કરવાની જરૂર છે (સામાન્ય રીતે આ છે ID) અને LIMIT, પરિણામે, ઉપરોક્ત સમાન પ્રશ્નો મેળવવામાં આવશે.

શા માટે? મુદ્દો એ છે કે વાંચેલી છેલ્લી પંક્તિના ઓળખકર્તાને સ્પષ્ટપણે સ્પષ્ટ કરીને, તમે તમારા DBMSને કહો છો કે જ્યાં તેને જરૂરી ડેટા શોધવાનું શરૂ કરવાની જરૂર છે. તદુપરાંત, શોધ, કીના ઉપયોગ માટે આભાર, કાર્યક્ષમ રીતે હાથ ધરવામાં આવશે; સિસ્ટમને નિર્દિષ્ટ શ્રેણીની બહારની રેખાઓથી વિચલિત થવાની જરૂર નથી.

ચાલો વિવિધ પ્રશ્નોની નીચેની કામગીરીની સરખામણી પર એક નજર કરીએ. અહીં એક બિનઅસરકારક ક્વેરી છે.

ધીમી વિનંતી

અને અહીં આ વિનંતીનું ઑપ્ટિમાઇઝ વર્ઝન છે.

ઝડપી વિનંતી

બંને ક્વેરી ડેટાની બરાબર સમાન રકમ પરત કરે છે. પરંતુ પ્રથમ પૂર્ણ થવામાં 12,80 સેકન્ડ લે છે, અને બીજાને 0,01 સેકન્ડ લાગે છે. શું તમે તફાવત અનુભવો છો?

શક્ય સમસ્યાઓ

સૂચિત ક્વેરી પદ્ધતિ અસરકારક રીતે કામ કરે તે માટે, કોષ્ટકમાં એક કૉલમ (અથવા કૉલમ્સ) હોવી જોઈએ જેમાં અનન્ય, અનુક્રમિક અનુક્રમણિકાઓ હોય, જેમ કે પૂર્ણાંક ઓળખકર્તા. કેટલાક ચોક્કસ કિસ્સાઓમાં, ડેટાબેઝ સાથે કામ કરવાની ઝડપ વધારવા માટે આ પ્રકારની ક્વેરીઝનો ઉપયોગ કરવાની સફળતા નક્કી કરી શકે છે.

સ્વાભાવિક રીતે, ક્વેરી બનાવતી વખતે, તમારે કોષ્ટકોના વિશિષ્ટ આર્કિટેક્ચરને ધ્યાનમાં લેવાની જરૂર છે અને તે પદ્ધતિઓ પસંદ કરવાની જરૂર છે જે હાલના કોષ્ટકો પર શ્રેષ્ઠ કાર્ય કરશે. ઉદાહરણ તરીકે, જો તમારે મોટા પ્રમાણમાં સંબંધિત ડેટા સાથે પ્રશ્નોમાં કામ કરવાની જરૂર હોય, તો તમને તે રસપ્રદ લાગી શકે છે ઇટા લેખ

જો આપણે પ્રાથમિક કી ગુમ થવાની સમસ્યાનો સામનો કરી રહ્યા છીએ, ઉદાહરણ તરીકે, જો આપણી પાસે અનેક-થી-ઘણા સંબંધ ધરાવતું ટેબલ હોય, તો તેનો ઉપયોગ કરવાનો પરંપરાગત અભિગમ OFFSET и LIMIT, અમને અનુકૂળ થવાની ખાતરી છે. પરંતુ તેનો ઉપયોગ સંભવિત ધીમી ક્વેરીઝમાં પરિણમી શકે છે. આવા કિસ્સાઓમાં, હું સ્વતઃ-વૃદ્ધિ કરતી પ્રાથમિક કીનો ઉપયોગ કરવાની ભલામણ કરીશ, ભલે તે ફક્ત પૃષ્ઠ ક્રમાંકિત પ્રશ્નોને હેન્ડલ કરવા માટે જરૂરી હોય.

જો તમને આ વિષયમાં રસ હોય તો - જોયેલું, જોયેલું и જોયેલું - ઘણી ઉપયોગી સામગ્રી.

પરિણામો

મુખ્ય નિષ્કર્ષ જે આપણે દોરી શકીએ છીએ તે એ છે કે, આપણે ગમે તે ડેટાબેઝના કદ વિશે વાત કરી રહ્યા છીએ, તે હંમેશા ક્વેરી એક્ઝેક્યુશનની ઝડપનું વિશ્લેષણ કરવું જરૂરી છે. આજકાલ, ઉકેલોની માપનીયતા અત્યંત મહત્વપૂર્ણ છે, અને જો ચોક્કસ સિસ્ટમ પર કામ કરવાની શરૂઆતથી જ બધું યોગ્ય રીતે ડિઝાઇન કરવામાં આવ્યું હોય, તો આ, ભવિષ્યમાં, વિકાસકર્તાને ઘણી સમસ્યાઓથી બચાવી શકે છે.

તમે ડેટાબેઝ પ્રશ્નોનું વિશ્લેષણ અને ઑપ્ટિમાઇઝ કેવી રીતે કરશો?

સોર્સ: www.habr.com