Database Indexing — Tối Ưu Truy Vấn Trong PostgreSQL • blog.dopana

Index là cách nhanh nhất để tăng tốc database — nếu dùng đúng. Dùng sai còn tệ hơn không dùng.

Index Là Gì?#

Giống như mục lục sách. Không có index, database phải scan toàn bộ bảng (sequential scan). Có index, nó nhảy thẳng đến dòng cần tìm.

-- Không index → Sequential scan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Seq Scan on users (cost=0.00..435.00 rows=1 width=36)

-- Có index → Index scan
CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan using idx_users_email (cost=0.28..8.29 rows=1 width=36)

sql

Các Loại Index#

B-tree (Mặc Định)#

Tốt cho: =, >, <, >=, <=, BETWEEN, LIKE 'abc%'

CREATE INDEX idx_users_created ON users(created_at);
-- WHERE created_at > '2024-01-01' → dùng index
-- WHERE name LIKE 'Alice%' → dùng index
-- WHERE name LIKE '%Alice' → KHÔNG dùng index

sql

Hash#

Chỉ tốt cho =. Ít dùng vì B-tree đã làm tốt hơn hầu hết trường hợp.

CREATE INDEX idx_users_email_hash ON users USING hash(email);
-- Chỉ: WHERE email = 'alice@example.com'

sql

GiST (Generalized Search Tree)#

Cho full-text search, geometry, range types.

-- Full-text search
CREATE INDEX idx_articles_content ON articles USING gin(to_tsvector('english', content));
-- WHERE to_tsvector('english', content) @@ to_tsquery('database & indexing')

sql

GIN (Generalized Inverted Index)#

Cho JSONB, array, full-text search.

-- JSONB
CREATE INDEX idx_users_metadata ON users USING gin(metadata jsonb_path_ops);
-- WHERE metadata @> '{"role": "admin"}'

-- Array
CREATE INDEX idx_articles_tags ON articles USING gin(tags);
-- WHERE tags && array['database']

sql

Composite Index — Nhiều Cột#

CREATE INDEX idx_users_org_role ON users(organization_id, role);

sql

Thứ tự cột quan trọng:

WHERE org_id = 1 AND role = 'admin' → dùng index
WHERE role = 'admin' → KHÔNG dùng index (role là cột thứ 2)
WHERE org_id = 1 → dùng index (org_id là cột đầu)

Rule: Cột có cardinality cao (nhiều giá trị unique) để trước.

Partial Index — Index Có Điều Kiện#

-- Chỉ index user active — nhỏ hơn, nhanh hơn
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';

-- Tìm kiếm chỉ trong active users
SELECT * FROM users WHERE status = 'active' AND email = 'alice@example.com';
-- → dùng index nhỏ, không cần scan 1 triệu inactive users

sql

Covering Index — Include Cột#

-- Nếu query chỉ cần id và email, index "cover" luôn query
CREATE INDEX idx_users_email_cover ON users(email) INCLUDE (id, name);

-- Query này chỉ đọc index, không cần động vào table:
SELECT id, name FROM users WHERE email = 'alice@example.com';
-- → Index Only Scan

sql

EXPLAIN — Đọc Query Plan#

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users WHERE email = 'alice';

sql

Các thông số quan trọng:

cost — đơn vị ước lượng (không phải ms). cost=0.00..435.00: 0.00 là cost để lấy dòng đầu, 435.00 là total
rows — số dòng ước lượng
width — kích thước mỗi dòng (byte)
actual time — thời gian thực tế (ms) — chỉ có với ANALYZE
Buffers: shared hit — số page đọc từ cache — càng nhiều càng tốt

Dấu Hiệu Cần Index#

Trong EXPLAIN, nếu thấy:

Seq Scan on users (cost=0.00..435.00 rows=100000) — full table scan
Sort Method: external merge Disk: 123kB — sort tràn disk
actual time=0.05..435.00 — chênh lệch lớn giữa cost và actual

Khi Nào Index Có Hại?#

Bảng nhỏ (< 1000 rows) — sequential scan nhanh hơn
Insert/Update nhiều — mỗi index làm chậm write
Nhiều index thừa — (a, b) + (a) → (a) là thừa

-- Tìm index trùng
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users'
ORDER BY indexname;

sql

Index Maintenance#

-- Xem kích thước index
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'users';

-- Rebuild index (khi bloat nhiều)
REINDEX INDEX idx_users_email;
REINDEX TABLE users;   -- Rebuild tất cả index trong table

sql

Checklist#

Index trên cột trong WHERE, JOIN, ORDER BY
Composite index với column cardinality cao ở đầu
Partial index cho filtered queries
Covering index cho frequent queries
Loại bỏ index trùng
REINDEX định kỳ cho bảng hay update
EXPLAIN ANALYZE trước khi thêm index

Kết Luận#

Index là công cụ mạnh — biết cách dùng là siêu năng lực. Luôn EXPLAIN ANALYZE trước khi thêm index. Đo lường, thêm, đo lại. Một index đúng có thể tăng tốc query từ phút xuống mili giây.

Database Indexing — Tối Ưu Truy Vấn Trong PostgreSQL