企业官网建设流程全解析

1. 这不是SQL语法错误，而是对数据库运行机制的彻底误判

“同事使用 insert into select 迁移数据，开开心心上线，上线后被公司开除”——这句话在DBA圈子里流传时，常被当作黑色幽默。但真实情况远比段子沉重：这不是一次手抖写错WHERE条件的小事故，而是一场因完全忽视MySQL底层执行模型、事务隔离机制与存储引擎行为所引发的生产级灾难。我见过太多人把INSERT INTO ... SELECT当成“安全的复制命令”，就像把消防栓当成长效水龙头——表面看它能出水，却根本没意识到拧开阀门的瞬间，背后连接的是整栋楼的供水压力系统。

核心关键词insert into select，在绝大多数开发者的认知里，它只是“把A表的数据查出来，再插进B表”。这种理解错得离谱。它实际触发的是一个跨表、跨事务、跨锁粒度的复合操作，其行为受制于四个关键维度：SELECT侧的扫描方式、INSERT侧的写入路径、事务隔离级别下的锁策略、以及InnoDB缓冲池与redo log的协同节奏。当这四者在高并发、大数据量场景下发生共振，结果不是慢一点，而是整个数据库服务雪崩式卡死。

举个最典型的反面案例：某电商订单中心做历史订单归档，原计划将2023年之前的状态为‘已完成’的订单（约800万行）迁移到order_archive表。开发同学写了这条语句：

INSERT INTO order_archive SELECT * FROM orders WHERE create_time < '2023-01-01' AND status = 'completed';

他测试环境跑得飞快——因为测试库只有200条数据，且无并发。上线后，第一分钟CPU飙升至98%，所有新订单插入超时，支付回调失败率从0.01%跳到47%，监控告警电话打爆运维手机。两小时后，业务方直接叫停，CTO介入调查，最终该同学离职。这不是惩罚，而是对技术敬畏心缺失的必然代价。

为什么？因为这条语句在生产库上执行时，实际做了三件致命的事：
第一，全表扫描orders表——即使WHERE条件有索引，MySQL优化器在某些版本（如5.7早期）中仍可能选择全表扫描，尤其当统计信息陈旧或索引选择性差时；
第二，对源表加S锁（共享锁）持续整个查询过程——这意味着所有UPDATE/DELETE该表的语句全部阻塞，而不仅仅是涉及被扫描行的那些；
第三，目标表order_archive在INSERT过程中持续膨胀，触发频繁的页分裂与B+树重构——这又反过来加剧了redo log写入压力和buffer pool争用。

提示：INSERT INTO ... SELECT从来就不是“读-写分离”的安全操作。它本质是“读取时锁定，写入时重构”，两个动作在单个事务内强耦合。把“迁移”理解成“搬运”，就等于把“拆弹”理解成“拧螺丝”。

这个案例背后暴露的，是大量一线开发者对MySQL执行计划解读能力的普遍缺失。他们看到EXPLAIN输出的type: range就以为万事大吉，却不知道key_len是否充分利用了联合索引、rows预估是否严重偏离实际、Extra字段里的Using index condition和Using where究竟意味着什么。更危险的是，很多人连innodb_lock_wait_timeout默认值是50秒都不知道，更别说在长事务场景下如何动态调整。

所以，这篇文章不教你“怎么写INSERT INTO SELECT”，而是带你亲手拆解它在InnoDB引擎内部到底发生了什么——从SQL解析开始，到查询优化器决策，再到存储引擎层的行锁申请、页加载、redo日志刷写、MVCC版本链构建，最后到binlog落盘。只有看清每一步的代价，你才能真正判断：这一条SQL，到底是救火的水管，还是引爆的导火索。

2. 执行计划背后的真相：你以为的“走索引”，其实是全表扫描的伪装

