Hoàn thành bài "Bài 3: Window Functions và Analytical Queries" trước để mở khoá bài này.Đến bài trước

Giới thiệu

Truy vấn chậm là triệu chứng dễ nhận thấy nhất của một hệ thống backend đang chịu tải. Trong PostgreSQL, sự khác biệt giữa một truy vấn quét hàng triệu dòng và một truy vấn chỉ lấy đúng mười dòng thường quy về một điều: đúng loại index. Bài học này sẽ đi qua ba loại index mà bạn dùng nhiều nhất trong công việc backend — B-tree, composite và partial — và chỉ cho bạn cách đọc một plan của EXPLAIN ANALYZE để biết mình cần loại nào.

Index B-tree: Lựa chọn mặc định đa năng

Index B-tree là loại index mà PostgreSQL tạo ra khi bạn chỉ đơn giản viết CREATE INDEX. Nó giữ các giá trị cột được sắp xếp, nên các thao tác tìm kiếm theo đẳng thức (=) và khoảng (>, <, BETWEEN) sẽ rất nhanh. Hãy dùng nó bất cứ khi nào một cột xuất hiện thường xuyên trong mệnh đề WHERE hoặc điều kiện JOIN.

sql
CREATE INDEX idx_users_email ON users (email);
SELECT * FROM users WHERE email = 'alice@example.com';

Nếu không có index, PostgreSQL sẽ thực hiện sequential scan — đọc từng dòng một. Khi có index, nó nhảy thẳng đến bản ghi khớp. Trên một bảng có mười triệu dòng, khoảng cách đó là từ 200ms xuống còn 2ms.

sơ đồ luồng so sánh sequential scan và index scan trên một bảng lớn

Index composite: Thứ tự rất quan trọng

Khi một truy vấn lọc hoặc sắp xếp theo nhiều cột, một index composite có thể phục vụ cả hai cùng lúc. Chi tiết mấu chốt là thứ tự cột: index chỉ được sử dụng hiệu quả khi các cột đứng đầu bị ràng buộc.

sql
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Ở đây index trên (user_id, created_at) cho phép PostgreSQL lọc theo user_id và đồng thời các dòng đã được sắp xếp theo created_at — không cần thêm bước sort riêng. Một lỗi phổ biến là tạo index với thứ tự cột sai. Một index trên (created_at, user_id) sẽ không giúp ích cho truy vấn này, vì user_id không phải cột đứng đầu.

sơ đồ minh hoạ các mục của composite index được sắp xếp theo cột đầu tiên trước, rồi mới đến cột thứ hai

Partial Indexes: Chỉ lập index những gì thực sự cần thiết

Partial index bổ sung mệnh đề WHERE ngay trong chính index, nên nó chỉ lưu trữ các dòng thoả mãn điều kiện. Nhờ đó index gọn nhẹ và nhanh khi phần lớn truy vấn của bạn chỉ quan tâm đến một tập con của bảng.

sql
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

Nếu 99% đơn hàng đã hoàn tất và chỉ 1% đang chờ xử lý, thì một full index trên cột status là lãng phí. Partial index có thể nhỏ hơn 1000× mà vẫn đápứng đầy đủ cho các truy vấn thực sự quan trọng.

Đọc EXPLAIN ANALYZE

EXPLAIN ANALYZE thực thi truy vấn và báo cáo plan thực tế mà PostgreSQL đã chọn, kèm thời gian và số dòng thật. Những tín hiệu quan trọng nhất cần chú ý:

- Sequential Scan trên bảng lớn → rất có thể bạn cần thêm index. - Số dòng bị filter loại bỏ cao hơn nhiều so với số dòng planner ước tính → số liệu thống kê đã cũ; hãy chạy ANALYZE table_name. - Sort node nằm trên một tập dữ liệu lớn → cân nhắc một index khớp với ORDER BY, hoặc thêm LIMIT sớm hơn trong truy vấn.

sql
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Hãy tìm Index Scan using idx_orders_user_date trong output. Nếu bạn thấy Seq Scan thay vào đó, nghĩa là index không được sử dụng — thường là do bảng quá nhỏ để planner cần dùng đến index, hoặc số liệu thống kê đã lỗi thời.

Những lỗi thường gặp

Lập index cho mọi cột còn tệ hơn là không lập index cột nào: mỗi index đều làm chậm thao tác ghi (INSERT, UPDATE, DELETE) vì PostgreSQL phải cập nhật cả index. Một nguyên tắc tốt là: chỉ lập index cho những cột xuất hiện trong WHERE, ORDER BY hoặc JOIN của các truy vấn chạy thường xuyên và xử lý nhiều dòng.

Một sai lầm phổ biến khác là sử dụng hàm trên cột đã được index trong mệnh đề WHERE, điều này khiến index không được dùng:

sql
-- This WILL use an index on users.email
SELECT * FROM users WHERE email = 'alice@example.com';
-- This WILL NOT use that index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

Nếu cần tra cứu không phân biệt hoa thường, bạn có thể lưu một cột đã chuẩn hoá hoặc tạo functional index: CREATE INDEX ON users (lower(email)).

Tóm tắt

Việc đánh index hiệu quả trong PostgreSQL phụ thuộc vào việc khớp đúng loại index và thứ tự cột với các mẫu truy vấn thực tế. B-tree xử lý hầu hết các truy vấn so sánh bằng và truy vấn khoảng. Composite index kết hợp cả lọc và sắp xếp khi cột dẫn đầu bị ràng buộc. Partial index thu nhỏ phạm vi index chỉ còn những dòng thực sự cần thiết. EXPLAIN ANALYZE là vòng phản hồi của bạn — luôn chạy nó trước và sau khi thêm index để xác nhận execution plan thực sự thay đổi.

Luyện tập sau buổi học

Bài kiểm tra cuối bài

Ôn lại kiến thức, thử sức và nhận góp ý cho bài làm.

0/1
Đã trả lời · 0/1