面试题：MySQL Schema设计中复杂表关系下的关联查询优化

Question

在一个电商系统中，有产品表products（id, product_name），订单表orders（id, order_date），订单详情表order_items（id, order_id, product_id, quantity）。现在需要查询出每个产品在最近一周内的销售总量，并按照销售总量降序排列。请设计合适的表结构（如有必要），并写出高效的SQL查询语句，同时说明你在优化过程中采取的索引策略及理由。

Answer 1

表结构设计

题目中给出的表结构已经较为合理，无需额外设计：

• products：包含产品信息，id（产品唯一标识），product_name（产品名称）
• orders：包含订单信息，id（订单唯一标识），order_date（订单日期）
• order_items：关联订单与产品，id（订单详情唯一标识），order_id（订单外键），product_id（产品外键），quantity（产品数量）

SQL查询语句

SELECT 
    p.product_name,
    SUM(oi.quantity) AS total_sales
FROM 
    products p
JOIN 
    order_items oi ON p.id = oi.product_id
JOIN 
    orders o ON oi.order_id = o.id
WHERE 
    o.order_date >= CURDATE() - INTERVAL 7 DAY
GROUP BY 
    p.id, p.product_name
ORDER BY 
    total_sales DESC;

索引策略及理由

1. orders表：在order_date字段上创建索引，理由是在WHERE子句中对order_date进行筛选，使用索引可以加快日期范围查询的速度，减少全表扫描。

CREATE INDEX idx_order_date ON orders(order_date);

2. order_items表：在order_id和product_id字段上分别创建索引。因为order_items表通过这两个字段分别与orders表和products表进行连接，索引可以加速连接操作，提升查询效率。

CREATE INDEX idx_order_id ON order_items(order_id);
CREATE INDEX idx_product_id ON order_items(product_id);

3. products表：在id字段上创建主键索引（一般创建表时默认会为主键创建索引），确保产品的唯一性，并且在与order_items表连接时可以快速定位到对应的产品记录。