Cuizhy Tech

Appearance

MySQL 表结构维护 (DDL) ​

本文档汇总了 MySQL 日常运维中常见的表结构维护操作，包括字段管理、索引维护等，并提供生产环境的最佳实践。

1. 字段管理 (Column) ​

增加字段 (Add Column) ​

在日常运维中，业务需求变更常常需要对现有的数据库表结构进行调整。

基础语法 ​

sql
ALTER TABLE table_name 
ADD COLUMN column_name column_definition [FIRST | AFTER existing_column];

示例 ​

假设有一个用户表 users，我们需要增加一个 age 字段，类型为 INT，默认值为 0，并添加注释。

sql
ALTER TABLE users 
ADD COLUMN age INT NOT NULL DEFAULT 0 COMMENT '年龄' AFTER name;
  • AFTER name: 表示将新字段放在 name 字段之后。如果不指定位置，默认添加到最后。
  • FIRST: 表示将新字段放在第一列。

批量增加字段 ​

如果需要一次性增加多个字段，可以在一条语句中完成，这样效率更高，只需要重建一次表（取决于 MySQL 版本和算法）。

sql
ALTER TABLE users 
ADD COLUMN age INT NOT NULL DEFAULT 0 COMMENT '年龄',
ADD COLUMN phone VARCHAR(20) DEFAULT '' COMMENT '手机号';

修改字段 (Modify/Change Column) ​

修改字段属性 (MODIFY) ​

只修改字段类型或属性，不改名。

sql
-- 将 age 字段修改为 TINYINT
ALTER TABLE users MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0;

修改字段名称及属性 (CHANGE) ​

同时修改字段名和属性。

sql
-- 将 phone 字段重命名为 mobile，并修改长度
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20) NOT NULL DEFAULT '';

删除字段 (Drop Column) ​

sql
ALTER TABLE users DROP COLUMN age;

2. 生产环境注意事项 ​

在生产环境中对大表（百万级以上数据量）进行 DDL 操作需要格外谨慎，可能会导致锁表，影响线上业务。

1. 锁表风险 ​

  • MySQL 5.6+: 支持 Online DDL，大部分 ALTER TABLE 操作支持 ALGORITHM=INPLACE, LOCK=NONE，即在修改表结构时允许并发 DML（增删改）操作。
  • MySQL 5.6 之前: ALTER TABLE 会锁全表，导致业务中断。

2. 推荐方案 (针对大表) ​

对于数据量巨大的表（如千万级、亿级），直接执行 ALTER TABLE 即使有 Online DDL 也可能因为元数据锁（MDL）或资源消耗过大导致主从延迟或性能抖动。推荐使用工具：

  • pt-online-schema-change (pt-osc): Percona Toolkit 的一部分，通过创建新表+触发器的方式实现无锁变更。
  • gh-ost: GitHub 开源的在线 schema 变更工具，基于 Binlog，无触发器，对负载影响更小。

3. 即时加列 (Instant Add Column) ​

  • MySQL 8.0.12+: 支持 ALGORITHM=INSTANT。
  • 特点: 只修改元数据，不重组数据文件，速度极快（毫秒级），无论表多大。
  • 限制: 只能在表最后添加字段，不支持 AFTER 指定位置（MySQL 8.0.29+ 解除了部分限制）。
sql
-- 强制使用 INSTANT 算法（如果不支持会报错）
ALTER TABLE users ADD COLUMN email VARCHAR(100), ALGORITHM=INSTANT;