"加个索引而已，几秒钟"——这句话在 5.5 时代会换来一次全表只读。5.6 之后 Online DDL 让加二级索引可以不阻塞读写，但"不阻塞"前面有一串限定条件，条件不满足时 MySQL 会静默降级，或者卡在一个你看不见的锁上。以下按 MySQL 8.0 InnoDB 核对。
1. 三种 ALGORITHM
MySQL 执行 DDL 时会在三种算法里选：
ALTER TABLE t_order ADD KEY idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;
| 算法 | 工作方式 | 是否重建表 | 并发 DML |
|---|---|---|---|
INSTANT |
只改元数据（8.0.12+ 部分操作） | 否 | 允许 |
INPLACE |
引擎层原地构建，不复制整表 | 否 | 视 LOCK 而定 |
COPY |
建影子表、逐行复制、rename | 是 | 阻塞 |
显式写 ALGORITHM=INPLACE 的意义是"失败就报错"，而不是让 MySQL 悄悄退化成 COPY。不写算法时它自己挑，挑到 COPY 也不告诉你。
2. LOCK 的三级语义
| 级别 | 允许的并发操作 | 典型场景 |
|---|---|---|
LOCK=NONE |
读 + 写全放行 | 加/删二级索引、改索引名 |
LOCK=SHARED |
允许读，阻塞写 | 加全文索引、空间索引 |
LOCK=EXCLUSIVE |
读写全阻塞 | 改列类型、COPY 类操作 |
三者不是并列选项，而是"声明你能接受的最差情况"。写 LOCK=NONE 而操作做不到，语句直接报 ER_ALTER_OPERATION_NOT_SUPPORTED，这是好事——比跑一半才发现锁表强。
3. 为什么加二级索引能 INPLACE + LOCK=NONE
加二级索引只需要：读聚簇索引 → 把索引列写进新的二级索引 → 处理这期间的增量。InnoDB 的做法是先标记索引为"创建中"，对新增/删除的行记到 row log（innodb_online_alter_log_max_size 管这块内存），最后回放。
| 操作 | 算法 | LOCK | 备注 |
|---|---|---|---|
ADD INDEX / DROP INDEX |
INPLACE | NONE | 最常用的一类 |
ADD COLUMN（末尾、无默认值） |
INSTANT | NONE | 8.0.12+ |
MODIFY COLUMN 改类型 |
COPY | EXCLUSIVE | 必定锁表 |
ADD FOREIGN KEY |
INPLACE | SHARED | 需 foreign_key_checks=0 才更快 |
OPTIMIZE TABLE |
INPLACE | NONE | 实际是 rebuild，慎用 |
值得注意的是：加索引不重建表，但会读一遍全表。大表上这一遍的 IO 与 buffer pool 挤占，才是"慢"的来源，而不是锁。
4. 那个躲不掉的元数据锁窗口
DDL 开始与提交时都要拿 exclusive MDL（metadata lock）。这段窗口很短（毫秒到秒级），但它是"加索引也会卡住业务"的全部原因。
-- 会话 A：长事务，持有 t_order 的 MDL 读锁
BEGIN;
SELECT COUNT(*) FROM t_order WHERE status = 1; -- 事务不提交
-- 会话 B：加索引，等 MDL 写锁
ALTER TABLE t_order ADD KEY idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;
MDL 的规则是"读写互斥、写写互斥、读读共享"。会话 B 排队等写锁；此时会话 C 的普通 SELECT 来了，它需要 MDL 读锁——但 MySQL 为避免写锁饿死，会把 C 排在 B 后面。
于是：一个没提交的 SELECT + 一条 DDL = 整张表后续所有查询排队。表现是业务全站超时，SHOW PROCESSLIST 里一堆 Waiting for table metadata lock。
5. 阻塞链怎么读
SELECT id, user, db, command, time, state, LEFT(info, 60) AS info
FROM information_schema.processlist
WHERE state LIKE '%metadata lock%' OR command = 'Query'
ORDER BY time DESC LIMIT 20;
| 现象 | 含义 |
|---|---|
一条 ALTER 长时间 Waiting for table metadata lock |
前面有未提交事务持读锁 |
一堆 SELECT 也停在 metadata lock |
它们排在 DDL 之后（队列效应） |
information_schema.innodb_trx 有 trx_state=RUNNING 且 trx_started 很早 |
就是它 |
真正的元凶往往是一个 autocommit=0 的连接做完查询没提交，或者一个连接池里泄漏的事务。找它比杀 DDL 更有效——杀 DDL 只解决一次，杀长事务解决根因。
6. 三个超时参数别搞混
| 参数 | 管什么 | 默认 | 建议 |
|---|---|---|---|
lock_wait_timeout |
MDL 等待时间（秒） | 31536000 | DDL 会话临时设 3–5 |
innodb_lock_wait_timeout |
行锁等待时间（秒） | 50 | 业务连接 3–10 |
innodb_online_alter_log_max_size |
Online DDL 增量日志上限 | 128M | 写入高峰调大，否则报 1799 |
SET SESSION lock_wait_timeout = 5;
ALTER TABLE t_order ADD KEY idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;
innodb_online_alter_log_max_size 被打满时，DDL 直接失败并回滚，报 ERROR 1799 (HY000): Creating index 'idx_status' required more than 'innodb_online_alter_log_max_size' bytes of modification log。这时该做的不是无限调大，而是换窗口或改用外部工具。
7. gh-ost 与 pt-online-schema-change
MySQL 原生 Online DDL 的两个硬伤：增量日志有上限；主库上的构建开销无法暂停。于是有了外部工具。
| 维度 | gh-ost | pt-osc |
|---|---|---|
| 原理 | 伪装成从库读 binlog，影子表 + 切换 | 触发器同步增量，影子表 + rename |
| 对主库写入影响 | 较小（binlog 解析） | 触发器带来额外写开销 |
| 是否依赖触发器 | 否 | 是（部分业务禁用触发器） |
| 暂停/限流 | 支持动态 --throttle |
支持 --max-load、--critical-load |
| 外键 | 支持较弱 | 支持较好 |
| 切换瞬间 | 需短暂锁表 rename | 同样需 rename 锁 |
gh-ost \
--host=db-primary --database=shop --table=t_order \
--alter="ADD KEY idx_status (status)" \
--allow-on-master --exact-rowcount \
--max-load='Threads_running=30' \
--critical-load='Threads_running=60' \
--initially-drop-ghost-table --execute
取舍一句话：能用原生 ALGORITHM=INPLACE, LOCK=NONE 就别上工具；DDL 期间写入太猛、或需要可暂停可观测，才用 gh-ost（无触发器）或 pt-osc（外键多）。
8. 安全操作的五步
- 查长事务（最重要的一步）：
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
trx_mysql_thread_id, trx_rows_locked, LEFT(trx_query, 80) AS q
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;
age_sec 超过几十秒的，先跟业务确认再处理；trx_mysql_thread_id 就是 KILL 的目标。
- 确认没人持有表级锁：
SHOW OPEN TABLES WHERE In_use > 0;与上面的 processlist 对照。 - 低峰执行，并显式声明算法与锁级别，让不支持时立刻失败。
- 设置会话级超时：
SET SESSION lock_wait_timeout = 5;，宁可失败也不排队拖死业务。 - 观察与回滚预案：执行期间盯
Threads_running、Innodb_row_lock_waits；失败就DROP INDEX（同样是 INPLACE + NONE），不留半成品。
9. 排错：五种卡住与报错
| 现象 | 原因 | 处理 |
|---|---|---|
DDL 一直 Waiting for table metadata lock |
有未提交事务/长查询持 MDL 读锁 | 找 innodb_trx 定位并 KILL 该连接 |
| 业务全线超时，processlist 大量 metadata lock | DDL 排在前面，后续查询被队列挡住 | 先杀 DDL，再杀长事务 |
ERROR 1205 锁等待超时 |
行锁冲突（innodb_lock_wait_timeout） |
与 MDL 无关，看具体业务 SQL |
ERROR 1799 增量日志超限 |
innodb_online_alter_log_max_size 太小或写入太猛 |
调大参数或换 gh-ost |
DDL 报 ALGORITHM=COPY is required |
操作本身不支持 INPLACE | 接受锁表窗口，或用外部工具 |
10. 边界：什么时候锁表是必然的
| 操作 | 是否可避免锁表 | 说明 |
|---|---|---|
改列类型（INT → BIGINT） |
否 | 必须重建，COPY + EXCLUSIVE |
加列到表中间（AFTER col） |
否 | INSTANT 只支持末尾追加 |
| 修改字符集 | 否 | 全表重建 |
| 加唯一索引 | 近似可以 | INPLACE，但重复值会导致失败 |
| 分区表重组 | 否 | 视分区操作而定 |
| 加普通二级索引 | 是 | INPLACE + LOCK=NONE，仅剩短 MDL |
最后提醒一句：LOCK=NONE 指的是不阻塞 DML，不是不消耗资源。大表加索引会读全表、写新索引页、抢 buffer pool，业务感受到的是"变慢"而不是"卡死"，这两种故障的排查方向完全不同。
11. 组织级落地：把 DDL 变成一次发布
单个 DDL 安全，不代表一批 DDL 安全。把变更纳入发布流程，才有可重复的判断。
# 发布前自检：长事务 + 表大小 + 现有索引
mysql -N -e "SELECT table_name, table_rows,
ROUND(data_length/1048576) AS data_mb
FROM information_schema.tables
WHERE table_schema='shop' ORDER BY data_length DESC LIMIT 10;"
| 变更规模 | 建议节奏 |
|---|---|
<1000 万行 |
原生 Online DDL，低峰直接执行 |
1000 万–1 亿行 |
原生 + 会话超时 + 执行前后盯 Threads_running |
>1 亿行 |
gh-ost / pt-osc，可暂停可限流 |
| 需要改列类型 | 视同重建，必须有维护窗口 |
| 收尾动作 | 目的 |
|---|---|
ANALYZE TABLE |
更新统计信息，避免新索引被优化器忽略 |
检查 SHOW INDEX 的 Cardinality |
低于预期说明选择性差或统计过期 |
观察 Innodb_row_lock_waits |
索引上线后锁等待应下降 |
保留 DROP INDEX 回滚语句 |
变更失败可逆 |
最容易被忽略的一步是 ANALYZE TABLE。 新索引刚建完统计信息往往不准，优化器可能继续选旧索引或全表扫，于是"加了索引没变快"，然后被误判成索引失效。
12. 自测
ALGORITHM=INPLACE与ALGORITHM=COPY的核心差别是什么？- 加二级索引为什么可以做到
LOCK=NONE？ - 一条
SELECT没提交，为什么会让后面的ALTER和更多SELECT一起排队？ lock_wait_timeout和innodb_lock_wait_timeout分别管什么锁？- DDL 报
ERROR 1799说明哪个参数不够，正确做法是什么？ - 什么情况下应该放弃原生 Online DDL 改用 gh-ost？