MySQL 在线改表实战：ALGORITHM/LOCK 算法选择与 gh-ost 无停机改大表全流程

  1.  Home
  2. Text  
    3. Share to：

为什么"改一个字段"能把整站搞停

很多个人站长都有过这样的经历：站点跑得好好的，某天想给文章表加一个字段、或者把某个 VARCHAR(255) 改宽一点，于是在phpMyAdmin里点了几下，或者直接在命令行敲了一条 ALTER TABLE，结果页面瞬间全白，数据库连接池被打满，等到MySQL把DDL执行完，几分钟甚至几十分钟已经过去了。

这背后的原因并不神秘。在 MySQL 5.6 之前，绝大多数 ALTER TABLE 都是"拷贝表"式的：MySQL 会新建一张结构相同但带新定义的临时表，把原表数据一行行读出来、写进去，再删掉原表、重命名临时表。整个过程持有表级写锁，期间所有写入全部阻塞。对一张几百万行的文章表来说，这个拷贝过程可能要几分钟到几小时，而在这期间，站点的每一次写入请求都在排队。

MySQL 5.6 引入了 Online DDL，5.7 和 8.0 又大幅扩展了支持的算法，把一部分操作变成了"原地修改"（in-place），或者至少允许读写并发。但"支持 Online DDL"不等于"永远不锁表"——很多站长以为升级了版本就万事大吉，结果还是踩坑。这篇就系统讲清楚：Online DDL 到底什么时候真的不锁表，什么情况下还是会锁，以及当 ALTER 实在无法在线执行时，怎么用 gh-ost 这类工具做到真正的无停机改表。

先弄清楚：三种 ALTER 算法到底差在哪

MySQL 8.0 里，执行 ALTER TABLE 时可以指定 ALGORITHM 和 LOCK 两个关键字，它们决定了这次改表怎么执行、锁到什么程度。

ALGORITHM=COPY 是最原始的方式：建临时表、逐行拷贝、重建索引。全程持有排他锁，原表在拷贝期间不可写。这种方式对表大小极其敏感，几百万行就是灾难。

ALGORITHM=INPLACE 是原地修改：不拷贝整表数据，直接在原表的数据文件上做修改。它又分两种情形——有些操作（比如加索引、改默认值）能在执行阶段仍然允许并发DML，只在开始和结束时短暂持有元数据锁；另一些操作（比如改列类型）虽然原地进行，但执行阶段仍然禁止并发写入。

ALGORITHM=INSTANT 是 MySQL 8.0 引入的最强模式：只修改数据字典里的元数据，连数据文件都不碰，执行瞬间完成，天然不锁表。典型场景是从表末尾加一个列、加一个虚拟列、修改列的默认值。它快到什么程度？一张上亿行的表加列也是毫秒级。

这里有一个所有站长都该记住的命令：

ALTER TABLE typecho_contents ADD COLUMN views INT DEFAULT 0, ALGORITHM=INSTANT;

如果你不确定某个操作走的是哪种算法，可以先加 ALGORITHM=INSTANT，如果MySQL不支持它会直接报错，而不是静默降级去拷表。这就是"显式声明算法"的价值：宁可报错让你知道，也不要默默锁死你的站。

LOCK 选项：把'能不能并发写'握在自己手里

除了算法，还有 LOCK 级别：

LOCK=NONE      -- 执行期间允许并发读写（最理想）
LOCK=SHARED    -- 允许并发读，禁止并发写
LOCK=EXCLUSIVE -- 禁止并发读写（最保守）
LOCK=DEFAULT   -- 让MySQL自己决定

和算法一样，你可以强制声明 LOCK=NONE。如果这次操作实际上做不到完全不锁，MySQL 会直接报错拒绝执行，而不是偷偷拿一把排他锁。对个人站长来说，这是最有用的安全阀：宁可改表失败，也不要站点在不知情的情况下被锁十几分钟。

一个实用的组合是：

