Bài 36: Chi phí truy vấn và lỗi N+1

Trang lớp học cần hiện tên và số lần làm bài của từng học viên. Người viết trang lấy danh sách trước, rồi hỏi điểm riêng cho từng bạn. Với một lớp đông, trang phải chờ nhiều lượt trao đổi dù mỗi câu hỏi rất ngắn. Cô Mai muốn biết có thể lấy đủ thông tin mà bớt những lượt hỏi lặp lại hay không.

Đếm lượt hỏi trước khi sửa câu truy vấn

N+1 là cách gọi một truy vấn lấy danh sách có N người, rồi gọi thêm N truy vấn dữ liệu liên quan. Chữ N chỉ số người trong danh sách, không phải số lần chấm. Khi danh sách dài hơn, số truy vấn tăng theo dù giao diện vẫn chỉ là một bảng.

Bạn hãy đọc danh sách học viên mà trang nhận ở lượt đầu:

SELECT id, name
FROM students
ORDER BY id;

Kết quả có sáu người. Nếu chương trình hỏi số điểm riêng cho mỗi người, nó sẽ gửi thêm sáu lượt nữa. Tổng cộng là bảy lượt, không phải sáu. Lan chưa có điểm vẫn cần một lượt hỏi mới biết kết quả bằng không.

Trong một ứng dụng có cơ sở dữ liệu ở máy khác, mỗi lượt có thể phải chờ mạng và kết nối. Những khoảng chờ nhỏ cộng lại có thể đáng kể. Tuy vậy, số lượt nhiều chưa đủ nói trang chậm ở đâu. Bạn vẫn cần đo thời gian để tìm phần đang chiếm nhiều nhất.

Phòng học chạy SQLite trong môi trường cô lập, không tái hiện đường mạng của một website thật. Một lượt gọi thư viện tại cùng máy khác một lượt qua mạng tới máy chủ. Vì vậy, bài này luyện cách lấy đúng dữ liệu theo nhóm. Nó không chứng minh N+1 luôn gây chậm trong mọi kiến trúc.

Lấy số lần chấm của cả nhóm trong một kết quả

Thay vì lặp qua từng người ở chương trình, bạn có thể yêu cầu cơ sở dữ liệu tính cho cả danh sách. Lấy theo tập (set-based query) nghĩa là mô tả một nhóm dòng cần xử lý trong cùng câu lệnh. Cơ sở dữ liệu tự ghép và đếm các dòng liên quan.

Truy vấn sau trả cả tên và số lần chấm trong một bảng kết quả:

SELECT s.id, s.name, COUNT(sc.id) AS attempts
FROM students AS s
LEFT JOIN scores AS sc ON sc.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

An có 2 lần, còn Lan có 0 lần. COUNT(sc.id) bỏ phần mã chấm NULL trên dòng được giữ cho Lan. Nếu đổi thành COUNT(*), dòng đó cũng bị đếm và Lan nhận 1. Giảm lượt gọi chỉ có ích khi số liệu sau khi gộp vẫn đúng.

Bạn cũng có thể lấy danh sách trước rồi lấy toàn bộ điểm của nhóm mã trong một lượt nữa. Cách này thường được gọi là lấy theo lô (batch loading). Nó có thể cần hai truy vấn thay vì một nhưng vẫn tránh hỏi riêng từng người. Việc chọn một hay hai truy vấn phụ thuộc dữ liệu mà trang cần.

Giả sử lượt đầu chọn An, Bình và Chi. Lượt thứ hai lấy điểm của cả nhóm mã bằng một truy vấn:

SELECT student_id, id, score
FROM scores
WHERE student_id IN (1, 2, 3)
ORDER BY student_id, id;

An có hai dòng, Bình có một dòng, còn Chi có hai dòng. Chương trình lấy mã từ lượt đầu, đưa cả nhóm vào IN, rồi ghép điểm theo student_id. Nếu lượt đầu có Lan, lượt lấy điểm không trả dòng nào cho bạn ấy. Chương trình phải hiểu trường hợp đó là 0 lần chấm.

Chọn trang người trước khi ghép điểm

Trang chỉ cần vài người thì nên lấy đúng nhóm người đó. Phân trang (pagination) là chia danh sách thành những phần có giới hạn. Khi danh sách nói về học viên, giới hạn phải áp dụng cho học viên. Một người có nhiều lần chấm không được chiếm nhiều chỗ của trang.

Cách viết dưới đây giới hạn các dòng sau khi ghép chi tiết:

SELECT s.id, s.name, sc.score
FROM students AS s
LEFT JOIN scores AS sc ON sc.student_id = s.id
ORDER BY s.id, sc.id
LIMIT 3;

An chiếm hai dòng vì có hai điểm. Dòng còn lại là Bình, nên trang không có người thứ ba là Chi. Câu lệnh không sai cú pháp. Nó trả lời câu hỏi ba dòng chi tiết, trong khi giao diện muốn ba học viên.

Hãy chọn người trước, rồi mới lấy những lần chấm liên quan:

WITH page AS (
  SELECT id, name
  FROM students
  ORDER BY id
  LIMIT 3
)
SELECT p.id, p.name, sc.score
FROM page AS p
LEFT JOIN scores AS sc ON sc.student_id = p.id
ORDER BY p.id, sc.id;

Kết quả có năm dòng điểm nhưng chỉ thuộc ba người đã chọn. Đây là điều mong đợi nếu trang cần chi tiết từng lần chấm. Bạn phải phân biệt số người của trang với số dòng chi tiết trả về. Khi giao diện chỉ cần số lần, hãy gom tiếp để mỗi người còn một dòng.

