mysql - 如何在 mysql 中快速更新数百万行

Question

我需要更新数据库中超过 400 万行。我们目前使用的是mysql 5.1，更新速度很慢。目前，不到 100,000 行需要 30 多分钟，这是不可取的。

我有一个 sql 脚本，它自动生成我需要的更新语句并将它们输出到一个文件中。我曾尝试将其分解为多个文件，以查看是否可以加快速度，但无济于事。

我的脚本使用 select concat 生成更新语句并将它们写入文件，如下所示:

 SELECT CONCAT("UPDATE status SET next_status_id=", ts_next.id, ",duration=",
 SUM(UNIX_TIMESTAMP(ts_next.status_date) - UNIX_TIMESTAMP(ts.status_date)),
  " WHERE id =", ts.id, " AND next_status_id IS NULL AND duration = 0;")
 into outfile '/tmp/status_updates.sql'
 FIELDS TERMINATED BY '' 
 LINES TERMINATED BY '\n'
FROM
   status ts
        LEFT JOIN
      status ts_next ON ts_next.ticket_id = ts.ticket_id
        AND ts_next.id = (SELECT 
            MIN(id)
        FROM
            status first_status
        WHERE
            first_status.id > ts.id AND first_status.ticket_id = ts.ticket_id)
            GROUP BY ts.id;

理想情况下，我希望尽快完成这些更新，并且乐于接受所有关于以最佳方式做到这一点且影响最小的建议。

解决方案:

UPDATE status ts1,
    (SELECT 
    ts_next.id as next_status_id,
    ts.id as status_id,
    IFNULL(SUM(UNIX_TIMESTAMP(ts_next.status_date) - UNIX_TIMESTAMP(ts.status_date)), 0) as duration
    FROM
   status ts
        LEFT JOIN
      status ts_next ON ts_next.ticket_id = ts.ticket_id
        AND ts_next.id = (SELECT 
            MIN(id)
        FROM
           status first_status
        WHERE
            first_status.id > ts.id AND first_status.ticket_id = ts.ticket_id)
            GROUP BY ts.id) ts2
    SET ts1.next_status_id = ts2.next_status_id, ts1.duration = ts2.duration 
    WHERE id=ts2.status_id AND ts1.next_status_id IS NULL;

Answer 1

使用“更新选择”。我认为这应该是更新多行的最快方法。因此看到这个问题: MySQL - UPDATE query based on SELECT Query