PostgreSQL指定SQL语句的含义、潜在问题及主外键关联合理性分析

SELECT
    *
FROM (
    SELECT
        ua1.given_name,
        (
            SELECT
                count(*)
            FROM
                portfolio_project_collaborator ppc1
                INNER JOIN portfolio_project pp1 ON ppc1.portfolio_project_id = pp1.id
            WHERE
                pp1.user_account_id = ua1.id
                AND ppc1.approved_at IS NOT NULL) + (
                SELECT
                    count(*)
                FROM
                    productized_service_collaborator psc1
                    INNER JOIN productized_service ps1 ON psc1.productized_service_id = ps1.id
                WHERE
                    ps1.user_account_id = ua1.id
                    AND psc1.approved_at IS NOT NULL) total_confirmed_collaborators
    FROM
        user_account ua1
    WHERE (ua1.given_name <-> 'mike') < 1) t1
ORDER BY
    t1.total_confirmed_collaborators DESC
LIMIT 10;

这是PostgreSQL环境下的业务查询语句，核心作用如下：

1. 筛选user_account用户表中名字和关键词mike相似度较高的用户（用到的<->是pg_trgm扩展提供的三元组相似度距离算子，值越小相似度越高，<1代表匹配相似度符合要求的结果）
2. 对每个符合条件的用户统计两个数值的总和，命名为total_confirmed_collaborators：
   • 该用户创建的所有作品集项目（portfolio_project）中，已经通过审核（approved_at不为空）的协作人总次数
   • 该用户创建的所有标准化服务（productized_service）中，已经通过审核的协作人总次数
3. 最终按统计的总协作次数倒序排序，取前10条结果返回。

当前查询涉及的5张表关联逻辑清晰，具体对应关系如下：

• 核心主表user_account主键为id
• portfolio_project表的user_account_id为外键，关联user_account.id，代表该项目的创建者；portfolio_project_collaborator表的portfolio_project_id为外键，关联portfolio_project.id，代表该协作记录所属的项目
• productized_service表的user_account_id为外键，关联user_account.id，代表该服务的创建者；productized_service_collaborator表的productized_service_id为外键，关联productized_service.id，代表该协作记录所属的服务

• 性能损耗严重：两个统计计数用的都是相关子查询，每遍历一条用户记录就要单独执行两次关联计数查询，当用户表、项目/服务表数据量较大时，查询效率会非常低，建议改用LEFT JOIN + GROUP BY聚合的方式改写，减少关联查询次数
• 计数逻辑不符合字段语义：total_confirmed_collaborators字段统计的是协作人次，而非去重后的协作人数：如果同一个人同时在该用户的多个项目/服务中担任协作人，会被多次计数，和字段字面代表的「确认协作总人数」含义不符
• 依赖未显式声明：用到的相似度算子<->依赖PostgreSQL的pg_trgm扩展，如果当前数据库未安装启用该扩展，查询会直接报错
• 冗余语法：外层查询的SELECT *完全冗余，子查询仅返回given_name和total_confirmed_collaborators两个字段，直接指定返回字段可读性更高
• 匹配阈值设置模糊：相似度距离<1的阈值非常宽松，可能返回很多和mike关联度极低的用户名，实际业务使用需要根据场景调整合理阈值

内容的提问来源于stack exchange，提问作者Eric Gideon