Đọc EXPLAIN cơ bản để biết truy vấn có dùng index

By Better Wise

Mở EXPLAIN trước khi tin một câu query AI viết

Bài trước bạn đã biết index là cấu trúc phụ giúp database tìm dòng nhanh thay vì quét cả bảng. Nhưng có index chưa chắc truy vấn dùng index: AI có thể viết điều kiện khiến index vô hiệu, hoặc thêm index sai cột. EXPLAIN là chỗ duy nhất cho bạn thấy database định chạy truy vấn như thế nào. Câu thần chú của tôi: đo trước khi đoán.

Sau bài này bạn sẽ:

Chạy được EXPLAIN và EXPLAIN ANALYZE trên một câu query.
Đọc được bốn chỗ quan trọng: type, key, rows, Extra.
Phân biệt quét toàn bảng (type = ALL) với đi qua index (ref, range, const).
Nhận ra ba kiểu viết phổ biến khiến index bị bỏ qua.

EXPLAIN và EXPLAIN ANALYZE khác nhau ở đâu

EXPLAIN in ra kế hoạch dự kiến, không chạy truy vấn, nên rất an toàn để nghịch trên production. EXPLAIN ANALYZE chạy thật rồi in thêm thời gian và số dòng thực tế, nên chỉ dùng khi bạn chấp nhận truy vấn đó được thực thi.

Bảng mẫu dùng xuyên bài: orders(id, customer_id, created_at, status, total) với khoảng 1,2 triệu dòng, ban đầu chỉ có primary key trên id.

Bốn cột quyết định: type, key, rows, Extra

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

+----+-------+------+---------------+------+---------+------+---------+-------------+
| id | table | type | possible_keys | key  | key_len | rows    | Extra       |
+----+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | orders| ALL  | NULL          | NULL | NULL    | 1200000 | Using where |
+----+-------+------+---------------+------+---------+------+---------+-------------+

Đọc theo thứ tự:

type: kiểu truy cập. ALL là quét toàn bảng, ref/range/const là có đi qua index. ALL trên bảng 1,2 triệu dòng là dấu hiệu chậm.
key: index thực sự được dùng. NULL nghĩa là không dùng index nào.
rows: số dòng database ước lượng phải đọc. Càng gần số dòng bạn thật sự cần càng tốt.
Extra: chi tiết phụ. Using where là còn phải lọc lại sau khi đọc, Using index là tốt (chỉ đọc index), Using filesort và Using temporary là dấu hiệu phải làm thêm việc.

Ví dụ 1: thêm index rồi đo lại

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

| id | table  | type | possible_keys       | key                | rows | Extra |
|  1 | orders | ref  | idx_orders_customer | idx_orders_customer|   37 | NULL  |

ALL biến thành ref, rows từ 1.200.000 xuống 37. Đó là toàn bộ câu chuyện về index, chỉ khác là giờ bạn có bằng chứng bằng số.

Ví dụ 2: index có mà không dùng, vì bọc cột trong hàm

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;

Kết quả: type = ALL, key = NULL, rows = 1200000, dù created_at đã có index. Lý do: database phải tính YEAR() cho từng dòng trước khi so sánh, nên không thể tra index theo giá trị cột.

Sửa bằng cách đổi sang khoảng giá trị, giữ cột trần ở bên trái phép so sánh:

EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Lần này type = range, key = idx_orders_created, rows vài chục nghìn. Cùng một ý nghĩa, khác hẳn chi phí.

Ví dụ 3: ba kiểu viết hay làm index vô hiệu

Cách viết trong query	Index có dùng?	Cách sửa
`WHERE YEAR(created_at) = 2024`	Không, phải quét bảng	Đổi sang khoảng `>=` và `<`
`WHERE email LIKE '%@gmail.com'`	Không, wildcard ở đầu	Chỉ dùng `LIKE 'abc%'`, hoặc tìm cách khác
`WHERE status + 0 = 1`	Không, biểu thức trên cột	Để cột trần: `WHERE status = 1`

Đọc thêm khi cần: EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Ngoài kế hoạch, bạn nhận thêm actual time và actual rows. Nếu rows ước lượng 10 mà actual rows là 50.000, thống kê của bảng đã cũ, và bộ tối ưu đang chọn sai đường. Đây là lúc cần ANALYZE TABLE trước khi kết luận bất cứ điều gì.