ALTER TABLE typecho_contents MODIFY title VARCHAR(500), ALGORITHM=INPLACE, LOCK=NONE;

如果这条命令报错说无法满足 LOCK=NONE，你就知道这个改表操作不能在线做，需要换策略——这正是我们下面要讲的场景。

哪些操作能在线，哪些一定得锁表

经验上可以这样分类。真正 INSTANT 的（说白了改元数据，零风险）：表末尾添加普通列、删除列（8.0.29+）、设置列默认值、重命名列、添加或删除虚拟列。

能 INPLACE 且 LOCK=NONE 的（并发DML不受影响，只在首尾短暂锁）：添加/删除二级索引、添加/删除外键（foreign_key_checks=0 时）、重命名索引、修改 AUTO_INCREMENT、添加/删除列注释。

能 INPLACE 但只能 LOCK=SHARED 的（执行期间禁止写）：把列类型改宽（比如 VARCHAR(100) 改 VARCHAR(500)）、VARCHAR 转 TEXT、改字符集。

只能 COPY 的（整表重写，锁表最久）：把列类型改窄、改变主键顺序、把字符集从 utf8 转 utf8mb4 且同时改变列长度、给已有表添加带生成值的存储列。这些是真正的"重活"，一旦表大了就必然停机。

这里就能看出一个常见陷阱：很多人以为"加索引不影响业务"，其实加索引确实能用 LOCK=NONE，但索引构建本身会消耗大量 IO。对一台低配 VPS 来说，边跑着网站边建一个大索引，IO 被吃满，页面照样卡。所以即便不锁表，也要挑访问低谷执行，并且用 innodb_online_alter_log_max_size 控制并发DML日志缓冲（默认 128MB，太小会导致DDL中途回滚）。

当 ALTER 必须锁表时：gh-ost 的无停机改表思路

如果遇到只能 COPY 的操作、而表又很大、迟迟不能停机，就要请出在线改表工具了。传统方案是 Percona 的 pt-online-schema-change，它用触发器同步数据；而 gh-ost（GitHub 出品）用 binlog 而不是触发器，对主库的负载更轻，也更容易暂停和限速，是目前个人站长自建场景里更推荐的方案。

gh-ost 的核心思路其实不难理解：它先在后台建一张影子表（_tablename_gho），在新表上把结构改好；然后把原表的历史数据一批批拷进影子表；同时订阅 binlog，把拷贝期间原表发生的新增、修改实时应用到影子表；等两边数据追平后，短暂地拿一下写锁，把原表和影子表换名（RENAME 是瞬间完成的原子操作），最后删掉旧表。整个过程原表一直可读写，停机时间只有最后换名的那一两秒。

gh-ost \
  --host=127.0.0.1 --port=3306 \
  --user=ghost --password=xxxxxxxx \
  --database=zz1984 --table=typecho_contents \
  --alter="MODIFY title VARCHAR(500) NOT NULL" \
  --allow-on-master \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=100" \
  --throttle-control-replicas="192.168.1.20:3306" \
  --cut-over=default \
  --exact-rowcount \
  --execute

几个关键参数值得站长们注意：--chunk-size=1000 是每次拷贝的行数，配小了慢、配大了会给主库压力；--max-load 让 gh-ost 在 Threads_running 超过阈值时自动暂停拷贝，等负载降下来继续，这是"不打扰线上业务"的核心；--critical-load 则是在极端情况下直接退出，避免把库压垮。--exact-rowcount 会先精确统计行数好估算进度，对大表要多花几秒，但对心里有底很有用。

一个真实的事故复盘

我自己的一个内容站曾经有过这样一次事故。当时文章表大约 380 万行，因为要支持一个更长的新字段，我打算把 title 从 VARCHAR(200) 扩到 VARCHAR(500)。按前面分类，"把列改宽"属于 INPLACE 但 LOCK=SHARED 的操作，我当时想"禁写就禁写吧，几秒钟的事"，于是在下午流量高峰期直接执行了不带任何参数的 ALTER TABLE。

