面试题：MySQL复杂子查询语句的结构优化

Question

已知有`orders`表（包含`order_id`、`customer_id`、`order_date`等字段）和`order_items`表（包含`order_item_id`、`order_id`、`product_id`、`quantity`等字段）。要查询出在2023年下单，且下单商品数量总和超过100的客户ID。写出实现该需求的复杂查询语句，此语句包含子查询，同时阐述如何优化该子查询的结构以提高查询性能。

Answer 1

查询语句

SELECT customer_id
FROM orders
WHERE YEAR(order_date) = 2023
  AND order_id IN (
    SELECT order_id
    FROM order_items
    GROUP BY order_id
    HAVING SUM(quantity) > 100
);

优化建议

1. 索引优化：
   • 在 orders 表的 order_date 字段上创建索引，以加速按日期过滤。例如：CREATE INDEX idx_order_date ON orders(order_date);
   • 在 order_items 表的 order_id 和 quantity 字段上创建联合索引，以加速分组和聚合操作。例如：CREATE INDEX idx_order_id_quantity ON order_items(order_id, quantity);
2. 使用 JOIN 替代子查询：

SELECT o.customer_id
FROM orders o
JOIN (
    SELECT order_id
    FROM order_items
    GROUP BY order_id
    HAVING SUM(quantity) > 100
) oi ON o.order_id = oi.order_id
WHERE YEAR(o.order_date) = 2023;

这种方式避免了子查询中的嵌套，通常在执行效率上会有所提升。同时，也可以利用上述提到的索引来进一步优化查询性能。