MST

星途 面试题库

面试题：MySQL计算字段与数据拼接在复杂业务逻辑及性能优化下的处理

在一个电商数据库中，有`products`表（`product_id`、`product_name`、`price`、`category_id`），`categories`表（`category_id`、`category_name`），`orders`表（`order_id`、`customer_id`、`order_date`），`order_items`表（`order_id`、`product_id`、`quantity`）。需求是编写一个查询，获取每个订单的详细信息，包括`order_id`、`order_date`、订单中所有产品名称拼接成一个字符串（格式为`product1, product2,...`），订单总金额（每种产品`price`乘以`quantity`之和）作为计算字段，同时要求查询结果按订单总金额降序排列，并且在大数据量下如何对该查询进行性能优化，说明优化思路和可能用到的索引策略。
24.9万 热度难度
数据库MySQL

知识考点

AI 面试

面试题答案

一键面试

SQL查询

SELECT
    o.order_id,
    o.order_date,
    GROUP_CONCAT(p.product_name SEPARATOR ', ') AS product_names,
    SUM(p.price * oi.quantity) AS total_amount
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id
JOIN
    products p ON oi.product_id = p.product_id
GROUP BY
    o.order_id, o.order_date
ORDER BY
    total_amount DESC;

性能优化思路

  1. 索引策略：
    • 在orders表的order_id列上创建索引，因为在连接order_items表时会用到，加快连接速度。
    • 在order_items表的order_id和product_id列上创建联合索引，这样在连接orders表和products表时都能提高效率。
    • 在products表的product_id列上创建索引，用于连接order_items表。
  2. 查询优化：
    • 避免使用SELECT *，只选择需要的字段，减少数据传输和处理量。
    • 分析查询计划（如使用EXPLAIN关键字），查看查询执行的实际情况，根据结果调整索引和查询结构。
  3. 数据分区：
    • 如果数据量非常大，可以考虑对orders表和order_items表按order_date进行分区，比如按月份或年份分区。这样在查询特定时间段的订单时，可以快速定位到相关的数据分区，减少扫描的数据量。