SQL क्वेरी ऑप्टिमाइजेशन

क्वेरी एग्जीक्यूशन प्लान्स को समझना

एक SQL क्वेरी के प्रदर्शन को बेहतर बनाने का पहला कदम यह समझना है कि डेटाबेस उसे चलाता कैसे है। हर बार जब आप कोई क्वेरी सबमिट करते हैं, तो क्वेरी ऑप्टिमाइज़र सबसे कुशल रास्ता तय करने के लिए कई रणनीतियाँ बनाता है। यह रास्ता ही 'एग्जीक्यूशन प्लान' कहलाता है।

EXPLAIN कमांड आपको यह अनुमानित प्लान दिखाता है, लेकिन EXPLAIN ANALYZE एक कदम आगे जाता है। यह वास्तव में क्वेरी को चलाता है और अनुमानित लागतों के साथ वास्तविक रनटाइम आँकड़े भी दिखाता है। यह आपको बताता है कि प्लान का हर हिस्सा कितना समय ले रहा है और कितनी पंक्तियों को प्रोसेस कर रहा है।

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 90000;

आउटपुट में, 'cost' (अनुमानित), 'actual time' (वास्तविक समय), और 'rows' (पंक्तियों की संख्या) जैसे मेट्रिक्स पर ध्यान दें। इन दोनों के बीच का अंतर आपको बताता है कि ऑप्टिमाइज़र के अनुमान कितने सटीक थे।

एडवांस्ड इंडेक्सिंग रणनीतियाँ

एक अच्छा एग्जीक्यूशन प्लान अक्सर सही इंडेक्सिंग पर निर्भर करता है। B-Tree इंडेक्स तो आम हैं, लेकिन जटिल परिदृश्यों में खास रणनीतियों की ज़रूरत होती है।

Covering Indexes एक कवरिंग इंडेक्स में वे सभी कॉलम होते हैं जिनकी क्वेरी को ज़रूरत होती है। इससे डेटाबेस को मुख्य टेबल (जिसे 'हीप' भी कहते हैं) को एक्सेस करने की ज़रूरत नहीं पड़ती। वह सिर्फ़ इंडेक्स को स्कैन करके ही परिणाम लौटा सकता है, जिससे I/O काफी कम हो जाता है।

-- मान लीजिए हमारे पास यह क्वेरी है:
SELECT employee_id, department FROM employees WHERE start_date > '2022-01-01';

-- एक कवरिंग इंडेक्स ऐसा दिखेगा:
CREATE INDEX idx_employees_start_date_covering ON employees (start_date, employee_id, department);

Partial Indexes जब आपको किसी टेबल के केवल एक छोटे हिस्से को बार-बार क्वेरी करना हो, तो पार्शियल इंडेक्स बहुत उपयोगी होते हैं। यह इंडेक्स केवल उन पंक्तियों पर बनता है जो एक নির্দিষ্ট WHERE क्लॉज को संतुष्ट करती हैं। इससे इंडेक्स का आकार छोटा रहता है और यह ज़्यादा कुशल होता है।

-- केवल एक्टिव कर्मचारियों के लिए एक इंडेक्स बनाएँ
CREATE INDEX idx_employees_active ON employees (employee_id)
WHERE status = 'active';

BRIN Indexes बहुत बड़ी तालिकाओं के लिए डिज़ाइन किए गए हैं जहाँ डेटा स्वाभाविक रूप से सॉर्ट किया गया हो, जैसे कि टाइम-सीरीज़ डेटा। यह हर ब्लॉक रेंज के लिए केवल न्यूनतम और अधिकतम मान स्टोर करता है, जिससे यह B-Tree इंडेक्स की तुलना में बहुत छोटा होता है।

जॉइन ऑप्टिमाइज़ेशन

जब आप कई टेबल्स को जॉइन करते हैं, तो ऑप्टिमाइज़र को यह तय करना होता है कि उन्हें कैसे जोड़ा जाए। दो सबसे आम रणनीतियाँ हैं: नेस्टेड लूप और हैश जॉइन।

Nested Loop Join यह रणनीति तब अच्छी होती है जब एक टेबल छोटी हो और दूसरी टेबल के जॉइन कॉलम पर एक इंडेक्स हो। यह बाहरी टेबल की हर पंक्ति के लिए आंतरिक टेबल में एक इंडेक्स लुकअप करता है। यह कुशल है अगर लुकअप की संख्या कम हो।

