面试题：MySQL同表复杂查询与更新的综合优化

Question

在一个复杂的电商订单系统中，有一张订单表orders，字段包括order_id（订单编号）, user_id（用户编号）, order_amount（订单金额）, status（订单状态）, parent_order_id（父订单编号，用于关联子订单，0表示无父订单）。需求如下：1. 查询每个用户的总订单金额，且只统计状态为已完成的订单，同时要考虑查询性能优化。2. 将每个用户订单金额最高的订单标记为‘重点订单’（在status字段添加‘priority’标识），在更新操作中要避免锁争用并确保数据一致性。请详细阐述实现方案，包括SQL语句、索引设计、事务处理以及可能涉及的优化手段。

Answer 1

1. 查询每个用户的总订单金额

• SQL 语句：

SELECT user_id, SUM(order_amount) AS total_amount
FROM orders
WHERE status = '已完成'
GROUP BY user_id;

• 索引设计： 为了提升查询性能，在 status 和 user_id 字段上创建复合索引。

CREATE INDEX idx_status_user_id ON orders (status, user_id);

此索引可以帮助数据库快速定位到状态为“已完成”的订单，并且按 user_id 进行高效分组。

2. 将每个用户订单金额最高的订单标记为‘重点订单’

• SQL 语句： 使用 UPDATE 和 CASE 语句结合窗口函数来实现。

WITH RankedOrders AS (
    SELECT order_id, user_id, order_amount, status,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS rn
    FROM orders
)
UPDATE orders
SET status = CONCAT(status, ',priority')
WHERE order_id IN (SELECT order_id FROM RankedOrders WHERE rn = 1);

此方法使用了公共表表达式（CTE）来为每个用户的订单按金额进行排名，然后更新排名第一（金额最高）的订单的 status 字段。

• 索引设计： 除了上述 idx_status_user_id 索引，为了提升更新性能，在 order_id 上创建索引。

CREATE INDEX idx_order_id ON orders (order_id);

• 事务处理： 为确保数据一致性并避免锁争用，使用 BEGIN TRANSACTION、COMMIT 和 ROLLBACK。

BEGIN TRANSACTION;
-- 执行上述更新语句
COMMIT;

在事务内执行更新操作，这样如果更新过程中出现错误，可以通过 ROLLBACK 回滚事务，保证数据的一致性。同时，现代数据库通常采用行级锁，这里使用单个 UPDATE 语句且通过索引定位行，能减少锁争用的可能性。

优化手段

• 批量操作：如果数据量巨大，可以考虑批量更新，减少单个事务处理的数据量，降低锁持有时间。
• 异步处理：可以将标记“重点订单”的操作放到异步任务队列中处理，避免对主业务流程产生影响。