景德镇市网站建设_网站建设公司_Figma_seo优化

一、Online DDL 演进概述

1.MySQL Online DDL发展历程

text

复制

下载

MySQL DDL算法演进： ┌─────────────────────────────────────────────────────────────┐ │ MySQL 5.6 (2013) │ MySQL 5.7 (2015) │ MySQL 8.0 (2018+) │ ├─────────────────────────────────────────────────────────────┤ │ 引入Online DDL概念 │ 增强INPLACE算法 │ 引入INSTANT算法 │ │ 支持部分操作的在线执行 │ 减少重建表场景 │ 秒级添加列/索引 │ │ COPY算法为主 │ 支持在线重命名索引 │ 支持更多在线操作 │ └─────────────────────────────────────────────────────────────┘

2.三种算法核心对比

二、INSTANT算法详解

1.INSTANT算法原理

1.1实现机制

sql

复制

下载

-- MySQL 8.0.12+ 引入的INSTANT算法核心原理： -- 1. 元数据变更：仅修改数据字典，不修改数据行 -- 2. 版本兼容：通过行格式兼容新旧模式 -- 3. 延迟应用：查询时动态解析列信息 -- 查看表是否支持INSTANT SHOW CREATE TABLE users\G -- 结果中的"ROW_FORMAT=DYNAMIC"支持INSTANT -- 查询INSTANT列信息 SELECT * FROM information_schema.innodb_tables WHERE name LIKE '%users%'; -- 查看INSTANT添加的列 SELECT * FROM information_schema.innodb_columns WHERE table_name = 'users' AND has_default = 1;

1.2内存数据结构

c

复制

下载

// InnoDB内部数据结构（简化） struct dict_table_t { // 表元数据 char* name; uint32_t id; // INSTANT相关字段 uint32_t instant_cols; // INSTANT添加的列数 uint32_t total_cols; // 总列数 uint32_t n_instant_nullable;// 可空INSTANT列数 // 列信息数组 dict_col_t* cols; }; // 行格式支持 enum row_format_t { REDUNDANT, // ❌ 不支持INSTANT COMPACT, // ✅ 支持INSTANT (8.0.20+) DYNAMIC, // ✅ 支持INSTANT COMPRESSED // ✅ 支持INSTANT };

2.INSTANT支持的操作

2.1支持的操作列表

sql

复制

下载

-- 1. 添加列（最常用） ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) DEFAULT '' COMMENT '中间名', ALGORITHM=INSTANT; -- 2. 删除列（8.0.29+） ALTER TABLE users DROP COLUMN middle_name, ALGORITHM=INSTANT; -- 3. 重命名列（8.0.28+） ALTER TABLE users RENAME COLUMN username TO account_name, ALGORITHM=INSTANT; -- 4. 设置列默认值 ALTER TABLE users ALTER COLUMN status SET DEFAULT 1, ALGORITHM=INSTANT; -- 5. 删除列默认值 ALTER TABLE users ALTER COLUMN status DROP DEFAULT, ALGORITHM=INSTANT; -- 6. 修改ENUM/SET值（8.0.26+） ALTER TABLE users MODIFY COLUMN role ENUM('user','admin','superadmin') DEFAULT 'user', ALGORITHM=INSTANT; -- 7. 修改索引可见性（8.0+） ALTER TABLE users ALTER INDEX idx_email INVISIBLE, ALGORITHM=INSTANT;

2.2INSTANT限制条件

sql

复制

下载

-- ❌ 不支持INSTANT的场景 -- 1. 表空间限制 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=INNODB; CREATE TABLE t1 (id INT) TABLESPACE ts1; ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT; -- 失败 -- 2. 压缩表（某些版本） CREATE TABLE t1 (id INT) ROW_FORMAT=COMPRESSED; ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT; -- 可能失败 -- 3. 临时表 CREATE TEMPORARY TABLE tmp (id INT); ALTER TABLE tmp ADD COLUMN c1 INT, ALGORITHM=INSTANT; -- 失败 -- 4. 全文索引表 CREATE TABLE t1 (id INT, content TEXT, FULLTEXT(content)); ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT; -- 失败 -- 5. 分区表（部分操作） CREATE TABLE t1 (id INT) PARTITION BY HASH(id) PARTITIONS 4; ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT; -- 可能失败

3.INSTANT性能基准测试

sql

复制

下载

