EXPLAIN และแผนการสืบค้น: อ่านสิ่งที่ฐานข้อมูลทำจริง ๆ

ถอดรหัสแผนที่ตัววางแผนของ SQLite เลือกใช้จริงสำหรับการสืบค้นของคุณด้วย EXPLAIN QUERY PLAN

10 นาที · 3 คำถาม

SQL เป็นภาษา เชิงประกาศ (declarative): คุณอธิบายผลลัพธ์ที่ต้องการ ไม่ใช่วิธีที่จะได้มันมา สิ่งที่ตัดสินใจว่าจะอ่านข้อมูลอย่างไรคือ ตัววางแผนการสืบค้น (query planner) เพื่อสังเกตการทำงานของมัน SQLite มีคำสั่งให้สองแบบ: EXPLAIN QUERY PLAN ให้แผนระดับสูงที่อ่านเข้าใจง่าย ในขณะที่ EXPLAIN เพียงอย่างเดียวจะแสดงไบต์โค้ดของเครื่องเสมือน VDBE ออกมา ติดตั้งเครื่องมือบรรทัดคำสั่ง sqlite3 หรือเปิด sqliteonline.com ในเบราว์เซอร์ของคุณ แล้วลองทำซ้ำทุกตัวอย่างที่ตามมา

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

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

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

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

วาง EXPLAIN QUERY PLAN ไว้หน้าคำสั่ง SELECT ใด ๆ ผลลัพธ์แต่ละบรรทัดจะอธิบายว่าตารางถูกอ่านอย่างไร มีคำสำคัญสองคำที่โดดเด่น: SCAN หมายความว่าตารางถูกสแกน ทั้งหมด ทีละแถว ในขณะที่ SEARCH บ่งบอกถึงการเข้าถึงแบบ เจาะจง ที่นำทางด้วยดัชนี หากไม่มีดัชนีที่เหมาะสม แม้แต่การเทียบเท่าอย่างง่าย ๆ ก็ทำให้เกิดการสแกนทั้งตารางแล้ว

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Result: SCAN employees
-- (no index: the whole table is read)

สร้างดัชนีบนคอลัมน์ที่ใช้กรอง แล้วรันคำสั่งเดิมเป๊ะ ๆ อีกครั้ง: แผนจะเปลี่ยนจาก SCAN เป็น SEARCH ... USING INDEX การกล่าวถึงอย่างอื่นก็มีคุณค่าเช่นกัน: USE TEMP B-TREE เผยให้เห็นการเรียงลำดับหรือการจัดกลุ่มที่สร้างขึ้นในหน่วยความจำ (มักเป็นสัญญาณว่าขาดดัชนีสำหรับการเรียงลำดับ) และ USING COVERING INDEX ส่งสัญญาณว่าตารางไม่ได้ถูกเปิดขึ้นมาด้วยซ้ำ

CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)

ทดสอบความรู้

ตรวจสอบว่าคุณจำประเด็นสำคัญของบทเรียนนี้ได้ครบถ้วน

1. ใน SQLite EXPLAIN QUERY PLAN คืนค่าอะไรเมื่อเทียบกับ EXPLAIN เพียงอย่างเดียว?
   • ไบต์โค้ด VDBE ทีละคำสั่ง
   • แผนระดับสูง (SCAN, SEARCH, การเรียงลำดับชั่วคราว)
   • เวลาที่ใช้รันการสืบค้นจริงที่วัดได้
   • รายการดัชนีที่มีอยู่บนตาราง
2. ในแผนของ SQLite บรรทัด SCAN employees หมายความว่าอย่างไร?
   • ตารางถูกสแกนทั้งหมด ทีละแถว
   • มีการใช้ดัชนีแบบครอบคลุม
   • มีการค้นหาแบบเจาะจงผ่านดัชนี
   • ตารางถูกโหลดเข้าสู่แคชในหน่วยความจำ
3. คำสั่ง ANALYZE ใช้ทำอะไร?
   • มันสร้างดัชนีทั้งหมดของฐานข้อมูลขึ้นใหม่
   • มันเก็บรวบรวมสถิติลงใน sqlite_stat1 เพื่อนำทางตัววางแผน
   • มันรันการสืบค้นและวัดระยะเวลาของมัน
   • มันล็อกตารางระหว่างการวิเคราะห์