PostgreSQL Indexing: Cách Tối Ưu Truy Vấn Cơ Sở Dữ Liệu

PostgreSQL Indexing: Cách Tối Ưu Truy Vấn Cơ Sở Dữ Liệu

Index trong PostgreSQL là cấu trúc dữ liệu giúp tăng tốc truy vấn đáng kể. Khi bảng có hàng triệu bản ghi, truy vấn không index có thể mất hàng giây, trong khi có index chỉ mất vài mili giây. Bài viết này phân tích các loại index, khi nào nên dùng, và cách tránh những sai lầm phổ biến khi tối ưu database.

Index Trong PostgreSQL Là Gì?

Khi không có index, PostgreSQL phải quét toàn bộ bảng (Sequential Scan) để tìm dữ liệu khớp. Với bảng triệu bản ghi, điều này rất tốn kém. Index tạo cấu trúc tìm kiếm nhanh (thường là B-tree), giống như mục lục sách giúp tìm trang nhanh thay vì đọc hết sách. Tài liệu PostgreSQL giải thích chi tiết về cơ chế lưu trữ chỉ mục.

Index cải thiện đọc dữ liệu nhưng có chi phí cho ghi (INSERT, UPDATE, DELETE phải cập nhật cả index). Vì vậy cần cân nhắc kỹ trước khi tạo.

Các Loại Index Phổ Biến

B-Tree Index (Mặc Định)

B-Tree là loại index mặc định trong PostgreSQL, phù hợp cho toán tử so sánh: =, <, >, BETWEEN, ORDER BY. B-tree giữ dữ liệu sắp xếp theo thứ tự, cho phép tìm kiếm nhị phân nhanh.

CREATE INDEX idx_users_email ON users(email);

-- Composite B-tree trên nhiều cột
CREATE INDEX idx_orders_composite ON orders(user_id, created_at);

Đọc thêm: B-tree index documentation

Hash Index

Chỉ hỗ trợ toán tử =, phù hợp cho lookup chính xác. Từ PostgreSQL 10, Hash index đã WAL-logging nên phục hồi crash an toàn. Hash index không hỗ trợ range query.

CREATE INDEX idx_users_hash ON users USING HASH (email);

GIN Index (Generalized Inverted Index)

GIN là inverted index, hỗ trợ dữ liệu phức hợp: mảng, JSONB, full-text search. Mỗi phần tử trỏ đến danh sách dòng chứa nó.

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
CREATE INDEX idx_posts_content ON posts USING GIN (to_tsvector('vietnamese', content));

GIN chậm khi ghi nhưng rất nhanh khi đọc, phù hợp cho search engine nội bộ.

BRIN Index (Block Range Index)

Nhỏ gọn hơn B-tree rất nhiều cho bảng lớn có dữ liệu sắp xếp tự nhiên (timestamp, ID tuần tự). BRIN chỉ lưu min/max giá trị theo khối dữ liệu, không cần chỉ mục từng dòng.

CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

Phù hợp cho bảng logs, IoT data, bảng ghi theo thời gian với hàng tỷ bản ghi.

Khi Nào Không Nên Tạo Index

Index không phải lúc nào cũng tốt. Tạo index bừa bãi gây hại rõ rệt:

• Bảng nhỏ: Ít dòng thì index tăng overhead mà không tăng tốc. Sequential scan trên bảng nhỏ còn nhanh hơn index lookup
• Cột ít chọn lọc: Cột chỉ có 2-3 giá trị (giới tính, trạng thái) — PostgreSQL thường bỏ index vì phải quét quá nhiều dòng
• Write-heavy tables: Mỗi INSERT/UPDATE/DELETE phải cập nhật tất cả index trên bảng, làm chậm đáng kể ghi
• Index dư thừa: Index trùng hoặc tiền tố trùng nhau lãng phí disk và bộ nhớ

Cách Phân Tích Index Với EXPLAIN

Sử dụng EXPLAIN ANALYZE để kiểm tra index có được dùng và đo thời gian thực:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Kết quả tốt sẽ hiển thị Index Scan hoặc Index Only Scan (khi tất cả dữ liệu cần đã có trong index). Nếu thấy Seq Scan, index không được dùng — kiểm tra lại query hoặc cập nhật thống kê:

ANALYZE users;

-- Kiểm tra danh sách index trên bảng
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

ANALYZE cập nhật thống kê để PostgreSQL quyết định đúng khi nào dùng index.

Composite Index Và Quy Tắc Leftmost

Composite index trên nhiều cột tuân theo quy tắc leftmost prefix — query phải dùng cột đầu tiên trong index thì index mới có hiệu lực:

CREATE INDEX idx_order ON orders(user_id, status, created_at);

Index này hỗ trợ:

• WHERE user_id = 1 ✓ — dùng cột đầu tiên
• WHERE user_id = 1 AND status = 'active' ✓ — dùng 2 cột đầu
• WHERE user_id = 1 AND status = 'active' AND created_at > '2024-01-01' ✓ — dùng cả 3 cột
• WHERE status = 'active' ✗ — bỏ qua cột đầu tiên (user_id)
• WHERE created_at > '2024-01-01' ✗ — bỏ qua 2 cột đầu

Partial Index: Index Có Điều Kiện

Partial index chỉ index một phần dữ liệu dựa trên WHERE, tiết kiệm dung lượng và tăng tốc cho các truy vấn cụ thể:

CREATE INDEX idx_active_users ON users(email) WHERE active = true;
CREATE INDEX idx_recent_orders ON orders(created_at) WHERE created_at > '2024-01-01';

Phù hợp khi chỉ truy vấn dữ liệu hoạt động, bỏ qua bản ghi đã xóa hoặc archived. Partial index nhỏ hơn nhiều so với full index.

Unique Index Và Constraint

Unique index đảm bảo không có 2 dòng có cùng giá trị: CREATE UNIQUE INDEX hoặc ALTER TABLE ADD CONSTRAINT. PostgreSQL tự động tạo unique index khi thêm PRIMARY KEY hoặc UNIQUE constraint. Sự khác biệt: constraint tên rõ ràng hơn, error message thân thiện hơn.

Kết Luận

Index là công cụ mạnh nhưng cần sử dụng có chọn lọc. Bắt đầu với EXPLAIN ANALYZE để xác định truy vấn chậm, tạo index đúng cột theo quy tắc leftmost, dùng partial index khi phù hợp, và thường xuyên ANALYZE để cập nhật thống kê. Database được index tốt chạy nhanh hơn 10-100 lần so với bảng chưa index, nhưng nhớ rằng mỗi index thêm đều có chi phí cho thao tác ghi.