Skip to content
~/zcrossoverz

Tối ưu SQL Nâng Cao: Chiến lược Index, Giải mã Execution Plan và Tuning Query cho Hệ thống Ngân hàng

By Nhân Nguyễn on Apr 19, 2026
#database#backend

Tối ưu SQL Nâng Cao: Chiến lược Index, Giải mã Execution Plan và Tuning Query cho Hệ thống Ngân hàng

Làm thế nào để giảm thời gian truy vấn từ 8 giây xuống còn 200ms trên bảng 50 triệu dòng?
Câu chuyện từ X Bank và bài học về B-Tree, Composite Index, và Covering Index

Mở đầu: Cú sốc 8 giây

Hãy tưởng tượng bạn là kỹ sư database tại X Bank. API tìm kiếm giao dịch /api/transactions với tham số đơn giản:

SELECT id, account_id, amount, status, created_at, description
FROM transactions
WHERE account_id = 'ACC001'
  AND status = 'COMPLETED'
  AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20

Bảng transactions có 50 triệu dòng. Business SLA yêu cầu dưới 500ms. Nhưng thực tế, API trả về sau 8 giây.

Không có N+1, không thiếu LIMIT. Database có index mặc định trên id (primary key). Query planner chọn… Full table scan.

Tại sao? Câu trả lời nằm ở cách database thực sự “suy nghĩ” về dữ liệu.

Phần 1: Mental Model — Cơ chế bên trong của Index

1.1 B-Tree Index: Không chỉ là danh sách có sắp xếp

Hầu hết developer nghĩ index như một danh sách đã sort. Thực tế, B-Tree là cây tự cân bằng đảm bảo lookup O(log n) ngay cả khi dữ liệu lớn:

                    [50 | 100]                    ← Root node
                   /    |     \
         [10 | 30]    [60 | 80]    [120 | 150]   ← Internal nodes
         /   |   \    /  |  \      /   |    \
    [5][15][35] [65][75][85] [125][160] ...      ← Leaf nodes (chứa TIDs)

Điều quan trọng cần hiểu:

Với 50 triệu dòng, nếu là binary tree đơn thuần, cần ~26 levels (log₂(50M)). Nhưng B-Tree có fan-out rộng (100-1000), depth thực tế chỉ 3-4. Mỗi level là một random I/O. So sánh:

  • Full scan: 50 triệu I/Os (nếu mỗi dòng một page)
  • Index scan: 3-4 I/Os cho B-Tree + heap fetch

Leaf nodes chứa row pointers (TIDs trong PostgreSQL). Query có hai phase:

  1. Index scan: Duyệt B-Tree tìm TIDs (3-4 I/Os)
  2. Heap fetch: Đọc row thực tế từ table pages (1 I/O mỗi row hoặc mỗi page)

1.2 Composite Index & Leftmost Prefix Rule — Lỗi số 1 mà developer mắc phải

CREATE INDEX idx_tx_account_status_date 
ON transactions(account_id, status, created_at DESC);

Index này tạo cây B-Tree sort theo account_id trước, trong mỗi account_id sort theo status, trong mỗi status sort theo created_at:

Account ACC001:
  Status COMPLETED:
    2026-01-01, 2026-01-05, 2026-01-10, ...  ← sorted by created_at
  Status PENDING:
    2026-01-02, 2026-01-06, ...

Account ACC002:
  ...

Leftmost Prefix Rule: Index chỉ được sử dụng khi filter bắt đầu từ cột đầu tiên.

WHERE clauseCó dùng index không?Lý do
account_id = ?✅ CóBắt đầu từ cột đầu
account_id = ? AND status = ?✅ CóĐúng thứ tự prefix
account_id = ? AND status = ? AND created_at > ?✅ CóĐúng cả 3 cột
status = ? AND account_id = ?❌ KhôngThiếu cột đầu (account_id)
created_at > ?❌ KhôngThiếu 2 cột đầu

1.3 Selectivity — Tại sao thứ tự cột lại quan trọng đến vậy?

