1. 为什么一说加字段我心里先咯噔一下1.1 一千万行到底是个什么概念先别急着算磁盘占用先说行数本身。一千万行数据放在OLTP系统里是一个非常微妙的量级它没有大到让你看一眼就放弃但也远没小到可以让你随手一条ALTER TABLE就完事。很多系统跑到这个量级时还处于业务蒸蒸日上、表结构三天两头要调的阶段而数据库负责人通常只有一个人可能是DBA也可能是后端兼着。这种情况下每次结构变更都像走钢丝。真正要命的不是那一千万行数据本身而是这张表背后的并发写入量。如果这是一张每天只有几百笔插入的流水表那就算直接ALTER TABLE锁几分钟影响也有限但如果这是一张订单表、用户行为表、计费表高峰期每秒几十上百的写入锁表哪怕只有几十秒线上就会堆起大量请求接着就是报警、超时、客诉。我见过一个很典型的案例业务方提需求说给用户表加一个crm_tag字段标记一下用户归属听起来人畜无害。结果执行完成后值班群炸了因为加字段那段时间所有涉及该表的写操作全部被阻塞积压了上万条慢SQL等到表重建完积压请求一拥而上又把数据库打到了CPU百分百。所以一千万行从来不是一道算术题而是一道风险题——你真正要评估的是变更窗口有多大影响面有多广。1.2 你以为的ALTER TABLE和实际的ALTER TABLE很多人刚接触MySQL时以为ALTER TABLE ADD COLUMN就是改一下表定义文件几毫秒完事。实际上在InnoDB的底层实现里往一张表加字段这件事远没有听起来那么轻量。MySQL 5.7及更早版本里如果你执行ALTER TABLE ADD COLUMN数据库可能选择的方式是新建一张结构变更后的临时表把原表的一千万行数据一行一行拷贝过去同时把拷贝期间产生的新写入同步进去最后删除原表、重命名临时表。这个过程也叫COPY算法。一千万行数据的拷贝不是瞬间完成的它取决于你的磁盘IO、CPU、缓冲池命中率少则几十秒多则十几分钟。在这个过程里写操作被阻塞读操作因为要服务拷贝进程性能也会明显下滑。更坑的是很多你以为的小动作会触发COPY。比如新字段没有默认值、或者字段加在中间某列的位置而不是末尾、或者表里已经有某些类型的索引约束优化器可能会放弃INPLACE算法退化成COPY。很多人踩坑的原因就是没搞清楚自己执行的ALTER到底走了哪条路真锁了表才追悔莫及。2. 摸清家底不同数据库、不同版本加字段的代价差着数量级2.1 MySQL老版本默认行为是重建整张表在MySQL 5.6之前加字段基本就是COPY这没什么好说的那个时代做表结构变更标准动作就是停服或者接受长时间锁表。从5.6开始InnoDB支持了ALGORITHMINPLACE某些结构变更可以在不重建整张表的情况下完成但ADD COLUMN这个操作在5.6/5.7里能不能走INPLACE取决于非常苛刻的条件。MySQL官方文档对ALGORITHM参数的解释很官方这里我用人话拆一下。ALTER TABLE时你可以显式指定算法和锁级别ALTER TABLE user_info ADD COLUMN crm_tag VARCHAR(32) NULL DEFAULT NULL, ALGORITHMINPLACE, LOCKNONE;ALGORITHMINPLACE的意思是能不重建表就别重建表LOCKNONE的意思是允许并发的读和写。这两个参数如果同时满足数据库会在不阻塞DML的前提下完成加字段。但问题来了不是你想指定就能成功。如果InnoDB发现条件不满足它不会跟你说我又默默用了COPY而是直接报错告诉你当前操作必须用COPY或者必须锁表。所以很多线上事故恰恰来自那些没写这两个参数的ALTER数据库自己选了最稳妥——或者说最保守——的方式去执行长锁表就发生了。有一个经常被忽略的点即便走了INPLACEMySQL 5.7的ADD COLUMN依然有可能需要短暂地拿到表的EXCLUSIVE锁来做元数据更新虽然这个时间通常极短但在大事务并发的情况下这段短暂锁等待也可能演变成严重的MDL排队。另外如果你的字段加在非末尾位置比如ADD COLUMN age INT AFTER name5.7里这类操作代价会明显升高很多版本里实际还是要重建数据页。2.2 MySQL 8.0的INSTANT秒加字段但限制也不少到了MySQL 8.0.12之后情况有了本质变化。官方引入了INSTANT算法加字段真的变成了只修改元数据级别的操作不再动数据文件。我实测过一张两千万行的表执行ALTER TABLE ... ADD COLUMN ... , ALGORITHMINSTANT返回结果用时0.0几秒基本就是改一下字典。但别高兴太早INSTANT的限制清单同样很长只能把新字段加在表的末尾不能在任意位置插入新字段必须允许为NULL或者带有非易变的默认值不支持压缩表不支持全文索引表不支持某些数据类型每张表使用INSTANT方式加字段的版本历史记录是有限制的累计达到一定次数默认64次后需要一次正常的REBUILD来重置这个计数。所以如果你的生产库已经升级到MySQL 8.0且字段逻辑上允许放在末尾那这个问题直接变成了低风险操作。但如果你还在5.7或者被迫把字段插在中间那依然得走稳妥路线。2.3 PostgreSQL、SQL Server这些库呢很多团队其实是多数据库共存的这里顺手说一嘴其他库免得大家误以为全世界的数据库加字段都跟MySQL老版本一样吓人。PostgreSQL对ADD COLUMN的处理一直很友好。如果新字段有常量默认值PG从9.x开始就走了只改元数据的优化路径一千万行的表加个默认常量字段毫秒级完成不锁读写。但注意如果默认值不是常量而是函数比如DEFAULT gen_random_uuid()或者字段带NOT NULL但没有默认值PG依然可能触发全表重写一千万行照样会堵。SQL Server的情况介乎两者之间。2012年之前ADD COLUMN带默认值也是一个相对轻量的操作只改元数据但2012之后某个版本起因为统一了在线索引重建的基础设施情况变得复杂。总的说来SQL Server加字段通常不会像MySQL 5.7那样动不动重建整个表但如果表上有大量非聚集索引、或者启用了某些特殊功能如压缩变更代价也会上升。真正危险的反而是你没有权限就提了工单然后在变更窗口里被阻塞。Oracle的话11g往后的ADD COLUMN配合DEFAULT值基本都可以只更新数据字典没什么好慌的。但如果你用的是国内某些基于老版本MySQL的云数据库分支或者某些分布式中间件那一切都得以实际文档和测试为准。2.4 选型判断先回答四个问题我每次接到给大表加字段的需求都会先让本方回答四个问题答案不同操作路径完全不同数据库引擎和版本是什么如果MySQL版本低于5.6基本就别想在线方案了必须走工具或者维护窗口如果是8.0.12以上且字段能放末尾INSTANT是首选。新字段能不能保证为空或带默认值如果业务逻辑要求非空且无默认值几乎所有方案的难度都会上一个台阶因为这既影响DDL算法选择也影响存量数据和应用兼容性。线上对锁的容忍度是多少有SLA要求的核心链路连几秒钟的写阻塞都接受不了非核心报表库锁几分钟也许能接受。有没有充足的运维窗口和回滚空间低峰期到底有多低是不是真的没人写去监控里拉一下真实数据别只看业务方说这会儿没人。这四个问题答完敢不敢加就变成了该怎么加事情就进入可控流程了。3. 老版本MySQL安全加字段的两把利器如果你已经确认生产环境是MySQL 5.7或者更早又没法接受锁表那就得请出两位老同志pt-online-schema-change简称pt-osc和gh-ost。两者思路不太一样但目标一致在不阻塞正常读写的情况下把表结构变更加载完成。3.1 pt-online-schema-change的工作原理与实战命令pt-osc是Percona Toolkit里最常用的工具之一它的工作流程可以概括为三步建影子表、加触发器、分批拷贝数据。具体是这个逻辑工具会先根据原表的DDL创建一张结构一致的空表名为_原表名_new然后在原表上创建三个触发器分别捕获INSERT、UPDATE、DELETE操作把变更实时同步到影子表接着按主键或唯一键分批把原表数据拷贝到影子表每批一个事务减少对资源的占用最后数据拷完后在极短时间内用RENAME TABLE把影子表替换原表并删除旧表和触发器。这里有个细节值得展开触发器的存在意味着从触发器创建到表切换完成这段时间原表上每一次DMLMySQL还要额外执行一次触发器逻辑本质上是双写。这就导致pt-osc在高写入负载下会放大写放大如果你的表每秒有几千次写入用pt-osc需要非常克制地控制拷贝速率否则数据库的负载会明显上升。常用命令长这样pt-online-schema-change \ hlocalhost,uadmin,Dtest,tuser_info \ --alter ADD COLUMN crm_tag VARCHAR(32) NULL COMMENT 用户标签 \ --chunk-size1000 \ --max-lag5 \ --critical-loadThreads_running50 \ --max-loadThreads_running20 \ --alter-foreign-keys-methodauto \ --execute参数上我特别提醒几个--chunk-size控制每批拷贝的行数太小了慢太大了容易长时间锁一批--max-lag是控制从库延迟的主从延迟超过设定值工具会暂停拷贝给复制追平的时间--critical-load是硬性保护超过阈值工具直接退出避免从库延迟失控或者主库负载过高。我的习惯是首次执行时把这些阈值设得保守一些确认负载可控后再提速。还有一点容易被忽略pt-osc要求原表必须有主键或唯一键否则无法分批拷贝。如果你的那张一千万行的大表连主键都没有那首先该想的不是怎么加字段而是怎么把主键补上。3.2 gh-ost靠binlog偷学变更的另一个选择gh-ost是GitHub开源的工具全称是GitHub Online Schema Transmogrifier。它和pt-osc最大的区别是不依赖触发器。gh-ost的思路是把自己伪装成一个MySQL从库连接到主库上拉取binlog。它会创建一张影子表然后把原表的存量数据分批拷贝过去同时通过解析binlog把拷贝期间产生的增量变更应用到影子表。整个过程不创建触发器对原表的额外开销非常低尤其适合写入量很大的场景。GitHub当初开发这个工具就是因为pt-osc的触发器方案在超大规模写入下扛不住。gh-ost的使用命令大概是这样的gh-ost \ --host127.0.0.1 \ --useradmin \ --passwordxxx \ --databasetest \ --tableuser_info \ --alterADD COLUMN crm_tag VARCHAR(32) NULL COMMENT 用户标签 \ --chunk-size1000 \ --max-lag-milliseconds5000 \ --switch-to-rbr \ --execute注意一个前置条件gh-ost要求MySQL开启binlog且binlog格式必须是ROW。因为只有ROW格式才能完整解析出每一行变更的前后镜像。如果你的库还在用STATEMENT格式得先切换binlog格式这本身又是一个需要评估的变更。gh-ost还有个很贴心的小功能支持先测试后执行。你可以先启动一个--migrate-on-test的模式工具会把整个流程走到切换前一步然后自动停下来让你确认影子表数据一致后再真正切换。我第一次用gh-ost给一个大表加字段时就是靠这个功能培养信心的。3.3 两把利器怎么选我的经验是写入流量大的核心表优先选gh-ost写入流量一般、且团队对Percona Toolkit更熟的表选pt-osc就行。pt-osc的资料更多参数更丰富排查问题的帖子也好找gh-ost理论上更优雅但binlog格式限制和它那套假从库机制对运维理解能力要求稍高。再有就是不管用哪种工具都必须先在一台从库或者测试环境上演练一遍确认工具版本、MySQL版本、表结构三者兼容。工具不是魔法它只是在帮你管理风险风险仍然存在。4. 一次千万级表加字段的完整执行方案前面讲了一堆理论现在我把一次真实可复用的执行方案完整写出来。假设场景MySQL 5.7核心业务表数据量1200万行高峰期每秒约200次写入目标是在不停服的情况下新增一个可空字段。4.1 执行前48小时检查清单这个阶段做的事情决定了变更窗口当天是十分钟收工还是折腾一整夜。第一确认表的基础信息。用SHOW TABLE STATUS看表行数和数据大小用SHOW INDEX确认主键类型。如果主键是自增IDpt-osc和gh-ost都会很开心如果主键是varchar字符串甚至是大文本分批拷贝的效率会打折工具对每批的排序和定位都会变慢。第二确认从库状态。执行SHOW SLAVE STATUS确认Seconds_Behind_Master长期为零而不是长期飘红。如果从库本来就延迟十几秒那工具设置的--max-lag会频繁触发暂停拷贝进度会非常慢。第三检查磁盘空间。在线加字段期间MySQL需要额外的空间来存放影子表。一个常见的估算方法是先看原表在磁盘上实际占多大空间然后再预留至少1.5倍的空间。如果你的磁盘剩余空间连原表的0.5倍都没有工具大概率会在拷贝过程中因为磁盘写满而失败。第四确认binlog保留时长和磁盘容量。gh-ost要读binlog如果binlog保留太短工具启动后拉不到正确的位置也会出问题。第五和应用团队对齐。新字段的业务语义是什么存量数据要不要打标应用代码里如果已经写了INSERT语句指定列新增可空字段不会炸但如果有代码用了INSERT INTO t VALUES (...)这种不写列名的写法一旦表结构新增字段插入数据的位置就会错位。这种问题在变更完成后才会爆发查起来极其费劲。4.2 变更当天的操作顺序我的标准流程是这样的按顺序执行第一步记录基线。变更前先记录主库的Threads_running、Threads_connected、QPS、主从延迟、磁盘使用率作为后续对比的基线。第二步在从库上先做一次完整的表结构变更演练。这个步骤很多人跳过我强烈建议保留。哪怕主库是真的没有可替换的测试环境从库上一张表做演练的成本也很低但能帮你发现一堆版本兼容性的坑。第三步正式在主库跑工具。以pt-osc为例先不要直接--execute而是先不带这个参数跑一遍工具会进入打印模式把要执行的SQL、预估的行数、要创建的触发器都列出来确认无误后再加--execute真正执行。第四步盯着监控看两个点主库的Threads_running是否超过你设的critical-load阈值从库的延迟是否触发max-lag。如果一切正常就让工具跑完。第五步切换完成后立刻做几件事确认新表结构SHOW CREATE TABLE确认行数一致确认原表上的索引完整迁移到了新表确认主键自增值没有错乱。如果表上有外键还需要额外确认外键关联关系。这里必须说明一个工具切换阶段的关键点pt-osc最后的RENAME TABLE是在一瞬间完成的这个操作本身需要获取一定的锁正常情况耗时极小。但如果你在切换前有未结束的长事务RENAME就有风险。所以我一般会在切换前用SHOW PROCESSLIST确认没有长时间运行的事务必要时等它们结束再让工具继续。4.3 变更后的验证与回滚预案变更完成不代表流程结束。接下来30分钟到1小时是观察窗口。验证两层数据层和应用层。数据层要对比原表和影子表在切换点的行数最靠谱的方法是数一遍或者抽样校验关键字段应用层要观察是否出现新的报错特别是写全列INSERT的报错、ORM映射不认识的字段名、以及某些框架自动生成的__v、version之类的字段冲突。回滚预案这块我的建议是表结构变更这种操作最好不要依赖跑了失败的命令再跑一次撤销命令来回滚。真正稳妥的回滚方式是在变更开始前用CREATE TABLE xx_bak AS SELECT或者CREATE TABLE xx_bak LIKE做一份结构备份但注意完全备份1200万行的数据本身也是一次大操作时间和空间成本都不低需要提前规划。实际上对于加可空字段这种变更回滚通常是新增字段后业务兼容比把字段删掉更重要。你完全可以让字段留着不启用等确认业务运行稳定后再评估要不要从表里移除。所以我会把回滚预案设计成三层第一层如果变更过程中工具报错立即停止工具原表还在业务不受影响第二层如果变更完成后发现数据不一致停止应用流量从备份恢复第三层如果一切正常但字段没用上留着也无妨不急着删。5. 那些坑我替你们踩过了5.1 元数据锁MDL未提交事务让DDL等了整个下午我先讲一个最隐蔽也最危险的坑MDL锁等待。这个场景特别常见。你选好了工具算好了低峰期信心满满地执行结果命令一执行就卡住了既不报错也不结束。一查SHOW PROCESSLIST发现ALTER语句的状态一直是Waiting for table metadata lock。什么原因绝大多数情况下是系统里有一个长期未提交的事务。哪怕那个事务只是执行了一条普通的SELECT只要它没提交或者没回滚它手里就捏着一把表的MDL读锁。而ALTER TABLE需要拿MDL写锁于是只能排队等。更要命的是MDL排队是有队首阻塞效应的你的ALTER排在后面后面新进来的普通查询也会被堵住整张表的读写瞬间全挂。那次我就是没有提前排查在一个还没提交的报表事务的影响下执行了加字段操作结果把一张一千万行的表堵了整整一个下午最后靠SELECT * FROM information_schema.INNODB_TRX揪出了那个幽灵事务。从那以后我的变更前检查清单里多了一条铁律执行DDL前必须检查information_schema.INNODB_TRX里有没有长时间未结束的事务有就先处理掉哪怕是提醒开发提交或者回滚。5.2 主从延迟不是加字段本身慢是复制扛不住第二个常见的坑是主从延迟被拉爆。有一类问题特别容易被误解你以为主库的工具没停从库报警却来了。实际上在线变更工具在拷贝数据到影子表时这些写入会走正常的binlog传到从库后同样要在从库执行。也就是说你在主库用pt-osc拷贝一千万行从库也会跟着执行一千万行的应用操作如果从库硬件能力弱延迟就蹭蹭往上涨。你可能会说那我在低峰期跑不就行了问题是很多系统的低峰期和从库能喘息的时间并不是一回事。低峰期只是业务写入少但如果你把拷贝速度提得太高从库的SQL线程依然扛不住。所以工具里的--max-lag参数一定要设置而且要配合监控观察不是设置了就完事是它真的会频繁触发、真的能保护你的从库。有个比较实用的经验chunk-size不要设太大1000行到2000行通常是个合理的起点。如果从库延迟还是压不住再往下调到500。宁可慢一点也别让复制延迟成为事故。5.3 默认值、字段命名和应用兼容性第三个坑我称之为字段本身引发的血案。加字段最怕的不是数据库层面的锁而是应用代码的隐性不兼容。比如你加了一个非空字段但没有默认值存量数据没问题工具会填默认值但新增数据如果应用没传到这个字段插入就报错。再比如字段命名有些词看起来人畜无害实际上可能是ORM框架的保留词或者和其他服务里的字段类型映射冲突。还有一点就是字段的字符集和排序规则要跟原表保持一致。我见过一个案例新加的varchar字段默认用了utf8mb4_0900_ai_ci而原表是utf8mb4_general_ci结果应用里做联表查询时索引失效慢查询暴涨。这种字段级别的坑只有在变更后的压测或监控里才能发现所以变更完成后的观察期真的不能省。另一个容易被忽略的细节是字段位置。如果你的应用代码里有SELECT *且依赖列顺序的拼接逻辑那字段加在末尾反而是最安全的如果业务上必须把字段加在中间那应用里凡是依赖结果集列顺序的地方都得额外确认。我知道现在很少有人在生产里用这种依赖列序的写法了但只要存在一个就可能是一次故障。5.4 一些容易被忽略的小事压箱底的小经验我一个一个列出来第一执行工具前看看数据库连接数。在线变更工具会额外占用连接如果连接数本来就快打满了工具可能会因为拿不到连接而失败或者把本就不宽裕的连接池彻底挤爆。第二不要在表上有长时间运行的大查询时做切换。即使在线变更允许并发读写切换瞬间还是希望表尽量安静。我的做法是切换前观察performance_schema里是否存在超过几秒未结束的查询有就再等等。第三一定要把工具版本固定下来。Percona Toolkit和gh-ost都还在迭代不同版本对MySQL版本的探测和处理逻辑有差别。同一个工具你今天用3.3跑通了下周其他同事用3.5跑可能就因为版本差异出现莫名其妙的问题。团队里建议统一固定版本有更新也先在测试库验证。第四执行变更时一定要开一个终端专门挂着监控实时看SHOW PROCESSLIST和SHOW GLOBAL STATUS里跟临时表、锁相关的计数器。工具自己会打印进度但那远远不够你得看到数据库的实际反应。第五如果你用gh-ost记得它默认会去修改binlog格式为ROW--switch-to-rbr这本身是一个全局级别的变更。有些云数据库或托管实例可能不允许这个操作先用--test-on-replica模式在从库上跑一遍能避免在主库上碰一鼻子灰。结尾一个更省心的建议做了这么多年的数据库变更我越来越觉得敢不敢加字段这个问题的答案不是靠胆子大而是靠流程和工具链。如果你问我现在的习惯我会先问自己能不能把字段加到末尾并允许为空如果能且MySQL是8.0.12以上直接用INSTANT秒级完成风险几乎为零。如果条件不满足就老老实实走pt-osc或gh-ost把前置检查和监控做到位让工具替你在风险边缘跳舞。再分享一个小技巧哪怕这次变更很顺利也建议在事后把整套检查清单和执行流程沉淀成团队的变更模板。因为一千万的表加字段这件事第一次做会紧张第十次做就会变成肌肉记忆。真正危险的从来不是某一次操作而是团队里每个人都用自己觉得没问题的方式去操作。把流程固化下来以后不管是加字段还是加索引、改类型都能直接复用这才是比敢不敢更重要的东西。