Giảm thiểu truy vấn N+1 để tối ưu hiệu suất cơ sở dữ liệu SQL

Giới thiệu

Truy vấn N+1 là một vấn đề hiệu suất phổ biến trong các ứng dụng sử dụng cơ sở dữ liệu. Nó xảy ra khi một ứng dụng thực hiện một truy vấn để lấy danh sách các bản ghi cha, sau đó thực hiện một truy vấn bổ sung cho mỗi bản ghi cha để lấy dữ liệu con liên quan. Điều này dẫn đến việc có N+1 truy vấn thay vì một truy vấn duy nhất hiệu quả hơn, gây ảnh hưởng lớn đến thời gian phản hồi của ứng dụng và tăng tải cho cơ sở dữ liệu. Giải quyết các truy vấn N+1 là bước cơ bản trong việc tối ưu hóa hiệu suất ứng dụng cho bất kỳ nhà phát triển nào làm việc với cơ sở dữ liệu quan hệ.

Hiểu về truy vấn N+1

Hãy tưởng tượng bạn đang lấy danh sách 100 người dùng, và cho mỗi người dùng, bạn lại lấy thông tin hồ sơ liên quan. Nếu bạn thực hiện điều này mà không tối ưu hóa, bạn sẽ có một truy vấn để lấy 100 người dùng và sau đó là 100 truy vấn riêng biệt để lấy hồ sơ của mỗi người. Tổng cộng sẽ là 101 truy vấn cho điều mà thường có thể thực hiện trong hai truy vấn. Mô hình này không thể mở rộng tốt; khi số lượng bản ghi cha tăng lên, số lượng truy vấn và sự suy giảm hiệu suất cũng tăng theo.

Phát hiện truy vấn N+1

Xác định các truy vấn N+1 là bước đầu tiên để giải quyết chúng.

• Nhật ký truy vấn: Hầu hết các hệ thống cơ sở dữ liệu cung cấp nhật ký cho thấy các truy vấn đã được thực hiện. Xem xét các nhật ký này có thể tiết lộ các mẫu truy vấn lặp lại.
• Công cụ cấp ứng dụng: Các framework thường có các công cụ gỡ lỗi. Ví dụ, Laravel Debugbar là một công cụ tuyệt vời để hình dung tất cả các truy vấn được thực hiện trong một yêu cầu, làm nổi bật các vấn đề N+1 tiềm năng.
• Bộ phân tích cơ sở dữ liệu: Các công cụ như EXPLAIN (PostgreSQL, MySQL) hoặc các bộ phân tích cơ sở dữ liệu chuyên dụng có thể giúp phân tích kế hoạch thực thi truy vấn và xác định các hoạt động tốn kém.

Tải trước dữ liệu liên quan

Tải trước là kỹ thuật chính để chống lại các truy vấn N+1. Thay vì truy vấn dữ liệu liên quan riêng biệt cho mỗi bản ghi, tải trước sẽ lấy tất cả dữ liệu liên quan cần thiết trong một hoặc một số truy vấn bổ sung.

Ví dụ trong Laravel/PHP:

Xem xét một mô hình User có nhiều mô hình Order.
Không tải trước:

$users = User::all(); // Truy vấn 1: SELECT * FROM users
foreach ($users as $user) {
    echo $user->orders->count(); // Truy vấn N: SELECT * FROM orders WHERE user_id = ?
}

Điều này sẽ thực hiện 1 (cho người dùng) + N (cho các đơn hàng của mỗi người dùng) truy vấn.

Với tải trước:

$users = User::with('orders')->get(); // Truy vấn 1: SELECT * FROM users; Truy vấn 2: SELECT * FROM orders WHERE user_id IN (...)
foreach ($users as $user) {
    echo $user->orders->count(); // Không có truy vấn bổ sung
}

Ở đây, with('orders') chỉ định cho Laravel tải trước tất cả các đơn hàng liên quan cho những người dùng đã lấy trong một truy vấn riêng, sử dụng một câu lệnh IN. Điều này giảm thiểu truy vấn N+1 xuống chỉ còn hai truy vấn.

Bạn có thể tải trước nhiều mối quan hệ:

$users = User::with('orders', 'profile')->get();

Mối quan hệ lồng nhau cũng có thể được tải trước:

$users = User::with('orders.products')->get();

Tải trước lười

Đôi khi, bạn có thể đã có một tập hợp các mô hình, và sau đó quyết định tải một mối quan hệ cho chúng. Đây là lúc tải trước lười trở nên hữu ích bằng cách sử dụng phương thức load().

