उन्नत इंडेक्स: कंपोजिट, कवरिंग, अनदेखा किया गया इंडेक्स

लेफ़्टमोस्ट-प्रिफ़िक्स नियम, कवरिंग इंडेक्स, और उन खतरों में महारत हासिल करें जो किसी इंडेक्स को प्लानर के लिए अदृश्य बना देते हैं।

11 मिनट · 3 प्रश्न

एक कंपोजिट इंडेक्स रो को कई कॉलमों पर, घोषित क्रम में क्रमबद्ध करता है। निर्णायक नियम है लेफ़्टमोस्ट प्रिफ़िक्स: (a, b) पर एक इंडेक्स a पर, या a और b पर फ़िल्टर की सेवा कर सकता है, लेकिन कभी अकेले b पर नहीं। इसलिए कॉलम का क्रम एक डिज़ाइन विकल्प है, कोई मामूली बात नहीं। पहले नीचे दिया गया डेटासेट बनाएँ।

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000),
  (4, 'Edsger', 1, 90000);

आइए (department_id, salary) पर एक इंडेक्स बनाएँ। department_id से शुरू होने वाला फ़िल्टर एक SEARCH को ट्रिगर करता है। लेकिन केवल salary पर फ़िल्टर लेफ़्टमोस्ट प्रिफ़िक्स को तोड़ देता है: प्लानर सीधे सही रो पर नहीं जा सकता और वापस SCAN पर लौट आता है। दोनों प्लान की तुलना करें।

CREATE INDEX idx_dept_salary
  ON employees (department_id, salary);

-- Leftmost prefix present: index used
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE department_id = 1
  AND salary > 60000;
-- SEARCH ... USING INDEX idx_dept_salary

-- Leading column missing: index ignored
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE salary > 60000;
-- SCAN employees

एक कवरिंग इंडेक्स में वह हर कॉलम होता है जिसे क्वेरी पढ़ती है। तब प्लानर सीधे इंडेक्स से उत्तर देता है, टेबल को खोले बिना: यही USING COVERING INDEX का अर्थ है। यहाँ, इंडेक्स (department_id, salary) एक ऐसी क्वेरी को कवर करता है जो केवल उन्हीं दो कॉलमों को पढ़ती है। याद रखें कि id (rowid) हर इंडेक्स में अंतर्निहित रूप से मौजूद होता है, इसलिए वह भी मुफ़्त में कवर हो जाता है।

-- Every column read is in the index
EXPLAIN QUERY PLAN
SELECT department_id, salary
FROM employees
WHERE department_id = 1;
-- SEARCH employees USING COVERING INDEX
-- idx_dept_salary (department_id=?)

ज्ञान जांच

सुनिश्चित करें कि आपको इस पाठ के मुख्य बिंदु याद हैं।

1. (department_id, salary) पर एक कंपोजिट इंडेक्स के साथ, कौन सी क्वेरी इसका उपयोग नहीं कर सकती?
   • WHERE department_id = 1
   • WHERE department_id = 1 AND salary > 60000
   • WHERE salary > 60000
   • WHERE department_id = 1 AND name = 'Ada'
2. कवरिंग इंडेक्स क्या है?
   • एक इंडेक्स जिसमें पढ़ा गया हर कॉलम होता है, जो टेबल खोलने से बचाता है
   • एक इंडेक्स जो एक साथ कई टेबलों को कवर करता है
   • हर INSERT के बाद स्वचालित रूप से पुनः बनाया गया इंडेक्स
   • प्राइमरी की पर यूनिक इंडेक्स का पर्यायवाची
3. इनमें से कौन सी शर्त email पर एक साधारण इंडेक्स के उपयोग को रोकती है?
   • WHERE email = 'a@b.co'
   • WHERE email > 'm'
   • WHERE lower(email) = 'a@b.co'
   • WHERE email IN ('a@b.co', 'c@d.co')