Tối Ưu Truy Vấn PostgreSQL 10M+ Bản Ghi: EXPLAIN ANALYZE, B-Tree Bloat & Index-Only Scan

Giải Mã Toàn Diện EXPLAIN (ANALYZE, BUFFERS, TIMING)

Khi truy vấn cơ sở dữ liệu trên bảng hàng chục triệu dòng bắt đầu có độ trễ từ vài trăm mili-giây lên đến hàng chục giây, nhiều kỹ sư thường vội vàng tạo thêm index mà không kiểm tra kế hoạch thực thi (Query Execution Plan). Điều này thường làm tình trạng tệ hơn do chi phí duy trì chỉ mục lúc ghi dữ liệu.

Để chẩn đoán chính xác nguyên nhân gốc rễ, lệnh bắt buộc phải chạy là:

sql(5 dòng)
Sao chép
EXPLAIN (ANALYZE, BUFFERS, TIMING, COSTS, VERBOSE)
SELECT customer_id, SUM(amount), status
FROM orders
WHERE created_at >= '2026-01-01' AND status = 'COMPLETED'
GROUP BY customer_id, status;

Cách Đọc Các Chỉ Số Buffers Quan Trọng

Trong output của EXPLAIN (BUFFERS), bạn cần tập trung vào dòng sau: Buffers: shared hit=45210 read=12403 dirtied=12 written=0

• shared hit: Số trang dữ liệu 8KB được đọc trực tiếp từ RAM (shared_buffers của PostgreSQL). Thời gian truy xuất tính bằng nano-giây.
• shared read: Số trang buộc phải đọc từ ổ cứng (hoặc OS Page Cache). Đây chính là nguyên nhân trực tiếp kéo dài thời gian phản hồi do I/O disk latency.
• Tỷ lệ Buffer Cache Hit Ratio: Mục tiêu vận hành của hệ thống OLTP luôn là giữ tỷ lệ này trên 99%: $$\text{Hit Ratio} = \frac{\text{shared hit}}{\text{shared hit} + \text{shared read}} \times 100%$$

Cơ Chế Visibility Map & Chiến Lược Đạt Index-Only Scan

Một ngộ nhận phổ biến là khi truy vấn đã dùng Index (Index Scan), database sẽ không cần đọc bảng dữ liệu nữa. Thực tế:

code(2 dòng)
Sao chép
Index Scan  -->  1. Duyệt B-Tree Index tìm Tuple ID (TID)
                 2. Đọc Heap Page tương ứng để kiểm tra Transaction Visibility (MVCC)

Trong PostgreSQL, B-Tree index không lưu thông tin phiên bản giao dịch (xmin, xmax). Do đó, mỗi bản ghi tìm thấy trong Index vẫn buộc database phải nhảy sang Heap page để kiểm tra xem dòng đó có bị xóa hoặc cập nhật bởi giao dịch khác hay không.

Bước Nhảy Lượng Tử: Index-Only Scan

Để triệt tiêu hoàn toàn bước đọc Heap (giảm 80-95% I/O), ta cần hai điều kiện:

1. Tất cả các cột xuất hiện trong mệnh đề SELECT, WHERE, ORDER BY đều phải nằm trọn trong Index.
2. Trang Heap chứa dòng dữ liệu đã được tiến trình autovacuum đánh dấu là All-Visible trong Visibility Map.

sql(5 dòng)
Sao chép
-- Tạo Covering Index sử dụng mệnh đề INCLUDE
-- Cột 'created_at' và 'status' dùng để lọc và sắp xếp trong cây B-Tree
-- Cột 'customer_id' và 'amount' được lưu kèm ở lá (Payload) không làm tăng độ sâu cây index
CREATE INDEX idx_orders_covering ON orders (created_at, status) 
INCLUDE (customer_id, amount);

Vấn Nạn B-Tree Index Bloat & Giải Pháp HOT Updates

Khi bảng có tần suất UPDATE cao, kiến trúc MVCC của PostgreSQL sẽ không ghi đè dữ liệu cũ mà chèn một tuple phiên bản mới và đánh dấu tuple cũ là dead. Hệ quả là mỗi lần update một cột bất kỳ, tất cả các chỉ mục của bảng đó đều phải thêm một con trỏ trỏ tới tuple mới. Cây B-Tree sẽ phình to gấp 3-5 lần dung lượng thực tế (Index Bloat).

Tận Dụng Heap-Only Tuples (HOT)

HOT cho phép tuple mới được đặt cùng một Data Page 8KB với tuple cũ, miễn là trang đó còn chỗ trống và cột được update không nằm trong bất kỳ Index nào. Khi đó, Index không cần tạo con trỏ mới mà đi qua chuỗi liên kết nội bộ trong trang.

Để kích hoạt HOT updates hiệu quả:

sql(6 dòng)
Sao chép
-- Mặc định fillfactor là 100 (lấp đầy trang).
-- Giảm fillfactor xuống 85 để dành 15% diện tích trang cho các bản ghi update sau này.
ALTER TABLE orders SET (fillfactor = 85);

-- Chạy VACUUM FULL hoặc REINDEX không khóa bảng
REINDEX TABLE CONCURRENTLY orders;

Kịch Bản Đo Kiểm Thực Nghiệm (10,000,000 Rows)

sql(6 dòng)
Sao chép
-- Query mẫu: Thống kê doanh thu theo khách hàng trong quý 1
SELECT customer_id, SUM(amount)
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31'
  AND status = 'COMPLETED'
GROUP BY customer_id;

Bảng So Sánh Chỉ Số Kế Hoạch Thực Thi

Bài Học Vận Hành

Tối ưu hóa cơ sở dữ liệu không phải là một bài toán cảm tính. Bằng việc phối hợp giữa Covering Index (INCLUDE), bảo trì Visibility Map qua Autovacuum chuẩn xác và cấu hình Fillfactor hợp lý, thời gian xử lý truy vấn đã giảm từ gần 5 giây xuống vỏn vẹn 18 mili-giây mà không cần tốn ngân sách nâng cấp RAM máy chủ.