-- 性能对比测试 CREATE TABLE test_instant ( id BIGINT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -- 插入100万数据 INSERT INTO test_instant(data) SELECT UUID() FROM information_schema.columns c1, information_schema.columns c2 LIMIT 1000000; -- 测试INSTANT添加列 SET @start = NOW(6); ALTER TABLE test_instant ADD COLUMN new_col INT DEFAULT 0, ALGORITHM=INSTANT; SET @end = NOW(6); SELECT TIMESTAMPDIFF(MICROSECOND, @start, @end)/1000 as instant_ms; -- 测试INPLACE添加列 SET @start = NOW(6); ALTER TABLE test_instant ADD COLUMN new_col2 INT DEFAULT 0, ALGORITHM=INPLACE; SET @end = NOW(6); SELECT TIMESTAMPDIFF(MICROSECOND, @start, @end)/1000 as inplace_ms; -- 测试COPY添加列 SET @start = NOW(6); ALTER TABLE test_instant ADD COLUMN new_col3 INT DEFAULT 0, ALGORITHM=COPY; SET @end = NOW(6); SELECT TIMESTAMPDIFF(MICROSECOND, @start, @end)/1000 as copy_ms; -- 典型结果： -- INSTANT: 10-100ms -- INPLACE: 5-30秒 (取决于数据量) -- COPY: 30-300秒 (取决于数据量)

三、INPLACE算法详解

1.INPLACE算法原理

1.1执行流程

sql

复制

下载

-- INPLACE算法三阶段执行 -- 阶段1: Prepare（准备） -- 1. 创建临时frm文件 -- 2. 申请MDL锁（排他锁） -- 3. 分配临时文件空间 -- 阶段2: Execute（执行） -- 1. 在InnoDB内部重建表 -- 2. 应用在线日志记录DML -- 3. 增量构建索引 -- 阶段3: Commit（提交） -- 1. 应用积压的DML -- 2. 重命名临时文件 -- 3. 释放MDL锁 -- 查看INPLACE执行进度（MySQL 8.0+） SELECT * FROM performance_schema.threads WHERE PROCESSLIST_COMMAND = 'Alter table'\G -- 监控INPLACE进度 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 '%stage/innodb/alter%';

1.2INPLACE内存管理

c

复制

下载

// InnoDB内部排序缓冲区 struct merge_buf_t { ulint buf_size; // 排序缓冲区大小 ulint n_recs; // 记录数 byte* buf; // 缓冲区指针 }; // Online DDL参数配置 SET GLOBAL innodb_sort_buffer_size = 67108864; -- 64MB SET GLOBAL innodb_online_alter_log_max_size = 134217728; -- 128MB SET GLOBAL innodb_temp_data_file_path = 'ibtmp1:12M:autoextend'; // 监控临时空间 SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO; SHOW STATUS LIKE 'Innodb_os_log%';

2.INPLACE支持的操作

2.1完整的INPLACE操作列表

sql

复制

下载

-- ✅ 支持INPLACE的操作 -- 1. 添加/删除二级索引（最常用） ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE; -- 允许并发DML ALTER TABLE users DROP INDEX idx_email, ALGORITHM=INPLACE, LOCK=NONE; -- 2. 重建表优化 ALTER TABLE users ENGINE=InnoDB, ALGORITHM=INPLACE; -- MySQL 5.6+支持 -- 3. 修改列类型（部分支持） ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED, ALGORITHM=INPLACE; -- 仅当类型兼容时 -- 4. 添加/删除主键 ALTER TABLE users DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at), ALGORITHM=INPLACE; -- 5. 修改表选项 ALTER TABLE users ROW_FORMAT=DYNAMIC, ALGORITHM=INPLACE; -- 6. 重命名列（MySQL 8.0+） ALTER TABLE users CHANGE COLUMN username account_name VARCHAR(50), ALGORITHM=INPLACE; -- 7. 修改自增值 ALTER TABLE users AUTO_INCREMENT = 1000000, ALGORITHM=INPLACE;

篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了：Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc

需要全套面试笔记及答案
【点击此处即可/免费获取】​​​

2.2INPLACE锁级别控制

sql

复制

下载

