Loại bỏ công việc lặp lại và tối ưu subquery — Ví dụ thực hành

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

Trong bài trước bạn đã xem xét viết lại IN / EXISTS và subquery tương quan.

Bài này xây dựng trên đó với các bản viết lại tránh tính cùng một tổng hợp hai lần (gấp các tổng hợp lặp thành một kết quả trung gian duy nhất) và vật chất hóa bảng dẫn xuất (xây subquery trong mệnh đề FROM một lần và giữ tạm) — được thực hành qua các ví dụ cụ thể.

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

① Dùng PRAGMA table_info(perf_sales); và PRAGMA table_info(employee); để kiểm tra đị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. perf_sales có 50.000 dòng, nên luôn gắn LIMIT khi xem trước.

Đừng viết cùng tổng hợp hai lần — gấp phần lặp vào một kết quả trung gian

Khi bạn muốn hỏi "tổng doanh số của từng khu vực có cao hơn trung bình tổng giữa các khu vực không?", việc viết tổng và trung bình như hai subquery riêng làm perf_sales bị quét hai lần.

Nếu bạn gấp cùng tổng hợp vào một CTE và tính nó một lần, sau đó tái sử dụng kết quả, bạn chỉ quét một lần.

Mẫu viết lại là: nhận ra trường hợp "cùng tổng hợp GROUP BY xuất hiện cả trong truy vấn chính và trong subquery", nâng tổng hợp đó vào một CTE duy nhất, và để truy vấn chính tham chiếu CTE thay vào đó.

Kết quả giống hệt, và lần quét lặp biến mất khỏi kế hoạch.

-- Trước: tổng và "trung bình giữa các khu vực" được tổng hợp riêng (perf_sales quét hai lần)
SELECT region, SUM(amount) AS total
FROM perf_sales
GROUP BY region
HAVING SUM(amount) > (
  SELECT AVG(t) FROM (
    SELECT SUM(amount) AS t FROM perf_sales GROUP BY region
  )
);

-- Sau: tổng hợp theo khu vực một lần trong CTE
WITH region_total AS (
  SELECT region, SUM(amount) AS total
  FROM perf_sales
  GROUP BY region
)
SELECT region, total
FROM region_total
WHERE total > (SELECT AVG(total) FROM region_total)
ORDER BY region;

Chạy câu hỏi "khu vực nào có tổng doanh số trên trung bình tổng giữa các khu vực?" theo hai dạng — phiên bản tổng hợp lặp và phiên bản CTE — và so sánh kế hoạch.

① Thêm EXPLAIN QUERY PLAN ở đầu truy vấn và hiển thị kế hoạch cho phiên bản viết tổng hợp GROUP BY region cả trong truy vấn chính và bên trong subquery HAVING.

② Gấp cùng tổng hợp khu vực vào một CTE duy nhất, lấy trung bình từ CTE đó với AVG, và sắp xếp các khu vực trên nó với ORDER BY region. Hiển thị kế hoạch cho phiên bản này với EXPLAIN QUERY PLAN.

③ Xác nhận rằng SCAN perf_sales xuất hiện nhiều lần ở phiên bản trước và giảm xuống còn một lần ở phiên bản sau.

Sụp đổ subquery tương quan thành một tổng hợp duy nhất

Khi một điều kiện như "tổng doanh số của từng khu vực có trên tổng tối đa theo từng nhân viên trong khu vực đó không?" được tính như subquery tương quan theo từng dòng khu vực bên ngoài, truy vấn bên trong được đánh giá lại một lần cho mỗi khu vực.

Điều này cũng có thể được gấp thành một tổng hợp một lần theo khu vực, sau đó join trở lại.

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

Việc đánh giá lại theo từng khu vực được thay bằng một tổng hợp duy nhất cộng với một join.

-- Trước: tương quan theo từng khu vực để tìm "tổng tối đa theo từng nhân viên"
SELECT region,
  (SELECT MAX(s) FROM (
     SELECT SUM(amount) AS s FROM perf_sales p2
     WHERE p2.region = p1.region GROUP BY emp_id
  )) AS max_emp_total
