MST

星途 面试题库

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

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

知识考点

AI 面试

面试题答案

一键面试

表结构设计

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

  • 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);
  1. order_items表:在order_idproduct_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);
  1. products表:在id字段上创建主键索引(一般创建表时默认会为主键创建索引),确保产品的唯一性,并且在与order_items表连接时可以快速定位到对应的产品记录。