我有这个更新查询:
UPDATE aggregate_usage_input t
JOIN (SELECT t2.id
FROM aggregate_usage_input t2
WHERE t2.is_excluded_total_gallons = 0
AND t2.is_excluded_cohort = 0
AND t2.is_excluded_outlier = 0
ORDER BY t2.occupant_bucket_id,
t2.residence_type_bucket_id,
t2.reading_year,
t2.nthreading,
t2.total_gallons)t_sorted
ON t_sorted.id = t.id
SET t.rownum = @rownum := @rownum + 1
其基于排序更新rownum字段(实际上是逐字段排序)。
select查询需要9秒,由于我们使用order by,所以它是可以接受的。
此查询的更新部分需要很长时间。在400000张记录表上超过5分钟。我们需要在一分钟左右的时间内将其减少
如何加快速度,或者你有其他方法来解决这个问题吗?
子查询在这里会减慢您的速度。在实践中,我注意到将子查询分离为临时表或表变量会更快。
尝试:
CREATE TEMPORARY TABLE Temp (id int);
INSERT INTO Temp
SELECT t2.id
FROM aggregate_usage_input t2
WHERE t2.is_excluded_total_gallons = 0
AND t2.is_excluded_cohort = 0
AND t2.is_excluded_outlier = 0
ORDER BY t2.occupant_bucket_id,
t2.residence_type_bucket_id,
t2.reading_year,
t2.nthreading,
t2.total_gallons;
UPDATE aggregate_usage_input t
JOIN Temp t_sorted
ON t_sorted.id = t.id
SET t.rownum = @rownum := @rownum + 1