Bảng đã có index đúng cột, vậy mà một câu SQL "bài viết liên quan" vẫn treo hàng chục giây, processlist đầy câu chồng lên nhau, load của máy database vọt lên ba chữ số. Đây là tình huống điển hình khi MySQL 8.4 chọn sai kế hoạch thực thi trên dữ liệu phân bố lệch. Bài này đi qua quy trình xử lý theo từng lần sửa, mỗi lần dạy một bài học: thứ tự ORDER BY, bảng dẫn của optimizer, giới hạn của FORCE INDEX, và một điều kiện <> tưởng như vô hại. Lời giải cuối cùng là tách truy vấn thành hai bước đơn giản, đưa thời gian từ vài giây xuống mức mili giây.

Triệu chứng

  • Máy database RAM nhỏ, không swap, max_connections và table_open_cache để cao.
  • Nhiều kết nối từ minion cùng ở trạng thái executing nhiều giây; khi lưu lượng tăng, có câu chạy tới vài phút.
  • Load average tăng vọt, CPU kín mỗi khi vài người cùng mở trang chi tiết bài viết.

Điều tra truy vấn trên MySQL 8.4

Bước 1: tìm câu đang chạy

Mọi thao tác điều tra trên máy MySQL 8.4 nên đi qua một tài khoản chỉ đọc:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 200)
FROM information_schema.processlist
WHERE COMMAND NOT IN ('Sleep', 'Daemon') AND ID != CONNECTION_ID()
ORDER BY TIME DESC;

Câu xuất hiện nhiều nhất:

SELECT posts.id, posts.name, posts.image, posts.description, posts.slug
FROM posts
INNER JOIN post_category_maps ON posts.id = post_category_maps.post_id
WHERE post_category_maps.post_category_id = ?
  AND posts.status = 1
  AND posts.id <> ?
ORDER BY posts.id DESC
LIMIT 5;

Đó là khối "bài liên quan" ở cuối mỗi trang chi tiết: lấy 5 bài mới nhất cùng danh mục, trừ bài đang xem.

Bước 2: index có rồi, vì sao MySQL 8.4 vẫn chậm?

SHOW CREATE TABLE cho thấy index không thiếu: posts có PRIMARY (id) và idx_status; bảng ánh xạ có idx_category_post (post_category_id, post_id). Nhưng EXPLAIN báo Using temporary; Using filesort trên toàn bộ bài của danh mục: MySQL lấy hết, join sang posts, đổ vào bảng tạm rồi mới sắp xếp để lấy 5 dòng. Nguyên nhân: ORDER BY posts.id sắp theo cột của bảng kia, nên optimizer không đi ngược được index (post_category_id, post_id) để dừng sớm.

-- sửa: sắp theo cột của bảng ánh xạ
ORDER BY post_category_maps.post_id DESC
-- EXPLAIN: Backward index scan; Using index

Hai cột bằng nhau qua điều kiện join nên kết quả giống hệt. Trên danh mục nhỏ, câu sửa nhanh hơn hàng trăm lần. Dễ tưởng là xong.

Bước 3: lần sửa đầu làm danh mục lớn chậm hơn

Khi một danh mục chiếm phần lớn số bài, optimizer của MySQL 8.4 so số dòng hai bảng và chọn posts làm bảng dẫn, quét toàn bảng, lại tạo bảng tạm và filesort. Câu trở nên chậm hơn cả bản gốc. Hướng sửa đúng cho dữ liệu nhỏ lại sai cho dữ liệu lớn, vì chỉ đo trên một danh mục nhỏ.

Bước 4: FORCE INDEX trên MySQL 8.4 chưa đủ

DB::table(DB::raw('post_category_maps FORCE INDEX (idx_category_post)'))
    ->join('posts', 'posts.id', '=', 'post_category_maps.post_id')
    ->where('post_category_maps.post_category_id', $category->id)
    ->where('posts.status', 1)
    ->where('posts.id', '<>', $post->id)
    ->orderBy('post_category_maps.post_id', 'desc')
    ->limit(5)

Đo thử trên danh mục lớn thấy nhanh hơn rõ rệt, kèm restart MySQL để dọn các câu đang treo và heap tích luỹ. Nhưng CPU vẫn vọt lên mỗi khi có vài request cùng lúc. Cần biết chính xác kế hoạch đang làm gì.

Bước 5: EXPLAIN ANALYZE lộ range scan do điều kiện <>

EXPLAIN chỉ cho ước tính. EXPLAIN ANALYZE của MySQL 8.4 thực thi câu lệnh và in số dòng, số vòng lặp, thời gian thật ở từng nút:

EXPLAIN ANALYZE
SELECT posts.id, ...
FROM post_category_maps FORCE INDEX (idx_category_post)
INNER JOIN posts ON posts.id = post_category_maps.post_id
WHERE post_category_maps.post_category_id = ?
  AND posts.status = 1
  AND posts.id <> ?
ORDER BY post_category_maps.post_id DESC LIMIT 5;

-> Nested loop inner join  (actual time=... rows=5 loops=1)
    -> Filter: (posts.status = 1)
        -> Index range scan on posts using PRIMARY
           over (id < X) OR (X < id)   (rows ≈ gần cả bảng)
    -> Single-row index lookup on post_category_maps
           using idx_category_post     (loops ≈ gần cả bảng)