Selectivity = số giá trị unique / tổng số dòng. Giá trị càng nhỏ, cột càng “selective” (filter mạnh).

Với bảng 50M dòng:

  • account_id: 500K accounts → selectivity = 0.00001 (mỗi account ~100 dòng) — rất selective
  • status: 4 values → selectivity = 0.25 (25% dòng) — ít selective
  • created_at: millions of distinct values → rất selective

Sai lầm kinh điển: Đặt cột ít selective lên đầu.

-- ❌ SAI: Index (status, account_id, created_at)
WHERE status = 'COMPLETED' AND account_id = 'ACC001'
-- Index phải quét 25% bảng (12.5M dòng) trước khi lọc account_id

-- ✅ ĐÚNG: Index (account_id, status, created_at)  
WHERE account_id = 'ACC001' AND status = 'COMPLETED'
-- Index quét ngay 100 dòng của ACC001, sau đó lọc status

Nguyên tắc vàng: Đặt cột selectivity cao nhất (giá trị unique nhiều nhất) lên đầu composite index.

1.4 Covering Index — Khi index tự mình trả lời query

Covering index chứa tất cả columns cần thiết trong chính index, không cần heap fetch.

CREATE INDEX idx_tx_covering ON transactions(account_id, status, created_at DESC)
INCLUDE (amount, description);

-- Query này KHÔNG cần touch heap:
SELECT account_id, status, amount, description, created_at
FROM transactions
WHERE account_id = 'ACC001' AND status = 'COMPLETED';

PostgreSQL thực hiện:

  1. Navigate index tree (3-4 I/Os)
  2. Đọc amount và description từ index leaf (đã có sẵn)
  3. Không bao giờ chạm heap → “Index Only Scan”

Benchmark:

  • Index + heap fetch: 100 dòng → 100 I/Os
  • Covering index: 100 dòng → 3-4 I/Os (chỉ index traversal)

Trade-off: Covering index tốn storage gấp 2-3 lần và chậm hơn trên writes.

Phần 2: Đọc và hiểu EXPLAIN ANALYZE

2.1 Output mẫu

EXPLAIN ANALYZE 
SELECT ... WHERE account_id = 'ACC001' AND status = 'COMPLETED' ...

Limit  (cost=10.29..10.35 rows=20 width=50) 
       (actual time=0.234..0.250 rows=20 loops=1)
  ->  Sort  (cost=10.29..10.35 rows=20 width=50)
            (actual time=0.228..0.238 rows=20 loops=1)
        Sort Key: created_at DESC
        ->  Index Scan using idx_tx_account_status_date on transactions
            (cost=0.29..10.00 rows=100 width=50)
            (actual time=0.042..0.080 rows=100 loops=1)
              Index Cond: (account_id = 'ACC001' AND status = 'COMPLETED' 
                           AND created_at > '2026-01-01')

2.2 Các metrics quan trọng

MetricÝ nghĩaRed flag
costChi phí ước lượng (đơn vị arbitrary)So sánh giữa các plan
rows (estimated)Số dòng planner ước lượngChênh lệch lớn với actual → statistics stale
rows (actual)Số dòng thực tếPlanner sai → cần ANALYZE
actual timeThời gian thực tế (ms)> SLA → cần tối ưu
loopsSố lần node được thực thi>1 trong nested loop có thể là N+1

2.3 Red flags cần cảnh giác

🚩 Seq Scan on transactions (cost=0.00..147500.00 rows=50000000)
→ Full table scan trên bảng lớn. Thiếu index hoặc index không dùng được.

🚩 rows=50000000 (estimated) vs rows=100 (actual)
→ Stale statistics. Chạy ANALYZE ngay.

🚩 Nested Loop (cost=0.00..1000000000.00 rows=1000000)
→ Join không có index trên foreign key.

Phần 3: Production-Grade Implementation

-- Step 1: Composite index theo đúng thứ tự selectivity
CREATE INDEX CONCURRENTLY idx_tx_account_status_date 
ON transactions(account_id, status, created_at DESC);

