SQL Index Under the Hood (Part 4)

"Đây là bài cuối của series. Chúng ta sẽ không học thêm lý thuyết. Chỉ làm đúng một việc: điều tra một query chậm giống như khi xử lý production."

Sau bài viết này bạn sẽ biết

• Đọc EXPLAIN ANALYZE

• Hiểu Execution Plan

• Khi nào dùng Composite Index, khi nào dùng Covering Index

• Benchmark thật trên 100K, 1M và 100M records

• Quy trình tối ưu query ngoài production

Production Story

2 giờ sáng, PagerDuty lại reo:

/api/orders/history — 3 seconds

Server khỏe, CPU thấp, RAM còn nhiều, network bình thường, application không báo lỗi. Nhưng API vẫn mất hơn 3 giây.

Việc đầu tiên mình làm không phải thêm Index — mà là chạy EXPLAIN ANALYZE.

Chuẩn bị dữ liệu

Lần này ta tạo một bảng ở quy mô gần với production lớn thật sự — 100 triệu dòng:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT,
    status VARCHAR(20),
    total NUMERIC(10,2),
    created_at TIMESTAMP,
    note TEXT
);

Seed dữ liệu:

INSERT INTO orders(customer_id, status, total, created_at, note)
SELECT
    (random()*100000)::BIGINT,
    CASE
        WHEN random() < 0.7 THEN 'PAID'
        WHEN random() < 0.9 THEN 'PENDING'
        ELSE 'FAILED'
    END,
    (random()*1000)::numeric,
    NOW() - ((random()*365)::int || ' days')::interval,
    md5(random()::text)
FROM generate_series(1, 100000000);

VACUUM ANALYZE orders;

⚠️ Lưu ý: seed 100 triệu dòng sẽ mất khá lâu và tốn vài GB dung lượng — nên chạy trên máy có SSD, đủ RAM, và kiên nhẫn chờ.

Benchmark: query gốc (chưa tối ưu)

SELECT * FROM orders
WHERE customer_id = 50000
ORDER BY created_at DESC
LIMIT 20;

Điều tra Execution Plan

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 50000
ORDER BY created_at DESC
LIMIT 20;

Planner trả về: Seq Scan → Sort → Limit

Đây là điểm mấu chốt: bạn chỉ cần 20 dòng, nhưng Database phải đọc, lọc và sắp xếp toàn bộ 100.000.000 dòng trước khi trả về.

Tối ưu lần 1 — Index đơn

CREATE INDEX idx_customer ON orders(customer_id);

Plan mới: Index Scan → Sort → Limit — tốt hơn, nhưng mỗi customer giờ có trung bình ~1.000 dòng (100M / 100.000 customer), nên vẫn còn kha khá dòng cần fetch và sort.

Đỡ hơn 3 giây rất nhiều, chỉ còn 1 mili giây.

Tối ưu lần 2 — Composite Index

Câu query có ORDER BY created_at DESC — đó là tín hiệu cần Composite Index theo đúng thứ tự lọc rồi sắp xếp:

CREATE INDEX idx_customer_created
ON orders(customer_id, created_at DESC);

Plan mới: Index Scan → Limit — bước Sort biến mất hoàn toàn. Vì dữ liệu trong Index đã sắp sẵn theo created_at DESC, Database chỉ cần lấy đúng 20 dòng đầu tiên rồi dừng — không phụ thuộc vào việc customer đó có 100 hay 100.000 đơn hàng.

Từ 1 mili giây xuống 0.065 mili giây — nhanh hơn hơn 15 lần so với bước trước.

Tối ưu lần 3 — Covering Index

API thực tế chỉ cần vài cột:

SELECT id, total, created_at FROM orders
WHERE customer_id = 50000
ORDER BY created_at DESC
LIMIT 20;

Planner vẫn phải nhảy sang bảng để lấy total. Giải pháp — gộp luôn cột đó vào Index:

CREATE INDEX idx_customer_cover
ON orders(customer_id, created_at DESC)
INCLUDE (total);

Plan mới: Index Only Scan → Limit — không còn truy cập bảng vật lý.

Đọc EXPLAIN ANALYZE theo thứ tự nào?

Khi mở Execution Plan, luôn kiểm tra theo đúng trình tự này:

1. Có phải Seq Scan không?

2. Có bước Sort không?

3. Có Bitmap Scan không?

4. Có phải Index Only Scan không?

5. Execution Time bao nhiêu?

6. Rows thực tế có khớp với Planner estimate không?

Trả lời được 6 câu hỏi này là đã xử lý được khoảng 80% các query chậm trong thực tế.

Quy trình tối ưu ngoài production

Query chậm
  → EXPLAIN ANALYZE
    → Seq Scan?
      → Có Index chưa?
        → Planner có dùng Index không?
          → Nếu không: vì sao? (Low Cardinality? Cần Sort?)
            → Composite Index? Covering Index?
              → Benchmark lại

Nguyên tắc: đừng tạo Index trước — hiểu Execution Plan trước.

Những sai lầm thường gặp

Sai lầm Hậu quả Index tất cả các cột INSERT/UPDATE chậm đi rõ rệt — với 100 triệu dòng, cái giá này càng lớn Không chạy ANALYZE Planner estimate sai → chọn sai Execution Plan Chỉ nhìn Execution Time, bỏ qua Plan Không hiểu vì sao chậm, dễ tối ưu sai chỗ Thấy Seq Scan liền nghĩ PostgreSQL bị lỗi Sai — Planner đang chọn cách nó tin là nhanh nhất

Tổng kết cả series

Part 1 — Vì sao Database chậm khi dữ liệu lớn Database đọc theo Page, không theo dòng. Không có Index → Full Table Scan.

Part 2 — Index hoạt động thế nào B-Tree giúp tìm đúng vị trí chỉ sau vài lần đọc — như một tấm bản đồ dẫn tới đúng Page.

Part 3 — Cách PostgreSQL ra quyết định Query Planner luôn chọn plan chi phí thấp nhất, không phải cứ có Index là dùng.

Part 4 — Áp dụng vào bài toán thực tế Đọc EXPLAIN ANALYZE, benchmark trước/sau, dùng Composite Index và Covering Index để đưa một query trên bảng 100 triệu dòng từ 3 giây xuống 0.1 mili giây.

Lời kết

Mình từng nghĩ tối ưu SQL là việc khá "mơ hồ" — thêm Index, chạy lại, nhanh hơn thì giữ, không thì xóa. Sau nhiều lần xử lý production, mình nhận ra đó không phải cách làm hiệu quả.

Điều quan trọng nhất không phải là thuộc lòng các loại Index, mà là hiểu cách Database suy nghĩ. Khi đọc được EXPLAIN ANALYZE và hiểu vì sao PostgreSQL chọn plan này thay vì plan khác, việc tối ưu không còn là thử may rủi — mà là một quá trình có cơ sở và có thể dự đoán được.