Viết lại IN, EXISTS và subquery tương quan

Dữ liệu dùng trong bài này — perf_sales và employee

Hai truy vấn cho cùng kết quả vẫn có thể sinh ra kế hoạch truy vấn khác nhau (các bước cơ sở dữ liệu chọn để đọc bảng nào theo thứ tự nào, và dùng chỉ mục nào).

Trong bài này bạn sẽ học khi nào dùng IN so với EXISTS, cách loại bỏ một subquery tương quan (subquery được đánh giá lại cho mỗi dòng bên ngoài), và cách viết lại "chỉ giữ các dòng có (hoặc không có) khớp" — so sánh các kế hoạch truy vấn với EXPLAIN QUERY PLAN xuyên suốt.

Trước khi vào bài thực hành, hãy kiểm tra định nghĩa cột và dữ liệu mẫu cho hai bảng dùng trong bài — perf_sales và employee.

① Dùng PRAGMA table_info(perf_sales); và PRAGMA table_info(employee); để xác nhận định nghĩa cột của hai bảng.

② Dùng SELECT * FROM perf_sales LIMIT 5; và SELECT * FROM employee LIMIT 5; để xem trước 5 dòng đầu của mỗi bảng. perf_sales có 50.000 dòng, nên luôn thêm LIMIT khi xem trước.

IN và EXISTS — hai cách viết cùng một phép kiểm tra

Có hai cách hỏi "có một dòng khớp trong bảng khác không?".

WHERE col IN (SELECT ...) kiểm tra giá trị có nằm trong tập hợp mà truy vấn bên trong trả về hay không, còn WHERE EXISTS (SELECT 1 FROM ... WHERE ...) kiểm tra truy vấn bên trong có trả về ít nhất một dòng hay không.

Trong nhiều trường hợp cả hai cho cùng kết quả, và cơ sở dữ liệu có thể xử lý cả hai như "chỉ giữ các dòng có khớp".

Thêm EXPLAIN QUERY PLAN query; ở đầu sẽ cho thấy các bước cơ sở dữ liệu chọn, từng dòng một.

SCAN perf_sales nghĩa là đi qua toàn bộ dòng, SEARCH ... USING INDEX nghĩa là đi thẳng đến các dòng cần qua chỉ mục, và CORRELATED SCALAR SUBQUERY là subquery được đánh giá lại cho mỗi dòng bên ngoài.

-- Ví dụ: đếm doanh số xử lý bởi nhân viên trong dept_id=2, hai cách

-- Dạng IN
SELECT COUNT(*) FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 2);

-- Dạng EXISTS (tương quan)
SELECT COUNT(*) FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 2
);

-- Thêm EXPLAIN QUERY PLAN ở đầu để xem kế hoạch
-- EXPLAIN QUERY PLAN SELECT COUNT(*) FROM perf_sales WHERE ...;

Hãy cụ thể hơn. IN và EXISTS hoạt động thế nào tùy theo kích thước bảng bên ngoài và kích thước subquery bên trong và liệu có chỉ mục hay không?

Hai trường hợp dưới — và dạng nào nhanh hơn trong mỗi trường hợp — được tóm tắt trong sơ đồ tiếp theo.

-- Trường hợp A: bên ngoài perf_sales (50.000 dòng), bên trong dept_id=1 nhân viên (~5 dòng, nhỏ và cố định)

-- Dạng IN
SELECT * FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 1);

-- Dạng EXISTS
SELECT * FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 1
);

-- Trường hợp B: bên ngoài employee (30 dòng), bên trong perf_sales (50.000 dòng, có chỉ mục trên emp_id)

-- Dạng IN
SELECT * FROM employee e
WHERE e.emp_id IN (SELECT emp_id FROM perf_sales);

-- Dạng EXISTS
SELECT * FROM employee e
WHERE EXISTS (
  SELECT 1 FROM perf_sales p
  WHERE p.emp_id = e.emp_id
);

Mẹo — cơ sở dữ liệu hiện đại gần như không có chênh lệch tốc độ thực tế

Chúng ta đã phân loại thành "IN thường thắng / EXISTS thường thắng", nhưng đó là xu hướng bắt nguồn từ các bộ tối ưu cũ.

Trong các cơ sở dữ liệu hiện đại như SQLite, PostgreSQL và MySQL 8.0+, cả IN (SELECT ...) và EXISTS tương quan đều được viết lại thành cùng kế hoạch "chỉ giữ các dòng có khớp", và kế hoạch thực tế cùng tốc độ thực tế kết thúc gần như giống nhau.

Nói cách khác, bạn có thể chọn cái nào dễ đọc hơn.

Khi chênh lệch kế hoạch hoặc chênh lệch tốc độ thực sự quan trọng, quy tắc là đo đạc — EXPLAIN QUERY PLAN và thời gian thực.

Viết "số doanh số perf_sales xử lý bởi nhân viên có dept_id là 2" theo hai dạng — IN và EXISTS — và so sánh từng kế hoạch truy vấn. (Chạy đúng sẽ làm xuất hiện phần giải thích.)