-- Step 2: Covering index cho query phổ biến nhất
CREATE INDEX CONCURRENTLY idx_tx_covering 
ON transactions(account_id, status, created_at DESC)
INCLUDE (amount, description);

-- Step 3: Index trên mọi foreign key
CREATE INDEX CONCURRENTLY idx_tx_ledger_id ON transactions(ledger_id);

Lưu ý: Dùng CONCURRENTLY để không lock table khi tạo index trên production.

3.2 Monitoring index health

-- Tìm index không được sử dụng
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

-- Tìm index bị fragment (bloat)
SELECT schemaname, tablename, indexname,
       pg_relation_size(indexname::regclass) as size_bytes,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_tup_read DESC;

3.3 Scheduled maintenance

-- Refresh statistics hàng đêm (cập nhật selectivity)
ANALYZE transactions;

-- Rebuild index bị fragment (chạy lúc traffic thấp)
REINDEX INDEX CONCURRENTLY idx_tx_account_status_date;
REINDEX INDEX CONCURRENTLY idx_tx_covering;

Phần 4: Anti-Patterns và Cách Fix

Anti-Pattern 1: Over-indexing

Vấn đề: Index mọi cột “để dành”.

-- ❌ 6 indexes trên 8 columns
CREATE INDEX idx_id ON transactions(id);
CREATE INDEX idx_account ON transactions(account_id);
CREATE INDEX idx_status ON transactions(status);
CREATE INDEX idx_created ON transactions(created_at);
CREATE INDEX idx_amount ON transactions(amount);
CREATE INDEX idx_desc ON transactions(description);

Hậu quả: Mỗi INSERT/UPDATE/DELETE maintain cả 6 indexes.

  • 1 index: 100K rows insert trong 2 giây
  • 6 indexes: 100K rows insert trong 6.2 giây (3x chậm hơn)

Fix: Chỉ index các column thực sự dùng trong WHERE, JOIN, ORDER BY. Dùng composite index thay vì nhiều single-column indexes.

Anti-Pattern 2: Hàm trên indexed column

-- ❌ Query này KHÔNG dùng được index trên status
SELECT * FROM transactions WHERE LOWER(status) = 'completed';

-- ✅ Cách 1: Store dữ liệu đã chuẩn hóa
-- (luôn store 'COMPLETED' thay vì 'Completed' hoặc 'completed')

-- ✅ Cách 2: Functional index
CREATE INDEX idx_status_lower ON transactions(LOWER(status));

Benchmark:

  • Indexed, no function: 5ms
  • Indexed, with function (no functional index): 8000ms

Anti-Pattern 3: SELECT * phá vỡ covering index

-- ❌ Covering index có amount, description nhưng SELECT * cần 15 columns
SELECT * FROM transactions WHERE account_id = 'ACC001';  -- heap fetch vẫn xảy ra

-- ✅ Chỉ select đúng columns trong covering index
SELECT account_id, status, amount, description, created_at
FROM transactions WHERE account_id = 'ACC001';  -- Index Only Scan

Anti-Pattern 4: Thiếu index trên foreign key

-- ❌ Không index trên ledger_id
SELECT t.* FROM transactions t
JOIN ledgers l ON t.ledger_id = l.id
WHERE l.status = 'ACTIVE';
-- → Nested loop: mỗi ledger (1000 rows) scan toàn bộ transactions (50M rows)

-- ✅ Thêm index
CREATE INDEX idx_tx_ledger_id ON transactions(ledger_id);
-- → Hash join hoặc Merge join, 1000 * log(50M) thay vì 1000 * 50M

Benchmark:

  • Without FK index: 45 seconds
  • With FK index: 200ms

Phần 5: Khi index không đủ — Partitioning & Caching

Sau khi thêm composite index, API latency giảm xuống 200ms. Nhưng một tháng sau, lại lên 5 giây. Data volume không đổi. Chuyện gì xảy ra?

