如何优化包含两个子查询的UPDATE SQL语句？

Hey there! Let's tackle optimizing that UPDATE statement with two subqueries. First off, since your Commissions table structure got cut off, I’ll work with what’s provided plus common related fields you might have (like a user_id or order_id that links to other tables your subqueries are referencing).

Let’s start with a common example of what your original query might look like—this will make the optimization steps concrete:

UPDATE Commissions c
SET network_commission = (
    SELECT commission_rate FROM Networks n WHERE n.id = c.network_id
),
tier_commission = (
    SELECT tier_rate FROM CommissionTiers t WHERE t.user_id = c.user_id AND t.level = c.tier_level
)
WHERE c.status = 'pending';

Top Optimization Strategies #

• Replace correlated subqueries with JOINs
  Correlated subqueries run once per row in the target table, which can tank performance on large datasets. Instead, use direct JOINs to pull all needed values in a single pass:

  UPDATE Commissions c
  LEFT JOIN Networks n ON n.id = c.network_id
  LEFT JOIN CommissionTiers t ON t.user_id = c.user_id AND t.level = c.tier_level
  SET c.network_commission = n.commission_rate,
      c.tier_commission = t.tier_rate
  WHERE c.status = 'pending';
  

  This way, the database only scans Networks and CommissionTiers once, not once per row in Commissions.

• Add targeted indexes
  Make sure columns used in JOIN conditions and WHERE clauses are indexed to speed up lookups:

  • For Networks: Ensure id is indexed (it’s likely the primary key already, which is ideal)
  • For CommissionTiers: Create a composite index on (user_id, level) since you’re filtering on both columns
  • For Commissions: Add an index on status to quickly isolate pending rows, plus indexes on join columns like network_id, user_id, and tier_level

• Trim the number of rows being updated
  If you don’t need to update every row in Commissions, tighten your WHERE clause as much as possible. Adding filters like date ranges (c.created_at >= '2024-01-01') will drastically reduce the number of rows the database processes.

• Use CTEs for readability (and sometimes performance)
  If your logic is complex, Common Table Expressions (CTEs) can make the query easier to follow, and modern optimizers (MySQL 8.0+, PostgreSQL, etc.) will optimize them effectively:

  WITH CommissionCalculations AS (
      SELECT 
          c.id,
          n.commission_rate,
          t.tier_rate
      FROM Commissions c
      LEFT JOIN Networks n ON n.id = c.network_id
      LEFT JOIN CommissionTiers t ON t.user_id = c.user_id AND t.level = c.tier_level
      WHERE c.status = 'pending'
  )
  UPDATE Commissions c
  JOIN CommissionCalculations cc ON c.id = cc.id
  SET c.network_commission = cc.commission_rate,
      c.tier_commission = cc.tier_rate;
  

• Validate with EXPLAIN
  Always run EXPLAIN before and after changes to see how the database executes the query. Watch for:

  • Full table scans (marked as ALL in the type column)
  • High rows values (indicates the database is processing too many rows)
  • Temporary tables or filesorts (these add unnecessary overhead)

If you can share your full original UPDATE statement and complete table structures (including the other tables referenced in the subqueries), I can give you a more tailored solution. But these strategies should cover most common cases!

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