Posted inMySQL

MySQL Hash Join: ‘Cứu cánh’ cho Query thiếu Index trên MySQL 8.0.18+

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

Cơn ác mộng 2 giờ sáng và ‘phép màu’ Hash Join

Điện thoại mình bỗng rung bần bật. Cảnh báo hệ thống báo về: CPU database server chạm ngưỡng 98%. Kiểm tra processlist, mình phát hiện một câu query báo cáo đang treo cứng. Một bạn dev mới deploy code đã thực hiện JOIN hai bảng orders (5 triệu dòng) và promotions (100.000 dòng) trên cột campaign_code nhưng lại quên đánh Index.

Với các phiên bản MySQL cũ, đây thực sự là thảm họa. Tuy nhiên, may mắn là server hôm đó chạy MySQL 8.0.25. Nhờ cơ chế Hash Join được giới thiệu từ bản 8.0.18, câu query vốn dĩ phải mất vài tiếng để chạy xong đã kết thúc trong chưa đầy 30 giây.

Trong bài này, mình sẽ cùng anh em mổ xẻ tại sao Hash Join lại mạnh mẽ đến thế và cách cấu hình nó để ‘vượt cạn’ những tình huống thiếu index trên Production.

Tại sao các cách JOIN cũ lại chậm?

1. Nested Loop Join (NLJ) – Kẻ đốt CPU

Đây là cách tiếp cận thô sơ nhất. MySQL lấy từng dòng từ bảng A, rồi lại chạy đi quét bảng B để tìm dòng khớp. Nếu bảng B không có index, MySQL phải thực hiện Full Table Scan cho mỗi dòng của bảng A. Hãy tưởng tượng bảng A có 10.000 dòng và bảng B có 10.000 dòng. Tổng cộng bạn phải thực hiện 100 triệu phép so sánh. CPU nào gánh nổi?

2. Block Nested Loop (BNL) – Bản cải tiến nửa vời

Để giảm bớt nỗi đau, MySQL dùng join_buffer_size. Thay vì lấy từng dòng, nó nạp một cụm dòng từ bảng A vào bộ đệm, sau đó quét bảng B một lần để so sánh với cả cụm đó. Cách này giảm số lần quét bảng B nhưng bản chất vẫn là so sánh từng cặp (Pairwise comparison). Nó vẫn cực kỳ tốn tài nguyên khi dữ liệu lớn dần.

Hash Join – Bước ngoặt về hiệu năng

Từ MySQL 8.0.20, Hash Join đã chính thức thay thế hoàn toàn Block Nested Loop. Thay vì so sánh thủ công, MySQL chuyển sang dùng bảng băm (Hash Table) với hai giai đoạn:

  • Build phase: MySQL chọn bảng nhỏ hơn (ví dụ bảng promotions), quét một lần và đưa các dòng vào một Hash Table trong bộ nhớ. Key của bảng băm chính là giá trị của cột JOIN.
  • Probe phase: MySQL quét bảng còn lại (bảng orders). Với mỗi dòng, nó chỉ cần tính hash của cột JOIN và tra cứu trong Hash Table. Việc tra cứu này diễn ra với độ phức tạp gần như là $O(1)$.

Tổng độ phức tạp lúc này chỉ còn là $O(M + N)$. Đây là sự thay đổi mang tính đột phá so với $O(M imes N)$ của các phương pháp cũ.

Dấu hiệu nhận biết MySQL đang dùng Hash Join

Thông thường, MySQL sẽ tự động kích hoạt Hash Join khi bạn thực hiện phép toán bằng (Equi-join) trên các cột không có index. Để kiểm tra, bạn đừng dùng EXPLAIN thông thường vì nó sẽ không hiển thị rõ. Hãy dùng EXPLAIN FORMAT=TREE:

EXPLAIN FORMAT=TREE 
SELECT * 
FROM orders o 
JOIN promotions p ON o.campaign_code = p.campaign_code;

Nếu kết quả có dòng Inner hash join, bạn có thể yên tâm:

-> Inner hash join (p.campaign_code = o.campaign_code)  (cost=0.70 rows=1)
    -> Table scan on p  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on o  (cost=0.35 rows=1)

Kinh nghiệm thực chiến: Tối ưu và Debug

Mặc dù Hash Join rất ‘xịn’, nhưng nó không phải là vạn năng. Dưới đây là vài lưu ý xương máu khi mình vận hành thực tế:

1. Đừng để join_buffer_size quá nhỏ

Hash Join ‘sống’ dựa vào RAM. Nếu bảng băm vượt quá kích thước join_buffer_size, MySQL sẽ phải ghi dữ liệu tạm xuống đĩa cứng (on-disk hash join). Tốc độ lúc này sẽ giảm thê thảm. Với các server hiện đại có 32GB-64GB RAM, mình thường cân nhắc tăng buffer này lên khoảng 16MB đến 64MB cho các phiên làm việc nặng:

-- Tăng lên 64MB để xử lý query báo cáo
SET SESSION join_buffer_size = 1024 * 1024 * 64;

2. Sử dụng công cụ hỗ trợ debug dữ liệu

Khi làm việc với các cột JOIN chứa chuỗi dài hoặc dữ liệu phức tạp, mình thường dùng Hash Generator của ToolCraft để test thử các thuật toán hash. Điều này giúp mình verify xem dữ liệu có bị collision (trùng mã hash) hay không khi migrate. Một điểm cộng là tool này chạy hoàn toàn ở trình duyệt, không lo lộ dữ liệu nhạy cảm của khách hàng.

Ngoài ra, nếu kết quả EXPLAIN FORMAT=JSON quá khó đọc, bạn có thể quăng nó vào JSON Formatter. Việc nhìn rõ cấu trúc cây query sẽ giúp bạn biết bảng nào đang bị scan nhầm, từ đó điều chỉnh query cho đúng.

3. Đừng bỏ quên Index

Đừng vì Hash Join quá mạnh mà lười đánh Index. Index vẫn là lựa chọn số một nếu bạn chỉ cần lấy ra vài dòng dữ liệu cụ thể. Hash Join chỉ thực sự tỏa sáng khi bạn buộc phải xử lý một lượng lớn dữ liệu mà không thể tận dụng index.

Lời kết

Hash Join là một bước tiến lớn giúp MySQL thu hẹp khoảng cách với PostgreSQL trong mảng xử lý dữ liệu lớn. Nó giúp hệ thống của bạn ‘sống sót’ qua những sai lầm ngớ ngẩn của dev hoặc những câu query báo cáo đột xuất.

Hãy nhớ: Luôn nâng cấp lên MySQL 8.0.18+, kiểm tra query bằng FORMAT=TREE và đừng ngần ngại tăng join_buffer_size khi cần thiết. Chúc anh em có những đêm ngon giấc, không bị dựng dậy bởi những câu query ‘vô tri’!

Share:
Tags: