अगोदर निर्देश केलेल्या बाबीसंबंधी बोलताना चुकीच्या शब्दात लिहिलेले SQL क्वेरी MySQL, PostgreSQL, SQL सर्व्हर, Oracle किंवा DB2 सारख्या मोठ्या रिलेशनल डेटाबेससह काम करताना अॅप्लिकेशन हळू चालते याचे हे सर्वात सामान्य कारण आहे. जरी आता आपल्याकडे शक्तिशाली सर्व्हर आणि लवचिक क्लाउड आहेत, तरीही अकार्यक्षम क्वेरीज शेवटी तुम्हाला महागात पडतील. पायाभूत सुविधांचा जास्त खर्च, जास्त विलंब आणि वापरकर्ता अनुभव खराब.
मोठ्या डेटाबेसमध्ये SQL क्वेरीज ऑप्टिमायझ करणे हे फक्त "इंडेक्स जोडणे आणि बस्स" यापलीकडे जाते. त्यात समाविष्ट आहे क्वेरी ऑप्टिमायझर कसा विचार करतो हे समजून घेणेडेटा कसा संग्रहित केला जातो, तुमचा अनुप्रयोग कोणते प्रवेश नमुने वापरतो आणि कोणत्या एकत्रित तंत्रांमुळे तुम्ही I/O, CPU आणि मेमरी वापर कमी करू शकता. पुढील विभागांमध्ये, आम्ही तपशीलवार आणि उदाहरणांसह पुनरावलोकन करू, तुमच्या रिलेशनल डेटाबेसमधून जास्तीत जास्त फायदा मिळवण्यासाठी सर्वात प्रभावी धोरणे.
SQL क्वेरी ऑप्टिमायझेशन म्हणजे नेमके काय आणि ते का महत्त्वाचे आहे?
SQL क्वेरी ऑप्टिमाइझ करा याचा अर्थ ते पुन्हा लिहिणे (आणि त्याचे संदर्भ समायोजित करणे: अनुक्रमणिका, आकडेवारी, डिझाइन) जेणेकरून इंजिन कमी संसाधने वापरत असताना आणि कमी वेळेत समान परिणाम देईल. SQL वाक्यरचना समान गोष्ट व्यक्त करण्याचे अनेक मार्ग देते, परंतु ते सर्व समान वेगाने कार्यान्वित होत नाहीत, विशेषतः जेव्हा लाखो पंक्ती किंवा जटिल जोड्या.
जेव्हा डेव्हलपरला ते कसे काम करते हे समजते क्वेरी प्लॅनर तुमच्या इंजिनसह (PostgreSQL, MySQL, SQL सर्व्हर, Oracle, DB2, इ.), तुम्ही अशा क्वेरी लिहू शकता ज्या इंडेक्सचा चांगला वापर करतात, अनावश्यक वाचन कमी करतात आणि सॉर्टिंग, सीक्वेंशियल स्कॅन किंवा पुनरावृत्ती होणारे सहसंबंधित उपक्वेरी यासारख्या महागड्या ऑपरेशन्स कमी करतात.
तथापि, हे स्पष्ट असणे महत्वाचे आहे की क्वेरी ऑप्टिमायझेशन हा एकमेव कामगिरी घटक नाही.स्कीमा डिझाइन (सामान्यीकरण, प्राथमिक आणि परदेशी की, डेटा प्रकार), आर्किटेक्चर (प्रतिकृती, विभाजने, कॅशे) आणि स्वतः पायाभूत सुविधांचा महत्त्वपूर्ण प्रभाव पडतो. परंतु चांगल्या आर्किटेक्चरसह, एकच खराब ऑप्टिमाइझ केलेली क्वेरी ही एक मोठी समस्या असू शकते. क्रूर अडथळा.
सल्लामसलत करून काम करण्याच्या फायद्यांपैकी, खालील गोष्टी ठळकपणे दिसून येतात: एकूण कामगिरी सुधारणा (कमी वेळेत अधिक विनंत्या हाताळल्या जातात), क्लाउड खर्चात कपात (कमी CPU आणि डिस्क, लहान इंस्टन्स आकार) आणि एक नितळ वापरकर्ता अनुभव सूची, शोध आणि अहवालांमध्ये प्रतीक्षा वेळ कमी करून. शिवाय, स्पष्ट आणि सुव्यवस्थित क्वेरी आहेत देखभाल करणे आणि डीबग करणे सोपे, प्रकल्प वाढल्यावर ज्याचे खूप कौतुक केले जाते.
ज्या अनुप्रयोगांचे खरोखरच स्केलिंग करण्याचे उद्दिष्ट आहे, त्यामध्ये सतत क्वेरी ऑप्टिमायझेशन हे एक आवर्ती कार्य बनते: निरीक्षण करणे, शोधणे, मोजणे, समायोजित करणे आणि पुन्हा मोजणेही एक-वेळची कृती नाही तर एक प्रक्रिया आहे.
व्यावहारिक उदाहरण: समान क्वेरी, खूप भिन्न कामगिरी
तुमच्या कल्पना प्रत्यक्षात आणण्यासाठी, एका टेबलाची कल्पना करा. २० दशलक्षाहून अधिक रेकॉर्ड असलेले ऑर्डर एका ई-कॉमर्स साईटमध्ये, आम्हाला ग्राहकाच्या गेल्या ३० दिवसांतील पूर्ण झालेल्या ऑर्डर परत मिळवायच्या आहेत आणि जास्त विचार न करता, आम्ही असे काहीतरी लिहू शकतो:
SELECT * FROM pedidos
WHERE cliente_id = 456
AND LOWER(estado) = 'completado'
AND fecha_creacion BETWEEN NOW() - INTERVAL '30 days' AND NOW();
ही क्वेरी आपल्याला जे हवे आहे ते परत करते, परंतु कामगिरीच्या दृष्टिकोनातून ते थोडे गोंधळलेले आहे: ते वापरत आहे निवडा *, एक फंक्शन लागू करते (LOWER) फिल्टर कॉलमवर आणि तारखा अशा अभिव्यक्तींसह एकत्र करते जे निर्देशांकांच्या वापरात व्यत्यय आणू शकतात. जर, याव्यतिरिक्त, योग्य निर्देशांक अस्तित्वात नसतील तर क्लायंट_आयडी, स्थिती किंवा निर्मिती_तारीख, इंजिनला टेबलचा मोठा भाग स्कॅन करण्यास भाग पाडले जाईल.
व्यावहारिक परिणाम स्पष्ट आहेत: आवश्यकतेपेक्षा जास्त डेटा ट्रान्सफर केलान वापरलेल्या कॉलम्स मॅपिंगसाठी बॅकएंडवर जास्त काम, भरपूर डिस्क रीडिंग आणि अंमलबजावणीचा वेळ जो खूप मोठ्या टेबल्समध्ये काही सेकंदांपर्यंत वाढू शकतो, ज्यामुळे अनेक वेळा लॉन्च केल्यावर संपूर्ण सिस्टमवर परिणाम होतो.
अधिक बुद्धिमानपणे मांडलेला हाच प्रश्न असा दिसू शकतो:
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 456
AND estado = 'Completado'
AND fecha_creacion >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY fecha_creacion DESC
LIMIT 100;
येथे आम्ही आहोत फक्त आवश्यक असलेले स्तंभ निवडणेस्थिती स्तंभावरील फंक्शन्स टाळणे, तारीख स्थिती सुलभ करणे आणि पंक्तींची संख्या मर्यादित करणे. चांगल्या प्रकारे डिझाइन केलेल्या निर्देशांकांसह (उदाहरणार्थ, INDEX(cliente_id, fecha_creacion) आणि एक बद्दल estado (जर त्याची कार्डिनॅलिटी जास्त असेल तर), इंजिन इंडेक्स स्कॅन वापरू शकते आणि क्वेरी सोडवू शकते सेकंदांऐवजी मिलिसेकंद.
हा विरोधाभास एक महत्त्वाचा विचार स्पष्ट करतो: क्वेरी "काम" करण्यासाठी पुरेसे नाही.टेबलावर शेकडो रांगा नसून लाखो रांगा असताना ते कसे चालेल याची काळजी तुम्हाला करावी लागेल.
निर्देशांक: शोधांना गती देण्यासाठी मुख्य लीव्हर
अगोदर निर्देश केलेल्या बाबीसंबंधी बोलताना क्वेरी जलद करण्यासाठी निर्देशांक हे सर्वात शक्तिशाली साधन आहे. मोठ्या डेटाबेसमध्ये. संपूर्ण टेबल एकामागून एक ओळ फिरवण्याऐवजी (क्रमिक स्कॅनिंग किंवा Seq स्कॅन), इंजिनमध्ये सहाय्यक संरचना (सामान्यतः बी-ट्रीज, आर-ट्रीज किंवा हॅश, डेटा प्रकार आणि इंजिनवर अवलंबून) वापरल्या जातात ज्या थेट उमेदवारांच्या पंक्तींवर उडी मारण्यास परवानगी देतात.
उदाहरणार्थ, MySQL मध्ये, सर्वात सामान्य रचना आहेत झाडे ब प्रकार निर्देशांकांसाठी PRIMARY KEY, UNIQUE, INDEX y FULLTEXT, तर अवकाशीय निर्देशांक वापरतात आर झाडे आणि इन-मेमरी टेबल्स निर्देशांकांवर आधारित काढू शकतात हॅशप्रत्येक विशिष्ट प्रवेश पॅटर्नसाठी ऑप्टिमाइझ केलेले आहे.
तथापि, प्रत्येक गोष्टीवर निर्देशांक लावण्याबद्दल नाही. प्रत्येक अतिरिक्त निर्देशांक ते डिस्कची जागा घेते आणि इन्सर्शन, अपडेट्स आणि डिलीटेशनची गती कमी करते.कारण इंजिनला रचना समक्रमित ठेवावी लागते. युक्ती म्हणजे शोधणे निर्देशांकांची संख्या आणि प्रतिसाद वेळ यांच्यातील संतुलन, गंभीर वाचन प्रश्नांवर लक्ष केंद्रित करणे.
रिलेशनल इंजिनमध्ये सर्वात सामान्य प्रकारच्या निर्देशांकांपैकी आपल्याला आढळतात की प्राथमिक की (प्रत्येक पंक्ती अद्वितीयपणे ओळखा आणि शून्य मूल्यांना परवानगी देऊ नका), ज्यांचे परदेशी की (दुसऱ्या टेबलच्या PK चा संदर्भ घ्या), अद्वितीय निर्देशांक (विशिष्टतेची हमी देते, परंतु शून्यतेला परवानगी देते) आणि संमिश्र निर्देशांक एका वेळी एकापेक्षा जास्त फील्ड फिल्टर करताना किंवा सॉर्ट करताना खूप उपयुक्त, अनेक कॉलम्सवर.
असेही काही परिस्थिती आहेत जिथे ते वापरणे उपयुक्त आहे पुनरावृत्ती मूल्यांसह निर्देशांक (अद्वितीय नसलेल्या स्तंभांमध्ये शोध जलद करण्यासाठी) किंवा पूर्ण-मजकूर अनुक्रमणिका (FULLTEXT उदाहरणार्थ, MySQL मध्ये) लांब मजकूर फील्डमध्ये शोध सुधारण्यासाठी. MySQL 8.0.13 पासून, हे तयार केले जाऊ शकतात कार्यात्मक निर्देशांकम्हणजेच, अभिव्यक्ती किंवा कार्याच्या परिणामावर (उदाहरणार्थ, YEAR(fecha_pago)), जे प्रगत ऑप्टिमायझेशनचे दरवाजे उघडते.
आपण MySQL मध्ये वेगवेगळ्या स्टेटमेंटसह इंडेक्स तयार करू शकतो: CREATE INDEX, त्यांना नंतर जोडणे; ALTER TABLEविद्यमान सारणी सुधारण्यासाठी; किंवा थेट व्याख्येत CREATE TABLEतिन्ही प्रकरणांमध्ये, साधे, संमिश्र, अद्वितीय आणि उपसर्ग निर्देशांकांना परवानगी आहे (फक्त पहिले N वर्ण a चे VARCHAR) किंवा FULLTEXT, आपल्याला आवश्यक असलेल्या डिझाइनवर अवलंबून.
चा वापर उपसर्ग निर्देशांक जेव्हा आपल्याकडे लांब स्ट्रिंग असतात तेव्हा हे उपयुक्त ठरते, परंतु अक्षरशः सर्व मूल्ये ओळखण्यासाठी तुलनेने कमी संख्येने वर्ण पुरेसे असतात. अशाप्रकारे, आपण जास्त निवडकता न गमावता निर्देशांकांचा आकार कमी करतो, जे ग्राहकांच्या नावांसारख्या स्तंभांमध्ये खूप उपयुक्त आहे जिथे आपण संपूर्ण फील्डऐवजी पहिले २५ वर्ण अनुक्रमित करू शकतो.
फक्त तुम्हाला आवश्यक असलेले स्तंभ निवडा.
शिवीगाळ निवडा * ही SQL मधील सर्वात सामान्य वाईट सवयींपैकी एक आहे. विकासादरम्यान ती सोयीस्कर असते, परंतु उत्पादनात ती एक ओझे बनते: प्रत्येक अतिरिक्त कॉलम म्हणजे डेटाबेसमधून प्रवास करणारे अधिक बाइट्स. तुमच्या अर्जापर्यंत, क्लायंटवर अधिक मेमरी आणि अधिक डीसीरियलायझेशन काम.
जेव्हा टेबलमध्ये मोठे कॉलम (BLOBs, मोठ्या JSON फाइल्स, मोठ्या टेक्स्ट फाइल्स, बायनरी अवतार इ.) असतात, तेव्हा त्यांचा समावेश केल्याने अनावश्यकपणे I/O आणि RAM वापर वाढतो. शिवाय, PostgreSQL सारख्या इंजिनमध्ये, कॉलमची संख्या मर्यादित केल्याने चांगले कार्यप्रदर्शन मिळते. फक्त इंडेक्स स्कॅन, जिथे डेटाबेस हीपमध्ये न जाता इंडेक्समधून प्रतिसाद देतो, परंतु हे फक्त तेव्हाच कार्य करते जेव्हा तुम्ही विनंती करत असलेले सर्व कॉलम इंडेक्समध्ये असतील.
एक उत्कृष्ट उदाहरण: एक टेबल users सारख्या स्तंभांसह आयडी, ईमेल, पासवर्ड_हॅश, अवतार, तयार_केले, शेवटचे_लॉगिनजर तुम्ही फेकले तर SELECT * FROM users WHERE email = 'juan@example.com';तुम्हाला फक्त ईमेल आणि शेवटची लॉगिन तारीख दाखवायची असली तरीही तुम्हाला पासवर्ड हॅश आणि बायनरी अवतार मिळेल. फक्त ते मागणे खूप चांगले. id, email, last_login.
नेहमी सोबत काम करा स्पष्ट स्तंभ सूची हे तुमच्या प्रश्नांना अधिक स्पष्ट करते, स्कीमा बदलांपासून तुमचे संरक्षण करते (स्तंभ जोडल्याने काहीही खंडित होत नाही), आणि मोठ्या टेबल्स किंवा पृष्ठांकित सूचींमध्ये संसाधनांचा वापर नाटकीयरित्या कमी करते, ज्यामुळे मदत होते मोठ्या प्रमाणात डेटा व्यवस्थापित करा.
जॉइन, सबक्वेरी आणि सीटीई: जटिल क्वेरींची योग्य रचना कशी करावी
अगोदर निर्देश केलेल्या बाबीसंबंधी बोलताना सहसंबंधित उपप्रश्न (बाह्य प्रश्नाच्या प्रत्येक ओळीसाठी एकदाच अंमलात आणलेले) कागदावर सुंदर वाटू शकतात, परंतु प्रत्यक्षात ते टेबल्स वाढत असताना कामगिरीमध्ये अडथळा बनतात. मुख्य सारणीतील प्रत्येक ओळ सबक्वेरीची अतिरिक्त अंमलबजावणी सुरू करते, परिणामी ऑपरेशन्सची संख्या प्रचंड असते.
जेव्हा शक्य असेल तेव्हा, या उपप्रश्नांचे रूपांतर सु-सूचित जॉइन किंवा मध्ये सीटीई (सामान्य सारणी अभिव्यक्ती) जे तर्कशास्त्र स्पष्ट पायऱ्यांमध्ये विभाजित करते. ऑप्टिमायझर सहसा जटिल सबक्वेरीजच्या घरट्यापेक्षा टेबल्सचे संयोजन खूप चांगले हाताळतो.
उदाहरणार्थ, उत्पादनांना त्यांच्या श्रेणीच्या नावासह मिळवण्यासाठी, सबक्वेरी करण्याऐवजी SELECT वापरणे अधिक कार्यक्षम आहे JOIN श्रेणी सारणीच्या विरुद्ध. जर जोडणी स्तंभ अनुक्रमित केले असतील (उदाहरणार्थ, productos.categoria_id y categorias.id), इंजिन मोठ्या टेबलांवर देखील खूप कमी खर्चात जोडणी सोडवू शकते.
अगोदर निर्देश केलेल्या बाबीसंबंधी बोलताना सीटीई (WITH ... AS (...)हे विशेषतः क्वेरीज, गुंतागुंतीचे एकत्रीकरण आणि चरण-दर-चरण तर्कशास्त्र नोंदवण्यासाठी उपयुक्त आहेत. जरी ते नेहमीच स्वतःहून कामगिरी सुधारत नसले तरी, ते प्लॅनरला मदत करतात आणि सर्वात महत्त्वाचे म्हणजे, वाचनीयता सुधारतात, विशिष्ट निर्देशांक जोडणे किंवा मध्यवर्ती निकाल प्रत्यक्षात आणणे यासारख्या पुढील ऑप्टिमायझेशन सुलभ करतात.
मोठ्या खंडांना नियंत्रित करण्यासाठी पृष्ठांकन आणि LIMIT
वास्तविक जगातील अनुप्रयोगांमध्ये, वापरकर्त्याच्या अनुभवाच्या दृष्टिकोनातून एकाच वेळी हजारो पंक्ती परत करणे जवळजवळ कधीच अर्थपूर्ण नसते. उत्पादन सूची, ऑर्डर इतिहास किंवा इव्हेंट लॉग सामान्यतः पृष्ठानुसार वापरले जाते, म्हणून परत केलेल्या ओळींची संख्या मर्यादित करा चढाईसाठी ही एक मूलभूत आवश्यकता आहे.
क्लासिक दृष्टिकोन वापरतो LIMIT y OFFSET (उदाहरणार्थ, LIMIT 10 OFFSET 20 "तिसऱ्या" पानावर जाण्यासाठी). ते अंमलात आणणे आणि समजणे सोपे आहे, परंतु त्यात एक गंभीर समस्या आहे: इंजिनला OFFSET च्या आधीच्या सर्व ओळी त्याच प्रकारे पार करा.जरी ते फक्त शेवटचे १० परत करते. खूप मोठ्या टेबल्समध्ये, उच्च OFFSET मूल्यांमुळे प्रतिसाद वेळ वाढत्या प्रमाणात खराब होतो.
शेकडो हजारो किंवा लाखो ओळींसह काम करताना, सहसा चांगले असते कीसेट पृष्ठांकन किंवा शोध-आधारित पृष्ठांकनया दृष्टिकोनात, डेटाबेसला "१००० पंक्ती वगळा" असे सांगण्याऐवजी, तुम्ही "या क्रमवारी लावलेल्या की मूल्यापासून सुरू होणारे पुढील N रेकॉर्ड परत करा" असे सांगता, या प्रकारच्या अटी वापरून. WHERE fecha_creacion < <última_fecha_vista> कॉन अन ORDER BY सुसंगत
या तंत्रामुळे इंजिनला क्रमवारी लावलेल्या स्तंभावर थेट निर्देशांकाचा फायदा घेता येतो (उदाहरणार्थ, fecha_creacion o id), मध्यवर्ती पृष्ठे ओलांडण्याचा खर्च टाळतो. शिवाय, ते पृष्ठांकन करते समाविष्ट करणे किंवा हटवणे यांविरुद्ध स्थिर पानांदरम्यान, ज्याची ऑफसेट हमी देत नाही.
त्या बदल्यात, कीसेट पृष्ठांकनाचा तोटा असा आहे की पान ३७ वर जाणे क्षुल्लक नाही. अतिरिक्त माहितीशिवाय, कारण ते लॉजिकल कर्सरपासून पुढे कार्य करते (शेवटचा आयडी किंवा पुनर्प्राप्त तारीख). म्हणूनच अनेक प्रणाली कार्यात्मक गरजांनुसार दोन्ही दृष्टिकोन एकत्र करतात.
फिल्टर केलेल्या कॉलममधील फंक्शन्स टाळा आणि WHERE क्लॉजचा चांगला वापर करा.
कामगिरी कमी होण्याचे एक सामान्य कारण म्हणजे फिल्टरमध्ये सहभागी होणाऱ्या स्तंभांवर कार्य करतेजसे की अभिव्यक्ती LOWER(nombre), DATE(fecha) o CAST(campo AS ...) कलमाच्या आत WHERE ते सहसा ऑप्टिमायझरला त्या कॉलमचा इंडेक्स वापरण्यापासून रोखतात.
त्याऐवजी, ते चांगले आहे डेटा घालताना किंवा अपडेट करताना सामान्य करा (उदाहरणार्थ, ईमेल लोअरकेसमध्ये सेव्ह करणे, एकसंध एन्कोडिंगसह स्टेटस) आणि प्रत्येक तुलनेतील कॉलममध्ये फंक्शन लागू करण्याऐवजी, त्या फॉरमॅटशी जुळण्यासाठी इनपुट व्हॅल्यूज रूपांतरित करा.
कलमाकडेच लक्ष देणे देखील योग्य आहे. WHERE शक्य तितके निवडक बनवण्यासाठी. जरी परिस्थितींच्या क्रमाचा नेहमीच थेट परिणाम होत नाही (ऑप्टिमायझर सहसा त्यांना पुन्हा क्रमबद्ध करतो), तरीही ते मदत करते चांगल्या प्रकारे अनुक्रमित भाकिते आणि सोपी तुलना महागड्या नमुन्यांऐवजी LIKE '%texto'जे सामान्यतः पूर्ण स्कॅन करण्यास भाग पाडते.
जेव्हा तुम्हाला डुप्लिकेट काढायचे असतील तेव्हा विचार करा की अ DISTINCT किंवा जर क्वेरी पुन्हा डिझाइन केली जाऊ शकते तर JOINs मॉडेलमध्ये अधिक अचूक किंवा विशिष्टतेची मर्यादा. दोन्ही DISTINCT कसे UNION सहसा सामील होणे वर्गीकरण किंवा गटबद्धीकरण ऑपरेशन्सजे अंमलबजावणी योजनेतील सर्वात महागड्यांपैकी एक आहेत.
ऑप्टिमायझरला मदत करण्यासाठी निर्देशांक आणि आकडेवारी राखणे
आधुनिक डेटाबेस इंजिने यावर अवलंबून असतात अंतर्गत आकडेवारी प्रत्येक अटी पूर्ण करणाऱ्या ओळींची संख्या किती आहे याचा अंदाज लावणे, कोणते निर्देशांक सर्वात योग्य आहेत आणि कोणत्या क्रमाने टेबल जोडायचे. जर ही आकडेवारी जुनी असेल, तर शेड्यूलर खूप चुकीचे निर्णय घेऊ शकतो आणि अकार्यक्षम अंमलबजावणी योजना तयार करू शकतो.
म्हणूनच वेळोवेळी अशा कमांड चालवणे महत्वाचे आहे जसे की ANALYZE (किंवा प्रत्येक इंजिनमध्ये त्यांचे विशिष्ट प्रकार) साठी मोठ्या प्रमाणात भार पडल्यानंतर आकडेवारी रिफ्रेश करास्थलांतर किंवा मोठ्या प्रमाणात INSERT, UPDATE y DELETEउदाहरणार्थ, PostgreSQL मध्ये, ऑटोव्हॅक्यूम सहसा स्वयंचलितपणे हाताळले जाते, परंतु मोठ्या प्रमाणात आयात केल्यानंतर ते चालवणे उपयुक्त ठरू शकते ANALYZE मॅन्युअल
MySQL मध्ये आपल्याकडे असे स्टेटमेंट आहेत ANALYZE TABLE, जे ऑप्टिमायझरला निर्देशांकांचा क्रम आणि वापर ठरवण्यास मदत करण्यासाठी की वितरणाचे विश्लेषण आणि संग्रह करते. JOINsयाव्यतिरिक्त, OPTIMIZE TABLE परवानगी द्या टेबल्स डीफ्रॅगमेंट करा, इंडेक्स पुन्हा क्रमाने लावा आणि अपडेट करा, अनेक बदल झालेल्या टेबलांमध्ये शिफारस केलेले काहीतरी.
इंजिन अपेक्षेप्रमाणे निर्देशांक वापरत आहे का हे तपासण्यासाठी, येथून खेचण्यासारखे काहीही नाही EXPLAIN o EXPLAIN ANALYZEही साधने आपल्याला अंदाजे योजना दाखवतात (आणि काही इंजिनमध्ये, वेळा आणि पंक्ती वाचून प्रत्यक्ष योजना देखील) आणि अनुक्रमिक स्कॅन केले जात आहे का ते दर्शवतात (ALL उदाहरणार्थ, MySQL मध्ये) किंवा जर ए Index Scanकिती ओळी अपेक्षित आहेत आणि प्रत्यक्षात किती खेळल्या जातात.
डेटाबेस ऑप्टिमाइझ करू इच्छिणाऱ्या प्रत्येकासाठी या योजना वाचायला शिकणे हे कदाचित सर्वात मौल्यवान कौशल्यांपैकी एक आहे: हे तुम्हाला अडथळे, निरुपयोगी निर्देशांक, खराब निवडक फिल्टर आणि खराब क्रमाने जोडणी शोधण्याची परवानगी देते. समस्या उत्पादनापर्यंत पोहोचण्यापूर्वी खूप आधी.
पूर्ण-मजकूर अनुक्रमणिका, नियमित अभिव्यक्ती आणि विशेष परिस्थिती
जेव्हा तुम्ही काम करता मोठे मजकूर फील्ड (वर्णने, समृद्ध HTML सामग्री, टिप्पण्या, इ.), शोध LIKE '%palabra%' मोठ्या टेबलांसाठी हे लवकर अव्यवहार्य बनतात. या प्रकरणांमध्ये, MySQL सारखी इंजिने अशा प्रकारच्या अनुक्रमणिका देतात FULLTEXT आणि ऑपरेटर जसे की MATCH() AGAINST()जे अधिक कार्यक्षम आणि संबंधित शोधांना अनुमती देतात.
सह FULLTEXT तुम्ही वेगवेगळ्या मोडमधून निवडू शकता: नैसर्गिक भाषा, बुलियन (ऑपरेटरसह) +, -, *(अचूक वाक्यांशांसाठी अवतरण चिन्ह इ.) किंवा क्वेरी विस्तार संबंधित परिणामांचा विस्तार करण्यासाठी. हे तुम्हाला डेटाबेस सोडल्याशिवाय बरेच शक्तिशाली अंतर्गत शोध इंजिन तयार करण्यास अनुमती देते.
अशा अधिक प्रगत परिस्थिती आहेत जिथे मजकुरात समाविष्ट आहे, उदाहरणार्थ, एम्बेडेड HTML टॅग. अशा परिस्थितीत, अनुक्रमणिका एकत्र करणे आवश्यक असू शकते. FULLTEXT सारख्या कार्यांसह REGEXP_REPLACE अचूक वाक्यांशांची तुलना करताना लेबल्स साफ करणे. एक सामान्य रणनीती म्हणजे पूर्ण-मजकूर अनुक्रमणिका वापरून प्रथम फिल्टर करा. आणि नंतर संपूर्ण टेबल स्कॅन न करता निकाल अचूक प्रमाणात कमी करण्यासाठी दुसऱ्या स्थितीत नियमित अभिव्यक्ती लागू करा.
इतर इंजिन, जसे की ओरेकल, वापरण्याची परवानगी देतात नियमित सारणी अभिव्यक्ती ही वैशिष्ट्ये ऑप्टिमायझरला दृश्यांमध्ये प्रेडिकेट घालण्यास आणि शक्य तितक्या लवकर इंटरमीडिएट डेटा व्हॉल्यूम कमी करण्यास मदत करतात. सहयोगी कार्य वातावरणात अनेक नेस्टेड दृश्ये किंवा जटिल व्याख्यांसह काम करताना हा दृष्टिकोन खूप उपयुक्त आहे.
अतिरिक्त सर्वोत्तम पद्धती: पॅरामीटर्स, भौतिक दृश्ये आणि क्वेरी विभाजन
निर्देशांक आणि अंमलबजावणी योजनांच्या पलीकडे, अनेक आहेत चांगल्या क्रॉस-कटिंग पद्धती जे कामगिरी आणि सुरक्षितता दोन्हीमध्ये योगदान देतात. सर्वात महत्वाचे म्हणजे पॅरामीटराइज्ड क्वेरी वापरा डायनॅमिक SQL तयार करण्यासाठी स्ट्रिंग्स एकत्र करण्याऐवजी, हे SQL इंजेक्शनचा धोका कमी करते आणि डेटाबेसला समान रचनेसह क्वेरींसाठी अंमलबजावणी योजना पुन्हा वापरण्याची परवानगी देते.
असलेल्या प्रणालींमध्ये खूप जड आणि पुनरावृत्ती होणारे प्रश्न (डॅशबोर्ड, कार्यकारी अहवाल, एकत्रित गणना), भौतिक दृश्ये ते एक उत्तम सहयोगी आहेत. सामान्य दृश्यापेक्षा वेगळे, ते क्वेरी निकाल भौतिकरित्या संग्रहित करतात, एक प्रकारचे पूर्व-गणना केलेले टेबल बनतात जे खूप लवकर अनुक्रमित आणि क्वेरी केले जाऊ शकते.
पोस्टग्रेएसक्यूएल, ओरेकल आणि एसक्यूएल सर्व्हर (त्यांच्या अनुक्रमित दृश्यांसह) विविध रिफ्रेश पर्यायांसह (मॅन्युअल, शेड्यूल्ड आणि काही प्रकरणांमध्ये ऑटोमॅटिक देखील) मटेरियलाइज्ड दृश्यांना मूळतः समर्थन देतात. मायएसक्यूएलमध्ये, थेट समर्थन नसल्यामुळे, हे वर्तन सहसा टेबल्स आणि प्रक्रियांसह अनुकरण केले जाते जे वेळोवेळी डेटा पुन्हा निर्माण करतात, बहुतेकदा ट्रिगर्स किंवा शेड्यूल्ड कार्यांद्वारे.
जेव्हा एखादी क्वेरी खूप जास्त टेबल्समध्ये सामील होते किंवा दृश्यांच्या जटिल मोज़ेकवर अवलंबून असते, तेव्हा दुसरी वैध रणनीती म्हणजे प्रश्न अनेक टप्प्यात विभागा.याचा अर्थ लहान संच (उदा. संबंधित आयडी) मिळविण्यासाठी प्रारंभिक क्वेरी चालवणे आणि नंतर माहिती पूर्ण करण्यासाठी अतिरिक्त क्वेरी चालवणे असा होतो. हा दृष्टिकोन विवेकीपणे वापरला पाहिजे, कारण तो डेटाबेस प्रवेशांची संख्या वाढवू शकतो, परंतु काही प्रकरणांमध्ये, तो योजनेची जटिलता आणि मध्यवर्ती संचांचा आकार लक्षणीयरीत्या कमी करतो.
या संपूर्ण प्रक्रियेत, देखरेख साधने जसे की pg_stat_statements, PgHero, PMM, क्वेरी स्टोअर, न्यू रेलिक किंवा डेटाडॉग कोणत्या क्वेरी हळू आहेत किंवा जास्त वेळा चालतात हे ते तुम्हाला पटकन ओळखण्यास मदत करू शकतात, जेणेकरून तुम्ही ऑप्टिमायझेशन प्रयत्नांना प्राधान्य देऊ शकता जिथे ते खरोखर महत्त्वाचे आहे.
एआयच्या मदतीने एसक्यूएल क्वेरीज ऑप्टिमाइझ करा
अलिकडच्या वर्षांत असे दिसून आले आहे की कृत्रिम बुद्धिमत्तेवर आधारित साधने जे तुमच्या क्वेरीज आणि डेटाबेस स्कीमाचे विश्लेषण करून सुधारणा सुचवतात: इंडेक्स सूचना, क्वेरी पुनर्लेखन, टेबल स्ट्रक्चरमधील बदल इ. EverSQL, DBScoop, PGAnalyzer किंवा Redshift Advisor सारखी नावे व्यावसायिक वातावरणात लोकप्रिय झाली आहेत.
हे उपाय मोठ्या प्रमाणात क्वेरी लॉगचे पुनरावलोकन करू शकतात, त्यांना आकडेवारी, अंमलबजावणी योजना आणि कामगिरी मेट्रिक्ससह क्रॉस-रेफरन्स करू शकतात आणि तिथून अकार्यक्षम नमुने किंवा अडथळे शोधा ते पहिल्या दृष्टीक्षेपात आपल्याला कळणार नाही. ते काही निर्देशांक तयार करण्याचा किंवा काढून टाकण्याचा काल्पनिक परिणाम मूल्यांकन करण्यास देखील मदत करतात.
तथापि, त्यांना समजून घेणे महत्वाचे आहे की आधार, पर्याय म्हणून नाही ते तुमच्या SQL ज्ञानावर आणि तुमच्या अनुप्रयोगाच्या समजुतीवर अवलंबून असते. तुम्हाला कदाचित एक इंडेक्स सूचना मिळेल जी सिद्धांततः विशिष्ट क्वेरीला गती देते परंतु एका गंभीर मॉड्यूलमध्ये लिहिण्यास लक्षणीयरीत्या बिघडवते. व्यवसाय संदर्भाशिवाय, टूलला सर्वात महत्त्वाचे काय आहे हे माहित नसते.
आदर्श संयोजन म्हणजे अशी टीम जी ऑप्टिमायझेशन तत्त्वांवर (योजना, निर्देशांक, सामान्यीकरण, प्रवेश नमुने) प्रभुत्व मिळवते आणि एआय वापरते विश्लेषणाला गती द्या आणि गृहीतके सत्यापित कराआंधळे निर्णय घेऊ नयेत.
जेव्हा तुम्ही या संपूर्ण तंत्रांचा संच आत्मसात करता - काळजीपूर्वक निर्देशांक डिझाइन, किमान स्तंभ निवड, JOIN आणि CTE चा बुद्धिमान वापर, कार्यक्षम पृष्ठांकन, आकडेवारीची नियमित देखभाल, भौतिक दृश्यांचे शोषण आणि अगदी AI साधनांचा आधार - मोठे डेटाबेस आता अनियंत्रित राक्षस राहिलेले नाहीत. आणि ते तुमच्या आर्किटेक्चरचा एक अंदाजे आणि स्केलेबल घटक बनतात, जे वापरकर्ता अनुभव किंवा पायाभूत सुविधांचे बजेट खराब न करता तुमच्या व्यवसायासोबत वाढण्यास सक्षम असतात.
