Chỉ mục & Kế hoạch truy vấn: Chọn đường truy cập dựa trên bằng chứng

12 giờ đêm ngày hội mua sắm Mega Sale, kênh trực chiến sự cố réo chuông liên hồi: CPU database chạm ngưỡng 100%, IOPS đọc đĩa quá tải, và thời gian phản hồi của API vọt từ 15 mili-giây lên 50 giây. Kỹ sư trực ca vội vã kiểm tra và tự tin khẳng định: "Hệ thống đã có chỉ mục rồi mà, hôm qua em vừa tạo CREATE INDEX idx_orders_status_created ON orders (status, created_at);!". Nhưng dấu vết câu lệnh truy vấn khẩn cấp đã lột trần sự cố tai hại: câu lệnh lấy danh sách đơn hàng của người dùng chỉ lọc theo WHERE created_at >= NOW() - INTERVAL '1 day', hoàn toàn bỏ qua cột đứng đầu status. Vi phạm quy tắc tiền tố ngoài cùng bên trái (leftmost prefix rule) của composite B-tree index khiến bộ lập kế hoạch tối ưu chi phí của PostgreSQL bỏ qua chỉ mục hoàn toàn và chuyển sang quét tuần tự (Seq Scan) toàn bộ 80 triệu dòng dữ liệu trên đĩa. Tai hại hơn, một kỹ sư khác vừa đưa lên tính năng tìm kiếm bằng WHERE email LIKE '%@gmail.com'; ký tự đại diện % đứng đầu đã vô hiệu hóa hoàn toàn cơ chế duyệt nhị phân của B-tree, kích hoạt thêm một đợt quét toàn bảng cho mỗi ký tự người dùng gõ vào, bóp nghẹt I/O và đánh sập hoàn toàn database.

Những sự cố này phơi bày một sự thật cốt tử: thêm index mà không thấu hiểu execution plan và access path thực chất chỉ là hành động phỏng đoán mù quáng khi đối mặt với áp lực production.

Tóm tắt

💡 Quy tắc bỏ túi: Chỉ mục không phải cây đũa thần tự động tăng tốc; nó chỉ cung cấp thêm một đường truy cập (access path). Bộ lập kế hoạch dựa trên chi phí sẽ chọn quét tuần tự (Seq Scan) bất cứ khi nào việc đọc trực tiếp trang dữ liệu bảng được ước lượng là rẻ hơn. Hãy thiết kế chỉ mục tuân thủ chặt chẽ leftmost prefix, kiểm tra độ chọn lọc (selectivity) của predicate, và luôn dùng EXPLAIN (ANALYZE, BUFFERS) để đo lường công việc thực tế trước và sau mỗi lần thay đổi schema.

• Chỉ mục cung cấp đường truy cập, không bảo đảm tốc độ tuyệt đối: Index đơn thuần là một tuyến đường di chuyển thay thế. Bộ lập kế hoạch truy vấn (query planner) sẽ đánh giá các đường truy cập dựa trên thống kê bảng và độ chọn lọc, sẵn sàng chọn Seq Scan nếu tập dữ liệu cần đọc chiếm tỷ lệ lớn trong bảng.
• Chỉ mục ghép B-tree đòi hỏi tuân thủ leftmost prefix: Một composite index trên (status, created_at) chỉ có thể thu hẹp không gian tìm kiếm nếu các cột khóa dẫn đầu được ràng buộc. Việc chỉ lọc theo created_at hoặc dùng biểu thức làm mất tính sargable (như LIKE '%text' hay DATE(created_at)) sẽ ngăn cản planner duyệt cây B-tree.
• Phân biệt rõ ràng cơ chế các kiểu quét vật lý: Hiểu bản chất chi phí giữa Seq Scan (đọc thô các block heap trên đĩa), Index Scan (duyệt lá B-tree rồi lấy từng tuple từ heap), Index Only Scan (trả dữ liệu trực tiếp từ trang index khi visibility map cho phép mà không cần chạm vào heap), và Bitmap Index Scan (gom con trỏ tuple rồi gom cụm đọc heap theo lô).
• Kiểm toán độ lệch ước lượng số hàng (cardinality estimate): Trong EXPLAIN ANALYZE, khoảng cách giữa số hàng ước lượng (estimated rows) và số hàng thực tế (actual rows) là tín hiệu chẩn đoán quan trọng nhất. Nếu planner dự đoán 10 hàng nhưng thực tế phải xử lý 500.000 hàng, nó sẽ chọn các thuật toán join lồng nhau hoặc sắp xếp tràn bộ nhớ đầy thảm họa.
• Cạm bẫy chết người: Tạo composite index (status, created_at) nhưng truy vấn chỉ tìm kiếm theo created_at hoặc dùng LIKE '%text', lầm tưởng index sẽ tự động tăng tốc, khiến database bỏ qua index và quét toàn bộ bảng (Seq Scan) làm sập hệ thống giữa giờ cao điểm.

