Phần lớn bảng nghiệp vụ đều lệch: 95% đơn hàng đã hoàn tất và không ai truy vấn tới, 90% bản ghi đã xoá mềm, 99% người dùng không phải quản trị viên. Chỉ mục đầy đủ vẫn lưu hết — chỉ mục một phần thì không.

Chỉ mục một phần: dung lượng, tốc độ, và điều kiện dùng được

Cùng cột, chỉ thêm một mệnh đề

Bảng 3.000.000 đơn hàng, trong đó 95% đã hoan_tat:

CREATE INDEX ON dh (trang_thai, ngay);
CREATE INDEX ON dh (trang_thai, ngay) WHERE trang_thai <> 'hoan_tat';
Chỉ mục Dung lượng Đọc Chèn 500.000 dòng
Đầy đủ 24 MB 0,32 ms 1,91 s
Một phần 1.208 kB 0,11 ms 0,70 s
nhỏ hơn 20× nhanh 2,9× nhanh 2,7×

Cả ba mặt đều thắng, và đó là điều hiếm gặp trong tối ưu CSDL — thường bạn phải đánh đổi.

Lý do đơn giản: chỉ mục chỉ chứa 5% số dòng, nên nó nhỏ hơn, cây thấp hơn, và mỗi lần INSERT một dòng hoan_tat thì không phải sửa chỉ mục gì cả.

Việc dựng cũng nhanh hơn nhiều: 0,17 s so với 1,41 s.

Truy vấn nào dùng được

PostgreSQL dùng chỉ mục một phần khi nó chứng minh được điều kiện của truy vấn suy ra điều kiện của chỉ mục:

Điều kiện truy vấn Dùng được? Kết quả
trang_thai = 'cho_xu_ly' ✅ Index Only Scan, 9.721 trang
trang_thai = 'dang_giao' ✅ 31.348 trang
trang_thai IN ('cho_xu_ly','dang_giao') ✅ 31.645 trang
trang_thai <> 'hoan_tat' ✅ 31.641 trang
trang_thai = 'hoan_tat' ❌ Seq Scan, 50.464 trang
(không lọc trang_thai) ❌ Seq Scan, 50.464 trang

Hai dòng cuối là hành vi đúng, không phải lỗi: những dòng đó không nằm trong chỉ mục, nên không có cách nào dùng nó.

Điều đáng chú ý là dòng áp chót. Nếu ứng dụng của bạn có một màn hình liệt kê đơn đã hoàn tất, chỉ mục một phần này vô dụng với nó — bạn cần một chỉ mục khác, hoặc chấp nhận Seq Scan.

Và dòng cuối là cái bẫy thực tế nhất: truy vấn quên điều kiện trang_thai sẽ âm thầm rơi về quét toàn bảng. Với ORM sinh câu lệnh động, điều đó xảy ra dễ hơn bạn nghĩ.

Hai cách dùng đáng nhớ hơn cả tiết kiệm dung lượng

UNIQUE một phần: ràng buộc chỉ áp cho một số dòng

Yêu cầu quen thuộc: mỗi khách chỉ được có một đơn đang mở, nhưng bao nhiêu đơn đã đóng cũng được. Ràng buộc UNIQUE(kh) thông thường không diễn đạt được điều đó.

CREATE UNIQUE INDEX ux_mo ON gh (kh) WHERE trang_thai = 'mo';

Đo:

chen 2 don 'dong' + 1 don 'mo' cho khach 1  -> OK
chen them 1 don 'mo' nua                    -> ERROR: duplicate key value violates "ux_mo"
chen them 1 don 'dong' nua                  -> OK

Đây là cách diễn đạt quy tắc nghiệp vụ ngay trong lược đồ, thay vì tin vào tầng ứng dụng. Nó cũng đúng khi có nhiều tiến trình ghi cùng lúc — thứ mà kiểm tra bằng SELECT rồi INSERT không bảo đảm được.

Xoá mềm: chỉ đánh chỉ mục dòng còn sống

Bảng 2.000.000 dòng, 90% đã xoá mềm:

Chỉ mục Dung lượng
ON xm (ten) 60 MB
ON xm (ten) WHERE xoa_luc IS NULL 6.168 kB (10%)

Nếu bạn dùng xoá mềm, gần như mọi chỉ mục nên có WHERE xoa_luc IS NULL. Ứng dụng hầu như không bao giờ truy vấn dòng đã xoá, nên chín phần mười chỉ mục đang phục vụ không ai.

Nhớ rằng mọi truy vấn muốn dùng chỉ mục đó cũng phải có WHERE xoa_luc IS NULL — đó là điều kiện mà ORM cần được cấu hình để tự thêm.

Một phép đo tôi làm hỏng

Lần đầu tôi so chỉ mục đầy đủ (trang_thai, ngay) với chỉ mục một phần (ngay) WHERE ... và ra kết quả ngược: chỉ mục đầy đủ nhanh hơn (0,05 ms so với 1,10 ms).

Hai chỉ mục có số cột khác nhau. Cái đầy đủ có trang_thai làm cột dẫn, nên nó phục vụ được Index Only Scan; cái một phần chỉ có ngay, nên vẫn phải về bảng kiểm trang_thai='cho_xu_ly' — bởi điều kiện của chỉ mục là <> 'hoan_tat', rộng hơn điều kiện truy vấn.

So sánh chỉ có nghĩa khi chỉ đúng một thứ khác nhau. Sau khi cho cả hai cùng cột (trang_thai, ngay), con số mới đảo lại đúng chiều.

Khi nào đừng dùng

  • Dữ liệu phân bố đều. Nếu điều kiện của bạn giữ lại 50% số dòng, chỉ mục chỉ nhỏ đi một nửa và bạn mất tính linh hoạt.
  • Điều kiện thay đổi theo thời gian. WHERE ngay > '2025-01-01' sẽ dần bao phủ cả bảng, và bạn không thể sửa điều kiện của một chỉ mục — phải xoá và dựng lại.
  • Truy vấn không luôn kèm điều kiện đó. Chỉ mục một phần chỉ hữu ích nếu phần lớn truy vấn quan trọng đều thoả điều kiện.

Thử ba mươi giây

Tìm cột lệch mạnh trong CSDL của bạn — đó là ứng viên cho chỉ mục một phần:

SELECT tablename, attname,
       (most_common_vals::text::text[])[1] AS gia_tri_pho_bien,
       (most_common_freqs)[1] AS ti_le
FROM pg_stats
WHERE schemaname = 'public' AND (most_common_freqs)[1] > 0.7
ORDER BY (most_common_freqs)[1] DESC;

Mỗi dòng là một cột mà hơn 70% số dòng mang cùng một giá trị. Nếu truy vấn của bạn hầu như luôn tìm những dòng khác giá trị đó, chỉ mục một phần sẽ nhỏ đi đúng theo tỉ lệ ấy.

Và kiểm tra chỉ mục một phần có thật sự được dùng:

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE indexrelname = 'ten_chi_muc_mot_phan';

idx_scan = 0 sau vài ngày nghĩa là điều kiện của chỉ mục không khớp với truy vấn thật của bạn.

Phần sau đo chỉ mục biểu thức: khi hàm bọc quanh cột làm chỉ mục thường vô dụng.