Thanaoscar

Bài 2: Tối ưu điều kiện WHERE – Tại sao dùng hàm trên cột lại làm chậm query?

Thana Oscar's photo
Thana Oscar
·

4 min read

Dưới đây là nội dung chi tiết cho Bài 2: Tối ưu điều kiện WHERE – Phân tích sâu nguyên nhân và giải pháp:

Hiểu cơ chế index và cách tránh "bẫy" hiệu năng

1. Vấn đề: Dùng hàm trên cột trong WHERE

Ví dụ điển hình

-- Tìm tên khách hàng viết hoa  
SELECT *  
FROM customers  
WHERE UPPER(name) = 'ALICE';  -- Chậm!  

-- Lọc theo phần của ngày  
SELECT *  
FROM orders  
WHERE MONTH(order_date) = 12;  -- Chậm!

Hậu quả

  • Query chậm hơn hàng chục đến hàng trăm lần, đặc biệt trên bảng lớn.

  • Tăng tải CPU và I/O do phải quét toàn bộ dữ liệu.

2. Nguyên nhân sâu: Index không được sử dụng

Cơ chế hoạt động của Index

  • Index (B-tree, Hash, v.v.) lưu trữ giá trị gốc của cột để tìm kiếm nhanh.

  • Khi dùng hàm trên cột, giá trị trong index không khớp với kết quả hàm → Database phải quét toàn bộ bảng (Full Table Scan).

Ví dụ minh họa

Giả sử cột name có index, và bảng có 1 triệu bản ghi:

  • Truy vấn tối ưu: WHERE name = 'Alice'
    → Index được dùng, chỉ quét 1-2 rows.

  • Truy vấn không tối ưu: WHERE UPPER(name) = 'ALICE'
    → Database phải:

    1. Đọc 1 triệu rows.

    2. Áp dụng hàm UPPER() lên từng name.

    3. So sánh kết quả với 'ALICE'.

Execution Plan minh họa

Với truy vấn chậm:

EXPLAIN  
SELECT * FROM customers WHERE UPPER(name) = 'ALICE';

Kết quả:

  • Type: ALL (Full Table Scan)

  • Rows: 1,000,000

Với truy vấn tối ưu:

EXPLAIN  
SELECT * FROM customers WHERE name = 'Alice';

Kết quả:

  • Type: ref (Index Lookup)

  • Key: idx_name

  • Rows: 1

3. Giải pháp chi tiết

3.1. Tránh biến đổi cột trong WHERE

Nguyên tắc: Giữ nguyên cột ở vế trái điều kiện.

Ví dụ áp dụng

  • Thay vì:

      WHERE MONTH(order_date) = 12  -- Lọc tháng 12

  • Hãy viết:

      WHERE order_date >= '2023-12-01' AND order_date < '2024-01-01'

    → Index trên order_date được sử dụng.

3.2. Chuẩn hóa dữ liệu từ đầu

  • Áp dụng khi: Dữ liệu cần được biến đổi thường xuyên (ví dụ: lưu tên in hoa).

  • Cách làm: Thêm cột phụ hoặc trigger để tự động chuẩn hóa.

Ví dụ:

-- Thêm cột name_upper  
ALTER TABLE customers ADD COLUMN name_upper VARCHAR(255);  
UPDATE customers SET name_upper = UPPER(name);  

-- Tạo index trên cột mới  
CREATE INDEX idx_name_upper ON customers(name_upper);  

-- Truy vấn nhanh  
SELECT * FROM customers WHERE name_upper = 'ALICE';

3.3. Sử dụng Function-Based Index (nếu hỗ trợ)

  • Database hỗ trợ: Oracle, PostgreSQL, SQL Server (> 2016).

  • Cách làm: Tạo index dựa trên hàm áp dụng lên cột.

Ví dụ trên PostgreSQL:

CREATE INDEX idx_upper_name ON customers (UPPER(name));  

-- Truy vấn sử dụng index  
SELECT * FROM customers WHERE UPPER(name) = 'ALICE';

4. Case Study: So sánh hiệu năng

Bối cảnh

  • Bảng orders có 10 triệu đơn hàng, index trên order_date.

  • Yêu cầu: Lấy đơn hàng trong tháng 12/2023.

Cách tiếp cận 1: Dùng hàm MONTH()

SELECT * FROM orders  
WHERE MONTH(order_date) = 12 AND YEAR(order_date) = 2023;

  • Execution Plan: Full Table Scan.

  • Thời gian: 2.5 giây.

Cách tiếp cận 2: Dùng phạm vi ngày

SELECT * FROM orders  
WHERE order_date >= '2023-12-01' AND order_date < '2024-01-01';

  • Execution Plan: Index Range Scan.

  • Thời gian: 0.02 giây.

Kết luận:

  • Cải thiện 125 lần nhờ tận dụng index.

5. Bài tập thực hành

Viết lại các truy vấn sau để tối ưu hiệu năng:

  1. Truy vấn gốc:

     SELECT * FROM products  
 WHERE LOWER(product_name) = 'iphone 15';

  2. Truy vấn gốc:

     SELECT * FROM logs  
 WHERE CAST(log_time AS DATE) = '2023-10-01';

Gợi ý đáp án:

  1. Chuẩn hóa dữ liệu hoặc dùng Function-Based Index:

     -- Cách 1: Thêm cột phụ  
 ALTER TABLE products ADD COLUMN product_name_lower VARCHAR(255);  
 UPDATE products SET product_name_lower = LOWER(product_name);  
 CREATE INDEX idx_product_lower ON products(product_name_lower);  

 SELECT * FROM products WHERE product_name_lower = 'iphone 15';  

 -- Cách 2 (PostgreSQL):  
 CREATE INDEX idx_lower_name ON products (LOWER(product_name));  
 SELECT * FROM products WHERE LOWER(product_name) = 'iphone 15';

  2. Tránh dùng CAST trên cột:

     SELECT * FROM logs  
 WHERE log_time >= '2023-10-01 00:00:00' AND log_time < '2023-10-02 00:00:00';

6. Tổng kết

  • Luôn ưu tiên giữ nguyên giá trị gốc của cột trong điều kiện WHERE.

  • Chuẩn hóa dữ liệu từ đầu hoặc dùng Function-Based Index nếu cần biến đổi.

  • Kiểm tra Execution Plan để xác nhận index có được sử dụng không.

Hiểu sâu về index và thứ tự xử lý query giúp bạn tránh những lỗi hiệu năng "chết người"!

Preview bài tiếp theo:
Bài 3: Sử dụng EXISTS thay vì IN – Khi nào và tại sao?

SQL