Kịch bản: Data pattern thay đổi. Trước đây, 90% query là account_id cũ (active accounts). Tháng này, user bắt đầu query account_id cũ hơn (năm ngoái), mỗi account có 10,000 giao dịch thay vì 100.

Giải pháp khi index không đủ:

5.1 Range Partitioning theo thời gian

-- Partition by month
CREATE TABLE transactions_partitioned (
    LIKE transactions INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- Tạo partitions monthly
CREATE TABLE transactions_2026_01 PARTITION OF transactions_partitioned
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

Lợi ích: Partition pruning → chỉ scan đúng partition cần thiết. Query account cũ chỉ scan 1 partition (vài triệu rows) thay vì toàn bộ 500M.

5.2 Redis Cache cho hot accounts

@Cacheable(value = "transactions", key = "#accountId + '_' + #status")
public List<Transaction> getTransactions(String accountId, String status) {
    // Only hits database if cache miss
}

Chiến lược:

  • Cache top 1% active accounts (chiếm 80% traffic)
  • TTL 5 phút, probabilistic early refresh để tránh cache stampede
  • Cache warming sau mỗi deploy

5.3 Read replica cho analytics queries

Master (write) → Replication lag < 100ms → Read Replica (read)

Route reporting queries và historical search sang read replica. Master chỉ phục vụ writes và real-time queries.

Phần 6: Checklist thành thạo — 5 điều bạn phải giải thích được không cần notes

✅ 1. B-Tree Index Traversal

”B-Tree có depth 3-4 cho bảng 50M rows nhờ fan-out rộng. Mỗi lookup tốn 3-4 random I/Os thay vì 50M sequential I/Os. Leaf nodes chứa TIDs trỏ đến heap pages.”

✅ 2. Leftmost Prefix Rule

”Composite index (a, b, c) chỉ dùng được khi WHERE bắt đầu bằng a. Vì index được sort theo a trước, rồi đến b, rồi c. Nếu skip a, database không biết bắt đầu từ đâu trong cây.”

✅ 3. Covering Index Optimization

”Covering index chứa tất cả columns cần thiết trong leaf nodes. Query chỉ cần đọc index, không cần heap fetch. Benchmark: Index Only Scan nhanh hơn 10-100x nhưng tốn storage gấp 2-3x và chậm hơn trên writes.”

✅ 4. Đọc EXPLAIN ANALYZE

”Red flags: Seq Scan trên bảng lớn, rows estimated vs actual chênh lệch >10x, nested loop với millions of outer rows. Stale statistics → chạy ANALYZE.”

✅ 5. Selectivity và thứ tự cột

”Selectivity = unique values / total rows. Giá trị càng nhỏ càng selective. Đặt cột selective nhất (account_id) lên đầu composite index để thu hẹp search space ngay lập tức.”

✅ Bonus: 3 anti-patterns và fix

Anti-patternFix
Over-indexingChỉ index columns thực sự dùng trong WHERE/JOIN/ORDER BY
Function on indexed columnDùng functional index hoặc store normalized data
Missing FK indexCREATE INDEX ON transactions(ledger_id)

Kết luận: Từ 8 giây xuống 200ms

Hành trình tối ưu của X Bank:

  1. Diagnose: EXPLAIN ANALYZE cho thấy full table scan (50M rows)
  2. Fix immediate: Composite index (account_id, status, created_at DESC) → 200ms
  3. Covering index: Thêm INCLUDE columns → 100ms (Index Only Scan)
  4. Maintenance: ANALYZE nightly, REINDEX weekly → stable performance
  5. Scale: Partition by month + Redis cache + read replica → <50ms for hot data

Nguyên lý quan trọng nhất: Database không phải “black box”. Planner chỉ tốt bằng statistics. Index chỉ tốt bằng column order. Performance chỉ bền bằng maintenance.

Tài liệu tham khảo

Hãy kết nối

Nếu bạn quan tâm tới việc hợp tác, có câu hỏi về bài viết, hay chỉ đơn giản muốn chuyện trò về backend — cứ ping mình nhé.

GitHubGửi email