Làm thế nào để tối ưu các truy vấn SQL chậm?

Question

Accepted Answer

Tối ưu các truy vấn chậm bắt đầu bằng việc **phân tích execution plan** (`EXPLAIN`) để tìm điểm nghẽn, rồi áp dụng các sửa chữa có mục tiêu — phổ biến nhất là **thêm index**, viết lại truy vấn, và tránh các anti-pattern phổ biến. Chiến thắng lớn nhất thường là biến các full table scan thành index lookup.

## Bước 1: phân tích với EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- hiển thị EXECUTION PLAN: loại scan (Seq Scan = quét toàn bộ, Index Scan = dùng index),
-- số dòng ước tính/thực tế, chi phí và thời gian → tiết lộ thời gian đi ĐÂU
```

`EXPLAIN` (hoặc `EXPLAIN ANALYZE`) cho thấy database thực thi truy vấn như thế nào — quan trọng là liệu nó đang thực hiện **full table scan** (chậm) hay dùng **index** (nhanh). Luôn profile trước khi tối ưu.

## Chiến thắng lớn nhất: thêm index

```sql
-- nếu EXPLAIN cho thấy Seq Scan trên một cột được lọc/join → thêm index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- biến một full scan thành một index lookup nhanh
```

Sửa chữa phổ biến và hiệu quả nhất: **thêm index** trên các cột dùng trong `WHERE`, `JOIN` và `ORDER BY`. Điều này biến các full table scan thành các lookup nhanh.

## Các anti-pattern phổ biến cần sửa

```sql
-- ❌ hàm trên một cột ngăn việc dùng index
WHERE UPPER(email) = 'X@Y.COM'        -- không dùng được index trên email
WHERE email = 'x@y.com'               -- ✅ có thể dùng index

-- ❌ LIKE với wildcard ở đầu không dùng được index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (tiền tố) có thể dùng index

-- ❌ SELECT * lấy các cột không cần
SELECT * → SELECT only_needed_columns  -- ít dữ liệu hơn, có thể dùng covering index

-- ❌ truy vấn N+1 (truy vấn trong vòng lặp) → dùng một JOIN duy nhất thay thế
```

## Các kỹ thuật tối ưu khác

```text
✓ Sửa truy vấn N+1 (một truy vấn cho mỗi dòng) → dùng JOIN / gộp vào một truy vấn
✓ Thêm index phù hợp (gồm cả COMPOSITE cho lọc nhiều cột)
✓ Giảm dữ liệu được quét — lọc sớm, chỉ chọn cột cần, phân trang
✓ Viết lại subquery thành JOIN khi hiệu quả hơn
✓ Dùng EXISTS thay cho IN cho kiểm tra tồn tại trên các subquery lớn
✓ Giữ thống kê được cập nhật (ANALYZE) để planner ra quyết định tốt
✓ Cân nhắc phi chuẩn hóa / materialized view cho các truy vấn lặp lại tốn kém
✓ Để ý index thiếu trên FOREIGN KEY (join quét bảng nếu không có chúng)
```

## Tại sao điều này quan trọng

Tối ưu truy vấn là kiến thức then chốt cho hiệu năng database — truy vấn chậm là một nguyên nhân hàng đầu của hiệu năng ứng dụng kém, và hiểu cách tìm và sửa chúng là kỹ năng cấp senior thiết yếu cho bất kỳ ai làm việc với database ở quy mô lớn.

Kỷ luật nền tảng là **phân tích với `EXPLAIN`** (xem execution plan để tìm điểm nghẽn thực sự — quan trọng là liệu truy vấn thực hiện full table scan chậm hay index lookup nhanh) trước khi tối ưu, thay vì đoán mò — cách tiếp cận dựa trên đo lường này là cái khiến tối ưu hiệu quả. **Chiến thắng đơn lẻ lớn nhất và phổ biến nhất là thêm index** trên các cột được lọc, join và sắp xếp (biến full table scan thành lookup nhanh — thường cải thiện thời gian truy vấn theo bậc độ lớn), khiến kiến thức về index trở thành trung tâm của tối ưu.

Hiểu các **anti-pattern phổ biến** cũng quan trọng không kém: hàm trên cột và `LIKE` với wildcard ở đầu ngăn việc dùng index, `SELECT *` lấy dữ liệu không cần thiết, và đặc biệt là **truy vấn N+1** (truy vấn trong vòng lặp thay vì một JOIN duy nhất — một sát thủ hiệu năng thường gặp, nghiêm trọng trong code ứng dụng) — nhận ra và sửa chúng giải quyết các nguyên nhân phổ biến nhất của truy vấn chậm.

Các kỹ thuật khác (composite index, giảm dữ liệu được quét, viết lại subquery thành JOIN, giữ thống kê cập nhật, materialized view cho các truy vấn tốn kém) hoàn thiện bộ công cụ.

Vì hiệu năng truy vấn ảnh hưởng trực tiếp đến độ phản hồi và khả năng mở rộng của ứng dụng, và vì cách tiếp cận có hệ thống (phân tích với EXPLAIN, rồi sửa — thường bằng cách thêm index và loại bỏ các anti-pattern như N+1) là cái giữ cho database nhanh dưới tải, thành thạo tối ưu truy vấn là kiến thức cấp senior quan trọng, giá trị cao phân biệt các lập trình viên có thể xây ứng dụng dựa trên database hiệu năng, mở rộng được — một kỹ năng thường liên quan, then chốt và là một chủ đề phỏng vấn phổ biến cho hiệu năng database.