sql - ON和WHERE子句的位置和效率表现

Question

我有两个表，一个名为Health_User，另一个名为Diary。他们分别拥有用户的人口统计信息和他们的记录值。我想做的是检索记录的值，但是:

1. 使用 Health_User 中的“is_tester”列( bool 值)排除测试人员(非真实用户)，以及
2. 在日记中排除测量值过高或过低的不合理值。

所以我有几个应该得到相同结果的查询:

# Query 1
SELECT d.user_id, d.id AS diary_id, d.glucose_value, d.unit
    FROM Diary AS d
    JOIN (
        SELECT id
        FROM Health_User
        WHERE is_tester = false
    ) AS u
    ON d.user_id = u.id
    WHERE ((d.glucose_value >= 20 AND d.glucose_value <= 600 AND d.unit = 'mg/dL')
            OR (d.glucose_value >= 20/18.02 AND d.glucose_value <= 600/18.02 AND d.unit = 'mmol/L'));

# Query 2
SELECT d.user_id, d.id AS diary_id, d.glucose_value, d.unit
    FROM Diary AS d
    JOIN Health_User AS u
    ON d.user_id = u.id
    WHERE u.is_tester = false
        AND ((d.glucose_value >= 20 AND d.glucose_value <= 600 AND d.unit = 'mg/dL')
              OR (d.glucose_value >= 20/18.02 AND d.glucose_value <= 600/18.02 AND d.unit = 'mmol/L'));

# Query 3
SELECT d.user_id, d.id AS diary_id, d.glucose_value, d.unit
    FROM Health_User AS u
    JOIN (
        SELECT id, user_id, glucose_value, unit
        FROM Diary
        WHERE ((glucose_value >= 20 AND glucose_value <= 600 AND unit = 'mg/dL')
                OR (glucose_value >= 20/18.02 AND glucose_value <= 600/18.02 AND unit = 'mmol/L'))
    ) AS d
    ON d.user_id = u.id
    WHERE u.is_tester = false;

这里我有三个问题:

问题 1:我推测查询 1 会比查询 2 有更好的性能，因为 a) 它只连接一列而不是 Health_User 的整个表，并且 b) 它在之前过滤掉测试人员加入表格。我说得对吗？

问题 2:Diary 的条件限制更为复杂(请参阅查询 1 中的最后一个 WHERE 子句)。在 JOIN 中切换 Diary 并像查询 3 一样在外部创建 Health_User 更好，还是没有区别？

问题三:性能方面有没有更好的方案？

Answer 1

如果数据库按照您的查询建议的顺序执行查询(首先过滤，然后加入，反之亦然)，将会有所不同。

事实上，PostgreSQL 有一个查询优化器，可以重新排列查询以找到最有效的执行顺序，并且您的所有查询都将以相同的执行计划结束，您可以使用 SQL 语句 EXPLAIN 进行验证。

对于inner join，在join之前或者之后过滤都不会影响结果；您也可以将所有条件写入连接条件而不更改结果。优化器知道这一点。

您可以通过创建适当的索引来加快执行速度。某个指标是否有用，要看数据的分布情况。经验法则是选择性条件(过滤掉许多数据)的索引更有用。使用 EXPLAIN 找到最佳索引。