5. Tối ưu query

MySQL thực thi một câu SQL thế nào, chẩn đoán query chậm ra sao, và viết lại sao cho optimizer dễ chọn plan tốt.

Phía sau một câu SELECT

1. Parse: kiểm tra cú pháp, bảng và cột có tồn tại.
2. Biến đổi: rút gọn biểu thức hằng (5 = 5 thành TRUE), bỏ điều kiện thừa, lan truyền đẳng thức (a = b AND b = 5 suy ra a = 5), chuyển subquery thành semi-join.
3. Optimizer theo chi phí: dùng thống kê (số dòng, cardinality của index, histogram) và cost model (chi phí đọc page, chi phí CPU so sánh) để thử các phương án: dùng index nào, join theo thứ tự nào, có cần bảng tạm hay sort không. Chọn phương án có cost ước tính thấp nhất.
4. Thực thi theo plan đã chọn.

SQL mô tả muốn gì, không mô tả làm thế nào. Thứ tự viết mệnh đề không phải thứ tự thực thi; optimizer có thể đổi thứ tự join, bỏ qua một index nếu thấy không đáng.

Giống Google Maps, đường được chọn là tốt nhất theo thống kê tại thời điểm đó. Cùng một câu SQL với tham số khác có thể ra plan khác: WHERE birthday >= '1998-01-01' dùng index, đổi thành '1990-01-01' có thể chuyển sang quét toàn bảng vì optimizer ước tính khoảng đó chứa quá nhiều dòng.

Chẩn đoán query chậm: quy trình

1. Tìm câu chậm. Bật slow_query_log với long_query_time (ví dụ 0,5 giây). Gom nhóm bằng pt-query-digest hoặc performance_schema.events_statements_summary_by_digest để thấy câu nào tốn tổng thời gian nhiều nhất, không chỉ câu chậm nhất.
2. So rows examined với rows sent. Trả về 20 dòng mà phải đọc 500.000 dòng thì gần như chắc chắn thiếu index hoặc plan sai.
3. EXPLAIN để xem plan: bảng nào đọc kiểu gì, index nào, ước tính bao nhiêu dòng, có filesort, bảng tạm không. EXPLAIN FORMAT=JSON cho thêm used_key_parts và cost từng bước; FORMAT=TREE dễ đọc với join.
4. EXPLAIN ANALYZE (8.0.18+) chạy thật và in số dòng, thời gian thực tế từng bước cạnh số ước tính. Lệch xa nghĩa là thống kê đã cũ: chạy ANALYZE TABLE hoặc tạo histogram.
5. Optimizer trace trả lời “vì sao chọn plan này”: các index đã cân nhắc, cost từng phương án, phương án bị loại.

SET optimizer_trace = 'enabled=on';
SELECT … ;   -- câu cần phân tích
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;

ANALYZE TABLE users UPDATE HISTOGRAM ON birthday WITH 64 BUCKETS;

Trace hay cho thấy một điều bất ngờ: quét toàn bảng đôi khi rẻ hơn range scan qua secondary index, vì mỗi dòng khớp lại phải tra clustered index một lần (đọc ngẫu nhiên). Multi-Range Read (MRR) giảm bớt chi phí này bằng cách sắp PK trước khi tra.

Đọc EXPLAIN

Cột type, từ tốt đến tệ: const → eq_ref → ref → range → index → ALL.

type	Nghĩa
const	Tra đúng 1 dòng theo PK hoặc UNIQUE với hằng số
eq_ref	Trong join: mỗi dòng bảng ngoài khớp đúng 1 dòng qua PK/UNIQUE
ref	So sánh bằng trên index không unique
range	Seek tới đầu khoảng rồi đọc liên tiếp (>, BETWEEN, IN, LIKE 'abc%')
index	Quét toàn bộ index
ALL	Quét toàn bảng

Extra hay gặp	Nghĩa
Using index	Covering index, không phải đọc bảng
Using index condition	ICP: lọc thêm trên entry index trước khi đọc dòng
Using where	Lọc sau khi đọc dòng; chỉ đáng lo khi rows lớn
Using filesort	Phải sort riêng vì ORDER BY không theo index
Using temporary	Tạo bảng tạm (GROUP BY, DISTINCT, UNION)
Using join buffer (hash join)	Join không có index phù hợp