-- LOCK选项控制并发性 -- 1. LOCK=NONE （最高并发） ALTER TABLE users ADD INDEX idx_phone (phone), ALGORITHM=INPLACE, LOCK=NONE; -- 允许读写，不阻塞 -- 2. LOCK=SHARED （读并发） ALTER TABLE users ADD INDEX idx_phone (phone), ALGORITHM=INPLACE, LOCK=SHARED; -- 允许读，阻塞写 -- 3. LOCK=DEFAULT （自动选择） ALTER TABLE users ADD INDEX idx_phone (phone), ALGORITHM=INPLACE, LOCK=DEFAULT; -- MySQL自动选择最低锁级别 -- 4. LOCK=EXCLUSIVE （完全阻塞） ALTER TABLE users ADD INDEX idx_phone (phone), ALGORITHM=INPLACE, LOCK=EXCLUSIVE; -- 阻塞所有读写 -- 查看当前锁状态 SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = 'test_db' AND OBJECT_NAME = 'users'; -- 监控锁等待 SELECT * FROM sys.innodb_lock_waits;

3.INPLACE优化策略

3.1并行索引构建（MySQL 8.0+）

sql

复制

下载

-- 启用并行索引构建 SET GLOBAL innodb_parallel_read_threads = 4; -- 并行读取线程 SET GLOBAL innodb_ddl_threads = 4; -- DDL并行线程 SET GLOBAL innodb_ddl_buffer_size = 134217728; -- 128MB缓冲区 -- 并行添加索引 ALTER TABLE large_table ADD INDEX idx_composite (col1, col2, col3), ALGORITHM=INPLACE, LOCK=NONE; -- 查看并行执行 SELECT THREAD_ID, NAME, PROCESSLIST_INFO FROM performance_schema.threads WHERE NAME LIKE '%parallel%';

3.2INPLACE空间优化

sql

复制

下载

-- 1. 预估空间需求 SELECT table_schema, table_name, round(((data_length + index_length) / 1024 / 1024), 2) as total_mb, round((data_length / 1024 / 1024), 2) as data_mb, round((index_length / 1024 / 1024), 2) as index_mb FROM information_schema.TABLES WHERE table_name = 'users'; -- 2. 临时表空间监控 SELECT FILE_NAME, TABLESPACE_NAME, INITIAL_SIZE/1024/1024 as initial_mb, AUTOEXTEND_SIZE/1024/1024 as autoextend_mb, MAXIMUM_SIZE/1024/1024 as max_mb FROM information_schema.FILES WHERE FILE_TYPE = 'TEMPORARY'; -- 3. 在线日志监控 SHOW STATUS LIKE 'Innodb_online_alter_log%'; -- Innodb_online_alter_log_max_size -- Innodb_online_alter_log_used

四、COPY算法详解

1.COPY算法原理

1.1传统COPY算法流程

sql

复制

下载

-- COPY算法执行流程（三阶段） -- 阶段1: 创建临时表 -- CREATE TABLE tmp_table LIKE original_table; -- 阶段2: 数据复制 -- INSERT INTO tmp_table SELECT * FROM original_table; -- 此阶段阻塞写操作 -- 阶段3: 切换表 -- RENAME TABLE original_table TO old_table, -- tmp_table TO original_table; -- DROP TABLE old_table; -- 可视化流程 /* 原始表 users (10GB) ↓ COPY开始 临时表 tmp_users (空) ↓ 数据复制 (阻塞写) 临时表 tmp_users (10GB数据) ↓ 元数据锁切换 (短暂阻塞) 重命名: users → users_old, tmp_users → users ↓ 清理 DROP TABLE users_old */

1.2COPY算法空间需求

sql

复制

下载

-- COPY算法需要双倍空间 -- 原始表大小 + 临时表大小 ≈ 2 × 原始表大小 -- 计算表空间需求 SELECT table_name, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) as table_size_gb, round(((data_length + index_length) * 2 / 1024 / 1024 / 1024), 2) as required_space_gb FROM information_schema.TABLES WHERE table_schema = 'your_db' AND table_name = 'your_table'; -- 监控磁盘空间 SELECT table_schema, sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 2)) as total_gb FROM information_schema.TABLES GROUP BY table_schema;

2.COPY算法使用场景

2.1必须使用COPY的场景

sql

复制

下载

-- 1. 更改列顺序 ALTER TABLE users MODIFY COLUMN email VARCHAR(100) AFTER username, ALGORITHM=COPY; -- 必须使用COPY -- 2. 修改存储引擎 ALTER TABLE users ENGINE=MyISAM, ALGORITHM=COPY; -- 跨引擎转换 -- 3. 修改字符集（表级） ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, ALGORITHM=COPY; -- 4. 删除主键（无新主键） ALTER TABLE users DROP PRIMARY KEY, ALGORITHM=COPY; -- 如果没有新主键 -- 5. 添加自增列 ALTER TABLE users ADD COLUMN seq_id INT AUTO_INCREMENT UNIQUE, ALGORITHM=COPY; -- 需要重新组织数据

