MST
星途 面试题库

面试题：MySQL复杂场景下的排序优化及方向调整

在一个大型电商数据库中，有`products`表（包含`product_id`、`product_name`、`price`等字段），`orders`表（包含`order_id`、`product_id`、`order_quantity`、`order_date`等字段），`customers`表（包含`customer_id`、`customer_name`等字段）。现在要查询每个客户购买的总金额（`price * order_quantity`），按照总金额降序排列。同时，为了提高查询性能，假设数据库中有合适的索引，你需要考虑如何利用索引来优化此排序查询，并且如果查询结果中有总金额相同的情况，再按照客户名称升序排列，写出完整的SQL查询及优化思路。
33.0万 热度难度
数据库MySQL

知识考点

AI 面试

面试题答案

一键面试

SQL查询

SELECT 
    c.customer_name,
    SUM(p.price * o.order_quantity) AS total_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    products p ON o.product_id = p.product_id
GROUP BY 
    c.customer_id, c.customer_name
ORDER BY 
    total_amount DESC, c.customer_name ASC;

优化思路

  1. 索引选择：
    • 在orders表的customer_id字段上建立索引，这样在连接customers表和orders表时可以快速定位相关记录。因为我们要根据customer_id关联两张表，此索引能加快连接操作。
    • 在orders表的product_id字段上建立索引，用于快速连接products表，提高orders表和products表连接时的查询效率。
    • 在products表的price字段上建立索引，虽然在计算price * order_quantity时不能直接利用索引优化计算，但在连接操作完成后，索引有助于更快地获取price值。
  2. 查询结构优化：
    • 使用JOIN操作而不是子查询来连接表，JOIN操作通常在现代数据库中执行效率更高，尤其是对于大型数据集。
    • GROUP BY子句在customer_id和customer_name上，这样能准确计算每个客户的总金额，并且在后续排序时，如果总金额相同，customer_name已经在分组中确定，便于按客户名称升序排列。
    • ORDER BY子句先按total_amount降序排列，再按customer_name升序排列，符合题目要求。在有合适索引的情况下，数据库可以更高效地完成这种排序操作。