上周帮运营团队做一个订单价格的批量修复他们拿来一张10万行的Excel需要按订单号把最新售价更新到线上库。运营同事最初用工具逐条生成UPDATE再执行跑了半小时进度还在原地。我接手后改用Python加MySQL的临时表关联更新全量几十秒跑完。这类“Python如何高效更新MySQL的数据”的问题我在批处理、数据修复、ETL同步项目里遇过太多次今天把经验和坑一次性捋清楚适合正在用Python做数据更新的开发、运维和数据同学参考。说明下面内容偏后端开发和数据修复视角如果你只是偶尔手工改几条数据直接在MySQL客户端里改更快没必要折腾批量方案。1. 先搞清楚“慢”在哪里从一条UPDATE到一万条UPDATE1.1 你最初可能就在用这种写法很多同学第一次写Python更新MySQL基本都是这个路子import pymysql conn pymysql.connect(hostlocalhost, userroot, password***, databasetest) data_list [(1001, 19.9), (1002, 29.9), ...] # 假设有几千条 for order_no, price in data_list: with conn.cursor() as cursor: cursor.execute(UPDATE order_data SET price%s WHERE order_no%s, (price, order_no)) conn.commit()这段代码逻辑上完全正确但性能非常差。不是Python解释器慢也不是MySQL单条UPDATE慢而是整个交互模式出了问题。我早期也这样写过后来用性能分析工具一测发现90%以上的时间都耗在“网络往返”和“事务提交”上。1.2 慢的三个关键原因网络往返、隐式事务、索引代价第一个原因是网络往返。Python程序通过TCP协议和MySQL服务端通信每调用一次cursor.execute就是一次请求加一次响应的完整来回。假设应用和数据库不在同一台机器每次往返至少0.5ms到2ms一万条UPDATE就产生10到20秒的纯网络开销。如果应用服务器和数据库跨机房这个数字还会放大。第二个原因是逐条提交事务。上面例子里每次execute后都调用conn.commit()InnoDB每提交一次事务都要把redo log刷到磁盘也就是fsync。一次fsync在机械盘上可能要几十毫秒。你以为是数据库更新慢其实是磁盘在扛着“每次提交都落盘”的压力。就算用的是固态盘一万次提交产生的刷盘开销依然很可观。第三个原因是索引维护。UPDATE修改字段时如果字段本身有索引或者WHERE条件要使用二级索引MySQL需要同步维护B树。单条操作时这个成本几乎感觉不到但循环一万次后随机IO和锁等待就会累积成瓶颈。再加上逐条UPDATE产生大量binlog主从复制环境里的同步延迟也会被拉高。1.3 一个简单的耗时模型帮你判断瓶颈优化之前先估算时间构成避免瞎调。下面是我对“一万条逐条UPDATE”场景的经验模型耗时项单次耗时一万次总耗时说明网络往返0.5~2ms5~20s应用服务器与数据库网络越远越严重事务提交commit2~20ms20~200s受磁盘刷盘策略影响机械盘更慢UPDATE执行0.1~1ms1~10s单条主键更新本身很快合计2~23ms26~230s慢主要来自提交和往返看到这个模型就能明白为什么“单条UPDATE很快循环UPDATE就爆炸”。优化的核心思路就一条减少网络往返次数减少事务提交次数让每一轮交互尽量处理更多行数据。这时候再去配上索引和合适的事务参数问题往往已经解决了大半。2. 批量更新的核心武器executemany与参数化SQL2.1 executemany到底做了什么网上很多文章说“用executemany批量执行能大幅提升性能”这句话必须拆开看。PyMySQL的executemany在遇到INSERT语句时会把多行数据拼成一条多值INSERT再执行这是实打实的优化。但当语句是UPDATE时PyMySQL并不会把多条UPDATE合并成一条它内部实际上还是循环调用execute跟你在Python里写for循环没有本质区别。我第一次踩这个坑是在一次价格批量修复任务里。当时把for循环换成executemany跑起来发现耗时几乎没有变化。查了驱动源码才明白executemany对UPDATE语句只是省掉了部分SQL解析和参数绑定的开销网络往返和事务提交一点没少。如果还保留着逐条commit那收益基本可以忽略。2.2 如何在UPDATE场景用一条SQL搞定多行既然executemany不能真正批量UPDATE就得自己构造批量语句。最直观的办法是把多条更新合并成一条CASE WHEN语句一次SQL执行更新多行UPDATE order_data SET price CASE order_no WHEN 1001 THEN 19.9 WHEN 1002 THEN 29.9 WHEN 1003 THEN 39.9 END WHERE order_no IN (1001, 1002, 1003);Python里按这个模板动态生成SQL和参数即可。注意参数占位符需要重复每个WHEN条件需要一个订单号占位符和一个价格占位符WHERE的IN列表还需要再来一遍订单号占位符。代码必须仔细对齐否则位置错一个数据就错一个。def batch_update_by_case(conn, table, key_field, value_field, data_list, batch_size1000): for start in range(0, len(data_list), batch_size): batch data_list[start:start batch_size] when_sql_parts [] keys [] params [] for key, value in batch: when_sql_parts.append(WHEN %s THEN %s) params.extend([key, value]) keys.append(key) in_placeholders ,.join([%s] * len(keys)) sql ( fUPDATE {table} SET {value_field} CASE {key_field} .join(when_sql_parts) f END WHERE {key_field} IN ({in_placeholders}) ) params.extend(keys) with conn.cursor() as cur: cur.execute(sql, params) conn.commit()这里有个坑CASE WHEN更新生成的SQL太长时可能超过max_allowed_packetMySQL会直接报Packet too large。所以函数里加了batch_size1000让每次拼接的SQL保持在安全范围。实际可按字段长短调整我一般控制在1000到2000行一批单批SQL字符串不超过1MB。2.3 临时表UPDATE JOIN最通用的批量更新方案CASE WHEN方式只适合更新单表、单字段、数据量可控的场景。如果数据来自Excel、接口或另一张表要更新多个字段或者数据量大到SQL拼接困难更好的方案是先把待更新数据灌入临时表再用UPDATE JOIN一次完成关联更新。def batch_update_by_tmp_table(conn, data_list): with conn.cursor() as cur: cur.execute(DROP TEMPORARY TABLE IF EXISTS tmp_order_price) cur.execute( CREATE TEMPORARY TABLE tmp_order_price ( order_no VARCHAR(32) PRIMARY KEY, price DECIMAL(10,2) ) ENGINEInnoDB ) # 对INSERT语句PyMySQL的executemany通常会拼成多值INSERT insert_sql INSERT INTO tmp_order_price (order_no, price) VALUES (%s, %s) cur.executemany(insert_sql, data_list) cur.execute( UPDATE order_data o JOIN tmp_order_price t ON o.order_no t.order_no SET o.price t.price ) conn.commit()临时表是session级别的连接断开会自动删除不污染线上库。建表时给关联字段加主键索引UPDATE JOIN就能走索引关联MySQL只需对原表按主键或二级索引逐行定位并更新避免每行都重新解析SQL。这个方案我用得最多能覆盖90%以上的批量更新场景。唯一额外成本是临时表写入时间但多值INSERT本身会减少网络往返整体性能远高于逐条UPDATE。3. 针对不同场景的高效更新方案3.1 场景A按唯一键更新同一张表价格、状态等离散字段如果你的需求是“已知一批订单号要把价格、状态或促销标记改成指定值”优先考虑CASE WHEN或临时表。我一般这样选数据量几千到几万且字段不超过两三个用CASE WHEN生成一条SQL。数据量超过5万或者字段很多用临时表UPDATE JOIN避免SQL过于臃肿。更新多个列时尽量合并到同一个CASE WHEN里。比如“改价格”和“改状态”一起做不要分成两条UPDATE分两次全表扫描。另外如果更新的字段本身是唯一索引或主键的一部分MySQL需要维护唯一索引耗时通常比普通字段更新更明显更要避免逐条循环。3.2 场景B从另一张表同步数据这类场景常见于数据中台同步、订单表从源库同步到分析库。假设你想把daily_report表里的价格同步到order_data表直接写UPDATE JOIN就能完成UPDATE order_data o JOIN daily_report d ON o.order_no d.order_no SET o.price d.price WHERE d.update_date 2025-01-01如果除了更新已有数据还要把新数据插入进来可以用INSERT ... ON DUPLICATE KEY UPDATE。MySQL 8.0.20之后不建议继续用老式的VALUES()函数推荐别名语法INSERT INTO order_data (order_no, price, status) VALUES (1001, 19.9, 1) AS new ON DUPLICATE KEY UPDATE price new.price, status new.status高版本新项目直接用别名写法别再用旧写法否则升级数据库时会看到deprecated警告未来迁移也麻烦。3.3 场景C海量数据分批更新与进度控制当需要更新的数据量上百万时一次UPDATE JOIN也可能因为锁范围过大导致主从延迟飙升。这时必须分批执行。分批的核心是控制每批次行数并在每批之间commit保留一个可继续的游标。相比OFFSET分页我更推荐键集分页keyset pagination记住上一批最后一条主键下一批只取主键大于它的数据。OFFSET会随着数据量增加越来越慢而keyset在有序主键下基本稳定。last_id 0 batch_size 2000 while True: rows fetch_rows( SELECT order_no, price FROM tmp_source WHERE id %s ORDER BY id LIMIT %s , (last_id, batch_size)) if not rows: break # 在这里做CASE WHEN或临时表批量更新 batch_update(rows) last_id rows[-1][id] conn.commit()每批commit非常重要。长事务会导致undo log膨胀、历史版本堆积一旦系统崩溃回滚时间会非常长。把长事务拆成多个短事务锁等待和恢复成本都会降低。批次大小建议1000到5000行具体还要看行宽和线上负载。3.4 场景D需要“先查后改”的复杂业务逻辑有些业务逻辑必须先把数据读进Python经过算法或外部接口计算后再写回MySQL比如调用外部风控后修改订单状态。这种场景最容易犯的错误是循环里先SELECT后UPDATE期间还夹着外部IO调用导致数据库连接长时间被占用。正确的做法是拆成三个阶段用一次性查询把待处理数据的主键和原值批量读出每次1000条。在Python里完成计算或外部调用把结果暂存在内存列表。回到数据库用CASE WHEN或临时表一次性写回。外部接口慢就不该连累数据库连接。阶段2可以控制并发量去做外部调用但别把更新拆成一条条发回数据库。我处理过每天几十万次外部调用的回写任务先攒批再统一更新整体时间比逐条“查询-计算-更新”快了一个数量级。4. 连接与事务配置高效更新的隐形因素4.1 autocommit与手动事务别让MySQL替你做保守选择连接配置里最容易忽略的是autocommit。PyMySQL默认autocommitFalse这意味着每条UPDATE如果不手动commit会一直处于同一个事务里。如果你循环里不commit最后统一commit一次事务会积累大量行锁如果每条都commit又会频繁fsync。两种极端都不可取。正确做法是手动控制事务边界一个批次开启一个事务批量更新后commit。conn pymysql.connect(hostlocalhost, userroot, password***, databasetest, autocommitFalse) try: for batch in batches: with conn.cursor() as cur: # 这里替换成你的批量UPDATE SQL cur.execute(update_sql, params) conn.commit() except Exception: conn.rollback() raise批次大小和commit时机直接相关。事务太小提交次数多事务太大锁管理和undo开销变大。我在生产环境里一般先压测几组批次大小再选择一个稳定值。有时候1000行一批和5000行一批差异并不大关键是不要一条一提交也不要一次性提交几十万行。4.2 连接池别再为每批次新建连接Python连接MySQLTCP握手和权限认证本身有成本。如果每执行一次批量任务就新建连接光建连时间可能达到几十甚至上百毫秒。批量更新场景下正确姿势是使用连接池复用连接。DBUtils 2.x配合PyMySQL可以这样写from dbutils.pooled_db import PooledDB import pymysql pool PooledDB( creatorpymysql, maxconnections20, mincached5, blockingTrue, hostlocalhost, userroot, password***, databasetest, autocommitFalse, ) conn pool.connection()连接池配合批量更新能省去反复建连的耗时也方便在多个批次间复用同一个会话。需要注意如果用了临时表方案一定要在同一连接内完成“建临时表→插数据→UPDATE→DROP”。连接被归还到池子后临时表是否还在取决于驱动的会话清理逻辑所以最好用with语句控制连接上下文。4.3 驱动与MySQL参数max_allowed_packet、innodb_flush_log_at_trx_commit批量SQL或多行INSERT很容易超过MySQL默认包大小。如果报Packet too large不要上来就改配置文件重启数据库可以先在会话级别调大SET SESSION max_allowed_packet 67108864;这样只对当前连接生效代码里每次连接初始化后执行一次即可。再聊一个全局参数innodb_flush_log_at_trx_commit。默认值是1表示每次事务提交都刷redo log到磁盘最安全但最慢。如果是批量修复数据且能接受极端情况下丢失最后1秒日志可以临时调成2减少刷盘次数以提升吞吐。但注意这是全局参数不能用SET SESSION调整需要DBA在维护窗口操作任务完成后必须改回1。生产环境别自己乱碰。另外MySQL 8.0中SHOW PROFILE虽然还能用但已有废弃趋势后续排查建议转向performance_schema。不过日常快速验证时SHOW PROFILE仍然够用。5. 真实项目中的性能调优与踩坑记录5.1 更新慢到锁表的排查链路我遇到过这样的问题用临时表UPDATE JOIN更新500万行跑了十几分钟还没结束。一查EXPLAIN发现临时表和原表关联时居然没走索引typeALL还出现了Using join buffer。原因是两表的order_no字段字符集不一致原表是utf8mb4_unicode_ci临时表建表时继承了session默认的排序规则。MySQL做关联时为了统一字符集无法直接使用原表索引只能临时转换再比较。排查链路我总结成四步先EXPLAIN看type是否变成ALL或出现Using join buffer。检查关联字段的字符集和排序规则是否一致。用SHOW CREATE TABLE对比原表和临时表字段定义。修正方式建临时表时显式指定CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci或者在插入临时表前先转换好字符串。这个坑非常隐蔽数据量小的时候感觉不到数据量一大就直接让你怀疑MySQL性能。5.2 索引失效函数包裹导致的坑另一个高频坑是WHERE条件里对索引字段使用函数。有人喜欢把日期字段转成字符串再比较UPDATE order_data SET status1 WHERE DATE(created_at) 2025-01-15这样写即使created_at有索引也用不上因为MySQL必须对每一行计算DATE()再比较。正确写法是范围条件UPDATE order_data SET status1 WHERE created_at 2025-01-15 00:00:00 AND created_at 2025-01-16 00:00:00批量更新通常隐含“影响大量行”一旦全表扫描加行锁数据库很容易被拖垮。所以批量更新上线前先INL有EXPLAIN确认SQL走索引再放到生产执行。5.3 死锁与事务过大怎么用分批和排序降低概率多个线程同时做批量更新时如果以不同顺序更新同一组行InnoDB的间隙锁和行锁容易互相等待最终触发死锁。MySQL检测到死锁会回滚其中一个事务Python脚本如果没做重试任务可能直接中断。我的土办法是所有批量更新之前先对数据按主键或唯一键排序。两个线程都在更新order_no1001和1002时如果都按1001、1002的顺序更新就不会交叉持有锁。排序后还要把事务长度控制在秒级不要让一个事务长时间握住几千上万个行锁。如果业务允许还可以给批量更新任务加一个分片字段多个worker各领一段不重叠的数据范围从源头避免锁冲突。5.4 验证效率用EXPLAIN与性能日志确认瓶颈优化做完了怎么证明有效除了记录Python层耗时我还会在MySQL里开profilingSET profiling 1; UPDATE order_data o JOIN tmp_order_price t ON o.order_no t.order_no SET o.price t.price; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;SHOW PROFILE会列出CPU、Sending data、executing等阶段的耗时。如果Sending data占了大头说明瓶颈在数据扫描如果Waiting for handler commit占了高位瓶颈就是提交和刷盘。这样比在Python里print时间更能定位问题。日常批量任务我也会在脚本里记录三段时间数据准备、批量更新执行、commit。分开统计后绝大多数“Python更新MySQL慢”都能明确落在网络往返、事务提交或SQL执行三个环节中。6. 给方案排个优先级什么时候用什么6.1 一张表直接选型我把这些年顺手且有效的方案整理成一张优先级表场景推荐方案原因少量数据几十条单条UPDATE 手动commit简单可控没必要拼SQL几千到几万条单表CASE WHEN一条SQL减少网络往返SQL简短几万条以上多字段临时表UPDATE JOIN关联走索引SQL可维护源表同步到目标表INSERT ... ON DUPLICATE KEY UPDATE一条语句完成插入和更新百万级分批临时表commit控制控制锁范围避免长事务先计算再回写攒批后统一更新不长期占用数据库连接优先级判断只看两个指标数据量以及更新数据是否有明确来源。有明确来源就优先临时表JOIN没有明确来源就拼CASE WHEN尽量别用for循环逐条UPDATE。6.2 什么时候不要用批量更新批量更新也不是万能的。以下场景我会刻意不用数据量很小手工改几条更直接。每条UPDATE的WHERE条件完全独立且中间掺杂大量业务判断强行合并反而让SQL不可维护。实时性要求极高的在线更新比如用户点击收藏立即改状态单条UPDATE本身够快攒批反而增加延迟。临时表方案要占用数据库临时空间如果更新数据大到tmpdir都无法容纳需要先评估磁盘和内存再决定。6.3 ORM的批量更新陷阱如果你用SQLAlchemy或Django ORM要小心它们的“批量更新”API底层不一定是一条SQL。很多ORM的bulk_update_mappings看似批量实际会在日志里打出N条UPDATE语句只是封装得比较“像批量”。我踩过SQLAlchemy的坑以为bulk操作能提速压测后发现耗时还是线性增长。追求极致性能时直接走原生SQL最可靠。6.4 一个可以被复制的检查清单最后给一份我每次做批量更新前都会过一遍的清单确认待更新数据的总行数和来源评估临时表空间。数据先落到临时表或Python内存避免边查边改。批量SQL的关键WHERE条件确保能走索引并用EXPLAIN验证。每批1000到5000行使用连接池复用连接。事务边界明确每批commit异常时rollback。更新上线前压测记录耗时和行数为后续调优留基准。这份清单看起来简单但每次都能挡住大部分低级问题。真正让批量更新变快的不是某一个炫技技巧而是把这些基础项全部做到位。最后再分享一个实际体会我最早也迷信executemany后来才发现它对UPDATE的优化有限。真正让我彻底告别逐条UPDATE的是“所有待更新数据先落到临时表最后JOIN一次写回”这个思路。你现在如果正被Python更新MySQL的慢性能折磨建议先按临时表JOIN改一版再回来对比耗时。10万行更新从半小时缩短到几十秒是很正常的结果前提是索引、事务、连接这三关都踩对。改完记得把耗时、批次大小、数据库版本记录下来下次遇到相似任务就能直接照着做。 
长河君 · 2026/10/6 8:59:32 · 阅读 1.2k Python高效更新MySQL：从逐条UPDATE到临时表JOIN的实践
上周帮运营团队做一个订单价格的批量修复他们拿来一张10万行的Excel需要按订单号把最新售价更新到线上库。运营同事最初用工具逐条生成UPDATE再执行跑了半小时进度还在原地。我接手后改用Python加MySQL的临时表关联更新全量几十秒跑完。这类“Python如何高效更新MySQL的数据”的问题我在批处理、数据修复、ETL同步项目里遇过太多次今天把经验和坑一次性捋清楚适合正在用Python做数据更新的开发、运维和数据同学参考。说明下面内容偏后端开发和数据修复视角如果你只是偶尔手工改几条数据直接在MySQL客户端里改更快没必要折腾批量方案。1. 先搞清楚“慢”在哪里从一条UPDATE到一万条UPDATE1.1 你最初可能就在用这种写法很多同学第一次写Python更新MySQL基本都是这个路子import pymysql conn pymysql.connect(hostlocalhost, userroot, password***, databasetest) data_list [(1001, 19.9), (1002, 29.9), ...] # 假设有几千条 for order_no, price in data_list: with conn.cursor() as cursor: cursor.execute(UPDATE order_data SET price%s WHERE order_no%s, (price, order_no)) conn.commit()这段代码逻辑上完全正确但性能非常差。不是Python解释器慢也不是MySQL单条UPDATE慢而是整个交互模式出了问题。我早期也这样写过后来用性能分析工具一测发现90%以上的时间都耗在“网络往返”和“事务提交”上。1.2 慢的三个关键原因网络往返、隐式事务、索引代价第一个原因是网络往返。Python程序通过TCP协议和MySQL服务端通信每调用一次cursor.execute就是一次请求加一次响应的完整来回。假设应用和数据库不在同一台机器每次往返至少0.5ms到2ms一万条UPDATE就产生10到20秒的纯网络开销。如果应用服务器和数据库跨机房这个数字还会放大。第二个原因是逐条提交事务。上面例子里每次execute后都调用conn.commit()InnoDB每提交一次事务都要把redo log刷到磁盘也就是fsync。一次fsync在机械盘上可能要几十毫秒。你以为是数据库更新慢其实是磁盘在扛着“每次提交都落盘”的压力。就算用的是固态盘一万次提交产生的刷盘开销依然很可观。第三个原因是索引维护。UPDATE修改字段时如果字段本身有索引或者WHERE条件要使用二级索引MySQL需要同步维护B树。单条操作时这个成本几乎感觉不到但循环一万次后随机IO和锁等待就会累积成瓶颈。再加上逐条UPDATE产生大量binlog主从复制环境里的同步延迟也会被拉高。1.3 一个简单的耗时模型帮你判断瓶颈优化之前先估算时间构成避免瞎调。下面是我对“一万条逐条UPDATE”场景的经验模型耗时项单次耗时一万次总耗时说明网络往返0.5~2ms5~20s应用服务器与数据库网络越远越严重事务提交commit2~20ms20~200s受磁盘刷盘策略影响机械盘更慢UPDATE执行0.1~1ms1~10s单条主键更新本身很快合计2~23ms26~230s慢主要来自提交和往返看到这个模型就能明白为什么“单条UPDATE很快循环UPDATE就爆炸”。优化的核心思路就一条减少网络往返次数减少事务提交次数让每一轮交互尽量处理更多行数据。这时候再去配上索引和合适的事务参数问题往往已经解决了大半。2. 批量更新的核心武器executemany与参数化SQL2.1 executemany到底做了什么网上很多文章说“用executemany批量执行能大幅提升性能”这句话必须拆开看。PyMySQL的executemany在遇到INSERT语句时会把多行数据拼成一条多值INSERT再执行这是实打实的优化。但当语句是UPDATE时PyMySQL并不会把多条UPDATE合并成一条它内部实际上还是循环调用execute跟你在Python里写for循环没有本质区别。我第一次踩这个坑是在一次价格批量修复任务里。当时把for循环换成executemany跑起来发现耗时几乎没有变化。查了驱动源码才明白executemany对UPDATE语句只是省掉了部分SQL解析和参数绑定的开销网络往返和事务提交一点没少。如果还保留着逐条commit那收益基本可以忽略。2.2 如何在UPDATE场景用一条SQL搞定多行既然executemany不能真正批量UPDATE就得自己构造批量语句。最直观的办法是把多条更新合并成一条CASE WHEN语句一次SQL执行更新多行UPDATE order_data SET price CASE order_no WHEN 1001 THEN 19.9 WHEN 1002 THEN 29.9 WHEN 1003 THEN 39.9 END WHERE order_no IN (1001, 1002, 1003);Python里按这个模板动态生成SQL和参数即可。注意参数占位符需要重复每个WHEN条件需要一个订单号占位符和一个价格占位符WHERE的IN列表还需要再来一遍订单号占位符。代码必须仔细对齐否则位置错一个数据就错一个。def batch_update_by_case(conn, table, key_field, value_field, data_list, batch_size1000): for start in range(0, len(data_list), batch_size): batch data_list[start:start batch_size] when_sql_parts [] keys [] params [] for key, value in batch: when_sql_parts.append(WHEN %s THEN %s) params.extend([key, value]) keys.append(key) in_placeholders ,.join([%s] * len(keys)) sql ( fUPDATE {table} SET {value_field} CASE {key_field} .join(when_sql_parts) f END WHERE {key_field} IN ({in_placeholders}) ) params.extend(keys) with conn.cursor() as cur: cur.execute(sql, params) conn.commit()这里有个坑CASE WHEN更新生成的SQL太长时可能超过max_allowed_packetMySQL会直接报Packet too large。所以函数里加了batch_size1000让每次拼接的SQL保持在安全范围。实际可按字段长短调整我一般控制在1000到2000行一批单批SQL字符串不超过1MB。2.3 临时表UPDATE JOIN最通用的批量更新方案CASE WHEN方式只适合更新单表、单字段、数据量可控的场景。如果数据来自Excel、接口或另一张表要更新多个字段或者数据量大到SQL拼接困难更好的方案是先把待更新数据灌入临时表再用UPDATE JOIN一次完成关联更新。def batch_update_by_tmp_table(conn, data_list): with conn.cursor() as cur: cur.execute(DROP TEMPORARY TABLE IF EXISTS tmp_order_price) cur.execute( CREATE TEMPORARY TABLE tmp_order_price ( order_no VARCHAR(32) PRIMARY KEY, price DECIMAL(10,2) ) ENGINEInnoDB ) # 对INSERT语句PyMySQL的executemany通常会拼成多值INSERT insert_sql INSERT INTO tmp_order_price (order_no, price) VALUES (%s, %s) cur.executemany(insert_sql, data_list) cur.execute( UPDATE order_data o JOIN tmp_order_price t ON o.order_no t.order_no SET o.price t.price ) conn.commit()临时表是session级别的连接断开会自动删除不污染线上库。建表时给关联字段加主键索引UPDATE JOIN就能走索引关联MySQL只需对原表按主键或二级索引逐行定位并更新避免每行都重新解析SQL。这个方案我用得最多能覆盖90%以上的批量更新场景。唯一额外成本是临时表写入时间但多值INSERT本身会减少网络往返整体性能远高于逐条UPDATE。3. 针对不同场景的高效更新方案3.1 场景A按唯一键更新同一张表价格、状态等离散字段如果你的需求是“已知一批订单号要把价格、状态或促销标记改成指定值”优先考虑CASE WHEN或临时表。我一般这样选数据量几千到几万且字段不超过两三个用CASE WHEN生成一条SQL。数据量超过5万或者字段很多用临时表UPDATE JOIN避免SQL过于臃肿。更新多个列时尽量合并到同一个CASE WHEN里。比如“改价格”和“改状态”一起做不要分成两条UPDATE分两次全表扫描。另外如果更新的字段本身是唯一索引或主键的一部分MySQL需要维护唯一索引耗时通常比普通字段更新更明显更要避免逐条循环。3.2 场景B从另一张表同步数据这类场景常见于数据中台同步、订单表从源库同步到分析库。假设你想把daily_report表里的价格同步到order_data表直接写UPDATE JOIN就能完成UPDATE order_data o JOIN daily_report d ON o.order_no d.order_no SET o.price d.price WHERE d.update_date 2025-01-01如果除了更新已有数据还要把新数据插入进来可以用INSERT ... ON DUPLICATE KEY UPDATE。MySQL 8.0.20之后不建议继续用老式的VALUES()函数推荐别名语法INSERT INTO order_data (order_no, price, status) VALUES (1001, 19.9, 1) AS new ON DUPLICATE KEY UPDATE price new.price, status new.status高版本新项目直接用别名写法别再用旧写法否则升级数据库时会看到deprecated警告未来迁移也麻烦。3.3 场景C海量数据分批更新与进度控制当需要更新的数据量上百万时一次UPDATE JOIN也可能因为锁范围过大导致主从延迟飙升。这时必须分批执行。分批的核心是控制每批次行数并在每批之间commit保留一个可继续的游标。相比OFFSET分页我更推荐键集分页keyset pagination记住上一批最后一条主键下一批只取主键大于它的数据。OFFSET会随着数据量增加越来越慢而keyset在有序主键下基本稳定。last_id 0 batch_size 2000 while True: rows fetch_rows( SELECT order_no, price FROM tmp_source WHERE id %s ORDER BY id LIMIT %s , (last_id, batch_size)) if not rows: break # 在这里做CASE WHEN或临时表批量更新 batch_update(rows) last_id rows[-1][id] conn.commit()每批commit非常重要。长事务会导致undo log膨胀、历史版本堆积一旦系统崩溃回滚时间会非常长。把长事务拆成多个短事务锁等待和恢复成本都会降低。批次大小建议1000到5000行具体还要看行宽和线上负载。3.4 场景D需要“先查后改”的复杂业务逻辑有些业务逻辑必须先把数据读进Python经过算法或外部接口计算后再写回MySQL比如调用外部风控后修改订单状态。这种场景最容易犯的错误是循环里先SELECT后UPDATE期间还夹着外部IO调用导致数据库连接长时间被占用。正确的做法是拆成三个阶段用一次性查询把待处理数据的主键和原值批量读出每次1000条。在Python里完成计算或外部调用把结果暂存在内存列表。回到数据库用CASE WHEN或临时表一次性写回。外部接口慢就不该连累数据库连接。阶段2可以控制并发量去做外部调用但别把更新拆成一条条发回数据库。我处理过每天几十万次外部调用的回写任务先攒批再统一更新整体时间比逐条“查询-计算-更新”快了一个数量级。4. 连接与事务配置高效更新的隐形因素4.1 autocommit与手动事务别让MySQL替你做保守选择连接配置里最容易忽略的是autocommit。PyMySQL默认autocommitFalse这意味着每条UPDATE如果不手动commit会一直处于同一个事务里。如果你循环里不commit最后统一commit一次事务会积累大量行锁如果每条都commit又会频繁fsync。两种极端都不可取。正确做法是手动控制事务边界一个批次开启一个事务批量更新后commit。conn pymysql.connect(hostlocalhost, userroot, password***, databasetest, autocommitFalse) try: for batch in batches: with conn.cursor() as cur: # 这里替换成你的批量UPDATE SQL cur.execute(update_sql, params) conn.commit() except Exception: conn.rollback() raise批次大小和commit时机直接相关。事务太小提交次数多事务太大锁管理和undo开销变大。我在生产环境里一般先压测几组批次大小再选择一个稳定值。有时候1000行一批和5000行一批差异并不大关键是不要一条一提交也不要一次性提交几十万行。4.2 连接池别再为每批次新建连接Python连接MySQLTCP握手和权限认证本身有成本。如果每执行一次批量任务就新建连接光建连时间可能达到几十甚至上百毫秒。批量更新场景下正确姿势是使用连接池复用连接。DBUtils 2.x配合PyMySQL可以这样写from dbutils.pooled_db import PooledDB import pymysql pool PooledDB( creatorpymysql, maxconnections20, mincached5, blockingTrue, hostlocalhost, userroot, password***, databasetest, autocommitFalse, ) conn pool.connection()连接池配合批量更新能省去反复建连的耗时也方便在多个批次间复用同一个会话。需要注意如果用了临时表方案一定要在同一连接内完成“建临时表→插数据→UPDATE→DROP”。连接被归还到池子后临时表是否还在取决于驱动的会话清理逻辑所以最好用with语句控制连接上下文。4.3 驱动与MySQL参数max_allowed_packet、innodb_flush_log_at_trx_commit批量SQL或多行INSERT很容易超过MySQL默认包大小。如果报Packet too large不要上来就改配置文件重启数据库可以先在会话级别调大SET SESSION max_allowed_packet 67108864;这样只对当前连接生效代码里每次连接初始化后执行一次即可。再聊一个全局参数innodb_flush_log_at_trx_commit。默认值是1表示每次事务提交都刷redo log到磁盘最安全但最慢。如果是批量修复数据且能接受极端情况下丢失最后1秒日志可以临时调成2减少刷盘次数以提升吞吐。但注意这是全局参数不能用SET SESSION调整需要DBA在维护窗口操作任务完成后必须改回1。生产环境别自己乱碰。另外MySQL 8.0中SHOW PROFILE虽然还能用但已有废弃趋势后续排查建议转向performance_schema。不过日常快速验证时SHOW PROFILE仍然够用。5. 真实项目中的性能调优与踩坑记录5.1 更新慢到锁表的排查链路我遇到过这样的问题用临时表UPDATE JOIN更新500万行跑了十几分钟还没结束。一查EXPLAIN发现临时表和原表关联时居然没走索引typeALL还出现了Using join buffer。原因是两表的order_no字段字符集不一致原表是utf8mb4_unicode_ci临时表建表时继承了session默认的排序规则。MySQL做关联时为了统一字符集无法直接使用原表索引只能临时转换再比较。排查链路我总结成四步先EXPLAIN看type是否变成ALL或出现Using join buffer。检查关联字段的字符集和排序规则是否一致。用SHOW CREATE TABLE对比原表和临时表字段定义。修正方式建临时表时显式指定CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci或者在插入临时表前先转换好字符串。这个坑非常隐蔽数据量小的时候感觉不到数据量一大就直接让你怀疑MySQL性能。5.2 索引失效函数包裹导致的坑另一个高频坑是WHERE条件里对索引字段使用函数。有人喜欢把日期字段转成字符串再比较UPDATE order_data SET status1 WHERE DATE(created_at) 2025-01-15这样写即使created_at有索引也用不上因为MySQL必须对每一行计算DATE()再比较。正确写法是范围条件UPDATE order_data SET status1 WHERE created_at 2025-01-15 00:00:00 AND created_at 2025-01-16 00:00:00批量更新通常隐含“影响大量行”一旦全表扫描加行锁数据库很容易被拖垮。所以批量更新上线前先INL有EXPLAIN确认SQL走索引再放到生产执行。5.3 死锁与事务过大怎么用分批和排序降低概率多个线程同时做批量更新时如果以不同顺序更新同一组行InnoDB的间隙锁和行锁容易互相等待最终触发死锁。MySQL检测到死锁会回滚其中一个事务Python脚本如果没做重试任务可能直接中断。我的土办法是所有批量更新之前先对数据按主键或唯一键排序。两个线程都在更新order_no1001和1002时如果都按1001、1002的顺序更新就不会交叉持有锁。排序后还要把事务长度控制在秒级不要让一个事务长时间握住几千上万个行锁。如果业务允许还可以给批量更新任务加一个分片字段多个worker各领一段不重叠的数据范围从源头避免锁冲突。5.4 验证效率用EXPLAIN与性能日志确认瓶颈优化做完了怎么证明有效除了记录Python层耗时我还会在MySQL里开profilingSET profiling 1; UPDATE order_data o JOIN tmp_order_price t ON o.order_no t.order_no SET o.price t.price; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;SHOW PROFILE会列出CPU、Sending data、executing等阶段的耗时。如果Sending data占了大头说明瓶颈在数据扫描如果Waiting for handler commit占了高位瓶颈就是提交和刷盘。这样比在Python里print时间更能定位问题。日常批量任务我也会在脚本里记录三段时间数据准备、批量更新执行、commit。分开统计后绝大多数“Python更新MySQL慢”都能明确落在网络往返、事务提交或SQL执行三个环节中。6. 给方案排个优先级什么时候用什么6.1 一张表直接选型我把这些年顺手且有效的方案整理成一张优先级表场景推荐方案原因少量数据几十条单条UPDATE 手动commit简单可控没必要拼SQL几千到几万条单表CASE WHEN一条SQL减少网络往返SQL简短几万条以上多字段临时表UPDATE JOIN关联走索引SQL可维护源表同步到目标表INSERT ... ON DUPLICATE KEY UPDATE一条语句完成插入和更新百万级分批临时表commit控制控制锁范围避免长事务先计算再回写攒批后统一更新不长期占用数据库连接优先级判断只看两个指标数据量以及更新数据是否有明确来源。有明确来源就优先临时表JOIN没有明确来源就拼CASE WHEN尽量别用for循环逐条UPDATE。6.2 什么时候不要用批量更新批量更新也不是万能的。以下场景我会刻意不用数据量很小手工改几条更直接。每条UPDATE的WHERE条件完全独立且中间掺杂大量业务判断强行合并反而让SQL不可维护。实时性要求极高的在线更新比如用户点击收藏立即改状态单条UPDATE本身够快攒批反而增加延迟。临时表方案要占用数据库临时空间如果更新数据大到tmpdir都无法容纳需要先评估磁盘和内存再决定。6.3 ORM的批量更新陷阱如果你用SQLAlchemy或Django ORM要小心它们的“批量更新”API底层不一定是一条SQL。很多ORM的bulk_update_mappings看似批量实际会在日志里打出N条UPDATE语句只是封装得比较“像批量”。我踩过SQLAlchemy的坑以为bulk操作能提速压测后发现耗时还是线性增长。追求极致性能时直接走原生SQL最可靠。6.4 一个可以被复制的检查清单最后给一份我每次做批量更新前都会过一遍的清单确认待更新数据的总行数和来源评估临时表空间。数据先落到临时表或Python内存避免边查边改。批量SQL的关键WHERE条件确保能走索引并用EXPLAIN验证。每批1000到5000行使用连接池复用连接。事务边界明确每批commit异常时rollback。更新上线前压测记录耗时和行数为后续调优留基准。这份清单看起来简单但每次都能挡住大部分低级问题。真正让批量更新变快的不是某一个炫技技巧而是把这些基础项全部做到位。最后再分享一个实际体会我最早也迷信executemany后来才发现它对UPDATE的优化有限。真正让我彻底告别逐条UPDATE的是“所有待更新数据先落到临时表最后JOIN一次写回”这个思路。你现在如果正被Python更新MySQL的慢性能折磨建议先按临时表JOIN改一版再回来对比耗时。10万行更新从半小时缩短到几十秒是很正常的结果前提是索引、事务、连接这三关都踩对。改完记得把耗时、批次大小、数据库版本记录下来下次遇到相似任务就能直接照着做。