大表上的 MySQL 存储过程占用服务器磁盘空间

Question

我继承了一个大约有 5 亿行的 MySQL InnoDB 表。该表包含 IP 编号和该编号所属的 ISP 的名称，均为字符串形式。

有时，在公司发生变化(例如合并或更名)后，我需要将 ISP 的名称更新为新值。但是，由于表太大，简单的 UPDATE...WHERE 语句不起作用 - 查询通常会超时，或者框内存不足。

因此，我编写了一个存储过程，它使用游标尝试一次更改一条记录。当我在一个小样本表上运行该过程时，它运行良好。但是，当我尝试针对生产中的整个 5 亿行表运行它时，我可以看到创建了一个临时表(因为出现了/tmp/xxx.MYI 和/tmp/xxx.MYD 文件)。临时表文件的大小不断增加，直到用完盒子上的所有可用磁盘空间(大约 40 GB)。

我不确定为什么需要这个临时表。服务器是否试图维护某种回滚状态？我真正的问题是，我可以更改存储过程以便不创建临时表吗？我真的不在乎是否有一些记录得到更新，但不是所有记录都得到更新 - 我可以轻松添加一些报告并继续运行过程，直到没有记录被更改。

此时，架构更改并不是一个真正的选择——例如，我无法更改表的结构。

在此先感谢您的帮助。

大卫

这是我的存储过程；

DELIMITER $$

DROP PROCEDURE IF EXISTS update_isp;
CREATE PROCEDURE update_isp()
BEGIN
  DECLARE v_finished INT DEFAULT 0;
  DECLARE v_num      VARCHAR(255) DEFAULT "";
  DECLARE v_isp      VARCHAR(255) DEFAULT "";

  DECLARE ip_cursor CURSOR FOR
    SELECT ip_number, isp FROM ips;

  DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET v_finished = 1;

  OPEN ip_cursor;

  get_ip: LOOP
    IF v_finished = 1 THEN
      LEAVE get_ip;
    END IF;

    FETCH ip_cursor INTO v_num, v_isp;

    IF v_isp = 'old name' THEN
      UPDATE ips SET isp = 'new name' WHERE ip_number = v_num;
    END IF;
  END LOOP get_ip;
  CLOSE ip_cursor;
END$$

DELIMITER ;

CALL update_isp();

我也试过将更新语句包装在事务中。这没有任何区别。

[编辑] 我下面的假设是错误的，即简单的计数过程不会创建临时表。临时表仍会创建，但它增长得更慢，并且在该过程完成之前该框不会用完磁盘空间。

所以问题似乎是在存储过程中对游标的任何使用都会导致创建临时表。我不知道为什么，也不知道是否有任何方法可以防止这种情况发生。

Answer 1

如果您的更新本质上是:

UPDATE ips
    SET isp = 'new name'
     WHERE isp = OLDNAME;

我猜测如果您在 isp(isp) 上有索引，这个没有光标的 update 会工作得更好:

create index idx_isp_isp on isp(isp);

创建此索引后，您的原始查询应该没问题。即使在非常大的表中更新单行也不应该有性能问题。问题很可能是找到该行，而不是更新它。