Viết WHERE để dùng được index

Optimizer tự rút gọn biểu thức hằng: WHERE (b >= 5 AND b = 5) OR (b = 6 AND 5 = 5) OR (b = 7 AND 5 = 6) được tự đưa về b = 5 OR b = 6. Viết gọn vẫn nên làm vì dễ đọc. Những thứ optimizer không tự sửa được:

• Hàm bọc cột: WHERE YEAR(birthday) + 2 > 1999 phải tính hàm cho từng dòng. Đưa tính toán sang vế hằng số: WHERE birthday >= '1998-01-01'. Khi viết lại phải kiểm tra hai điều kiện thật sự tương đương (thử ở dưới). Nếu buộc phải giữ hàm, dùng functional index.
• Ép kiểu ngầm: WHERE phone = 0912345678 với phone VARCHAR khiến MySQL đổi cột sang số, mất index. Viết phone = '0912345678'.
• OR giữa các cột khác nhau: WHERE a = 1 OR b = 2 chỉ dùng được index qua index merge. Nếu không hiệu quả, viết thành hai câu nối bằng UNION ALL.
• Wildcard ở đầu: LIKE '%abc'.

Viết lại có tương đương không?

Đổi YEAR(birthday) + 2 > YEAR('1999-12-01') thành birthday > DATE_SUB('1999-12-01', INTERVAL 2 YEAR) trông giống nhau nhưng không tương đương. Nhập một ngày sinh để so sánh.

birthday

Điều kiện	Với 1997-12-15
YEAR(birthday) + 2 > 1999 gốc, không dùng được index	bỏ
birthday > '1997-12-01' viết lại sai	lấy khác điều kiện gốc
birthday >= '1998-01-01' viết lại đúng, dùng được index	bỏ

Vùng tô đỏ là các ngày bản viết lại sai lấy thừa: 02 đến 31/12/1997. Điều kiện gốc tương đương YEAR(birthday) > 1997, tức chỉ lấy từ 01/01/1998.

JOIN

• Nested loop + index: với mỗi dòng của bảng ngoài, seek vào index của bảng trong. Chi phí xấp xỉ số dòng ngoài × độ sâu B+Tree. Đây là đường mặc định khi cột join có index; bảng ngoài được lọc tốt thì càng ít vòng lặp.
• Hash join (8.0.18+): không có index phù hợp cho equi-join thì dựng bảng băm từ bảng nhỏ hơn trong join_buffer_size, rồi quét bảng kia một lần để dò. Thay hẳn Block Nested Loop từ 8.0.20.
• Thứ tự join của INNER JOIN do optimizer chọn; bảng nào viết trước không quan trọng. Muốn ép thì dùng STRAIGHT_JOIN hoặc hint /*+ JOIN_ORDER(a, b) */. Không đổi INNER sang LEFT JOIN để ép thứ tự vì như vậy là đổi kết quả.
• Index đặt ở bảng được tra cứu. Join A.col1 = B.col2 mà A là bảng ngoài thì cần index trên B.col2. Cột khoá ngoại thường đã có index sẵn.
• LEFT JOIN + WHERE trên bảng phải (WHERE b.status = 1) loại mất các dòng không khớp (NULL), biến nó thành INNER JOIN. Muốn giữ dòng bên trái thì đưa điều kiện vào ON.
• Tách join lên tầng app (lấy danh sách id trước, query bảng thứ hai bằng WHERE id IN (…)) có ích khi dữ liệu nằm ở service hoặc database khác, hoặc để tận dụng cache từng phần. Cái giá: thêm round-trip, dễ thành N+1 query nếu làm vòng lặp, và hai lần đọc không cùng snapshot.

COUNT, GROUP BY, ORDER BY, UNION

• COUNT(*) đếm dòng; COUNT(col) đếm giá trị khác NULL. Hai câu hỏi khác nhau. COUNT(*) và COUNT(1) như nhau trong InnoDB.
• InnoDB không lưu sẵn tổng số dòng (do MVCC, mỗi transaction có thể thấy số khác nhau), nên COUNT(*) bảng lớn phải quét index nhỏ nhất. Cần con số nhanh thì dùng bảng counter hoặc số ước tính.
• MySQL 5.7 trở về trước: GROUP BY tự sort kết quả, thêm ORDER BY NULL để bỏ sort. MySQL 8 không còn sort ngầm nữa.
• ORDER BY khớp thứ tự index thì không cần filesort (chương 4). Sort trộn chiều cần descending index (8.0).
• UNION khử trùng lặp nên phải dùng bảng tạm; dùng UNION ALL khi chắc chắn không trùng.

Phân trang: OFFSET vs keyset

LIMIT 20 OFFSET 100000 buộc MySQL đọc 100.020 dòng rồi bỏ 100.000 dòng đầu. Trang càng sâu càng chậm. Keyset pagination dùng giá trị cuối của trang trước để seek thẳng.

Trang số 500

SELECT * FROM orders
ORDER BY id LIMIT 20 OFFSET 9980;

SELECT * FROM orders
WHERE id > :last_id   -- id cuối trang trước
ORDER BY id LIMIT 20;

OFFSET: đọc rồi bỏ 9.980 dòng10.000 dòng đọc

Keyset: seek tới id rồi đọc 2020 dòng đọc

Ở trang 500, OFFSET đọc gấp 500 lần keyset. Thanh dài nhất ứng với trang 5.000 (100.000 dòng).

Keyset cần ORDER BY khớp index. Sort theo cột không duy nhất như created_at thì thêm id làm tie-breaker:

SELECT … FROM orders
WHERE created_at < ? OR (created_at = ? AND id < ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- index (created_at, id)

Nếu buộc phải có số trang, dùng deferred join để phần OFFSET chỉ quét index: … JOIN (SELECT id FROM … LIMIT 20 OFFSET n) t USING (id).

Chia nhỏ việc lớn

MySQL (và database OLTP nói chung) được thiết kế cho rất nhiều query nhỏ trả về nhanh, không phải vài query khổng lồ “tất cả trong một”.

• UPDATE/DELETE hàng loạt: chia batch theo khoá chính. Transaction nhỏ giữ lock ngắn, undo log nhỏ, replica không bị lag dồn.
• IN rất dài (hàng chục nghìn giá trị): chia thành nhiều câu vài trăm tới vài nghìn giá trị.
• Xử lý theo từng dòng (định dạng chuỗi, tính toán nghiệp vụ phức tạp) đưa lên tầng app: scale app dễ và rẻ hơn scale database. Ngược lại, phép tổng hợp làm giảm lượng dữ liệu (SUM, COUNT, GROUP BY) nên để database làm, tránh kéo hàng triệu dòng qua mạng.

-- lặp lại tới khi ảnh hưởng 0 dòng, nghỉ ngắn giữa các lần
DELETE FROM logs
WHERE created_at < '2026-07-01'
ORDER BY id LIMIT 5000;

Optimizer hint: phương án cuối

Khi đã hiểu dữ liệu, đã cập nhật thống kê mà optimizer vẫn chọn plan tệ, có thể dùng hint: ép hoặc cấm một index, cố định thứ tự join, giới hạn thời gian chạy. Hint gắn câu SQL vào trạng thái dữ liệu hiện tại; khi dữ liệu hay schema đổi, hint cũ có thể thành vật cản. Chỉ dùng cho query quan trọng, có đo đạc và review.

SELECT /*+ JOIN_ORDER(o, c) MAX_EXECUTION_TIME(1000) */ …
FROM orders o JOIN customers c ON c.id = o.customer_id;

SELECT … FROM orders FORCE INDEX (idx_customer_status) WHERE …;

Ghi nhớ

1. Quy trình: slow log → rows examined/sent → EXPLAIN → EXPLAIN ANALYZE → optimizer trace → sửa query, index hoặc thống kê.
2. Không bọc hàm, không ép kiểu cột trong WHERE; viết lại phải tương đương.
3. Phân trang sâu dùng keyset; việc lớn chia batch.
4. Hint là phương án cuối.