Đây là mấu chốt. Với cột có index, optimizer được phép biến id <> X thành hai khoảng id < X và id > X, như mô tả trong tài liệu Range Optimization của MySQL 8.4. Hai khoảng đó gần như phủ cả bảng, nhưng trong mắt optimizer, posts giờ có một "range" trên khoá chính và trở nên hấp dẫn để làm bảng dẫn. FORCE INDEX chỉ ép index cho bảng ánh xạ, không ép thứ tự join. Kết quả: quét gần hết posts rồi tra ngược sang bảng ánh xạ cho từng dòng, vài giây CPU mỗi request.

Đây không phải bug của MySQL 8.4: optimizer làm đúng những gì nó được phép, chỉ là ước lượng chi phí sai với phân bố dữ liệu này.

Bước 6: tách hai bước để MySQL 8.4 không phải chọn

// Bước 1: vài id mới nhất của danh mục, đọc thẳng trên idx_category_post
$postIds = DB::table('post_category_maps')
    ->where('post_category_id', $category->id)
    ->where('post_id', '<>', $post->id)
    ->orderBy('post_id', 'desc')
    ->limit(10)
    ->pluck('post_id');

// Bước 2: lấy bài theo khoá chính, giữ model Eloquent
$related_posts = Post::select('id', 'name', 'image', 'description', 'slug')
    ->whereIn('id', $postIds)
    ->where('status', 1)
    ->orderBy('id', 'desc')
    ->limit(5)
    ->get();

Điều kiện <> vẫn còn, nhưng giờ nằm trên bảng ánh xạ, trong phạm vi một danh mục của index ghép, nên không kéo theo bảng lớn nào. Mỗi bước chỉ đọc vài dòng, tổng thời gian ở mức dưới một mili giây, và không còn phụ thuộc kích thước danh mục.

Lỗi hay gặp sau khi tối ưu: đổi kiểu dữ liệu trả về

Nếu bước 2 viết bằng DB::table('posts'), kết quả là stdClass thay vì model Post. View gọi accessor hay phương thức của model sẽ trả lỗi 500. Tối ưu truy vấn trong Laravel phải giữ nguyên kiểu dữ liệu mà view chờ đợi.

Nguyên nhân gốc

  1. JOIN hai bảng lớn kèm ORDER BY theo cột của bảng kia khiến MySQL không dùng được index để dừng sớm.
  2. Với danh mục lớn, optimizer của MySQL 8.4 chọn sai bảng dẫn. Điều kiện posts.id <> X thành range scan trên khoá chính, làm kế hoạch sai trông rẻ hơn thực tế.
  3. Cache "bài liên quan" theo từng bài không đủ: site nhiều bài thì mỗi lượt xem một bài chưa có trong cache vẫn là một lần chạy câu nặng.
  4. Máy RAM nhỏ, không swap, max_connections cao: khi query dồn lại, RAM và CPU không có chỗ thở.

Khắc phục truy vấn chậm trên MySQL 8.4

  • Tạm thời: kill các câu treo hoặc restart MySQL 8.4 để giải phóng kết nối và heap tích luỹ.
  • Lâu dài: tách truy vấn hai bước như trên, giữ model Eloquent ở bước 2.
  • Đánh đổi cần biết: lấy 10 id rồi mới lọc status = 1, nên nếu nhiều bài mới nhất đang ẩn, khối có thể hiện ít hơn 5 bài. Tăng số id ở bước 1 nếu cần.
  • Cấu hình máy, như quy trình trong bài Giảm RAM MySQL trên VPS nhỏ: tuning buffer và kết nối: đặt max_connections và table_open_cache theo nhu cầu thật, thêm swap làm lưới an toàn.

Phòng ngừa

  • Mọi câu truy vấn mới hoặc vừa sửa trên MySQL 8.4 phải chạy EXPLAIN ANALYZE trên dữ liệu lớn nhất nó sẽ gặp, không chỉ trên ví dụ đầu tiên.
  • Cẩn thận với mẫu "JOIN hai bảng lớn + <> trên khoá chính + ORDER BY bảng kia + LIMIT nhỏ". Ưu tiên lấy id từ index trước, lấy bản ghi theo khoá chính sau.
  • FORCE INDEX là gợi ý hẹp: không ép thứ tự join. Đừng coi nó là lời giải cuối.
  • Bật slow query log với ngưỡng vừa phải trên các máy MySQL 8.4 và 8.0 dùng chung, để thấy câu chậm trước khi load tăng vọt.
  • Sau mỗi lần đổi kiểu trả về (Eloquent sang query builder), mở thử trang thật trước khi coi là xong.

Với vấn đề khoá thay vì kế hoạch truy vấn, xem bài Deadlock MySQL: đọc gap lock và insert intention trong InnoDB status.

Bài học

  • Có index chưa đủ. Thứ tự sắp xếp, bảng dẫn và điều kiện lọc đều có thể khiến MySQL 8.4 bỏ qua index tốt nhất.
  • Đo trên trường hợp xấu nhất. Phân bố dữ liệu quyết định kế hoạch. Một lần sửa nhanh hơn hẳn trên danh mục nhỏ có thể chậm hơn hẳn trên danh mục lớn.
  • EXPLAIN đoán, EXPLAIN ANALYZE đếm. Chỉ khi thấy số vòng lặp thật mới hiểu vì sao FORCE INDEX chưa đủ.
  • Câu đơn giản dễ đoán hơn câu thông minh. Hai truy vấn theo index và khoá chính không cho optimizer của MySQL 8.4 cơ hội chọn sai.

Bài tiếp theo

CPU 100% trên MySQL: lock chờ nhau và index còn thiếu. Một ứng dụng bán hàng làm CPU database kín mỗi sáng, và cách thử index ngay trên production mà không để schema lệch khỏi code.