Index và Explain: Bí kíp tối ưu query mysql

Nếu bạn từng gặp cảnh API chạy mượt lúc test với 100 dòng dữ liệu, nhưng lên production với 1 triệu dòng thì "đơ" mất 5-10 giây — 90% nguyên nhân nằm ở thiếu index đúng chỗ. Bài viết này mình tổng hợp lại toàn bộ kiến thức thực chiến về index và cách đọc EXPLAIN, từ cơ bản đến những góc khuất hay bị bỏ sót.

1️⃣ Vì sao Index giúp query nhanh hơn?

Không có index, MySQL phải quét toàn bộ bảng (full table scan) — giống như tìm 1 cuốn sách trong thư viện không có mục lục, phải lật từng cuốn. Index giống như mục lục sách: dữ liệu được sắp xếp sẵn theo 1 cấu trúc gọi là B-Tree, giúp MySQL nhảy thẳng đến vị trí cần tìm thay vì lật từng dòng.

-- Không có index trên sku_code → quét toàn bộ bảng
SELECT * FROM skus WHERE sku_code = 'AOTHUN-DEN-M';

-- Có UNIQUE INDEX(sku_code) → tra cứu gần như tức thì
CREATE UNIQUE INDEX idx_sku_code ON skus (sku_code);

💡 Nguyên tắc vàng: Bảng càng lớn, tác dụng của index càng rõ. Với bảng vài trăm dòng, có index hay không gần như không khác biệt. Với bảng hàng triệu dòng, đây là ranh giới giữa 5ms và 5000ms.

2️⃣ Học đọc EXPLAIN — công cụ "chẩn đoán" query

Trước khi tối ưu bất kỳ query nào, việc đầu tiên luôn là chạy EXPLAIN:

EXPLAIN SELECT s.*, sp.name 
FROM skus s
JOIN spus sp ON s.spu_id = sp.id
WHERE s.spu_id = 1 AND s.status = 'active';

Output trả về nhiều cột, nhưng có 3 cột cần soi kỹ nhất theo thứ tự ưu tiên:

🎯 Cột type — cách MySQL truy cập dữ liệu

Nếu thấy type: ALL trên bảng có hàng chục nghìn dòng trở lên — dừng lại, đây gần như luôn là dấu hiệu thiếu index.

🎯 Cột Extra — nơi ẩn nhiều vấn đề nhất

Đây là cột hay bị bỏ qua nhưng lại chứa thông tin quyết định hiệu năng thực tế:

• Using index ✅ — Covering index, lấy dữ liệu chỉ từ index, không cần đọc bảng gốc
• Using where — bình thường, lọc thêm sau khi đọc dữ liệu
• Using temporary ❌ — MySQL phải tạo bảng tạm, thường do GROUP BY/DISTINCT chưa có index phù hợp
• Using filesort ❌ — MySQL phải sort thủ công vì index chưa được sắp theo đúng thứ tự ORDER BY

Using temporary và Using filesort là 2 "cờ đỏ" quan trọng nhất — query có type tốt vẫn có thể chậm nếu dính 1 trong 2 cái này.

🎯 Cột rows — số dòng MySQL ước tính phải quét

Nguyên tắc: rows càng gần với số dòng kết quả trả về thực tế càng tốt. Nếu rows = 500.000 nhưng query chỉ trả 10 dòng, index đang không hiệu quả.

💡 Với MySQL 8.0+, nên dùng EXPLAIN ANALYZE thay vì EXPLAIN thường — nó thực sự chạy query và trả về thời gian thực (ms), chính xác hơn con số ước tính của optimizer.

3️⃣ Left-most Prefix Rule — nguyên tắc sống còn của Composite Index

Đây là kiến thức nhiều dev "biết mà không hiểu tại sao", dẫn đến tạo index sai mà không nhận ra. Composite index được lưu trong B-Tree theo thứ tự ưu tiên từ trái sang phải, giống như sắp xếp danh bạ theo "Tỉnh → Huyện → Tên".

CREATE INDEX idx_spu_status_price ON skus (spu_id, status, price);

Index này chỉ phát huy hiệu quả khi query đi theo đúng thứ tự cột từ trái:

-- ✅ Dùng được (đủ cả 3 cột)
WHERE spu_id = 1 AND status = 'active' AND price > 100000

-- ✅ Dùng được (2 cột đầu)
WHERE spu_id = 1 AND status = 'active'

-- ❌ KHÔNG dùng được hiệu quả (thiếu cột trái nhất spu_id)
WHERE status = 'active' AND price > 100000

Lưu ý quan trọng: Thứ tự bạn viết trong câu WHERE không ảnh hưởng gì — MySQL Optimizer tự sắp xếp lại. Điều quyết định là cột nào có mặt trong điều kiện, không phải vị trí viết:

-- 2 cách viết này CHO KẾT QUẢ EXPLAIN GIỐNG HỆT NHAU
WHERE spu_id = 1 AND status = 'active';
WHERE status = 'active' AND spu_id = 1;

📌 Thứ tự đặt cột khi thiết kế Composite Index

1. Cột dùng với = (equality) → đặt trước
2. Cột dùng với ORDER BY/GROUP BY → đặt tiếp theo
3. Cột dùng với range (>, <, BETWEEN) → đặt cuối cùng

Nếu có nhiều cột cùng dùng =, cột nào có cardinality cao hơn (nhiều giá trị phân biệt hơn, ít lặp lại hơn) nên đứng trước — vì nó lọc bớt dữ liệu nhanh hơn ngay từ bước đầu.

