直接给千万级数据量的 MySQL 表加字段是一个我见过太多人踩进去的坑。尤其当你负责的是用户表、订单表这类核心业务表数据量一上来一条看似普通的ALTER TABLE ADD COLUMN可能直接让线上业务卡死几分钟慢 SQL 堆积如山连接数打满最后只能狼狈回滚。这个事我前前后后经历过三次踩过锁表的坑也踩过空间不足的坑。今天把完整的排查思路、真实事故过程、以及最终可落地的方案全部拆开讲希望能帮你在动手之前就避开这些我用真金白银换来的教训。1. 千万级大表加字段最怕的不是慢而是锁网上一搜MySQL 大表加字段大部分文章都在讲慢和锁表。慢是结果锁才是原因。但很多人对锁表的理解停留在概念层面觉得等一下就好了直到线上挂了才明白这个等不是几秒钟的事而是直接拖垮整个数据库集群。1.1 MySQL 大表 DDL 的完整执行机制在聊加字段之前必须先搞清楚 MySQL 执行ALTER TABLE时底层到底在干什么。以最常见的 MySQL 8.0 为例一条加字段的 DDL 语句可能走的算法有三种INSTANT、INPLACE、COPY。INSTANT只在元数据层面做修改不触碰数据文件秒级完成。MySQL 8.0.12 以后在表末尾加列可以走这个算法但要求新列的默认值必须是常量表达式。INPLACE不需要复制整张表的数据文件但通常需要重建表Rebuild比如修改列类型、修改列顺序、加索引。重建过程中会在原表上申请 MDL 锁期间不允许其他事务执行写入。COPY最古老也最粗暴的方式步骤是创建一张临时新表 → 逐行拷贝原表全部数据 → 拷贝过程中所有写入会被阻塞 → 拷贝完成后删除旧表、重命名新表。期间对原表的写操作完全不可用。问题就在这很多你以为很简单的加字段操作MySQL 实际走的是COPY或INPLACE而不是你期待的INSTANT。举个最典型的场景ALTER TABLE orders ADD COLUMN user_remark VARCHAR(255) DEFAULT NOT NULL;这条语句在 MySQL 5.7 及以下版本走的是COPY算法并且在拷贝过程中对orders表的INSERT、UPDATE、DELETE全部阻塞直到新表拷贝完成期间执行的写操作会被挂起。即便到了 MySQL 8.0如果新列默认值不是常量比如DEFAULT UUID()、DEFAULT NOW()这类函数同样会退化为COPY。千万级数据量的表COPY一次的耗时是分钟级起步数据量越大阻塞时间越长业务中断时间就越长。1.2 为什么ADD COLUMN会触发全表重建很多开发者不理解的点是我不就是加一列吗为什么要把整张表的数据都复制一遍答案藏在 InnoDB 的存储结构里。InnoDB 的表数据是按聚簇索引主键索引组织的每一行数据在物理上是一条连续的记录记录中包含了表结构定义的全部列。当你新增一个字段等于每一条记录都要把新字段的值写进物理文件。这不像在 Excel 里加一列那么容易因为每行的长度变了B 树里大量页面的存储位置、页内记录的组织方式都要跟着变。MySQL 为了不把原表搞坏选择了一种最保守的方式创建一张符合新表结构的新表把旧表的数据一行行读出来、写到新表里然后原子地替换掉旧表。这个设计的牺牲品就是你的线上业务。这里有一个非常关键的性能拐点当单表数据量在 500 万以下COPY过程通常几十秒内能完成业务方大多能忍。但数据量到了 1000 万以上每行数据有几十个字段、单行占用 1KB 以上时拷贝过程动辄就要 5~10 分钟DBA 手速再快也扛不住这十分钟的业务中断。1.3 5.7 与 8.0 的 DDL 行为差异很多公司至今还在用 MySQL 5.7所以有必要把版本差异单独拿出来说。版本ADD COLUMN无默认值ADD COLUMN有默认值加普通索引修改列类型MySQL 5.6 及以下COPY锁写COPY锁写INPLACE锁写COPY锁写MySQL 5.7INPLACE锁写COPY锁写INPLACE但 Online DDL 期间允许读写COPY锁写MySQL 8.0.12INSTANT表末尾加列部分场景 INSTANT默认值非函数时可行INPLACE允许读写COPY锁写看到没有即使在 8.0 时代加一个带默认值的 NOT NULL 字段在部分场景下依然不是 INSTANT。而 5.7 下只要给了非空默认值就老老实实走 COPY全表重建跑不掉。这个行为的背后是 MySQL 对表结构变更时数据一致性的谨慎。新列有默认值时MySQL 需要保证既有行的新列都能正确填上默认值在 COPY 算法下这是最稳的如果你加的列允许NULL且没有默认值InnoDB 可以在存储层面做文章——新列的默认值就是NULL不需要实际动每一行数据所以能走 INPLACE速度上快不少。所以第一个经验教训就是在大表上加字段之前先查清楚这条 DDL 在那个版本下会走哪种算法而不是想当然地以为加个字段而已。2. 从一次线上事故看完整排查链路概念讲完我直接结合自己经历过的一次事故来复盘。那次事故发生在订单表orders上数据量接近 1800 万单行平均占用约 800 字节使用的 MySQL 版本是 5.7。当时产品提了一个需求订单表中需要增加一个客户备注字段用于记录客服人工修改的备注信息。需求很简单数据库层面就是加一列。我一开始也没当回事直接写了一条 DDLALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL COMMENT 客户备注;然后执行然后事故就发生了。2.1 事故现场连接数打满、慢查询堆积大约在执行这条 DDL 的 30 秒后监控报警开始响先是大量连接数飙升随后业务侧开始报数据库连接池耗尽。我当时的第一反应是为什么一条加字段的 SQL 会把连接池打爆。通过SHOW PROCESSLIST查看时发现大量 SQL 处于Waiting for table metadata lock状态而这些 SQL 大多是原本执行得飞快的订单查询和插入。这个状态的本质是DDL 语句在重建表时需要在原表上获取一把 MDL 写锁所有后续的读写请求都必须等待这把锁释放。而 MySQL 5.7 的 COPY 算法又慢导致等待时间拉长业务连接迟迟拿不到执行结果连接池里的连接被占满新请求进不来。Nginx 层开始出现大量 502服务端日志全是连接超时。整条链路从数据库到应用再到入口全被这条 DDL 拖垮。2.2 排查链路从表象到根因的完整过程很多文章只告诉你大表加字段会锁表但不告诉你遇到问题后应该怎么查。这里我完整还原一下我的排查链路每一步包含命令和判断依据希望你遇到同类问题时能少走弯路。第一步查当前正在执行的 SQL 和锁等待状态-- 查看当前所有连接及正在执行的 SQL SHOW FULL PROCESSLIST; -- 从 sys 库查锁等待关系 SELECT * FROM sys.innodb_lock_waits;SHOW PROCESSLIST会显示所有连接的状态。如果看到大量Waiting for table metadata lock且其中有一个连接正在执行ALTER TABLE那么基本可以确认 DDL 锁表。这一步是为了确认问题范围是单条 SQL 慢还是全局性的锁等待。第二步查看 DDL 执行进度-- 查看当前 DDL 执行的进度8.0 中 performance_schema 更详细 SELECT * FROM performance_schema.threads WHERE PROCESSLIST_COMMAND ALTER TABLE; -- 5.7 可通过 information_schema.innodb_trx 查看当前事务 SELECT * FROM information_schema.innodb_trx\G当时我通过innodb_trx看到有一条事务已经运行了快 5 分钟对应语句正是我执行的ALTER TABLEtrx_rows_modified在持续增长说明它还在逐行拷贝数据。由此判断问题根因就是 DDL 的 COPY 过程过长。第三步检查磁盘空间和 IO 压力-- 查看磁盘剩余空间 df -h -- 查看 MySQL 数据目录大小 du -sh /var/lib/mysqlALTER TABLE执行 COPY 算法时MySQL 会在数据目录下生成临时文件通常命名为#sql-xxx.ibd大小约等于原表数据文件。当时我发现磁盘剩余空间只有不到 30GB而orders表的数据文件已经占 24GB临时文件生成后磁盘直接飙红。这一步非常关键如果磁盘空间不足DDL 执行到一半会直接失败MySQL 会回滚临时文件但回滚同样耗时而且期间锁不会释放。空间不足导致的 DDL 卡死比 DDL 本身慢更致命。第四步确认不涉及已有事务的干扰还有一种常见的 MDL 锁等待是长事务导致的即使 DDL 本身走的是 Online DDL如果表上有一个长期未提交的事务DDL 也可能被阻塞在Waiting for table metadata lock状态。这也是为什么我强调大表 DDL 前必须检查information_schema.innodb_trx里是否有长事务。-- 查找运行时间超过 60 秒的事务 SELECT trx_id, trx_state, trx_started, trx_rows_modified, trx_query FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) 60;在确认没有长事务干扰后我把根因锁定在DDL 自身走了 COPY 算法拷贝时间过长上。2.3 事故处理想快速止损先解决锁而不是先解决业务事故中的命令执行时间已无法挽回当下最重要的是止损。我当时做了三步KILL掉正在执行的ALTER TABLE连接让 DDL 终止。等待 MySQL 自动回滚临时表。这个回滚过程也花了两三分钟期间 MDL 锁还没完全释放但至少不再有新的锁请求堆积。业务恢复后检查复制从库是否出现较大延迟。这里有个灵魂拷问既然 DDL 已经执行了 5 分钟KILL 掉是不是太可惜了我的答案是如果你判断 DDL 短时间内无法完成且业务已经严重受损必须杀掉。虽然前期拷贝的数据会回滚作废但相比让业务持续不可用这个代价是值得的。事后我检查了从库状态SHOW SLAVE STATUS\G看到Seconds_Behind_Master已经飙到 200 多秒。这说明主库上 DDL 的 COPY 过程产生的巨大 IO 压力已经传导到了主从复制链路。主库 DDL 期间产生的 Binlog从库回放时同样需要执行对应的 DDL 和大量数据页修改延迟在所难免。这也是大表 DDL 的重大隐患之一主库的问题会通过复制链路放大到从库影响所有读写分离场景下的读流量。3. 真正安全的三种操作方案与选型逻辑那次事故之后我把大表加字段从随便执行一条 SQL提升到了必须走变更流程的高度。接下来聊几个真正能落地的方案以及我实际选择时的取舍逻辑。3.1 方案一MySQL 8.0 的 INSTANT ADD COLUMN最省事如果你的数据库是 MySQL 8.0.12 及以上版本且目标只是在表末尾加一列并且新列可以定义为常量默认值那你很幸运可以直接走INSTANT算法ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL COMMENT 客户备注, ALGORITHMINSTANT;这个算法只修改元数据不动数据文件执行时间毫秒级对线上业务几乎无感知。但注意两个前提只在表末尾加列才能走 INSTANT如果你在某一列的后面强制指定位置比如AFTER user_id则无法使用 INSTANT。默认值必须是一个常量不能是函数、表达式或随机值。比如DEFAULT (UUID())这种就不行会退化为 INPLACE 或 COPY。MySQL 8.0 还有一条硬限制一张表使用 INSTANT 加列的总次数不能超过 64 次具体跟行格式有关8.0.29 前是 64 次。频繁加列会导致行内列数量超出限制后续会强制走 COPY。所以即使有 INSTANT也不要养成随意加字段的习惯。3.2 方案二pt-online-schema-changeMySQL 5.7 时代的救命稻草如果你的环境像我一样还在 MySQL 5.7或者 DDL 是改列类型调整列位置这类无法走 INSTANT 的操作pt-oscPercona Toolkit 中的pt-online-schema-change是 DBA 圈子里最成熟的方案。pt-osc的核心思想是曲线救国一共有三个步骤创建一张与原表结构一致的新表然后在新表上执行你要做的ALTER TABLE此时这张新表是空的执行 DDL 几乎瞬间完成。创建三个触发器AFTER INSERT、AFTER UPDATE、AFTER DELETE把原表上发生的增量变更实时同步到新表。分批将原表的存量数据插入到新表INSERT IGNORE ... SELECT ...分批循环每批处理一定行数避免一次性拷贝导致 IO 风暴。数据全部同步完成后原表和新表的数据一致了执行RENAME TABLE原子切换业务无缝转接到新表。具体命令pt-online-schema-change \ --useradmin \ --passwordyour_password \ --host127.0.0.1 \ Dyour_db,torders \ --alter ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL COMMENT 客户备注 \ --max-lag5 \ --check-interval2 \ --max-loadThreads_running100 \ --critical-loadThreads_running200 \ --charsetutf8mb4 \ --execute几个参数的含义--max-lag5从库复制延迟超过 5 秒时pt-osc会自动暂停拷贝数据等延迟恢复后再继续这是防止拖垮从库的关键参数。--max-load和--critical-load当主库的并发线程数超过阈值时暂停或终止操作防止把主库 IO 打满。--chunk-size和--chunk-time控制每批拷贝的行数或耗时默认是每批 1000 行或运行 0.5 秒。如果磁盘 IO 压力大可以调小比如--chunk-size500。我当时就是用pt-osc完成了那次事故遗留的加字段需求全程约 40 分钟主库Threads_running被控制在 50 以内从库延迟基本没超过 3 秒业务无感。这个方案在 5.7 时代几乎是必会的技能。3.3 方案三新建表 业务双写彻底但工程量大如果数据量极大上亿级且对 DDL 锁表零容忍最彻底但工程复杂度最高的方案是新表 双写。核心思路创建一张新表orders_new带好要新增的字段。修改业务代码写入操作同时写orders和orders_new双写。写一个数据回填任务把orders里的存量数据分批导入orders_new。数据追平后通过灰度开关把读流量切到orders_new。观察稳定后停掉orders的写入删掉旧表。这个方案的最大优势是完全可控每一步都可以灰度、回滚、暂停对线上影响最小。缺点是工程量大需要业务方配合改代码DBA 还要写数据回填脚本整个周期往往要做好几天。选型逻辑怎么说我个人的经验判断是数据量在 1000 万~3000 万MySQL 5.7 且走 COPY 的 DDL优先用pt-osc。数据库已经是 8.0.12且加列位置在末尾、默认值是常量直接用INSTANT。数据量上亿且不能接受任何锁等待风险的考虑新表 双写。绝对不要在任何情况下直接在生产环境执行不带ALGORITHM和LOCK限制的裸ALTER TABLE。3.4 一个补充ALTER TABLE 的 LOCK 子句用法很多人不知道ALTER TABLE可以显式指定锁级别来强制MySQL 不使用过高等级的锁ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL, ALGORITHMINPLACE, LOCKNONE;LOCKNONE允许 DDL 期间并发读写。如果 MySQL 发现当前 DDL 无法在无锁情况下完成会直接报错ER_NOT_SUPPORTED_YET而不是偷偷升级锁级别。LOCKSHARED允许 DDL 期间并发读但阻塞写。LOCKEXCLUSIVE禁止一切并发读写。这是一个极其有用的安全阀在生产环境执行大表 DDL 时宁可让 MySQL 报错拒绝执行也不要让它默默把锁级别升到 EXCLUSIVE。如果加了LOCKNONE且 MySQL 报错至少你知道自己在做什么不会像裸执行那样把业务搞挂。强调一遍这条经验是我踩过坑之后才总结出来的生产环境执行大表 DDL永远不要省略ALGORITHM和LOCK子句。4. 方案落地后你依然躲不掉的三个隐藏坑你以为选了pt-osc就万事大吉了吗方案选对只是第一步落地执行阶段还有三个坑我敢说大部分人都踩过。4.1 坑一主从复制延迟的连锁反应pt-osc的--max-lag参数保护的是从库但如果你没有设置这个参数或者阈值设置得太宽松大表 DDL 期间主库的 IO 压力会传导到从库。来说说原理主库执行 DDL 期间不仅 Binlog 体积会膨胀每一条增删改都会被记录而且从库回放 Binlog 时MySQL 5.7 的并行复制能力有限碰到 DDL 语句时SQL 线程会退化为串行执行。如果 DDL 中包含了大量的行变更操作从库回放时间会被拉长读写分离场景下从库上的报表查询、统计任务全部会变慢。我推荐的做法是在pt-osc执行期间持续盯从库状态SHOW SLAVE STATUS\G -- 观察 Seconds_Behind_Master 是否持续大于 10一旦发现从库延迟超过 5 秒pt-osc的--max-lag会暂停拷贝等待延迟恢复。但如果你用的是方案三新表 双写数据回填脚本也要做好限流和从库保护。4.2 坑二磁盘空间算少了DDL 失败导致回滚更痛苦这个坑我在事故复盘里提到过一句但这里必须展开讲因为它太容易被忽略。假设你的表数据文件是 24GB你以为留 30GB 就够了但实际执行 COPY 类 DDL 时磁盘空间需求远不止一个表文件的大小新表的数据文件约等于原表大小24GB临时排序文件或 undo log 增长实际取决于 DDL 类型通常在 2~5GBBinlog 增长执行期间如果有大量写操作Binlog 需要额外空间保存这些日志也就是说一次大表 DDL建议预留原表大小的2.5 倍磁盘空间。否则 DDL 执行到一半报Table full或磁盘写满错误MySQL 开始回滚时回滚过程同样要在磁盘上做大量 IO如果磁盘空间已经满了回滚会卡住锁就一直不释放这才是最灾难的结局。落地的检查命令很简单df -h /var/lib/mysql同时监控 MySQL 的临时文件生成情况watch -n 5 ls -lh /var/lib/mysql/*#sql* 2/dev/null看到临时文件大小快速增长的时候心里要有数磁盘空间要用多少什么时候必须下手止损。4.3 坑三连接数打满后KILL 掉 DDL 也救不了业务很多人以为发现锁表后立即KILL掉 DDL 就完事了。但实际场景中KILL 之后 MySQL 需要回滚已经做了一半的 COPY 操作这个回滚过程会继续占用 IO 和 CPU并且 MDL 锁不会立刻释放。更要命的是在连接数已经被打满的情况下KILL命令需要一个空闲连接来执行但你用 DBeaver、Navicat 这些客户端去连的时候会发现连接超时。我当时是靠mysql命令行从本机 socket 连接进去才执行成功的mysql -uadmin -p -S /var/run/mysqld/mysqld.sock这是一个非常实用的技巧数据库连接池打满时TCP 连接大概率无法建立但通过 Unix Socket 方式连接通常还通畅。遇到类似险情不要傻傻地等客户端连上优先试 socket 登录然后执行KILL。另外KILL 之前最好确认你要 KILL 的是哪个连接-- 找到正在执行 ALTER TABLE 的连接 ID SELECT id, time, state, left(query, 50) AS query_info FROM information_schema.processlist WHERE command ALTER TABLE;然后针对性地KILL id而不是KILL所有连接。5. 把大表 DDL纳入正规变更流程先说一句掏心窝的话单靠技术手段解决一次 DDL 锁表问题不难难的是每次都做好预防。5.1 日常变更前必须做的四步检查我在团队内部制定了一个 DDL 变更清单每次执行大表变更前必须走一遍检查一明确 DDL 走哪种算法-- 用 EXPLAIN 查看 ALTER TABLE 的执行计划8.0 支持 EXPLAIN ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL;看输出中的ALGORITHM字段如果是COPY绝对不能在生产环境直接执行。8.0 还支持通过performance_schema查看历史 DDL 的执行方式。检查二确认当前表上有无长事务SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_running_seconds FROM information_schema.innodb_trx ORDER BY trx_started ASC;超过 30 秒未结束的事务在 DDL 执行期间会造成Waiting for table metadata lock。这个检查必须放在 DDL 之前而不是之后。检查三确认磁盘空间充足# 至少预留原表数据文件大小的 2.5 倍 df -h /var/lib/mysql du -h /var/lib/mysql/your_db/orders.ibd如果空间不够宁可晚几天执行变更也不要赌应该能撑过去。检查四通知业务方预留低峰期窗口大表 DDL 不是 DBA 一个人的事。即使走pt-osc主库的 IO 压力仍然可能影响线上性能。我通常会在凌晨 2 点到 5 点之间的低峰窗口执行变更并且提前在运维群里发变更通告让业务方做好心理预期。5.2 变更后的验证清单DDL 执行完成后不代表事情结束了还需要做一遍验证检查表结构是否正确SHOW CREATE TABLE orders\G确认新字段的数据是否正确SELECT COUNT(*) FROM orders WHERE customer_remark ;检查从库复制延迟是否已恢复正常SHOW SLAVE STATUS\G看Seconds_Behind_Master是否为 0如果仍有较大延迟需要等待或者排查原因。清理pt-osc的残留触发器正常情况下pt-osc会自动清理但出过意外建议确认一下SHOW TRIGGERS FROM your_db LIKE orders%;5.3 一个补充经验尽量用允许 NULL 且无默认值的列来规避锁最后分享一个小技巧。如果业务场景允许加字段时尽量写成ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) NULL COMMENT 客户备注;注意允许NULL且没有指定默认值。这种情况下MySQL 不需要为每一行回填具体值新列在物理上可以直接以不存在的状态存在本质上走 INSTANT 或 INPLACE 的可能性大增锁表和全表重建的风险大幅下降。当然这取决于业务需求如果业务层严格要求非空约束这条不适用。但从数据库变更安全的角度来说允许 NULL 无默认值 是 DDL 友好度最高的一种加列方式。我在实际项目中经常建议后端同事如果这个字段的可选性允许先加一个允许 NULL 的列等数据回填完毕、校验无误后再单独执行一次修改约束的 DDL。虽然多了一个步骤但每一步的风险都会小很多。最后再分享一个实际执行时的判断技巧我个人的体会是在你每次准备往千万级大表上执行 DDL 之前先问自己三个问题——这条 DDL 会锁多久锁了之后业务能不能扛住如果中途失败回滚需要多久这三个问题的答案决定了你是直接执行、走pt-osc还是干脆放弃 DDL、改用新表双写方案。根据我的实战经验1 个亿的数据量一次普通pt-osc加字段操作大概需要 1~2 个小时期间主库负载会有明显上升但不会锁表。5000 万以下的数据量通常 30 分钟内能跑完。如果你发现某个方案预计执行时间超过 2 小时那就要警惕了宁可停下来重新评估也不要让 DDL 挂在那里持续消耗数据库资源。另外补充一个实用技巧在执行大表 DDL 前可以先把long_query_time临时调大比如从 1 秒调到 30 秒避免pt-osc内部产生的大量查询被慢日志记录刷屏。执行完后再调回来SET GLOBAL long_query_time 30; -- DDL 完成后恢复 SET GLOBAL long_query_time 1;这些都是文档里不会写的细节但关键时刻真能救命。 千万级MySQL大表加字段：锁表原理与安全变更实战
直接给千万级数据量的 MySQL 表加字段是一个我见过太多人踩进去的坑。尤其当你负责的是用户表、订单表这类核心业务表数据量一上来一条看似普通的ALTER TABLE ADD COLUMN可能直接让线上业务卡死几分钟慢 SQL 堆积如山连接数打满最后只能狼狈回滚。这个事我前前后后经历过三次踩过锁表的坑也踩过空间不足的坑。今天把完整的排查思路、真实事故过程、以及最终可落地的方案全部拆开讲希望能帮你在动手之前就避开这些我用真金白银换来的教训。1. 千万级大表加字段最怕的不是慢而是锁网上一搜MySQL 大表加字段大部分文章都在讲慢和锁表。慢是结果锁才是原因。但很多人对锁表的理解停留在概念层面觉得等一下就好了直到线上挂了才明白这个等不是几秒钟的事而是直接拖垮整个数据库集群。1.1 MySQL 大表 DDL 的完整执行机制在聊加字段之前必须先搞清楚 MySQL 执行ALTER TABLE时底层到底在干什么。以最常见的 MySQL 8.0 为例一条加字段的 DDL 语句可能走的算法有三种INSTANT、INPLACE、COPY。INSTANT只在元数据层面做修改不触碰数据文件秒级完成。MySQL 8.0.12 以后在表末尾加列可以走这个算法但要求新列的默认值必须是常量表达式。INPLACE不需要复制整张表的数据文件但通常需要重建表Rebuild比如修改列类型、修改列顺序、加索引。重建过程中会在原表上申请 MDL 锁期间不允许其他事务执行写入。COPY最古老也最粗暴的方式步骤是创建一张临时新表 → 逐行拷贝原表全部数据 → 拷贝过程中所有写入会被阻塞 → 拷贝完成后删除旧表、重命名新表。期间对原表的写操作完全不可用。问题就在这很多你以为很简单的加字段操作MySQL 实际走的是COPY或INPLACE而不是你期待的INSTANT。举个最典型的场景ALTER TABLE orders ADD COLUMN user_remark VARCHAR(255) DEFAULT NOT NULL;这条语句在 MySQL 5.7 及以下版本走的是COPY算法并且在拷贝过程中对orders表的INSERT、UPDATE、DELETE全部阻塞直到新表拷贝完成期间执行的写操作会被挂起。即便到了 MySQL 8.0如果新列默认值不是常量比如DEFAULT UUID()、DEFAULT NOW()这类函数同样会退化为COPY。千万级数据量的表COPY一次的耗时是分钟级起步数据量越大阻塞时间越长业务中断时间就越长。1.2 为什么ADD COLUMN会触发全表重建很多开发者不理解的点是我不就是加一列吗为什么要把整张表的数据都复制一遍答案藏在 InnoDB 的存储结构里。InnoDB 的表数据是按聚簇索引主键索引组织的每一行数据在物理上是一条连续的记录记录中包含了表结构定义的全部列。当你新增一个字段等于每一条记录都要把新字段的值写进物理文件。这不像在 Excel 里加一列那么容易因为每行的长度变了B 树里大量页面的存储位置、页内记录的组织方式都要跟着变。MySQL 为了不把原表搞坏选择了一种最保守的方式创建一张符合新表结构的新表把旧表的数据一行行读出来、写到新表里然后原子地替换掉旧表。这个设计的牺牲品就是你的线上业务。这里有一个非常关键的性能拐点当单表数据量在 500 万以下COPY过程通常几十秒内能完成业务方大多能忍。但数据量到了 1000 万以上每行数据有几十个字段、单行占用 1KB 以上时拷贝过程动辄就要 5~10 分钟DBA 手速再快也扛不住这十分钟的业务中断。1.3 5.7 与 8.0 的 DDL 行为差异很多公司至今还在用 MySQL 5.7所以有必要把版本差异单独拿出来说。版本ADD COLUMN无默认值ADD COLUMN有默认值加普通索引修改列类型MySQL 5.6 及以下COPY锁写COPY锁写INPLACE锁写COPY锁写MySQL 5.7INPLACE锁写COPY锁写INPLACE但 Online DDL 期间允许读写COPY锁写MySQL 8.0.12INSTANT表末尾加列部分场景 INSTANT默认值非函数时可行INPLACE允许读写COPY锁写看到没有即使在 8.0 时代加一个带默认值的 NOT NULL 字段在部分场景下依然不是 INSTANT。而 5.7 下只要给了非空默认值就老老实实走 COPY全表重建跑不掉。这个行为的背后是 MySQL 对表结构变更时数据一致性的谨慎。新列有默认值时MySQL 需要保证既有行的新列都能正确填上默认值在 COPY 算法下这是最稳的如果你加的列允许NULL且没有默认值InnoDB 可以在存储层面做文章——新列的默认值就是NULL不需要实际动每一行数据所以能走 INPLACE速度上快不少。所以第一个经验教训就是在大表上加字段之前先查清楚这条 DDL 在那个版本下会走哪种算法而不是想当然地以为加个字段而已。2. 从一次线上事故看完整排查链路概念讲完我直接结合自己经历过的一次事故来复盘。那次事故发生在订单表orders上数据量接近 1800 万单行平均占用约 800 字节使用的 MySQL 版本是 5.7。当时产品提了一个需求订单表中需要增加一个客户备注字段用于记录客服人工修改的备注信息。需求很简单数据库层面就是加一列。我一开始也没当回事直接写了一条 DDLALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL COMMENT 客户备注;然后执行然后事故就发生了。2.1 事故现场连接数打满、慢查询堆积大约在执行这条 DDL 的 30 秒后监控报警开始响先是大量连接数飙升随后业务侧开始报数据库连接池耗尽。我当时的第一反应是为什么一条加字段的 SQL 会把连接池打爆。通过SHOW PROCESSLIST查看时发现大量 SQL 处于Waiting for table metadata lock状态而这些 SQL 大多是原本执行得飞快的订单查询和插入。这个状态的本质是DDL 语句在重建表时需要在原表上获取一把 MDL 写锁所有后续的读写请求都必须等待这把锁释放。而 MySQL 5.7 的 COPY 算法又慢导致等待时间拉长业务连接迟迟拿不到执行结果连接池里的连接被占满新请求进不来。Nginx 层开始出现大量 502服务端日志全是连接超时。整条链路从数据库到应用再到入口全被这条 DDL 拖垮。2.2 排查链路从表象到根因的完整过程很多文章只告诉你大表加字段会锁表但不告诉你遇到问题后应该怎么查。这里我完整还原一下我的排查链路每一步包含命令和判断依据希望你遇到同类问题时能少走弯路。第一步查当前正在执行的 SQL 和锁等待状态-- 查看当前所有连接及正在执行的 SQL SHOW FULL PROCESSLIST; -- 从 sys 库查锁等待关系 SELECT * FROM sys.innodb_lock_waits;SHOW PROCESSLIST会显示所有连接的状态。如果看到大量Waiting for table metadata lock且其中有一个连接正在执行ALTER TABLE那么基本可以确认 DDL 锁表。这一步是为了确认问题范围是单条 SQL 慢还是全局性的锁等待。第二步查看 DDL 执行进度-- 查看当前 DDL 执行的进度8.0 中 performance_schema 更详细 SELECT * FROM performance_schema.threads WHERE PROCESSLIST_COMMAND ALTER TABLE; -- 5.7 可通过 information_schema.innodb_trx 查看当前事务 SELECT * FROM information_schema.innodb_trx\G当时我通过innodb_trx看到有一条事务已经运行了快 5 分钟对应语句正是我执行的ALTER TABLEtrx_rows_modified在持续增长说明它还在逐行拷贝数据。由此判断问题根因就是 DDL 的 COPY 过程过长。第三步检查磁盘空间和 IO 压力-- 查看磁盘剩余空间 df -h -- 查看 MySQL 数据目录大小 du -sh /var/lib/mysqlALTER TABLE执行 COPY 算法时MySQL 会在数据目录下生成临时文件通常命名为#sql-xxx.ibd大小约等于原表数据文件。当时我发现磁盘剩余空间只有不到 30GB而orders表的数据文件已经占 24GB临时文件生成后磁盘直接飙红。这一步非常关键如果磁盘空间不足DDL 执行到一半会直接失败MySQL 会回滚临时文件但回滚同样耗时而且期间锁不会释放。空间不足导致的 DDL 卡死比 DDL 本身慢更致命。第四步确认不涉及已有事务的干扰还有一种常见的 MDL 锁等待是长事务导致的即使 DDL 本身走的是 Online DDL如果表上有一个长期未提交的事务DDL 也可能被阻塞在Waiting for table metadata lock状态。这也是为什么我强调大表 DDL 前必须检查information_schema.innodb_trx里是否有长事务。-- 查找运行时间超过 60 秒的事务 SELECT trx_id, trx_state, trx_started, trx_rows_modified, trx_query FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) 60;在确认没有长事务干扰后我把根因锁定在DDL 自身走了 COPY 算法拷贝时间过长上。2.3 事故处理想快速止损先解决锁而不是先解决业务事故中的命令执行时间已无法挽回当下最重要的是止损。我当时做了三步KILL掉正在执行的ALTER TABLE连接让 DDL 终止。等待 MySQL 自动回滚临时表。这个回滚过程也花了两三分钟期间 MDL 锁还没完全释放但至少不再有新的锁请求堆积。业务恢复后检查复制从库是否出现较大延迟。这里有个灵魂拷问既然 DDL 已经执行了 5 分钟KILL 掉是不是太可惜了我的答案是如果你判断 DDL 短时间内无法完成且业务已经严重受损必须杀掉。虽然前期拷贝的数据会回滚作废但相比让业务持续不可用这个代价是值得的。事后我检查了从库状态SHOW SLAVE STATUS\G看到Seconds_Behind_Master已经飙到 200 多秒。这说明主库上 DDL 的 COPY 过程产生的巨大 IO 压力已经传导到了主从复制链路。主库 DDL 期间产生的 Binlog从库回放时同样需要执行对应的 DDL 和大量数据页修改延迟在所难免。这也是大表 DDL 的重大隐患之一主库的问题会通过复制链路放大到从库影响所有读写分离场景下的读流量。3. 真正安全的三种操作方案与选型逻辑那次事故之后我把大表加字段从随便执行一条 SQL提升到了必须走变更流程的高度。接下来聊几个真正能落地的方案以及我实际选择时的取舍逻辑。3.1 方案一MySQL 8.0 的 INSTANT ADD COLUMN最省事如果你的数据库是 MySQL 8.0.12 及以上版本且目标只是在表末尾加一列并且新列可以定义为常量默认值那你很幸运可以直接走INSTANT算法ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL COMMENT 客户备注, ALGORITHMINSTANT;这个算法只修改元数据不动数据文件执行时间毫秒级对线上业务几乎无感知。但注意两个前提只在表末尾加列才能走 INSTANT如果你在某一列的后面强制指定位置比如AFTER user_id则无法使用 INSTANT。默认值必须是一个常量不能是函数、表达式或随机值。比如DEFAULT (UUID())这种就不行会退化为 INPLACE 或 COPY。MySQL 8.0 还有一条硬限制一张表使用 INSTANT 加列的总次数不能超过 64 次具体跟行格式有关8.0.29 前是 64 次。频繁加列会导致行内列数量超出限制后续会强制走 COPY。所以即使有 INSTANT也不要养成随意加字段的习惯。3.2 方案二pt-online-schema-changeMySQL 5.7 时代的救命稻草如果你的环境像我一样还在 MySQL 5.7或者 DDL 是改列类型调整列位置这类无法走 INSTANT 的操作pt-oscPercona Toolkit 中的pt-online-schema-change是 DBA 圈子里最成熟的方案。pt-osc的核心思想是曲线救国一共有三个步骤创建一张与原表结构一致的新表然后在新表上执行你要做的ALTER TABLE此时这张新表是空的执行 DDL 几乎瞬间完成。创建三个触发器AFTER INSERT、AFTER UPDATE、AFTER DELETE把原表上发生的增量变更实时同步到新表。分批将原表的存量数据插入到新表INSERT IGNORE ... SELECT ...分批循环每批处理一定行数避免一次性拷贝导致 IO 风暴。数据全部同步完成后原表和新表的数据一致了执行RENAME TABLE原子切换业务无缝转接到新表。具体命令pt-online-schema-change \ --useradmin \ --passwordyour_password \ --host127.0.0.1 \ Dyour_db,torders \ --alter ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL COMMENT 客户备注 \ --max-lag5 \ --check-interval2 \ --max-loadThreads_running100 \ --critical-loadThreads_running200 \ --charsetutf8mb4 \ --execute几个参数的含义--max-lag5从库复制延迟超过 5 秒时pt-osc会自动暂停拷贝数据等延迟恢复后再继续这是防止拖垮从库的关键参数。--max-load和--critical-load当主库的并发线程数超过阈值时暂停或终止操作防止把主库 IO 打满。--chunk-size和--chunk-time控制每批拷贝的行数或耗时默认是每批 1000 行或运行 0.5 秒。如果磁盘 IO 压力大可以调小比如--chunk-size500。我当时就是用pt-osc完成了那次事故遗留的加字段需求全程约 40 分钟主库Threads_running被控制在 50 以内从库延迟基本没超过 3 秒业务无感。这个方案在 5.7 时代几乎是必会的技能。3.3 方案三新建表 业务双写彻底但工程量大如果数据量极大上亿级且对 DDL 锁表零容忍最彻底但工程复杂度最高的方案是新表 双写。核心思路创建一张新表orders_new带好要新增的字段。修改业务代码写入操作同时写orders和orders_new双写。写一个数据回填任务把orders里的存量数据分批导入orders_new。数据追平后通过灰度开关把读流量切到orders_new。观察稳定后停掉orders的写入删掉旧表。这个方案的最大优势是完全可控每一步都可以灰度、回滚、暂停对线上影响最小。缺点是工程量大需要业务方配合改代码DBA 还要写数据回填脚本整个周期往往要做好几天。选型逻辑怎么说我个人的经验判断是数据量在 1000 万~3000 万MySQL 5.7 且走 COPY 的 DDL优先用pt-osc。数据库已经是 8.0.12且加列位置在末尾、默认值是常量直接用INSTANT。数据量上亿且不能接受任何锁等待风险的考虑新表 双写。绝对不要在任何情况下直接在生产环境执行不带ALGORITHM和LOCK限制的裸ALTER TABLE。3.4 一个补充ALTER TABLE 的 LOCK 子句用法很多人不知道ALTER TABLE可以显式指定锁级别来强制MySQL 不使用过高等级的锁ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL, ALGORITHMINPLACE, LOCKNONE;LOCKNONE允许 DDL 期间并发读写。如果 MySQL 发现当前 DDL 无法在无锁情况下完成会直接报错ER_NOT_SUPPORTED_YET而不是偷偷升级锁级别。LOCKSHARED允许 DDL 期间并发读但阻塞写。LOCKEXCLUSIVE禁止一切并发读写。这是一个极其有用的安全阀在生产环境执行大表 DDL 时宁可让 MySQL 报错拒绝执行也不要让它默默把锁级别升到 EXCLUSIVE。如果加了LOCKNONE且 MySQL 报错至少你知道自己在做什么不会像裸执行那样把业务搞挂。强调一遍这条经验是我踩过坑之后才总结出来的生产环境执行大表 DDL永远不要省略ALGORITHM和LOCK子句。4. 方案落地后你依然躲不掉的三个隐藏坑你以为选了pt-osc就万事大吉了吗方案选对只是第一步落地执行阶段还有三个坑我敢说大部分人都踩过。4.1 坑一主从复制延迟的连锁反应pt-osc的--max-lag参数保护的是从库但如果你没有设置这个参数或者阈值设置得太宽松大表 DDL 期间主库的 IO 压力会传导到从库。来说说原理主库执行 DDL 期间不仅 Binlog 体积会膨胀每一条增删改都会被记录而且从库回放 Binlog 时MySQL 5.7 的并行复制能力有限碰到 DDL 语句时SQL 线程会退化为串行执行。如果 DDL 中包含了大量的行变更操作从库回放时间会被拉长读写分离场景下从库上的报表查询、统计任务全部会变慢。我推荐的做法是在pt-osc执行期间持续盯从库状态SHOW SLAVE STATUS\G -- 观察 Seconds_Behind_Master 是否持续大于 10一旦发现从库延迟超过 5 秒pt-osc的--max-lag会暂停拷贝等待延迟恢复。但如果你用的是方案三新表 双写数据回填脚本也要做好限流和从库保护。4.2 坑二磁盘空间算少了DDL 失败导致回滚更痛苦这个坑我在事故复盘里提到过一句但这里必须展开讲因为它太容易被忽略。假设你的表数据文件是 24GB你以为留 30GB 就够了但实际执行 COPY 类 DDL 时磁盘空间需求远不止一个表文件的大小新表的数据文件约等于原表大小24GB临时排序文件或 undo log 增长实际取决于 DDL 类型通常在 2~5GBBinlog 增长执行期间如果有大量写操作Binlog 需要额外空间保存这些日志也就是说一次大表 DDL建议预留原表大小的2.5 倍磁盘空间。否则 DDL 执行到一半报Table full或磁盘写满错误MySQL 开始回滚时回滚过程同样要在磁盘上做大量 IO如果磁盘空间已经满了回滚会卡住锁就一直不释放这才是最灾难的结局。落地的检查命令很简单df -h /var/lib/mysql同时监控 MySQL 的临时文件生成情况watch -n 5 ls -lh /var/lib/mysql/*#sql* 2/dev/null看到临时文件大小快速增长的时候心里要有数磁盘空间要用多少什么时候必须下手止损。4.3 坑三连接数打满后KILL 掉 DDL 也救不了业务很多人以为发现锁表后立即KILL掉 DDL 就完事了。但实际场景中KILL 之后 MySQL 需要回滚已经做了一半的 COPY 操作这个回滚过程会继续占用 IO 和 CPU并且 MDL 锁不会立刻释放。更要命的是在连接数已经被打满的情况下KILL命令需要一个空闲连接来执行但你用 DBeaver、Navicat 这些客户端去连的时候会发现连接超时。我当时是靠mysql命令行从本机 socket 连接进去才执行成功的mysql -uadmin -p -S /var/run/mysqld/mysqld.sock这是一个非常实用的技巧数据库连接池打满时TCP 连接大概率无法建立但通过 Unix Socket 方式连接通常还通畅。遇到类似险情不要傻傻地等客户端连上优先试 socket 登录然后执行KILL。另外KILL 之前最好确认你要 KILL 的是哪个连接-- 找到正在执行 ALTER TABLE 的连接 ID SELECT id, time, state, left(query, 50) AS query_info FROM information_schema.processlist WHERE command ALTER TABLE;然后针对性地KILL id而不是KILL所有连接。5. 把大表 DDL纳入正规变更流程先说一句掏心窝的话单靠技术手段解决一次 DDL 锁表问题不难难的是每次都做好预防。5.1 日常变更前必须做的四步检查我在团队内部制定了一个 DDL 变更清单每次执行大表变更前必须走一遍检查一明确 DDL 走哪种算法-- 用 EXPLAIN 查看 ALTER TABLE 的执行计划8.0 支持 EXPLAIN ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) DEFAULT NOT NULL;看输出中的ALGORITHM字段如果是COPY绝对不能在生产环境直接执行。8.0 还支持通过performance_schema查看历史 DDL 的执行方式。检查二确认当前表上有无长事务SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_running_seconds FROM information_schema.innodb_trx ORDER BY trx_started ASC;超过 30 秒未结束的事务在 DDL 执行期间会造成Waiting for table metadata lock。这个检查必须放在 DDL 之前而不是之后。检查三确认磁盘空间充足# 至少预留原表数据文件大小的 2.5 倍 df -h /var/lib/mysql du -h /var/lib/mysql/your_db/orders.ibd如果空间不够宁可晚几天执行变更也不要赌应该能撑过去。检查四通知业务方预留低峰期窗口大表 DDL 不是 DBA 一个人的事。即使走pt-osc主库的 IO 压力仍然可能影响线上性能。我通常会在凌晨 2 点到 5 点之间的低峰窗口执行变更并且提前在运维群里发变更通告让业务方做好心理预期。5.2 变更后的验证清单DDL 执行完成后不代表事情结束了还需要做一遍验证检查表结构是否正确SHOW CREATE TABLE orders\G确认新字段的数据是否正确SELECT COUNT(*) FROM orders WHERE customer_remark ;检查从库复制延迟是否已恢复正常SHOW SLAVE STATUS\G看Seconds_Behind_Master是否为 0如果仍有较大延迟需要等待或者排查原因。清理pt-osc的残留触发器正常情况下pt-osc会自动清理但出过意外建议确认一下SHOW TRIGGERS FROM your_db LIKE orders%;5.3 一个补充经验尽量用允许 NULL 且无默认值的列来规避锁最后分享一个小技巧。如果业务场景允许加字段时尽量写成ALTER TABLE orders ADD COLUMN customer_remark VARCHAR(500) NULL COMMENT 客户备注;注意允许NULL且没有指定默认值。这种情况下MySQL 不需要为每一行回填具体值新列在物理上可以直接以不存在的状态存在本质上走 INSTANT 或 INPLACE 的可能性大增锁表和全表重建的风险大幅下降。当然这取决于业务需求如果业务层严格要求非空约束这条不适用。但从数据库变更安全的角度来说允许 NULL 无默认值 是 DDL 友好度最高的一种加列方式。我在实际项目中经常建议后端同事如果这个字段的可选性允许先加一个允许 NULL 的列等数据回填完毕、校验无误后再单独执行一次修改约束的 DDL。虽然多了一个步骤但每一步的风险都会小很多。最后再分享一个实际执行时的判断技巧我个人的体会是在你每次准备往千万级大表上执行 DDL 之前先问自己三个问题——这条 DDL 会锁多久锁了之后业务能不能扛住如果中途失败回滚需要多久这三个问题的答案决定了你是直接执行、走pt-osc还是干脆放弃 DDL、改用新表双写方案。根据我的实战经验1 个亿的数据量一次普通pt-osc加字段操作大概需要 1~2 个小时期间主库负载会有明显上升但不会锁表。5000 万以下的数据量通常 30 分钟内能跑完。如果你发现某个方案预计执行时间超过 2 小时那就要警惕了宁可停下来重新评估也不要让 DDL 挂在那里持续消耗数据库资源。另外补充一个实用技巧在执行大表 DDL 前可以先把long_query_time临时调大比如从 1 秒调到 30 秒避免pt-osc内部产生的大量查询被慢日志记录刷屏。执行完后再调回来SET GLOBAL long_query_time 30; -- DDL 完成后恢复 SET GLOBAL long_query_time 1;这些都是文档里不会写的细节但关键时刻真能救命。