MySQL / 系统课时
完成一次端到端大表变更演练
完成一次端到端大表变更演练
本课用一次“为大表新增联合索引”的演练串起完整发布闭环:先预检版本、规模、事务与 MDL,再在线构建并观测,随后验证不可见索引,注入阻塞练习中止与回退,最后形成可复用的发布记录。
学习目标
- 能在执行大表
ALTER前确认 MySQL 8.4、表规模、长事务、MDL 和目标索引状态。 - 能用
ALGORITHM=INPLACE、LOCK=NONE执行在线索引构建,并从独立连接观察执行与等待。 - 能利用不可见索引、异常注入和回退动作完成一次接近真实发布窗口的演练。
设定演练对象与退出条件
以下以 MySQL 8.4 InnoDB 表 app.orders 为例,目标是新增联合索引 idx_status_created(status, created_at)。索引先保持不可见,验证通过后再切换为可见,把“构建索引”和“让优化器开始使用索引”拆成两个可控动作。

演练开始前先写明退出条件,而不是看到异常后临时讨论。至少包括:MDL 等待超过发布单阈值、数据库负载超过既定阈值、复制延迟持续扩大、核心 SQL 延迟恶化,以及无法解释的错误或告警。阈值应来自本系统基线,本课不编造固定数值。
做变更前预检
先确认版本、表规模以及同名索引是否已经存在。TABLE_ROWS 对 InnoDB 是估算值,容量判断应同时关注数据和索引字节数。
SELECT VERSION() AS mysql_version;
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_ROWS,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app'
AND TABLE_NAME = 'orders';
SELECT COUNT(*) AS index_exists
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'app'
AND TABLE_NAME = 'orders'
AND INDEX_NAME = 'idx_status_created';预期结果
mysql_version: 8.4.<VALUE>
TABLE_SCHEMA: app
TABLE_NAME: orders
TABLE_ROWS: <VALUE>
size_gb: <VALUE>
index_exists: 0实际版本、行数和容量会不同;如果 index_exists 不是 0,先确认已有索引定义,不能继续重复创建。
接着检查长事务和等待中的 MDL。大表在线 DDL 即使允许并发读写,也仍可能在开始或提交阶段等待元数据锁。
SELECT
trx_mysql_thread_id,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_s,
trx_rows_modified
FROM information_schema.INNODB_TRX
ORDER BY trx_started;
SELECT
OBJECT_TYPE,
OBJECT_SCHEMA,
OBJECT_NAME,
LOCK_TYPE,
LOCK_DURATION,
LOCK_STATUS,
OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'app'
AND OBJECT_NAME = 'orders'
ORDER BY LOCK_STATUS, OWNER_THREAD_ID;预期结果
长事务数量与持续时间应处于发布单允许范围内。
metadata_locks 中不应存在无法解释的 PENDING 锁。在线执行并实时观测
正式执行前给当前会话设置有限的元数据锁等待时间,避免在入口阶段无限等待。显式指定 ALGORITHM=INPLACE 和 LOCK=NONE 后,如果当前版本或变更形式不支持该组合,语句会报错,而不是悄悄采用未明确接受的算法或锁策略。
SET SESSION lock_wait_timeout = 30;
ALTER TABLE app.orders
ADD INDEX idx_status_created (status, created_at) INVISIBLE,
ALGORITHM = INPLACE,
LOCK = NONE;预期结果
Query OK, 0 rows affected (<TIME>)
Records: 0 Duplicates: 0 Warnings: 0<TIME> 取决于表大小、磁盘与并发负载。演练时不要只盯着客户端是否“还在跑”,应从另一连接观察 ALTER 会话和目标表的锁状态。
SELECT
PROCESSLIST_ID,
PROCESSLIST_TIME,
PROCESSLIST_STATE,
PROCESSLIST_INFO
FROM performance_schema.threads
WHERE PROCESSLIST_INFO LIKE 'ALTER TABLE app.orders%';
SELECT
LOCK_TYPE,
LOCK_DURATION,
LOCK_STATUS,
OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'app'
AND OBJECT_NAME = 'orders'
ORDER BY LOCK_STATUS, OWNER_THREAD_ID;预期结果
ALTER 执行期间可看到对应会话,PROCESSLIST_STATE 为 <VALUE>。
正常构建阶段目标锁应以 GRANTED 为主;若出现 PENDING,应立即定位阻塞会话。如果 ALTER 很快完成,观测查询可能来不及捕获正在执行的会话;这不代表观测方法无效,只说明测试数据规模不足以模拟大表窗口。
验证不可见索引
索引构建成功后先确认它确实存在且仍为不可见,避免尚未验证就改变其他业务连接的执行计划。
SELECT
INDEX_NAME,
SEQ_IN_INDEX,
COLUMN_NAME,
IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'app'
AND TABLE_NAME = 'orders'
AND INDEX_NAME = 'idx_status_created'
ORDER BY SEQ_IN_INDEX;预期结果
idx_status_created | 1 | status | NO
idx_status_created | 2 | created_at | NO只在测试会话中允许优化器使用不可见索引,再执行目标 SQL 的运行时计划。这个开关只是让不可见索引进入优化器候选,不保证成本模型一定选择它。
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN ANALYZE
SELECT id, status, created_at
FROM app.orders
WHERE status = 'PAID'
AND created_at >= NOW() - INTERVAL 1 DAY
ORDER BY created_at
LIMIT 100;预期结果
执行计划可能出现 idx_status_created,也可能因成本估算继续选择其他访问路径。
实际 rows、loops 与耗时为 <VALUE>;应与发布前基线比较,并确认是否真的改善了目标 SQL。只有当目标 SQL 改善、关联 SQL 无明显回归、数据库负载仍在允许范围内时,才进入可见性切换。
注入 MDL 阻塞并练习中止
为了演练“变更本身没问题,但最终切换被长事务卡住”的场景,先在会话 A 开启事务并访问目标表,故意不提交。该事务会持续到事务结束。
START TRANSACTION;
SELECT id
FROM app.orders
WHERE id = <ID>;预期结果
返回订单 id = <ID>,事务保持未提交状态。在会话 B 尝试把索引切为可见。由于这一步需要获取相应的元数据锁,它应进入等待,而不是立即完成。
SET SESSION lock_wait_timeout = 300;
ALTER TABLE app.orders
ALTER INDEX idx_status_created VISIBLE;预期结果
会话暂不返回,等待 metadata lock。在会话 C 查询锁关系与等待中的 DDL 连接 ID。
SELECT
ml.LOCK_TYPE,
ml.LOCK_STATUS,
ml.OWNER_THREAD_ID,
t.PROCESSLIST_ID,
t.PROCESSLIST_TIME,
t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.threads AS t
ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_SCHEMA = 'app'
AND ml.OBJECT_NAME = 'orders'
ORDER BY ml.LOCK_STATUS, t.PROCESSLIST_TIME DESC;预期结果
至少能看到一个已授予的事务相关元数据锁,以及一个与 ALTER 相关的 PENDING 元数据锁。
等待中的 ALTER 连接 ID 为 <ID>。假设此时等待时间已经超过发布单允许值,练习按“停止发布”处理:从会话 C 中止等待中的 ALTER,而不是先粗暴终止业务长事务。
KILL QUERY <ID>;预期结果
Query OK, 0 rows affected会话 B 随后应收到查询被中断的错误;会话 A 再结束事务,释放阻塞。
ROLLBACK;预期结果
Query OK, 0 rows affected最后确认索引仍然不可见,证明“中止可见性切换”没有把验证中的索引暴露给优化器。
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'app'
AND TABLE_NAME = 'orders'
AND INDEX_NAME = 'idx_status_created';预期结果
idx_status_created | NO做回退判断与发布复盘
本次演练把索引先建成不可见,因此遇到异常时有三种明确状态:构建失败则无需回退;构建成功但尚未可见时,可以保留不可见索引等待下个窗口,也可以删除;如果已经切为可见后发现执行计划回归,优先把索引重新设为不可见,先恢复优化器行为,再安排后续清理。
若索引已经上线后需要快速逻辑回退,可执行:
ALTER TABLE app.orders
ALTER INDEX idx_status_created INVISIBLE;预期结果
Query OK, 0 rows affected (<TIME>)确认不再需要该索引后,再在合适窗口删除它:
ALTER TABLE app.orders
DROP INDEX idx_status_created,
ALGORITHM = INPLACE,
LOCK = NONE;预期结果
Query OK, 0 rows affected (<TIME>)复盘时至少记录变更开始与结束时间、表容量、实际算法和锁策略、MDL 等待、资源与复制指标峰值、目标 SQL 前后计划、异常注入点、中止动作以及最终索引状态。真正可复用的成果不是“ALTER 成功”这一行日志,而是一条任何值班同事都能照着执行、观察、停止和回退的变更路径。