Thứ tự theo mã ở bước chọn trang giúp xác định nhóm người được lấy. Thứ tự cuối cùng sắp các dòng mà trang sẽ đọc. Bạn vẫn cần cả hai nếu muốn kết quả có thứ tự rõ ràng. Một thứ tự trong bước trung gian không phải lời hứa cho toàn bộ truy vấn.

Trả đúng cột và đúng mức chi tiết

Gộp N+1 thành một truy vấn tải mọi bảng chưa chắc giúp trang nhẹ hơn. Trang cần số lần mà nhận từng điểm sẽ phải xử lý nhiều dòng dư. Trang cần tên mà nhận cả số dư cũng nhận thêm thông tin không dùng. Hãy bắt đầu từ những gì thực sự xuất hiện trên màn hình.

Truy vấn sau chọn ba người tiếp theo (từ người thứ tư) rồi chỉ tính số lần cho nhóm đó:

WITH page AS (
  SELECT id, name
  FROM students
  ORDER BY id
  LIMIT 3 OFFSET 3
)
SELECT p.id, p.name, COUNT(sc.id) AS attempts
FROM page AS p
LEFT JOIN scores AS sc ON sc.student_id = p.id
GROUP BY p.id, p.name
ORDER BY p.id;

Dung và Huy đều có 1 lần, Lan có 0. Tập người đã được giới hạn trước khi ghép, còn phép đếm giữ kết quả gọn.

Nếu một bảng có rất nhiều dòng, cách tìm trang còn cần được đo riêng. OFFSET lớn có thể khiến cơ sở dữ liệu phải đi qua nhiều dòng trước đó. Ở đây ta dùng nó để nhìn rõ thứ tự các bước. Bài này chưa đặt ra một cách phân trang tối ưu cho mọi quy mô.

So thời gian bằng điều kiện đo có thể lặp lại

Một câu ngắn hơn chưa chắc chạy nhanh hơn. Hai cách cần được so trên cùng bộ dữ liệu và cùng yêu cầu. Trước hết, hãy xác nhận các cột, số người và giá trị không đổi. Sau đó mới đo xem thời gian nào đã giảm.

Trước khi đo, hãy so cách gộp mới với cách cũ hỏi số lần riêng cho từng người. Truy vấn con tương quan dưới đây mô tả kết quả đầy đủ của cách cũ, kể cả Lan:

WITH new_result AS (
  SELECT s.id, s.name, COUNT(sc.id) AS attempts
  FROM students AS s
  LEFT JOIN scores AS sc ON sc.student_id = s.id
  GROUP BY s.id, s.name
), old_result AS (
  SELECT s.id, s.name,
    (SELECT COUNT(*) FROM scores AS sc
     WHERE sc.student_id = s.id) AS attempts
  FROM students AS s
), new_only AS (
  SELECT id, name, attempts
  FROM new_result
  EXCEPT
  SELECT id, name, attempts
  FROM old_result
), old_only AS (
  SELECT id, name, attempts
  FROM old_result
  EXCEPT
  SELECT id, name, attempts
  FROM new_result
)
SELECT 'new_only' AS difference, id, name, attempts
FROM new_only
UNION ALL
SELECT 'old_only', id, name, attempts
FROM old_only
ORDER BY difference, id;

Kết quả rỗng: không có dòng nào chỉ nằm ở một trong hai kết quả. Rỗng ở cả hai chiều mới chứng minh hai tập bằng nhau trên bộ dữ liệu này. Mỗi cách đều trả một dòng cho mỗi mã nên không có dòng trùng cần kiểm riêng.

Truy vấn kiểm chứng không mô phỏng thời gian của nhiều lượt gọi qua mạng. Nó chỉ xác nhận số liệu không đổi trước khi đo ứng dụng thật.

Một lần đo hữu ích nên ghi các phần sau:

• Số truy vấn của một lần mở trang và nhóm dữ liệu mỗi truy vấn lấy.
• Thời gian cơ sở dữ liệu xử lý và thời gian chờ trả lời qua mạng.
• Số dòng, số cột và lượng dữ liệu nhận về.
• Thời gian giao diện vẽ nội dung sau khi có dữ liệu.

Nếu thời gian nằm chủ yếu ở ảnh hay cách vẽ trang, sửa SQL có thể ít tác dụng. Nếu có nhiều lượt hỏi lặp, gộp theo nhóm có thể giảm chờ. Kết luận phải gắn với kết quả đo, không chỉ với số lượng câu lệnh. Sáu học viên mẫu chỉ giúp kiểm tính đúng, chưa thể hiện khả năng phục vụ một trường đông người.

Lỗi thường gặp

Tóm tắt

• N+1 xuất hiện khi một lượt lấy danh sách kéo theo một lượt riêng cho từng đối tượng.
• Lấy theo tập hoặc theo lô giúp tránh vòng lặp hỏi dữ liệu liên quan.
• Giới hạn theo học viên trước, rồi mới ghép hoặc đếm các lần chấm.
• Kiểm tính đúng trước khi so tốc độ, đặc biệt với người chưa có điểm.
• Số lượt, thời gian, lượng dữ liệu và thời gian vẽ trang là các phép đo khác nhau.

Kiểm tra nhanh

Đọc thêm

• SQLite: giải thích vì sao nhiều truy vấn nhỏ có thể phù hợp khi chạy cùng tiến trình.
• SQLite: các cách cơ sở dữ liệu tìm dòng.