面试题：MySQL子查询与JOIN性能优化的深度探究

Question

假设存在一组复杂的业务表，`products`表（`product_id`、`category_id`、`product_name`等），`categories`表（`category_id`、`category_name`等），`orders_products`表（`order_id`、`product_id`、`quantity`等），`orders`表（`order_id`、`customer_id`、`order_date`等），`customers`表（`customer_id`、`customer_name`等）。现有一个需求：查询购买过某一特定类别（如电子产品类别，假设category_name为'Electronics'）产品的客户在2023年11月的订单总金额（订单总金额 = 产品数量 * 产品价格，假设产品价格在`products`表中的`price`字段）。请先用子查询实现，然后将其转换为JOIN形式，并从MySQL查询优化器的工作原理角度，详细阐述子查询转换为JOIN后，优化器在执行计划生成过程中的不同策略以及对性能产生的影响。

Answer 1

子查询实现

SELECT 
    SUM(op.quantity * p.price) AS total_amount
FROM 
    orders_products op
JOIN 
    orders o ON op.order_id = o.order_id
JOIN 
    customers c ON o.customer_id = c.customer_id
WHERE 
    o.order_date BETWEEN '2023-11-01' AND '2023-11-30'
  AND c.customer_id IN (
      SELECT 
          c.customer_id
      FROM 
          customers c
      JOIN 
          orders o ON c.customer_id = o.customer_id
      JOIN 
          orders_products op ON o.order_id = op.order_id
      JOIN 
          products p ON op.product_id = p.product_id
      JOIN 
          categories cat ON p.category_id = cat.category_id
      WHERE 
          cat.category_name = 'Electronics'
  );

JOIN形式实现

SELECT 
    SUM(op.quantity * p.price) AS total_amount
FROM 
    orders_products op
JOIN 
    orders o ON op.order_id = o.order_id
JOIN 
    customers c ON o.customer_id = c.customer_id
JOIN 
    products p ON op.product_id = p.product_id
JOIN 
    categories cat ON p.category_id = cat.category_id
WHERE 
    o.order_date BETWEEN '2023-11-01' AND '2023-11-30'
  AND cat.category_name = 'Electronics';

优化器策略及性能影响

1. 子查询：
   • 策略：MySQL查询优化器处理子查询时，通常会先执行子查询，然后将子查询的结果作为外部查询的条件。子查询会生成一个临时表，该临时表可能无法利用索引等优化手段，尤其是在子查询结果集较大时。
   • 性能影响：子查询可能导致多次扫描表，增加I/O开销。例如，在上述子查询中，先找出购买电子产品的客户ID，生成临时表，再在外部查询中使用这个临时表进行关联，若临时表数据量大，将严重影响性能。
2. JOIN：
   • 策略：MySQL优化器在处理JOIN时，会尝试通过分析表之间的关联条件、索引等信息，生成最优的执行计划。它可以基于成本模型，考虑不同JOIN顺序对性能的影响，选择成本最低的执行方案。例如，优化器可能会根据表的大小、索引情况等，决定先关联哪两个表，以减少中间结果集的大小。
   • 性能影响：JOIN通常性能更好，因为它可以一次性处理多个表的关联，避免了子查询中临时表的创建和多次扫描。在上述JOIN查询中，优化器可以直接利用表之间的索引，通过合理的JOIN顺序，减少数据扫描量，提高查询效率。