เชี่ยวชาญกฎคำนำหน้าซ้ายสุด ดัชนีแบบครอบคลุม และกับดักที่ทำให้ดัชนีล่องหนต่อตัววางแผน
เปิดบทเรียนนี้ใน Kodokonดัชนีแบบผสม (composite index) จัดเรียงแถวข้ามหลายคอลัมน์ ตามลำดับที่ประกาศไว้ กฎที่ชี้ขาดคือ คำนำหน้าซ้ายสุด (leftmost prefix): ดัชนีบน (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ดัชนีแบบครอบคลุม (covering index) บรรจุทุกคอลัมน์ที่การสืบค้นอ่าน ตัววางแผนจึงตอบได้โดยตรงจากดัชนี โดยไม่ต้องเปิดตาราง: นั่นคือความหมายของ 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=?)(department_id, salary) การสืบค้นใดที่ ไม่สามารถ ใช้มันได้?email?