Index hoạt động thế nào

Bạn sẽ học được

• Index là gì dưới góc nhìn của một cuốn sách có mục lục.
• Vì sao truy vấn trên bảng 16 dòng vẫn quét toàn bảng dù đã có index.
• Đo khác biệt thật giữa Seq Scan và Index Scan trên 200.000 dòng.
• Chọn cột để đánh index: độ chọn lọc, khóa ngoại, thứ tự cột trong index tổ hợp.
• Cái giá phải trả: dung lượng, tốc độ ghi, và index không được dùng.

Khái niệm cốt lõi

Index giống mục lục cuối sách. Không có mục lục, muốn tìm mọi chỗ nói về "retention", bạn phải lật từng trang. Có mục lục, bạn nhảy thẳng đến trang cần đọc. Nhưng mục lục cũng tốn giấy, và mỗi lần thêm một chương mới bạn phải cập nhật nó — đó chính là cái giá của index khi ghi dữ liệu.

PostgreSQL mặc định dựng B-tree: một cây cân bằng, mỗi nút giữ nhiều khóa đã sắp xếp, độ sâu thường 3–4 tầng kể cả với hàng chục triệu dòng. Tìm một khóa mất vài lần đọc trang thay vì quét toàn bộ bảng.

Trong dataset retail:

Đánh index lên orders(customer_id) rồi chạy EXPLAIN:

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 2;

Kế hoạch vẫn là:

Seq Scan on orders  (cost=0.00..1.20 rows=1 width=44)
  Filter: (customer_id = 2)

Planner bỏ qua index. Đây không phải lỗi cấu hình mà là quyết định đúng: toàn bộ bảng chỉ nằm trong một trang (8 kB). Đọc một trang là xong; đi qua index rồi quay lại bảng tốn nhiều thao tác hơn. Với bảng nhỏ, Seq Scan luôn thắng.

Bài học đầu tiên, và cũng là bài học khiến nhiều người mất niềm tin vào index: index chỉ có ý nghĩa khi bảng đủ lớn và điều kiện đủ chọn lọc.

Đo trên bảng lớn

Muốn thấy index làm việc, phải có dữ liệu. Tạo bảng 200.000 dòng:

CREATE TABLE events (
  id integer PRIMARY KEY,
  user_id integer NOT NULL,
  kind text NOT NULL,
  created_at timestamp NOT NULL
);

INSERT INTO events
SELECT g,
       (g % 5000) + 1,
       CASE WHEN g % 3 = 0 THEN 'click' ELSE 'view' END,
       timestamp '2025-01-01' + (g || ' minutes')::interval
FROM generate_series(1, 200000) g;

Năm nghìn người dùng, mỗi người khoảng 40 sự kiện. Chưa có index trên user_id:

Aggregate  (cost=2979.68..2979.69 rows=1 width=8) (actual time=9.793..9.794 rows=1.00 loops=1)
  Buffers: shared hit=1274
  ->  Seq Scan on events  (cost=0.00..2977.98 rows=682 width=0) (actual time=0.012..9.777 rows=40.00 loops=1)
        Filter: (user_id = 42)
        Rows Removed by Filter: 199960
        Buffers: shared hit=1274
Execution Time: 9.819 ms

Đọc 1.274 trang, loại 199.960 dòng để lấy 40 dòng đúng. Sau khi tạo index:

CREATE INDEX idx_events_user ON events(user_id);

Aggregate  (cost=1282.62..1282.63 rows=1 width=8) (actual time=0.066..0.067 rows=1)
  Buffers: shared hit=40 read=2
  ->  Bitmap Heap Scan on events  (cost=12.04..1280.12 rows=1000 width=0) (actual time=0.020..0.056 rows=40.00 loops=1)
        Recheck Cond: (user_id = 42)
        Heap Blocks: exact=40
        ->  Bitmap Index Scan on idx_events_user  (cost=0.00..11.79 rows=1000 width=0)
              Index Cond: (user_id = 42)
Execution Time: 0.082 ms

Từ 9,8 ms xuống 0,08 ms, từ 1.274 trang xuống 40 trang. Hai điều đáng chú ý trong kế hoạch:

• Bitmap Index Scan gom các vị trí dòng từ index thành bitmap, rồi Bitmap Heap Scan đọc các trang chứa chúng theo thứ tự vật lý. Cách này tránh nhảy trang lung tung khi số dòng khớp còn nhiều.
• Recheck Cond xuất hiện vì bitmap chỉ lưu vị trí, không lưu giá trị. PostgreSQL kiểm tra lại điều kiện sau khi đọc dòng để chắc chắn.

Khi số dòng khớp rất ít, planner có thể chọn Index Scan thuần thay vì bitmap. Ba loại bạn sẽ gặp:

Index Only Scan chỉ khả thi khi mọi cột cần dùng đều nằm trong index. Vì vậy SELECT id FROM orders WHERE customer_id = 2 có thể dùng index thuần, còn SELECT * thì không.

Chọn cột để index

Ba tiêu chí, theo thứ tự quan trọng:

Một, độ chọn lọc. Cột có nhiều giá trị khác nhau và điều kiện = trả về ít dòng là ứng viên tốt: email, order_id, user_id. Cột status với ba giá trị completed/pending/cancelled có độ chọn lọc thấp — index trên đó thường bị bỏ qua vì một giá trị chiếm phần lớn bảng.

Hai, khóa ngoại. PostgreSQL không tự tạo index cho khóa ngoại. order_items.order_id không có index thì mỗi lần xoá một đơn, PostgreSQL phải quét toàn bộ order_items để kiểm tra ràng buộc. Đây là nguyên nhân kinh điển khiến thao tác xoá chậm dần theo thời gian.

Ba, thứ tự trong index tổ hợp. CREATE INDEX ON events(user_id, created_at DESC) phục vụ được cả WHERE user_id = ? lẫn ORDER BY created_at DESC LIMIT 10 — và kế hoạch thật cho thấy nó đọc đúng 10 dòng:

Limit  (cost=0.42..29.52 rows=10 width=48) (actual time=0.015..0.027 rows=10.00 loops=1)
  ->  Index Scan using idx_events_user_created on events
        Index Cond: (user_id = 42)
Execution Time: 0.039 ms

Ngược lại, CREATE INDEX ON events(created_at, user_id) không giúp được truy vấn này, vì cột dẫn đầu là created_at chứ không phải user_id. Quy tắc: cột dùng trong = đứng trước, cột dùng trong sắp xếp hoặc khoảng đứng sau.

Cái giá của index

Index không miễn phí. Mỗi index:

• Chiếm dung lượng đĩa, có khi bằng 20–30% bảng.
• Làm chậm INSERT, UPDATE, DELETE vì mọi index liên quan phải được cập nhật trong cùng giao dịch.
• Có thể bị bỏ quên: index không dùng vẫn tốn dung lượng và vẫn làm chậm ghi.

Trong môi trường học, mỗi lần chạy bài là một cơ sở dữ liệu mới, nên CREATE INDEX ở trên chỉ tồn tại trong phiên hiện tại. Đó cũng là lý do các bài luyện không phụ thuộc vào index: dữ liệu mẫu quá nhỏ để planner dùng đến chúng.

Thực hành

1. Chạy EXPLAIN SELECT * FROM orders WHERE customer_id = 2; trước và sau khi tạo index. Xác nhận cả hai đều Seq Scan và giải thích vì sao.
2. Tạo bảng events 200.000 dòng như trên, đo Seq Scan và Index Scan, ghi lại số Buffers của mỗi bên.
3. Đổi index thành (user_id, created_at DESC) và chạy truy vấn lấy 10 sự kiện mới nhất của một người. Tìm Index Scan trong kế hoạch.
4. Tạo index (created_at, user_id) rồi chạy lại câu trên. Giải thích vì sao planner không dùng nó cho WHERE user_id = ....
5. Liệt kê ba cột trong dataset retail mà bạn sẽ đánh index nếu bảng lớn gấp một triệu lần.

Lỗi thường gặp

Tóm tắt

• Index là mục lục có sắp xếp; B-tree tìm trong vài lần đọc trang.
• Bảng nhỏ thì Seq Scan đúng — planner bỏ qua index là hành vi hợp lý.
• Trên 200.000 dòng, index giảm 9,8 ms xuống 0,08 ms và 1.274 trang xuống 40.
• Index tổ hợp: cột dùng = đứng trước, cột sắp xếp đứng sau.
• Index có giá: dung lượng và tốc độ ghi. Chỉ tạo khi truy vấn chứng minh được lợi ích.