面试题：MySQL单条查询剖析之高级难度：复杂查询优化

Question

有三张表，`orders`表（包含`order_id`、`customer_id`、`order_date`等字段），`customers`表（包含`customer_id`、`customer_name`等字段），`order_items`表（包含`order_item_id`、`order_id`、`product_id`、`quantity`等字段）。要查询出2023年下单且购买产品数量总和超过100的客户名称。请写出SQL查询语句，并详细剖析该查询在执行过程中的查询计划，以及如何通过优化索引和查询结构来提升查询性能。

Answer 1

SQL查询语句

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

查询计划剖析

1. JOIN操作：
   • 首先进行customers表和orders表的连接（JOIN），连接条件是c.customer_id = o.customer_id。这个操作会生成一个临时表，其中包含两个表匹配的行。
   • 接着，将上述临时表与order_items表进行连接，连接条件是o.order_id = oi.order_id，再次生成一个更大的临时表，包含了所有相关联的数据。
2. WHERE过滤：
   • 使用WHERE YEAR(o.order_date) = 2023过滤出2023年下单的记录。这个操作在前面生成的临时表上进行，减少后续处理的数据量。
3. GROUP BY和HAVING：
   • GROUP BY c.customer_id, c.customer_name按照客户ID和客户名称对数据进行分组。
   • HAVING SUM(oi.quantity) > 100对每个分组的数据进行统计，只保留购买产品数量总和超过100的分组。

优化索引

1. 在orders表上：
   • 为customer_id字段添加索引，因为它用于连接customers表，索引可以加快连接操作。例如：CREATE INDEX idx_orders_customer_id ON orders(customer_id);
   • 为order_date字段添加索引，因为它用于WHERE子句过滤，能加速日期过滤。例如：CREATE INDEX idx_orders_order_date ON orders(order_date);
2. 在order_items表上：
   • 为order_id字段添加索引，因为它用于连接orders表，有助于快速定位相关订单的订单项。例如：CREATE INDEX idx_order_items_order_id ON order_items(order_id);
   • 为quantity字段添加索引，虽然在HAVING子句中使用聚合函数，但该索引可能对数据检索有一定帮助，尤其在数据量较大时。例如：CREATE INDEX idx_order_items_quantity ON order_items(quantity);

优化查询结构

1. 子查询优化：可以尝试将复杂的多表连接拆分成子查询，先在子查询中处理部分逻辑，减少主查询的复杂度。例如，先在子查询中过滤出2023年的订单及其订单项，再与customers表连接。
2. 使用覆盖索引：如果查询涉及的字段都包含在索引中，数据库可以直接从索引中获取数据，避免回表操作，提高查询效率。例如，如果查询只需要customer_name和quantity字段，可以创建一个包含customer_id、customer_name和quantity的复合索引。