पृष्ठांकित प्रश्नांमध्ये 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 ms लागला, परंतु त्याची अंमलबजावणी दुसरा - 1 एमएस).

आणि जर अधिक डेटा असेल तर सर्वकाही आणखी वाईट दिसेल (याची खात्री करण्यासाठी, माझ्याकडे पहा उदाहरण 10 दशलक्ष पंक्तींसह).

आम्ही नुकतीच ज्या गोष्टींवर चर्चा केली आहे त्यावरून तुम्हाला डेटाबेस क्वेरीवर प्रत्यक्षात कशी प्रक्रिया केली जाते याबद्दल काही अंतर्दृष्टी दिली पाहिजे.

कृपया लक्षात ठेवा की मूल्य जितके जास्त असेल OFFSET - विनंती पूर्ण होण्यास जितका जास्त वेळ लागेल.

OFFSET आणि LIMIT च्या संयोजनाऐवजी मी काय वापरावे?

संयोजनाऐवजी OFFSET и LIMIT खालील योजनेनुसार तयार केलेली रचना वापरणे योग्य आहे:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

हे कर्सर आधारित पृष्ठांकनासह क्वेरी अंमलबजावणी आहे.

स्थानिक पातळीवर वर्तमान संचयित करण्याऐवजी OFFSET и LIMIT आणि त्यांना प्रत्येक विनंतीसह प्रसारित करा, आपल्याला शेवटची प्राप्त प्राथमिक की संग्रहित करणे आवश्यक आहे (सामान्यतः हे आहे ID) आणि LIMIT, परिणामी, वरील प्रमाणेच प्रश्न प्राप्त होतील.

का? मुद्दा असा आहे की वाचलेल्या शेवटच्या पंक्तीचा अभिज्ञापक स्पष्टपणे निर्दिष्ट करून, आपण आपल्या DBMS ला आवश्यक डेटा शोधणे कोठे सुरू करणे आवश्यक आहे ते सांगता. शिवाय, की वापरल्याबद्दल धन्यवाद, शोध कार्यक्षमतेने चालविला जाईल; सिस्टमला निर्दिष्ट श्रेणीच्या बाहेरील ओळींद्वारे विचलित होण्याची गरज नाही.

चला विविध प्रश्नांची खालील कामगिरी तुलना पाहू. येथे एक अप्रभावी क्वेरी आहे.

सावकाश विनंती

आणि येथे या विनंतीची ऑप्टिमाइझ केलेली आवृत्ती आहे.

त्वरित विनंती

दोन्ही क्वेरी तंतोतंत समान प्रमाणात डेटा परत करतात. पण पहिल्याला पूर्ण होण्यासाठी १२.८० सेकंद लागतात आणि दुसऱ्याला ०.०१ सेकंद लागतात. तुम्हाला फरक जाणवतो का?

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

प्रस्तावित क्वेरी पद्धती प्रभावीपणे कार्य करण्यासाठी, टेबलमध्ये एक स्तंभ (किंवा स्तंभ) असणे आवश्यक आहे ज्यात अद्वितीय, अनुक्रमिक अनुक्रमणिका, जसे की पूर्णांक अभिज्ञापक. काही विशिष्ट प्रकरणांमध्ये, हे डेटाबेससह कार्य करण्याची गती वाढविण्यासाठी अशा क्वेरी वापरण्याचे यश निश्चित करू शकते.

साहजिकच, क्वेरी तयार करताना, तुम्हाला टेबल्सचे विशिष्ट आर्किटेक्चर विचारात घेणे आवश्यक आहे आणि त्या यंत्रणा निवडणे आवश्यक आहे जे विद्यमान टेबलवर सर्वोत्तम कार्य करतील. उदाहरणार्थ, जर तुम्हाला मोठ्या प्रमाणात संबंधित डेटासह क्वेरींमध्ये काम करण्याची आवश्यकता असेल, तर तुम्हाला ते मनोरंजक वाटेल हे लेख.

जर आपल्याला प्राथमिक की गहाळ होण्याची समस्या भेडसावत असेल, उदाहरणार्थ, जर आपल्याकडे अनेक-ते-अनेक संबंध असलेले टेबल असेल, तर वापरण्याचा पारंपारिक दृष्टिकोन OFFSET и LIMIT, आमच्या अनुरूप हमी आहे. परंतु त्याच्या वापरामुळे संभाव्य धीमे क्वेरी होऊ शकतात. अशा प्रकरणांमध्ये, मी स्वयं-वृद्धी करणारी प्राथमिक की वापरण्याची शिफारस करतो, जरी ती केवळ पृष्ठांकित क्वेरी हाताळण्यासाठी आवश्यक असेल.

तुम्हाला या विषयात स्वारस्य असल्यास - पाहा, पाहा, पाहा, पाहा и पाहा, पाहा - अनेक उपयुक्त साहित्य.

परिणाम

आम्ही काढू शकतो तो मुख्य निष्कर्ष हा आहे की, आम्ही कोणत्या आकाराच्या डेटाबेसबद्दल बोलत आहोत हे महत्त्वाचे नाही, क्वेरी अंमलबजावणीच्या गतीचे विश्लेषण करणे नेहमीच आवश्यक असते. आजकाल, सोल्यूशन्सची स्केलेबिलिटी अत्यंत महत्वाची आहे आणि जर एखाद्या विशिष्ट सिस्टमवर काम करण्याच्या अगदी सुरुवातीपासूनच सर्वकाही योग्यरित्या डिझाइन केले असेल तर, भविष्यात, हे विकसकाला बर्याच समस्यांपासून वाचवू शकते.

तुम्ही डेटाबेस क्वेरीचे विश्लेषण आणि ऑप्टिमाइझ कसे करता?

स्त्रोत: www.habr.com