Prefix Index và Hash Index - Vượt giới hạn kích thước index

Index phải nhỏ hơn đáng kể so với bảng để hiệu quả. Tạo index trên cột text dài sẽ gặp lỗi kiểu:

ERROR: index row size 3480 bytes exceeds maximum 2712 bytes

Kỹ thuật 1: Prefix Index - chỉ index phần đầu chuỗi

-- PostgreSQL: dùng biểu thức cắt chuỗi
CREATE INDEX articles_search ON articles (type, (SUBSTRING(title, 1, 20)));
-- MySQL: cú pháp tích hợp, gọn hơn
CREATE INDEX articles_search ON articles (type, title(20));

Vì tiền tố có thể TRÙNG (hai tiêu đề cùng 20 ký tự đầu), query cần thêm bước lọc:

-- PostgreSQL: phải lọc 2 lần
WHERE SUBSTRING(title, 1, 20) = '..20 ký tự đầu..'  -- dùng index
  AND title = '..tiêu đề đầy đủ..';                 -- lọc chính xác
-- MySQL: tự động xử lý - chỉ cần query bình thường
WHERE title = '..tiêu đề đầy đủ..';

Cân bằng: tiền tố quá ngắn → nhiều kết quả trùng → phải đọc nhiều dòng từ bảng để lọc lại. Tiền tố quá dài → index phình to, mất ý nghĩa.

Kỹ thuật 2: Index giá trị băm (hash) - cho chuỗi rất dài

CREATE INDEX articles_search ON articles (type, (SHA1(title)));
 
-- Query dùng đúng hàm hash + thêm điều kiện title để tránh trùng hash
WHERE SHA1(title) = SHA1('..tiêu đề đầy đủ..')
  AND title = '..tiêu đề đầy đủ..';

Ưu điểm so với tiền tố: hash gần như không bao giờ trùng → bước lọc lại từ bảng gần như không xảy ra. Có thể kết hợp cả hai: (SUBSTRING(SHA1(title), 1, 20)).

Kỹ thuật 3: Hash Index riêng biệt (chỉ PostgreSQL)

CREATE INDEX invoices_uniqid ON invoices USING HASH (uniqid);

B-tree Index	Hash Index
Hỗ trợ =, <, >, BETWEEN, ORDER BY	CHỈ hỗ trợ =
Kích thước lớn hơn	Nhỏ hơn
Tốc độ =: nhanh	Tốc độ =: nhanh hơn

Dùng hash index khi cột chỉ cần tra cứu bằng (exact value): mã giao dịch, token, UUID.

Liên quan

• Functional Index - prefix/hash index là functional index
• B+ Tree - cấu trúc mặc định mà hash index thay thế
• Index Write Overhead - index to = ghi chậm