MySQL / 系统课时

完成一次端到端大表变更演练

完成一次端到端大表变更演练

本课用一次“为大表新增联合索引”的演练串起完整发布闭环:先预检版本、规模、事务与 MDL,再在线构建并观测,随后验证不可见索引,注入阻塞练习中止与回退,最后形成可复用的发布记录。

学习目标

  • 能在执行大表 ALTER 前确认 MySQL 8.4、表规模、长事务、MDL 和目标索引状态。
  • 能用 ALGORITHM=INPLACE、LOCK=NONE 执行在线索引构建,并从独立连接观察执行与等待。
  • 能利用不可见索引、异常注入和回退动作完成一次接近真实发布窗口的演练。

设定演练对象与退出条件

以下以 MySQL 8.4 InnoDB 表 app.orders 为例,目标是新增联合索引 idx_status_created(status, created_at)。索引先保持不可见,验证通过后再切换为可见,把“构建索引”和“让优化器开始使用索引”拆成两个可控动作。

大表在线 DDL 从预检到复盘的端到端演练闭环

演练开始前先写明退出条件,而不是看到异常后临时讨论。至少包括:MDL 等待超过发布单阈值、数据库负载超过既定阈值、复制延迟持续扩大、核心 SQL 延迟恶化,以及无法解释的错误或告警。阈值应来自本系统基线,本课不编造固定数值。

做变更前预检

先确认版本、表规模以及同名索引是否已经存在。TABLE_ROWS 对 InnoDB 是估算值,容量判断应同时关注数据和索引字节数。

sql
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';

预期结果

text
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 即使允许并发读写,也仍可能在开始或提交阶段等待元数据锁。

sql
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;

预期结果

text
长事务数量与持续时间应处于发布单允许范围内。
metadata_locks 中不应存在无法解释的 PENDING 锁。

在线执行并实时观测

正式执行前给当前会话设置有限的元数据锁等待时间,避免在入口阶段无限等待。显式指定 ALGORITHM=INPLACE 和 LOCK=NONE 后,如果当前版本或变更形式不支持该组合,语句会报错,而不是悄悄采用未明确接受的算法或锁策略。

sql
SET SESSION lock_wait_timeout = 30;

ALTER TABLE app.orders
    ADD INDEX idx_status_created (status, created_at) INVISIBLE,
    ALGORITHM = INPLACE,
    LOCK = NONE;

预期结果

text
Query OK, 0 rows affected (<TIME>)
Records: 0  Duplicates: 0  Warnings: 0

<TIME> 取决于表大小、磁盘与并发负载。演练时不要只盯着客户端是否“还在跑”,应从另一连接观察 ALTER 会话和目标表的锁状态。

sql
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;

预期结果

text
ALTER 执行期间可看到对应会话,PROCESSLIST_STATE 为 <VALUE>。
正常构建阶段目标锁应以 GRANTED 为主;若出现 PENDING,应立即定位阻塞会话。

如果 ALTER 很快完成,观测查询可能来不及捕获正在执行的会话;这不代表观测方法无效,只说明测试数据规模不足以模拟大表窗口。

验证不可见索引

索引构建成功后先确认它确实存在且仍为不可见,避免尚未验证就改变其他业务连接的执行计划。

sql
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;

预期结果

text
idx_status_created | 1 | status     | NO
idx_status_created | 2 | created_at | NO

只在测试会话中允许优化器使用不可见索引,再执行目标 SQL 的运行时计划。这个开关只是让不可见索引进入优化器候选,不保证成本模型一定选择它。

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;

预期结果

text
执行计划可能出现 idx_status_created,也可能因成本估算继续选择其他访问路径。
实际 rows、loops 与耗时为 <VALUE>;应与发布前基线比较,并确认是否真的改善了目标 SQL。

只有当目标 SQL 改善、关联 SQL 无明显回归、数据库负载仍在允许范围内时,才进入可见性切换。

注入 MDL 阻塞并练习中止

为了演练“变更本身没问题,但最终切换被长事务卡住”的场景,先在会话 A 开启事务并访问目标表,故意不提交。该事务会持续到事务结束。

sql
START TRANSACTION;

SELECT id
FROM app.orders
WHERE id = <ID>;

预期结果

text
返回订单 id = <ID>,事务保持未提交状态。

在会话 B 尝试把索引切为可见。由于这一步需要获取相应的元数据锁,它应进入等待,而不是立即完成。

sql
SET SESSION lock_wait_timeout = 300;

ALTER TABLE app.orders
    ALTER INDEX idx_status_created VISIBLE;

预期结果

text
会话暂不返回,等待 metadata lock。

在会话 C 查询锁关系与等待中的 DDL 连接 ID。

sql
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;

预期结果

text
至少能看到一个已授予的事务相关元数据锁,以及一个与 ALTER 相关的 PENDING 元数据锁。
等待中的 ALTER 连接 ID 为 <ID>。

假设此时等待时间已经超过发布单允许值,练习按“停止发布”处理:从会话 C 中止等待中的 ALTER,而不是先粗暴终止业务长事务。

sql
KILL QUERY <ID>;

预期结果

text
Query OK, 0 rows affected

会话 B 随后应收到查询被中断的错误;会话 A 再结束事务,释放阻塞。

sql
ROLLBACK;

预期结果

text
Query OK, 0 rows affected

最后确认索引仍然不可见,证明“中止可见性切换”没有把验证中的索引暴露给优化器。

sql
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders'
  AND INDEX_NAME = 'idx_status_created';

预期结果

text
idx_status_created | NO

做回退判断与发布复盘

本次演练把索引先建成不可见,因此遇到异常时有三种明确状态:构建失败则无需回退;构建成功但尚未可见时,可以保留不可见索引等待下个窗口,也可以删除;如果已经切为可见后发现执行计划回归,优先把索引重新设为不可见,先恢复优化器行为,再安排后续清理。

若索引已经上线后需要快速逻辑回退,可执行:

sql
ALTER TABLE app.orders
    ALTER INDEX idx_status_created INVISIBLE;

预期结果

text
Query OK, 0 rows affected (<TIME>)

确认不再需要该索引后,再在合适窗口删除它:

sql
ALTER TABLE app.orders
    DROP INDEX idx_status_created,
    ALGORITHM = INPLACE,
    LOCK = NONE;

预期结果

text
Query OK, 0 rows affected (<TIME>)

复盘时至少记录变更开始与结束时间、表容量、实际算法和锁策略、MDL 等待、资源与复制指标峰值、目标 SQL 前后计划、异常注入点、中止动作以及最终索引状态。真正可复用的成果不是“ALTER 成功”这一行日志,而是一条任何值班同事都能照着执行、观察、停止和回退的变更路径。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

完成一次端到端大表变更演练

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通