Posted inMySQL

Tối ưu MySQL 8 với EXPLAIN ANALYZE: Đừng để Optimizer “đánh lừa” bạn

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Khi EXPLAIN truyền thống chỉ còn là những lời “hứa suông”

Nếu anh em làm việc với MySQL, chắc hẳn EXPLAIN là câu lệnh cửa miệng mỗi khi hệ thống báo slow query. Thao tác quăng thêm từ khóa này vào đầu câu lệnh để xem MySQL định thực hiện thế nào đã quá quen thuộc. Tuy nhiên, EXPLAIN cũ chỉ mang tính chất dự báo dựa trên xác suất. Nó giống như việc xem Google Maps báo đường thoáng, dự kiến đi mất 10 phút, nhưng thực tế bạn lại kẹt cứng vì một vụ va chạm vừa mới xảy ra.

MySQL Optimizer dựa vào số liệu thống kê (statistics) để ước tính số dòng (rows) và chi phí (cost). Vấn đề là dữ liệu này thường xuyên bị “outdate” hoặc không phản ánh đúng thực tế dữ liệu trên đĩa cứng. Mình từng xử lý một ca oái oăm: EXPLAIN báo chỉ quét 50 dòng, nhưng query chạy mất tận 30 giây. Hóa ra do nghẽn I/O khi đọc dữ liệu thực từ ổ cứng cũ. Lúc đó, mình chỉ ước có thể nhìn thấu MySQL đang thực sự làm gì bên dưới.

Đó là lý do EXPLAIN ANALYZE (xuất hiện từ MySQL 8.0.18) trở thành vị cứu tinh. Thay vì chỉ phán đoán, nó trực tiếp thực thi câu lệnh và ghi lại nhật ký chi tiết. Đây chính là sự khác biệt giữa việc “nghe dự báo thời tiết” và “tự mình ra ngoài trời”.

Tại sao EXPLAIN ANALYZE là “vũ khí” phải có trong túi đồ?

Thực tế luôn đáng tin hơn dự tính

Sự khác biệt lớn nhất nằm ở các con số thực (Actual) được đặt ngay cạnh các con số ước tính (Estimated). Anh em sẽ nắm chắc trong lòng bàn tay:

  • Time to first row: Mất bao lâu để nhận được bản ghi đầu tiên (cực kỳ quan trọng với các ứng dụng web cần phản hồi nhanh).
  • Time to all rows: Tổng thời gian hoàn thành bước đó.
  • Actual rows: Số lượng bản ghi thực tế đã quét qua.
  • Loops: Số vòng lặp thực tế khi thực hiện các phép Join.

Nhìn vào kết quả, bạn sẽ thấy ngay “nút thắt cổ chai” đang nằm ở đâu. Không cần phải ngồi cãi nhau xem Index này có hiệu quả hay không, con số thực tế sẽ tự trả lời.

Cẩn thận: Đo thật thì tốn sức thật

Vì EXPLAIN ANALYZE thực sự chạy câu lệnh SQL, nên dữ liệu sẽ bị tác động nếu bạn dùng với INSERT, UPDATE hoặc DELETE. Với các bảng lớn tầm 100 triệu records, việc chạy lệnh này tốn thời gian y hệt query bình thường, thậm chí lâu hơn do phải ghi log chi tiết. Đừng dại mà “thử lửa” trực tiếp trên DB production đang lúc cao điểm kẻo gây treo hệ thống. Tốt nhất, hãy mang query đó về môi trường Staging với dữ liệu tương đương để mổ xẻ.

Cách đọc hiểu dữ liệu để “bắt bệnh” truy vấn

Cú pháp rất gọn, bạn chỉ cần thêm EXPLAIN ANALYZE vào trước câu SELECT:

EXPLAIN ANALYZE 
SELECT e.first_name, d.dept_name
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
WHERE e.last_name = 'Facello';

Kết quả trả về sẽ theo dạng cây (Tree format). Hãy tập trung đọc từ trong ra ngoài và từ dưới lên trên để thấy trình tự thực thi:

-> Inner hash join (de.dept_no = d.dept_no)  (cost=120.50 rows=150) (actual time=0.512..0.890 rows=180 loops=1)
    -> Table scan on d  (cost=1.10 rows=10) (actual time=0.011..0.025 rows=10 loops=1)
    -> Hash
        -> Inner hash join (e.emp_no = de.emp_no)  (cost=100.20 rows=140) (actual time=0.300..0.750 rows=150 loops=1)
            -> Index lookup on e using last_name (last_name='Facello')  (cost=20.10 rows=100) (actual time=0.100..0.250 rows=100 loops=1)
            -> Hash
                -> Table scan on de  (cost=50.50 rows=1000) (actual time=0.050..0.400 rows=1000 loops=1)

Ba chỉ số cần soi kỹ nhất

  • actual time=0.100..0.250: 0.100ms để có dòng đầu tiên, 0.250ms để kết thúc bước đó. Nếu khoảng cách giữa hai số này quá lớn, bước đó đang xử lý một lượng dữ liệu khổng lồ.
  • rows=180: Nếu số này lệch hẳn so với estimated rows (ví dụ dự tính 10 mà thực tế 1000), nghĩa là Optimizer đang bị lừa bởi statistics cũ.
  • loops=1: Trong phép Nested Loop Join, nếu loops nhảy lên hàng ngàn, đó chính là nơi bạn cần bổ sung Index ngay lập tức.

Kinh nghiệm thực chiến: Bài học từ hệ thống thương mại điện tử

Mới đây, mình tối ưu một trang web gặp lỗi load danh sách đơn hàng cho khách VIP mất hơn 5 giây. EXPLAIN báo MySQL sử dụng Index trên customer_id và chỉ quét 50 dòng. Lý thuyết rất đẹp, nhưng thực tế vẫn chậm.

Khi bật EXPLAIN ANALYZE, sự thật mới lộ diện: bước Index lookup tốn đến 4.5 giây. Lý do là dù chỉ có 50 dòng, nhưng dữ liệu của khách hàng này nằm rải rác khắp các phân vùng đĩa cứng. MySQL phải thực hiện Random I/O liên tục để đọc dữ liệu. Mình quyết định tạo một Covering Index chứa luôn các cột cần lấy. Kết quả là MySQL chỉ cần đọc trên Index, không cần mò xuống đĩa nữa. Thời gian thực thi giảm từ 5 giây xuống chỉ còn 0.04 giây.

Bài học rút ra: Đừng tin vào những gì Optimizer dự đoán, hãy nhìn vào những gì nó thực sự làm.

Quy trình tối ưu 3 bước cho anh em

  1. Check nhanh: Dùng EXPLAIN thường để loại bỏ các lỗi cơ bản như thiếu Index hoặc type=ALL (quét toàn bộ bảng).
  2. Mổ xẻ: Dùng EXPLAIN ANALYZE để so sánh giữa các phương án viết SQL hoặc kiểm tra xem bước nào tốn mili giây nhiều nhất.
  3. Kiểm chứng: Sau khi sửa code hoặc thêm Index, chạy lại EXPLAIN ANALYZE để chắc chắn các con số actual time đã giảm như kỳ vọng.

Nếu đang dùng MySQL 8, hãy biến công cụ này thành thói quen. Nó giúp bạn dẹp bỏ mọi phỏng đoán mơ hồ và tập trung vào những giải pháp dựa trên dữ liệu thực tế.

Share:
Tags: