我有一種情況，我需要顯示已發布學習材料或測驗的主題串列。下面的查詢有效，但需要大約 8 秒。有沒有更好的方法可以優化此查詢？謝謝你。
SELECT sj.*, sj.id AS id FROM subjects sj
WHERE (
(SELECT COUNT(lmc.id) FROM learning_materials_codes lmc
INNER JOIN students s ON lmc.student_id = s.id
INNER JOIN learning_materials lm ON lmc.learning_material_id = lm.id
WHERE lmc.student_id = 1 AND sj.id = ANY(lm.subject_ids)) > 0
OR
(SELECT COUNT(tc.id) FROM test_codes tc
INNER JOIN students s ON tc.student_id = s.id
INNER JOIN tests t ON tc.test_id = t.id
WHERE tc.student_id = 1 AND t.subject_id = sj.id) > 0
)
AND sj.school_id = 1
Table structure below
subjects
~~~~~~~~~~~
id
name
school_id
...
students
~~~~~~~~~~
id
f_name
l_name
school_id
...
tests
~~~~~~~~~
id
title
subject_id
...
test_codes
~~~~~~~~~~
test_id
student_id
code
...
learning_materials
~~~~~~~~~~~~~~~~~
id
title
subject_ids []
...
learning_material_codes
~~~~~~~~~~~~~~~~~~~~~~~~
learning_material_id
student_id
code
...
注：每次學習材料或測驗發布，讓學生產生的訪問代碼和資料保存在learning_material_codes或test_codes表
uj5u.com熱心網友回復：
我會將查詢重寫為
SELECT sj.*
FROM subjects sj
WHERE EXISTS (SELECT 1
FROM learning_materials_codes lmc
INNER JOIN students s ON lmc.student_id = s.id
INNER JOIN learning_materials lm ON lmc.learning_material_id = lm.id
WHERE lmc.student_id = 1
AND sj.id = ANY(lm.subject_ids)
AND lmc.id IS NOT NULL)
AND sj.school_id = 1
UNION
SELECT sj.*
FROM subjects sj
WHERE EXISTS (SELECT 1
FROM test_codes tc
INNER JOIN students s ON tc.student_id = s.id
INNER JOIN tests t ON tc.test_id = t.id
WHERE tc.student_id = 1
AND t.subject_id = sj.id
AND tc.id IS NOT NULL)
AND sj.school_id = 1;
這樣，PostgreSQL 可以使用半連接并且可能更快。如果您不介意重復的結果行，請使用UNION ALL代替以UNION獲得更好的性能。
使用適當的索引可以獲得更高的性能，但需要EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)輸出來評估它。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/391459.html
標籤：sql 数据库 PostgreSQL的
上一篇：SQL查詢日期范圍內表中的單個值