结果远不是几秒。数据是拷贝变慢，磁盘是普通的 SATA SSD，加上 InnoDB 每行都要更新二级索引，改表实际跑了将近 6 分钟才结束，期间所有写入全部排队。因为是写锁，评论、浏览计数、后台保存全部超时，前台因为读的是缓存所以最初还没暴露，等缓存过期后整站开始大面积 500。等我发现时,已经过去了四十多分钟——据当时的监控，改表本身 6 分钟，但后续 PHP-FPM 进程堆积、连接池耗尽、排队请求连锁超时，恢复用了更久。

事后我做了三件事。第一，把这次事故复现了一遍，确认没有显式指定算法时，MySQL 确实选择了它认为"最省事"的策略而非最安全策略。第二，给所有未来的改表操作定了规矩：任何 ALTER 都必须显式带上 ALGORITHM 和 LOCK，先在不带业务的从库上跑一遍看耗时和锁行为。第三，给文章表这类大表引入 gh-ost，把改表变成可暂停、可限速的操作。这三件事之后，同样的改表需求再没出过事故。下面是从这次事故提炼出的对照数据：

改表内容    : title VARCHAR(200) -> VARCHAR(500)
表行数      : 约 380 万行
无参数 ALTER: 执行约 6 分钟, 执行期间写入全部阻塞
             连锁影响: PHP 进程堆积 -> 连接池打满 -> 全站 500
             恢复到正常: 约 40 分钟
gh-ost 改表 : 后台拷贝约 12 分钟, 全程原表可读写
             最后换名锁表: 约 1 秒
             业务可感知的停机: 近乎为零

这张对照表最值得记住的一点是：gh-ost 后台拷贝其实比原生 ALTER 更慢（12 分钟 vs 6 分钟），因为它刻意限速、避免打扰线上。但对业务来说，"慢慢来且不锁"永远优于"飞快但锁死"。

个人站长落地清单

把上面所有内容收敛成一套可执行的流程，大致是这几步。

第一步，动手前先确认操作类型。用 ALGORITHM=INSTANT, LOCK=NONE 试一次，支持就直接过；不支持再退到 ALGORITHM=INPLACE, LOCK=NONE。让MySQL明确告诉你哪种算法可行，而不是让它替你决定锁多久。

第二步,看一眼表有多大：SELECT table_rows, data_length, index_length FROM information_schema.tables WHERE table_schema='zz1984' AND table_name='typecho_contents';。行数超过一百万、或者索引体积占比很高时，任何非 INSTANT 的操作都要谨慎。

第三步，大表且非 INSTANT 的改表，一律走 gh-ost，配上 --max-load 限速，并在低峰期启动；--allow-on-master 只用于没有从库的单机场景，有从库时应当让 gh-ost 迁移到从库检查延迟。

第四步，无论用哪种方式，改完都要立刻 ANALYZE TABLE 让统计信息刷新，否则优化器可能因为旧的统计信息选错执行计划，服务恢复后查询反而变慢。

第五步，也是最重要的：给数据库配一份能在任何操作前回退的备份。gh-ost 最后会删掉旧表，虽然有 --ok-to-drop-table 之类的保护，但真正的底气来自"提前做过一次恢复演练"。没有验证过能恢复的备份，等于没有备份。

小结

在线改表这件事，本质上是把"锁在什么时候、锁多久"从数据库的隐式行为，变成站长可以显式控制的东西。ALGORITHM=INSTANT/INPLACE 加上 LOCK=NONE 是日常首选，报错就是信号；真的遇到必须拷表的操作时，gh-ost 用"影子表 + binlog 同步 + 原子换名"把停机压缩到一两秒。个人站长不需要像大厂那样搭一套复杂的 DDL 平台，但把这几条规矩固化下来，就足以避免"改一个字段把整站搞停"这类本可预防的事故。

Last modification：October 8th, 2026 at 09:24 pm
© 允许规范转载

相关文章

Leave a Comment