MySQL / 系统课时

安全新增与删除列

安全新增与删除列

本课以 MySQL 8.4 与 InnoDB 为基线,判断普通列的新增和删除能否走 ALGORITHM=INSTANT,并处理默认值、列位置、行版本上限与旧版本差异。目标是让不满足条件的变更立即失败,而不是意外退化为昂贵重建。

学习目标

  • 判断普通加列、删列是否满足 INSTANT 条件,并显式要求算法
  • 正确处理默认值、列位置与 MySQL 8.0 不同小版本的能力差异
  • 在删列前完成应用兼容检查,并用行版本指标识别长期治理风险

用显式 INSTANT 做失败保护

ALGORITHM=INSTANT 只修改数据字典元数据,不扫描和重写整表,因此执行时间基本不随表行数增长。生产环境应显式指定它:如果当前表结构或版本不支持,语句会直接报错,避免服务器自动选择 INPLACE 或 COPY 后才发现变更成本远超预期。

INSTANT 操作只允许默认锁策略,不要同时写 LOCK=NONE。以下示例在 status 后新增一个带默认值的普通列;MySQL 8.4 支持在任意位置进行 INSTANT 加列。

sql
CREATE DATABASE IF NOT EXISTS ddl_demo;
USE ddl_demo;

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    status VARCHAR(16) NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

INSERT INTO orders VALUES
(1, 'NEW', 99.00),
(2, 'PAID', 120.00);

ALTER TABLE orders
    ADD COLUMN risk_level TINYINT NOT NULL DEFAULT 0 AFTER status,
    ALGORITHM=INSTANT;

SELECT id, status, risk_level, amount
FROM orders
ORDER BY id;

预期结果

text
+----+--------+------------+--------+
| id | status | risk_level | amount |
+----+--------+------------+--------+
|  1 | NEW    |          0 |  99.00 |
|  2 | PAID   |          0 | 120.00 |
+----+--------+------------+--------+

旧行会按新增列定义读取默认值,不需要先把整表回填一遍。后续修改默认值和回填历史业务数据是两件事,不能把默认值变更当成历史数据修复。

版本与表结构决定是否真能 INSTANT

MySQL 8.0 的能力不是从一开始就完全相同。8.0.12 起支持 INSTANT 加列,但在 8.0.29 之前,INSTANT 新列只能放在表尾;从 8.0.29 起才支持任意位置加列,并加入 INSTANT 删列。MySQL 8.4 对支持的加列、删列会默认优先使用 INSTANT,但治理脚本仍建议显式写出算法。

普通列也并非始终可用 INSTANT。常见阻断条件包括 ROW_FORMAT=COMPRESSED、存在 FULLTEXT 索引、数据字典表空间中的表以及临时表;添加 AUTO_INCREMENT 列也不是普通的瞬时加列。删列时,如果目标列参与某些索引或其他受限结构,也可能无法直接 INSTANT。

新增与删除列选择 INSTANT 的判断路径

关注行版本而不是只看执行耗时

从 MySQL 8.0.29 开始,INSTANT 加列或删列会创建新的行版本。MySQL 8.4 的单表行版本上限是 64;频繁做瞬时加删列,最终会因为达到上限而拒绝新的 INSTANT 变更。可以在变更后把 TOTAL_ROW_VERSIONS 纳入结构治理检查。

sql
SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 'ddl_demo/orders';

预期结果

text
+-----------------+--------------------+
| NAME            | TOTAL_ROW_VERSIONS |
+-----------------+--------------------+
| ddl_demo/orders | <VALUE>            |
+-----------------+--------------------+

<VALUE> 取决于这张表历史上执行过多少次 INSTANT 加列或删列。一次 ALTER TABLE 即使同时加减多个列,也对应一次新的行版本;表重建类操作会重新整理物理记录并重置该计数。行版本接近上限时,不应继续把 INSTANT 当作无限资源,而应计划一次受控重建。

删列要先解决应用依赖

INSTANT 删列虽然快,但它不是可逆的“隐藏列”。一旦删掉,应用再读取或写入该列会立即报错,而且重新添加同名列也不能自动恢复旧值。因此安全删列应先完成代码与数据依赖治理,再执行 DDL。

建议采用两阶段发布:先发布不再读写目标列的应用版本,观察一个完整业务周期并检查 SQL、报表、ETL、触发器和下游同步;确认无依赖后再删列。下面删除刚才的 risk_level。

sql
ALTER TABLE orders
    DROP COLUMN risk_level,
    ALGORITHM=INSTANT;

SHOW COLUMNS FROM orders;

预期结果

text
Field   Type           Null  Key  Default  Extra
id      bigint         NO    PRI  NULL
status  varchar(16)    NO         NULL
amount  decimal(10,2)  NO         NULL

如果显式 ALGORITHM=INSTANT 失败,不要为了“让脚本跑过去”立刻改成默认算法。先确认失败原因、预计重建时间、临时空间、复制延迟和元数据锁风险,再决定是否安排 INPLACE 或 COPY 窗口。对大表而言,失败得早往往比自动降级后长时间占用资源更安全。

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

目录与阅读设置

记录一个想法

安全新增与删除列

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

登录哪都通