यह बड़े डेटासेट के लिए बेहतर है, खासकर जब जॉइन कॉलम पर कोई उपयोगी इंडेक्स न हो। इसमें दो चरण होते हैं:

Build Phase: ऑप्टिमाइज़र छोटी टेबल को स्कैन करता है और जॉइन की (key) के आधार पर मेमोरी में एक हैश टेबल बनाता है।
Probe Phase: फिर यह बड़ी टेबल को स्कैन करता है, हर पंक्ति के लिए जॉइन की (key) को हैश करता है, और हैश टेबल में मिलान ढूंढता है।

एनालिटिकल क्वेरीज़ और हाइरार्किकल डेटा

आधुनिक डेटा विश्लेषण में अक्सर जटिल गणनाएँ शामिल होती हैं। विंडो फ़ंक्शंस और कॉमन टेबल एक्सप्रेशंस (CTEs) इसके लिए शक्तिशाली टूल हैं।

Advanced Window Functions and Frames विंडो फ़ंक्शंस आपको वर्तमान पंक्ति से संबंधित पंक्तियों के एक सेट पर गणना करने की अनुमति देते हैं। ROW_NUMBER, LAG, और LEAD उपयोगी हैं, लेकिन असली शक्ति FRAMES से आती है। एक फ्रेम क्लॉज (ROWS BETWEEN या RANGE BETWEEN) उस विंडो को परिभाषित करता है जिस पर फ़ंक्शन काम करता है। यह मूविंग एवरेज या रनिंग टोटल जैसी गणनाओं के लिए आवश्यक है।

-- पिछले 30 दिनों के लिए मूविंग एवरेज सेलरी की गणना करें
SELECT
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date
        RANGE BETWEEN '30 days' PRECEDING AND CURRENT ROW
    ) AS moving_avg_30_day
FROM daily_sales_summary;

Recursive CTEs जब आपको हाइरार्किकल डेटा, जैसे कि एक संगठन चार्ट या एक फ़ाइल सिस्टम, को क्वेरी करने की आवश्यकता होती है, तो रिकर्सिव CTEs बहुत काम आते हैं। एक रिकर्सिव CTE खुद को संदर्भित करता है, जिससे आप एक पदानुक्रम के माध्यम से यात्रा कर सकते हैं।

यह एक 'एंकर मेम्बर' (शुरुआती बिंदु) और एक 'रिकर्सिव मेम्बर' (जो खुद को कॉल करता है) से बना होता है, जो UNION ALL द्वारा जुड़े होते हैं।

-- एक संगठन चार्ट को ट्रैवर्स करें
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: शीर्ष स्तर के प्रबंधक का चयन करें
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: प्रत्येक कर्मचारी को उसके प्रबंधक से जोड़ें
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Sargability अंत में, सर्गेबिलिटी (Sargability) का सिद्धांत याद रखें। इसका मतलब है कि एक क्वेरी 'Search ARGument Able' है, यानी ऑप्टिमाइज़र क्वेरी के WHERE क्लॉज को संतुष्ट करने के लिए एक इंडेक्स का उपयोग कर सकता है।

उदाहरण के लिए, WHERE YEAR(order_date) = 2023 सर्गेबल नहीं है क्योंकि ऑप्टिमाइज़र को हर पंक्ति पर YEAR() फ़ंक्शन चलाना पड़ता है। इंडेक्स का उपयोग नहीं किया जा सकता है।

इसके बजाय, WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01' लिखें। यह सर्गेबल है क्योंकि ऑप्टिमाइज़र सीधे order_date पर इंडेक्स का उपयोग करके आवश्यक डेटा रेंज को कुशलता से ढूंढ सकता है। हमेशा अपने प्रेडिकेट्स को ऐसे लिखें कि वे इंडेक्स का लाभ उठा सकें।

Quiz Questions 1/5

EXPLAIN ANALYZE कमांड EXPLAIN की तुलना में क्या अतिरिक्त जानकारी प्रदान करता है?

Quiz Questions 2/5

बहुत बड़ी तालिकाओं के लिए, जहाँ डेटा स्वाभाविक रूप से सॉर्ट किया गया हो (जैसे टाइम-सीरीज़ डेटा), कौन सा इंडेक्स प्रकार सबसे उपयुक्त है?

एडवांस डेटा एनालिटिक्स मास्टरी