Ba thói quen vận hành quan trọng:

1. thiết kế chỉ mục theo predicate, thứ tự sắp xếp, giới hạn và cột trả về của truy vấn thật;
2. đọc kế hoạch được chọn thay vì giả định chỉ mục chắc chắn được dùng;
3. so sánh số hàng ước lượng với số hàng thực tế trước khi đổi schema hoặc cấu hình planner.

Một mô hình tư duy: chỉ mục là tuyến đường, kế hoạch là tuyến được chọn

Quét tuần tự, quét chỉ mục, đường bitmap hay quét chỉ dùng chỉ mục không phải nhãn “tốt” hoặc “xấu”. Chúng là những cách khác nhau để thực hiện cùng một yêu cầu quan hệ.

Vì sao quét tuần tự có thể là kế hoạch đúng

Giả sử orders có hàng triệu hàng và phần lớn có status = 'completed'.

SELECT id, customer_id, total_cents
FROM orders
WHERE status = 'completed';

Có chỉ mục trên status không có nghĩa planner phải dùng nó. Nếu predicate trả về phần lớn bảng, Seq Scan có thể rẻ hơn vì bộ thực thi dù sao cũng phải chạm vào rất nhiều dữ liệu bảng.

So sánh với lookup hẹp:

SELECT id, customer_id, total_cents
FROM orders
WHERE id = 918273;

B-tree trên một định danh duy nhất là ứng viên tự nhiên vì lookup có độ chọn lọc cao.

Thay vì hỏi “vì sao PostgreSQL bỏ qua chỉ mục của tôi?”, hãy bắt đầu bằng câu hỏi:

Với số hàng được ước lượng và chi phí lấy chúng, vì sao planner ưu tiên đường truy cập này?

B-tree: thu hẹp không gian tìm kiếm rồi lấy hàng

B-tree là kiểu chỉ mục mặc định của PostgreSQL và hỗ trợ các so sánh equality/range phổ biến. Về mặt mô hình tư duy, chỉ mục giữ các khóa theo thứ tự để bộ thực thi có thể thu hẹp phần chỉ mục cần đọc.

Sơ đồ này là mô hình tư duy, không phải mô tả vật lý chính xác của page layout. Ở mức sâu hơn còn có page split, fill factor, concurrency và các chi tiết lưu trữ khác.

Chỉ mục ghép mã hóa thứ tự hữu ích

Với truy vấn:

SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

chỉ mục sau khớp khá tốt với shape của truy vấn:

CREATE INDEX orders_open_feed_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);

Truy vấn và chỉ mục trên khớp ở bốn điểm:

• tenant_id thu hẹp về một tenant;
• status thu hẹp tiếp trong tenant đó;
• created_at DESC phù hợp thứ tự cần trả;
• LIMIT 50 cho phép dừng sớm khi đã đủ kết quả.

Các cột trong INCLUDE là payload, không phải khóa tìm kiếm. Chúng có thể giúp chỉ mục cover truy vấn nhưng cũng làm chỉ mục lớn hơn.

Đọc kế hoạch như một giả thuyết về lượng công việc

Dùng EXPLAIN trước khi bạn chỉ muốn kế hoạch ước lượng:

EXPLAIN
SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Một kế hoạch đơn giản hóa có thể trông như sau:

Limit
  -> Index Scan using orders_open_feed_idx on orders
       Index Cond: ((tenant_id = 42) AND (status = 'open'))

Tên node, cost, estimate và chiến lược được chọn phụ thuộc vào phiên bản PostgreSQL, thống kê, dữ liệu, cấu hình và shape truy vấn. Không nên dạy một plan minh họa như output chắc chắn.

