Truy vấn chậm? Cách đọc EXPLAIN và đặt index đúng chỗ

Index giống mục lục của cuốn sách

Bạn cần tìm chữ "Redis" trong một cuốn sách 300 trang. Cách chậm: lật từng trang từ đầu. Cách nhanh: mở mục lục ở cuối sách, thấy "Redis: trang 211", mở thẳng. Index chính là cái mục lục đó, nhưng cho một bảng dữ liệu.

Database có thể đọc tất cả 300.000 dòng để tìm một khách (gọi là quét toàn bảng), hoặc đi theo index để tới đúng chỗ. Chênh lệch nghe không to khi bảng nhỏ, và khổng lồ khi bảng lớn.

Mục lục cũng có giá. Mỗi lần thêm hoặc sửa một đơn, database phải cập nhật cả mục lục. Càng nhiều index, việc ghi càng chậm. Vì thế mục tiêu không phải "index mọi cột", mà là đúng những index mà câu truy vấn thật sự dùng tới.

EXPLAIN là cách hỏi database "bạn định làm gì?"

Gõ EXPLAIN trước câu SELECT, database không chạy câu đó mà kể kế hoạch. Có bốn thứ cần đọc:

• type: cách nó tìm. ALL nghĩa là đọc cả bảng (xấu). ref, range nghĩa là đi theo index (tốt).
• key: index nó chọn. NULL nghĩa là không dùng index nào.
• rows: số dòng nó dự đoán phải đọc.
• Extra: ghi chú. Using filesort là phải sắp xếp thêm bên ngoài index. Using index là trả lời được hoàn toàn từ index, rất tốt.

Con số rows chỉ là dự đoán, và có lúc dự đoán sai nặng. Vì vậy mình luôn chạy thêm ANALYZE (của MariaDB, MySQL 8 có EXPLAIN ANALYZE): nó chạy câu truy vấn thật rồi báo số dòng đã đọc và thời gian. Phần phân trang bên dưới có thêm một ví dụ cho thấy vì sao.

Làm thế nào

1. Tìm câu truy vấn thật sự chậm từ ứng dụng hoặc từ nhật ký truy vấn chậm (slow query log). Đừng tối ưu theo cảm giác.
2. Chạy EXPLAIN, nhìn type, key, rows, Extra. Rồi chạy ANALYZE để xem số thật.
3. Sửa câu truy vấn trước, vì sửa không tốn gì: bỏ hàm bọc quanh cột, so sánh đúng kiểu dữ liệu.
4. Nếu vẫn cần, thêm index. Với điều kiện nhiều cột: cột so bằng (=) đứng trước, cột khoảng (>, <) đứng sau, rồi mới đến cột chỉ dùng để sắp xếp.
5. Chạy lại ANALYZE để chắc index thật sự được dùng, và nhớ rằng mỗi index mới làm việc ghi chậm thêm.
6. Thử trên lượng dữ liệu gần thật. Bảng vài trăm dòng thì cái gì cũng nhanh.

Cho bạn nào viết code

Bảng thử của mình:

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,   -- 0..4, trạng thái 0 chiếm một nửa
  total       DECIMAL(10,2) NOT NULL,
  created_at  DATETIME NOT NULL,           -- trải đều trong 2 năm
  email       VARCHAR(100) NOT NULL,
  phone       VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

300.000 dòng, 20.000 khách. Mọi thời gian dưới đây lấy từ ANALYZE (lần nhanh nhất trong ba lần chạy, dữ liệu đã nằm sẵn trong bộ nhớ), nên chỉ so tương đối với nhau, đừng đem so với máy của bạn.

Một cột, một index

Truy vấn SELECT id, total FROM orders WHERE customer_id = 3396 khi chưa có index: type=ALL, đọc cả 300.000 dòng, khoảng 110 ms. Sau ALTER TABLE orders ADD INDEX idx_customer (customer_id): type=ref, đọc 32 dòng, khoảng 0,1 ms. Chênh cỡ nghìn lần với một cột và một câu lệnh.

Thứ tự cột trong index nhiều cột

Đếm đơn trạng thái 4 trong nửa năm đầu 2026:

SELECT COUNT(*) FROM orders
WHERE status = 4 AND created_at >= '2026-01-01' AND created_at < '2026-07-01';

Cùng dữ liệu, cùng truy vấn, chỉ đổi thứ tự cột mà chênh gần 8 lần. Lý do: index đặt khoảng thời gian lên trước buộc database đọc hết mọi mục trong khoảng đó rồi mới lọc status. Index đặt status trước thì nhảy thẳng tới đúng nhóm. Cũng vì index sắp theo cột đầu, nên index (status, created_at) không giúp được truy vấn chỉ lọc theo created_at: mình thử và EXPLAIN báo type=ALL kèm Using filesort, khoảng 130 ms.

