上个月接了个小项目，给一个内部工具做数据归档。说白了就是把三年前的历史数据从主库迁到冷存储，顺便给表结构瘦身。当时觉得这活儿简单，不就是导数据嘛，mysqldump一把梭就完了。结果第一天我就栽了跟头。
我用的MySQL 8.0.32，服务器是16核32G的云主机，压力不算大。原表大概有1.2亿行，索引建了七八个，其中有个联合索引占了快10GB。我直接写了个SELECT * INTO OUTFILE，然后LOAD DATA INFILE到新表。跑起来之后，IO直接打满，主库的慢查询日志里全是等待，业务方开始报接口超时。赶紧kill掉进程，脸都黑了。
核心问题其实就两个：数据量太大时全量导出会锁表，索引重建时磁盘写放大太严重。后来我换成分批处理的方案。先按主键ID范围切分，每次取10万条。用程序分页查，配合FORCE INDEX强制走主键索引，避免全表扫描。每批数据导完就COMMIT一次，同时把新表的auto_increment设成上一批的最大ID加1，防止主键冲突。
具体代码我贴一下关键部分，用的Python 3.10和pymysql 1.1.0。核心逻辑是这样：
“`python
def batch_export(cursor, min_id, max_id, batch_size=100000):
sql = “””
SELECT /*+ MAX_EXECUTION_TIME(30000) */
id, col1, col2, col3
FROM old_table FORCE INDEX (PRIMARY)
WHERE id >= %s AND id < %s
ORDER BY id
“””
cursor.execute(sql, (min_id, min_id + batch_size))
return cursor.fetchall()
“`
注意那个MAX_EXECUTION_TIME hint，防止慢查询把数据库拖死。实际跑下来，每批大概耗时1.2秒到2秒之间，IO压力从原来的100%降到30%左右。但还有一个坑：单机上的pymysql默认不开启流式读取，如果一次fetchall拿太多行，客户端内存会爆。所以我加了sscursor参数，改成服务端游标：
“`python
from pymysql.cursors import SSCursor
conn = pymysql.connect(…, cursorclass=SSCursor)
“`
这样每次只从MySQL服务端拿一行，客户端内存一直稳定在200MB以内。
再说说索引重建的事。原表上有个索引是(col_a, col_b, col_c)，col_a区分度很低，只有三个值。这个索引占了15GB，查询却几乎用不上，因为col_a的cardinality太低，MySQL优化器根本不走它。我重建新表时直接把这个索引去掉了，换成只对col_b和col_c分别建单列索引。新表建完后，用pt-online-schema-change做在线DDL，版本是Percona Toolkit 3.5.5。命令是这样：
“`
pt-online-schema-change h=localhost,D=mydb,t=new_table –alter “DROP INDEX idx_abc, ADD INDEX idx_b(col_b), ADD INDEX idx_c(col_c)” –execute –chunk-size=5000 –max-lag=5 –check-interval=1
“`
chunk-size设成5000是因为机器IOPS有限，太大容易触发磁盘排队。max-lag检查主从延迟，因为归档期间主库还有写入，不能把从库拖垮。这个跑了一小时四十分钟，中间有个chunk因为死锁回滚了一次，pt自动重试了三次才成功。日志里能看到这种信息：
“`
Chunk: 4500000-4505000, 237 rows affected, 0.20 sec
Chunk: 4505000-4510000, DEADLOCK FOUND, retrying…
Chunk: 4505000-4510000, retry #1, 0.25 sec
“`
死锁的原因是归档进程和业务写入同时对同一行加了gap锁。解决办法是把pt的锁等待超时调小，默认是1秒，我改成0.5秒，这样死锁检测更快触发，重试代价更低。
数据迁移完了之后，还有个头疼的事：检查数据一致性。我用了mysqldbcompare，但直接全表比对太慢了，1.2亿行要跑三个多小时。后来改成按分片比对checksum。把原表和新表按主键ID分成100个区间，每个区间计算crc32聚合值。如果某个区间的crc32对不上，再单独查那个区间的具体行。这样95%的区间一次性通过，只有5个区间有差异，仔细看发现是原表里有几条被业务update过的记录，归档时没有同步到新表。最后手动补了那几条数据。
总结一下踩的坑。第一，不要迷信mysqldump，超过千万级的表就该用分批+游标。第二，索引不是越多越好，低区分度的联合索引是磁盘杀手。第三，pt-online-schema-change虽然稳，但要在低峰期跑，死锁重试是常态。第四，数据一致性校验不能偷懒，但可以用分片策略加速。
对了，还有个细节差点忘了说。归档完成之后，原表空间没有释放。因为MySQL的InnoDB表空间回收要靠OPTIMIZE TABLE，但这个操作会锁表，而且对1.2亿行的表来说，跑一次至少两小时。我查了官方文档，MySQL 8.0支持在线DDL重建表，所以用了ALTER TABLE old_table ENGINE=InnoDB，这个操作在8.0.12之后支持ALGORITHM=INSTANT，但只适用于少量字段变更。实际我跑了ALTER TABLE old_table ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE，耗时1小时15分钟，期间业务完全无感知。
就这些了。干这行就是这样，你以为是个小活，结果数据库教你做人。