① Thêm EXPLAIN QUERY PLAN ở đầu để hiển thị kế hoạch cho truy vấn đếm theo dạng emp_id IN (SELECT ...).

② Sau đó hiển thị kế hoạch cho cùng truy vấn đếm viết theo dạng EXISTS (tương quan với employee) — cũng với EXPLAIN QUERY PLAN.

③ So sánh hai kế hoạch, để ý các từ SCAN / SEARCH / USING INDEX và tương tự.

Viết lại subquery tương quan thành JOIN + tổng hợp

Một subquery tương quan trong danh sách SELECT (được đánh giá lại cho mỗi dòng bên ngoài) bị gọi ngày càng nhiều khi số dòng tăng lên.

Trong EXPLAIN QUERY PLAN nó xuất hiện như dòng CORRELATED SCALAR SUBQUERY.

Trong nhiều trường hợp nó có thể được viết lại thành một phép tổng hợp duy nhất chuẩn bị trước và được join vào — đánh giá lại theo từng dòng biến mất.

Mẫu viết lại biến "cho mỗi dòng bên ngoài, gọi (SELECT ... WHERE child.key = parent.key)" thành "xây bảng tổng hợp một lần với GROUP BY key, sau đó JOIN nó với cha".

Kết quả như nhau và tương quan biến mất khỏi kế hoạch.

-- Trước: tổng doanh số theo từng nhân viên qua subquery tương quan
SELECT e.emp_id, e.name,
  (SELECT SUM(p.amount) FROM perf_sales p
   WHERE p.emp_id = e.emp_id) AS total
FROM employee e
ORDER BY e.emp_id;

-- Sau: xây tổng hợp một lần và JOIN
WITH sales_sum AS (
  SELECT emp_id, SUM(amount) AS total
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.name, s.total
FROM employee e
LEFT JOIN sales_sum s ON s.emp_id = e.emp_id
ORDER BY e.emp_id;

Chạy "danh sách tổng perf_sales của từng nhân viên" theo hai cách — như subquery tương quan và như bản viết lại — và xem kế hoạch thay đổi thế nào.

① Thêm EXPLAIN QUERY PLAN ở đầu để hiển thị kế hoạch cho phiên bản đặt (SELECT SUM(amount) FROM perf_sales WHERE cùng emp_id) trong danh sách SELECT như subquery tương quan.

② Cuộn cùng tổng hợp vào một CTE thực hiện GROUP BY trên emp_id một lần, LEFT JOIN nó với employee, và hiển thị kế hoạch đó với EXPLAIN QUERY PLAN.

③ Xác nhận dòng CORRELATED SCALAR SUBQUERY trong phiên bản tương quan biến mất ở phiên bản đã viết lại.

Bẫy NULL của NOT IN — tránh nó với NOT EXISTS

Khi bạn hỏi "không có dòng khớp" — một anti-join — NOT IN (SELECT ...) có một bẫy.

Nếu chỉ một NULL lọt vào tập hợp bên trong, three-valued logic (TRUE / FALSE / UNKNOWN) làm toàn bộ điều kiện trở thành UNKNOWN, và kết quả trả về là 0 dòng.

Trong khi đó, NOT EXISTS (SELECT 1 ... WHERE ...) chỉ hỏi truy vấn bên trong có sinh ra dòng không, nên NULL không phá nó và anti-join hoạt động đúng.

Khi viết một anti-join, dùng NOT EXISTS — hoặc, nếu bạn phải dùng NOT IN, thêm WHERE col IS NOT NULL bên trong để loại bỏ NULL.

Khóa học này dùng NOT EXISTS như dạng chuẩn.

-- Xây ví dụ với NULL trong tập bên trong, dùng team
-- team(mgr_emp) cố ý bao gồm một NULL
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
-- Bẫy: với NULL có mặt, NOT IN trả về 0 dòng
SELECT COUNT(*) FROM perf_sales
WHERE emp_id NOT IN (SELECT mgr_emp FROM team);

-- Sửa: NOT EXISTS không bị ảnh hưởng bởi NULL, nên anti-join hoạt động
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
SELECT COUNT(*) FROM perf_sales p
WHERE NOT EXISTS (
  SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id
);

Quan sát — qua số đếm dòng — điều gì xảy ra khi tập bên trong chứa NULL, so sánh NOT IN và NOT EXISTS. Đây là bài thực hành cuối của bài. Xây danh sách giá trị nhỏ gọi là team (ba giá trị: 3, 7, NULL) như một CTE, sau đó đếm "perf_sales dòng có emp_id không nằm trong danh sách đó" theo hai cách.

① Xây CTE với 3, 7, NULL dùng WITH team(mgr_emp) AS (VALUES ...), và đếm dòng với emp_id NOT IN (SELECT mgr_emp FROM team).

② Viết cùng phép kiểm tra như NOT EXISTS (SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id) và đếm các dòng.

③ So sánh hai số đếm và xác nhận phiên bản NOT IN bằng 0.