2.2COPY算法性能优化

sql

复制

下载

-- 1. 分批执行大表DDL -- 方法：创建新表，分批导入数据 CREATE TABLE users_new LIKE users; -- 修改新表结构 ALTER TABLE users_new ADD COLUMN new_col INT, ALGORITHM=COPY; -- 空表很快 -- 分批导入数据（避免长事务） INSERT INTO users_new SELECT *, 0 as new_col FROM users WHERE id BETWEEN 1 AND 100000; INSERT INTO users_new SELECT *, 0 as new_col FROM users WHERE id BETWEEN 100001 AND 200000; -- ... 继续分批 -- 最终切换 RENAME TABLE users TO users_old, users_new TO users; -- 2. 使用pt-online-schema-change # 外部工具，触发器实现 pt-online-schema-change \ --alter="ADD COLUMN new_col INT" \ D=test_db,t=users \ --execute

3.COPY与INPLACE对比场景

3.1相同操作的不同算法

sql

复制

下载

-- 场景：修改列类型 CREATE TABLE test ( id INT PRIMARY KEY, price DECIMAL(10,2) ); -- 情况1：兼容修改 → INPLACE ALTER TABLE test MODIFY COLUMN price DECIMAL(10,3), ALGORITHM=INPLACE; -- 仅修改精度，允许INPLACE -- 情况2：不兼容修改 → COPY ALTER TABLE test MODIFY COLUMN price VARCHAR(20), ALGORITHM=COPY; -- 类型完全改变，需要COPY -- 查看算法选择 ALTER TABLE test MODIFY COLUMN price DECIMAL(12,2), ALGORITHM=DEFAULT; -- MySQL自动选择 -- 验证算法选择 SHOW CREATE TABLE test\G -- 查看information_schema SELECT * FROM information_schema.INNODB_TABLES WHERE NAME LIKE '%test%';

五、三种算法实战对比

1.操作兼容性矩阵

2.性能基准测试对比

sql

复制

下载

