执行多表关联嵌套查询时SQL浏览器崩溃问题咨询

原查询性能瓶颈分析 #

• 内置了4个关联子查询，每查询1条grocery表记录，就要重复扫描4次grocery_nutrient表，数据量大会产生极高的IO开销
• 存在冗余的LEFT JOIN grocery_nutrient + GROUP BY逻辑，额外增加了表关联和分组计算成本
• 缺少必要的索引，所有条件过滤、表关联都走全表扫描，执行效率极低

优化方案 #

1. 新增联合覆盖索引（必做） #

给关联表grocery_nutrient创建联合覆盖索引，查询时直接命中索引不需要回表，大幅降低扫描开销：

CREATE INDEX idx_grocery_nutrient_cov ON grocery_nutrient (nutrient_id, grocery_id, amount, unit);

如果grocery表的grocery_id不是主键/没有索引，也需要给该字段加索引。

2. 改写SQL逻辑，替换子查询为条件聚合 #

去掉所有冗余的子查询，用条件聚合一次性计算所有营养字段，仅需扫描1次grocery_nutrient表，逻辑和原查询完全等价：

SELECT 
  g.*,
  MAX(CASE WHEN gn.nutrient_id = 2 THEN gn.amount || ' ' || gn.unit END) AS calories,
  MAX(CASE WHEN gn.nutrient_id = 6 THEN gn.amount || ' ' || gn.unit END) AS fats,
  MAX(CASE WHEN gn.nutrient_id = 3 THEN gn.amount || ' ' || gn.unit END) AS protein,
  MAX(CASE WHEN gn.nutrient_id = 4 THEN gn.amount || ' ' || gn.unit END) AS carbs
FROM grocery g
LEFT JOIN grocery_nutrient gn ON g.grocery_id = gn.grocery_id
GROUP BY g.grocery_id

3. 额外优化建议 #

如果全量数据体量过大，即便SQL优化完成，也可能因为SQL浏览器内存不足导致崩溃，可以采用分段拉取的方式查询：

-- 按grocery_id范围分段，每次拉取1000条，根据实际情况调整分段大小
SELECT * FROM (
  -- 此处写入上面优化后的查询语句
) t WHERE t.grocery_id BETWEEN 1 AND 1000;

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