पृष्ठांकन गरिएका प्रश्नहरूमा OFFSET र LIMIT प्रयोग नगर्नुहोस्

ती दिनहरू गए जब तपाईले डाटाबेस प्रदर्शनलाई अनुकूलन गर्ने बारे चिन्ता गर्नुपर्दैन। समय स्थिर रहँदैन। हरेक नयाँ प्राविधिक उद्यमी अर्को फेसबुक सिर्जना गर्न चाहन्छन्, सबै डाटा सङ्कलन गर्ने प्रयास गर्दा तिनीहरूले आफ्नो हातमा प्राप्त गर्न सक्छन्। व्यवसायहरूलाई पैसा कमाउन मद्दत गर्ने मोडेलहरूलाई राम्रो तालिम दिन यो डेटा चाहिन्छ। यस्तो अवस्थामा, प्रोग्रामरहरूले API हरू सिर्जना गर्न आवश्यक छ जसले तिनीहरूलाई छिटो र भरपर्दो रूपमा जानकारीको ठूलो मात्रामा काम गर्न अनुमति दिन्छ।

यदि तपाइँ कुनै पनि समयको लागि एप्लिकेसन वा डाटाबेस ब्याकएन्डहरू डिजाइन गर्दै हुनुहुन्छ भने, तपाइँले पृष्ठन गरिएका प्रश्नहरू चलाउनको लागि कोड लेख्नुभएको छ। उदाहरण को लागी, यो जस्तै:

SELECT * FROM table_name LIMIT 10 OFFSET 40

यो तरिका हो?

तर यदि तपाईंले आफ्नो पृष्ठांकन यसरी गर्नुभयो भने, तपाईंले यो सबैभन्दा प्रभावकारी तरिकामा गर्नुभएन भनेर म माफी चाहन्छु।

के तपाईं मेरो विरोध गर्न चाहनुहुन्छ? तिमी सक्छौ छैन खर्च время. सुस्त, Shopify и मिक्समैक्स तिनीहरू पहिले नै प्रविधिहरू प्रयोग गर्दैछन् जुन म आज कुरा गर्न चाहन्छु।

कहिल्यै प्रयोग नगर्ने कम्तिमा एक ब्याकइन्ड विकासकर्ताको नाम दिनुहोस् OFFSET и LIMIT पृष्ठबद्ध प्रश्नहरू प्रदर्शन गर्न। MVP (न्यूनतम व्यवहार्य उत्पादन) र परियोजनाहरूमा जहाँ थोरै मात्रामा डाटा प्रयोग गरिन्छ, यो दृष्टिकोण एकदमै लागू हुन्छ। यो "केवल काम गर्दछ," त्यसैले बोल्न।

तर यदि तपाईंले स्क्र्याचबाट भरपर्दो र प्रभावकारी प्रणालीहरू सिर्जना गर्न आवश्यक छ भने, तपाईंले त्यस्ता प्रणालीहरूमा प्रयोग गरिएका डाटाबेसहरू क्वेरी गर्ने दक्षताको बारेमा पहिले नै ख्याल राख्नुपर्छ।

आज हामी पृष्ठन गरिएको क्वेरी इन्जिनहरूको सामान्यतया प्रयोग हुने (धेरै नराम्रो) कार्यान्वयनका समस्याहरूको बारेमा कुरा गर्नेछौं, र त्यस्ता प्रश्नहरू कार्यान्वयन गर्दा कसरी उच्च प्रदर्शन प्राप्त गर्ने।

OFFSET र LIMIT मा के गलत छ?

जस्तै पहिले नै भनिएको छ, OFFSET и LIMIT तिनीहरू परियोजनाहरूमा राम्रो प्रदर्शन गर्छन् जुन ठूलो मात्रामा डाटासँग काम गर्न आवश्यक छैन।

समस्या तब उत्पन्न हुन्छ जब डाटाबेस यस्तो आकारमा बढ्छ कि यो अब सर्भरको मेमोरीमा फिट हुँदैन। जे होस्, यो डाटाबेससँग काम गर्दा, तपाईंले पृष्ठबद्ध प्रश्नहरू प्रयोग गर्न आवश्यक छ।

यो समस्या आफैं प्रकट हुनको लागि, त्यहाँ एक अवस्था हुनुपर्दछ जसमा DBMS ले प्रत्येक पृष्ठन गरिएको क्वेरीमा अकुशल पूर्ण तालिका स्क्यान अपरेशनमा रिसोर्ट गर्दछ (जब सम्म सम्मिलन र मेटाउने कार्यहरू हुन सक्छ, र हामीलाई पुरानो डाटाको आवश्यकता पर्दैन!)।

"पूर्ण तालिका स्क्यान" (वा "अनुक्रम तालिका स्क्यान", अनुक्रमिक स्क्यान) के हो? यो एक अपरेसन हो जसको क्रममा DBMS ले तालिकाको प्रत्येक पङ्क्तिलाई क्रमिक रूपमा पढ्छ, त्यो हो, यसमा समावेश डेटा, र दिइएको अवस्थाको अनुपालनको लागि जाँच गर्दछ। यस प्रकारको तालिका स्क्यान सबैभन्दा ढिलो हुन जानिन्छ। तथ्य यो हो कि जब यो निष्पादित हुन्छ, धेरै इनपुट/आउटपुट अपरेशनहरू प्रदर्शन गरिन्छ जुन सर्भरको डिस्क सबसिस्टम समावेश गर्दछ। डिस्कमा भण्डारण गरिएको डाटासँग काम गर्ने ढिलाइले स्थिति बिग्रिएको छ, र तथ्य यो हो कि डिस्कबाट मेमोरीमा डाटा स्थानान्तरण संसाधन-गहन सञ्चालन हो।

उदाहरणका लागि, तपाईंसँग 100000000 प्रयोगकर्ताहरूको रेकर्ड छ र तपाईंले निर्माणको साथ क्वेरी चलाउनुहुन्छ। OFFSET 50000000। यसको मतलब यो हो कि DBMS ले यी सबै रेकर्डहरू लोड गर्नुपर्नेछ (र हामीलाई तिनीहरूको आवश्यकता पनि छैन!), तिनीहरूलाई मेमोरीमा राख्नुहोस्, र त्यस पछि, भन्नुहोस्, २० परिणामहरू रिपोर्ट गरियो। LIMIT.

मानौं यो यस्तो देखिन सक्छ: "१०००० बाट ५०००० देखि ५००२० सम्मका पङ्क्तिहरू चयन गर्नुहोस्"। अर्थात्, प्रणालीले प्रश्न पूरा गर्न पहिले ५०,००० पङ्क्तिहरू लोड गर्न आवश्यक हुनेछ। के तपाईंले देख्नुहुन्छ कि उसले कति अनावश्यक काम गर्नुपर्नेछ?

यदि तपाईं मलाई विश्वास गर्नुहुन्न भने, मैले सुविधाहरू प्रयोग गरेर सिर्जना गरेको उदाहरण हेर्नुहोस् 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 ms लाग्यो, तर कार्यान्वयन दोस्रो - 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