写博客这么久了经常有人问我“MySQL脚本这东西有啥好讲的不就是写几条SQL吗”说实话没真正在生产环境上吃过亏的人很难理解为什么一个看似简单的“mysql数据脚本”能让后端同学凌晨三点爬起来救火。我自己就是踩过坑之后才慢慢总结出一套写法的。今天这篇就围绕数据脚本这个主题聊聊怎么写出一套能直接落地、可追溯、可重跑、可回滚的MySQL数据脚本内容包括脚本设计思路、SQL细节、完整实操案例和几个高频问题的排查方法。适合DBA、后端开发、数据分析师参考也适合刚入行想系统了解数据脚本写法的同学。1. 内容整体设计与思路拆解1.1 先分清脚本的四种用途MySQL数据脚本这个称呼其实涵盖了好几种用途不同用途的脚本对正确性、性能的要求完全不一样。我习惯先按需求把脚本分成四类再决定怎么动手。第一类是数据初始化脚本。新环境部署、新库上线的时候需要把表结构、基础字典数据、初始配置一次性灌进去。这类脚本的特点是执行频率低、数据量不算大但表结构变化频繁经常要跟着版本走。它最怕的是重复执行报错所以建表和插入数据都要做幂等处理。第二类是数据清洗脚本。业务数据里总有脏数据比如历史库里的手机号缺区号、渠道字段大小写不统一、重复标签需要合并。这类脚本面对的是存量数据核心同样是幂等但更强调操作范围的精准必须用WHERE条件把影响行数锁死不允许出现无边界更新。第三类是数据归档和迁移脚本。线上表越来越大要把几个月前的数据搬到归档库或者按新结构做数据重组。这类脚本对性能要求最高因为直接跑在线库上稍微不注意就能把业务拖死。归档脚本的核心矛盾是“数据量巨大”和“影响业务最小化”必须分批处理。第四类是日常跑批脚本。比如每天凌晨统计前一天的数据生成报表这类脚本讲究稳定、可重跑、有完整日志。和清洗脚本相比跑批脚本往往还会涉及多张表的联动计算逻辑更复杂对调度和监控的要求更高。把用途分清楚之后很多决策就简单了。初始化脚本可以写得直接一点归档脚本必须严格分批、控事务、留日志清洗脚本要反复强调条件判断。1.2 常见的数据脚本翻车原因很多数据脚本的问题不是SQL写错了而是工程化做得太差。我见过不少生产事故总结下来是这么几类。没有幂等设计。同一份脚本因为网络原因、人工重跑在同一个库上执行了两遍于是重复数据出现、累计字段翻倍。脚本如果不能保证重复执行结果一致就等于埋了一颗随时会炸的雷。没有日志。脚本跑完就是跑完了跑完之后没人知道处理了多少行、花了多长时间、有没有报错。等三天后业务方反馈数据对不上只能靠猜问题定位成本极高。没有事务边界。整个脚本几百行SQL包在一个大事务里跑得慢不说中途报错还会把所有操作全部回滚严重影响业务。或者反过来每条语句自动提交数据更新到一半失败留下了永远说不清楚的半成品状态。没有回滚方案。脚本上线前根本没想过失败了怎么办结果线上执行出问题只能手动一条一条恢复越恢复越乱。没有环境差异兼容。开发环境、测试环境、生产环境的表结构经常有一点差异脚本里没做兼容处理拿到另一个环境直接报错。这些问题的根源都是把数据脚本当成临时工具而不是需要长期维护的代码。数据脚本写完之后至少要活几个月甚至几年只要不是一次性的DELETE就应该用工程化的方式去写。1.3 一套可复用的脚本框架我习惯给每个正式的数据脚本都套一个固定框架不管多简单。这个框架包含六个部分。第一部分是脚本头注释。写明脚本用途、目标环境、执行权限要求、预计影响行数、是否需要业务停写、执行窗口建议、编写人和维护人。这部分不是废话几个月后有人接手你的脚本靠的就是这些信息。第二部分是参数与变量定义。所有需要人工输入的路径、时间、阈值都集中定义在文件头部不分散写在SQL中间方便执行前快速调整。第三部分是环境与权限自检。脚本开头先确认当前连接的库、版本、是否开启事务、是否有必要权限不符合条件直接退出。很多线上事故其实是一条本该被前置检查拦下来的SQL硬跑出来的。第四部分是业务逻辑区。按顺序执行没有副作用的查询、有副作用的更新、需要分批的循环。每个阶段之间留日志输出。第五部分是日志与统计信息。每完成一个阶段打印进度结束时输出各类影响行数和耗时。第六部分是异常处理与退出码。捕获SQL异常、事务回滚、返回非零退出码方便上层调度系统感知失败。有了这个框架脚本的可维护性会好很多。后面所有案例都按这个框架来写。2. 核心细节解析与实操要点2.1 事务与锁边界划在哪不少同学写数据脚本习惯直接把SQL往mysql客户端里一贴让它一条一条自动提交。这在数据量小的时候没感觉数据量一大问题就来了。MySQL默认是自动提交模式每条语句单独一个事务。如果一条UPDATE语句要更新几百万行实际执行时InnoDB会拿着大量行锁期间所有相关表的写入都会被阻塞。更麻烦的是一旦中途某一行触发了错误前面的更新已经提交后面的更新全部失败数据就处于改了一半的状态。所以写数据脚本尤其是有UPDATE、DELETE、INSERT的脚本一定要自己控制事务边界。我的做法是按照“业务上不可分割的单元”来画事务而不是把整个脚本画成一个事务。比如一个归档脚本核心流程是备份、归档、清理那我会把备份放一个事务归档放一个事务清理放一个事务每个事务执行前都记录日志。有人会问那中途失败怎么办这时候就靠日志了。因为事务被拆分失败之后可以定位到是哪个阶段挂了然后从那个阶段重新执行。只要脚本本身是幂等的重跑就没有副作用。我经常说数据脚本的设计目标不是永远不出错而是出错之后能在几分钟内安全恢复。2.2 幂等重跑一万遍也不怕幂等是数据脚本最容易忽视、又最容易翻车的设计点。所谓幂等就是同一份脚本无论执行一次、两次还是十次最终的数据结果都是一样的。设计幂等可以从几个层面入手。INSERT场景。如果业务上有唯一键优先用INSERT ... ON DUPLICATE KEY UPDATE或者先做SELECT判断再决定是否插入。INSERT IGNORE也可以但要注意它会把其他类型的错误也吞掉不建议滥用。举个例子初始化字典表的时候我几乎都用ON DUPLICATE KEY UPDATE来保证无论跑多少遍字典数据都是同一套。UPDATE场景。很多更新操作实际上是“把A字段的值按规则变为B”这类操作天然幂等因为结果是同样的值。怕的是那种“在原有值基础上累加”的写法比如把统计字段变成统计字段加1。一旦脚本重跑数值就会翻倍。应对方式是在脚本开头先做状态判断或者用条件UPDATE只更新符合特定标记值的行。DELETE场景。删除本身是幂等的删过了再删不影响结果。但要注意删除之前必须先确认数据已经完整备份并且有条件限制绝不能出现没有WHERE的DELETE。给表加防重字段。有些场景我会在表里加一个batch_id字段每次跑批都生成一个唯一批次号重跑时先删除同批次数据再重新插入。这种方式在数据跑批脚本里特别实用牺牲一点存储空间换来极大的安全性。2.3 大批量更新的分批处理在线库上跑一条UPDATE影响上百万行即使用了索引也会因为锁范围太大造成长时间阻塞。我在生产上处理大批量更新基本都走分批策略。分批的方式有三种常见思路。按主键范围切分。先查出最大值和最小值然后按固定步长切段每段一个UPDATE。这种方式适合ID分布比较均匀的表。比如主键从1到一千万每批处理ID在1到10000之间的数据循环下来就分成了一千批。按时间窗口切分。归档类脚本最常见的做法按天或者按小时批量处理比如每次处理某一天的数据一天一天向前推。用游标和存储过程。MySQL的存储过程里可以用游标循环每取一批数据就提交一次事务。不过存储过程调试起来麻烦逻辑复杂之后难维护我更倾向于把循环写在脚本语言里比如用Python、Go或者Shell逐批下发SQL。分批粒度怎么定没有一个绝对标准我一般看表的行锁竞争情况和服务器负载。如果表不大、写入频率不高每批5000到10000行比较稳妥如果表是热点表每批1000到2000行都行宁小勿大。核心原则是每一批SQL的执行时间控制在几秒内别让自己的脚本变成慢查询制造机。2.4 动态SQL、预处理与变量MySQL的SQL脚本里经常需要动态拼SQL比如根据传入的时间参数决定归档到哪张表或者根据环境不同调整过滤条件。直接用字符串拼接很危险也不利于性能。此时可以用PREPARE和EXECUTE配合用户变量做动态SQL。具体写法大致如下SET target_date 2025-01-01; SET sql CONCAT( INSERT INTO archive_, DATE_FORMAT(target_date, %Y), SELECT * FROM business_table WHERE create_time ? ); PREPARE stmt FROM sql; EXECUTE stmt USING target_date; DEALLOCATE PREPARE stmt;这段脚本的意思是先定义目标日期变量然后拼接归档表的表名再用PREPARE预编译SQL并执行。好处是表名、库名可以动态决定同时执行参数通过USING绑定避免拼接注入漏洞。使用PREPARE时要注意两点。第一PREPARE的对象在MySQL连接关闭或者手动DEALLOCATE之前一直占用服务器端内存用完一定要释放。第二如果SQL语句非常复杂预编译的开销可能大于直接执行但多数数据脚本场景下影响不大。如果希望在Shell脚本里实现类似逻辑可以直接把变量通过命令行传给mysql客户端mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -D$DB_NAME \ -e CALL archive_data($TARGET_DATE);这种方式的思路是让存储过程接收参数逻辑集中在数据库端调用端只负责传参。适合业务稳定、不需要频繁改逻辑的场景。两种方式没有绝对优劣关键看团队运维习惯和项目复杂度。2.5 索引与执行计划脚本快慢的关键数据脚本的很多性能问题追到根上都是索引没走对。写脚本之前我习惯先用EXPLAIN把每条涉及查询和更新的SQL都过一遍。EXPLAIN里我最关心几列type有没有达到range或refkey用到了哪个索引rows估算扫描多少行extra里面有没有Using filesort和Using temporary。如果一条UPDATE语句的WHERE条件没有索引MySQL只能全表扫描扫描行数可能上亿光这一条就够把库拖垮。写数据脚本有几个索引上的实用建议。第一UPDATE和DELETE的WHERE条件字段必须有索引最好配合主键使用。比如按时间清理数据那create_time字段必须有索引并且最好结合主键区间一起过滤。第二不要在索引列上做函数运算。比如WHERE DATE(create_time) 2025-01-01这样会导致索引失效应该写成WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00。第三JOIN操作时驱动表和被驱动表的连接字段要都有索引。数据清洗脚本经常要自连接找重复数据连接字段没索引会慢得让人崩溃。第四大批量读取的时候尽量用覆盖索引让查询只在索引树上完成减少回表开销。比如需要统计标签表中某个标签的数量只需要count(tag_code)的话就把索引建在(tag_code, member_id)上。2.6 临时表和存储过程怎么选数据清洗和汇总脚本里经常需要中间结果。把中间结果存到哪里也是值得花心思的点。MySQL提供CREATE TEMPORARY TABLE语法可以在连接内创建临时表。临时表的生命周期只属于当前会话连接断开自动销毁不会污染正式库。我用它来保存清洗后的中间数据、分批处理的ID清单、比对用的一致性快照。临时表的好处是不需要担心后续清理问题缺点是如果单次会话里数据量太大占用的内存和磁盘临时空间会比较高要留意tmp_table_size和max_heap_table_size参数。存储过程的选择要慎重。存储过程能把复杂逻辑封装在数据库内业务方只需调用一个名字网络交互少。但它有几个潜在问题版本管理困难MySQL的存储过程不像代码文件那样方便Git对比调试不方便出问题之后只能靠日志输出定位语法也比较古老写惯了现代编程语言的人会觉得别扭。我的个人偏好是简单逻辑、需要数据库内循环执行的用存储过程逻辑复杂、需要和外部系统联动的脚本尽量用Python或Shell调度SQL负责单条操作流程控制放在代码里。这样既保留了数据库的能力又让脚本本身更好维护。3. 实操过程与核心环节实现3.1 动手前的三件事每次写数据脚本之前我真的建议你先花十几分钟做三件事能省掉后面很多麻烦。第一件摸清表结构。用DESC确认字段名和类型用SHOW CREATE TABLE确认索引、分区、字符集、外键。别靠记忆写字段名字段名字写错一个脚本运行到一半才报错是最亏的。第二件确认数据量。用COUNT(*)、MAX、MIN确认总行数和范围这样才能决定批次大小和执行时间。数据量大意味着脚本要做分批否则可能把线上的慢查询池直接打满。第三件准备备份和回滚方案。对生产库来说执行前必须有备份。小表直接CREATE TABLE ... AS SELECT备份大表可以考虑逻辑导出或者基于binlog的回退方案至少也要把涉及的行先备份到一张影子表。这三件事做完写脚本的时候心里就有底了。后面所有案例都建立在这个准备基础上。3.2 数据初始化脚本案例下面用一个模拟项目X的初始化脚本作为例子演示基础框架的样子。假设要新建一个会员标签系统脚本目标是一次性把基础表和字典表建好并灌入初始状态数据。-- -- 初始化脚本会员标签系统基础表 -- 目标环境生产 -- 预计影响新建5张表写入200行字典数据 -- 执行窗口无业务时段建议凌晨0点至6点 -- 重跑策略可重复执行建表IF NOT EXISTS 幂等插入 -- 维护人某开发者 -- SET batch_id REPLACE(UUID(), -, ); SELECT CONCAT(batch_id , batch_id) AS running_info; -- 1. 建立基础用户表 CREATE TABLE IF NOT EXISTS t_member ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, member_no VARCHAR(32) NOT NULL COMMENT 会员编号, member_name VARCHAR(64) NOT NULL DEFAULT COMMENT 会员名称, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0-正常 1-冻结, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_member_no (member_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT会员主表; -- 2. 建立会员标签表 CREATE TABLE IF NOT EXISTS t_member_tag ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, member_id BIGINT UNSIGNED NOT NULL, tag_code VARCHAR(32) NOT NULL COMMENT 标签编码, tag_value VARCHAR(128) NOT NULL DEFAULT COMMENT 标签值, source_system VARCHAR(32) NOT NULL DEFAULT COMMENT 来源系统, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_member_id (member_id), UNIQUE KEY uk_member_tag (member_id, tag_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT会员标签表; -- 3. 初始化字典表 CREATE TABLE IF NOT EXISTS t_dict_tag_code ( tag_code VARCHAR(32) NOT NULL, tag_name VARCHAR(64) NOT NULL, sort_no INT NOT NULL DEFAULT 0, PRIMARY KEY (tag_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT标签字典表; -- 4. 写入字典数据幂等处理 INSERT INTO t_dict_tag_code (tag_code, tag_name, sort_no) VALUES (level, 会员等级, 1), (channel, 注册渠道, 2), (preference, 兴趣偏好, 3) ON DUPLICATE KEY UPDATE tag_name VALUES(tag_name), sort_no VALUES(sort_no); SELECT COUNT(*) AS dict_count_after FROM t_dict_tag_code; -- 5. 初始化状态跟踪表 CREATE TABLE IF NOT EXISTS t_script_exec_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, batch_id VARCHAR(64) NOT NULL, script_name VARCHAR(128) NOT NULL, exec_start DATETIME NOT NULL, exec_end DATETIME, affected_rows INT NOT NULL DEFAULT 0, status VARCHAR(16) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT脚本执行日志表; INSERT INTO t_script_exec_log (batch_id, script_name, exec_start, status) VALUES (batch_id, init_member_tag_system, NOW(), RUNNING);这段脚本有几个典型的处理细节。建表都用IF NOT EXISTS字典数据用ON DUPLICATE KEY UPDATE这样同一份脚本重复执行不会报错。还建了一张t_script_exec_log用来记录每次脚本运行的批次号、起止时间、受影响行数和状态。这张日志表对初始化脚本来说作用很大后续排查问题、确认版本都能用上。我还习惯在初始化脚本最后输出一次校验结果比如字典表行数、用户表结构是否完整。如果初始化脚本是平台部署的一部分这些校验输出能帮助后续的自动化流程判断是否继续执行。3.3 数据清洗脚本案例数据清洗脚本的典型场景是修正存量脏数据。继续用会员标签系统举例。假设发现渠道标签值存在前后空格、大小写混用、空值这三种情况现在要修复。-- -- 清洗脚本渠道标签值规范化 -- 目标环境生产 -- 预计影响约12万行 -- 执行窗口业务低峰期 -- 重跑策略可重复执行 -- 维护人某开发者 -- SET batch_id REPLACE(UUID(), -, ); SET processed_rows 0; -- 1. 去掉前后空格 UPDATE t_member_tag SET tag_value TRIM(tag_value), updated_at NOW() WHERE tag_code channel AND tag_value TRIM(tag_value); -- 2. 统一小写 UPDATE t_member_tag SET tag_value LOWER(tag_value), updated_at NOW() WHERE tag_code channel AND tag_value REGEXP [A-Z]; -- 3. 空值置为默认渠道 UPDATE t_member_tag SET tag_value unknown, updated_at NOW() WHERE tag_code channel AND (tag_value IS NULL OR tag_value OR tag_value unknown);这个脚本看起来简单实际上有两个关键点。第一个关键点是WHERE条件写得很精确。比如去掉空格的那条语句WHERE里明确写了tag_value TRIM(tag_value)只有存在空格的行才会被更新。这保证了脚本重复执行时第二批匹配不到任何行UPDATE影响行数为0不会反复更新导致不必要的锁和日志量。第二个关键点是出现了清洗顺序。先去空格再转小写最后补默认值。如果顺序反过来先补默认值再去空格空值行的unknown可能又被误操作。清洗脚本的逻辑顺序是业务规则的直接体现写之前最好在草稿纸上把规则顺序列一遍。另一个高频清洗场景是去重。比如会员标签表因为历史bug出现了同member_id和tag_code重复的数据要去掉重复记录保留最早插入的一条。DELETE t1 FROM t_member_tag t1 JOIN t_member_tag t2 ON t1.member_id t2.member_id AND t1.tag_code t2.tag_code AND t1.id t2.id;这条SQL是典型的自连接删重复。保留id最小的记录把后续重复的行全部删除。执行之前一定要先备份相关表或者把待删除的数据先插入一张备份表。我在生产上执行这类删除从来都是先在测试环境跑一遍确认影响行数是预期范围再上线的。3.4 数据归档脚本案例现在看一个生产环境更常见、也更复杂的场景订单历史数据归档。假设业务订单表t_order已经积累了两年数据总量超过一亿行要把一年前的订单搬运到归档库并且从在线库清理掉。这类脚本我建议分三步走先备份、再归档、后清理。备份环节-- 假设已经确认归档范围为创建时间早于某日期 CREATE TABLE IF NOT EXISTS db_archive.t_order_backup_2024 AS SELECT * FROM db_business.t_order WHERE create_time 2024-01-01 00:00:00;这一步相当于把目标数据快速复制到归档库的备份表万一后续清理出现问题可以直接从备份表恢复。归档环节为避免一次性操作量太大按天分批循环处理SET calc_date DATE_SUB(CURDATE(), INTERVAL 365 DAY); WHILE calc_date 2024-01-01 DO SET sql CONCAT( INSERT INTO db_archive.t_order_archive SELECT * FROM db_business.t_order WHERE create_time , calc_date, 00:00:00, AND create_time DATE_ADD(, calc_date, , INTERVAL 1 DAY) ); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET sql2 CONCAT( DELETE FROM db_business.t_order WHERE create_time , calc_date, 00:00:00, AND create_time DATE_ADD(, calc_date, , INTERVAL 1 DAY) ); PREPARE stmt2 FROM sql2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2; COMMIT; SELECT CONCAT(archive done for , calc_date) AS progress; SET calc_date DATE_SUB(calc_date, INTERVAL 1 DAY); END WHILE;这段脚本用WHILE循环从目标日期开始每日倒推每天先插入归档表再删除在线库每个批次提交一次事务。如果担心DELETE太大可以再加LIMIT比如每次删除10000行配合循环多次删除。在真实项目里我不会直接把这个循环写进一条SQL而是用Shell脚本或Python脚本调度每条SQL单独执行这样即使某一天出了错也可以跳过已经成功的日期继续往后跑。执行器脚本大致长这样#!/bin/bash # 归档历史订单数据逐日执行 DB_HOST127.0.0.1 DB_USERscript_user DB_PASS这里是密码占位 DB_ARCHIVEdb_archive DB_BUSINESSdb_business START_DATE2024-01-01 END_DATE$(date -d -365 days %F) current$START_DATE while [[ $current $END_DATE ]]; do echo processing $current mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_BUSINESS \ -e CALL archive_one_day($current); if [ $? -eq 0 ]; then echo success $current else echo failed $current, stop exit 1 fi current$(date -d $current 1 day %F) done echo archive task finished 把业务逻辑收敛到存储过程archive_one_day里调用端只负责控制节奏和记录成败。这个方案的坏处是存储过程不好做版本管理好处是生产环境不需要安装额外的运行时只要MySQL在就能跑。如果项目组已经有Python环境我更推荐用Python脚本加重试机制管理起来灵活很多。3.5 定时任务让脚本自动跑起来有些数据脚本需要每天固定时间执行比如报表汇总、数据清理。MySQL自带的EVENT调度器可以完成这件事但要注意几个坑。先用SHOW VARIABLES LIKE event_scheduler检查事件调度器是否开启。如果没开需要设置SET GLOBAL event_scheduler ON;然后创建一个每日凌晨2点执行的报表事件CREATE EVENT IF NOT EXISTS ev_daily_report ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 02:00:00 ON COMPLETION PRESERVE ENABLE DO BEGIN CALL generate_daily_report(); END;使用EVENT的好处是不需要额外部署定时任务服务缺点是它依赖数据库实例长期在线而且如果事件执行时长超过调度间隔可能产生堆积。所以复杂脚本我更倾向用系统级crontab至少失败重试、日志留存这些能力都能交给系统工具。crontab的写法很直接0 2 * * * /home/scripts/run_daily_report.sh /var/log/mysql_script.log 21无论用哪种方式建议都加一个上次成功状态的落库记录。脚本执行完成时更新状态表监控任务每隔一段时间检查一下这个表有异常立刻报警。这点对数据脚本非常重要因为很多数据问题不是当天暴露的而是慢慢累积出来的。3.6 上线检查与回滚方案数据脚本上线前的检查流程我总结为四步试运行、影子核对、灰度执行、监控观察。试运行是在开发或测试环境把脚本完整跑一遍确认不会报错同时观察执行时间和影响行数是否符合预期。注意开发环境的表结构和生产环境可能有差异所以试运行只能证明逻辑通不能百分百证明生产环境也安全。影子核对是找一个业务低峰期在目标库的只读副本或者直接在一张影子表里模拟执行核对脚本产生的数据结果是否正确。比如归档脚本把归档SQL在副本上跑一遍对比归档出来的行数是否等于在线库目标数据量。灰度执行是指如果在生产库上执行先拿一小部分数据跑通比如先处理最近几天的数据确认无误后再放开到全部数据。对于归档类脚本灰度阶段尤其重要因为影响行数和表结构往往比预想的复杂。监控观察是执行完成后持续一段时间关注主从延迟、慢查询曲线、磁盘空间增长、业务接口错误率避免脚本执行完留下隐形问题。关于回滚我强调一点不要等到出事才写回滚脚本。写正数脚本的同时把回滚脚本也写完。回滚的逻辑就是把备份表的数据反向插回去把被更新的状态恢复成原值。哪怕最后用不上回滚脚本的存在也能让你在出问题时睡个安稳觉。4. 常见问题与排查技巧实录4.1 脚本执行慢到底卡在哪数据脚本运行慢绝大多数情况不是MySQL不给力而是SQL没有利用好索引或者锁等待严重。排查的时候我一般按下面顺序看。先用EXPLAIN看执行计划。确认有没有走索引、扫描行数是不是异常大、有没有使用临时文件或排序。EXPLAIN里出现Using filesort或Using temporary说明SQL可能需要优化。再看是否被锁阻塞。用SHOW PROCESSLIST看有没有大量状态为Waiting for table metadata lock的会话或者用SHOW ENGINE INNODB STATUS看最近的事务与锁信息。如果发现脚本一直在等锁多半是业务高峰期有人在写同一张表或者前面有一个大事务没提交。解决方式要么调整脚本执行窗口要么把事务拆更小要么和业务方协调避开高峰。最后看参数是否不对。比如max_allowed_packet太小会报Got packet bigger than max_allowed_packet bytesnet_write_timeout太小可能导致大查询被截断。这类参数优先级排序不高但遇到特定问题往往是一击即中。4.2 乱码问题从哪来乱码问题十有八九是字符集不匹配排查思路其实很清晰。MySQL的字符集是分层设置的服务器层、数据库层、表层、连接层各有一个。脚本里如果没显式设置连接字符集直接使用默认值可能出现UTF8MB4的表写入Latin1连接然后所有中文都变成了问号。解决方法是脚本连接之后第一条语句先执行SET NAMES utf8mb4再执行任何业务SQL。如果已经出了问题只能从源数据端重新导入或者重新执行脚本没有太好的修复空间。日常习惯是统一使用utf8mb4把数据库、表、字段全部设成这种字符集脚本连接层也统一设置如果跨库迁移先确认源库和目标库字段字符集是否一致不一致的话提前做转换。4.3 大批量脚本导致主从延迟归档和清理类脚本如果在主库上跑写入量瞬间变大从库的复制线程很容易跟不上于是主从延迟开始拉大。如果项目里做了读写分离业务读请求全部打到从库上延迟就意味着业务读到旧数据体验会非常差。缓解办法有几个方向。一是把脚本放在业务低峰期执行比如凌晨给从库足够时间追赶。二是缩小每批数据量拉长循环间隔比如每批删除5000行后sleep 1秒让主库写入压力平缓。三是在确实必要的情况下暂时把脚本涉及的表从读写分离路由中摘除等脚本跑完再切回来。还有个容易被忽视的点脚本里执行时间过长的事务会在事务内产生大量binlog但从库重放可能是单线程的所以延迟会成倍放大。减少单事务内的影响行数尽量让事务小而快对降低延迟非常有效。4.4 权限报错与最小化授权生产环境的数据脚本我强烈建议不使用root账号而是创建专用的脚本账号只授予执行所需的最小权限。这样即使脚本被误执行或者泄露影响面也有限。权限不足时常见的报错有Access denied for user、SELECT command denied、LOCK TABLES command denied等。排查思路很简单先看脚本用了哪个账号连接再看这个账号是否有对应库表权限最后确认是否缺少额外的全局权限。建账号的示例CREATE USER IF NOT EXISTS script_user% IDENTIFIED BY 这里替换成强密码; GRANT SELECT, INSERT, UPDATE, DELETE ON db_business.* TO script_user%; GRANT SELECT, INSERT, UPDATE, DELETE ON db_archive.* TO script_user%; GRANT EXECUTE ON db_business.* TO script_user%; FLUSH PRIVILEGES;别忘了如果脚本里有LOCK TABLES、CREATE TEMPORARY TABLE也要给对应权限。最好的办法是写完脚本后对照脚本用到的命令逐个授权别图省事一把梭。4.5 日志、监控与数据核对数据脚本跑完必须留下痕迹。我见过太多脚本没有日志运行出错了也不知道错在哪。这里分享一套我一直在用的轻量做法。第一在每个脚本里统一记录开始时间、结束时间、影响行数。最省事的方法就是借助前面提到的t_script_exec_log表脚本开始写一条RUNNING结束更新成SUCCESS或FAILED覆盖行数写进affected_rows字段。第二对关键数据量做前后对比。比如归档脚本执行前统计一下目标表最大时间、最小时间、总量执行后再统计一次确认归档范围和清理范围一致。这个对比结果直接打印出来留档用。第三把脚本执行状态接入监控。哪怕只是每分钟扫一次脚本日志表的定时任务发现连续两天没有成功记录就报警也能挽回大量问题。数据脚本的排障百分之八十的工作其实都在确认脚本到底跑没跑、跑到哪一步了、影响行数对不对。有了日志和监控这些都不需要靠猜。4.6 快速自查清单排查数据脚本问题的时候我经常对照下面这张表快速定位方向。症状常见原因初步排查命令或方法脚本跑得很慢索引失效、锁等待、扫描行数大EXPLAIN、SHOW PROCESSLIST执行报错权限不足、字段名错误、字符集不匹配SHOW CREATE TABLE、GRANT检查数据出现重复缺少唯一键、脚本非幂等检查表唯一约束、看脚本插入逻辑中文乱码连接字符集与表字符集不一致连接后执行SET NAMES utf8mb4主从延迟升高单事务过大、写入量集中拆分事务、降低批次行数回滚困难没有备份表、没有回滚脚本提前准备备份表和反向INSERT任务没跑事件调度器未开启、crontab没有执行权限查event_scheduler、查系统日志日志缺失脚本没有统一日志机制启用t_script_exec_log表这张表不是标准答案但能帮你快速缩小排查范围。遇到奇怪的问题先确认脚本有没有日志再确认执行计划和锁状态通常就能找到线索。我个人在实际操作中体会最深的一点是数据脚本不能只看它能跑更要关注重跑能不能安全、失败能不能定位、回滚能不能恢复。每次上线前多花十分钟写个回滚脚本、加一行日志比事后花两小时救火值太多了。最后再分享一个小技巧。脚本头部一定要写清楚这个脚本删了哪些表、改了多少行、最短执行间隔是多少越具体越好。几个月后有人接手这份脚本甚至就是几个月后的你自己都会感谢这行注释。数据脚本的维护功夫其实都花在那些别人看不见的细节上。