Giới thiệu về phân tích hiệu năng truy vấn

Cho mình kể một câu chuyện thực tế nhé. Hãy tưởng tượng bạn đang làm dev cho một nền tảng shop online lớn. Mọi thứ chạy ngon lành, trừ một chuyện nhỏ: báo cáo doanh số tháng load chậm đến mức user còn kịp đặt thêm đơn nữa trước khi nó xong. Bạn căng thẳng, manager của bạn cũng căng thẳng, server PostgreSQL thì sôi như ấm nước, và mọi thứ có mùi tận thế. Giờ thử tưởng tượng bạn chỉ cần một phát là tìm ra nguyên nhân và fix luôn.

Phân tích hiệu năng truy vấn giống như kiểm tra sức khỏe cho PostgreSQL của bạn vậy. Nó giúp bạn tìm ra "bottleneck" (điểm nghẽn) và sửa chúng để cải thiện trải nghiệm người dùng và tiết kiệm tài nguyên hệ thống.

Truy vấn hoạt động như thế nào bên trong PostgreSQL?

Khi bạn viết một truy vấn đơn giản kiểu:

SELECT * FROM products WHERE price > 100;

PostgreSQL không nhảy vào lấy dữ liệu ngay đâu. Nó sẽ phân tích truy vấn của bạn, nghĩ xem nên thực hiện thế nào cho tối ưu, rồi mới bắt đầu làm việc.

Các bước chính khi thực hiện truy vấn:

1. Phân tích cú pháp. PostgreSQL kiểm tra truy vấn của bạn có lỗi cú pháp không và chuyển nó thành dạng trung gian.
2. Tối ưu hóa. Query optimizer sẽ đánh giá nhiều cách thực hiện truy vấn và chọn cách "rẻ" nhất (về thời gian và tài nguyên).
3. Thực thi. Server sẽ làm theo plan đã chọn để lấy dữ liệu.

Bottleneck là gì?

"Bottleneck" là phần của truy vấn làm chậm cả quá trình. Đó có thể là thao tác nào đó bất ngờ tốn nhiều thời gian hoặc tài nguyên nhất. Ví dụ, nếu PostgreSQL thay vì dùng index lại đi scan toàn bộ bảng (Seq Scan), truy vấn sẽ chậm. Hoặc nếu dữ liệu nhiều hơn dự kiến, server sẽ tốn nhiều thời gian để sort, join, filter.

Những chỗ như vậy gọi là bottleneck – chính chúng là thứ bạn nên tìm và tối ưu đầu tiên.

Công cụ phân tích hiệu năng truy vấn

PostgreSQL có vài tool mạnh giúp bạn nhận diện vấn đề trong truy vấn:

1. EXPLAIN và EXPLAIN ANALYZE. Hai lệnh này cho bạn biết PostgreSQL sẽ thực hiện truy vấn như thế nào, hoặc thậm chí chạy luôn để đo hiệu năng thực tế.

• EXPLAIN: hiển thị execution plan mà không thực sự chạy truy vấn.
• EXPLAIN ANALYZE: chạy truy vấn và hiển thị execution plan thực tế kèm theo số liệu thời gian.

Ví dụ dùng EXPLAIN:

EXPLAIN SELECT * FROM products WHERE price > 100;

Kết quả:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Ở đây bạn thấy truy vấn đang dùng "Seq Scan" – quét toàn bộ bảng, rất không hiệu quả với bảng lớn.

2. pg_stat_statements. Đây là extension bổ sung, ghi lại các truy vấn đã chạy. Nó cho bạn biết:

• Những truy vấn nào đang chạy trên server.
• Mỗi truy vấn tốn bao nhiêu thời gian.
• Truy vấn trả về bao nhiêu dòng và tiêu tốn bao nhiêu tài nguyên.

Để bật pg_stat_statements, bạn cần:

1. Bật extension:

CREATE EXTENSION pg_stat_statements;

2. Cấu hình PostgreSQL: Trong file postgresql.conf thêm:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. Khởi động lại PostgreSQL.

Bây giờ bạn có thể phân tích truy vấn:

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Lệnh này sẽ hiển thị 5 truy vấn "nặng" nhất theo tổng thời gian thực thi giảm dần.

3. Log truy vấn chậm. Bạn có thể cấu hình PostgreSQL để ghi lại các truy vấn chạy quá lâu (ví dụ hơn 1 giây) vào file log.

Để làm điều này, trong postgresql.conf đặt:

log_min_duration_statement = 1000  # Thời gian tính bằng mili giây (1 giây)

Bây giờ các truy vấn chậm sẽ được lưu vào log của PostgreSQL.

Các chỉ số chính để phân tích hiệu năng

Khi bạn phân tích hiệu năng truy vấn, hãy chú ý các chỉ số sau:

1. Thời gian thực thi. Chỉ số chính cho biết truy vấn chạy mất bao lâu. Càng nhanh càng tốt.
2. Số dòng. Nếu truy vấn trả về hoặc scan nhiều dòng hơn bạn nghĩ, có thể là vấn đề.
3. Sử dụng index. Nếu truy vấn nên dùng index mà lại scan tuần tự (Seq Scan), đó là dấu hiệu cần tối ưu.
4. Buffer và thao tác đĩa. Truy vấn phải đọc/ghi nhiều trên đĩa sẽ chậm hơn so với truy vấn dùng dữ liệu trong RAM.

Áp dụng kiến thức này vào thực tế như thế nào?

Ví dụ 1: Truy vấn chậm

Bạn viết truy vấn lấy tất cả sản phẩm giá trên 100:

SELECT * FROM products WHERE price > 100;

Bạn nhận ra truy vấn chạy quá lâu. Dùng EXPLAIN và thấy:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Vấn đề: truy vấn đang scan toàn bộ bảng vì chưa có index cho cột price.

Giải pháp:

Tạo index:

CREATE INDEX idx_price ON products(price);

Bây giờ truy vấn sẽ dùng Index Scan:

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

Ví dụ 2: Phát hiện truy vấn chậm với pg_stat_statements

Dùng lệnh:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Bạn phát hiện một truy vấn tốn rất nhiều thời gian. Bạn mở nó bằng EXPLAIN ANALYZE, sửa lại, và nó chạy nhanh hơn hẳn.

Khi bạn bắt đầu dùng EXPLAIN, pg_stat_statements và các tool khác, truy vấn của bạn sẽ chạy nhanh hơn, còn server PostgreSQL sẽ chạy mượt như đồng hồ Thụy Sĩ. Ở bài sau, tụi mình sẽ đi sâu vào các tham số của EXPLAIN như cost, rows và width để đọc execution plan như đọc sách vậy.