-- 创建测试环境 CREATE DATABASE IF NOT EXISTS ddl_benchmark; USE ddl_benchmark; -- 创建大表（1000万行） CREATE TABLE large_table ( id BIGINT AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, data1 VARCHAR(255), data2 VARCHAR(255), data3 VARCHAR(255), INDEX idx_uuid (uuid), INDEX idx_created (created_at) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -- 插入测试数据（使用存储过程简化） DELIMITER // CREATE PROCEDURE populate_data(IN num_rows INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < num_rows DO INSERT INTO large_table (uuid, data1, data2, data3) VALUES (UUID(), MD5(RAND()), MD5(RAND()), MD5(RAND())); SET i = i + 1; IF i % 10000 = 0 THEN COMMIT; END IF; END WHILE; END// DELIMITER ; -- 调用存储过程插入1000万数据 -- CALL populate_data(10000000); -- 实际执行需要时间 -- 测试脚本 SET @iterations = 5; -- 1. INSTANT添加列测试 DROP PROCEDURE IF EXISTS test_instant; DELIMITER // CREATE PROCEDURE test_instant() BEGIN DECLARE i INT DEFAULT 0; DECLARE total_time DECIMAL(10,3) DEFAULT 0; WHILE i < @iterations DO SET @start = NOW(6); ALTER TABLE large_table ADD COLUMN test_instant_col INT DEFAULT 0, ALGORITHM=INSTANT; SET @end = NOW(6); SET total_time = total_time + TIMESTAMPDIFF(MICROSECOND, @start, @end)/1000000; -- 清理 ALTER TABLE large_table DROP COLUMN test_instant_col, ALGORITHM=COPY; -- DROP需要COPY SET i = i + 1; END WHILE; SELECT 'INSTANT' as algorithm, total_time/@iterations as avg_seconds, total_time as total_seconds; END// DELIMITER ; -- 2. INPLACE添加索引测试 DROP PROCEDURE IF EXISTS test_inplace; DELIMITER // CREATE PROCEDURE test_inplace() BEGIN DECLARE i INT DEFAULT 0; DECLARE total_time DECIMAL(10,3) DEFAULT 0; WHILE i < @iterations DO SET @start = NOW(6); ALTER TABLE large_table ADD INDEX idx_test_inplace (data1(100)), ALGORITHM=INPLACE, LOCK=NONE; SET @end = NOW(6); SET total_time = total_time + TIMESTAMPDIFF(MICROSECOND, @start, @end)/1000000; -- 清理 ALTER TABLE large_table DROP INDEX idx_test_inplace, ALGORITHM=INPLACE, LOCK=NONE; SET i = i + 1; END WHILE; SELECT 'INPLACE' as algorithm, total_time/@iterations as avg_seconds, total_time as total_seconds; END// DELIMITER ; -- 3. COPY修改列类型测试 DROP PROCEDURE IF EXISTS test_copy; DELIMITER // CREATE PROCEDURE test_copy() BEGIN DECLARE i INT DEFAULT 0; DECLARE total_time DECIMAL(10,3) DEFAULT 0; -- 先添加测试列 ALTER TABLE large_table ADD COLUMN test_copy_col VARCHAR(100) DEFAULT 'test', ALGORITHM=INSTANT; WHILE i < @iterations DO SET @start = NOW(6); ALTER TABLE large_table MODIFY COLUMN test_copy_col VARCHAR(200), ALGORITHM=COPY; SET @end = NOW(6); SET total_time = total_time + TIMESTAMPDIFF(MICROSECOND, @start, @end)/1000000; SET i = i + 1; END WHILE; -- 清理 ALTER TABLE large_table DROP COLUMN test_copy_col, ALGORITHM=COPY; SELECT 'COPY' as algorithm, total_time/@iterations as avg_seconds, total_time as total_seconds; END// DELIMITER ; -- 执行测试 CALL test_instant(); CALL test_inplace(); CALL test_copy();

3.生产环境选择策略

sql

复制

下载

-- 决策树：选择DDL算法的逻辑 /* 开始DDL操作 ↓ 是否支持INSTANT算法？ / \ 是 否 ↓ ↓ 使用INSTANT执行 是否支持INPLACE算法？ / \ 是 否 ↓ ↓ 使用INPLACE执行 使用COPY算法 ↓ 根据业务负载选择LOCK级别 (NONE < SHARED < EXCLUSIVE) */ -- 自动化选择函数 DELIMITER // CREATE FUNCTION recommend_algorithm( p_operation VARCHAR(50), p_mysql_version VARCHAR(10), p_table_size_mb DECIMAL(10,2), p_peak_hour BOOLEAN ) RETURNS VARCHAR(20) DETERMINISTIC BEGIN DECLARE v_algorithm VARCHAR(20); DECLARE v_major_version INT; SET v_major_version = SUBSTRING_INDEX(p_mysql_version, '.', 1); -- INSTANT算法检查 IF v_major_version >= 8 THEN IF p_operation IN ('ADD_COLUMN', 'DROP_COLUMN', 'RENAME_COLUMN') THEN IF p_table_size_mb > 1024 AND p_peak_hour = TRUE THEN SET v_algorithm = 'INSTANT'; ELSE SET v_algorithm = 'INPLACE'; END IF; ELSE SET v_algorithm = 'INPLACE'; END IF; ELSE -- MySQL 5.6/5.7 IF p_operation IN ('ADD_INDEX', 'DROP_INDEX') THEN SET v_algorithm = 'INPLACE'; ELSE SET v_algorithm = 'COPY'; END IF; END IF; RETURN v_algorithm; END// DELIMITER ; -- 使用示例 SELECT recommend_algorithm( 'ADD_COLUMN', @@version, (SELECT round(((data_length + index_length)/1024/1024), 2) FROM information_schema.TABLES WHERE table_schema = 'test' AND table_name = 'users'), HOUR(NOW()) BETWEEN 9 AND 18 ) as recommended_algorithm;

六、监控与故障处理

1.DDL执行监控

1.1实时监控DDL进度

sql

复制

下载

-- MySQL 8.0+ 进度监控 SELECT pps.THREAD_ID, t.PROCESSLIST_ID as conn_id, t.PROCESSLIST_USER as user, t.PROCESSLIST_HOST as host, t.PROCESSLIST_DB as db, t.PROCESSLIST_COMMAND as command, t.PROCESSLIST_INFO as query, es.STAGE_NAME, es.WORK_COMPLETED, es.WORK_ESTIMATED, CASE WHEN es.WORK_ESTIMATED > 0 THEN ROUND((es.WORK_COMPLETED / es.WORK_ESTIMATED) * 100, 2) ELSE 0 END as progress_pct, ROUND(es.TIMER_WAIT / 1000000000000, 2) as elapsed_seconds FROM performance_schema.threads t LEFT JOIN performance_schema.events_stages_current es ON t.THREAD_ID = es.THREAD_ID LEFT JOIN performance_schema.events_statements_current psc ON t.THREAD_ID = psc.THREAD_ID WHERE t.PROCESSLIST_COMMAND = 'Query' AND t.PROCESSLIST_INFO LIKE 'ALTER TABLE%' AND es.STAGE_NAME LIKE '%alter%';

1.2历史DDL监控

sql

复制

下载

-- 创建DDL监控表 CREATE TABLE ddl_monitor ( id BIGINT AUTO_INCREMENT PRIMARY KEY, start_time DATETIME(6) NOT NULL, end_time DATETIME(6), schema_name VARCHAR(64) NOT NULL, table_name VARCHAR(64) NOT NULL, ddl_statement TEXT NOT NULL, algorithm VARCHAR(20), lock_type VARCHAR(20), rows_affected BIGINT, duration_ms BIGINT, status ENUM('RUNNING', 'COMPLETED', 'FAILED') DEFAULT 'RUNNING', error_message TEXT, created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6), INDEX idx_table (schema_name, table_name), INDEX idx_time (start_time), INDEX idx_status (status) ) ENGINE=InnoDB; -- 创建监控触发器 DELIMITER // CREATE TRIGGER ddl_monitor_start AFTER UPDATE ON information_schema.INNODB_TABLES FOR EACH ROW BEGIN -- 通过解析processlist检测DDL开始 -- 实际实现需要更复杂的逻辑 END// DELIMITER ;

