mysql语法优化,需要此语句在2秒内处理完数据,表数据有50w条

Question

update temp_trend_Reason1 AS M set M.MovementCodePre2=(select A.MovementCode from hr_equipment_trend AS A WHERE A.EquipmentNo=M.EquipmentNo AND A.ISDISABLED=0 AND A.ActivityDate<M.ActivityDatePre1 and A.MovementCode not in ('SNTR','RCVR') ORDER BY A.ActivityDate DESC LIMIT 1 ),
M.DepotPre2=(select A.Depot from hr_equipment_trend AS A WHERE A.EquipmentNo=M.EquipmentNo AND A.ISDISABLED=0 AND A.ActivityDate<M.ActivityDatePre1
and A.MovementCode not in ('SNTR','RCVR') ORDER BY A.ActivityDate DESC LIMIT 1 ),
M.ActivityDatePre2=(select A.ActivityDate from hr_equipment_trend AS A WHERE A.EquipmentNo=M.EquipmentNo AND A.ISDISABLED=0 AND A.ActivityDate<M.ActivityDatePre1 and A.MovementCode not in ('SNTR','RCVR') ORDER BY A.ActivityDate DESC LIMIT 1 );

Answer 1

参考免费微信小程序：皆我百晓生 作答，经由专家-皆知整理回复，如问题解决，望采纳；如仍有疑问，请评论回复。

要优化这个MySQL查询，我们可以考虑以下几个方面：

1. 索引优化：确保涉及的列（EquipmentNo, ActivityDate, ISDISABLED, MovementCode）都有适当的索引，这可以显著提高查询速度。

2. 减少子查询：子查询在每个更新操作中都会被执行，这可能会导致性能问题。如果可能，尝试将子查询的结果预先计算出来。

3. 批量更新：如果更新操作可以分成多个较小的批次执行，可以减少锁的争用，并可能提高性能。

4. 使用临时表：如果子查询的结果不会频繁变化，可以考虑将结果存储在临时表中，然后通过连接来更新主表。

5. 避免使用ORDER BY和LIMIT在子查询中：这会增加查询的复杂性。如果可能，尝试在子查询外部进行排序和限制。

6. 使用JOIN代替子查询：在某些情况下，使用JOIN可以提高性能。

下面是一个根据上述建议改进的示例代码。请注意，这只是一个示例，实际性能提升需要根据具体的数据库结构和数据分布进行调整：

-- 假设已经创建了必要的索引

-- 1. 创建临时表存储需要更新的数据
CREATE TEMPORARY TABLE IF NOT EXISTS temp_update_data AS
SELECT
    A.EquipmentNo,
    MAX(CASE WHEN A.MovementCode NOT IN ('SNTR', 'RCVR') THEN A.MovementCode END) AS MovementCode,
    MAX(CASE WHEN A.MovementCode NOT IN ('SNTR', 'RCVR') THEN A.Depot END) AS Depot,
    MAX(CASE WHEN A.MovementCode NOT IN ('SNTR', 'RCVR') THEN A.ActivityDate END) AS ActivityDate
FROM
    hr_equipment_trend AS A
WHERE
    A.ISDISABLED = 0
GROUP BY
    A.EquipmentNo;

-- 2. 使用JOIN更新主表
UPDATE temp_trend_Reason1 AS M
JOIN temp_update_data AS T ON M.EquipmentNo = T.EquipmentNo
SET
    M.MovementCodePre2 = T.MovementCode,
    M.DepotPre2 = T.Depot,
    M.ActivityDatePre2 = T.ActivityDate;

-- 3. 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_update_data;

这个例子使用了一个临时表来存储需要更新的数据，然后通过JOIN操作来更新主表。这种方法可以减少对子查询的依赖，从而可能提高性能。

请注意，性能优化是一个复杂的过程，可能需要根据实际情况进行多次调整和测试。如果问题依然存在，可能需要进一步分析查询计划（使用EXPLAIN语句）来确定瓶颈所在。