Tối ưu hóa truy vấn dựa trên phân tích kế hoạch: EXPLAIN ANALYZE

Đây là khoảnh khắc sự thật: truy vấn SQL không chỉ là mấy dòng code, mà là một cuộc trò chuyện thực sự với database. Nếu bạn thì thầm nhẹ nhàng "SELECT *", database có thể hiểu đúng ý bạn và thực hiện lệnh mà không phàn nàn gì. Nhưng nếu bạn ném cho nó một truyện dài SQL không rõ ràng, database sẽ phải suy nghĩ... và rồi bắt đầu lag.

Tối ưu hóa truy vấn là kỹ năng nói chuyện với database bằng ngôn ngữ ngắn gọn, dễ hiểu. Khi truy vấn được viết rõ ràng và hiệu quả, nó chạy nhanh, không làm nặng hệ thống và không ảnh hưởng đến các process khác. Nhưng nếu truy vấn viết dở, cả hệ thống có thể chậm lại: database bắt đầu ngốn CPU và RAM, ổ đĩa thì bận rộn đọc ghi linh tinh, và cả mấy app dùng database cũng bị lag theo.

EXPLAIN ANALYZE giúp bạn phát hiện ra những chỗ truy vấn bị "nặng". Nó giống như chẩn đoán bệnh – không có nó thì khó mà chữa được vấn đề hiệu suất.

Những vấn đề thường gặp trong truy vấn và cách phát hiện

Bây giờ là lúc làm quen với các "nghi phạm" gây giảm hiệu suất. Để làm việc này, mình sẽ dùng lệnh EXPLAIN ANALYZE.

Vấn đề 1: Quét tuần tự (Seq Scan)

Seq Scan (quét tuần tự) là khi PostgreSQL tìm dữ liệu bằng cách duyệt từng dòng trong bảng. Nếu bảng nhỏ thì không sao, nhưng với bảng to thì kiểu này đúng là cực hình.

Làm sao biết truy vấn đang dùng Seq Scan? Chỉ cần chạy EXPLAIN ANALYZE. Ví dụ:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

Kết quả có thể như sau (để ý Seq Scan):

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

Giải quyết thế nào?

Tạo index cho student_id nếu chưa có:

CREATE INDEX idx_student_id ON students(student_id);

Sau đó chạy lại EXPLAIN ANALYZE. Bạn sẽ thấy Index Scan thay vì Seq Scan.

Vấn đề 2: Điều kiện lọc có độ chọn lọc thấp

Độ chọn lọc (selectivity) là số dòng cần xử lý để tìm được cái bạn muốn. Nếu filter của bạn chọn gần như cả bảng, thì index cũng không giúp được gì.

Ví dụ truy vấn có độ chọn lọc thấp:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

Nếu 90% sinh viên trong bảng học Computer Science, truy vấn này vẫn dùng Seq Scan dù đã có index trên program.

Cải thiện truy vấn thế nào?

1. Xem lại logic truy vấn: có thể bạn cần filter kỹ hơn, thêm điều kiện khác.
2. Đảm bảo thống kê của bảng là mới nhất (giúp PostgreSQL đánh giá đúng độ chọn lọc):

ANALYZE students;

3. Nếu truy vấn dùng index không hợp lý thay vì quét tuần tự, thử ép PostgreSQL dùng index:

SET enable_seqscan = OFF;

Vấn đề 3: Quá nhiều thao tác sắp xếp

Sắp xếp (Sort) có thể rất tốn tài nguyên, nhất là khi dữ liệu không vừa RAM. Câu lệnh điển hình cần sort là ORDER BY.

Ví dụ vấn đề:

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

Bạn có thể thấy kiểu như sau:

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

Làm sao tăng tốc sort? Nếu bạn thường xuyên sort theo một cột nào đó, hãy tạo index:

CREATE INDEX idx_last_name ON students(last_name);

Bây giờ PostgreSQL có thể dùng index để lấy dữ liệu đã sort sẵn, không cần sort thêm nữa.

Vấn đề 4: Không giới hạn kết quả (LIMIT)

Khi bạn truy vấn với SELECT mà không giới hạn số dòng trả về, truy vấn có thể phải xử lý cả bảng, dù bạn chỉ cần dòng đầu tiên.

Ví dụ:

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

Nếu database có một triệu dòng, mà filter gpa > 3.5 trả về 80% bảng, bạn sẽ phải chờ khá lâu.

Nếu bạn chỉ cần 10 sinh viên giỏi nhất, hãy dùng LIMIT:

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

Bạn cũng có thể dùng OFFSET cùng với LIMIT để phân trang.

Quản lý tham số thực thi: SET

Lệnh SET trong PostgreSQL dùng để thay đổi tham số của session hoặc truy vấn. Nó giống như chỉnh tạm thời, chỉ ảnh hưởng trong kết nối hiện tại thôi.

Nói đơn giản, SET là cách điều chỉnh "tâm trạng" của PostgreSQL ngay lập tức, không cần đổi config toàn hệ thống.

Dùng ở đâu?

• Đổi ngôn ngữ hoặc định dạng ngày trước khi chạy báo cáo.
• Tăng RAM cho một truy vấn nặng.
• Tắt log khi import dữ liệu lớn.
• Tạm thời đổi search_path.
• Quản lý bảo mật (ví dụ, tạm hạ quyền user).

Cú pháp chung

SET tham_so = gia_tri;

Để xem giá trị hiện tại của tham số:

SHOW tham_so;

Để trả về giá trị mặc định:

RESET tham_so;

Ví dụ tối ưu hóa tổng hợp

Giả sử bạn cần tìm 10 sinh viên cuối cùng có GPA cao nhất, học Computer Science. Đây là truy vấn gốc:

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. Phân tích truy vấn: Đầu tiên chạy EXPLAIN ANALYZE:

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   Nếu bạn thấy quét tuần tự và sort, đó là dấu hiệu cần tối ưu.

2. Index cho filter và sort:

   Tạo index tổng hợp cho cả hai cột:

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. Kiểm tra cải thiện:

   Chạy lại EXPLAIN ANALYZE. Bây giờ truy vấn sẽ dùng index mới, không cần sort hay quét tuần tự nữa.

Phương pháp tối ưu hóa truy vấn

1. Bắt đầu bằng phân tích kế hoạch thực thi hiện tại. Dùng EXPLAIN ANALYZE để tìm các thao tác gây vấn đề.

2. Xác định điểm nghẽn. Tìm các node trong plan tốn nhiều thời gian hoặc tài nguyên nhất.

3. Tạo index. Kiểm tra cột nào dùng để filter, sort và tạo index phù hợp.

4. Giảm lượng dữ liệu. Dùng LIMIT, OFFSET và điều kiện filter chính xác.

5. Cập nhật thống kê. Chạy ANALYZE để PostgreSQL có thông tin mới nhất về dữ liệu.

6. Kiểm tra lại sau khi tối ưu. Sau khi tối ưu, chạy lại EXPLAIN ANALYZE để chắc chắn hiệu suất đã tốt hơn.

Tiếp theo là gì?

Bạn vừa đi qua một khóa tăng tốc truy vấn siêu tốc. Chúc mừng nhé! Càng vọc nhiều với EXPLAIN ANALYZE, bạn càng hiểu rõ bên trong PostgreSQL. Và nhớ: không có index thần thánh nào cứu nổi nếu truy vấn quá phức tạp hoặc viết mù mờ. SQL, cũng như mọi ngôn ngữ khác, thích sự rõ ràng.