2.常见故障处理

2.1空间不足处理

sql

复制

下载

-- 1. 检查空间使用 SELECT table_schema, table_name, round(((data_length + index_length) / 1024 / 1024), 2) as size_mb, round((data_free / 1024 / 1024), 2) as free_mb FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema') ORDER BY size_mb DESC LIMIT 10; -- 2. 清理旧数据 -- 方案1：归档历史数据 CREATE TABLE users_archive LIKE users; INSERT INTO users_archive SELECT * FROM users WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR); DELETE FROM users WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR); -- 方案2：使用分区表 ALTER TABLE users PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p_future VALUES LESS THAN MAXVALUE ); -- 删除旧分区 ALTER TABLE users DROP PARTITION p2020;

2.2长事务阻塞DDL

sql

复制

下载

-- 1. 查找阻塞DDL的长事务 SELECT r.trx_id as blocking_trx_id, r.trx_mysql_thread_id as blocking_thread, r.trx_started as blocking_started, TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) as blocking_duration_sec, r.trx_query as blocking_query, b.requesting_trx_id as waiting_trx_id, b.requesting_lock_id as waiting_lock, t.PROCESSLIST_INFO as waiting_query FROM information_schema.INNODB_TRX r JOIN information_schema.INNODB_LOCK_WAITS b ON r.trx_id = b.blocking_trx_id JOIN information_schema.INNODB_TRX w ON b.requesting_trx_id = w.trx_id LEFT JOIN performance_schema.threads t ON w.trx_mysql_thread_id = t.PROCESSLIST_ID WHERE r.trx_state = 'RUNNING' ORDER BY blocking_duration_sec DESC; -- 2. 安全终止长事务 -- 首先尝试回滚 KILL QUERY <thread_id>; -- 如果不行，强制终止（谨慎使用） KILL <thread_id>; -- 3. 设置事务超时 SET SESSION innodb_lock_wait_timeout = 30; -- 30秒超时 SET SESSION lock_wait_timeout = 30;

七、最佳实践总结

1.版本升级建议

sql

复制

下载

-- 升级到MySQL 8.0+以获得INSTANT支持 -- 升级前检查表兼容性 -- 1. 检查表格式 SELECT table_name, engine, row_format, table_rows, round(((data_length + index_length) / 1024 / 1024), 2) as size_mb, CASE WHEN row_format IN ('COMPACT', 'REDUNDANT') THEN '可能需要转换' WHEN row_format IN ('DYNAMIC', 'COMPRESSED') THEN '兼容INSTANT' ELSE '未知' END as instant_compatibility FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema') AND engine = 'InnoDB' ORDER BY size_mb DESC; -- 2. 转换表格式 ALTER TABLE table_name ROW_FORMAT=DYNAMIC; -- 3. 验证INSTANT支持 ALTER TABLE table_name ADD COLUMN test_instant INT DEFAULT 0, ALGORITHM=INSTANT; -- 如果成功，说明支持INSTANT ALTER TABLE table_name DROP COLUMN test_instant;

