多表父子关系场景下SQL子查询咨询：现有正常查询的优化建议

Hey there! Let's walk through some practical optimizations for your multi-table subquery SQL—I’ve tackled similar scenarios before, so these tweaks should help boost performance and make the query cleaner:

1. Merge Subqueries to Reduce Join Overhead #

Right now, you’re doing three separate LEFT OUTER JOINs to fetch aggregated values from three subtables. Each join adds overhead, especially if your datasets are large. Instead, combine all three aggregations into a single subquery to cut down on join operations.

Two solid approaches here:

• Option 1: Use FULL OUTER JOIN in a subquery to combine aggregations
  This lets you calculate all three sums in one place, then join once to your main table:

  SELECT 
      maintable1.col1,
      COALESCE(t_combined.total, 0) AS 'value'
  FROM mtable1 maintable1
  LEFT JOIN (
      SELECT 
          COALESCE(t1.id, t2.id, t3.id) AS id,
          COALESCE(t1.s1tot, 0) + COALESCE(t2.s2tot, 0) - COALESCE(t3.s3tot, 0) AS total
      FROM (SELECT id, SUM(s1*t1) AS s1tot FROM subtable1 WHERE period < 200 GROUP BY id) t1
      FULL OUTER JOIN (SELECT id, SUM(s2*t2) AS s2tot FROM subtable2 WHERE period < 200 GROUP BY id) t2 ON t1.id = t2.id
      FULL OUTER JOIN (SELECT id, SUM(s3*t3) AS s3tot FROM subtable3 WHERE period < 200 GROUP BY id) t3 ON COALESCE(t1.id, t2.id) = t3.id
  ) t_combined ON maintable1.id = t_combined.id
  WHERE --- Your filter conditions
  GROUP BY maintable1.col1
  HAVING COALESCE(SUM(t_combined.total), 0) ... --- Your having conditions
  

• Option 2: Use UNION ALL for simpler aggregation
  This treats each subtotal as a separate row, then sums them up in one go—often faster than multiple joins because it avoids expensive matching operations:

  SELECT 
      maintable1.col1,
      SUM(t_combined.total) AS 'value'
  FROM mtable1 maintable1
  LEFT JOIN (
      SELECT id, SUM(s1*t1) AS total FROM subtable1 WHERE period <200 GROUP BY id
      UNION ALL
      SELECT id, SUM(s2*t2) AS total FROM subtable2 WHERE period <200 GROUP BY id
      UNION ALL
      SELECT id, -SUM(s3*t3) AS total FROM subtable3 WHERE period <200 GROUP BY id
  ) t_combined ON maintable1.id = t_combined.id
  WHERE --- Your filter conditions
  GROUP BY maintable1.col1
  HAVING SUM(t_combined.total) ... --- Your having conditions
  

2. Add Targeted Indexes to Speed Up Filtering & Grouping #

Your subtables are filtering on period < 200 and grouping by id—create composite indexes on each subtable to make these operations blazingly fast:

• For subtable1: CREATE INDEX idx_sub1_period_id ON subtable1 (period, id);
• For subtable2: CREATE INDEX idx_sub2_period_id ON subtable2 (period, id);
• For subtable3: CREATE INDEX idx_sub3_period_id ON subtable3 (period, id);
  These indexes let the database quickly filter rows where period < 200 and then group by id without scanning the entire table. Also, ensure mtable1.id is a primary key or has an index to speed up the final join.

3. Handle NULL Values Explicitly #

Since you’re using LEFT JOIN, some subtotals might return NULL if there’s no matching id in a subtable. If you don’t handle these, your final sum could end up as NULL instead of 0. Use COALESCE() to convert NULLs to 0, either in the subquery or the main calculation (like in the examples above).

4. Push Filtering & Aggregation Down to Subqueries #

Avoid doing calculations in the main query that can be done earlier. For example, if your HAVING clause filters on the total value, you can pre-filter in the subquery to reduce the number of rows joined to the main table. For instance:

-- In the UNION ALL subquery, add a HAVING clause if applicable
SELECT id, SUM(s1*t1) AS total FROM subtable1 WHERE period <200 GROUP BY id HAVING SUM(s1*t1) > 0

This cuts down on the data being passed up to the main query, reducing memory usage and join time.

5. Simplify Group By Logic #

If mtable1.id is a primary key (so each id maps to exactly one col1), you can group by maintable1.id, maintable1.col1 instead of just col1—this helps the database optimize the grouping operation more effectively. Some databases also allow grouping by the primary key alone if col1 is functionally dependent on it.

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