Index là gì và khi nào nên thêm

By Better Wise

Đây là bài mở đầu của module đọc hiệu năng truy vấn AI viết. Bạn đã biết một chút SQL và đã từng dùng app có database, thế là đủ để bắt đầu. Cả module xoay quanh một câu hỏi duy nhất: truy vấn AI vừa sinh ra nhanh hay chậm, và nó có dùng index hay không. Bài này lo phần gốc: index là gì, và khi nào đáng thêm.

Sau khoảng 20 phút, bạn sẽ:

Giải thích được index bằng hình ảnh mục lục của một cuốn sách.
Nhận ra đoạn code AI viết có khả năng phải quét cả bảng.
Biết ba dấu hiệu nên thêm index và ba dấu hiệu nên bỏ qua.
Viết được câu CREATE INDEX đúng cột, đúng thứ tự cho một truy vấn cụ thể.

Index là mục lục của bảng

Hãy tưởng tượng một cuốn sách 500 trang và bạn cần tìm mọi chỗ nói về "transaction". Không có mục lục, bạn lật từng trang. Có mục lục, bạn tra một chỗ rồi nhảy thẳng tới trang cần đọc. Database cũng vậy.

Index là một cấu trúc dữ liệu phụ (thường là B-tree) lưu giá trị của một hoặc nhiều cột đã sắp xếp sẵn, kèm con trỏ tới dòng gốc trong bảng. Khi truy vấn lọc theo cột đó, database đọc index (nhỏ, có thứ tự) rồi nhảy tới đúng dòng, thay vì đọc hết bảng. Kiểu đọc hết bảng gọi là full table scan, và đó là lý do quen thuộc khiến một truy vấn "chạy được trên máy dev" lại treo trên production.

Vài chi tiết dễ nhầm:

Primary key luôn có index sẵn, không cần tạo.
Foreign key thì không tự có index, ở cả MySQL lẫn PostgreSQL.
Một bảng có thể có nhiều index, mỗi index là một bản sao có thứ tự của dữ liệu.

Và đây là câu quan trọng nhất bài: mỗi index thêm vào làm mọi thao tác _____ (INSERT, UPDATE, DELETE) chậm hơn, vì database phải cập nhật thêm cấu trúc đó. Index không miễn phí.

Đo trước khi đoán: một truy vấn AI viết và hai lần chạy

Giả sử AI viết cho bạn chức năng đăng nhập trên bảng users có 2 triệu dòng:

SELECT id, full_name
FROM users
WHERE email = '[email protected]';

Nhìn qua thì hợp lý, nhưng trên users chưa có index nào cho email. Không có index, database phải _____ (full table scan) cho mỗi lần đăng nhập. Trên máy dev vài nghìn dòng, bạn không thấy gì. Trên production, mỗi request đọc 2 triệu dòng.

Theo tinh thần đo trước khi đoán, bước một là đo thời gian thực của truy vấn đó (log slow query, hoặc bấm giờ ở tầng ứng dụng). Bước hai mới là thêm index:

CREATE INDEX idx_users_email ON users (email);

Bước ba, bắt buộc: chạy lại truy vấn và đo lại. Nếu thời gian không đổi, index của bạn không được dùng, và bài sau (EXPLAIN) sẽ chỉ cách nhìn ra điều đó.

Composite index: thứ tự cột quan trọng hơn số cột

Index một cột là mục lục một chiều. Khi truy vấn vừa lọc vừa sắp xếp, bạn cần index nhiều cột, gọi là composite index:

SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Quy tắc thực dụng: cột dùng điều kiện bằng (equality) đứng trước, cột dùng sắp xếp hoặc khoảng đứng sau. Với composite index, cột equality nên đứng _____, cột dùng ORDER BY đứng sau. Lý do giống danh bạ điện thoại: sắp theo (họ, tên) thì tra theo họ rất nhanh, tra theo tên thì gần như vô dụng.

Hệ quả cần nhớ: index (customer_id, created_at) không giúp truy vấn chỉ lọc created_at. Muốn vậy phải có index riêng trên created_at.

flowchart TD
    A["🐢<br/>Truy vấn AI vừa sinh"] --> B{"Có cột trong WHERE, JOIN, ORDER BY?"}
    B -- Không --> C["Chưa cần index"]
    B -- Có --> D{"Bảng lớn và truy vấn chạy thường xuyên?"}
    D -- Không --> C
    D -- Có --> E["Đo thời gian thật trước"]
    E --> F["Tạo index đúng cột, đúng thứ tự"]
    F --> G["Đo lại và so sánh"]

Khi nào không nên thêm index

Thêm index là một quyết định đánh đổi, không phải một nghi thức. Bốn trường hợp nên bỏ qua:

Tình huống	Vì sao bỏ qua
Bảng chỉ vài trăm dòng	Đọc cả bảng đã đủ nhanh, index chỉ thêm chi phí
Cột ít giá trị khác nhau (giới tính, trạng thái `active`)	Độ chọn lọc (selectivity) thấp, index lọc không được bao nhiêu dòng
Bảng ghi nhiều, đọc ít	Mỗi lần ghi phải cập nhật mọi index, ghi sẽ chậm đi
Index đã có sẵn cùng cột đó	Index trùng lặp chỉ tốn chỗ và tốn thời gian ghi

Ranh giới nằm ở chỗ: index đáng tiền khi nó lọc được một phần nhỏ của bảng lớn, và truy vấn đó chạy thường xuyên.

Mẹo và lỗi thường gặp khi thêm index

Lỗi phổ biến nhất của AI là bỏ sót index trên cột khóa ngoại. Thấy JOIN ... ON orders.customer_id = customers.id thì kiểm tra ngay orders.customer_id có index chưa. Hầu như lần nào cũng chưa.
Đặt tên index có quy ước, ví dụ idx_<bảng>_<cột>, để lần sau bạn và AI không tạo trùng.
Thêm index bằng migration, đừng sửa tay trên production. Bài về migration sẽ nói kỹ, ở đây chỉ cần nhớ: index là một phần của schema.
LIKE '%abc%' gần như không dùng được index thường. Đừng thêm index rồi tự hỏi sao không nhanh hơn.
Đừng thêm index cho mọi cột "cho chắc". Bảng ghi nhiều mà có 8 index sẽ chậm hơn bảng chỉ có 2 index đúng chỗ.
Câu thần chú giữ bạn khỏi đoán mò: đo trước khi đoán, và đo lại sau khi sửa.

Trước khi sang bài EXPLAIN

Ba điều mang theo:

Index là _____ của bảng, giúp tìm dòng mà không phải đọc hết bảng.
Thêm index khi truy vấn lọc, JOIN hoặc sắp xếp trên cột của một bảng lớn và chạy thường xuyên.
Mỗi index có giá: dung lượng đĩa và tốc độ ghi.

Bạn giờ đã biết khi nào nên thêm index. Nhưng làm sao biết database thật sự đã dùng index bạn vừa tạo? Đó chính là nội dung bài tiếp theo: đọc EXPLAIN cơ bản, mở chậm một truy vấn và nhìn xem nó quét bao nhiêu dòng.

Front: Index trong database hoạt động giống gì? Back: Giống mục lục của cuốn sách: tra vị trí dòng mà không cần đọc hết bảng.

Front: Full table scan là gì? Back: Database đọc từng dòng của cả bảng để tìm dữ liệu, thường xảy ra khi thiếu index phù hợp.

Front: Truy vấn nào thường đáng thêm index? Back: Truy vấn lọc (WHERE), JOIN hoặc sắp xếp (ORDER BY) trên cột của bảng lớn và chạy thường xuyên.

Front: Cái giá của mỗi index là gì? Back: Tốn dung lượng đĩa và làm INSERT, UPDATE, DELETE chậm hơn vì phải cập nhật thêm cấu trúc.

Front: Composite index là gì? Back: Index trên nhiều cột, trong đó thứ tự cột quyết định index có dùng được hay không.

Front: Với composite index, cột nào nên đứng trước? Back: Cột dùng điều kiện bằng trong WHERE, rồi mới tới cột dùng ORDER BY hoặc khoảng giá trị.

Front: Index (customer_id, created_at) có giúp truy vấn chỉ lọc created_at không? Back: Không. Muốn lọc riêng created_at thì cần một index riêng trên cột đó.

Front: Bảng chỉ vài trăm dòng có cần index không? Back: Thường không, vì đọc cả bảng đã đủ nhanh và index chỉ thêm chi phí ghi.

Front: Cột có ít giá trị khác nhau như cột giới tính có nên đánh index? Back: Thường không, vì độ chọn lọc (selectivity) thấp, index không lọc được nhiều dòng.

Front: Foreign key có tự động được đánh index không? Back: Không. Ở MySQL và PostgreSQL bạn phải tự tạo index cho cột khóa ngoại nếu lọc hoặc JOIN nhiều trên đó.

Front: Primary key có sẵn index không? Back: Có, primary key luôn được đánh index sẵn.

Front: AI hay bỏ sót index ở đâu nhất? Back: Ở cột khóa ngoại và các cột dùng để lọc, JOIN hoặc ORDER BY trong truy vấn nó vừa sinh ra.

Front: Vì sao index giúp ORDER BY kèm LIMIT nhanh hơn? Back: Vì dữ liệu trong index đã có thứ tự, database chỉ đọc vài dòng đầu thay vì sắp xếp cả bảng.

Index là một cấu trúc dữ liệu phụ lưu giá trị của một hoặc nhiều cột đã _____ sẵn, kèm con trỏ tới dòng gốc.