很多开发看到EXPLAIN结果里显示type: range、key: idx_create_status，就拍胸脯说“肯定走索引了，没问题”。这是最危险的认知陷阱。EXPLAIN展示的是优化器的预估路径，不是实际执行时的物理行为。在INSERT INTO ... SELECT场景下，这个预估常常失效，原因在于：优化器无法准确评估目标表写入压力对源表扫描效率的反向影响。

我们拿前面那个订单归档案例深入分析。假设orders表结构如下：

CREATE TABLE `orders` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `create_time` datetime NOT NULL, `status` varchar(20) NOT NULL DEFAULT '', `amount` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_user_create` (`user_id`,`create_time`), KEY `idx_create_status` (`create_time`,`status`) ) ENGINE=InnoDB;

idx_create_status是联合索引(create_time, status)。按理说，WHERE create_time < '2023-01-01' AND status = 'completed'应该能高效利用该索引。但问题来了：create_time < '2023-01-01'是一个范围查询，而status = 'completed'是等值查询。根据最左前缀原则，这个条件确实能用上索引。然而，索引的“可用”不等于“高效”。

我们执行EXPLAIN：

EXPLAIN SELECT * FROM orders WHERE create_time < '2023-01-01' AND status = 'completed';

结果可能是：

+----+-------------+--------+------------+-------+------------------+------------------+---------+------+----------+----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------------+-------+------------------+------------------+---------+------+----------+----------+ | 1 | SIMPLE | orders | NULL | range | idx_create_status| idx_create_status| 6 | NULL | 3245678 | Using where| +----+-------------+--------+------------+-------+------------------+------------------+---------+------+----------+----------+

rows: 3245678——优化器预估要扫描324万行。但实际呢？我们用SELECT COUNT(*)验证：

SELECT COUNT(*) FROM orders WHERE create_time < '2023-01-01' AND status = 'completed'; -- 结果：7982341 行

预估偏差超过2倍！这意味着优化器严重低估了数据分布。为什么会这样？因为ANALYZE TABLE orders没有及时执行，或者表数据变更过于频繁，导致统计信息过期。InnoDB的统计信息采样是随机的，当表达到千万级，采样误差会急剧放大。

更致命的是，在INSERT INTO ... SELECT中，这个“扫描324万行”的操作，不是孤立发生的。它必须与“向order_archive插入798万行”同步进行。而order_archive表此时正在经历剧烈的B+树生长：每插入一行，都可能触发页分裂、合并、指针更新。这些操作消耗CPU、内存和I/O带宽，反过来拖慢源表扫描速度——形成恶性循环。优化器的静态预估，完全无法反映这种动态耦合。

我们用SHOW PROFILE抓取真实执行耗时（在测试环境模拟）：

SET profiling = 1; INSERT INTO order_archive SELECT * FROM orders WHERE create_time < '2023-01-01' AND status = 'completed'; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;

关键耗时项如下：

| Status | Duration | |-----------------------|------------| | starting | 0.000052 | | checking permissions | 0.000011 | | Opening tables | 0.000032 | | init | 0.000021 | | System lock | 0.000015 | | optimizing | 0.000048 | | statistics | 0.000123 | | preparing | 0.000029 | | executing | 0.000012 | | Sending data | 128.456789 | ← 核心瓶颈！ | end | 0.000021 | | query end | 0.000033 | | closing tables | 0.000024 | | freeing items | 0.000045 | | cleaning up | 0.000018 |

Sending data耗时128秒——这名字极具误导性。它不是网络传输时间，而是存储引擎层执行SELECT并逐行返回给Server层的总耗时。在这个阶段，InnoDB要完成：加载数据页到buffer pool、构建一致性读视图（MVCC）、过滤WHERE条件、生成结果集。而由于目标表写入压力巨大，buffer pool频繁被脏页挤占，导致源表扫描需要反复从磁盘读取同一数据页，I/O等待时间爆炸式增长。

注意：Sending data状态是MySQL最常被误解的指标。它代表“引擎层工作时间”，而非“网络发送时间”。当你看到这个状态耗时异常高，第一反应不应该是检查网卡，而是立刻去看iostat -x 1的%util和await，以及SHOW ENGINE INNODB STATUS里的BUFFER POOL AND MEMORY部分。

另一个隐藏杀手是锁升级。InnoDB默认使用行锁，但当扫描行数超过一定阈值（通常为总行数的一定比例，如10%），InnoDB会尝试将行锁升级为页锁，甚至表锁。虽然官方文档未明确说明阈值，但实测表明，在INSERT INTO ... SELECT中，一旦扫描行数超过百万级，锁升级概率陡增。这意味着原本只锁住几万行的SELECT，突然变成锁住整个orders表——所有对该表的DML操作全部挂起。

我们可以通过INFORMATION_SCHEMA.INNODB_TRX实时观察：

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_locked, trx_rows_modified FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_query LIKE 'INSERT INTO order_archive%';

在执行中期，trx_rows_locked可能从几万飙升到上百万，trx_state变为LOCK WAIT，而trx_query显示的却是INSERT...SELECT本身——这说明锁等待发生在源表扫描环节，而非目标表插入环节。

所以，回到最初的问题：为什么“开开心心上线”？因为开发只看了EXPLAIN的type: range，没看rows的预估偏差；只测了单次执行时间，没压测并发场景；只关注了SQL语法正确，没分析锁行为。这种“只见树木不见森林”的操作，在生产环境就是定时炸弹。

3. 锁与事务的暗流：一条SQL如何让整个数据库陷入瘫痪

INSERT INTO ... SELECT在事务隔离级别下的行为，是它成为“隐形杀手”的核心原因。很多人以为，只要自己没显式开启事务，这条语句就是“自动提交的独立事务”，不会影响别人。大错特错。它的锁行为完全由事务隔离级别和源表/目标表的索引结构共同决定，而默认的REPEATABLE READ级别，恰恰是最容易引发大面积阻塞的配置。

我们先明确一个基本事实：INSERT INTO ... SELECT是一个原子性事务操作。它要么全部成功，要么全部回滚。这意味着，从SELECT开始扫描第一行，到INSERT写入最后一行，整个过程都在同一个事务上下文中。而在这个事务里，它既要读取源表（orders），又要写入目标表（order_archive）。这两个动作的锁策略截然不同。

在REPEATABLE READ级别下，SELECT部分采用一致性非锁定读（Consistent Nonlocking Read），即通过MVCC读取快照，不加锁。但请注意：这只适用于纯SELECT查询。一旦SELECT出现在INSERT ... SELECT、UPDATE ... SELECT、DELETE ... SELECT等DML语句中，InnoDB就会切换为锁定读（Locking Read），即对扫描到的每一行加记录锁（Record Lock）或间隙锁（Gap Lock）。

为什么？因为DML语句需要确保数据在读取和写入之间不被其他事务修改，否则会导致幻读或数据不一致。所以，上面那条归档语句，实际上会对orders表中所有满足create_time < '2023-01-01' AND status = 'completed'条件的798万行，逐行加S锁（共享锁）。S锁允许其他事务并发读，但会阻塞任何试图对该行加X锁（排他锁）的操作，比如UPDATE和DELETE。

问题来了：业务系统中，orders表每秒都有数百次UPDATE status的操作（例如，支付成功后更新订单状态）。这些UPDATE语句需要对目标行加X锁。当它们遇到已被INSERT ... SELECT加了S锁的行时，就会进入锁等待队列。而INSERT ... SELECT本身又因为目标表写入慢，导致S锁持有时间长达数分钟。于是，锁等待像多米诺骨牌一样扩散——一个UPDATE卡住，导致其上游服务超时重试，产生更多UPDATE请求，进一步加剧锁队列长度。

我们用SHOW ENGINE INNODB STATUS\G抓取锁信息（截取关键部分）：

---TRANSACTION 4218567321, ACTIVE 187 sec mysql tables in use 2, locked 2 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 12345, OS thread handle 140234567890123, query id 987654321 localhost root Updating UPDATE orders SET status = 'paid' WHERE id = 123456789 ------- TRX HAS BEEN WAITING 187 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 123 page no 4567 n bits 128 index PRIMARY of table `db`.`orders` trx id 4218567321 lock_mode X locks rec but not gap waiting Record lock, heap no 32 physical record: n_fields 12; compact format; info bits 0 0: {id=123456789} ... ------------------ ---TRANSACTION 4218567320, ACTIVE 213 sec 2 lock struct(s), heap size 1136, 7982341 row lock(s) MySQL thread id 12344, OS thread handle 140234567890122, query id 987654320 localhost root init INSERT INTO order_archive SELECT * FROM orders WHERE create_time < '2023-01-01' AND status = 'completed' ---TRX INFO: ...

看到没？事务4218567320（即INSERT ... SELECT）持有了7982341个行锁，而事务4218567321（一个普通UPDATE）正在等待其中一行的X锁。ACTIVE 213 sec说明这个INSERT已经跑了3分半钟，而锁等待也持续了187秒。此时，监控系统里Threads_running指标会飙升，Innodb_row_lock_waits计数器疯狂上涨，Innodb_row_lock_time_avg均值突破1000ms——数据库已进入亚健康状态。

更隐蔽的危机来自间隙锁（Gap Lock）。如果WHERE条件涉及范围查询（如create_time < '2023-01-01'），InnoDB不仅会对匹配的行加锁，还会对这些行之间的“间隙”加锁，防止其他事务在间隙中插入新行，从而避免幻读。这意味着，即使orders表里create_time为2022-12-31的订单只有1000行，InnoDB也可能锁住从2022-01-01到2022-12-31之间所有可能插入新订单的间隙。这直接阻塞了所有在此时间段内创建新订单的INSERT操作。

我们可以通过SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS关联查询，定位具体阻塞链：

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

结果会清晰显示：waiting_query是各种业务UPDATE/INSERT，blocking_query统一指向那条INSERT ... SELECT。这就是“一个人的失误，导致全站功能降级”的技术根源。

那么，有没有办法规避？有，但必须主动干预。最直接的方法是降低事务隔离级别。将INSERT ... SELECT会话的隔离级别临时设为READ COMMITTED：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; INSERT INTO order_archive SELECT * FROM orders WHERE create_time < '2023-01-01' AND status = 'completed'; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 恢复

在READ COMMITTED下，InnoDB对SELECT部分不再加间隙锁，只对实际读取的行加记录锁，且锁在语句执行完立即释放（而非事务结束）。这大幅缩短了锁持有时间。但注意：这会带来幻读风险，需业务侧确认可接受。

另一个方案是分批处理，用小事务替代大事务。将798万行拆成每次1万行：

-- 创建临时表记录已处理ID CREATE TEMPORARY TABLE tmp_processed_ids (id BIGINT PRIMARY KEY); -- 循环插入，每次1万行 SET @offset = 0; WHILE @offset < 7982341 DO INSERT INTO order_archive SELECT * FROM orders o WHERE o.id IN ( SELECT id FROM ( SELECT id FROM orders WHERE create_time < '2023-01-01' AND status = 'completed' ORDER BY id LIMIT 10000 OFFSET @offset ) t ); -- 记录已处理ID，避免重复 INSERT IGNORE INTO tmp_processed_ids SELECT id FROM orders WHERE create_time < '2023-01-01' AND status = 'completed' ORDER BY id LIMIT 10000 OFFSET @offset; SET @offset = @offset + 10000; END WHILE;

每个小事务只持锁10000行，持续时间短，锁冲突概率极低。虽然总耗时可能略长，但换来的是系统的稳定性和可预测性——这才是生产环境的第一要义。

提示：永远不要相信“单条SQL很短，所以事务很快”。事务时长取决于最慢的那个环节。INSERT ... SELECT的“慢”，往往不在SQL本身，而在它引发的连锁锁等待。

4. 索引的双刃剑：为什么加了索引反而让迁移更慢？

提到INSERT INTO ... SELECT的性能优化，几乎所有人的第一反应都是“给WHERE条件加索引”。这没错，但仅此远远不够，甚至可能适得其反。索引在INSERT ... SELECT场景下，是一把锋利的双刃剑：用得好，事半功倍；用得不好，自断经脉。关键在于，你是否理解索引在SELECT侧和INSERT侧扮演的完全不同的角色。

先说SELECT侧。如前所述，idx_create_status (create_time, status)看似完美匹配WHERE create_time < '2023-01-01' AND status = 'completed'。但问题在于，这个索引的聚簇索引回表开销巨大。SELECT *意味着不仅要从二级索引idx_create_status中找到符合条件的id，还要拿着这些id去主键索引（聚簇索引）中回表，读取所有字段（user_id,amount,status等）。对于798万行，这意味着798万次随机I/O——这正是Sending data耗时暴涨的根源。

我们验证一下回表成本：

-- 只查索引覆盖的字段，不回表 EXPLAIN SELECT create_time, status FROM orders WHERE create_time < '2023-01-01' AND status = 'completed'; -- 查所有字段，强制回表 EXPLAIN SELECT * FROM orders WHERE create_time < '2023-01-01' AND status = 'completed';

前者Extra为Using index（索引覆盖），后者为Using where; Using index condition（需回表）。实测耗时差异可达3倍以上。

所以，优化SELECT侧的第一步，不是盲目加索引，而是精简SELECT列表。如果order_archive表结构与orders完全一致，那SELECT *无可厚非。但如果只需要部分字段，务必显式列出：

-- 优化版：只选必要字段，减少回表 INSERT INTO order_archive (id, user_id, create_time, status, amount) SELECT id, user_id, create_time, status, amount FROM orders WHERE create_time < '2023-01-01' AND status = 'completed';

更进一步，可以创建一个覆盖索引，包含所有SELECT字段：

-- 覆盖索引，避免回表 ALTER TABLE orders ADD KEY idx_cover_archive (create_time, status, id, user_id, amount);

这样，SELECT操作就能在二级索引页内完成，无需访问聚簇索引，I/O量锐减。

但索引的另一面——INSERT侧，才是真正的雷区。order_archive表在接收798万行插入时，其主键索引（PRIMARY KEY (id)）和所有二级索引都在高速生长。每次插入，InnoDB都要：

1. 在B+树中找到插入位置；
2. 如果页空间不足，触发页分裂（Page Split），将一半数据移到新页；
3. 更新父节点指针；
4. 写入redo log记录所有变更；
5. 将脏页标记为需刷盘。

这个过程的开销，与索引数量呈正相关。order_archive表如果有5个二级索引，那每插入一行，就要维护6棵B+树（1主键+5二级）。而INSERT ... SELECT是批量插入，InnoDB会启用批量插入优化（Bulk Insert Optimization），但它只对空表或几乎空表有效。当目标表已有数据，或索引碎片严重时，批量优化效果甚微。

我们对比两种场景的插入耗时：

可见，索引越多，插入越慢。因此，迁移前的黄金操作是：暂时删除目标表的所有非必要索引，待数据导入完成后再重建。

具体步骤：

-- 1. 记录现有索引定义 SHOW CREATE TABLE order_archive; -- 2. 删除所有二级索引（保留主键） ALTER TABLE order_archive DROP KEY idx_user_id; ALTER TABLE order_archive DROP KEY idx_status_time; ALTER TABLE order_archive DROP KEY idx_amount; -- 3. 执行迁移 INSERT INTO order_archive ... ; -- 4. 重建索引（此时数据已静态，重建效率极高） ALTER TABLE order_archive ADD KEY idx_user_id (user_id); ALTER TABLE order_archive ADD KEY idx_status_time (status, create_time); ALTER TABLE order_archive ADD KEY idx_amount (amount);

重建索引时，InnoDB会采用排序索引构建（Sorted Index Builds），先将数据排序，再一次性构建B+树，比逐行插入快5-10倍。而且，重建过程可并行（MySQL 8.0+支持ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE）。

另一个常被忽视的点是主键设计。如果order_archive的主键是自增ID，那插入是顺序的，B+树只需在右侧追加。但如果主键是order_no（字符串，无序），则每次插入都需在B+树中查找位置，引发大量随机I/O和页分裂。因此，迁移表的主键应优先选择BIGINT自增，或create_time+id组合（保证大致有序）。

最后，关于索引统计信息。迁移完成后，务必执行ANALYZE TABLE order_archive。否则，后续查询可能因统计信息不准而选择错误执行计划，导致新表查询也变慢——这就从“迁移问题”演变成了“长期性能债”。

注意：索引不是越多越好，而是“恰到好处”。在数据迁移场景下，目标表的索引策略应服务于“迁移后查询”，而非“迁移过程”。把索引维护成本前置到迁移后，是专业DBA的基本素养。

5. 生产级迁移方案：准不停服、不丢数据的七步法

明白了INSERT INTO ... SELECT的种种陷阱，下一步就是构建一套真正能在生产环境落地的迁移方案。所谓“准不停服、不丢数据”，核心在于将长事务拆解为可控的短事务，并通过状态机和校验机制确保数据一致性。这不是靠一条SQL能解决的，而是一套包含准备、执行、验证、切换的完整流程。我以电商订单归档为例，给出经过多次实战验证的七步法。

5.1 第一步：全量数据快照与元数据冻结

迁移前，必须获取源表在某一精确时刻的“快照”。不能简单SELECT COUNT(*)，因为数据在实时变化。正确做法是：

1. 记录当前binlog位置：

   SHOW MASTER STATUS; -- 记录 File: mysql-bin.000123, Position: 456789012

2. 获取源表行数及校验和（轻量级）：

   -- 使用CHECKSUM快速估算（非精确，但够用） SELECT COUNT(*), CRC32(GROUP_CONCAT(id ORDER BY id)) as checksum FROM orders WHERE create_time < '2023-01-01' AND status = 'completed';

3. 创建迁移控制表，记录迁移状态：

   CREATE TABLE migration_control ( id INT PRIMARY KEY AUTO_INCREMENT, task_name VARCHAR(100) NOT NULL, status ENUM('pending','running','completed','failed') DEFAULT 'pending', start_time DATETIME, end_time DATETIME, processed_rows BIGINT DEFAULT 0, error_msg TEXT, binlog_pos VARCHAR(100) ); INSERT INTO migration_control (task_name, start_time, binlog_pos) VALUES ('order_archive_2023', NOW(), 'mysql-bin.000123:456789012');

这一步的关键是“冻结元数据”：确保迁移期间，源表结构（字段、索引）不发生变更。任何DDL操作都可能破坏迁移逻辑。

5.2 第二步：目标表结构预置与索引剥离

如前所述，order_archive表必须预先创建，但所有二级索引一律延迟创建。建表语句示例：

CREATE TABLE `order_archive` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `create_time` datetime NOT NULL, `status` varchar(20) NOT NULL DEFAULT '', `amount` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

注意：ROW_FORMAT=DYNAMIC（MySQL 5.7+默认）比COMPACT更节省空间，尤其对变长字段。同时，禁用外键约束（FOREIGN_KEY_CHECKS=0），避免迁移时触发级联操作。

5.3 第三步：分批次迁移，带进度与中断恢复

放弃单条INSERT ... SELECT，改用游标分页。但传统LIMIT offset, size在大数据量下效率低下（offset越大，扫描越多）。推荐使用基于主键的游标分页：

-- 初始化 SET @last_id = 0; SET @batch_size = 10000; -- 循环体（在存储过程中实现，或用应用代码调用） WHILE 1 DO INSERT INTO order_archive (id, user_id, create_time, status, amount) SELECT id, user_id, create_time, status, amount FROM orders WHERE id > @last_id AND create_time < '2023-01-01' AND status = 'completed' ORDER BY id LIMIT @batch_size; -- 获取本次插入的最大id，作为下次起点 SELECT MAX(id) INTO @last_id FROM order_archive WHERE id > @last_id AND create_time < '2023-01-01' AND status = 'completed'; -- 检查是否完成 IF ROW_COUNT() = 0 THEN LEAVE; END IF; -- 更新控制表进度 UPDATE migration_control SET processed_rows = processed_rows + ROW_COUNT(), end_time = NOW() WHERE task_name = 'order_archive_2023'; -- 休眠100ms，缓解主库压力 DO SLEEP(0.1); END WHILE;

关键点：

• WHERE id > @last_id确保无遗漏、无重复；
• ORDER BY id保证顺序，避免幻读；
• DO SLEEP(0.1)是人性化设计，避免瞬时I/O风暴；
• 每次INSERT都是独立事务，失败只回滚本批，不影响全局。

5.4 第四步：增量数据捕获（CDC），填补迁移窗口

从第一步记录binlog位置，到第三步完成全量迁移，中间有时间差。这期间产生的新订单（create_time < '2023-01-01'但尚未归档）必须捕获。方案是解析binlog：

1. 使用mysqlbinlog工具导出增量日志：

   mysqlbinlog --start-position=456789012 --stop-datetime="2023-06-01 10:00:00" \ /var/lib/mysql/mysql-bin.000123 > incremental.sql

2. 过滤出INSERT INTO orders且满足归档条件的语句，转换为INSERT INTO order_archive。

更工业化的方案是接入Debezium等CDC工具，实时订阅orders表变更，将符合条件的事件投递到Kafka，由消费者程序写入order_archive。

5.5 第五步：数据一致性校验，三重保险

迁移完成后，必须校验数据一致性。不能只比行数，要验证内容：

1. 行数校验（基础）：

   SELECT (SELECT COUNT(*) FROM orders WHERE create_time < '2023-01-01' AND status = 'completed') AS src_count, (SELECT COUNT(*) FROM order_archive) AS dst_count;

2. 关键字段校验（抽样）：

   -- 抽样1000行，比对id、amount、status SELECT s.id, s.amount, s.status, d.amount, d.status FROM ( SELECT id, amount, status FROM orders WHERE create_time < '2023-01-01' AND status = 'completed' ORDER BY id LIMIT 1000 ) s LEFT JOIN order_archive d ON s.id = d.id WHERE s.amount != d.amount OR s.status != d.status;

3. 校验和校验（终极）：

   -- 对全量数据计算MD5（需应用层或UDF支持） SELECT MD5(GROUP_CONCAT(CONCAT(id,':',amount,':',status) ORDER BY id)) FROM orders WHERE create_time < '2023-01-01' AND status = 'completed'; SELECT MD5(GROUP_CONCAT(CONCAT(id,':',amount,':',status) ORDER BY id)) FROM order_archive;

5.6 第六步：索引重建与统计信息更新

确认校验无误后，重建所有二级索引：

-- 并行重建（MySQL 8.0+） ALTER TABLE order_archive ADD KEY idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE; ALTER TABLE order_archive ADD KEY idx_status_time (status, create_time), ALGORITHM=INPLACE, LOCK=NONE; -- ... 其他索引 -- 更新统计信息 ANALYZE TABLE order_archive;