Thêm một mệnh đề WHERE vào chỉ mục làm nó nhỏ đi 20 lần, đọc nhanh 2,9 lần và ghi nhanh 2,7 lần — cùng lúc

Một văn phòng bận rộn không giữ một cuốn sổ đăng ký xếp theo vần chứa mọi phiếu từng phát ra chỉ để lôi ra vài phiếu còn dở. Họ giữ một tấm bảng nhỏ, chỉ ghi những việc đang mở. Khi 95% phiếu đã đóng, tấm bảng ấy nhỏ hơn cuốn sổ hai chục lần, và mỗi lần phát rồi đóng ngay một phiếu thường lệ thì chẳng ai phải đụng tới bảng. Đổi lại, tấm bảng vô dụng nếu bạn hỏi "cho tôi xem một phiếu đã đóng" — những phiếu đó không có trên bảng. Chỉ mục một phần của PostgreSQL chính là tấm bảng "việc đang mở" ấy. 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.

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';

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:

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:

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.

Mẫu số chung

Tấm bảng "việc đang mở" ở đầu bài là một nguyên lý rộng: chỉ bỏ công tổ chức và bảo trì cấu trúc cho đúng phần dữ liệu người ta thật sự chạm tới — tập nóng — và bỏ mặc phần lạnh chiếm đa số. Trên dữ liệu lệch, chi phí bám theo tập nóng chứ không theo cả bảng, nên bạn thắng cả ba mặt cùng lúc: nhỏ hơn, đọc nhanh hơn, ghi nhanh hơn — vì không còn trả tiền để sắp xếp thứ không ai hỏi. Cái giá duy nhất là một hợp đồng: mọi người đọc phải mang đúng cái vị từ giữ nó ở trong tập ấy, nếu không nó lặng lẽ rơi về quét toàn bộ.

Cùng ý tưởng ấy, đổi ngăn xếp:

• Đây đúng là filtered index của SQL Server và partial index của MongoDB — cùng một tính năng, cùng một động cơ: đừng đánh chỉ mục dòng mà truy vấn không bao giờ hỏi.
• Trong bộ nhớ, một cache LRU hay một hot-set cache làm y hệt: nó chỉ giữ tập-làm-việc đang hoạt động chứ không giữ toàn bộ dữ liệu, vì phần lạnh tốn RAM mà gần như không được đọc. "Chỉ mục hoá tập nóng" và "cache tập làm việc" là hai tên của cùng một quyết định.
• Ràng buộc UNIQUE một phần là một hình thức khác: một bất biến chỉ áp trên một tập con — ở đây là các dòng ở trạng thái mo. Đó chính là "bất biến theo trạng thái" trong một máy trạng thái: quy tắc "chỉ một đơn mở" không nói gì về các đơn đã đóng, và lược đồ diễn đạt được đúng điều đó thay vì ép cả bảng.

Nên câu hỏi đúng trước khi đánh một chỉ mục đầy đủ trên bảng lệch không phải "chỉ mục này có nhanh không" mà là "tôi có đang trả tiền để tổ chức đống dữ liệu lạnh mà không truy vấn nào hỏi tới không" — và nếu có, phần nóng có được xác định bằng một vị từ mà mọi truy vấn quan trọng đều mang theo không. Muốn tìm cột lệch mạnh làm ứng viên, soát nhanh:

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à nhớ kiểm nó có thật sự được dùng — idx_scan = 0 sau vài ngày nghĩa là điều kiện chỉ mục không khớp truy vấn thật:

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

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.