Ôn nhanh vài con số trước khi sang phần tiếp:

type bằng _____ nghĩa là database đang quét toàn bảng. Cột _____ cho biết index nào được dùng, giá trị NULL nghĩa là không dùng index. Cột _____ là số dòng database ước lượng phải đọc. Giá trị _____ trong Extra nghĩa là phải sắp xếp thêm sau khi đọc dữ liệu. Bọc cột trong hàm, ví dụ YEAR(created_at), làm index trên cột đó _____.

Kinh nghiệm đọc EXPLAIN mà không đoán mò

Đọc type trước, key sau, rồi mới nhìn rows. Ba cột này kể gần hết câu chuyện.
type = ALL trên bảng nhỏ vài nghìn dòng thường không sao. Đừng tối ưu theo cảm giác, hãy nhìn số dòng.
Đừng vội thêm index khi thấy ALL. Kiểm tra xem query có bọc cột trong hàm, dùng wildcard đầu chuỗi, hay so sánh sai kiểu dữ liệu không. Sửa câu query thường rẻ hơn thêm index.
EXPLAIN ANALYZE chạy thật, nên đừng bắn nó vào câu UPDATE hay DELETE trên production.
Một lần đo chưa đủ. Chạy lại sau khi thêm index để có con số trước và sau, và giữ lại cả hai khi review code AI sinh.

Ghi nhớ và bước tiếp theo

EXPLAIN cho bạn kế hoạch, EXPLAIN ANALYZE cho bạn cả kế hoạch lẫn số đo thật.
Bốn cột cần đọc: type, key, rows, Extra.
type = ALL và key = NULL là dấu hiệu quét toàn bảng; ref/range là có dùng index.
Bọc cột trong hàm, wildcard ở đầu LIKE, và biểu thức trên cột đều làm index vô hiệu.
Luôn đo lại sau khi sửa, và so sánh trước, sau bằng số.

Bài sau chúng ta sẽ dùng đúng kỹ năng này để bắt một loại chậm rất hay gặp trong code AI sinh: truy vấn N+1, khi một trang danh sách bắn ra hàng trăm câu query nhỏ thay vì một câu.

Front: EXPLAIN khác EXPLAIN ANALYZE ở điểm nào? Back: EXPLAIN chỉ in kế hoạch dự kiến; EXPLAIN ANALYZE chạy thật và in thêm thời gian, số dòng thực tế.

Front: type = ALL có nghĩa là gì? Back: Database quét toàn bộ bảng, không dùng index.

Front: Cột key trong EXPLAIN cho biết gì? Back: Index thực sự được dùng; NULL nghĩa là không dùng index nào.

Front: Cột rows trong EXPLAIN là gì? Back: Số dòng database ước lượng phải đọc để trả về kết quả.

Front: Những giá trị type nào cho thấy có đi qua index? Back: ref, range, const, eq_ref; ngược lại ALL là quét toàn bảng.

Front: Vì sao WHERE YEAR(created_at) = 2024 không dùng được index trên created_at? Back: Hàm bọc quanh cột buộc database tính giá trị cho từng dòng trước khi so sánh.

Front: Sửa query YEAR(created_at) = 2024 thế nào để dùng index? Back: Đổi sang khoảng: created_at >= '2024-01-01' AND created_at < '2025-01-01'.

Front: LIKE '%abc' có dùng index không? Back: Không, wildcard ở đầu chuỗi khiến database không tra được theo thứ tự index.

Front: Extra = Using index nghĩa là gì? Back: Truy vấn chỉ đọc dữ liệu từ index, không phải mở bảng chính, thường là dấu hiệu tốt.

Front: Extra = Using filesort báo hiệu điều gì? Back: Database phải sắp xếp thêm sau khi đọc dữ liệu, thường do thiếu index phù hợp với ORDER BY.

Front: Khi nào nên nghi ngờ thống kê bảng đã cũ? Back: Khi rows ước lượng trong EXPLAIN ANALYZE chênh quá xa actual rows.