Index "đủ cả bữa"

Nếu mọi cột câu truy vấn cần đều nằm sẵn trong index, database khỏi phải chạm vào bảng (Extra ghi Using index). Truy vấn SELECT customer_id, created_at, total ... WHERE customer_id = 3396 ORDER BY created_at với index (customer_id) có Using filesort, khoảng 0,15 ms. Với (customer_id, created_at, total) chỉ còn Using index, khoảng 0,03 ms. Dùng cho truy vấn nóng và hẹp thôi, vì mỗi cột thêm làm index phình ra.

Khi index "tàng hình"

Đây là những lỗi làm database không dùng được index dù index có sẵn:

-- Bọc hàm quanh cột: ALL, khoảng 249 ms
SELECT id, total FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2026-03-03';
-- Viết lại thành khoảng: range, 398 dòng, khoảng 1,5 ms
SELECT id, total FROM orders WHERE created_at >= '2026-03-03' AND created_at < '2026-03-04';

Quy tắc là để cột trần ở một vế, đưa phép tính sang vế bên kia. Lỗi thứ hai là so sai kiểu: cột phone là chuỗi, mà WHERE phone = 912345678 (số, không có nháy) buộc database ép từng giá trị sang số để so, mất khoảng 166 ms. Viết '0912345678' có nháy thì khoảng 0,02 ms. Hai câu nhìn gần y hệt nhau. Lỗi thứ ba là dấu % đứng đầu: LIKE 'user12345%' đi theo index, 0,04 ms, còn LIKE '%12345@example.com' phải đọc cả index, khoảng 253 ms. Index sắp theo đầu chữ, nên không thể tìm theo đuôi.

Phân trang sâu

LIMIT 20 OFFSET 250000 không nhảy tới dòng thứ 250.000: database vẫn đọc rồi bỏ 250.000 dòng đầu. Mình đo khoảng 164 ms, kèm Using filesort, và càng về sau càng chậm. Cách khác là phân trang theo mốc (keyset): thay vì đếm bao nhiêu dòng, nói "lấy các dòng đứng sau dòng cuối của trang trước".

SELECT id, total, created_at FROM orders
WHERE created_at < '2025-05-02 22:39:43'
   OR (created_at = '2025-05-02 22:39:43' AND id < 251509)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Với index (created_at, id): type=range, đọc đúng 20 dòng, khoảng 0,1 ms, tức hơn một nghìn lần nhanh hơn ở cùng vị trí. Cần cột phụ id vì nhiều dòng có thể trùng created_at; thiếu nó, bạn mất hoặc lặp dòng ở ranh giới trang. Đổi lại, keyset không cho nhảy thẳng tới "trang 5000". Nó hợp cho cuộn vô hạn và nút "trang sau".

Lỗi hay gặp

Có người thêm index cho mọi cột "phòng khi cần". Kết quả là việc ghi chậm, đĩa phình, và trình tối ưu khó chọn đúng index. Nếu hai index (a) và (a, b) cùng có, cái đầu thường thừa.

Một lỗi khác là đặt index trên cột chỉ có vài giá trị khác nhau như status hoặc cờ true/false, rồi ngạc nhiên vì database không dùng. Cột như vậy chỉ có ích khi đứng đầu một index nhiều cột, luôn đi kèm điều kiện khác.

Hay gặp nhất là tin vào cột rows mà không chạy ANALYZE. Mình từng thử viết phân trang bằng so sánh hàng WHERE (created_at, id) < (..., ...). Trên MariaDB 11.8, EXPLAIN báo rows=20, nhưng ANALYZE cho thấy thực tế nó đọc 250.020 dòng và mất hơn nửa giây, tệ hơn cả OFFSET. Dạng OR như trên mới đúng.

Nên làm gì tiếp theo

Lấy một câu truy vấn chậm nhất của bạn, chạy EXPLAIN rồi ANALYZE, và đọc cột type. Nếu thấy ALL, hãy tìm hàm bọc quanh cột hoặc sai kiểu dữ liệu trước khi nghĩ tới index. Và nếu sau tất cả câu truy vấn vẫn đắt, đặt một lớp nhớ tạm phía trước, như trong bài Cho web nhanh hơn bằng Redis: nhớ tạm kết quả để database đỡ quá tải, thường rẻ hơn việc chỉnh thêm. Phân trang bằng mốc cũng là cách các API nên trả danh sách, xem Thiết kế API cho người khác dùng: báo lỗi, chia trang, thử lại và giới hạn lượt gọi.