详细解析了 MySQL Online DDL 的三种核心算法：INSTANT、INPLACE 和 COPY。INSTANT 仅修改元数据，速度最快且不阻塞；INPLACE 原地修改数据文件，轻度阻塞；COPY 创建新表复制数据，重度阻塞但兼容性好。文章对比了三者的实现原理、适用场景、锁机制及性能特征，并提供了生产环境中的最佳实践、故障处理策略及优化技巧，帮助开发者在面试及实际工作中选择合适的 DDL 方案。
| 特性维度 | INSTANT 算法 | INPLACE 算法 | COPY 算法 |
|---|---|---|---|
| 实现原理 | 仅修改元数据 | 原地修改数据文件 | 创建新表复制数据 |
| 阻塞级别 | 瞬间完成，基本不阻塞 | 轻度阻塞（短暂 MDL 锁） | 重度阻塞（长 MDL 锁） |
| 磁盘空间 | 几乎不占用额外空间 | 需要临时空间（索引重建） | 需要双倍空间 |
| 执行速度 | 亚秒级 | 秒到分钟级 | 分钟到小时级 |
| 适用版本 | MySQL 8.0.12+ | MySQL 5.6+ | 所有版本 |
| 回滚能力 | 支持（快速回滚） | 支持（中间状态） | 不支持 |
-- INSTANT 算法执行示例
ALTER TABLE users ADD COLUMN last_login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ALGORITHM=INSTANT;
核心原理：
class InstantAlgorithm:
""" INSTANT 算法：仅修改数据字典（元数据），不修改实际数据文件 """
def execute(self, table, operation):
""" 执行流程：
1. 获取 MDL 锁（极短暂）
2. 在表数据字典中添加新列的元数据
3. 更新数据字典中的列默认值
4. 释放 MDL 锁
"""
# 只修改元数据，不接触用户数据
metadata_change_only = True
# 在表的元数据中增加新列描述
alter_metadata({
'table_name': table.name,
'operation': 'ADD_COLUMN',
'column': {
'name': 'last_login_time',
'type': 'TIMESTAMP',
'default': 'CURRENT_TIMESTAMP',
'position': 'LAST'
}
})
# 表数据文件（.ibd）完全不修改
return "Operation completed instantly"
-- MySQL 8.0 支持的 INSTANT 操作
-- 1. 添加列（默认到最后，8.0.12+ 支持任意位置）
ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INSTANT;
ALTER TABLE t1 ADD COLUMN c INT AFTER b, ALGORITHM=INSTANT;
-- 2. 删除列（8.0.29+）
ALTER TABLE t1 DROP COLUMN c, ALGORITHM=INSTANT;
-- 3. 重命名列（8.0.28+）
ALTER TABLE t1 RENAME COLUMN a TO b, ALGORITHM=INSTANT;
-- 4. 设置/删除默认值
ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 100, ALGORITHM=INSTANT;
ALTER TABLE t1 ALTER COLUMN c DROP DEFAULT, ALGORITHM=INSTANT;
-- 5. 修改索引可见性（8.0.23+）
ALTER TABLE t1 ALTER INDEX idx_name INVISIBLE, ALGORITHM=INSTANT;
ALTER TABLE t1 ALTER INDEX idx_name VISIBLE, ALGORITHM=INSTANT;
-- 6. 修改 ENUM/SET 值添加（末尾添加）
ALTER TABLE t1 MODIFY COLUMN status ENUM('active','inactive','pending'), ALGORITHM=INSTANT;
class InstantAlgorithmLimitations:
""" INSTANT 算法限制分析 """
def check_limitations(self, operation):
limitations = {
'row_format_requirement': '表必须是 DYNAMIC 或 COMPRESSED 格式',
'data_directory_requirement': '不能有 DATA DIRECTORY 子句',
'temporary_table': '不能是临时表',
'fulltext_index': '不能有 FULLTEXT 索引',
'spatial_index': '不能有 SPATIAL 索引',
'compressed_table': '不能是 COMPRESSED 格式',
'encrypted_table': '不能是加密表',
'max_instant_cols': 64, # MySQL 8.0.29 后移除限制
'partition_table': '不支持分区表',
'virtual_columns': '不支持虚拟列操作'
}
return limitations
def internal_implementation(self):
""" 内部实现机制：
1. 在表结构末尾添加 instant 列位图
2. 新列数据存储在现有行的末尾
3. 对于已有数据，新列为 NULL 或默认值
4. 新插入数据包含新列值
"""
return {
'instant_bitmap': '在表空间头存储哪些列是 instant 添加的',
'column_count': '原始列数和 instant 列数分开存储',
'default_values': '通过数据字典存储默认值',
'space_management': '使用现有的未使用空间存储新列'
}
-- INPLACE 算法执行示例
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
核心原理：
class InplaceAlgorithm:
""" INPLACE 算法：原地修改数据文件，不需要创建新表 """
def execute(self, table, operation):
""" 三阶段执行流程：
阶段 1: Prepare（准备阶段）
- 创建临时文件（.tmp）
- 准备需要的资源
- 获取共享 MDL 锁
阶段 2: Execute（执行阶段）
- 在原表空间内修改数据
- 重建索引（如需要）
- 更新数据字典
阶段 3: Commit（提交阶段）
- 提交修改
- 释放锁
- 清理临时文件
"""
phases = {
'phase1_prepare': {
'duration': '短暂',
'lock': 'MDL_SHARED_UPGRADABLE',
'operations': [
'验证操作可行性',
'分配临时空间',
'创建临时文件',
'初始化排序缓冲区'
]
},
'phase2_execute': {
'duration': '主要耗时阶段',
'lock': 'MDL_EXCLUSIVE（短暂）',
'operations': {
'add_index': '读取数据 -> 排序 -> 构建 B+ 树',
'drop_index': '标记索引为删除 -> 空间回收',
'optimize_table': '碎片整理，页重组'
}
},
'phase3_commit': {
'duration': '短暂',
'lock': 'MDL_EXCLUSIVE（极短）',
'operations': [
'原子切换新结构',
'更新数据字典',
'清理临时文件',
]
}
}
phases
-- INPLACE 支持的主要操作
-- 1. 索引操作（最常用）
ALTER TABLE orders ADD INDEX idx_order_date (order_date), ALGORITHM=INPLACE;
ALTER TABLE orders DROP INDEX idx_order_date, ALGORITHM=INPLACE;
ALTER TABLE orders RENAME INDEX old_name TO new_name, ALGORITHM=INPLACE;
-- 2. 修改列类型（有限制）
ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED, ALGORITHM=INPLACE;
-- 3. 添加/删除外键（8.0+）
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id), ALGORITHM=INPLACE;
ALTER TABLE orders DROP FOREIGN KEY fk_user, ALGORITHM=INPLACE;
-- 4. 修改表选项
ALTER TABLE users ROW_FORMAT=DYNAMIC, ALGORITHM=INPLACE;
ALTER TABLE users CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
-- 5. 空间列操作
ALTER TABLE locations ADD SPATIAL INDEX idx_point (point), ALGORITHM=INPLACE;
-- 6. 重建表（相当于 OPTIMIZE TABLE）
ALTER TABLE users FORCE, ALGORITHM=INPLACE;
class InplaceRebuildAlgorithm:
""" 原地重建算法：需要重建整个表，但仍在原表空间 """
def analyze_operations(self):
return {
'requires_full_rebuild': [
'修改列的数据类型（如 INT -> BIGINT）',
'修改字符集（需要重新编码）',
'修改 ROW_FORMAT',
'删除主键',
'添加/删除自增列',
'OPTIMIZE TABLE（碎片整理）'
],
'partial_rebuild': [
'添加二级索引（仅重建索引）',
'删除二级索引（仅更新元数据）',
'修改索引类型',
'修改索引可见性'
],
'performance_characteristics': {
'disk_space': '需要额外空间存储中间数据',
'memory_usage': '使用 sort buffer 排序',
'io_pattern': '顺序读取，随机写入',
'parallelism': 'MySQL 8.0 支持并行 DDL'
}
}
def execute_optimize_table(self):
""" OPTIMIZE TABLE 的 INPLACE 实现
注意：实际上会创建临时表，但使用 INPLACE 算法
"""
steps = [
'1. 创建与原表结构相同的临时表',
'2. 逐行从原表读取数据，插入临时表（重建行）',
'3. 删除原表，重命名临时表',
'4. 重建索引'
]
return {
'actual_algorithm': 'COPY 但标记为 INPLACE',
'reason': '需要重建整个表来消除碎片',
'space_requirement':
}
-- COPY 算法执行示例
ALTER TABLE employees ADD COLUMN middle_name VARCHAR(50), ALGORITHM=COPY;
核心原理：
class CopyAlgorithm:
""" COPY 算法：创建新表，复制数据，替换原表 """
def execute(self, table, operation):
""" 五阶段执行流程：
阶段 1: 创建临时表
- 根据新结构创建临时表（#sql-xxx）
- 在新表上创建所有索引
阶段 2: 复制数据
- 逐行从原表读取数据
- 应用转换（如类型转换）
- 写入临时表
阶段 3: 同步数据
- 在复制期间应用 DML 操作
- 使用触发器捕获变更
阶段 4: 切换表
- 获取排他锁
- 重命名表：原表 -> 备份表，临时表 -> 原表
阶段 5: 清理
- 删除备份表
- 删除触发器
- 释放锁
"""
resource_requirements = {
'disk_space': '原表大小 * 2（最坏情况）',
'memory': '取决于 buffer pool 配置',
'io_operations': '全表扫描 + 全表写入',
'lock_duration': '整个复制过程持有排他锁',
'dml_blocking': '完全阻塞读写'
}
return resource_requirements
-- COPY 算法使用的触发器（MySQL 内部自动创建）
-- 1. 复制开始前创建触发器
CREATE TRIGGER trg_employees_insert AFTER INSERT ON employees FOR EACH ROW BEGIN
INSERT INTO tmp_employees VALUES (NEW.*);
END;
CREATE TRIGGER trg_employees_update AFTER UPDATE ON employees FOR EACH ROW BEGIN
UPDATE tmp_employees SET ... WHERE id = OLD.id;
END;
CREATE TRIGGER trg_employees_delete AFTER DELETE ON employees FOR EACH ROW BEGIN
DELETE FROM tmp_employees WHERE id = OLD.id;
END;
class CopyAlgorithmTriggers:
""" COPY 算法的触发器同步机制 """
def explain_sync_mechanism(self):
return {
'trigger_creation': {
'timing': '数据复制开始前',
'types': ['INSERT', 'UPDATE', 'DELETE'],
'purpose': '捕获 DDL 执行期间的 DML 变更'
},
'data_sync_flow': {
'step1': '用户执行 DML 操作',
'step2': '触发器捕获变更',
'step3': '应用到临时表',
'step4': '继续复制剩余数据'
},
'performance_impact': {
'dml_overhead': '每个 DML 操作额外执行触发器逻辑',
'trigger_maintenance': '需要维护触发器元数据',
'locking_issues': '可能产生死锁'
}
}
class AlgorithmComparison:
""" 三大算法详细对比分析 """
def comparison_matrix(self):
return {
'algorithm_comparison': {
'INSTANT': {
'blocking_level': 'LOW（仅元数据锁）',
'disk_io': '几乎为零',
'dml_impact': '几乎无影响',
'disk_space': '可忽略',
'recovery': '秒级回滚',
'applicability': '有限操作',
'version_requirement': 'MySQL 8.0.12+'
},
'INPLACE': {
'blocking_level': 'MEDIUM（分阶段锁）',
'disk_io': '中度（取决于数据量）',
'dml_impact': '阶段 2 短暂阻塞',
'disk_space': '需要临时空间',
'recovery': '可中断，有中间状态',
'applicability': '大部分 DDL 操作',
'version_requirement': 'MySQL 5.6+'
},
'COPY': {
'blocking_level': 'HIGH（全程排他锁）',
'disk_io': '重度（全表读写）',
'dml_impact': '完全阻塞',
: ,
: ,
: ,
:
}
},
: {
: {
: ,
: ,
:
},
: {
: ,
: ,
:
},
: {
: ,
: ,
:
},
: {
: ,
: ,
:
},
: {
: ,
: ,
:
},
: {
: ,
: ,
:
}
}
}
-- 锁机制对比示例
-- INSTANT 算法：极短暂的 MDL 锁
LOCK TABLES users WRITE; -- 用户手动锁，INSTANT 不需要
-- INPLACE 算法：阶段性的 MDL 锁
-- 阶段 1: MDL_SHARED_UPGRADABLE（允许读）
-- 阶段 2: MDL_EXCLUSIVE（短暂，阻塞所有操作）
-- 阶段 3: MDL_EXCLUSIVE（极短，提交变更）
-- COPY 算法：长时间的 MDL_EXCLUSIVE 锁
-- 整个 DDL 过程都持有排他锁
class LockMechanismAnalysis:
""" 三大算法的锁机制分析 """
def analyze_lock_patterns(self):
return {
'MDL_lock_types': {
'MDL_SHARED': '允许并发读，阻止写',
'MDL_SHARED_UPGRADABLE': '允许并发读，准备升级',
'MDL_EXCLUSIVE': '完全独占，阻止所有操作'
},
'algorithm_lock_usage': {
'INSTANT': {
'lock_type': 'MDL_EXCLUSIVE（极短暂）',
'duration': '亚秒级',
'concurrent_dml': '几乎无阻塞',
'lock_contention': '极低'
},
'INPLACE': {
'phase1': {
'lock': 'MDL_SHARED_UPGRADABLE',
'duration': '准备时间',
'dml': '允许读写'
},
'phase2': {
'lock': 'MDL_EXCLUSIVE',
'duration': '执行时间（主耗时）',
'dml': '完全阻塞'
},
'phase3': {
'lock': 'MDL_EXCLUSIVE',
'duration': '提交时间',
:
}
},
: {
: ,
: ,
: ,
:
}
},
: {
: [
,
,
,
],
: [
,
,
,
]
}
}
class AlgorithmDecisionTree:
""" Online DDL 算法选择决策树 """
def decide_algorithm(self, operation, mysql_version, table_size):
""" 决策逻辑：
1. 检查 MySQL 版本是否支持 INSTANT
2. 检查操作是否支持 INSTANT
3. 检查 INPLACE 是否支持
4. 默认使用 COPY
"""
decision_tree = {
'step1': '检查 MySQL 版本 >= 8.0.12？',
'step2': '检查操作是否支持 INSTANT？',
'step3': '检查操作是否支持 INPLACE？',
'step4': '使用 COPY 算法',
'instant_criteria': {
'mysql_version': '>= 8.0.12',
'table_format': 'DYNAMIC 或 COMPRESSED',
'operation_type': [
'ADD_COLUMN（任意位置）',
'DROP_COLUMN（8.0.29+）',
'RENAME_COLUMN',
'SET/DROP DEFAULT',
'索引可见性修改'
]
},
'inplace_criteria': {
'mysql_version': '>= 5.6',
'operation_type': [
'ADD/DROP INDEX',
'ADD/DROP FOREIGN KEY',
'RENAME TABLE',
'修改表选项',
'OPTIMIZE TABLE'
],
'considerations': [
'表大小影响执行时间',
'需要临时磁盘空间',
'阶段 2 会短暂阻塞'
]
},
: {
: [
,
,
,
],
: [
,
,
]
}
}
decision_tree
-- 1. 预检查：查看 DDL 执行计划
ALTER TABLE users ADD COLUMN last_ip VARCHAR(45), ALGORITHM=INPLACE, LOCK=NONE;
-- 查看实际执行计划
SHOW WARNINGS;
-- 2. 测试环境验证
-- 在测试环境执行相同 DDL，观察影响
EXPLAIN ALTER TABLE test_users ADD COLUMN last_ip VARCHAR(45), ALGORITHM=INPLACE, LOCK=NONE;
-- 3. 监控执行进度（MySQL 8.0+）
-- 开启 performance_schema
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%stage/innodb/alter%';
-- 查看 DDL 进度
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,
(WORK_COMPLETED/WORK_ESTIMATED)*100 as progress_pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';
-- 4. 分批执行大表 DDL
-- 方法 1：使用 pt-online-schema-change
-- 方法 2：手动分批（添加列示例）
ALTER TABLE big_table ADD COLUMN new_col INT DEFAULT 0, ALGORITHMINSTANT;
big_table new_col , ALGORITHM;
big_table new_col id ;
big_table new_col id ;
class DDLFailureRecovery:
""" Online DDL 故障处理策略 """
def handle_failure_scenarios(self):
scenarios = {
'instant_algorithm_failure': {
'cause': '不满足 INSTANT 条件但指定了 ALGORITHM=INSTANT',
'symptoms': '立即报错，无影响',
'recovery': '重新执行合适的算法',
'sql': "ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INPLACE;"
},
'inplace_algorithm_failure': {
'cause': '磁盘空间不足、内存不足、锁超时',
'symptoms': 'DDL 中断，表处于中间状态',
'recovery_steps': [
'检查错误日志',
'查看 INFORMATION_SCHEMA.INNODB_TABLES',
'如果有临时表，手动清理',
'重启 MySQL（最后手段）'
],
'prevention': [
'确保足够磁盘空间',
'设置合理的 lock_wait_timeout',
'监控长事务'
]
},
'copy_algorithm_failure': {
'cause': '磁盘空间不足、进程被 kill、服务器重启',
'symptoms': '遗留#sql-xxx 临时表',
'recovery': {
'case1': '临时表存在，原表存在 → 手动清理临时表',
'case2': '临时表存在，原表丢失 → 重命名临时表',
'case3': '原表存在，临时表丢失 → 原表正常',
:
},
: [
,
]
},
: {
: ,
: ,
: [
,
,
,
],
: [
,
]
}
}
scenarios
-- MySQL 8.0.14+ 支持并行创建索引
SET SESSION innodb_ddl_threads = 4; -- 设置并行线程数
SET SESSION innodb_ddl_buffer_size = 1048576; -- 设置 DDL 缓冲区
ALTER TABLE large_table ADD INDEX idx_column (column_name), ALGORITHM=INPLACE, LOCK=SHARED;
-- 查看并行 DDL 状态
SELECT * FROM performance_schema.events_statements_current
WHERE SQL_TEXT LIKE '%ALTER TABLE%';
-- 配置并行 DDL 参数
SHOW VARIABLES LIKE 'innodb_parallel%';
/* innodb_parallel_read_threads = 4
innodb_ddl_threads = 4
innodb_ddl_buffer_size = 1048576 */
class LargeTableDDLOptimization:
""" 大表 Online DDL 优化策略 """
def optimization_strategies(self):
return {
'strategy_1': {
'name': '分阶段执行',
'description': '将一个大 DDL 拆分成多个小 DDL',
'example': [
'1. 添加允许 NULL 的列（INSTANT）',
'2. 分批更新默认值（避免长事务）',
'3. 修改列为 NOT NULL（INPLACE）'
],
'benefits': '减少单次锁持有时间'
},
'strategy_2': {
'name': '利用从库先行',
'description': '先在从库执行，验证后再在主库执行',
'steps': [
'1. 在从库执行 DDL',
'2. 监控复制延迟',
'3. 验证业务影响',
'4. 主库低峰期执行'
],
'tools': 'pt-online-schema-change, gh-ost'
},
'strategy_3': {
'name': '智能算法选择',
'description': '根据表大小和操作类型选择最优算法',
'decision_matrix': {
'small_table(<10GB)': '根据操作选择',
'medium_table(10-100GB)': '优先 INPLACE',
'large_table(>100GB)': '使用外部工具'
},
: [
,
,
]
},
: {
: ,
: ,
: {
: ,
: ,
: ,
:
},
: [
,
,
]
}
}
():
{
: ,
: {
: ,
: ,
: ,
: ,
:
},
: [
,
,
,
],
: [
,
,
,
]
}
关键区别：
Q1：什么情况下 INPLACE 算法实际上需要复制整个表？
答：INPLACE 算法在以下情况需要重建整个表：
Q2：如何判断一个 DDL 操作使用哪种算法？
答：可以通过以下方式：
Q3：Online DDL 有哪些限制和风险？
答：主要限制和风险包括：
Q4：MySQL 8.0 在 Online DDL 方面有哪些重要改进？
答：MySQL 8.0 的重要改进：
这个完整的 Online DDL 三大算法解析，从基础原理到生产实践，涵盖了面试和工作中的各种场景，帮助你深入理解 MySQL 的 DDL 机制。
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
使用加密算法（如AES、TripleDES、Rabbit或RC4）加密和解密文本明文。 在线工具，加密/解密文本在线工具，online
查找任何按下的键的javascript键代码、代码、位置和修饰符。 在线工具，Keycode 信息在线工具，online
JavaScript 字符串转义/反转义；Java 风格 \uXXXX（Native2Ascii）编码与解码。 在线工具，Escape 与 Native 编解码在线工具，online
使用 Prettier 在浏览器内格式化 JavaScript 或 HTML 片段。 在线工具，JavaScript / HTML 格式化在线工具，online
Terser 压缩、变量名混淆，或 javascript-obfuscator 高强度混淆（体积会增大）。 在线工具，JavaScript 压缩与混淆在线工具，online
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online