php - 优化/替代此自引用更新查询

Question

我有这个更新查询:

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 字段(实际上是按字段排序)。

选择查询需要 9 秒，因为我们使用 order by 它是可以接受的。

此查询的更新部分需要很长时间。在 400.000 条记录表上超过 5 分钟。我们需要将其缩短到一分钟左右。

如何加快速度，或者您是否有其他方法来解决此问题？

Answer 1

子查询会拖慢你的速度。在实践中，我注意到将子查询分离到临时表或表变量中速度更快。

尝试:

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