面试题：MySQL关联查询性能瓶颈深度分析与优化

Question

在一个大型电商数据库中，存在`orders`（包含数百万条记录，有`order_id`，`customer_id`，`order_status`等字段），`customers`（包含数十万条记录，有`customer_id`，`customer_type`等字段），`payments`（包含大量支付记录，有`payment_id`，`order_id`，`payment_amount`等字段），`products`（包含数千种商品信息，有`product_id`，`product_name`等字段），`order_items`（记录订单商品明细，有`order_item_id`，`order_id`，`product_id`等字段）这五个表。现在需要查询出不同客户类型下，已支付且订单状态为完成的订单中，每种商品的平均支付金额，同时要考虑性能优化。请深入分析可能出现的性能瓶颈，并给出详细的优化方案及优化后的SQL语句。

Answer 1

性能瓶颈分析

1. 数据量巨大：orders表有数百万条记录，payments表有大量支付记录，在连接操作时可能导致笛卡尔积过大，消耗大量内存和时间。
2. 多表连接：涉及五个表的连接操作，连接条件复杂，容易使查询优化器生成不佳的执行计划。
3. 过滤条件：在大表上应用order_status = '完成'和已支付等过滤条件，如果相关字段没有合适的索引，全表扫描会严重影响性能。

优化方案

1. 索引优化：
   • 在orders表的order_status字段上创建索引，加快订单状态过滤。
   • 在orders表的customer_id字段上创建索引，加速与customers表的连接。
   • 在payments表的order_id字段上创建索引，加速与orders表的连接。
   • 在order_items表的order_id和product_id字段上分别创建索引，加速连接操作。
2. 查询改写：
   • 减少中间结果集，先在orders和payments表中筛选出符合条件的订单，再与其他表连接。
   • 合理使用临时表或CTE（Common Table Expressions），将中间结果缓存，避免重复计算。

优化后的SQL语句（以MySQL为例）

WITH filtered_orders AS (
    SELECT o.order_id
    FROM orders o
    JOIN payments p ON o.order_id = p.order_id
    WHERE o.order_status = '完成'
),
order_item_info AS (
    SELECT oi.order_id, oi.product_id, p.product_name
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE oi.order_id IN (SELECT order_id FROM filtered_orders)
)
SELECT 
    c.customer_type,
    oii.product_name,
    AVG(p.payment_amount) AS average_payment_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_item_info oii ON o.order_id = oii.order_id
JOIN payments p ON o.order_id = p.order_id
WHERE o.order_id IN (SELECT order_id FROM filtered_orders)
GROUP BY c.customer_type, oii.product_name;

上述SQL语句首先使用CTE filtered_orders筛选出已支付且订单状态为完成的订单ID。然后，order_item_info CTE获取订单中的商品信息。最后，通过连接customers、orders、order_item_info和payments表，并结合GROUP BY计算不同客户类型下每种商品的平均支付金额。