Tutorial 2x Tìm và tối ưu các truy vấn SQL chậm (Slow Queries) cho XenForo 2.3

[PVS]

Tìm và tối ưu các truy vấn SQL chậm (Slow Queries) cho XenForo 2.3

Khi diễn đàn của bạn phát triển, một số trang bỗng nhiên tải rất chậm? Bạn đã tối ưu hình ảnh, CSS/JS mà vẫn không cải thiện? Rất có thể, "thủ phạm" đang ẩn mình trong cơ sở dữ liệu (database) dưới dạng một hoặc nhiều truy vấn SQL (SQL query) không hiệu quả. Điều này đặc biệt thường xảy ra với các add-on của bên thứ ba.

Việc tìm và tối ưu hóa các "slow query" này là một kỹ năng nâng cao, nhưng nó có thể tạo ra sự khác biệt khổng lồ về hiệu suất. Bài viết này sẽ hướng dẫn bạn quy trình từng bước để chẩn đoán và khắc phục chúng.

Bài viết này liên quan đến việc thay đổi cấu trúc database. Một sai sót nhỏ có thể gây hỏng hoàn toàn diễn đàn. Luôn luôn BACKUP DATABASE trước khi thực hiện bất kỳ thao tác nào và hãy thử trên một trang web thử nghiệm (staging) trước nếu có thể.

Bước 1: Bật chế độ gỡ lỗi (Debug Mode) để "săn" Query​

Để tìm ra các query chậm, trước tiên bạn cần bật chế độ debug của XenForo.

1. Sử dụng FTP hoặc File Manager, mở file src/config.php trong thư mục gốc của diễn đàn.
2. Thêm dòng sau vào cuối file: $config['debug'] = true;
3. Lưu file lại.

Bây giờ, khi bạn truy cập diễn đàn với tài khoản admin, bạn sẽ thấy một thanh công cụ gỡ lỗi màu đen ở dưới cùng của trang.

Bước 2: Xác định truy vấn "thủ phạm"​

1. Truy cập vào trang/chức năng trên diễn đàn mà bạn cảm thấy nó đang bị chậm.
2. Nhìn vào thanh gỡ lỗi ở dưới, tìm và nhấp vào mục DB.
3. Một cửa sổ pop-up sẽ hiện ra, liệt kê tất cả các truy vấn SQL đã được thực thi để tải trang này, cùng với thời gian thực thi của mỗi query.
4. Hãy tìm những query có thời gian thực thi (time) cao bất thường (ví dụ: trên 0.5 giây). Đây chính là các "slow query" mà chúng ta cần tìm.
5. Sao chép lại toàn bộ đoạn mã SQL của query đó.

Bước 3: Chẩn đoán bằng lệnh EXPLAIN​

EXPLAIN là một lệnh của MySQL, nó sẽ cho chúng ta biết "kế hoạch" mà database sẽ sử dụng để thực thi một query.

1. Đăng nhập vào phpMyAdmin (hoặc một công cụ quản lý database khác).
2. Chọn database của diễn đàn và mở tab SQL.
3. Gõ từ khóa EXPLAIN ở đầu, theo sau là đoạn mã SQL chậm bạn đã sao chép. Ví dụ:
   

   EXPLAIN SELECT * FROM `xf_post` WHERE `message_state` = 'visible' ORDER BY `post_date` DESC LIMIT 10;

4. Nhấn "Go".

Kết quả trả về sẽ là một bảng phân tích. Hãy chú ý đến 3 cột quan trọng nhất:

• type: Đây là cột quan trọng nhất. Nếu nó có giá trị là ALL, đây là dấu hiệu cực kỳ xấu, có nghĩa là MySQL phải quét toàn bộ bảng để tìm kiếm. Giá trị tốt thường là ref hoặc index.
• key: Cho biết index nào đang được sử dụng. Nếu là NULL, có nghĩa là không có index nào được dùng cho truy vấn này.
• rows: Ước tính số dòng mà MySQL phải quét qua. Nếu type là ALL và rows là một con số khổng lồ, bạn đã tìm ra vấn đề.

Bước 4: Giải pháp - Thêm chỉ mục (Index)​

Nếu một query phải quét toàn bộ bảng (type: ALL) vì nó đang lọc/sắp xếp theo một cột không có chỉ mục, giải pháp là thêm chỉ mục cho cột đó.

• Index là gì? Hãy tưởng tượng nó như mục lục của một cuốn sách. Thay vì đọc từ đầu đến cuối để tìm một chương, bạn chỉ cần xem mục lục. Database index cũng hoạt động tương tự.
• Cách thêm Index trong phpMyAdmin:
  1. Xác định cột cần thêm index. Thường là các cột trong mệnh đề WHERE hoặc ORDER BY của query chậm.
  2. Chọn bảng tương ứng (ví dụ: xf_post).
  3. Chuyển sang tab Structure.
  4. Nhìn xuống phần Indexes, nhấp vào Add index.
  5. Trong cửa sổ hiện ra, đặt tên cho Index và chọn (các) cột bạn muốn thêm vào index.
  6. Nhấn "Go".
• Sau khi thêm index, hãy chạy lại lệnh EXPLAIN ở Bước 3. Bạn sẽ thấy các giá trị type, key và rows được cải thiện đáng kể.

Kết luận​

Phân tích và tối ưu hóa slow query là một kỹ năng nâng cao nhưng vô cùng đắc lực. Quy trình rất rõ ràng: Bật debug -> Tìm query chậm -> Phân tích bằng EXPLAIN -> Thêm Index -> Kiểm tra lại.

Lời khuyên cuối cùng: Nếu query chậm đến từ một add-on của bên thứ ba, cách tốt nhất là hãy sao chép thông tin phân tích EXPLAIN và gửi báo cáo cho nhà phát triển add-on đó. Họ sẽ là người sửa chữa và tối ưu nó một cách tốt nhất. Và đừng quên tắt chế độ debug sau khi đã hoàn thành công việc!

Chúc các bạn thành công.

VNXF.VN​