Ví dụ trong Laravel/PHP:

$users = User::where('active', true)->get(); // Người dùng đã được tải
// Sau đó, nếu bạn cần các đơn hàng của họ:
$users->load('orders'); // Bây giờ, tất cả đơn hàng cho tập hợp người dùng này được lấy trong một truy vấn duy nhất.

Điều này tránh việc lặp lại qua tập hợp và tải mỗi mối quan hệ riêng lẻ, điều này vẫn sẽ dẫn đến N+1.

Thao tác theo lô

Mặc dù không hoàn toàn là tải trước, thao tác theo lô có thể giải quyết các vấn đề hiệu suất tương tự khi thực hiện các cập nhật hoặc các hành động cơ sở dữ liệu khác không phải là truy vấn. Thay vì lặp qua các bản ghi và thực hiện một UPDATE cho mỗi bản ghi, hãy sử dụng một UPDATE duy nhất với một câu lệnh WHERE IN.

Ví dụ:
Thay vì:

foreach ($ids as $id) {
    DB::table('items')->where('id', $id)->update(['status' => 'processed']);
}

Sử dụng:

DB::table('items')->whereIn('id', $ids)->update(['status' => 'processed']);

Điều này sẽ là một truy vấn duy nhất bất kể số lượng ID.

Tối ưu hóa tổng hợp

Đối với các hàm tổng hợp phổ biến trên các mô hình liên quan, như đếm các mục liên quan, các phương pháp tải trước cụ thể có thể tối ưu hóa hơn nữa các truy vấn.

Ví dụ trong Laravel/PHP:
Để đếm đơn hàng cho người dùng:

$users = User::withCount('orders')->get();
foreach ($users as $user) {
    echo $user->orders_count; // Truy cập vào số lượng đã được tính toán trước
}

Điều này thêm một thuộc tính _count vào mỗi mô hình người dùng với số lượng đơn hàng liên quan của họ, thực hiện điều này trong một truy vấn con hoặc join duy nhất, tránh các truy vấn riêng biệt cho từng tập hợp đơn hàng của người dùng.

Mẹo và thủ thuật

• Luôn xem xét tải trước: Đối với bất kỳ dữ liệu nào sẽ được truy cập qua một tập hợp các mô hình, hãy mặc định sử dụng tải trước.
• Cân bằng hiệu suất và bộ nhớ: Tải trước quá nhiều mối quan hệ hoặc quá nhiều dữ liệu cho một tập hợp kết quả rất lớn có thể tiêu tốn bộ nhớ quá mức. Phân tích và lựa chọn một cách khôn ngoan. Đôi khi, một truy vấn đơn giản với ít dữ liệu được tải là hiệu quả hơn so với một truy vấn nặng nề với quá nhiều tải cho các trường hợp sử dụng cụ thể.
• Sử dụng tải trước có điều kiện: Trong Laravel, whenLoaded() có thể ngăn việc tải các mối quan hệ không được tải trước, hữu ích cho các tài nguyên API để tránh các vấn đề N+1 khi các mối quan hệ có thể hoặc không thể hiện diện.
• Phân tích cơ sở dữ liệu là chìa khóa: Thường xuyên phân tích các tương tác cơ sở dữ liệu của ứng dụng trong môi trường phát triển và staging. Các công cụ như Laravel Debugbar hoặc các bộ phân tích cơ sở dữ liệu cụ thể là vô giá.
• Lưu trữ dữ liệu tĩnh: Đối với dữ liệu hiếm khi thay đổi nhưng thường xuyên được truy cập (ví dụ: cài đặt cấu hình, bảng tra cứu), hãy xem xét việc lưu trữ kết quả để tránh các truy vấn đến cơ sở dữ liệu hoàn toàn.

Kết luận

Truy vấn N+1 là một gánh nặng hiệu suất đáng kể trong nhiều ứng dụng. Tải trước dữ liệu liên quan, sử dụng các kỹ thuật như with() và load() trong các framework PHP, là cách hiệu quả nhất để giảm thiểu điều này. Thường xuyên phân tích các truy vấn cơ sở dữ liệu và hiểu các mẫu truy cập dữ liệu của ứng dụng sẽ giúp bạn xác định và giải quyết các vấn đề N+1, dẫn đến việc sử dụng cơ sở dữ liệu hiệu quả hơn và thời gian phản hồi ứng dụng nhanh hơn.