Truy vấn chậm: quy trình gỡ

Năm mươi lăm phần trước đo từng cơ chế riêng lẻ. Phần này ghép chúng lại thành một quy trình, và chạy trên một sự cố dựng sẵn từ đầu tới cuối.

Sự cố

3.000.000 đơn hàng, 200.000 khách hàng, không có chỉ mục nào ngoài khoá chính. Một truy vấn báo cáo:

select k.hang, count(*), sum(d.tien)
from dh d join kh k on k.id = d.kh_id
where d.luc >= '2025-06-01' and d.luc < '2025-07-01'
  and d.tt = 'xong'
group by k.hang;

Bước 1: cái gì tốn nhiều thời gian máy chủ nhất

select round(total_exec_time::numeric) as tong_ms, calls,
       round(mean_exec_time::numeric, 2) as tb_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60)
from pg_stat_statements
order by total_exec_time desc limit 5;

684 ms tổng      5 lần   136,88 ms/lần   select k.hang, count(*), sum(d.tien) from dh ...
  7 ms tổng  3.000 lần     0,00 ms/lần   select count(*) from kh where id = $1

Bắt đầu từ tổng thời gian, không phải thời gian mỗi lần — phần 54 đã đo trường hợp mà hai cách xếp hạng cho hai kết luận trái ngược.

Ở đây chúng trùng nhau, nhưng bạn chỉ biết điều đó sau khi nhìn cả hai cột.

Bước 2: thời gian đi đâu

explain (analyze, buffers) <truy vấn>;

Phần đáng đọc nằm ở đáy kế hoạch, không phải đỉnh:

Parallel Seq Scan on dh
   Rows Removed by Filter: 971.200
   Buffers: shared hit=256 read=21.803

Đọc 21.803 trang rồi vứt đi 971.200 dòng mỗi tiến trình. Đây là chỗ tốn tiền.

Rows Removed by Filter là con số hữu ích nhất trong EXPLAIN ANALYZE và hay bị bỏ qua. Nó nói: "tôi đã đọc từng này dòng chỉ để loại chúng đi". Tỉ lệ giữa nó và rows thật là thước đo trực tiếp của việc thiếu chỉ mục.

Luôn thêm BUFFERS. Không có nó, bạn thấy thời gian mà không thấy khối lượng công việc — và phần 44 đã đo rằng thời gian có thể nhỏ trong khi khối lượng rất lớn, chỉ vì bộ đệm hệ điều hành đang che.

Bước 3: kiểm ước lượng trước khi đổ lỗi cho chỉ mục

Đây là bước hay bị bỏ, và bỏ nó dẫn tới dựng chỉ mục cho một vấn đề khác.

Lệch dưới hai lần ở mọi nút — thống kê đúng. Bộ lập lịch chọn quét tuần tự vì đó thật sự là lựa chọn tốt nhất trong số những gì nó có, không phải vì nó bị lừa.

Nếu cột "lệch" có số hàng trăm hoặc hàng nghìn, vấn đề là ở phần 20 (thống kê cũ) hoặc phần 32 (CREATE STATISTICS cho cột liên quan nhau) — và dựng chỉ mục sẽ không sửa được.

Bước 4: thử từng cách sửa, đo cả hai chiều

Hai điều đọc ra được.

Chỉ mục một phần thắng cả hai chiều: nhanh nhất và nhỏ nhất. Nó chỉ chứa những dòng mà truy vấn thật sự hỏi tới. Phần 13 đã đo cùng hiện tượng, và phần 27 đã đo nó trên bảng hàng đợi — 40 kB thay vì 15 MB.

Chỉ mục bao phủ chậm hơn dù nó đạt Index Only Scan. 142 MB là 83% kích thước bảng, và đọc 142 MB chỉ mục không rẻ hơn đọc phần bảng cần thiết. Index Only Scan không phải luôn thắng — phần 15 đã đo trường hợp nó thắng 6,6 lần, ở đây nó thua.

Đó là lý do bước này phải thử và đo chứ không suy luận. Bốn cách sửa, bốn kết quả, và cách trực giác gợi ý nhất (bao phủ) lại tệ thứ hai.

Bước 5: kiểm lại bằng chính công cụ của bước 1

trước khi sửa    684 ms tổng    136,9 ms/lần
sau khi sửa      208 ms tổng     41,7 ms/lần

Nhanh gấp 3,3 lần.

Và kiểm chỉ mục có thật sự được dùng không:

select indexrelname, idx_scan
from pg_stat_user_indexes where relname = 'dh';
--  dh_phan   5

Bước cuối này hay bị bỏ nhất, và nó là bước duy nhất chứng minh việc sửa có tác dụng. Phần 19 đã đo một trường hợp mà chỉ mục mới dựng không bao giờ được bộ lập lịch chọn — nếu không kiểm idx_scan, bạn chỉ có một chỉ mục tốn đĩa.

Quy trình rút gọn

Ba lỗi hay gặp khi bỏ bước:

Bỏ bước 1 — tối ưu câu mà bạn tình cờ để ý thấy, thay vì câu tốn nhất.

Bỏ bước 3 — dựng chỉ mục cho một vấn đề thống kê. Chỉ mục nằm đó, tốn đĩa, và truy vấn vẫn chậm.

Bỏ bước 5 — không biết mình đã sửa được gì. Đây là bước rẻ nhất và bị bỏ nhiều nhất.

Khi nào không phải lỗi của truy vấn

Nếu bước 2 cho thấy thời gian không nằm ở nút quét nào, hãy nhìn chỗ khác:

Trường hợp cuối đáng nói riêng: nếu EXPLAIN ANALYZE chạy nhanh mà ứng dụng thấy chậm, truy vấn không phải vấn đề — nó đang đợi. Kiểm bằng:

select pid, state, wait_event_type, wait_event,
       pg_blocking_pids(pid), left(query, 60)
from pg_stat_activity
where backend_type = 'client backend' and state <> 'idle';

Thử ba mươi giây

Chạy bước 1 trên hệ thống của bạn:

select round(100.0 * total_exec_time / sum(total_exec_time) over (), 1) as phan_tram,
       calls, round(mean_exec_time::numeric, 2) as tb_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 70)
from pg_stat_statements order by total_exec_time desc limit 5;

Rồi chạy bước 2 trên dòng đầu tiên. Hai câu lệnh đó đưa bạn đi được phần lớn quãng đường, và chúng mất chưa tới một phút.

Đây là phần cuối của nhóm giám sát và gỡ lỗi. Bốn phần vừa rồi — pg_stat_statements, các bảng pg_stat, và quy trình này — là bộ công cụ để tự tìm ra những gì năm mươi hai phần trước đã đo sẵn trên các ca dựng riêng.

Phần sau chuyển sang nhóm cuối của sê-ri: bảo mật, phân quyền, và những thứ cần làm trước khi đưa một cơ sở dữ liệu ra Internet.