Tối ưu Index Merge trong MySQL: Hiểu đúng Intersection, Union và cách xử lý khi Optimizer chọn sai

So sánh các hướng tìm kiếm dữ liệu trong MySQL

Giả sử bạn quản lý một bảng users với hơn 10 triệu bản ghi. Sếp yêu cầu: “Lấy danh sách nhân viên thuộc phòng Marketing có quê ở Đà Nẵng”. MySQL sẽ tìm dữ liệu này thế nào? Dưới đây là 4 cách tiếp cận phổ biến:

• Approach 1: Full Table Scan (Quét toàn bộ bảng)
  MySQL duyệt qua từng dòng trong bảng từ đầu đến cuối. Cách này tốn disk I/O khủng khiếp. Khi dữ liệu chạm mốc vài triệu dòng, query thường mất từ 3 đến 8 giây để phản hồi.
• Approach 2: Single Index Scan (Dùng một index đơn)
  MySQL dùng index department_id để lọc ra 200.000 nhân viên Marketing. Tiếp đó, hệ thống đọc từng bản ghi từ clustered index để kiểm tra quê quán. Cách này nhanh hơn quét toàn bảng, nhưng số lượt truy xuất ngẫu nhiên (random I/O) vẫn rất lớn.
• Approach 3: Index Merge (Gộp nhiều index)
  Hệ thống đọc song song hai index riêng biệt: idx_dept và idx_city. Thay vì đọc dữ liệu gốc ngay, MySQL lấy hai danh sách Primary Key (row ID) rồi tìm phần giao hoặc hợp trong bộ nhớ. Sau khi có danh sách khóa chính đã lọc, nó mới lấy dữ liệu thực tế.
• Approach 4: Composite Index (Index đa cột)
  Bạn tạo sẵn index trên cặp (department_id, city). Dữ liệu đã được sắp xếp sẵn theo thứ tự logic. MySQL chỉ cần nhảy tới đúng vị trí và đọc liên tục trong vài mili-giây mà không tốn công gộp ID.

Phân tích ưu nhược điểm của các cơ chế Index Merge

Nhiều lập trình viên lầm tưởng cứ đánh index riêng lẻ từng cột là truy vấn tự động chạy nhanh. Thực tế, Cost-based Optimizer (CBO) của MySQL phải tính toán chi phí trước khi quyết định gộp index. Đôi khi lựa chọn này dẫn đến bottleneck nghiêm trọng.

Index Merge thường xuất hiện dưới ba cơ chế chính:

1. Using intersect (Giao thoa chỉ mục)

Cơ chế này xuất hiện khi câu truy vấn nối các cột có index đơn bằng toán tử AND:

SELECT id, username, email FROM users 
WHERE department_id = 4 AND status = 'active';

Ưu điểm: Tận dụng được các index có sẵn. Bạn không cần tạo thêm index mới làm chậm thao tác ghi đĩa.
Nhược điểm: MySQL phải nạp toàn bộ tập khóa chính từ cả hai index vào RAM để đối chiếu. Nếu mỗi điều kiện khớp với 300.000 dòng, thuật toán intersect ngốn rất nhiều CPU. Thời gian xử lý có thể kéo dài tới 1.5 giây, chậm hơn cả việc dùng một index duy nhất.

2. Using union (Hợp nhất chỉ mục)

Kịch bản này xảy ra khi câu lệnh chứa toán tử OR:

SELECT id, username, email FROM users 
WHERE email = '[email protected]' OR phone_number = '0901234567';

Ưu điểm: Với phép OR, nếu chỉ dùng một index thì nhánh còn lại sẽ khiến MySQL phải quét toàn bảng. Index Merge Union cho phép duyệt cả hai index rồi gộp kết quả lại.
Nhược điểm: Hệ thống tốn bộ nhớ đệm để loại bỏ các khóa chính trùng lặp (deduplication) trước khi trả về bản ghi.

3. Using sort_union (Hợp nhất sau khi sắp xếp)

Tương tự như Union, nhưng cơ chế này kích hoạt khi điều kiện OR chứa phép so sánh khoảng (như >, <, BETWEEN):

