Khi câu lệnh SELECT mất 4 giây: Kỹ thuật Indexing cứu nguy bảng 10 triệu dòng

Lúc ứng dụng mới có vài ngàn người dùng, mọi câu truy vấn SQL đều chạy dưới 5 mili-giây. Nhưng khi bảng dữ liệu chạm mốc vài triệu đến chục triệu bản ghi, các câu lệnh tra cứu đơn hàng hay lịch sử giao dịch bắt đầu biến thành “cơn ác mộng” với thời gian phản hồi vọt lên 3–5 giây, kéo theo CPU database chạm trần 100%.

Phản xạ đầu tiên của nhiều đội ngũ là nâng cấp gói server (scale up RAM/CPU) – một cách chữa cháy vừa tốn tiền vừa không giải quyết được gốc rễ. Dưới đây là 3 kỹ thuật tối ưu truy vấn và thiết kế Index chuẩn mực giúp kéo thời gian thực thi từ 4 giây xuống dưới 20 mili-giây.

1. Đọc đúng bản đồ EXPLAIN ANALYZE: Tìm kẻ giết chết hiệu năng

Đừng bao giờ đoán mò lý do truy vấn chậm. Việc đầu tiên trước khi sửa bất kỳ dòng code nào là chạy lệnh chẩn đoán trực tiếp trên database:

EXPLAIN ANALYZE 
SELECT id, total_amount, created_at 
FROM orders 
WHERE user_id = 'usr_9988' AND status = 'COMPLETED' 
ORDER BY created_at DESC 
LIMIT 20;

Nếu kết quả trả về xuất hiện dòng:

• Seq Scan (Sequential Scan) on orders: Database đang phải duyệt tuần tự qua toàn bộ 10 triệu dòng trên đĩa cứng để tìm dữ liệu. Đây là nguyên nhân trực tiếp làm sập server.
• Mục tiêu cần đạt: Biến nó thành Index Scan hoặc Index Only Scan.

2. Composite Index: Quy tắc “Đẳng thức trước, Dải sau” (Equality First, Range Next)

Sai lầm phổ biến nhất khi tạo chỉ mục là đánh index riêng lẻ cho từng cột: 1 cái cho user_id, 1 cái cho status, 1 cái cho created_at. Khi chạy, database chỉ chọn được duy nhất 1 index và vẫn phải tốn tài nguyên lọc phần còn lại.

Bạn cần một Composite Index (Chỉ mục kết hợp), nhưng thứ tự các cột đặt trong index quyết định sống còn việc nó có hoạt động hay không:

-- SAI: Đặt cột thời gian/dải (Range) trước
CREATE INDEX idx_orders_bad ON orders (created_at, user_id, status);

-- ĐÚNG: Cột so sánh chính xác (=) đưa lên đầu, cột sắp xếp/khoảng thời gian đưa ra sau cùng
CREATE INDEX idx_orders_optimized ON orders (user_id, status, created_at DESC);

Tại sao? B-Tree index hoạt động giống như một cuốn danh bạ điện thoại được sắp xếp theo [Họ] -> [Tên] -> [Số nhà]. Nếu bạn tìm theo Số nhà trước, bạn buộc phải lật từng trang. Đặt user_id và status (so sánh bằng) ở đầu giúp database nhảy thẳng tới đúng nút lá chứa tập dữ liệu cần lấy, sau đó đọc ngay 20 dòng theo thứ tự created_at mà không tốn công sắp xếp lại (loại bỏ hoàn toàn bước Sort nặng nề).

3. Thoát khỏi bẫy OFFSET khi phân trang lớn

Khi làm tính năng xem lịch sử đơn hàng ở các trang sâu (ví dụ trang 5.000):

-- RẤT CHẬM: Database phải đọc và bỏ qua 100.000 dòng trước đó
SELECT * FROM orders 
WHERE user_id = 'usr_9988' 
ORDER BY id DESC 
LIMIT 20 OFFSET 100000;

Dù có index, database vẫn phải duyệt qua 100.000 con trỏ bản ghi rồi vứt đi để lấy 20 dòng cuối cùng.

Giải pháp: Chuyển sang Cursor-based Pagination (Phân trang theo con trỏ / Keyset pagination):

-- CỰC NHANH: Tận dụng trực tiếp B-Tree để nhảy tới vị trí con trỏ
SELECT * FROM orders 
WHERE user_id = 'usr_9988' AND id < 'last_seen_order_id' 
ORDER BY id DESC 
LIMIT 20;

Truy vấn này có thời gian thực thi ổn định ở mức O(1) bất kể người dùng đang xem trang thứ nhất hay trang thứ một triệu.

Ba điều cấm kỵ khi viết SQL trên bảng lớn

• Bọc hàm quanh cột được index: Câu lệnh WHERE DATE(created_at) = '2026-10-12' sẽ vô hiệu hóa hoàn toàn index trên cột created_at. Hãy viết lại thành khoảng thời gian: WHERE created_at >= '2026-10-12 00:00:00' AND created_at < '2026-10-13 00:00:00'.
• Lạm dụng SELECT *: Chỉ lấy đúng những cột giao diện cần hiển thị. Đọc dư thừa các trường text dài hoặc JSON từ ổ cứng làm nghẽn băng thông I/O và vô hiệu hóa cơ hội dùng Index Only Scan.
• Đánh index vô tội vạ: Mỗi index thêm vào là một gánh nặng cho các lệnh INSERT, UPDATE, DELETE. Với bảng có tần suất ghi cao, chỉ giữ lại những index thực sự phục vụ các truy vấn nóng.

Một truy vấn được tối ưu đúng cách bằng cấu trúc dữ liệu chuẩn mực mang lại hiệu quả gấp nhiều lần so với việc chi thêm hàng ngàn USD tiền thuê cụm máy chủ cấu hình khủng.