Trong quá trình phát triển các hệ thống có quy mô dữ liệu lớn, truy vấn chậm (slow query) là vấn đề gần như không thể tránh khỏi. Các truy vấn này không chỉ làm tăng thời gian phản hồi của API mà còn có nguy cơ chiếm dụng toàn bộ tài nguyên kết nối của cơ sở dữ liệu, dẫn đến treo hệ thống trong các kịch bản thực tế có lưu lượng truy cập cao.

Để giải quyết bài toán này, bên cạnh việc tinh chỉnh logic nghiệp vụ hay cấu trúc mã nguồn, tối ưu hóa chỉ mục (index) là phương pháp hiệu quả nhất. Công cụ mạnh mẽ nhất giúp lập trình viên phân tích hành vi của bộ tối ưu hóa MySQL chính là lệnh EXPLAIN.

Khái niệm về EXPLAIN

Lệnh EXPLAIN cung cấp thông tin chi tiết về cách MySQL thực hiện một câu lệnh truy vấn (SELECT, DELETE, INSERT, REPLACE, UPDATE). Dựa vào kết quả trả về, chúng ta có thể biết được thứ tự truy cập các bảng, chỉ mục nào đang được sử dụng, và khối lượng dữ liệu dự kiến phải quét qua.

Cú pháp cơ bản:

EXPLAIN SELECT * FROM orders WHERE order_id = 100;

Phân tích các cột quan trọng trong kết quả EXPLAIN

Khi chạy lệnh EXPLAIN, MySQL sẽ trả về một bảng thông tin. Để tối ưu hóa hiệu quả, chúng ta cần hiểu rõ ý nghĩa của các cột sau:

1. Cột id

Đây là số thứ tự thực hiện của các truy vấn con hoặc các bảng trong câu lệnh. Quy tắc đọc như sau:

• id giống nhau: Các bảng được thực thi từ trên xuống dưới.
• id khác nhau: Truy vấn có id lớn hơn sẽ được thực hiện trước (thường là các subquery).
• id hỗn hợp: Thực hiện theo quy tắc ưu tiên id lớn nhất, sau đó đến thứ tự từ trên xuống cho các dòng có id bằng nhau.

2. Cột select_type

Xác định loại truy vấn để nhận diện mức độ phức tạp:

• SIMPLE: Truy vấn đơn giản, không chứa subquery hoặc UNION.
• PRIMARY: Truy vấn ngoài cùng nhất trong một cấu trúc phức tạp.
• SUBQUERY: Truy vấn con nằm trong SELECT hoặc WHERE.
• DERIVED: Bảng tạm được tạo ra từ subquery trong mệnh đề FROM.
• UNION: Truy vấn thứ hai trở đi trong một lệnh UNION.

3. Cột type (Kiểu truy cập)

Đây là chỉ số quan trọng nhất để đánh giá hiệu năng. Thứ tự từ tốt nhất đến tệ nhất như sau:

system > const > eq_ref > ref > range > index > ALL

• const: Truy vấn trả về tối đa 1 dòng dữ liệu (thường dùng Primary Key hoặc Unique Index).
• eq_ref: Thường gặp trong các phép JOIN, trong đó mỗi dòng từ bảng trước chỉ khớp với đúng một dòng ở bảng sau qua Primary Key.
• ref: Sử dụng chỉ mục không duy nhất (Non-unique Index) để tìm kiếm các dòng khớp với một giá trị.
• range: Quét chỉ mục trong một phạm vi giá trị cụ thể (ví dụ: dùng BETWEEN, >, IN).
• index: Quét toàn bộ cây chỉ mục (thường nhanh hơn ALL vì kích thước chỉ mục nhỏ hơn dữ liệu thực tế).
• ALL: Quét toàn bộ bảng (Full Table Scan) - cần hạn chế tối đa ở bảng lớn.

4. Cột key và key_len

• key: Tên của chỉ mục thực tế mà MySQL quyết định sử dụng. Nếu NULL, nghĩa là không có chỉ mục nào được dùng.
• key_len: Độ dài của chỉ mục được tính bằng byte. Chỉ số này giúp kiểm tra xem các thành phần trong một Composite Index (chỉ mục tổ hợp) có được sử dụng triệt để hay không.

Công thức tính key_len cơ bản (với bảng mã UTF8MB4 - 4 bytes/char):

• Varchar(N): N * 4 + 2 bytes (để lưu độ dài) + 1 byte (nếu cột cho phép NULL).
• Int: 4 bytes.
• BigInt: 8 bytes.

5. Cột Extra

Chứa các thông tin bổ sung cực kỳ giá trị:

• Using index: Sử dụng Covering Index, nghĩa là toàn bộ dữ liệu cần thiết đều nằm trong chỉ mục, không cần truy cập vào bảng dữ liệu thật (Record).
• Using where: Sử dụng điều kiện WHERE để lọc dữ liệu.
• Using filesort: MySQL phải thực hiện một tiến trình sắp xếp riêng biệt thay vì dùng thứ tự của chỉ mục. Đây là dấu hiệu cần tối ưu hóa ORDER BY.
• Using temporary: MySQL phải tạo bảng tạm để lưu kết quả (thường gặp khi dùng GROUP BY hoặc DISTINCT trên các cột không có chỉ mục).

Ví dụ minh họa và Tối ưu hóa

Giả sử ta có bảng account với cấu trúc như sau:

-- Cấu trúc bảng
CREATE TABLE account (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    status TINYINT,
    INDEX idx_user_status (username, status)
);

Trường hợp 1: Sử dụng đúng chỉ mục tổ hợp

EXPLAIN SELECT username FROM account WHERE username = 'admin' AND status = 1;

Kết quả: type sẽ là ref, key là idx_user_status, và Extra là Using index. Đây là trạng thái lý tưởng.

Trường hợp 2: Truy vấn gây ra Filesort

EXPLAIN SELECT * FROM account ORDER BY email;

Kết quả: Extra sẽ xuất hiện Using filesort vì cột email không có chỉ mục. Để tối ưu, ta cần thêm chỉ mục cho cột email.

Quy trình tối ưu hóa truy vấn

1. Sử dụng Slow Query Log để xác định những câu lệnh SQL chạy chậm vượt ngưỡng cho phép.
2. Dùng EXPLAIN để xem kế hoạch thực thi của câu lệnh đó.
3. Kiểm tra cột key để xem có dùng chỉ mục không, cột type để xem mức độ quét dữ liệu, và Extra để phát hiện bảng tạm hoặc sắp xếp thủ công.
4. Tiến hành điều chỉnh: Thêm chỉ mục, thay đổi cấu trúc Composite Index, hoặc viết lại câu lệnh SQL (ví dụ: tránh dùng SELECT *, hạn chế subquery lồng nhau).
5. Chạy lại EXPLAIN để kiểm chứng kết quả sau khi điều chỉnh.