MySQL大表DDL的最佳实践 - 详解

MySQL 大表 DDL 操作最佳实践文档

目录

• 1. 概述
• 2. MySQL 版本差异
• 3. 大表加字段最佳实践
• 4. 大表加索引最佳实践
• 5. 工具推荐
• 6. 监控与回滚
• 7. 常见问题与解决方案
• 8. 执行脚本模板
• 9. 总结

---

1. 概述

大表 DDL 操作（加字段、加索引）是数据库运维中的高风险操作，可能导致：

• 长时间锁表，影响业务
• 大量磁盘 IO，影响性能
• 主从延迟
• 磁盘空间不足

本文档针对 MySQL 5.7 和 8.0 版本，提供最佳实践。

---

2. MySQL 版本差异

2.1 DDL 算法对比

特性	MySQL 5.7	MySQL 8.0
INSTANT 算法	❌ 不支持	✅ 8.0.12+ 支持
INPLACE 算法	✅ 部分支持	✅ 全面支持
COPY 算法	✅ 默认	⚠️ 降级使用
在线 DDL	⚠️ 有限支持	✅ 全面支持
进度监控	❌ 不支持	✅ 支持

2.1.1 INPLACE 算法版本支持详情

INPLACE 算法支持版本：

• MySQL 5.5+：开始支持 INPLACE 算法（部分操作）
• MySQL 5.6+：支持更多在线 DDL 操作
• MySQL 5.7+：支持大部分在线 DDL 操作
• MySQL 8.0+：全面支持 INPLACE 算法，支持更多操作类型

不同操作的支持情况：

操作类型	MySQL 5.5	MySQL 5.6	MySQL 5.7	MySQL 8.0
添加索引	✅ INPLACE	✅ INPLACE	✅ INPLACE	✅ INPLACE
删除索引	✅ INPLACE	✅ INPLACE	✅ INPLACE	✅ INPLACE
添加列（末尾）	⚠️ COPY	✅ INPLACE	✅ INPLACE	✅ INSTANT/INPLACE
添加列（非末尾）	❌ COPY	❌ COPY	❌ COPY	✅ INPLACE (8.0.29+)
删除列	❌ COPY	✅ INPLACE	✅ INPLACE	✅ INPLACE
修改列类型	❌ COPY	⚠️ 部分	⚠️ 部分	✅ INPLACE (部分)
重命名列	❌ COPY	✅ INPLACE	✅ INPLACE	✅ INSTANT
修改列默认值	❌ COPY	✅ INPLACE	✅ INPLACE	✅ INSTANT

注意：

• MySQL 5.5 和 5.6 对 INPLACE 的支持有限，建议使用 pt-online-schema-change
• MySQL 5.7 对大部分操作支持 INPLACE，但添加列到非末尾位置仍需要 COPY
• MySQL 8.0 全面支持，且很多操作可以使用 INSTANT 算法（更快）

2.2 版本检查

-- 检查 MySQL 版本
SELECT VERSION();
-- 检查 MySQL 版本号（判断支持的 DDL 算法）
SELECT
VERSION() AS mysql_version,
CASE
WHEN VERSION() >= '8.0.12' THEN '支持 INSTANT 和 INPLACE 算法'
WHEN VERSION() >= '8.0.0' THEN '支持 INPLACE 算法，不支持 INSTANT'
WHEN VERSION() >= '5.7.0' THEN '支持部分 INPLACE 算法，建议使用 pt-online-schema-change'
WHEN VERSION() >= '5.6.0' THEN '支持部分 INPLACE 算法，建议使用 pt-online-schema-change'
WHEN VERSION() >= '5.5.0' THEN '支持少量 INPLACE 算法，强烈建议使用 pt-online-schema-change'
ELSE '建议使用 pt-online-schema-change'
END AS ddl_support;
-- 检查 performance_schema 是否启用（用于监控 DDL 进度）
SHOW VARIABLES LIKE 'performance_schema';
-- 如果返回 OFF，则 DDL 进度监控功能不可用
-- 注意：即使 performance_schema 未启用，DDL 操作仍可正常执行
--       只是无法查看详细的执行进度，可以使用 SHOW PROCESSLIST 监控
-- 如需启用 performance_schema（需要重启 MySQL）：
-- 1. 修改配置文件 my.cnf：
--    [mysqld]
--    performance_schema = ON
-- 2. 重启 MySQL 服务
-- 注意：启用 performance_schema 会占用一定内存，请根据实际情况决定
-- 检查 InnoDB 相关配置
SHOW VARIABLES LIKE 'innodb%';

2.2.1 检查特定操作是否支持 INPLACE 算法

-- 方法 1：使用 EXPLAIN 查看 DDL 执行计划（MySQL 5.7+）
-- 注意：需要先创建一个测试表
CREATE TABLE IF NOT EXISTS test_ddl_check (
id INT PRIMARY KEY,
name VARCHAR(100)
);
-- 检查添加列是否支持 INPLACE
ALTER TABLE test_ddl_check
ADD COLUMN test_col VARCHAR(255) NULL,
ALGORITHM=INPLACE,
LOCK=NONE;
-- 查看执行结果，如果报错说明不支持 INPLACE
-- 如果成功，说明支持 INPLACE
-- 清理测试表
DROP TABLE IF EXISTS test_ddl_check;
-- 方法 2：查看官方文档或使用在线 DDL 支持矩阵
-- MySQL 8.0 官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
-- MySQL 5.7 官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

---

3. 大表加字段最佳实践

3.1 MySQL 8.0 最佳实践

3.1.1 使用 INSTANT 算法（推荐）

适用场景：

• 添加可空字段到表末尾
• 修改字段默认值
• 重命名字段/索引

语法：

-- 添加可空字段（推荐）
ALTER TABLE `table_name`
ADD COLUMN `new_column` VARCHAR(255) NULL COMMENT '字段说明',
ALGORITHM=INSTANT;
-- 添加字段到指定位置
ALTER TABLE `table_name`
ADD COLUMN `new_column` VARCHAR(255) NULL COMMENT '字段说明' AFTER `existing_column`,
ALGORITHM=INSTANT;
-- 添加 NOT NULL 字段（需要先添加可空，再更新，最后改 NOT NULL）
-- 步骤 1：添加可空字段
ALTER TABLE `table_name`
ADD COLUMN `new_column` VARCHAR(255) NULL COMMENT '字段说明',
ALGORITHM=INSTANT;
-- 步骤 2：更新数据
UPDATE `table_name` SET `new_column` = 'default_value' WHERE `new_column` IS NULL;
-- 步骤 3：改为 NOT NULL
ALTER TABLE `table_name`
MODIFY COLUMN `new_column` VARCHAR(255) NOT NULL COMMENT '字段说明',
ALGORITHM=INPLACE,
LOCK=NONE;

注意事项：

• INSTANT 算法几乎不锁表，秒级完成
• 不支持添加自增主键
• 不支持添加在非末尾位置（8.0.29+ 支持）

3.1.2 使用 INPLACE 算法

适用场景：

• INSTANT 不支持的操作
• 需要重建表的操作

语法：

ALTER TABLE `table_name`
ADD C