Front: Vì sao không nên chạy EXPLAIN ANALYZE trên câu UPDATE hoặc DELETE ở production? Back: Vì EXPLAIN ANALYZE thực thi câu lệnh thật, dữ liệu sẽ bị thay đổi.

Front: Thứ tự đọc bốn cột quan trọng trong EXPLAIN? Back: type trước, key sau, rồi rows, cuối cùng là Extra.

Trong EXPLAIN của MySQL, cột _____ cho biết cách database truy cập dữ liệu, và giá trị _____ là dấu hiệu quét toàn bảng. Cột _____ ghi tên index mà optimizer thật sự chọn, còn NULL nghĩa là không dùng index nào. Cột _____ là số dòng database ước lượng phải đọc; con số này càng gần số dòng trả về thật thì càng tốt. Khi thấy Extra chứa _____, nhiều khả năng truy vấn đang thiếu index khớp với mệnh đề ORDER BY. Chỉ nên chạy EXPLAIN ANALYZE trên câu SELECT, vì với UPDATE hay DELETE nó sẽ _____ và làm thay đổi dữ liệu.

Ba thói quen nhỏ khi ngồi trước EXPLAIN

Đọc theo thứ tự cố định. type, rồi key, rồi rows, cuối cùng là Extra. Đọc lộn xộn dễ thấy rows lớn rồi hoảng mà bỏ qua chuyện type = ALL mới là gốc vấn đề.
Đo trước khi đoán. Đừng sửa query chỉ vì "cảm giác chậm". Chạy EXPLAIN, ghi lại type và rows, thêm index hoặc viết lại, rồi chạy lại và so sánh. Nếu con số không đổi, giả thuyết của bạn sai, và đó là thông tin có ích.
Cẩn thận với hàm bọc quanh cột. WHERE YEAR(created_at) = 2024 là lỗi kinh điển mà AI rất hay sinh ra. Đổi sang khoảng ngày là xong: created_at >= '2024-01-01' AND created_at < '2025-01-01'.
LIKE '%...' gần như luôn quét bảng. Wildcard đứng đầu chuỗi thì index trên cột đó không giúp được gì.
rows nhỏ chưa chắc nhanh. Một truy vấn rows = 20 nhưng chạy trong vòng lặp 500 lần vẫn là thảm họa. Đọc EXPLAIN phải đi kèm câu hỏi: truy vấn này chạy bao nhiêu lần?

Tip: Nếu EXPLAIN ANALYZE cho rows ước lượng chênh quá xa actual rows (ví dụ ước lượng 10 nhưng thực tế 50.000), đừng vội thêm index. Hãy yêu cầu chạy ANALYZE TABLE trước, vì thống kê cũ làm optimizer chọn sai đường.

Chốt lại và bước sang N+1

Bốn dòng cần nhớ: type cho biết có đi qua index hay không (ALL là quét toàn bảng), key cho biết index nào được chọn, rows cho biết khối lượng đọc ước tính, Extra cho biết còn phải sắp xếp hay đọc bảng chính nữa không. Đó là toàn bộ kỹ năng tối thiểu để trả lời câu "truy vấn AI vừa viết nhanh hay chậm".

Bài sau sẽ mở rộng đúng điểm mà EXPLAIN không nhìn thấy: một truy vấn đẹp trong EXPLAIN vẫn có thể là thủ phạm, nếu nó bị gọi 500 lần trong một vòng lặp. Đó là truy vấn N+1.

Front: EXPLAIN dùng để làm gì? Back: Xem kế hoạch thực thi mà database chọn cho một câu truy vấn, trước khi chạy nó.

Front: EXPLAIN ANALYZE khác EXPLAIN ở điểm nào? Back: EXPLAIN ANALYZE chạy câu lệnh thật và trả về thời gian cùng số dòng thực tế.

Front: Cột type trong EXPLAIN của MySQL cho biết gì? Back: Cách database truy cập dữ liệu, tức là có đi qua index hay không.

Front: type = ALL có nghĩa là gì? Back: Quét toàn bảng, không dùng index nào.

Front: Bốn cột quan trọng nhất khi mới đọc EXPLAIN? Back: type, key, rows, Extra.

Front: Cột key trong EXPLAIN là gì? Back: Tên index mà optimizer chọn; NULL nghĩa là không dùng index.