如何优化MySQL帖子可见性判断表达式以避免不必要表连接？

需要编写高效的MySQL表达式判断帖子对用户是否可见，当前逻辑是先检查用户是否为作者，若不是则检查帖子是否可公开查看。但通过EXPLAIN分析发现，不管v_authors.id IS NOT NULL的结果如何，v_posts子查询都会执行，希望避免这种不必要的连接，而且后续还要添加更多逻辑，要求结构尽量高效。

原SQL代码：

SELECT (
            SELECT (
                CASE WHEN v_authors.id IS NOT NULL THEN 1
                WHEN (
                    SELECT COUNT(*)
                    FROM posts as v_posts
                    WHERE id = example_foreign_table.post_id
                    AND first_public_release <= NOW()
                ) > 0 THEN 1
                ELSE 0 END
            ) as visible
            FROM (SELECT 1) as aux
            # 尝试关联当前用户作为作者
            LEFT OUTER JOIN authors as v_authors
                ON v_authors.type = 'user'
                AND v_authors.foreign_id = 123
        ) as visible
FROM (
    SELECT 1270 as post_id
) as example_foreign_table

核心思路 #

利用MySQL的短路求值特性，让第二个判断条件仅在第一个条件不满足时才执行。原查询的嵌套结构导致优化器无法识别短路逻辑，调整结构后就能触发短路，避免不必要的子查询执行。

优化后的SQL（推荐版本）

SELECT 
    CASE
        # 先检查是否为作者
        WHEN EXISTS(SELECT 1 FROM authors v_authors WHERE v_authors.type = 'user' AND v_authors.foreign_id = 123) THEN 1
        # 仅当不是作者时，检查帖子是否公开
        WHEN EXISTS(SELECT 1 FROM posts v_posts WHERE v_posts.id = example_foreign_table.post_id AND v_posts.first_public_release <= NOW()) THEN 1
        ELSE 0
    END AS visible
FROM (SELECT 1270 AS post_id) AS example_foreign_table

另一种简化写法（用IF函数）

SELECT 
    IF(
        EXISTS(SELECT 1 FROM authors v_authors WHERE v_authors.type = 'user' AND v_authors.foreign_id = 123),
        1,
        EXISTS(SELECT 1 FROM posts v_posts WHERE v_posts.id = example_foreign_table.post_id AND v_posts.first_public_release <= NOW())
    ) AS visible
FROM (SELECT 1270 AS post_id) AS example_foreign_table

优化点说明 #

1. 用EXISTS替代COUNT(*): EXISTS找到第一条匹配记录就会停止查询，比COUNT(*)遍历全表计数高效得多，尤其适合数据量大的表。
2. 简化查询结构: 去掉了不必要的辅助表aux和LEFT JOIN，直接用EXISTS检查用户身份，让优化器更容易识别短路逻辑。
3. 确保短路执行: 调整后的结构中，只要第一个EXISTS返回TRUE，MySQL就会直接返回1，不会执行第二个子查询，用EXPLAIN可以验证这一点。

后续扩展建议 #

如果之后要加更多判断条件，直接在CASE里追加分支就行，保持短路逻辑的结构，比如添加用户权限检查：

SELECT 
    CASE
        WHEN EXISTS(SELECT 1 FROM authors v_authors WHERE v_authors.type = 'user' AND v_authors.foreign_id = 123) THEN 1
        WHEN EXISTS(SELECT 1 FROM posts v_posts WHERE v_posts.id = example_foreign_table.post_id AND v_posts.first_public_release <= NOW()) THEN 1
        WHEN EXISTS(SELECT 1 FROM post_permissions pp WHERE pp.post_id = example_foreign_table.post_id AND pp.user_id = 123) THEN 1
        ELSE 0
    END AS visible
FROM (SELECT 1270 AS post_id) AS example_foreign_table

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