4000万行MySQL表UPDATE语句过慢优化求助（128GB内存）

针对你4000万行temp表的UPDATE性能瓶颈问题，我从SQL优化、索引调整、配置优化和替代方案这几个方向给你一些实用建议：

既然temp表是每日先截断再插入新数据，最有效的优化方式是避免后续的UPDATE操作，直接在插入时完成所有字段的计算和关联：

1. 整合第一个UPDATE的关联逻辑 #

原本的插入语句如果是INSERT INTO temp (...) SELECT ... FROM 数据源，可以改成关联SchDate表一次性插入：

INSERT INTO temp (DATE1, CODE, TYPE, ..., LatestNav, NavDate, ...)
SELECT 
    数据源.DATE1, 数据源.CODE, 数据源.TYPE, ...,
    SchDate.NavRs, SchDate.LDate, ...
FROM 数据源
LEFT JOIN SchDate ON 数据源.Sch_Code = SchDate.Sch_Code;

这样就省去了后续关联4万行SchDate更新4000万行的操作，直接在插入时填充LatestNav和NavDate。

2. 整合第二个UPDATE的计算逻辑 #

同样，在插入时直接计算Age、CurrAmt等字段，不需要后续全表更新：

INSERT INTO temp (DATE1, CODE, ..., Age, CurrAmt, PL_Notional, Divd_Recd)
SELECT 
    数据源.DATE1, 数据源.CODE, ...,
    DATEDIFF(SchDate.LDate, 数据源.TR_DATE), -- 假设NavDate就是SchDate.LDate
    SchDate.NavRs * 数据源.Units,
    数据源.UNITS * (SchDate.NavRs - 数据源.Rate),
    0
FROM 数据源
LEFT JOIN SchDate ON 数据源.Sch_Code = SchDate.Sch_Code;

这种方式的性能远优于先插入再更新，因为插入时是批量写入，不需要维护已有的索引（插入后再建索引也比更新时维护索引快）。

如果因为业务限制无法修改插入逻辑，那么可以优化UPDATE的执行方式：

1. 分批次执行第一个关联UPDATE #

一次性关联更新4000万行会产生大量锁和IO，建议按Sch_Code分批次处理，每次处理一小部分数据并提交事务：

-- 先获取Sch_Code的范围
SELECT MIN(Sch_Code), MAX(Sch_Code) FROM SchDate;

-- 循环处理，每次处理1000个Sch_Code（根据实际情况调整批次大小）
SET @start = 最小Sch_Code;
SET @end = @start + 1000;
WHILE @start <= 最大Sch_Code DO
    UPDATE Temp 
    INNER JOIN SchDate ON Temp.Sch_Code = SchDate.Sch_Code 
    SET LatestNav = NavRs, NavDate = LDate 
    WHERE SchDate.Sch_Code BETWEEN @start AND @end;
    COMMIT;
    SET @start = @end + 1;
    SET @end = @start + 1000;
END WHILE;

这样可以减少单次操作的锁范围和IO压力，避免长时间占用资源。

2. 分批次执行第二个全表UPDATE #

全表更新4000万行会触发所有二级索引的更新，耗时极长。可以按主键NO分批次更新：

-- 获取主键范围
SELECT MIN(NO), MAX(NO) FROM Temp;

-- 每次更新10万行（根据服务器性能调整）
SET @start = 最小NO;
SET @end = @start + 100000;
WHILE @start <= 最大NO DO
    UPDATE Temp 
    SET Age = DATEDIFF(NAVDATE, TR_DATE), 
        CurrAmt = (LatestNav * Units), 
        PL_Notional = (UNITS * (LatestNav - Rate)), 
        Divd_Recd = 0
    WHERE NO BETWEEN @start AND @end;
    COMMIT;
    SET @start = @end + 1;
    SET @end = @start + 100000;
END WHILE;

分批次更新可以避免大事务，减少日志写入压力，同时降低锁冲突的概率。

temp表有大量二级索引，每次UPDATE都会更新所有涉及的索引，这是性能瓶颈的重要原因：

1. 全表UPDATE前临时删除二级索引 #

对于第二个全表UPDATE操作，可以先删除所有二级索引，更新完成后再重建：

-- 删除二级索引
DROP INDEX SCODE ON Temp;
DROP INDEX C_Code ON Temp;
DROP INDEX TYPE ON Temp;
DROP INDEX OS_Code ON Temp;
DROP INDEX LNav ON Temp;
DROP INDEX IDX_1 ON Temp;
DROP INDEX DATE1 ON Temp;

-- 执行全表UPDATE
UPDATE Temp SET Age = ...;

-- 重建索引（批量重建比单个CREATE INDEX更快）
ALTER TABLE Temp ADD INDEX SCODE(SCODE), ADD INDEX C_Code(C_Code), ADD INDEX TYPE(TYPE), ADD INDEX OS_Code(OS_Code), ADD INDEX LNav(LNav), ADD INDEX IDX_1(AGE,Type2), ADD INDEX DATE1(DATE1);

重建索引的速度远快于UPDATE时逐行维护索引，尤其是全表更新场景。

2. 检查索引必要性 #

回顾业务需求，确认所有二级索引都是必须的。比如IDX_1 (AGE,Type2)，如果这个索引只在后续存储过程中使用，而存储过程是在UPDATE之后执行，那么可以在UPDATE完成后再创建该索引，避免UPDATE时维护它。

你的my.cnf中有一些参数可以优化，尤其是针对大表更新的场景：

1. 调整缓冲池与内存参数 #

• sort_buffer_size、read_buffer_size、read_rnd_buffer_size、join_buffer_size：当前设置为2G、2G、1G、1G，太大了！每个连接都会分配这些内存，Windows下很容易导致内存不足。建议调整为：

  sort_buffer_size=64M
  read_buffer_size=64M
  read_rnd_buffer_size=128M
  join_buffer_size=128M
  

• innodb_buffer_pool_size：设置为90G，Windows 10需要确保物理内存足够（至少100G以上），否则会出现虚拟内存交换，严重影响性能。如果内存不足，建议降低到物理内存的70%左右。

2. 优化InnoDB日志与刷盘策略 #

• innodb_flush_log_at_trx_commit：当前设置为1（最安全但性能最差），如果业务可以接受秒级数据丢失风险，建议改成2：

  innodb_flush_log_at_trx_commit=2
  

  这样日志会每秒刷盘一次，性能提升明显。
• innodb_log_files_in_group：当前设置为10，一般2-4个足够，过多会增加管理成本：

  innodb_log_files_in_group=4
  

• innodb_flush_method：Windows下建议使用O_DIRECT绕过系统缓存，减少IO开销：

  innodb_flush_method=O_DIRECT
  

3. 调整事务与并发参数 #

• innodb_lock_wait_timeout：当前设置为100，分批次更新时可以适当降低，避免长时间等待锁：

  innodb_lock_wait_timeout=30
  

• max_connections：当前设置为500，足够日常使用，但要确保没有过多空闲连接占用资源。

• Windows环境限制：MySQL在Windows下的性能比Linux差，尤其是大表操作。如果业务允许，建议迁移到Linux服务器（比如CentOS、Ubuntu），性能会有显著提升。
• 检查表结构：temp表有很多字段，确认是否有冗余字段，减少每行数据的大小可以降低IO开销。比如decimal(19,4)如果可以用更小的精度，或者double满足需求的话，可以调整类型减少存储占用。

内容的提问来源于stack exchange，提问作者vishal