EXPLAIN QUERY PLAN trong SQLite: đọc kết quả và tìm truy vấn chậm

EXPLAIN QUERY PLAN cho biết truy vấn sẽ chạy thế nào

Trước khi tối ưu một truy vấn chậm, bạn cần biết SQLite thực sự đang làm gì. EXPLAIN QUERY PLAN in ra bản tóm tắt ngắn về chiến lược mà bộ lập kế hoạch (query planner) đã chọn: nó đụng đến những bảng nào, theo thứ tự nào, và dùng index nào (nếu có). Bản thân truy vấn không chạy; bạn chỉ nhận được kế hoạch.

Đặt các từ khóa này trước bất kỳ câu lệnh nào:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

Kết quả trông giống thế này:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

Một dòng đó cho bạn biết rất nhiều: SQLite đang dùng SEARCH (không phải quét), trên bảng users, dùng index unique được tạo tự động cho email, với email là khóa tra cứu. Đúng như bạn mong đợi.

SCAN và SEARCH: điều đầu tiên cần đọc

Mỗi dòng trong kế hoạch đều bắt đầu bằng SCAN hoặc SEARCH. Sự khác biệt này là tín hiệu quan trọng nhất trong toàn bộ kết quả.

• SCAN <table>: SQLite đọc mọi hàng của bảng (hoặc mọi mục của một index). Chi phí tăng theo kích thước bảng.
• SEARCH <table> USING ...: SQLite nhảy thẳng đến các hàng khớp qua index hoặc khóa chính. Chi phí tăng theo kích thước kết quả, không phải kích thước bảng.

Đây là so sánh song song. Một cột có index, cột còn lại thì không:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Kế hoạch đầu tiên báo SEARCH orders USING INDEX idx_orders_customer. Kế hoạch thứ hai báo SCAN orders: không có index trên status, nên SQLite đọc mọi hàng. Trên bảng nhỏ thì không thấy khác biệt; trên bảng một triệu hàng, đó là chênh lệch giữa vài mili giây và vài giây.

SCAN không phải lúc nào cũng sai. Với các bảng tra cứu rất nhỏ, hay những truy vấn thực sự trả về phần lớn các hàng, quét bảng là kế hoạch đúng. Nhưng trên bảng lớn với điều kiện lọc có tính chọn lọc cao, SCAN là dấu hiệu bạn nên thêm index.

Xác nhận index đang được dùng

Cụm cần tìm là USING INDEX <name> (hoặc USING COVERING INDEX <name>, sẽ nói thêm ở dưới). Nếu bạn tạo index với hy vọng bộ lập kế hoạch sẽ dùng nó, đây là cách kiểm tra:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

Bạn sẽ thấy SEARCH events USING INDEX idx_events_user (user_id=?). Nếu thay vào đó kế hoạch báo SCAN events, có gì đó đang ngăn bộ lập kế hoạch dùng index. Nguyên nhân thường gặp là bọc cột trong một hàm (WHERE lower(user_id) = ...), so sánh giữa các kiểu khác nhau, hoặc dùng LIKE '%foo%' với ký tự đại diện ở đầu.

Thử nhanh điều đó:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

Phần + 0 đó vô hiệu hóa index: kế hoạch quay về SCAN events. Mọi biểu thức trên cột có index đều gây ra kết quả tương tự.

Covering index hiển thị khác

Khi một index chứa mọi cột mà truy vấn cần, SQLite có thể trả lời truy vấn chỉ từ index mà không cần đụng đến bảng. Kế hoạch báo USING COVERING INDEX:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

Kế hoạch: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). Truy vấn cần price, index đã lưu sẵn sku và price, nên SQLite không bao giờ đọc bảng gốc. Covering index là kế hoạch nhanh nhất bạn có thể có cho một lần tra cứu, đáng để biết khi chọn những cột nào sẽ đánh index cùng nhau.

Đọc kế hoạch của phép join

Phép join là nơi kế hoạch trở nên thú vị. Mỗi dòng trong kế hoạch tương ứng với một bảng trong phép join, và thứ tự các dòng là thứ tự SQLite duyệt các bảng. Bảng đầu tiên là bảng ngoài; các bảng sau được tra cứu một lần cho mỗi hàng của bảng ngoài.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

Một kế hoạch điển hình:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Đọc từ trên xuống: SQLite tìm một khách hàng duy nhất bằng khóa chính, rồi với khách hàng đó, tra cứu các đơn hàng khớp qua index trên customer_id. Cả hai dòng đều là SEARCH, không có lần quét toàn bộ nào, đúng như bạn muốn.

Nếu bạn thấy SCAN o ở dòng thứ hai, mỗi lần tra cứu khách hàng sẽ kéo theo một lượt quét toàn bộ orders. Trên bảng lớn, đó là thảm họa. Cách sửa gần như luôn là thêm index trên cột dùng để join.

Truy vấn kết hợp và truy vấn con

Kế hoạch cho UNION, EXCEPT và truy vấn con (subquery) được lồng nhau. Mỗi nhánh hiển thị thụt lề bên dưới nút cha:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

Bạn sẽ thấy hai hàng con dưới tiêu đề COMPOUND QUERY, mỗi hàng cho một nhánh. Truy vấn con và CTE cũng tương tự: mỗi cái có một nút kế hoạch thụt lề riêng, và bạn đọc từng nút với cùng góc nhìn SCAN hay SEARCH.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

Truy vấn con trở thành một nút kế hoạch riêng ("LIST SUBQUERY" hoặc tương tự), với chiến lược truy cập riêng. Áp dụng cùng các bước kiểm tra ở mọi cấp.

EXPLAIN và EXPLAIN QUERY PLAN

Đây là hai thứ khác nhau, và nhiều người hay nhầm lẫn.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (không có QUERY PLAN) in ra bytecode mà máy ảo của SQLite sẽ thực thi: hàng chục opcode cấp thấp như OpenRead, SeekRowid, Column, ResultRow. Hữu ích nếu bạn đang gỡ lỗi chính engine. Gần như không bao giờ hữu ích khi tối ưu.

EXPLAIN QUERY PLAN là bản tóm tắt dễ đọc mà bạn thực sự cần. Khi phân vân, hãy luôn chọn EXPLAIN QUERY PLAN.

Quy trình xử lý truy vấn chậm

Khi một truy vấn chạy chậm, vòng lặp xử lý trông như sau:

1. Chạy EXPLAIN QUERY PLAN trên truy vấn đó.
2. Với mỗi dòng của bảng, tự hỏi: đây là SCAN hay SEARCH? Trên bảng lớn, SCAN là nghi phạm.
3. Nếu một SCAN đang lọc theo cột nào đó, cân nhắc tạo index trên cột đó.
4. Với phép join, xác nhận các bảng ở vòng lặp trong dùng SEARCH USING INDEX trên cột join.
5. Chạy lại EXPLAIN QUERY PLAN sau khi thêm index. Kế hoạch phải thay đổi. Nếu không, bộ lập kế hoạch đã quyết định index của bạn không đáng dùng, thường vì bảng nhỏ hoặc điều kiện lọc không đủ chọn lọc.

Ví dụ cụ thể cho bước 5:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

Kế hoạch đã đổi từ SCAN sang SEARCH. Đó là tín hiệu index đang làm đúng việc của nó. (Trên một bảng mới, gần như trống, bộ lập kế hoạch có thể vẫn quét vì chưa đủ dữ liệu để đáng dùng index: hãy thêm dữ liệu vào bảng hoặc chạy ANALYZE, lựa chọn thường sẽ đổi.)

Những gì kế hoạch không cho bạn biết

EXPLAIN QUERY PLAN mô tả chiến lược, không phải chi phí. Nó không cho bạn biết truy vấn mất 800 ms hay trả về 50.000 hàng. Để biết điều đó, bạn cần đo thời gian (.timer on trong CLI) và đếm số hàng. Kế hoạch và thời gian bổ sung cho nhau: kế hoạch cho biết vì sao truy vấn chậm, bộ đếm giờ cho biết truy vấn có chậm hay không.

Thêm hai giới hạn nên biết:

• Kế hoạch có thể thay đổi khi dữ liệu tăng lên. Một truy vấn quét bảng 100 hàng vẫn ổn sẽ cần index khi bảng lên đến một triệu hàng. Hãy kiểm tra lại kế hoạch trên dữ liệu cỡ production, không phải dữ liệu mẫu khi phát triển.
• Bộ lập kế hoạch dùng thống kê do ANALYZE thu thập. Không có chúng, nó dùng các giá trị mặc định không phải lúc nào cũng tốt. Thống kê cũ hoặc thiếu là lý do phổ biến của những kế hoạch bất ngờ.

Tiếp theo: ANALYZE và VACUUM

Bộ lập kế hoạch đưa ra quyết định dựa trên thống kê về bảng và index của bạn. Nếu thống kê bị thiếu hoặc lỗi thời, ngay cả một cấu trúc có index hoàn hảo cũng có thể cho ra kế hoạch tệ. ANALYZE là cách giữ chúng luôn mới, còn VACUUM là lệnh đi kèm để thu hồi dung lượng và chống phân mảnh tệ cơ sở dữ liệu. Đó là nội dung tiếp theo.