想复制一份真实数据出来单独做回放测试避免污染原始日志表。做法就是创建一张备份表。于是我新来的初级开发同学非常自信地敲下了这行 SQLCREATE TABLE biz_operation_log_bak SELECT * FROM biz_operation_log;在开发环境执行成功他还特意查了一下数据量SELECT COUNT(1) FROM biz_operation_log_bak;结果和原表一模一样没问题然后非常丝滑地提交到了测试环境。那问题出在哪里我们一步一步往下看。首先查看分页查询SQL的执行计划EXPLAIN SELECT * FROM biz_operation_log_bak WHERE biz_id ? ORDER BY create_time DESC LIMIT 0,20;结果type: ALL rows: 12000000全表扫描。1200 万日志记录一页 20 条。继续查原表结构SHOW INDEX FROM biz_operation_log;原表有索引PRIMARY KEY(id) INDEX idx_biz_id(biz_id) INDEX idx_create_time(create_time)再看备份表SHOW INDEX FROM biz_operation_log_bak;返回结果为空。于是问题原因找到了备份表没有原始表的主键、索引信息查询慢是因为全表扫描了。主键冲突是因为备份表根本没有主键这个字段ID只是普通字段。程序继续按老逻辑插入某些场景下业务层自己生成了重复 ID。直接报错Duplicate entry for key PRIMARY分页混乱是因为原表有主见ID默认按主键物理顺序返回相对稳定。但新表没有主键、没有任何索引。MySQL返回顺序完全不保证。什么原因CREATE TABLE new_table AS SELECT * FROM old_table; CREATE TABLE new_table SELECT * FROM old_table;这种方式在 MySQL 中叫 CTASCREATE TABLE AS SELECT。它的特点是复制字段复制数据不复制索引不复制主键不复制唯一约束不复制 AUTO_INCREMENT不复制外键不复制默认值部分场景官方明确写了CREATE TABLE ... SELECT does not automatically create any indexes所以看到“索引不见了”其实是这种语法的正常行为。怎么解决正确的方式应该是先复制表结构再把数据插进去。第一步复制表结构包含索引CREATE TABLE new_table LIKE old_table;这个语句会复制字段复制主键复制普通索引复制唯一索引复制默认值复制自增属性不复制触发器第二步复制数据INSERT INTO new_table SELECT * FROM old_table;第三步检查#检查表的DDL SHOW CREATE TABLE new_table; SHOW CREATE TABLE old_table; #检查数据量 SELECT COUNT(1) FROM new_table; SELECT COUNT(1) FROM old_table; #两张表的 checksum 相同通常意味着数据完全一致每一行数据都一样每个字段值都一样 CHECKSUM TABLE old_table; CHECKSUM TABLE new_table;表复制的技术方案在日常开发和数据库运维中经常会遇到以下场景创建测试表数据备份表结构迁移历史数据归档大表重构分库分表前的数据迁移灰度发布新表这时候“复制表结构和数据”就是一个高频操作。很多人只会一句CREATE TABLE new_table AS SELECT * FROM old_table;但实际上这只是最基础的一种方式。不同方式在 索引、主键、自增、约束、性能、锁表风险 上差异非常大。接下来我将详细讲述不同场景下的表复制与解决方案。只复制表结构只复制表结构怎么做首先回忆一下表结构DDL有哪些字段字段类型默认值NOT NULL主键唯一索引普通索引注释自增属性存储引擎字符集强烈推荐的复制表结构方式CREATE TABLE LIKECREATE TABLE new_table LIKE old_table;相当于把原表的建表语句提取出来然后再执行CREATE TABLE。最终得到的是一个和原始表一模一样的表。但是数据和配置是全新的。谨慎使用CREATE TABLE AS SELECTCTAS的方式除非你知道自己在做什么。-- 其中where条件后面的“ 10 ”表示不复制数据’ CREATE TABLE new_table AS SELECT * FROM original_table WHERE 10;复制表结构中的某些字段不需要复制索引、约束等CREATE TABLE new_table AS SELECT 需要的字段... FROM original_table WHERE 10; #where条件恒不成立不复制数据注意这种方式不复制索引不复制主键不复制唯一约束不复制 AUTO_INCREMENT不复制外键不复制默认值部分场景复制表结构中的某些字段如果这个字段有索引则也需要一并复制不能直接实现方案一手动指定表结构。方案二用CREATE TABLE SELECT创建基础表再Alter表的索引、约束等。注意复制时需确保新表与原表的字符集如utf8、utf8mb4、排序规则如utf8_general_ci一致否则可能出现中文乱码、排序异常。若需修改可在创建新表时指定如DEFAULT CHARSETutf8mb4。复制表结构数据那我既要复制表结构又要复制表数据应该怎么做呢正确的做法是什么先复制结构再复制数据第一步复制结构 CREATE TABLE new_table LIKE old_table; 第二步插入数据 INSERT INTO new_table SELECT * FROM old_table;谨慎使用CREATE TABLE AS SELECTCTAS的方式除非你知道自己在做什么。数据量太大时INSERT INTO...SELECT... 在迁移表数据时可能会产生什么问题执行很慢因为有索引每插入一行都要维护 BTree。锁表问题主从延迟大事务会让 binlog 一次性写入大量数据从库可能延迟几十分钟。磁盘 IO 打满连续全表扫描 连续写入大量顺序读大量索引写可能直接把数据库拖慢。解决方案1分批次在Java层面管理id的范围第一批INSERT INTO new_table SELECT * FROM old_table WHERE id 0 AND id 100000; 第二批INSERT INTO new_table SELECT * FROM old_table WHERE id 100000 AND id 200000; ......//Java实现伪代码 long lastId 0; int batchSize 100000; while (true) { int rows jdbc.execute( INSERT INTO new_table SELECT * FROM old_table WHERE id ? ORDER BY id LIMIT ? , lastId, batchSize); if (rows 0) break; lastId batchSize; }解决方案2先建无索引表导完数据后再单独建索引先建无索引表CREATE TABLE new_table AS SELECT * FROM old_table;导完数据后再单独建索引:ALTER TABLE ADD INDEX ...其他注意点原表中的NULL值、默认值会被正常复制但需注意CREATE TABLE ... AS SELECT创建的表默认值不会保留需手动添加如DEFAULT CURRENT_TIMESTAMP。若新表已存在数据插入数据时需避免主键重复可通过INSERT IGNORE忽略重复数据、REPLACE INTO替换重复数据或ON DUPLICATE KEY UPDATE更新重复数据处理示例-- 忽略重复主键的数据 INSERT IGNORE INTO 新表名 SELECT * FROM 原表名; -- 替换重复主键的数据删除原重复数据插入新数据 REPLACE INTO 新表名 SELECT * FROM 原表名; -- 重复主键时更新指定字段 INSERT INTO 新表名 SELECT * FROM 原表名 ON DUPLICATE KEY UPDATE order_no VALUES(order_no);增量同步前面我们已经创建了用于回放测试的备份表但是现在测试给我们说要把原始日志表的变动实时同步到备份表更方便他做测试。所以我们现在的目标就是把原始表的变动DML操作全部同步到备份表上。此外为了通用性的表达有如下定义原始表、旧表old_table新表new_tableTrigger触发器主要思路在某一时刻起先为原始表创建DML操作的触发器再把原始表的数据批量插入到新表。等待回归测试结束再删除原表上的触发器。主要步骤创建新表为old_table创建触发器包括INSERT、UPDATE、DELETE数据迁移分批次校验数据一致性删除触发器、新表执行步骤1) 创建新表CREATE TABLE new_table LIKE old_table;2) 为old_table创建触发器包括INSERT、UPDATE、DELETEDELIMITER $$ CREATE TRIGGER trg_old_table_insert AFTER INSERT ON old_table FOR EACH ROW BEGIN INSERT INTO new_table VALUES ( NEW.id, NEW.col1, NEW.col2, NEW.col3 -- 所有字段 ); END$$ DELIMITER ; DELIMITER $$ CREATE TRIGGER trg_old_table_update AFTER UPDATE ON old_table FOR EACH ROW BEGIN UPDATE new_table SET col1 NEW.col1, col2 NEW.col2, col3 NEW.col3 WHERE id NEW.id; END$$ DELIMITER ; DELIMITER $$ CREATE TRIGGER trg_old_table_delete AFTER DELETE ON old_table FOR EACH ROW BEGIN DELETE FROM new_table WHERE id OLD.id; END$$ DELIMITER ;3) 数据迁移分批次一次性迁移INSERT INTO new_table SELECT * FROM old_table; 全量分批迁移 INSERT INTO new_table SELECT * FROM old_table WHERE id lastId AND id maxId;4) 校验数据一致性#校验数量是否一致 SELECT COUNT(1) FROM old_table; SELECT COUNT(1) FROM new_table; #两张表的 checksum 相同通常意味着数据完全一致每一行数据都一样每个字段值都一样 CHECKSUM TABLE old_table; CHECKSUM TABLE new_table;5) 删除触发器、新表DROP TRIGGER IF EXISTS trg_old_table_insert; DROP TRIGGER IF EXISTS trg_old_table_update; DROP TRIGGER IF EXISTS trg_old_table_delete;增量的DML操作数据如何同步到新表的全量数据在分批复制触发器在同步增量两条线并行运行。触发器保证增量数据同步到新表在“触发器刚创建”和“复制开始”之间会不会有空档根本不存在这个窗口期也就是说不会存在有数据丢失的情况。虽然不会有数据丢失但可能有“重复同步”。重复同步会不会有问题不会情况1复制时读到的是新值。这种情况是没问题的情况2复制时插入旧数据触发器再 update 一次最终结果还是正确的幂等覆盖为什么用 AFTER 触发器如果使用BEFORE可能会由于事务回滚导致了脏数据移动到了new_table。AFTER表示old_table数据已经执行插入则可以进行同步到new_table技术方案评价优势简单易理解利用数据库原生能力就可以实现无需引入额外的组件劣势影响old_table的DML操作例如影响写性能触发器逻辑复杂难维护如果是单表还好如果涉及到多表同步更新失败难排查如果触发器内执行报错了数据不可恢复的丢失总结适合中小规模在线迁移Binlog解析这个方案的主要思路是伪装成MySQL的一个从库解析binlog回放主库上目标表的DML操作从而实现表数据的增量同步。开源的解决方案有Canal、Debezium。这里不再展开。总结在复制表结构和数据时旧表的DML也需要增量同步到新表上有两类解决方案基于触发器实现优势简单易理解不需要引入额外组件劣势侵入数据库操作复杂容易出错且不方便debug适用场景少量数据迁移单表数据同步对性能要求不高基于binlog实现优势无侵入性能好可以拓展性可以整合ES、Kafka等组件劣势引入新组件实现比较复杂适用场景性能要求高适合数据同步、迁移和数据平台等场景。总结本文主要介绍了再复制表结构时的三个重要问题1) 复制表结构时尽量首选这种方案CREATE TABLE new_table LIKE old_table;2) 复制数据时有2种核心的操作方式方式一INSERT INTO new_table SELECT * FROM old_table; 方式二INSERT INTO new_table SELECT * FROM old_table WHERE id lastId AND id maxId;3) 增量同步的解决方案有Trigger和Binlog解析