Các lỗi cú pháp hoặc thiết kế truy vấn SQL không chỉ làm giảm hiệu suất mà còn gây khó khăn trong việc mở rộng hệ thống. Dưới đây là tám sai lầm thường gặp và cách khắc phục hiệu quả.

1. Sử dụng LIMIT với offset lớn

Khi phân trang ở quy mô lớn, ví dụ LIMIT 1000000, 10, cơ sở dữ liệu vẫn phải duyệt qua một triệu bản ghi trước khi trả về 10 kết quả — dù có chỉ mục đi chăng nữa. Giải pháp tối ưu là áp dụng kỹ thuật keyset pagination:

SELECT id, type, name, create_time
FROM operation
WHERE type = 'SQLStats'
  AND name = 'SlowLog'
  AND create_time > '2024-05-10 09:30:00'
ORDER BY create_time
LIMIT 10;

Phương pháp này loại bỏ hoàn toàn chi phí tính toán offset và giữ thời gian thực thi ổn định bất kể kích thước bảng.

2. Chuyển đổi kiểu dữ liệu ngầm

Khi so sánh trường kiểu VARCHAR với giá trị số nguyên, MySQL tự động ép kiểu — dẫn đến việc không thể dùng chỉ mục:

EXPLAIN SELECT * FROM my_balance 
WHERE bpn = 14000000123;

Trong đó bpn được khai báo là VARCHAR(20). Hệ thống sẽ chuyển toàn bộ cột thành số để so sánh → mất khả năng sử dụng chỉ mục. Cách sửa đúng là đảm bảo kiểu dữ liệu đầu vào khớp với định nghĩa cột:

WHERE bpn = '14000000123'

3. Cập nhật/xóa thông qua subquery phụ thuộc

Câu lệnh UPDATE sau đây gây hiệu suất kém do MySQL thực hiện vòng lặp nội bộ cho từng hàng:

UPDATE task_queue
SET status = 'processing'
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM task_queue
    WHERE category = 'urgent' AND status = 'pending'
    ORDER BY priority DESC, created_at ASC
    LIMIT 1
  ) AS candidate
);

Thay vào đó, nên viết lại bằng JOIN để tận dụng kế hoạch thực thi tuyến tính:

UPDATE task_queue t
JOIN (
  SELECT id FROM task_queue
  WHERE category = 'urgent' AND status = 'pending'
  ORDER BY priority DESC, created_at ASC
  LIMIT 1
) AS candidate ON t.id = candidate.id
SET t.status = 'processing';

4. Sắp xếp hỗn hợp không tận dụng chỉ mục

Cụm ORDER BY is_active ASC, updated_at DESC không thể dùng chỉ mục nếu hai cột có thứ tự sắp xếp trái ngược nhau. Một giải pháp thay thế là tách truy vấn theo giá trị rời rạc:

(SELECT *, 0 AS sort_order FROM notification 
 WHERE is_active = 0 ORDER BY updated_at DESC LIMIT 20)
UNION ALL
(SELECT *, 1 AS sort_order FROM notification 
 WHERE is_active = 1 ORDER BY updated_at DESC LIMIT 20)
ORDER BY sort_order, updated_at DESC
LIMIT 20;

Cách này tận dụng được chỉ mục trên updated_at cho mỗi nhánh và tránh filesort toàn cục.

5. Thay EXISTS bằng JOIN khi có thể

Câu truy vấn kiểm tra sự tồn tại thường bị dịch thành subquery lồng — gây tổn thất hiệu năng nghiêm trọng:

SELECT u.name, u.email
FROM user u
WHERE EXISTS (
  SELECT 1 FROM login_log l 
  WHERE l.user_id = u.id AND l.login_date > '2024-05-01'
);

Viết lại dưới dạng INNER JOIN giúp tối ưu hóa bằng kế hoạch nối bảng và loại bỏ vòng lặp:

SELECT DISTINCT u.name, u.email
FROM user u
INNER JOIN login_log l ON u.id = l.user_id AND l.login_date > '2024-05-01';

6. Không đẩy điều kiện xuống subquery tổng hợp

Khi lọc kết quả từ subquery nhóm, nhiều lập trình viên quên rằng điều kiện WHERE có thể được đưa vào bên trong — giảm đáng kể khối lượng dữ liệu xử lý:

-- Không tối ưu
SELECT dept, COUNT(*) 
FROM (SELECT dept FROM employee GROUP BY dept) AS grouped 
WHERE dept LIKE 'ENG%';

-- Tối ưu: đẩy điều kiện vào trước khi nhóm
SELECT dept, COUNT(*)
FROM employee
WHERE dept LIKE 'ENG%'
GROUP BY dept;

7. Thực hiện join trên tập dữ liệu chưa được thu nhỏ

Truy vấn sau đây thực hiện LEFT JOIN trên toàn bộ bảng order_record, dù chỉ cần 20 bản ghi cuối cùng:

SELECT o.*, u.full_name, p.title
FROM order_record o
LEFT JOIN user u ON o.user_id = u.id
LEFT JOIN product p ON o.product_id = p.id
WHERE o.status = 'shipped'
ORDER BY o.shipped_at DESC
LIMIT 20;

Giải pháp là giới hạn tập con trước khi join:

SELECT o.*, u.full_name, p.title
FROM (
  SELECT id, user_id, product_id, shipped_at
  FROM order_record
  WHERE status = 'shipped'
  ORDER BY shipped_at DESC
  LIMIT 20
) AS o
LEFT JOIN user u ON o.user_id = u.id
LEFT JOIN product p ON o.product_id = p.id
ORDER BY o.shipped_at DESC;

8. Tái sử dụng tập kết quả trung gian bằng CTE

Khi một tập con được dùng nhiều lần trong truy vấn phức tạp, việc lặp lại subquery gây tổn thất hiệu năng và trùng lặp logic. Sử dụng CTE (Common Table Expression) giúp cải thiện cả hiệu năng lẫn khả năng bảo trì:

WITH top_customers AS (
  SELECT customer_id
  FROM sales
  WHERE sale_date >='2024-01-01'
  GROUP BY customer_id
  ORDER BY SUM(amount) DESC
  LIMIT 50
)
SELECT tc.customer_id, 
       COALESCE(r.total_revenue, 0) AS revenue,
       COALESCE(s.total_support_tickets, 0) AS tickets
FROM top_customers tc
LEFT JOIN (
  SELECT customer_id, SUM(revenue) AS total_revenue
  FROM revenue_log
  GROUP BY customer_id
) r ON tc.customer_id = r.customer_id
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS total_support_tickets
  FROM support_ticket
  GROUP BY customer_id
) s ON tc.customer_id = s.customer_id;