大表加索引不锁表：Online DDL前的四项检查

  1.  Home
  2. Text  
    3. Share to：

为什么大表加索引不能用「晚上跑一下」的思路

给一张几千万行的表加索引，很多人的经验是「放到凌晨跑」。这个经验在 MySQL 5.6 之前是对的，因为那时候加索引会重建整张表并锁写。但 MySQL 5.6 引入 Online DDL 之后，大部分加索引操作变成了 in-place 并且允许并发读写，于是「凌晨跑」这个习惯反而带来了新的风险：它把操作从「锁几小时」变成了「跑几小时」，而这几小时里如果出现一个长事务，整个 DDL 会卡在等元数据锁（MDL）上，同时把后续所有 SQL 全部堵在后面。

这是我在一次真实事故里学到的：一张 2400 万行的文章表加一个联合索引，凌晨 2 点开始执行，本来预计 8 分钟。结果跑了两小时，早上 8 点站长发现全站 502。原因是一台从库上的一个分析脚本开了一个事务一直没提交，DDL 在等它的 MDL，而 DDL 又持有 MDL 的 pending 队列，导致后面所有对该表的查询全部排队。最终只能 kill 掉分析脚本，DDL 立刻完成。

第一步：先确认要执行的是不是 in-place

MySQL 8.0 的 Online DDL 支持情况可以用一句话判断：在 ALGORITHM=INPLACE, LOCK=NONE 下，加二级索引、加列（末尾）、改列默认值、重命名索引这些操作是支持的；而修改列类型、加主键、改字符集、删除列、修改列顺序则不支持，会退化成 COPY 算法，也就是重建表。

最实用的办法是直接让 MySQL 告诉你。 在执行前，先加上显式的算法和锁要求：

ALTER TABLE article
  ADD INDEX idx_cat_time (category_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

如果这个操作不支持 in-place，MySQL 会直接报错 ER_ALTER_OPERATION_NOT_SUPPORTED 并拒绝执行，而不是默默降级成锁表。这比跑一半才发现要锁表好得多。查询支持矩阵的另一种方式是看官方文档的 Online DDL Operations 表，但那表以 MySQL 版本为维度，实际环境里最好用上面这个「提前声明」的办法。

还有一个隐藏的降级原因：如果 innodb_autoinc_lock_mode 是 0（传统模式），加自增列的 DDL 就无法用 in-place。另外如果表上有全文索引，某些 ALTER 也会被迫降级。所以先用 SHOW CREATE TABLE 把表结构和索引完整看一遍，比凭记忆判断靠谱。

第二步：DDL 真正会等什么

Online DDL 的执行过程可以分成三段，每一段都可能出问题。

阶段一：获取 MDL 排他锁（很短）。 在开始和结束时各需要一次短暂的排他 MDL，用来更新表定义。这个阶段通常是毫秒级，但如果此时有长事务持有该表的 MDL 共享锁，DDL 就会等待。而 MySQL 8.0 的 MDL 有一个「队列」特性：一旦 DDL 在等 MDL，后续所有新来的请求都要排在 DDL 后面，哪怕它们只是 SELECT。这就是雪崩的机制。

理论上 DDL 等 MDL 的时间受 lock_wait_timeout 限制，默认是 31536000 秒（一年），等于不超时。所以必须显式设置：

SET SESSION lock_wait_timeout = 10;

ALTER TABLE article
  ADD INDEX idx_cat_time (category_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

10 秒拿不到 MDL 就报错退出，代价是这次 DDL 失败需要重试，但保住了线上业务。这个参数是每次 DDL 前必须加的习惯，比任何监控都有效。

阶段二：扫描与构建（很长，但不阻塞 DML）。 InnoDB 会扫描聚簇索引并构建新的二级索引，同时通过 row log 记录这期间发生的 DML 变更，最后回放。这一段的耗时基本和表大小成正比，而且会消耗大量 IO 和 CPU。它不锁写，但会明显拖慢正常查询——这也是「凌晨跑」的真正价值所在，不是为了躲锁，而是为了躲业务高峰的 IO 竞争。

阶段三：再次获取 MDL 提交表定义（很短）。 同样是毫秒级，同样可能被长事务卡住。

所以真正的风险集中在阶段一和阶段三的 MDL 获取，而不是扫描过程。理解了这一点，处置思路就清楚了：DDL 前清理长事务，DDL 时限制等待时间。

第三步：DDL 前的四条检查

按顺序做，每条都能避免一类事故。

  1. 查长事务。 MySQL 8.0 用 information_schema.innodb_trx： 
    SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY age_sec DESC LIMIT 10;
    age_sec 超过 60 秒的都要看一下是谁。常见的是备份程序的 mysqldump（如果没有加 --single-transaction 就会长事务）、报表导出的 GUI 客户端、还有一个很隐蔽的：应用里的连接池没提交事务。这条 SQL 在低峰期执行，如果此时有人在页面上开着一个导出的页面，会被抓出来。
  2. 查表大小和索引大小。 
    SELECT table_name, table_rows,
       ROUND(data_length/1024/1024/1024, 2) AS data_gb,
       ROUND(index_length/1024/1024/1024, 2) AS idx_gb
FROM information_schema.tables
WHERE table_schema = 'zz1984' AND table_name = 'article';
    table_rows 是估算值，InnoDB 可能相差 30% 以上，但足够判断量级。要知道的是，加索引大约需要额外的空间等于新索引大小，如果磁盘水位超过 80% 就要先扩容。
  3. 查从库延迟。 DDL 在从库上是串行重放的，主库跑 8 分钟，从库可能跑 8 分钟以上（因为从库没有并发扫描的优势）。这期间从库完全延迟，如果有读流量分到从库，用户会看到旧数据。所以 DDL 前先看 SHOW REPLICA STATUS 的 Seconds_Behind_Source 是否接近 0，DDL 期间盯着它。
  4. 查执行计划，确认这个索引真的会被用到。 这是最容易被跳过的一步。加了索引但查询用不上，等于白花几小时 IO 加 2GB 空间。用 EXPLAIN 看目标 SQL 的 possible_keys 和 key，如果目标 SQL 里对 created_at 做了函数运算（比如 DATE(created_at) = '2026-10-01'），那么新索引的前缀根本用不上。

第四步：大表（千万级以上）的分批方案

即使 Online DDL 不锁写，一张 5000 万行的表加索引也可能跑几小时，期间从库严重延迟、磁盘写满的风险都存在。这种情况更稳妥的做法是「影子表 + 触发器」或者用专门的工具。

方案 A：pt-online-schema-change。 Percona Toolkit 里这个工具的逻辑是：建一张空的新表（带目标索引），在原表上挂三个触发器（INSERT/UPDATE/DELETE）把变更同步到新表，然后分批用 INSERT IGNORE ... SELECT 拷贝数据，每批 1000 行并短暂 sleep 让主库喘息，最后原子 RENAME 换表。它的优点是可控——每批的耗时、暂停时间都能调，且整个过程随时可以中断。

pt-online-schema-change \
  --alter "ADD INDEX idx_cat_time (category_id, created_at)" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=60" \
  --pause-time=50ms \
  D=zz1984,t=article \
  --execute

--max-load 和 --critical-load 是这个工具最值得用的部分：当服务器负载超过阈值时它会自动暂停，超过 critical 就退出并保留现场。这比「估算好凌晨没人用」可靠得多。

代价也是明确的：触发器会给每次 DML 增加开销（通常是 5%~15%），如果表上有大量写入，需要谨慎评估；而且 MySQL 8.0.20 之后不能对带外键的表使用（除非加 --alter-foreign-keys-method）。另外这个工具会创建名为 _article_new 的表，如果配置里有监控会误报。

方案 B：gh-ost。 GitHub 的方案，用 binlog 而不是触发器来同步增量，因此对主库的额外负担更小，而且可以「暂停/恢复」、可以只做 dry-run 看执行计划。缺点是它需要连到从库或者用 binlog 流，配置比 pt-osc 复杂一些，且不支持有外键的表。

对个人站长的场景（通常是几十万到几百行的表），Online DDL 已经足够，不值得引入这些工具。判断标准是：预计执行时间超过 30 分钟，或者表超过 1000 万行，就该考虑分批方案。

第五步：DDL 执行中的观测

DDL 跑起来之后，需要一个窗口盯着进度。MySQL 8.0 提供了 performance_schema 中的进度表：

SELECT EVENT_ID, WORK_COMPLETED, WORK_ESTIMATED,
       ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 1) AS pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';

WORK_ESTIMATED 会动态修正，所以进度条可能从 80% 跳回 40%，这是正常的（说明估算更准了，不是出错）。同时盯三个数：

  • SHOW PROCESSLIST 里是否有大量 Waiting for table metadata lock —— 一旦出现超过 5 个，立刻判断是否可以 kill DDL；
  • SHOW REPLICA STATUS 的延迟 —— 从库延迟持续上涨且没有下降趋势，说明 DDL 在主库上跑得太快，可能需要考虑先在从库单独执行；
  • 磁盘水位 df -h —— 索引构建的临时文件在 datadir 下，空间不足会导致 DDL 失败，而 MySQL 8.0 里失败后的回滚也需要时间。

一条必须记住的中止规则

如果 DDL 已经开始等 MDL，或者已经出现大量查询排队，不要犹豫，直接 kill DDL 的线程。很多人会想「再等等，那个事务可能马上就提交了」，但等待的每一秒都在累积排队请求，而这些请求又各自持有连接和内存，最终会把连接池打满，从「一个 DDL 慢」变成「全站不可用」。

kill 掉 DDL 之后的回滚在 MySQL 8.0 里是秒级完成的（因为 Online DDL 的日志可以直接丢弃），不像 5.7 那样要等很久。回滚后先清理掉长事务，再重新执行，这一次记得带上 lock_wait_timeout。

总结：把 DDL 当成一次发布

我现在的习惯是把 DDL 当成一次代码发布来管理：提前准备 SQL 文件、在旁边写好回滚语句（一般是 DROP INDEX）、执行前跑四条检查、执行时开两个窗口盯进度和 processlist、执行后立刻看一遍目标 SQL 的 EXPLAIN 是否真的走了新索引。这些步骤加起来十分钟，但它们把「凌晨碰运气」变成了「白天也可以安全执行」。

最后一句提醒：加完索引后别忘了 ANALYZE TABLE。统计信息不更新的话，优化器可能仍然选择旧索引，白忙一场。

Last modification：October 22nd, 2026 at 12:25 pm
© 允许规范转载

相关文章

Leave a Comment