Tối ưu câu truy vấn SQL chậm và phân tích execution plan PostgreSQL

Khi một API hoặc báo cáo bắt đầu phản hồi chậm theo quy mô dữ liệu, phản xạ phổ biến của nhiều đội phát triển là lập tức thêm chỉ mục (index) vào cột xuất hiện trong mệnh đề lọc hoặc nâng cấp cấu hình máy chủ cơ sở dữ liệu. Cách làm này đôi khi giảm triệu chứng tức thời nhưng thường che khuất nguyên nhân gốc và làm tăng chi phí ghi trên toàn hệ thống.

Tối ưu câu truy vấn SQL chậm bền vững bắt đầu từ việc đo lường hiện trạng (baseline), đọc đúng kế hoạch thực thi (execution plan) của bộ tối ưu hóa truy vấn và đối chiếu với đặc thù tải (workload) thực tế.

Xác định đúng nút thắt trước khi sửa câu truy vấn

Một câu truy vấn chạy lâu trên môi trường vận hành không đồng nghĩa với việc bản thân thuật toán truy vấn đó kém hiệu quả. Trước khi can thiệp vào mã SQL hoặc schema, cần phân tách ba nhóm nguyên nhân:

• Đọc thừa dữ liệu (I/O-bound và CPU-bound): Truy vấn quét hàng trăm nghìn dòng chỉ để lọc lấy vài chục bản ghi hiển thị trên một trang, hoặc kéo toàn bộ các cột văn bản lớn vào bộ nhớ dù giao diện chỉ cần định danh và tiêu đề.
• Tranh chấp khóa (Lock contention): Câu truy vấn thực chất rất nhẹ nhưng phải xếp hàng chờ một giao dịch khác đang giữ khóa trên cùng tập dòng hoặc cùng bảng. Nếu chỉ nhìn vào tổng thời gian phản hồi từ phía ứng dụng, đội phát triển dễ nhầm lẫn giữa truy vấn chậm và truy vấn bị chặn.
• Truy vấn lặp ở tầng ứng dụng (N+1 queries): Một luồng nghiệp vụ gọi hàng trăm câu truy vấn nhỏ nối tiếp nhau qua mạng nội bộ. Mỗi câu truy vấn riêng lẻ chỉ mất vài mili-giây nhưng tổng độ trễ tích lũy làm nghẽn kết nối.

Để tránh tối ưu nhầm chỗ, bước đầu tiên trong quy trình tối ưu hóa cơ sở dữ liệu là thu thập thống kê truy vấn tổng hợp (ví dụ qua pg_stat_statements trên PostgreSQL) kết hợp với nhật ký truy vấn chậm để biết truy vấn nào tiêu tốn tổng thời gian CPU và I/O lớn nhất theo toàn bộ chu kỳ vận hành.

Phân tích execution plan PostgreSQL dựa trên số đo thực tế

Khi đã khoanh vùng được truy vấn mục tiêu, việc chỉ chạy lệnh EXPLAIN thông thường mới cho biết kế hoạch dự kiến mà bộ lập kế hoạch (planner) ước tính dựa trên thống kê sẵn có. Để có bằng chứng xác thực, cần đối chiếu ước tính đó với kết quả thực thi thật qua EXPLAIN (ANALYZE, BUFFERS).

Khi đọc cây kế hoạch thực thi từ nút lá lên nút gốc, cần tập trung vào bốn tín hiệu định lượng:

• Độ lệch giữa dòng ước lượng (rows) và dòng thực tế (actual rows): Khi planner ước lượng một bước chỉ trả về 10 dòng nhưng thực tế trả về 50.000 dòng, bộ tối ưu có thể đã chọn thuật toán nối bảng Nested Loop thay vì Hash Join hoặc Merge Join. Nguyên nhân thường nằm ở thống kê cột bị cũ, điều kiện lọc phụ thuộc chéo giữa nhiều cột hoặc biểu thức hàm bọc quanh cột làm mất khả năng ước lượng.
• Tỷ lệ dòng bị loại bỏ sau khi đọc (Rows Removed by Filter): Nếu một bước Seq Scan hoặc Index Scan phải đọc từ đĩa và bộ nhớ đệm 200.000 dòng rồi loại bỏ 199.950 dòng qua bộ lọc phụ, chỉ mục hiện tại chưa bao phủ đúng điều kiện truy vấn.
• Chi phí bộ nhớ đệm và đọc đĩa (Buffers: shared hit và shared read): Số lượng trang dữ liệu (mỗi trang mặc định 8KB trên PostgreSQL) cho biết mức độ thực sự mà truy vấn tác động lên hệ thống lưu trữ. Một truy vấn trả kết quả trong 30 mili-giây nhờ dữ liệu đang nằm sẵn trong RAM (shared hit) nhưng chạm tới hàng chục nghìn trang vẫn sẽ gây suy giảm hiệu năng nghiêm trọng khi bộ nhớ đệm bị đẩy ra ngoài ở giờ cao điểm.
• Tràn bộ nhớ tạm khi sắp xếp hoặc băm (Sort Method: external merge Disk hoặc Batches > 1): Khi vùng nhớ làm việc (work_mem) không đủ chứa tập dữ liệu trung gian, cơ sở dữ liệu phải ghi tạm xuống đĩa, làm thời gian phản hồi tăng đột biến và dao động mạnh.

