Thường thì chúng ta index sẽ hiểu là tất cả các dòng của cột đều được đánh index, nhưng trong một số trường hợp ta chỉ muốn đánh index theo một điều kiện nào đó. Một số hệ quản trị cung cấp chúng ta một tiện ích gọi là partial (PostgreSQL) or filtered (SQL Server).
Nhưng tại sao phải index theo điều kiện, index hết không phải hơn à? Thường thì index theo điều kiện này thường dùng cho các trường hợp điều kiên where chứa các hằng số, như trang thái (status code, status)...
SELECT message FROM messages WHERE processed = 'N' AND receiver = ?
Những truy vấn như trên rất hay gặp trong hệ thống queue. Truy vấn tìm tất cả các tin nhắn(message) chưa qua xử lý (processed = 'N') cho một người nhận cụ thể (receiver). Những message đã xử lý thì không cần phải query lắm. Nếu cần thì những message lấy cũng chiếm phần lớn số lượng của bảng nên việc index sẽ không có độ selectivity cao nên cũng không hiệu quả.
Thông thường chúng ta sẽ tạo index như sau:
CREATE INDEX messages_todo ON messages (receiver, processed)
Index như vậy đáp ứng được yêu cầu, tuy nhiên nó bao gồm cả những row mà ít khi query đến (Những dòng có trạng thái đã xử lý) và tốn ổ cứng để lưu dữ liệu index cho những bản ghi này, đôi khi tốn hơn rất nhiều dung lượng cần thiết cho những bản ghi cần truy vấn.
Với partial indexing ta có thể giới hạn được số lượng bản ghi cần được index. Cú pháp thì rất đơn giản như sau:
CREATE INDEX messages_todo ON messages (receiver) WHERE processed = 'N'
Chỉ những row thỏa mãn điều kiện WHERE mới được index, rất tiết kiệm ổ cứng, hơn nữa trọng index không cần thêm trường processed nữa vì nó luôn luôn bằng 'N' vừa tiết kiệm số lượng bản ghi index lại tiết kiệm thêm kích thước từng dòng trong index. Cả hai chiều row và column.
Số lượng index trở lên rất nhỏ, với trường hợp queue số lượng index còn không tăng lên dù số lượng bản ghi tăng lên nhiều đi nữa, vì trạng thái processed = 'N' thường dao động quanh một con số nào đó, chỉ những bản ghi chưa được xử lý mà thôi. Còn số lượng bản ghi có xử lý có lớn bao nhiêu cũng không ảnh hưởng tới kích thước index.
Điều kiện trong cấu WHERE có thể phức tạp tùy ý, tuy nhiên cần chú ý một điều nếu dùng hàm trong WHERE thì hàm đó phải là deterministic nghĩa là với cùng một đầu vào luôn có cùng kết quả đầu ra (Những hàm như GetDate() không phải là deterministic vì kết quả khác nhau mỗi lần gọi).
Nghĩ một chút
Câu truy vấn dưới đây thì nên dùng index như thế nào là tốt nhât? SELECT message FROM messages WHERE processed = 'N'