Thanaoscar

Bài 9: Tối ưu câu lệnh WHERE

Thana Oscar's photo
Thana Oscar
·

5 min read

Mệnh đề WHERE là một phần không thể thiếu trong các truy vấn SQL, giúp lọc dữ liệu dựa trên các điều kiện cụ thể. Tuy nhiên, nếu không được tối ưu hóa, WHERE có thể trở thành nguyên nhân gây ra các vấn đề về hiệu suất, đặc biệt là khi làm việc với lượng dữ liệu lớn. Bài viết này sẽ đi sâu vào các kỹ thuật tối ưu hóa mệnh đề WHERE, bao gồm sử dụng toán tử so sánh hiệu quả, tối ưu điều kiện logic, và tránh sử dụng hàm trong WHERE.

9.1. Sử dụng toán tử so sánh hiệu quả

Các toán tử so sánh trong mệnh đề WHERE có ảnh hưởng lớn đến hiệu suất của truy vấn. Sử dụng đúng toán tử có thể giúp tận dụng index và giảm thời gian thực thi.

9.1.1. Ưu tiên sử dụng = thay vì LIKE

  • Vấn đề: Toán tử LIKE thường kém hiệu quả hơn so với = vì nó yêu cầu quét toàn bộ bảng hoặc index, đặc biệt là khi sử dụng với ký tự đại diện (% hoặc _).

  • Giải pháp: Ưu tiên sử dụng = khi có thể, đặc biệt là khi so sánh với các giá trị cố định.

    • Ví dụ không tối ưu:

        SELECT * FROM employees WHERE first_name LIKE 'John';

    • Ví dụ tối ưu:

        SELECT * FROM employees WHERE first_name = 'John';
9.1.2. Tránh sử dụng NOT IN hoặc !=

  • Vấn đề: Toán tử NOT IN và != thường kém hiệu quả vì chúng yêu cầu quét toàn bộ bảng hoặc index để loại trừ các giá trị không phù hợp.

  • Giải pháp: Sử dụng các toán tử thay thế như EXISTS hoặc LEFT JOIN với điều kiện IS NULL.

    • Ví dụ không tối ưu:

        SELECT * FROM employees WHERE department_id NOT IN (1, 2, 3);

    • Ví dụ tối ưu:

        SELECT e.*
  FROM employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
  WHERE d.department_id IS NULL;

9.2. Tối ưu điều kiện logic

Các điều kiện logic trong mệnh đề WHERE cần được sắp xếp và viết một cách hợp lý để tối ưu hóa hiệu suất.

9.2.1. Sắp xếp điều kiện hợp lý

  • Vấn đề: Các điều kiện trong WHERE được đánh giá từ trái sang phải. Nếu các điều kiện phức tạp hoặc không hiệu quả được đặt trước, chúng có thể làm chậm truy vấn.

  • Giải pháp: Đặt các điều kiện đơn giản và hiệu quả lên trước để giảm số lượng bản ghi cần kiểm tra.

    • Ví dụ không tối ưu:

        SELECT * FROM employees WHERE salary > 50000 AND department_id = 1;

    • Ví dụ tối ưu:

        SELECT * FROM employees WHERE department_id = 1 AND salary > 50000;
9.2.2. Sử dụng EXISTS thay vì IN

  • Vấn đề: Toán tử IN có thể kém hiệu quả khi so sánh với một danh sách giá trị lớn hoặc một subquery phức tạp.

  • Giải pháp: Sử dụng EXISTS thay vì IN khi kiểm tra sự tồn tại của dữ liệu.

    • Ví dụ không tối ưu:

        SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

    • Ví dụ tối ưu:

        SELECT * FROM employees e
  WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location_id = 1);

9.3. Tránh sử dụng hàm trong WHERE

Sử dụng hàm trong mệnh đề WHERE có thể làm mất hiệu quả của index và dẫn đến hiệu suất kém.

9.3.1. Hàm làm mất hiệu quả của index

  • Vấn đề: Khi sử dụng hàm trên cột trong WHERE, database engine không thể sử dụng index trên cột đó, dẫn đến Table Scan.

  • Giải pháp: Tránh sử dụng các hàm như UPPER(), LOWER(), YEAR(), MONTH(), v.v. trong WHERE. Thay vào đó, hãy sử dụng các điều kiện trực tiếp trên cột.

    • Ví dụ không tối ưu:

        SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

    • Ví dụ tối ưu:

        SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
9.3.2. Cách viết lại query để tránh sử dụng hàm

  • Vấn đề: Sử dụng hàm trong WHERE có thể làm chậm truy vấn và tăng tải lên hệ thống.

  • Giải pháp: Viết lại query để tránh sử dụng hàm, hoặc sử dụng các cột tính toán sẵn.

    • Ví dụ không tối ưu:

        SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

    • Ví dụ tối ưu:

        SELECT * FROM employees WHERE last_name = 'Smith';

Kết luận

Tối ưu hóa mệnh đề WHERE là một quá trình quan trọng để đảm bảo hiệu suất cao và ổn định của hệ thống cơ sở dữ liệu. Bằng cách sử dụng toán tử so sánh hiệu quả, tối ưu điều kiện logic, và tránh sử dụng hàm trong WHERE, bạn có thể giảm đáng kể thời gian thực thi của truy vấn và tiết kiệm tài nguyên hệ thống. Trong các bài tiếp theo, chúng ta sẽ đi sâu vào các kỹ thuật tối ưu hóa khác, chẳng hạn như sử dụng stored procedure và function.

Từ vựng chuyên môn (Glossary)

  • WHERE: Điều kiện lọc dữ liệu.

  • LIKE: Toán tử so sánh chuỗi với ký tự đại diện.

  • NOT IN: Toán tử loại trừ các giá trị trong danh sách.

  • EXISTS: Toán tử kiểm tra sự tồn tại của dữ liệu.

  • Index: Chỉ mục.

  • Table Scan: Quét toàn bộ bảng.

  • UPPER: Hàm chuyển đổi chuỗi thành chữ hoa.

  • LOWER: Hàm chuyển đổi chuỗi thành chữ thường.

  • YEAR: Hàm trích xuất năm từ ngày.

  • MONTH: Hàm trích xuất tháng từ ngày.

  • BETWEEN: Toán tử so sánh trong khoảng giá trị.

sql optimize