Hai bài trước dựng nền: bảng và index đều là cây B-tree, và tìm theo khóa chỉ tốn vài lần đọc trang. Nhưng làm sao biết SQLite có thực sự dùng cây index hay không, thay vì quét từng dòng? Câu trả lời nằm ở một câu lệnh bạn nên gõ trước mọi truy vấn quan trọng: EXPLAIN QUERY PLAN. Nó cho xem kế hoạch SQLite định chạy trước khi chạy — và khác biệt giữa hai kế hoạch, đo trên bảng hai triệu dòng trong container sqlite 3.45.3, là ba nghìn lần.
Một từ quyết định tất cả: SCAN hay SEARCH
Dựng bảng đơn hàng hai triệu dòng, rồi hỏi SQLite sẽ làm gì khi tìm theo cột khach lúc chưa có index:
EXPLAIN QUERY PLAN SELECT * FROM don WHERE khach='KH012345';
-- `--SCAN don
Một từ: SCAN. SQLite nói thẳng nó sẽ đọc từng dòng của bảng — đi qua cả 2 triệu dòng, kiểm khach của mỗi dòng. Vì không có cây index cho khach, nó không còn cách nào khác. Đo thời gian thật (lặp nhiều lần lấy trung bình):
SCAN don (2 triệu dòng, không index): 43 ms / truy vấn
Giờ tạo một index trên khach và hỏi lại đúng câu đó:
CREATE INDEX idx_khach ON don(khach);
EXPLAIN QUERY PLAN SELECT * FROM don WHERE khach='KH012345';
-- `--SEARCH don USING INDEX idx_khach (khach=?)
Từ đổi thành SEARCH ... USING INDEX. SQLite giờ sẽ đi xuống cây B-tree của idx_khach để nhảy thẳng tới đúng các dòng khớp (ở đây có 40 dòng) thay vì quét cả bảng. Thời gian:
SEARCH USING INDEX idx_khach: 0,014 ms / truy vấn
43 ms xuống 0,014 ms — nhanh hơn khoảng 3.000 lần. Cùng một câu SELECT, cùng một dữ liệu; khác biệt duy nhất là có một cây index để đi hay không. Và bạn biết trước điều đó chỉ bằng cách đọc một từ trong EXPLAIN QUERY PLAN, không cần chạy thật.
Hình 1: Cùng một truy vấn, hai kế hoạch — SCAN đọc cả hai triệu dòng (43 ms), SEARCH đi qua cây index tới đúng 40 dòng khớp (0,014 ms). EXPLAIN QUERY PLAN cho biết bạn đang ở phía nào trước khi chạy.
Khóa chính: nhanh hơn cả index
Tìm theo INTEGER PRIMARY KEY còn nhanh hơn nữa, vì như phần 3 đã đo, nó chính là khóa của cây bảng — không cần một cây index phụ, đi thẳng vào cây dữ liệu:
EXPLAIN QUERY PLAN SELECT * FROM don WHERE id=1234567;
-- `--SEARCH don USING INTEGER PRIMARY KEY (rowid=?)
SEARCH USING INTEGER PRIMARY KEY: 0,003 ms / truy vấn
0,003 ms — nhanh hơn cả index thường (0,014 ms), vì tìm theo index còn phải qua hai cây (index → bảng), còn tìm theo khóa chính chỉ qua một. Đây là lý do thực dụng để truy vấn theo khóa chính bất cứ khi nào có thể.
COVERING INDEX: khỏi tra về bảng
Có một mức tối ưu nữa mà EXPLAIN QUERY PLAN bóc ra. Bình thường, tìm theo index là hai bước: xuống cây index lấy rowid, rồi xuống cây bảng lấy trọn dòng. Nhưng nếu mọi cột bạn cần đều đã nằm trong index, bước thứ hai là thừa. Chọn chỉ cột khach:
EXPLAIN QUERY PLAN SELECT khach FROM don WHERE khach='KH012345';
-- `--SEARCH don USING COVERING INDEX idx_khach (khach=?)
Chữ COVERING nghĩa là index đã "phủ" trọn truy vấn — SQLite lấy câu trả lời ngay từ cây index mà không chạm tới cây bảng lần nào. Đây là cơ sở của một kỹ thuật tối ưu quan trọng: đưa thêm vào index những cột hay được đọc kèm, để biến một truy vấn hai cây thành một cây. Bài sau về bộ lập kế hoạch sẽ đo đúng lợi ích đó.
Cái giá của index, và vì sao không index tất cả
Nếu index nhanh gấp ba nghìn lần, sao không tạo index cho mọi cột? Vì index không miễn phí, và EXPLAIN QUERY PLAN chỉ cho thấy phía đọc. Phần 2 đã đo: mỗi index là một cây B-tree riêng tốn dung lượng (index trên nửa triệu dòng ngốn thêm mấy nghìn trang). Quan trọng hơn, mỗi index phải được cập nhật theo ở mỗi lần INSERT, UPDATE, DELETE — thêm một dòng vào bảng có ba index nghĩa là ghi vào bốn cây B-tree, không phải một. Index tăng tốc đọc bằng cách làm chậm ghi và tốn đĩa. Quy tắc: tạo index cho đúng những cột trong mệnh đề WHERE và JOIN mà truy vấn thật của bạn dùng — và cách kiểm chứng một index có được dùng hay không, một lần nữa, là EXPLAIN QUERY PLAN.
Hình 2: Bốn kế hoạch và thời gian thật đo trên hai triệu dòng — và cái giá phía ghi khiến ta chỉ index đúng cột cần, không index tất cả.
Vì sao điều này quan trọng
EXPLAIN QUERY PLAN là công cụ chẩn đoán hiệu năng quan trọng nhất của SQLite, và nó rẻ: gõ trước truy vấn, đọc một từ. Thấy SCAN trên một bảng lớn trong đường nóng là tín hiệu đỏ — gần như luôn có một index biến nó thành SEARCH và một khác biệt nghìn lần như ta vừa đo. Nhưng tới đây mới chỉ là chuyện SQLite có dùng index hay không. Câu hỏi tinh hơn là: khi một bảng có nhiều index khả dĩ, SQLite chọn cái nào, và chọn dựa trên gì? Đó là việc của bộ lập kế hoạch truy vấn và thống kê ANALYZE — bài sau đo cách SQLite ước lượng chi phí và đôi khi chọn sai khi thiếu thống kê.