Lưu ý quan trọng khi kiểm chứng: lệnh có kèm ANALYZE sẽ thực sự chạy câu truy vấn đó. Với các câu lệnh UPDATE, DELETE hoặc INSERT, bắt buộc phải bọc trong giao dịch thử nghiệm có ROLLBACK trên môi trường tách biệt hoặc khung giờ đã được phê duyệt để không làm biến đổi dữ liệu thật.

Thiết kế chỉ mục có chọn lọc và đánh đổi đọc/ghi

Chỉ mục không phải là tài nguyên miễn phí. Mỗi chỉ mục thêm vào bảng làm tăng dung lượng lưu trữ, chiếm thêm bộ nhớ đệm dùng chung và buộc mọi thao tác INSERT, DELETE hoặc UPDATE chạm vào cột được đánh chỉ mục phải cập nhật thêm cấu trúc cây chỉ mục.

Khi thiết kế hoặc tái cấu trúc chỉ mục, cần kiểm tra các khía cạnh sau:

• Thứ tự cột trong chỉ mục phức hợp (Composite B-Tree Index): Đặt các cột so sánh bằng (=) lên trước các cột lọc theo khoảng (>, <, BETWEEN) hoặc sắp xếp (ORDER BY) để thu hẹp phạm vi duyệt cây chỉ mục ngay từ các tầng đầu.
• Chỉ mục một phần (Partial Index): Nếu ứng dụng thường xuyên truy vấn một tập con cố định — chẳng hạn các đơn hàng đang ở trạng thái chờ xử lý hoặc các bản ghi chưa bị xóa mềm — việc tạo chỉ mục có mệnh đề WHERE đi kèm giúp giảm đáng kể kích thước chỉ mục và giảm chi phí bảo trì khi ghi.
• Chỉ mục bao phủ (Covering Index với INCLUDE): Với các truy vấn đọc tần suất rất cao trên bảng lớn, việc đính kèm các cột cần lấy vào phần tải của chỉ mục giúp PostgreSQL thực hiện Index Only Scan khi trang dữ liệu đã được đánh dấu sạch trong Visibility Map, tránh bước nhảy ngẫu nhiên sang bảng chính (Heap Fetch).
• Dọn dẹp chỉ mục dư thừa hoặc trùng lặp: Trước khi thêm chỉ mục mới, cần rà soát các chỉ mục hiện có chưa từng được sử dụng hoặc bị trùng tiền tố với chỉ mục mới để gỡ bỏ có kiểm soát.

Kiểm chứng trước và sau khi thay đổi trên môi trường vận hành

Mọi đề xuất sửa câu truy vấn, thêm chỉ mục hoặc điều chỉnh cấu hình đều phải đi kèm phương án kiểm chứng độc lập và điều kiện quay lui (rollback):

• Chụp lại kế hoạch thực thi và số lượng shared hit/read trước và sau khi thay đổi trên cùng tập dữ liệu đại diện.
• Tạo chỉ mục trên bảng đang phục vụ người dùng bằng cơ chế không khóa ghi (như CREATE INDEX CONCURRENTLY) và theo dõi tiến trình hoàn tất, đảm bảo không để lại chỉ mục ở trạng thái INVALID nếu quá trình tạo bị ngắt.
• Quan sát biểu đồ độ trễ phân vị (p95, p99), tải CPU và tốc độ phát sinh WAL sau khi triển khai để xác nhận thay đổi không gây hồi quy cho các luồng ghi liên quan.

Nếu hệ thống của bạn đang gặp hiện tượng truy vấn chậm, đồng thời phát sinh chờ khóa giữa các giao dịch, hãy xem thêm bài viết về xử lý deadlock PostgreSQL và giảm lock wait cùng checklist sẵn sàng production cho ứng dụng AI. Bạn cũng có thể thực hành trực tiếp trên các bài lab kỹ thuật tại Wiki: đọc kế hoạch thực thi EXPLAIN ANALYZE, thiết kế Composite Index và Covering Index và xử lý Table Bloat với PostgreSQL VACUUM.

Đánh giá và tối ưu database theo bằng chứng

Khi đội ngũ cần xác định chính xác nút thắt hiệu năng cơ sở dữ liệu mà không muốn thử sai trực tiếp trên môi trường đang chạy, bạn có thể tham khảo dịch vụ tối ưu database và dịch vụ audit hệ thống phần mềm của ShiftLeft để thiết lập baseline, phân tích execution plan và xây dựng kế hoạch thay đổi có kiểm chứng.