🥇आपको पृष्ठांकित प्रश्नों में OFFSET और LIMIT का उपयोग नहीं करना चाहिए

वे दिन गए जब आपको डेटाबेस प्रदर्शन को अनुकूलित करने के बारे में चिंता करने की ज़रूरत नहीं थी। समय स्थिर नहीं रहता. प्रत्येक नया तकनीकी उद्यमी अगला फेसबुक बनाना चाहता है, साथ ही वह सभी डेटा एकत्र करने का प्रयास कर रहा है जो उनके हाथ लग सकता है। व्यवसायों को मॉडल को बेहतर ढंग से प्रशिक्षित करने के लिए इस डेटा की आवश्यकता होती है जो उन्हें पैसा बनाने में मदद करता है। ऐसी स्थितियों में, प्रोग्रामर को एपीआई बनाने की आवश्यकता होती है जो उन्हें बड़ी मात्रा में जानकारी के साथ जल्दी और विश्वसनीय रूप से काम करने की अनुमति देती है।

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

SELECT * FROM table_name LIMIT 10 OFFSET 40

जिस तरीके से है वो?

लेकिन अगर आपने अपना पेजिनेशन इसी तरह किया है, तो मुझे यह कहते हुए खेद है कि आपने इसे सबसे कुशल तरीके से नहीं किया है।

क्या आप मुझ पर आपत्ति करना चाहते हैं? आप कर सकते हैं नहीं खर्च करने के लिए समय. सुस्त, Shopify и मिक्समैक्स वे पहले से ही उन तकनीकों का उपयोग कर रहे हैं जिनके बारे में मैं आज बात करना चाहता हूं।

कम से कम एक बैकएंड डेवलपर का नाम बताएं जिसने कभी उपयोग नहीं किया हो OFFSET и LIMIT पृष्ठांकित क्वेरी निष्पादित करने के लिए. एमवीपी (न्यूनतम व्यवहार्य उत्पाद) और उन परियोजनाओं में जहां कम मात्रा में डेटा का उपयोग किया जाता है, यह दृष्टिकोण काफी लागू है। ऐसा कहने के लिए, यह "बस काम करता है"।

लेकिन अगर आपको शुरू से ही विश्वसनीय और कुशल सिस्टम बनाने की आवश्यकता है, तो आपको ऐसे सिस्टम में उपयोग किए जाने वाले डेटाबेस को क्वेरी करने की दक्षता के बारे में पहले से ही ध्यान रखना चाहिए।

आज हम पेजिनेटेड क्वेरी इंजनों के आमतौर पर उपयोग किए जाने वाले (बहुत खराब) कार्यान्वयन की समस्याओं के बारे में बात करेंगे, और ऐसी क्वेरी निष्पादित करते समय उच्च प्रदर्शन कैसे प्राप्त करें।

OFFSET और LIMIT में क्या खराबी है?

जैसा कि पहले ही कहा जा चुका है, OFFSET и LIMIT वे उन परियोजनाओं में अच्छा प्रदर्शन करते हैं जिनमें बड़ी मात्रा में डेटा के साथ काम करने की आवश्यकता नहीं होती है।

समस्या तब उत्पन्न होती है जब डेटाबेस इतना बड़ा हो जाता है कि वह सर्वर की मेमोरी में फिट नहीं बैठता। हालाँकि, इस डेटाबेस के साथ काम करते समय, आपको पृष्ठांकित प्रश्नों का उपयोग करने की आवश्यकता होती है।

इस समस्या को स्वयं प्रकट करने के लिए, ऐसी स्थिति होनी चाहिए जिसमें DBMS प्रत्येक पृष्ठांकित क्वेरी पर एक अक्षम पूर्ण तालिका स्कैन ऑपरेशन का सहारा लेता है (जबकि प्रविष्टि और विलोपन संचालन हो सकता है, और हमें पुराने डेटा की आवश्यकता नहीं है!)।

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

उदाहरण के लिए, आपके पास 100000000 उपयोगकर्ताओं के रिकॉर्ड हैं और आप निर्माण के साथ एक क्वेरी चलाते हैं OFFSET 50000000. इसका मतलब यह है कि डीबीएमएस को इन सभी रिकॉर्ड्स को लोड करना होगा (और हमें उनकी आवश्यकता भी नहीं है!), उन्हें मेमोरी में रखना होगा, और उसके बाद, मान लीजिए, 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, परिणामस्वरूप, उपरोक्त के समान प्रश्न प्राप्त होंगे।

क्यों? मुद्दा यह है कि पढ़ी गई अंतिम पंक्ति के पहचानकर्ता को स्पष्ट रूप से निर्दिष्ट करके, आप अपने डीबीएमएस को बताते हैं कि उसे आवश्यक डेटा की खोज कहाँ से शुरू करनी है। इसके अलावा, खोज, कुंजी के उपयोग के लिए धन्यवाद, कुशलता से की जाएगी; सिस्टम को निर्दिष्ट सीमा के बाहर लाइनों से विचलित नहीं होना पड़ेगा।

आइए विभिन्न प्रश्नों की निम्नलिखित प्रदर्शन तुलना पर एक नज़र डालें। यहाँ एक अप्रभावी प्रश्न है.

धीमा अनुरोध

और यहां इस अनुरोध का एक अनुकूलित संस्करण है।

त्वरित अनुरोध

दोनों क्वेरीज़ बिल्कुल समान मात्रा में डेटा लौटाती हैं। लेकिन पहले को पूरा होने में 12,80 सेकंड लगते हैं, और दूसरे को 0,01 सेकंड लगते हैं। क्या आपको फर्क महसूस होता है?

संभव समस्याएं

प्रस्तावित क्वेरी विधि के प्रभावी ढंग से काम करने के लिए, तालिका में एक कॉलम (या कॉलम) होना चाहिए जिसमें पूर्णांक पहचानकर्ता जैसे अद्वितीय, अनुक्रमिक अनुक्रमणिकाएं हों। कुछ विशिष्ट मामलों में, यह डेटाबेस के साथ काम करने की गति बढ़ाने के लिए ऐसे प्रश्नों का उपयोग करने की सफलता निर्धारित कर सकता है।

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

यदि हमें प्राथमिक कुंजी गुम होने की समस्या का सामना करना पड़ता है, उदाहरण के लिए, यदि हमारे पास अनेक-से-अनेक संबंध वाली एक तालिका है, तो उपयोग करने का पारंपरिक तरीका OFFSET и LIMIT, हमारे अनुरूप होने की गारंटी है। लेकिन इसके उपयोग के परिणामस्वरूप संभावित रूप से धीमी क्वेरी हो सकती हैं। ऐसे मामलों में, मैं एक ऑटो-इंक्रीमेंटिंग प्राथमिक कुंजी का उपयोग करने की अनुशंसा करूंगा, भले ही यह केवल पृष्ठांकित प्रश्नों को संभालने के लिए आवश्यक हो।

यदि आप इस विषय में रुचि रखते हैं - यहां, यहां и यहां - कई उपयोगी सामग्रियां।

परिणाम

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

आप डेटाबेस क्वेरीज़ का विश्लेषण और अनुकूलन कैसे करते हैं?

स्रोत: www.habr.com

पृष्ठांकित प्रश्नों में OFFSET और LIMIT का उपयोग करने से बचें

OFFSET और LIMIT में क्या खराबी है?

OFFSET और LIMIT के संयोजन के स्थान पर मुझे क्या उपयोग करना चाहिए?

संभव समस्याएं

परिणाम

एक टिप्पणी जोड़ें उत्तर रद्द