-- spu_id: cardinality cao (hàng nghìn giá trị)
-- status: cardinality thấp (chỉ 2-3 giá trị: active/inactive)
-- → spu_id đứng trước status
CREATE INDEX idx_good ON skus (spu_id, status);

4️⃣ Index đơn cột vs Composite Index — đừng tạo thừa

Một sai lầm rất phổ biến: đã có composite index nhưng vẫn tạo thêm index đơn cột trùng lặp.

-- Đã có
CREATE INDEX idx_composite ON skus (spu_id, status);

-- ❌ THỪA — vì spu_id đã là cột trái nhất của idx_composite rồi
CREATE INDEX idx_spu_id_only ON skus (spu_id);

Nguyên tắc: Composite Index (A, B) đã tự động "bao gồm" khả năng của Index đơn (A). Chỉ nên tạo thêm index riêng cho cột B nếu có query thực sự filter riêng theo B mà không kèm A.

Index thừa không phải "vô hại" — nó có chi phí thật:

• Chậm mọi thao tác INSERT/UPDATE/DELETE (mỗi index phải cập nhật đồng thời)
• Tốn dung lượng đĩa và RAM (Buffer Pool)
• Optimizer mất thêm thời gian cân nhắc giữa nhiều lựa chọn

MySQL 8.0+ có sẵn công cụ soát index dư thừa:

SELECT * FROM sys.schema_redundant_indexes 
WHERE table_schema = 'your_db';

5️⃣ Covering Index — mẹo tăng tốc mạnh cho query đọc nhiều

Nếu index chứa đủ mọi cột mà query cần (cả điều kiện lẫn phần SELECT), MySQL không cần quay lại đọc bảng gốc nữa — chỉ cần đọc trên index là đủ. Đây gọi là Covering Index, thể hiện qua Extra: Using index trong EXPLAIN.

-- Query trang danh sách sản phẩm
SELECT sku_code, price, stock_quantity 
FROM skus WHERE spu_id = 1 AND status = 'active';

-- Covering index chứa đủ cả điều kiện lẫn cột SELECT
CREATE INDEX idx_covering ON skus (spu_id, status, sku_code, price, stock_quantity);

Kỹ thuật này rất mạnh nhưng tốn thêm dung lượng lưu trữ — chỉ nên áp dụng cho các query chạy rất thường xuyên (API hot path), không lạm dụng cho mọi query.

6️⃣ Những lỗi khiến Index "vô hiệu hoá" mà không hay biết

-- 1. Dùng hàm lên cột có index
WHERE YEAR(created_at) = 2026        -- ❌ mất index
WHERE created_at >= '2026-01-01'     -- ✅ dùng được index

-- 2. Wildcard đầu chuỗi trong LIKE
WHERE name LIKE '%thun%'             -- ❌ mất index
WHERE name LIKE 'Áo thun%'           -- ✅ dùng được (chỉ khi wildcard ở cuối)

-- 3. So sánh khác kiểu dữ liệu
WHERE sku_code = 12345               -- ❌ sku_code là VARCHAR, so với số → mất index

-- 4. OR giữa các cột khác index
WHERE spu_id = 1 OR brand_id = 5     -- ❌ thường gây full scan

7️⃣ Xử lý LIKE '%keyword%' — trường hợp gây đau đầu nhất

Đây là pattern cực phổ biến trong tính năng search ("tìm sản phẩm chứa từ khóa bất kỳ đâu") nhưng lại là trường hợp tệ nhất với B-Tree Index, vì MySQL không biết chuỗi bắt đầu từ đâu để tra cứu nhanh.

3 hướng giải quyết theo mức độ phức tạp tăng dần:

CREATE FULLTEXT INDEX idx_name_ft ON spus (name, description);

SELECT *, MATCH(name, description) AGAINST('áo thun' IN NATURAL LANGUAGE MODE) AS relevance
FROM spus
WHERE MATCH(name, description) AGAINST('áo thun' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

⚠️ MySQL Full-text tách từ tiếng Việt khá thô (không hiểu "áo thun" là 1 cụm từ ghép). Nếu search là tính năng cốt lõi của sản phẩm, đầu tư sang Elasticsearch với analyzer tiếng Việt (ICU) sẽ cho trải nghiệm tốt hơn hẳn.

8️⃣ Quy trình thực hành tối ưu 1 query chậm

1. Chạy EXPLAIN ANALYZE để có số liệu thực tế
2. Nhìn type — có phải ALL không? Ưu tiên fix đầu tiên
3. Nhìn Extra — có Using temporary/Using filesort không?
4. Nhìn rows — có quá lớn so với kết quả thực tế trả về không?
5. Thêm/sửa composite index theo Left-most Prefix Rule
6. Chạy lại EXPLAIN, so sánh trước-sau
7. Rà soát index thừa định kỳ bằng sys.schema_redundant_indexes

🎯 Tổng kết

Index không phải "cứ tạo càng nhiều càng nhanh" — mỗi index đều có cái giá phải trả ở chiều ghi dữ liệu. Nguyên tắc cốt lõi xuyên suốt bài viết:

• Query pattern quyết định thiết kế index, không phải suy luận lý thuyết đơn thuần
• EXPLAIN là công cụ bắt buộc trước khi tối ưu bất kỳ query nào — đừng đoán mò
• Left-most Prefix Rule là nguyên tắc nền tảng của mọi composite index
• Index thừa cũng là một dạng nợ kỹ thuật, cần rà soát định kỳ

Nắm chắc những nguyên tắc này, bạn sẽ tự tin xử lý 90% các case query chậm gặp phải trong dự án thực tế — không cần đoán mò hay "thử cho chắc".