优化SQL查询：统计关联特定Receipt的指定catid金额总和

原查询通过IN子筛选目标收据号，但在数据量较大时，子查询的DISTINCT操作和IN的匹配逻辑会导致性能瓶颈。以下是几种优化方案：

1. 使用EXISTS替代IN #

EXISTS采用半连接逻辑，只要找到匹配的收据记录就停止扫描，比IN更适合处理大结果集：

SELECT catid, SUM(amount)
FROM mytable t1
WHERE EXISTS (
    SELECT 1 
    FROM mytable t2 
    WHERE t2.receiptno = t1.receiptno 
      AND t2.catid > 0 
      AND t2.catid < 10
)
AND catid IN (11, 12, 13, 14, 15, 16)
GROUP BY catid

2. 用内连接筛选目标收据 #

先提取符合条件的收据号，再通过内连接关联主表统计，避免IN的性能损耗：

子查询写法 #

SELECT t1.catid, SUM(t1.amount)
FROM mytable t1
INNER JOIN (
    SELECT DISTINCT receiptno 
    FROM mytable 
    WHERE catid BETWEEN 1 AND 9
) t2 ON t1.receiptno = t2.receiptno
WHERE t1.catid IN (11, 12, 13, 14, 15, 16)
GROUP BY t1.catid

CTE写法（支持CTE的数据库：MySQL 8.0+、PostgreSQL等） #

WITH valid_receipts AS (
    SELECT DISTINCT receiptno 
    FROM mytable 
    WHERE catid BETWEEN 1 AND 9
)
SELECT t.catid, SUM(t.amount)
FROM mytable t
JOIN valid_receipts vr ON t.receiptno = vr.receiptno
WHERE t.catid IN (11,12,13,14,15,16)
GROUP BY t.catid

3. 添加核心索引（性能提升关键） #

索引能避免全表扫描，直接定位目标数据：

• 针对收据筛选的索引：

  CREATE INDEX idx_mytable_catid_receiptno ON mytable (catid, receiptno);
  

  快速筛选出catid在1-9之间的收据号，无需扫描全表。
• 针对统计的覆盖索引：

  CREATE INDEX idx_mytable_receiptno_catid_amount ON mytable (receiptno, catid, amount);
  

  主查询可直接从索引获取receiptno、catid、amount数据，无需回表查询，大幅提升统计效率。

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