FROM perf_sales p1
GROUP BY region;

-- Sau: tổng hợp một lần theo (region, emp_id), sau đó lấy MAX theo khu vực
WITH emp_region AS (
  SELECT region, emp_id, SUM(amount) AS s
  FROM perf_sales
  GROUP BY region, emp_id
)
SELECT region, MAX(s) AS max_emp_total
FROM emp_region
GROUP BY region
ORDER BY region;

Chạy "tổng doanh số tối đa theo từng nhân viên trong mỗi khu vực" theo hai dạng — phiên bản tương quan và phiên bản tổng hợp một lần — và so sánh kế hoạch.

① Thêm EXPLAIN QUERY PLAN ở đầu truy vấn và hiển thị kế hoạch cho phiên bản dùng subquery tương quan để tính "tổng tối đa theo từng nhân viên trong khu vực này" theo từng dòng khu vực bên ngoài.

② Xây CTE thực hiện GROUP BY trên `(region, emp_id)` đúng một lần, sau đó GROUP BY region trên CTE đó để lấy max. Hiển thị kế hoạch cho phiên bản này với EXPLAIN QUERY PLAN.

③ Xác nhận rằng từ CORRELATED trong kế hoạch tương quan biến mất ở kế hoạch viết lại.

Vật chất hóa bảng dẫn xuất — xây subquery FROM-clause một lần

Một bảng dẫn xuất — subquery viết trong mệnh đề FROM — được tham chiếu ở hai chỗ đôi khi có thể được bộ tối ưu tính lại hai lần.

Nếu bạn xây nó một lần và giữ tạm (vật chất hóa nó), các tham chiếu không xây lại nó mỗi lần.

Trong SQLite, tham chiếu CTE nhiều lần làm EXPLAIN QUERY PLAN hiển thị dòng MATERIALIZE — xác nhận CTE được vật chất hóa đúng một lần.

Trong bài thực hành cuối của bài này bạn sẽ lấy một bảng dẫn xuất nặng tổng hợp perf_sales theo emp_id và được tham chiếu hai lần, biến nó thành CTE được vật chất hóa một lần, và xác nhận MATERIALIZE xuất hiện trong kế hoạch và lần quét chỉ xảy ra một lần.

-- Trước: cùng bảng dẫn xuất được tham chiếu trong hai mệnh đề FROM
SELECT a.emp_id, a.tot, b.avg_tot
FROM (SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id) a
CROSS JOIN (
  SELECT AVG(tot) AS avg_tot FROM (
    SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id
  )
) b
ORDER BY a.emp_id;

-- Sau: bảng dẫn xuất như CTE được tham chiếu hai lần (vật chất hóa một lần)
WITH emp_total AS (
  SELECT emp_id, SUM(amount) AS tot
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.tot, (SELECT AVG(tot) FROM emp_total) AS avg_tot
FROM emp_total e
ORDER BY e.emp_id;

Chạy "tổng doanh số của từng nhân viên cùng với trung bình tổng thể giữa các nhân viên" theo hai dạng — phiên bản tham chiếu bảng dẫn xuất hai lần và phiên bản CTE — và so sánh kế hoạch. Đây là bài thực hành cuối của bài.

① Thêm EXPLAIN QUERY PLAN ở đầu truy vấn và hiển thị kế hoạch cho phiên bản tham chiếu bảng dẫn xuất tổng hợp emp_id ở hai chỗ trong mệnh đề FROM.

② Gấp cùng tổng hợp vào một CTE duy nhất, lấy trung bình từ cùng CTE qua (SELECT AVG(tot) FROM cùng_CTE), và sắp xếp theo emp_id. Hiển thị kế hoạch cho phiên bản này với EXPLAIN QUERY PLAN.

③ Xác nhận rằng MATERIALIZE xuất hiện trong kế hoạch viết lại và SCAN perf_sales giảm xuống còn một lần.