Database Engineering for Backend & AI Engineers
Trang chủ / Module 3
⚡ Module 3 / 14 · 1-2 tuần

Indexing & Query Optimization

Cách index thực sự hoạt động bên dưới (B-Tree/Hash), đọc execution plan bằng EXPLAIN ANALYZE, và thiết kế composite/covering index đúng.

IndexEXPLAINB-Tree
📌 Vì sao module này quan trọng

Đây là kỹ năng phân biệt rõ nhất giữa engineer viết được SQL và engineer làm cho hệ thống chạy nhanh ở quy mô thật. Một query chạy tốt với 1.000 dòng dữ liệu test có thể mất 30 giây với 50 triệu dòng production nếu thiếu index đúng — và thêm index sai chỗ còn làm chậm write mà không giúp gì cho read. Module Algorithms đã dạy B-Tree về mặt cấu trúc dữ liệu; module này áp dụng nó vào quyết định index thực tế hàng ngày.

🎯 Mục tiêu học tập

  • Giải thích cách B-Tree index tăng tốc tra cứu và tại sao nó không giúp ích cho mọi loại truy vấn
  • Đọc và diễn giải kết quả EXPLAIN ANALYZE để tìm ra bottleneck thực sự
  • Thiết kế composite index đúng thứ tự cột theo pattern truy vấn thực tế
  • Nhận biết dấu hiệu over-indexing và biết đánh đổi giữa tốc độ đọc và tốc độ ghi

B-Tree index hoạt động thế nào — và giới hạn của nó

Mặc định, hầu hết index trong PostgreSQL/MySQL là B-Tree — cấu trúc cây cân bằng cho phép tra cứu, range scan, và sắp xếp trong O(log n) thay vì quét tuần tự O(n) toàn bảng. Nhưng B-Tree chỉ hiệu quả khi điều kiện WHERE dùng được thứ tự của cây: =, <, >, BETWEEN, LIKE 'abc%' (prefix). Nó không giúp ích cho LIKE '%abc' (không có prefix cố định) hay áp dụng hàm lên cột được index (WHERE LOWER(email) = ... cần index trên biểu thức, không phải index thường trên email).

Loại indexPhù hợp cho
B-Tree (mặc định)So sánh, range, sort — trường hợp phổ biến nhất
HashChỉ so sánh bằng (=), không hỗ trợ range — hiếm dùng trực tiếp
GINFull-text search, JSONB, mảng — tìm kiếm bên trong cấu trúc phức tạp
GiST/BRINDữ liệu không gian (geo), hoặc bảng cực lớn sắp theo thời gian (BRIN)

Đọc EXPLAIN ANALYZE — dừng đoán mò, bắt đầu đo

Không bao giờ tối ưu query dựa trên cảm giác — luôn chạy EXPLAIN ANALYZE để xem kế hoạch thực thi thật và thời gian thật:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

-- Kết quả cần chú ý:
-- Seq Scan on orders  (cost=0.00..18334.00 rows=1 width=120) (actual time=95.2..95.2 rows=1 loops=1)
--   Filter: (customer_id = 42 AND status = 'shipped')
--   Rows Removed by Filter: 499999

Seq Scan nghĩa là quét toàn bảng — chấp nhận được với bảng nhỏ, là dấu hiệu cảnh báo với bảng lớn thường xuyên được lọc theo điều kiện đó. Sau khi thêm CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);, kế hoạch sẽ đổi thành Index Scan với thời gian giảm đáng kể. Luôn so sánh cost (ước lượng) với actual time (thực đo) — 2 con số lệch xa nhau là dấu hiệu bảng thống kê (statistics) đã lỗi thời, cần chạy ANALYZE.

Composite index — thứ tự cột quyết định tất cả

Composite (multi-column) index chỉ dùng được hiệu quả cho các điều kiện tuân theo thứ tự cột từ trái sang phải — gọi là "leftmost prefix rule". Với index (customer_id, status, created_at):

  • ✅ WHERE customer_id = 42 — dùng được index
  • ✅ WHERE customer_id = 42 AND status = 'shipped' — dùng được index
  • ✅ WHERE customer_id = 42 AND status = 'shipped' ORDER BY created_at — dùng được cả phần sort, không cần thêm bước sắp xếp riêng
  • ❌ WHERE status = 'shipped' (bỏ qua customer_id) — không dùng được index này hiệu quả

Quy tắc thực dụng: đặt cột dùng với = lên trước, cột dùng cho range/sort đặt sau cùng. Covering index — index chứa đủ mọi cột mà query cần (kể cả qua INCLUDE trong PostgreSQL) — cho phép trả kết quả hoàn toàn từ index mà không cần đọc bảng chính (gọi là "index-only scan"), nhanh hơn đáng kể cho query đọc nhiều.

Over-indexing — cái giá ẩn của index

Mỗi index tăng tốc read nhưng làm chậm mọi write (INSERT/UPDATE/DELETE) vì database phải cập nhật đồng thời bảng dữ liệu và mọi index liên quan, đồng thời chiếm thêm dung lượng đĩa. Dấu hiệu over-indexing: có nhiều index trùng lặp một phần (ví dụ index trên (a) và index trên (a, b) — index đầu gần như thừa), hoặc index trên cột hiếm khi xuất hiện trong WHERE/JOIN/ORDER BY. Nguyên tắc: thêm index dựa trên pattern truy vấn thực tế đo được (query log, pg_stat_statements), không thêm "phòng khi cần" cho mọi cột.

🏋️ Bài tập thực hành

Chẩn đoán và sửa 1 query chậm bằng EXPLAIN ANALYZE

Tạo bảng orders với ít nhất 1 triệu dòng dữ liệu giả (dùng generate_series trong PostgreSQL). Viết 1 câu query lọc theo 2-3 điều kiện kết hợp với ORDER BY và LIMIT, đo thời gian chạy khi chưa có index (dùng EXPLAIN ANALYZE). Thiết kế composite index đúng thứ tự cột, tạo index, đo lại thời gian, và giải thích bằng lời sự khác biệt trong execution plan trước/sau. Sau đó thử thêm 1 index "sai" (thứ tự cột ngược) và cho thấy tại sao nó không được dùng.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Giải thích được vì sao B-Tree index không giúp ích cho LIKE '%abc'
  • Đọc được kết quả EXPLAIN ANALYZE và xác định đúng chỗ đang chậm
  • Thiết kế composite index đúng thứ tự cột theo leftmost prefix rule
  • Giải thích được cái giá của over-indexing đối với tốc độ ghi