如何优化基于同表查询的MySQL EXISTS/NOT EXISTS语句性能？

嘿，这个场景我太熟悉了！你的原查询逻辑是对的，但两次独立的EXISTS子查询会让数据库对tbl2执行两次扫描（或者至少两次索引查找），当tbl2数据量较大时，这会明显拖慢查询速度。咱们可以把这两个子查询合并成一次对tbl2的扫描，一次性算出两个需要的布尔值，性能会提升不少。

优化方案（适用于PostgreSQL等支持布尔聚合/FILTER的数据库） #

SELECT 
    t1.col1, 
    t1.col2, 
    t1.col3,
    -- 没有符合条件的活跃记录时，can_be_deleted为true
    COALESCE(t2.no_active_items, TRUE) AS can_be_deleted,
    -- 存在重要活跃记录时，have_important_items为true
    COALESCE(t2.has_important_items, FALSE) AS have_important_items
FROM tbl1 t1
LEFT JOIN (
    SELECT 
        -- 检查是否不存在mycategory=10且status>0的记录
        NOT BOOL_OR(status > 0) AS no_active_items,
        -- 检查是否存在mycategory=10、status>0且type_item>0的记录
        BOOL_OR(status > 0 AND type_item > 0) AS have_important_items
    FROM tbl2
    WHERE mycategory = 10
) t2 ON 1=1
WHERE ... -- 保留你原来的WHERE条件
LIMIT 1;

兼容MySQL等不支持BOOL_OR/FILTER的数据库版本 #

如果你的数据库不支持BOOL_OR或者FILTER子句，可以用SUM配合CASE语句来实现：

SELECT 
    t1.col1, 
    t1.col2, 
    t1.col3,
    (t2.total_active = 0) AS can_be_deleted,
    (t2.total_important > 0) AS have_important_items
FROM tbl1 t1
LEFT JOIN (
    SELECT 
        SUM(CASE WHEN status > 0 THEN 1 ELSE 0 END) AS total_active,
        SUM(CASE WHEN status > 0 AND type_item > 0 THEN 1 ELSE 0 END) AS total_important
    FROM tbl2
    WHERE mycategory = 10
) t2 ON 1=1
WHERE ... -- 保留你原来的WHERE条件
LIMIT 1;

关键优化点说明 #

• 减少扫描次数：不管用哪种写法，都只对tbl2做一次扫描，一次性计算出两个统计值，避免了原查询的两次独立子查询开销。
• 索引加持：一定要给tbl2创建合适的索引，比如：

  CREATE INDEX idx_tbl2_mycat_status_type ON tbl2(mycategory, status, type_item);
  

  这个索引能让数据库快速定位到mycategory=10的记录，不用全表扫描，进一步提升查询效率。

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