2.生产环境DDL策略

sql

复制

下载

-- DDL执行检查清单 /* ✅ 前置检查： 1. 备份数据（至少要有逻辑备份） 2. 检查磁盘空间（至少2倍表空间） 3. 选择业务低峰期 4. 设置会话参数 5. 通知相关方 ✅ 执行策略： 1. 优先尝试INSTANT 2. 其次INPLACE (LOCK=NONE) 3. 最后考虑COPY 4. 大表考虑分批执行 ✅ 监控要点： 1. 进度监控 2. 空间监控 3. 性能影响监控 4. 错误日志监控 ✅ 回滚方案： 1. 准备好回滚脚本 2. 测试回滚流程 3. 保留足够备份 */

篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了：Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc

需要全套面试笔记及答案
【点击此处即可/免费获取】​​​

3.自动化DDL管理脚本

bash

复制

下载

#!/bin/bash # ddl_manager.sh - 自动化DDL管理 # 配置 MYSQL_HOST="localhost" MYSQL_PORT="3306" MYSQL_USER="admin" MYSQL_PASS="password" BACKUP_DIR="/backup/ddl" LOG_DIR="/var/log/ddl" # 执行DDL execute_ddl() { local DB="$1" local TABLE="$2" local DDL_SQL="$3" # 创建备份 local TIMESTAMP=$(date +%Y%m%d_%H%M%S) local BACKUP_FILE="${BACKUP_DIR}/${DB}_${TABLE}_${TIMESTAMP}.sql" echo "[$(date)] 开始执行DDL: ${DB}.${TABLE}" | tee -a "${LOG_DIR}/ddl.log" # 逻辑备份 mysqldump -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASS} \ --single-transaction --routines --triggers \ ${DB} ${TABLE} > "${BACKUP_FILE}" if [ $? -ne 0 ]; then echo "[$(date)] 备份失败，终止DDL" | tee -a "${LOG_DIR}/ddl.log" return 1 fi # 执行DDL（带超时） timeout 3600 mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASS} ${DB} <<EOF SET SESSION lock_wait_timeout = 30; SET SESSION innodb_lock_wait_timeout = 30; ${DDL_SQL} EOF local DDL_STATUS=$? if [ $DDL_STATUS -eq 0 ]; then echo "[$(date)] DDL执行成功" | tee -a "${LOG_DIR}/ddl.log" # 可选：清理旧备份 find "${BACKUP_DIR}" -name "${DB}_${TABLE}_*.sql" -mtime +7 -delete elif [ $DDL_STATUS -eq 124 ]; then echo "[$(date)] DDL执行超时" | tee -a "${LOG_DIR}/ddl.log" # 回滚？ else echo "[$(date)] DDL执行失败" | tee -a "${LOG_DIR}/ddl.log" # 恢复备份？ fi return $DDL_STATUS } # 使用示例 # execute_ddl "myapp" "users" "ALTER TABLE users ADD COLUMN middle_name VARCHAR(50), ALGORITHM=INSTANT"

八、未来发展趋势

1.MySQL 8.1+新特性

sql

复制

下载

-- 预期改进方向 -- 1. 更多INSTANT操作支持 -- 预计支持：修改列类型、添加外键等 -- 2. 并行DDL增强 -- 更细粒度的并行控制 -- 3. 在线分区管理 -- 支持更多的在线分区操作 -- 4. DDL原子性增强 -- 更完善的原子DDL支持

2.云原生DDL优化

sql

复制

下载

-- 云数据库的DDL优化 -- 1. 异步DDL执行 -- 2. 分布式DDL协调 -- 3. 智能算法选择 -- 4. 自动回滚机制 -- 例如AWS RDS/Aurora的优化 ALTER TABLE users ADD COLUMN new_column INT, ALGORITHM=INSTANT, WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS = 'auto';

核心建议：

• MySQL 8.0+用户优先使用INSTANT算法

• 大表操作务必测试和监控

• 始终准备好回滚方案

• 结合业务特点选择最佳算法