Các dạng scan nên nhận biết:

EXPLAIN ANALYZE đổi câu hỏi

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

ANALYZE thực thi câu lệnh thật rồi báo quan sát runtime. Với SELECT, đây thường là thứ cần khi điều tra một truy vấn đại diện. Với câu lệnh có side effect, nhớ rằng EXPLAIN ANALYZE cũng thực thi side effect đó; hãy dùng môi trường an toàn hoặc transaction/rollback phù hợp.

BUFFERS giúp phân biệt công việc được thỏa từ shared buffers và công việc cần thêm đọc dữ liệu, nhưng một lần chạy vẫn chỉ là một mẫu. Cache state, tải đồng thời, parameter và phân bố dữ liệu đều có thể làm kết quả khác đi.

So sánh estimated rows với actual rows thường là bước có giá trị cao nhất

Khi estimate và actual rows lệch mạnh, hãy điều tra trước khi thêm chỉ mục ngẫu nhiên:

• thống kê có còn mới sau một đợt tăng/đổi dữ liệu lớn không?
• giá trị có skew mạnh thay vì phân bố đều không?
• hai cột có tương quan mà thống kê đơn cột không mô tả tốt không?
• truy vấn có function/cast khác với expression đã được đánh chỉ mục không?
• phân bố parameter ở production có khác giá trị bạn đang thử không?

Mục tiêu đầu tiên là giải thích estimate. Sau đó mới quyết định sửa ở chỉ mục, thống kê, query shape hay mô hình dữ liệu.

Index-only scan là khả năng, không phải bảo đảm

Một covering index có thể chứa đủ các cột truy vấn cần. Trong PostgreSQL, điều đó có thể cho phép Index Only Scan, nhưng executor vẫn có thể phải ghé heap để xác nhận visibility. Vì vậy:

• “mọi cột SELECT đều có trong index” không đồng nghĩa zero heap fetch;
• covering index rộng hơn làm tăng storage và write work;
• chỉ nên thêm payload columns khi việc giảm heap visit có ý nghĩa với workload thật.

Vì vậy ví dụ trước dùng INCLUDE (id, total_cents) như một khả năng tối ưu, không phải mặc định chung.

Kịch bản production: endpoint chậm đi sau khi dữ liệu tăng

Một dashboard đơn hàng multi-tenant chạy nhanh lúc mới ra mắt. Vài tháng sau, endpoint GET /orders?status=open trở thành một trong các read có latency cao nhất khi những tenant lớn tích lũy nhiều lịch sử hơn.

Truy vấn:

SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Schema có các chỉ mục riêng trên tenant_id, status và created_at, nhưng chưa có chỉ mục khớp với predicate kết hợp và ordering.

Hậu quả: tenant lớn thấy dashboard chậm và database I/O tăng mạnh vào giờ cao điểm.

Nguyên nhân cốt lõi: team coi “các cột filter đã có index” tương đương “truy vấn có đường truy cập hiệu quả”. Planner vẫn phải kết hợp predicate, lấy hàng và thỏa ordering. Nhiều single-column index không tự động tạo cùng ordered path như một composite index phù hợp.

Cách khắc phục chuẩn: lấy một EXPLAIN (ANALYZE, BUFFERS) đại diện trong môi trường an toàn, xem estimate cùng scan/sort work, rồi thử một composite index khớp tenant, status và recency. Chạy plan lại và so sánh lượng công việc. Chỉ giữ index nếu lợi ích read xứng với storage và write cost.

Thay đổi quan trọng không phải “hãy thêm đúng index này”, mà là chuyển workflow từ đoán index sang query shape → plan → giả thuyết → thay đổi có đo lường → đọc plan lại.

Những bẫy indexing thường gặp

Mỗi cột filter một index

Nhiều single-column index đôi khi có thể kết hợp qua bitmap, nhưng không tương đương với composite B-tree có ordering khớp một truy vấn thường xuyên. So sánh plan thật thay vì đếm số index.

Mặc định index một flag có độ chọn lọc thấp

Cột boolean hoặc status có thể hữu ích trong composite/partial index, nhưng một standalone index trên giá trị xuất hiện ở phần lớn hàng có thể giúp rất ít cho read rộng.

Che giá trị được index phía sau expression khác

