面试题：MySQL复杂查询性能瓶颈分析

Question

假设有三张表：orders（订单表，字段有order_id, customer_id, order_date等），order_items（订单项表，字段有item_id, order_id, product_id, quantity等），products（产品表，字段有product_id, product_name, price等）。要执行一个查询，统计每个客户在2023年购买的不同产品数量以及总花费，查询语句如下：`SELECT o.customer_id, COUNT(DISTINCT oi.product_id) AS product_count, SUM(p.price * oi.quantity) AS total_spend FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id WHERE YEAR(o.order_date) = 2023 GROUP BY o.customer_id;` 请分析此查询可能出现的性能瓶颈，并给出优化建议。

Answer 1

性能瓶颈分析

1. 全表扫描：
   • 如果 orders 表、order_items 表和 products 表数据量较大，在没有合适索引的情况下，JOIN 操作可能会导致全表扫描。例如，o.order_id = oi.order_id 和 oi.product_id = p.product_id 这两个连接条件，若相关字段没有索引，数据库需要逐行匹配数据，性能开销大。
   • WHERE YEAR(o.order_date) = 2023 条件，对 order_date 字段使用 YEAR 函数，数据库无法使用该字段上可能存在的索引，同样会导致对 orders 表进行全表扫描。
2. 聚合操作开销：
   • COUNT(DISTINCT oi.product_id) 操作会消耗较多资源，因为它需要对所有符合条件的 product_id 进行去重处理。在大数据量下，这种去重操作的性能较差。

优化建议

1. 添加索引：
   • 在 orders 表的 order_id 和 order_date 字段上添加索引：

     CREATE INDEX idx_order_id ON orders(order_id);
     CREATE INDEX idx_order_date ON orders(order_date);
     

   • 在 order_items 表的 order_id 和 product_id 字段上添加索引：

     CREATE INDEX idx_order_items_order_id ON order_items(order_id);
     CREATE INDEX idx_order_items_product_id ON order_items(product_id);
     

   • 在 products 表的 product_id 字段上添加索引：

     CREATE INDEX idx_products_product_id ON products(product_id);
     

2. 优化 WHERE 条件：
   • 改写 WHERE 条件，避免在字段上使用函数，例如：

     WHERE o.order_date >= '2023 - 01 - 01' AND o.order_date < '2024 - 01 - 01'
     

3. 优化 COUNT(DISTINCT) 操作：
   • 如果业务允许，可以考虑在应用层进行去重计数，这样可以减少数据库的压力。如果一定要在数据库中处理，可以先通过子查询将 DISTINCT 操作提前，例如：

     WITH distinct_product AS (
         SELECT order_id, DISTINCT product_id
         FROM order_items
     )
     SELECT o.customer_id, COUNT(dp.product_id) AS product_count, SUM(p.price * oi.quantity) AS total_spend
     FROM orders o
     JOIN distinct_product dp ON o.order_id = dp.order_id
     JOIN order_items oi ON o.order_id = oi.order_id
     JOIN products p ON oi.product_id = p.product_id
     WHERE o.order_date >= '2023 - 01 - 01' AND o.order_date < '2024 - 01 - 01'
     GROUP BY o.customer_id;