WHERE NOT IN替代方案与SQL慢查询优化咨询

问题背景 #

现有以下SQL查询，执行耗时约2秒，但需在高访问量页面调用，急需优化：

SELECT *
FROM customers_cards
WHERE id NOT IN ( SELECT card_id FROM customers_card_psp)

补充说明：

• customers_card_psp表中card_id不唯一，每条customers_cards数据对应5条customers_card_psp数据，但在子查询中添加DISTINCT后性能无提升。
• 改用LEFT JOIN写法后性能相同：

SELECT cc.*
FROM customers_cards cc
LEFT JOIN customers_card_psp ccp ON ccp.card_id = cc.id
WHERE ccp.id IS null

优化建议 #

1. 优化索引策略 #

• 确保customers_cards.id为主键（主键默认自带索引，若未设置需补建）。
• 给customers_card_psp.card_id建立单独索引：

  CREATE INDEX idx_ccp_card_id ON customers_card_psp(card_id);
  

  该索引能加速子查询或JOIN时的匹配操作，减少全表扫描开销，即便DISTINCT无效，索引仍能显著提升关联效率。
• 若customers_card_psp.id非主键，建议为其添加索引，加速ccp.id IS NULL的过滤判断。

2. 改用NOT EXISTS写法 #

多数场景下，NOT EXISTS的执行效率优于NOT IN和LEFT JOIN，数据库可通过半连接（semi-join）优化逻辑，避免笛卡尔积生成与重复数据处理：

SELECT cc.*
FROM customers_cards cc
WHERE NOT EXISTS (
    SELECT 1 FROM customers_card_psp ccp
    WHERE ccp.card_id = cc.id
);

3. 避免SELECT *，使用覆盖索引 #

若业务无需customers_cards全字段，仅查询所需字段可减少数据传输与内存消耗：

SELECT cc.id, cc.card_number, cc.customer_id -- 替换为实际业务字段
FROM customers_cards cc
WHERE NOT EXISTS (
    SELECT 1 FROM customers_card_psp ccp
    WHERE ccp.card_id = cc.id
);

同时可针对查询字段建立覆盖索引，让数据库直接从索引获取数据，无需回表：

CREATE INDEX idx_cc_cover ON customers_cards(id, card_number, customer_id);

4. 引入缓存机制 #

针对高访问量场景，若查询结果无需实时更新，可通过Redis等缓存组件缓存结果，设置合理过期时间；若需实时性，可考虑应用层缓存结合数据库变更通知（如触发器、binlog监听）更新缓存，避免重复查询数据库。

5. 大表分区优化 #

若两张表数据量达百万级以上，可按业务维度（如时间、用户ID范围）对表进行分区，缩小每次查询扫描的数据范围，降低IO开销。

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