Nếu query áp function, cast hoặc expression, chỉ mục thường trên cột gốc có thể không hỗ trợ predicate như mong đợi. PostgreSQL có expression index, nhưng nó cũng phải bám một query pattern thật và mang chi phí bảo trì riêng.

Coi estimate cũ là vấn đề index

Planner phụ thuộc thống kê. Sau thay đổi dữ liệu lớn, thông tin từ ANALYZE rất quan trọng. Estimate sai có thể dẫn tới plan tệ ngay cả khi index phù hợp đã tồn tại.

Giữ mọi index mãi mãi

Mỗi index thêm storage và write/maintenance work. Index bảo vệ một read path quan trọng có thể đáng giá; index suy đoán nhưng không dùng thì không miễn phí.

Bài tập

Bạn có truy vấn:

SELECT id, occurred_at, payload
FROM audit_events
WHERE account_id = 77
  AND event_type = 'login_failed'
  AND occurred_at >= now() - interval '7 days'
ORDER BY occurred_at DESC
LIMIT 100;

Ba ứng viên:

-- A
CREATE INDEX audit_events_account_idx
ON audit_events (account_id);

-- B
CREATE INDEX audit_events_feed_idx
ON audit_events (account_id, event_type, occurred_at DESC);

-- C
CREATE INDEX audit_events_time_idx
ON audit_events (occurred_at DESC, account_id, event_type);

Trước khi mở phần giải thích, hãy dự đoán index nào là giả thuyết khởi đầu tốt nhất cho query shape này. Sau đó nêu một lý do planner vẫn có thể chọn plan khác trong database thật.

Checklist review query plan

• Query shape: Tôi đã ghi rõ predicate, ordering, limit và các cột trả về quan trọng chưa?
• Độ chọn lọc: Predicate nào thực sự thu hẹp tập hàng với giá trị production?
• Thứ tự khóa: Composite index có đặt equality constraints ổn định trước phần range/order của workload không?
• Plan: Tôi đã xem access path được chọn thay vì suy từ schema chưa?
• Estimate: Estimated rows có gần actual rows với parameter đại diện không?
• Sort: Executor có đang sort một intermediate result lớn mà index order có thể tránh không?
• Heap work: Covering/index-only có loại bỏ đủ row fetch để đáng làm index rộng hơn không?
• Write cost: Index này thêm chi phí insert/update/delete và storage bao nhiêu?
• Đo lại: Sau khi đổi index hoặc query, tôi có lấy plan lại và so sánh lượng công việc thay vì chỉ nhìn wall-clock time không?

Quy tắc cho agent

Khi được yêu cầu tối ưu một truy vấn SQL, không đề xuất chỉ mục chỉ từ tên cột. Trước tiên phải lấy query shape, các index đang có, parameter đại diện và một EXPLAIN plan. Ưu tiên thay đổi schema nhỏ nhất tạo ra access path tốt hơn rõ ràng, rồi xác minh estimate và runtime work sau thay đổi.

Khái niệm liên quan

• Relational Data Model — shape của bảng và quan hệ quyết định các access pattern có thể có.
• SQL Querying — predicate, join, ordering, aggregation và projection xác định công việc planner phải thỏa.
• Database Transactions — index tham gia write path nên ảnh hưởng chi phí ghi transactional.
• Transaction Isolation — visibility là một lý do index-only path vẫn có thể phải ghé heap.
• Backend Request Lifecycle — thời gian database chỉ là một phần của end-to-end request latency.

Dùng lộ trình Backend Systems để đặt indexing/query plans trước transactions, isolation, partial failure và các chủ đề reliability.

Nguồn

Các tài liệu PostgreSQL chính thức được xác minh ngày 2026-09-10:

• PostgreSQL documentation — Indexes
• PostgreSQL documentation — Multicolumn Indexes
• PostgreSQL documentation — Index-Only Scans and Covering Indexes
• PostgreSQL documentation — Using EXPLAIN
• PostgreSQL documentation — EXPLAIN
• PostgreSQL documentation — ANALYZE

Bài này được phân loại evolving với chu kỳ review 180 ngày vì hành vi planner, các tính năng plan và chi tiết theo phiên bản database vẫn tiếp tục thay đổi dù mental model cốt lõi về indexing khá bền vững.