Tối ưu truy vấn SQLite: Index, EXPLAIN QUERY PLAN và những lỗi thường gặp

Khi ứng dụng SQLite chậm, thêm index theo cảm tính thường tạo thêm dung lượng và chi phí ghi mà chưa chắc sửa đúng truy vấn. Quy trình hiệu quả là đo truy vấn thật, đọc kế hoạch thực thi, thiết kế index theo điều kiện lọc và sắp xếp, rồi đo lại trên dữ liệu có kích thước gần production.

Bắt đầu bằng truy vấn chậm, không bắt đầu bằng index

Ghi nhận câu SQL, tham số, thời gian chạy, số hàng và tần suất gọi. Một truy vấn 80 ms chạy mỗi giờ ít quan trọng hơn truy vấn 8 ms xuất hiện hàng nghìn lần trong một thao tác. Hãy kiểm tra cả thời gian khóa và số lần truy vấn do ORM tạo ra.

EXPLAIN QUERY PLAN
SELECT id, total
FROM orders
WHERE customer_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN QUERY PLAN cho biết SQLite đang quét toàn bảng (SCAN), tìm qua index (SEARCH ... USING INDEX) hay tạo B-tree tạm để sắp xếp. Đầu ra dành cho gỡ lỗi tương tác và có thể thay đổi giữa các phiên bản; không nên viết logic ứng dụng phụ thuộc chuỗi mô tả này.

Thiết kế index ghép theo truy vấn

Với truy vấn trên, một index hợp lý là:

CREATE INDEX idx_orders_customer_status_created
ON orders(customer_id, status, created_at DESC);

Các cột so sánh bằng thường đứng trước, sau đó đến cột phạm vi hoặc sắp xếp. Thứ tự cột rất quan trọng vì index nhiều cột hoạt động theo tiền tố bên trái. Một index bắt đầu bằng customer_id có thể hỗ trợ truy vấn theo khách hàng, nhưng thường không giúp nhiều cho truy vấn chỉ lọc status.

Covering index: nhanh hơn nhưng không miễn phí

Nếu index chứa cả các cột cần trả về, SQLite có thể đọc kết quả mà không quay lại bảng. Thêm total vào cuối index có thể tạo covering index cho ví dụ trên. Đổi lại, file cơ sở dữ liệu lớn hơn, mỗi lần ghi phải cập nhật nhiều dữ liệu hơn và cache chứa ít trang hữu ích hơn.

Không tạo một index riêng cho mọi truy vấn. Hãy tìm index có thể phục vụ nhiều truy vấn quan trọng và loại bỏ index trùng tiền tố khi đã xác minh không còn cần thiết.

Khi index không được sử dụng

• Bảng nhỏ khiến quét toàn bảng rẻ hơn.
• Cột có độ chọn lọc thấp, chẳng hạn phần lớn hàng có cùng trạng thái.
• Điều kiện dùng hàm hoặc phép biến đổi không khớp expression index.
• Kiểu dữ liệu tham số và cột không phù hợp.
• Index không khớp tiền tố trái hoặc truy vấn trả về phần lớn bảng.
• Thống kê chưa phản ánh dữ liệu hiện tại.

Đừng ép index bằng INDEXED BY như một mẹo tối ưu thông thường. Tài liệu SQLite mô tả nó chủ yếu như cách phát hiện thay đổi kế hoạch ngoài ý muốn, không phải lời gợi ý mềm cho planner.

Cập nhật thống kê bằng PRAGMA optimize

SQLite khuyến nghị chạy PRAGMA optimize; định kỳ và sau thay đổi schema, đặc biệt sau khi tạo index. Với kết nối tồn tại lâu, có thể chạy PRAGMA optimize=0x10002; khi mở rồi gọi PRAGMA optimize; theo chu kỳ hoặc trước khi đóng. Lệnh thường không làm gì và chỉ chạy ANALYZE khi planner có thể hưởng lợi.

Đừng quên mẫu N+1 và phân trang

Một truy vấn nhanh vẫn tạo hệ thống chậm nếu bị gọi một lần cho mỗi hàng. Dùng eager loading, join hoặc truy vấn theo lô để loại N+1. Với bảng lớn, phân trang bằng con trỏ như WHERE id < ? ORDER BY id DESC LIMIT ? thường ổn định hơn OFFSET sâu.

Checklist tối ưu an toàn

1. Tái hiện trên dữ liệu gần kích thước thật.
2. Đo trước và sau bằng cùng truy vấn, tham số và cache state.
3. Đọc query plan, không suy đoán.
4. Thêm một thay đổi tại một thời điểm.
5. Đo tác động lên INSERT, UPDATE, dung lượng và migration.
6. Chạy test đúng kết quả, không chỉ benchmark tốc độ.
7. Theo dõi truy vấn chậm sau khi phát hành.

Kết luận

Tối ưu SQLite là bài toán cân bằng giữa tốc độ đọc, chi phí ghi và độ phức tạp vận hành. Query plan cho biết cơ sở dữ liệu đang làm gì; index ghép và covering index chỉ nên được thêm khi số liệu chứng minh chúng giải quyết một đường truy cập quan trọng.

Nguồn tham khảo

• SQLite: EXPLAIN QUERY PLAN
• SQLite: Query Planning
• SQLite: ANALYZE and PRAGMA optimize