Database Index: tại sao query chậm và index cứu bạn như phép màu?

Index là gì

Index là cấu trúc dữ liệu (thường là B-Tree) giúp tìm dòng theo khóa rất nhanh. Không có index, database phải quét toàn bộ bảng để lọc điều kiện. Có index, DB duyệt cây và nhảy đến block dữ liệu tương ứng.

Vì sao thêm index lại tăng tốc

• Giảm số trang dữ liệu phải đọc.
• Cho phép “seek” theo giá trị thay vì “scan” toàn bộ.
• Hỗ trợ sắp xếp và nhóm dữ liệu nhanh hơn khi order/group trùng cột index.

Chi phí của index

• Tăng dung lượng lưu trữ.
• Ghi chậm hơn một chút vì phải cập nhật index khi INSERT/UPDATE/DELETE.
• Index sai cột hoặc thừa thãi có thể làm query chậm hơn do optimizer chọn kế hoạch không tối ưu.

Ví dụ 1: Tìm theo khóa ngoại — cứu ngay SELECT chậm

Giả sử có bảng đơn hàng:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  total DECIMAL(10,2) NOT NULL
);
-- Không có index nào ngoài PRIMARY KEY(id)

Query chậm:

-- Tìm đơn hàng gần đây của 1 user
SELECT id, total, created_at
FROM orders
WHERE user_id = 12345
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

Vì user_id chưa có index, DB phải quét toàn bảng. Sửa bằng cách thêm index composite đúng thứ tự lọc:

-- Index dẫn đường theo WHERE rồi phục vụ luôn ORDER BY
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

Lợi ích:

• Filter theo user_id bằng index.
• Đã có created_at trong index nên ORDER BY DESC có thể sử dụng index scan ngược.
• LIMIT 20 dừng rất sớm, giảm I/O mạnh.

Ví dụ 2: WHERE + ORDER BY khác cột — chọn thứ tự cột trong index

Query:

SELECT id, user_id, total
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

Chúng ta có hai điều kiện: lọc theo status và sắp xếp theo created_at. Hãy tạo index sao cho vừa lọc tốt vừa sắp xếp nhanh:

-- Thứ tự quan trọng: điều kiện lọc trước, rồi đến cột sort
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

Vì nhiều database không lưu hướng DESC trong B-Tree theo cách rõ ràng, vẫn dùng được cho cả ASC/DESC. Quan trọng là (status, created_at) giúp vừa lọc vừa sắp xếp mà không cần filesort.

Ví dụ 3: Covering index — đọc xong ngay trong index

Nếu SELECT chỉ trả về các cột nằm trong index, DB có thể “cover” luôn kết quả ở tầng index, không cần về bảng gốc:

-- Thường xuyên chạy:
SELECT created_at, total
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 10;

-- Tạo covering index chứa đủ cột dùng ở WHERE/ORDER BY/SELECT
CREATE INDEX idx_orders_user_created_total ON orders (user_id, created_at, total);

Giờ DB chỉ đọc index là đủ, giảm random I/O vào bảng.

Ví dụ 4: LIKE prefix vs LIKE có wildcard đầu — INDEX giúp hay bó tay

Với tìm kiếm tiền tố, index hoạt động tốt:

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  name  VARCHAR(200) NOT NULL
);

CREATE INDEX idx_users_email ON users (email);

-- Khai thác index (tiền tố)
SELECT id, email FROM users WHERE email LIKE 'hao%@example.com';

Nhưng với LIKE '%hao%', index B-Tree không dùng được vì wildcard đầu chuỗi phá tiền tố. Lúc này cân nhắc Full-Text Index hoặc search engine chuyên dụng.

Ví dụ 5: JOIN nhanh khi có index đúng cột khóa

Hai bảng:

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL
);

JOIN chậm nếu orders.user_id không có index:

SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 12345;

Thêm index khóa ngoại:

CREATE INDEX idx_orders_user ON orders (user_id);

Giờ JOIN chỉ cần seek theo user_id, không scan cả bảng orders.

Ví dụ 6: Partial/Filtered index (PostgreSQL) để nhỏ gọn và nhanh

Nếu 90% query chỉ đụng dữ liệu “đang hoạt động”, lọc theo trạng thái:

-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_orders_paid_recent
  ON orders (created_at DESC)
  WHERE status = 'PAID' AND created_at > now() - interval '90 days';

Index nhỏ hơn, scan nhanh hơn và giảm chi phí ghi.

Ví dụ 7: Sai lầm phổ biến — cột trong hàm/biểu thức làm “mù” index

Query:

-- created_at có index, nhưng ta trói nó vào hàm nên index khó dùng
SELECT COUNT(*)
FROM orders
WHERE DATE(created_at) = '2025-10-01';

Viết lại dạng “sargable” để DB dùng index:

SELECT COUNT(*)
FROM orders
WHERE created_at >= '2025-10-01' AND created_at < '2025-10-02';

Ví dụ 8: Thứ tự cột trong composite index

Index đa cột chỉ hỗ trợ tốt từ cột trái sang cột phải. Nếu WHERE lọc theo user_id và status nhưng hầu hết chọn theo user_id trước, hãy đặt user_id ở đầu:

-- Tối ưu cho WHERE user_id = ? AND status = ?
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at);

Nếu đa số query bắt đầu bằng status thì đảo thứ tự cho phù hợp. Hãy để dữ liệu thực tế (cardinality, tần suất query) dẫn dắt.

EXPLAIN: cách kiểm chứng index có được dùng

Dùng EXPLAIN để xem kế hoạch thực thi:

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

Kỳ vọng:

• type hoặc access: index / range (thay vì ALL/seq scan).
• key: tên index bạn tạo.
• rows ước tính nhỏ hơn đáng kể.

Checklist chọn cột để tạo index

• Các cột xuất hiện sau WHERE, JOIN ON, ORDER BY, GROUP BY.
• Ưu tiên cột có tính phân biệt cao (cardinality lớn).
• Dùng composite index cho nhiều điều kiện cùng lúc, sắp xếp cột theo trình tự lọc.
• Cân nhắc covering index cho query nóng, tránh quay về bảng.
• Viết điều kiện sargable, tránh bọc cột trong hàm.

Quy trình tối ưu một query chậm

1. EXPLAIN để xem DB đang làm gì.
2. Xác định điều kiện lọc và sắp xếp chính.
3. Thêm hoặc điều chỉnh index (đúng thứ tự cột).
4. Viết lại query cho sargable nếu cần.
5. EXPLAIN lại, so sánh rows đọc và thời gian thực thi.

Kết luận

Index không phải phép thuật, nhưng khi hiểu đúng, hiệu quả giống như phép màu. Chìa khóa là chọn đúng cột, đúng thứ tự và viết query thân thiện với index. Trước khi nghĩ đến việc nâng cấp server, hãy tối ưu index và kiểm tra EXPLAIN — bạn thường sẽ có kết quả tức thì.