SELECT id, name FROM products 
WHERE price < 50000 OR category_id = 12;

Ưu điểm: Tránh được việc quét toàn bộ bảng khi mệnh đề OR chứa điều kiện phức tạp.
Nhược điểm: Tốn tài nguyên RAM lớn. Do tập row ID từ range scan không có thứ tự đồng nhất, MySQL buộc phải dùng sort_buffer_size để sắp xếp lại toàn bộ khóa chính trước khi gộp.

Quy tắc xử lý khi Optimizer chọn sai kế hoạch thực thi

Bộ tối ưu hóa của MySQL tính toán chi phí dựa trên số liệu thống kê (cardinality và số trang dữ liệu ước tính). Tuy nhiên, thống kê thường bị lệch khi dữ liệu phân bố không đồng đều (data skew). Hậu quả là Optimizer chọn Index Merge thay vì một index tối ưu hơn.

Dưới đây là các tiêu chuẩn thực chiến để bạn quyết định:

• Giữ nguyên Index Merge: Áp dụng cho phép OR trên các cột có độ chọn lọc cao (high cardinality). Giải pháp này cũng phù hợp với bảng có tần suất ghi lớn (hơn 1.000 write/giây), nơi việc tạo thêm index sẽ gây suy giảm thông lượng INSERT/UPDATE.
• Thay bằng Composite Index: Áp dụng cho các truy vấn AND chạy với tần suất cao. Composite Index (col1, col2) giúp giảm query time từ 1.200ms xuống dưới 8ms vì loại bỏ hoàn toàn bước lọc ID trong RAM.
• Tắt Index Merge hoặc can thiệp bằng hint: Cần thực hiện ngay khi Optimizer cố gộp hai tập dữ liệu lớn khiến CPU chạm ngưỡng 100%.

Các bước chẩn đoán và khắc phục thực tế

Bước 1: Nhận diện Index Merge qua EXPLAIN

Chạy lệnh phân tích trên câu query có độ trễ bất thường:

EXPLAIN SELECT id, username FROM users 
WHERE role = 'manager' AND created_at >= '2026-01-01';

Nếu cột type trả về index_merge và cột Extra ghi nhận Using intersect(idx_role, idx_created_at), MySQL đang gộp hai index đơn lẻ này.

Bước 2: Thay thế bằng Composite Index chuẩn

Với các câu query dùng phép AND, một composite index đúng thứ tự sẽ giải quyết triệt để vấn đề:

-- Đặt cột có độ chọn lọc cao hơn hoặc điều kiện bằng (=) lên trước
CREATE INDEX idx_role_created_at ON users (role, created_at);

Khi kiểm tra lại bằng EXPLAIN, cột type sẽ chuyển sang range hoặc ref. Cột key nhận trực tiếp idx_role_created_at. Bước xử lý trung gian trên RAM hoàn toàn biến mất.

Bước 3: Can thiệp bằng Optimizer Switch hoặc Index Hints

Nếu bảng production quá lớn và bạn chưa thể chạy DDL migration ngay, hãy tắt Index Merge ở cấp session:

-- Tắt toàn bộ tính năng index merge cho session hiện tại
SET SESSION optimizer_switch = 'index_merge=off';

-- Hoặc chỉ tắt riêng cơ chế Intersect
SET SESSION optimizer_switch = 'index_merge_intersection=off';

Để xử lý riêng từng query mà không ảnh hưởng kết nối khác, bạn dùng Index Hint cổ điển:

-- Ép MySQL chỉ dùng index created_at
SELECT id, username FROM users USE INDEX (idx_created_at)
WHERE role = 'manager' AND created_at >= '2026-01-01';

Hoặc sử dụng cú pháp Optimizer Hint chuẩn từ MySQL 8.0:

SELECT /*+ NO_INDEX_MERGE(users idx_role, idx_created_at) */ id, username 
FROM users 
WHERE role = 'manager' AND created_at >= '2026-01-01';

Hiểu rõ bản chất của Index Merge giúp bạn chủ động kiểm soát kế hoạch thực thi, giữ p99 latency ở mức thấp và bảo vệ database trước các đợt tăng tải đột biến.