Posted inMySQL

Làm chủ OPTIMIZER_TRACE: Tuyệt chiêu ‘mổ xẻ’ logic chọn Index của MySQL

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

Khi EXPLAIN không còn đủ để giải quyết vấn đề

Tôi từng xử lý một hệ thống e-commerce với bảng orders vượt ngưỡng 20 triệu dòng. Một ngày nọ, câu query lọc đơn hàng bỗng chậm bất thường, mất tận 15 giây để phản hồi. Dù cột status và created_at đều đã được đánh Index, MySQL vẫn quyết định chọn Full Table Scan.

Lệnh EXPLAIN thông thường chỉ cho biết MySQL đang làm gì. Nó xác nhận đang quét toàn bộ bảng nhưng không giải thích tại sao lại bỏ qua Index. Tại sao bộ tối ưu hóa lại đánh giá chi phí (cost) của Index X cao hơn Index Y? Đó là lúc bạn cần một giải pháp chuyên sâu hơn để “mổ xẻ” tận gốc vấn đề.

OPTIMIZER_TRACE chính là chiếc máy quét MRI cho SQL. Nó tiết lộ toàn bộ quá trình “tư duy” của MySQL Optimizer. Bạn sẽ thấy rõ từng con số tính toán chi phí đằng sau mỗi quyết định chọn Index.

So sánh các công cụ soi xét truy vấn

Mỗi công cụ có một mục đích riêng. Hiểu rõ chúng sẽ giúp bạn tiết kiệm hàng giờ debug vô ích.

1. EXPLAIN (Cơ bản)

  • Ưu điểm: Hiển thị nhanh execution plan tổng quát.
  • Nhược điểm: Quá sơ sài. Không giải thích được lý do tại sao một Index bị loại bỏ (pruned).

2. EXPLAIN ANALYZE (MySQL 8.0+)

  • Ưu điểm: Đo lường thời gian thực thi thực tế của từng bước. Rất tốt để tìm nút thắt cổ chai.
  • Nhược điểm: Bạn phải đợi query chạy xong mới có kết quả. Nếu query làm treo server, công cụ này khá nguy hiểm.

3. OPTIMIZER_TRACE (Chuyên sâu)

  • Ưu điểm: Giải thích logic dựa trên chi phí (Cost-based). Nó chỉ ra chính xác con số I/O và CPU dự kiến cho từng phương án.
  • Nhược điểm: Kết quả trả về là JSON rất dài. Bạn cần sự kiên nhẫn để đọc hiểu.

Ưu và nhược điểm thực tế

Qua nhiều dự án tối ưu DB, tôi rút ra một số lưu ý quan trọng về công cụ này.

Lợi ích vượt trội:

  • Minh bạch về Cost: MySQL tính 1.0 đơn vị cost cho mỗi lần đọc trang đĩa. Trace sẽ cho bạn thấy tổng cost của Table Scan so với Index Scan.
  • Bắt bệnh Index Merge: Đôi khi MySQL gộp 2 index nhưng lại chậm hơn dùng 1 index đơn lẻ. Trace sẽ chỉ ra sai lầm này.
  • Phân tích Range Optimizer: Đặc biệt hiệu quả với các câu lệnh IN(...) chứa hàng ngàn giá trị.

Rào cản cần lưu ý:

  • Overhead lớn: Việc ghi trace tốn CPU và RAM. Tuyệt đối không bật global trên Production. Chỉ nên dùng cho session hiện hành.
  • Giới hạn bộ nhớ: Nếu trace quá dài, kết quả có thể bị cắt cụt. Bạn cần tăng biến optimizer_trace_max_mem_size lên khoảng 1MB hoặc hơn.

Quy trình triển khai 4 bước

Đừng chạy bừa bãi. Hãy tuân thủ quy trình dưới đây để lấy được dữ liệu chính xác nhất.

Bước 1: Bật Trace cho session

Câu lệnh này chỉ có tác dụng trong kết nối hiện tại của bạn, không ảnh hưởng đến người dùng khác.

SET SESSION optimizer_trace="enabled=on";
-- Tăng buffer để tránh mất dữ liệu trace
SET SESSION optimizer_trace_max_mem_size=1048576;

Bước 2: Thực thi query cần soi

Hãy chạy câu lệnh SQL bạn đang muốn tối ưu. Nếu bảng quá lớn, bạn có thể thêm LIMIT. Optimizer vẫn sẽ tính toán logic tương tự trước khi áp dụng limit.

SELECT * FROM orders WHERE customer_id = 5001 AND status = 'COMPLETED';

Bước 3: Trích xuất dữ liệu JSON

Dữ liệu nằm trong bảng hệ thống ảo INFORMATION_SCHEMA.

SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Bước 4: Dọn dẹp tài nguyên

Luôn tắt trace ngay sau khi xong việc để trả lại tài nguyên cho server.

SET SESSION optimizer_trace="enabled=off";

Đọc hiểu JSON: Tập trung vào đâu?

Kết quả trace có thể dài hàng ngàn dòng. Đừng đọc hết. Hãy tập trung vào mục join_optimization, nơi chứa các phép toán quan trọng nhất.

Mục considered_paths

Đây là nơi MySQL liệt kê các “ứng cử viên” Index. Hãy nhìn vào ví dụ sau:

"chosen_range_access_summary": {
  "range_access_plan": {
    "type": "range_scan",
    "index": "idx_status",
    "rows": 150240,
    "cost": 18201,
    "chosen": false,
    "cause": "cost"
  }
}

Nếu chosen là false, MySQL sẽ cho bạn biết lý do. Thông thường là do cost cao hơn việc quét toàn bộ bảng hoặc một index khác.

Kinh nghiệm thực chiến: Khi Optimizer bị “lừa”

Có lần tôi gặp trường hợp MySQL dự báo rows trong trace là 10.000 nhưng thực tế chỉ có 10 dòng. Sai lệch này khiến nó chọn sai Index trầm trọng.

Giải pháp: Khi số liệu rows_estimation trong trace không khớp thực tế, hãy chạy ngay lệnh ANALYZE TABLE tên_bảng;. Lệnh này cập nhật lại thống kê phân phối dữ liệu, giúp Optimizer lấy lại “thị lực”.

Ngoài ra, hãy cẩn thận với Index Merge. Nếu trace cho thấy MySQL đang cố gộp nhiều Index lẻ, hãy cân nhắc tạo một Composite Index (Index đa cột). Theo kinh nghiệm của tôi, Composite Index luôn mang lại hiệu năng ổn định và dễ dự đoán hơn.

Lời kết

Làm chủ OPTIMIZER_TRACE giúp bạn thoát khỏi việc đoán mò khi tối ưu Database. Đây là công cụ không thể thiếu nếu bạn muốn xử lý những query phức tạp trên các hệ thống dữ liệu lớn. Hãy kết hợp nó cùng EXPLAIN ANALYZE để có cái nhìn toàn diện nhất về hiệu năng SQL của mình.

Share:
Tags: