Posted inDatabase

Hiệu năng PostgreSQL: Tăng tốc truy vấn với Covering Index

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Chi phí ẩn của Heap

Sáu tháng trước, đội ngũ của tôi đã gặp phải một rào cản về khả năng mở rộng. Microservice kiểm tra quyền hạn của chúng tôi gặp khó khăn dưới tải trọng nặng. Ngay cả khi đã có index B-tree tiêu chuẩn trên user_id, mức sử dụng CPU của cơ sở dữ liệu vẫn duy trì ở mức 80% và thời gian chờ I/O tăng vọt. Truy vấn rất cơ bản: SELECT status FROM user_permissions WHERE user_id = $1;.

PostgreSQL sử dụng hệ thống lưu trữ dựa trên heap (heap-based storage). Không giống như InnoDB của MySQL vốn có thể lưu trữ dữ liệu dòng trực tiếp trong index khóa chính, Postgres lưu trữ dữ liệu trong một vùng riêng biệt gọi là “Heap”. Khi bạn chạy một truy vấn, cơ sở dữ liệu sẽ tìm mục nhập trong index và sau đó thực hiện thêm một bước nhảy tới heap để lấy giá trị thực tế của cột. Bước nhảy này là một thao tác I/O ngẫu nhiên. Với 10.000 truy vấn mỗi giây, những bước nhảy nhỏ này đã làm tê liệt thông lượng của chúng tôi.

Chúng tôi đã giải quyết vấn đề này bằng cách triển khai Covering Indexes. Kỹ thuật này đã loại bỏ hoàn toàn việc truy cập vào heap, giúp giảm độ trễ truy vấn trung bình từ 12ms xuống dưới 0,5ms. Dưới đây là cách bạn có thể làm điều tương tự.

Triển khai Covering Index

Một Index-Only Scan xảy ra khi bản thân index chứa mọi phần dữ liệu mà truy vấn cần. Kể từ PostgreSQL 11, mệnh đề INCLUDE giúp việc này trở nên đơn giản.

1. Thiết lập

Xét một bảng theo dõi hàng triệu hành động của người dùng:

CREATE TABLE user_activity (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    activity_type TEXT,
    created_at TIMESTAMP DEFAULT now()
);

2. Index tiêu chuẩn

Một index điển hình trên user_id sẽ trông như thế này:

CREATE INDEX idx_user_id ON user_activity(user_id);

-- Kiểm tra kế hoạch thực thi
EXPLAIN ANALYZE 
SELECT created_at FROM user_activity WHERE user_id = 500;

Kiểm tra kế hoạch thực thi của bạn. Bạn có khả năng sẽ thấy một Index Scan. Dù nhanh, nhưng cơ sở dữ liệu vẫn phải truy cập vào heap để lấy mốc thời gian created_at.

3. Covering Index

Chúng ta có thể “bao phủ” (cover) truy vấn bằng cách đính kèm cột dữ liệu vào index:

CREATE INDEX idx_user_id_covering 
ON user_activity(user_id) 
INCLUDE (created_at);

Chạy lại EXPLAIN ANALYZE. Kế hoạch bây giờ sẽ hiển thị Index Only Scan. Cơ sở dữ liệu đã lấy mốc thời gian trực tiếp từ tệp index, bỏ qua hoàn toàn các tệp dữ liệu của bảng.

Vai trò của Visibility Map

Tại sao không phải mọi index-only scan đều hoạt động hoàn hảo? Câu trả lời nằm ở khả năng hiển thị của dòng (row visibility). Các index trong PostgreSQL không lưu trữ thông tin về transaction. Cơ sở dữ liệu thường phải kiểm tra heap để đảm bảo một dòng có thể hiển thị đối với transaction hiện tại của bạn và không phải là một phiên bản cũ đang chờ xóa.

Để bỏ qua bước kiểm tra này, Postgres sử dụng một Visibility Map. Tập hợp bit nhỏ này theo dõi các trang dữ liệu nơi tất cả các dòng được xác nhận là hiển thị với tất cả các transaction đang hoạt động. Nếu bản đồ xác nhận một trang là “sạch”, Index-Only Scan sẽ thành công mà không cần truy cập heap. Tuy nhiên, nếu trang vừa được cập nhật gần đây, cơ sở dữ liệu vẫn phải kiểm tra heap. Điều này làm cho hiệu suất của VACUUM trở nên cực kỳ quan trọng để duy trì tốc độ cao cho các index.

INCLUDE so với Index đa cột

Bạn có thể thắc mắc tại sao chúng ta không sử dụng một index tổng hợp (composite index) tiêu chuẩn như (user_id, created_at). Có hai lý do chính:

  • Mục đích: Các cột trong phần chính của index được sử dụng để lọc và sắp xếp. Các cột trong mệnh đề INCLUDE chỉ là “payload” (dữ liệu đi kèm). Chúng nằm ở các nút lá nhưng không ảnh hưởng đến logic tìm kiếm của B-tree.
  • Bảo trì: Các cột đi kèm không yêu cầu cùng một mức chi phí tài nguyên khi tái cân bằng index. Điều này giúp index hiệu quả hơn một chút khi ghi dữ liệu, đồng thời báo hiệu rõ ràng cho các nhà phát triển khác biết cột nào ở đó vì mục đích hiệu năng thay vì mục đích logic.

Chiến lược nâng cao: Partial Covering Indexes

Kết hợp covering index với logic một phần (partial logic) cực kỳ hiệu quả cho các hệ thống có lưu lượng lớn. Điều này lý tưởng cho các mô hình dữ liệu “Hoạt động” nơi bạn chỉ quan tâm đến một tập hợp con các dòng.

Nếu bạn thường xuyên truy vấn total_amount của các đơn hàng “Đang chờ” (Pending), đừng index toàn bộ bảng. Hãy sử dụng một index có mục tiêu:

CREATE INDEX idx_pending_orders_amount 
ON orders(customer_id) 
INCLUDE (total_amount) 
WHERE status = 'PENDING';

Index này sẽ luôn nhỏ và nhanh. Nó cung cấp kết quả gần như tức thì cho dashboard của bạn mà không làm phình dung lượng lưu trữ hoặc làm chậm thao tác chèn cho các đơn hàng đã hoàn tất.

Bài học kinh nghiệm từ thực tế sản xuất

Việc chuyển hệ thống phân quyền của chúng tôi sang mô hình này đã dạy cho chúng tôi một số bài học thực tế về bảo trì cơ sở dữ liệu.

1. Theo dõi Vacuum

Index-Only Scans sống còn nhờ vào Visibility Map. Nếu bảng của bạn xử lý lưu lượng UPDATE lớn và autovacuum quá chậm, hiệu năng của bạn sẽ giảm sút. Hãy giám sát bộ đếm “Heap Fetches” trong kế hoạch EXPLAIN của bạn. Nếu nó cao, Visibility Map của bạn đã cũ và bạn đang mất đi lợi ích của covering index.

2. Tránh phình Index (Index Bloat)

Mỗi cột thêm vào trong mệnh đề INCLUDE sẽ làm tăng kích thước index. Trong một thử nghiệm của chúng tôi, việc thêm một cột VARCHAR lớn đã làm tăng kích thước index thêm 60%. Index lớn hơn tiêu thụ nhiều shared_buffers (RAM) hơn và làm chậm quá trình sao lưu. Chỉ nên bao gồm các cột xuất hiện trong các truy vấn thường xuyên nhất và nhạy cảm nhất về độ trễ.

3. Đo lường thành công

Sử dụng truy vấn này để xác minh xem các index của bạn có thực sự thực hiện Index-Only Scans trong thực tế hay không:

SELECT 
    schemaname, relname, indexrelname, 
    idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan > 0 
ORDER BY idx_scan DESC;

Một covering index khỏe mạnh sẽ hiển thị số lượng idx_tup_read cao nhưng số lượng idx_tup_fetch rất thấp. Điều này cho thấy dữ liệu đến từ index chứ không phải từ bảng.

4. Giữ cho Index tinh gọn

Đừng bao giờ bao gồm các trường JSONB hoặc TEXT lớn trong một covering index. Index nên đủ gọn nhẹ để nằm trong bộ nhớ. Nếu một index trở nên quá lớn để vừa với bộ nhớ đệm (cache), bạn sẽ đánh đổi vấn đề I/O này lấy một vấn đề I/O khác. Hãy bám sát các kiểu dữ liệu có độ rộng cố định và nhỏ như integer, timestamp hoặc enum ngắn.

Hãy coi index không chỉ là một công cụ tìm kiếm. Chúng là một cách chiến lược để lưu trữ dữ liệu cho các luồng truy vấn quan trọng nhất của bạn. Bằng cách cắt giảm việc truy cập heap, bạn có thể mở rộng thực thể PostgreSQL của mình xa hơn nhiều trên cùng một phần cứng.

Share:
Tags: