Đọc EXPLAIN và tư duy tối ưu truy vấn SQL

Index không phải “bật chế độ nhanh” cho database. Nó là cấu trúc dữ liệu bổ sung giúp database tìm row với ít I/O hơn, đổi lại tốn storage và chi phí khi ghi.

B-tree index thực sự làm gì?

Phần lớn relational database dùng biến thể B-tree cho index thông thường. Leaf pages được tổ chức để hỗ trợ lookup và range scan hiệu quả. “B-tree + doubly linked list” là cách hình dung đơn giản, nhưng implementation khác nhau theo database; đừng biến sơ đồ minh họa thành hiến pháp.

1
CREATE INDEX index_users_on_email ON users(email);

Query có thể dùng index:

1
SELECT * FROM users WHERE email = 'hudson@example.com';

Nhưng optimizer có thể chọn sequential scan nếu bảng nhỏ hoặc điều kiện trả về phần lớn rows. Đọc cả bảng đôi khi rẻ hơn nhảy qua index rồi quay lại heap hàng nghìn lần.

Composite index và leftmost prefix

1
2
CREATE INDEX index_orders_on_customer_and_created_at
ON orders(customer_id, created_at);

Index phù hợp tốt với:

1
2
WHERE customer_id = ?
WHERE customer_id = ? AND created_at >= ?

Không mặc định tối ưu WHERE created_at >= ? vì cột đầu không được constrain. Thứ tự cột phụ thuộc query pattern, selectivity, sort và database—not alphabetical aesthetics.

Function trên indexed column

1
WHERE LOWER(email) = LOWER(?)

Index thường trên email không nhất thiết dùng được. PostgreSQL có expression index:

1
CREATE INDEX index_users_on_lower_email ON users (LOWER(email));

Tương tự, tránh biến đổi cột ngày nếu có thể viết range:

1
2
WHERE created_at >= '2026-08-20'
  AND created_at <  '2026-08-21'

Đọc EXPLAIN ANALYZE

1
2
3
4
5
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Quan sát:

  • estimated rows so với actual rows;
  • scan type;
  • loops;
  • sort method;
  • rows removed by filter;
  • buffer hits/reads;
  • node nào chiếm thời gian thật.

ANALYZE thực thi query. Với UPDATE/DELETE, đặt trong transaction rồi rollback hoặc dùng môi trường an toàn. Đừng benchmark production bằng cách vô tình chạy câu xóa—kết quả rất rõ nhưng hơi khó phục hồi.

Bind parameters

Parameter binding giảm SQL injection và giúp tách query structure khỏi data:

1
SELECT * FROM users WHERE email = $1;

Prepared statements có thể giảm parse/plan overhead, nhưng generic plan đôi khi không tối ưu cho mọi parameter distribution. Vẫn phải đo.

Checklist tối ưu

  1. Xác định query chậm từ production metrics.
  2. Lấy execution plan và actual row counts.
  3. Kiểm tra statistics/selectivity.
  4. Giảm rows/columns sớm.
  5. Tạo index phục vụ query thật.
  6. Đo lại write cost, storage và latency.
  7. Xóa index dư thừa.

Tối ưu SQL không phải sưu tập index. Mỗi index là một khoản vay: query đọc nhận lợi ích, còn insert/update phải trả góp.

References