Trong quá trình vận hành các hệ thống cơ sở dữ liệu lớn, việc phát sinh các câu lệnh SQL có cấu trúc phức tạp là điều khó tránh khỏi. Một trong những tình huống gây ảnh hưởng nghiêm trọng đến hiệu năng là sử dụng quá nhiều điều kiện OR trong mệnh đề WHERE.

Bối cảnh thực tế

Giả sử chúng ta có bảng sales_order_detail chứa khoảng 40 triệu bản ghi. Một công cụ tự động hoặc framework ORM nào đó sinh ra câu lệnh truy vấn với hàng ngàn điều kiện OR nối tiếp nhau như sau:

SELECT * FROM sales_order_detail 
WHERE 
   (warehouse_id = 10 AND district_id = 5 AND order_id = 1001 AND line_no = 1) 
OR (warehouse_id = 10 AND district_id = 5 AND order_id = 1005 AND line_no = 2)
OR (warehouse_id = 10 AND district_id = 5 AND order_id = 1010 AND line_no = 3)
...
-- Tổng cộng có khoảng 10.000 cụm điều kiện OR như thế này

Với số lượng điều kiện khổng lồ, độ dài của câu lệnh SQL có thể lên tới gần 1MB. Trong môi trường thử nghiệm, câu lệnh này mất hơn 50 giây để hoàn tất, quét qua hơn 75.000 dòng dữ liệu nhưng chỉ trả về khoảng 8.000 kết quả.

Phân tích nguyên nhân

Việc quét 75.000 dòng trên một bảng 40 triệu dòng không phải là con số quá lớn, nhưng thời gian phản hồi lên tới gần 1 phút là dấu hiệu bất thường. Khi kiểm tra cảnh báo từ hệ thống bằng lệnh SHOW WARNINGS, chúng ta sẽ thấy thông báo:

Level: Warning
Code: 3170
Message: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

Cảnh báo này liên quan đến tham số range_optimizer_max_mem_size. Đây là biến hệ thống được giới thiệu từ MySQL 5.7.9 nhằm kiểm soát lượng bộ nhớ mà trình tối ưu hóa (Optimizer) sử dụng khi thực hiện phương pháp truy cập phạm vi (range access method).

Giá trị mặc định của tham số này thường là 8MB. Khi mệnh đề WHERE chứa quá nhiều điều kiện OR hoặc AND, trình tối ưu hóa ước tính bộ nhớ cần thiết để xử lý vượt quá giới hạn này. Khi đó, nó sẽ từ bỏ kế hoạch tối ưu hóa phạm vi và chuyển sang các phương án kém hiệu quả hơn, thậm chí là quét toàn bộ bảng (Full Table Scan).

Giải pháp khắc phục

1. Điều chỉnh cấu hình hệ thống

Nếu tăng giới hạn bộ nhớ cho trình tối ưu hóa lên mức cao hơn (ví dụ: 24MB hoặc 32MB), MySQL có thể thực hiện tối ưu hóa phạm vi một cách chính xác.

SET SESSION range_optimizer_max_mem_size = 25165824; -- 24MB

Sau khi điều chỉnh, kết quả thực tế cho thấy:

• Thời gian thực thi giảm từ 56 giây xuống còn khoảng 6 giây.
• Số lượng dòng cần quét giảm từ 75.000 xuống đúng bằng số dòng trả về (8.192 dòng).
• Số lượng trang dữ liệu (pages) cần đọc giảm đáng kể.

Lúc này, thay vì quét dải rộng, MySQL thực hiện tương đương với 10.000 lần tìm kiếm trực tiếp trên chỉ mục (index lookup).

2. Tối ưu hóa cấu trúc câu lệnh SQL

Việc tăng bộ nhớ chỉ là giải pháp tình thế. Một cách tiếp cận bền vững hơn là viết lại câu lệnh SQL để giảm bớt độ phức tạp. Thay vì liệt kê hàng vạn điều kiện rời rạc, hãy cố gắng tìm ra quy luật của dữ liệu để sử dụng toán tử BETWEEN hoặc IN.

SELECT * FROM sales_order_detail 
WHERE warehouse_id = 10 
  AND district_id = 5 
  AND order_id BETWEEN 1000 AND 3000
  AND line_no BETWEEN 1 AND 15;

Kết quả khi áp dụng cách này thường vượt trội:

• Thời gian thực thi có thể giảm xuống mức mili giây (ví dụ: từ 6 giây xuống 6ms).
• Chi phí CPU và I/O của hệ thống được giảm thiểu tối đa.

Lời khuyên kỹ thuật

Sử dụng quá nhiều điều kiện OR là một "anti-pattern" trong SQL. Khi làm việc với các framework như Hibernate, Entity Framework hay các bộ thư viện xây dựng câu lệnh SQL tự động, lập trình viên cần đặc biệt lưu ý:

• Hạn chế việc truyền các mảng ID quá lớn vào mệnh đề IN hoặc tạo chuỗi OR dài vô tận.
• Nên chia nhỏ truy vấn thành nhiều đợt nếu số lượng điều kiện quá lớn.
• Sử dụng bảng tạm (Temporary Table) để chứa các giá trị điều kiện, sau đó thực hiện JOIN với bảng chính để lấy dữ liệu.