如何降低MySQL/MariaDB跨大分区表复制数据的内存占用？

这个场景我太熟悉了——8192个分区的大表做全量迁移时，MySQL会默认加载大量分区元数据到内存，再加上1.5亿行数据的缓存开销，8G内存肯定扛不住。下面给你几个实操性强的方案，按优先级排序：

1. 按分区逐个迁移（最优解） #

既然两张表分区规则完全一致，直接针对单个分区做Insert/Select，内存只会加载当前分区的元数据和数据，压力瞬间降低。

操作步骤： #

• 先查询table1的所有分区名称：

SELECT partition_name 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE table_schema = '你的数据库名' 
AND table_name = 'table1';

• 循环对每个分区执行迁移：

-- 示例：针对单个分区p0001
INSERT INTO table2 PARTITION(p0001) 
SELECT * FROM table1 PARTITION(p0001);

• 自动化处理：如果分区太多，写个简单的Shell脚本批量执行，省去手动操作的麻烦：

#!/bin/bash
DB_NAME="your_db"
TABLE_SRC="table1"
TABLE_DEST="table2"

mysql -u root -p$PASSWORD -N -e "SELECT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_schema='$DB_NAME' AND table_name='$TABLE_SRC'" | while read PART; do
  echo "Migrating partition $PART..."
  mysql -u root -p$PASSWORD -e "INSERT INTO $DB_NAME.$TABLE_DEST PARTITION($PART) SELECT * FROM $DB_NAME.$TABLE_SRC PARTITION($PART);"
done

2. 调整MySQL内存参数，减少不必要开销 #

针对这个简单的全量查询，很多默认内存参数设置得过大，导致内存浪费。修改以下参数（可以先动态生效验证，稳定后再写到配置文件）：

• join_buffer_size：设为128K（这个查询没有复杂Join，不需要大缓冲区）
• sort_buffer_size：设为256K（查询是SELECT *，不需要排序操作）
• innodb_buffer_pool_size：服务器8G内存的话，设为4G（留足内存给系统和其他进程，避免OOM）
• max_heap_table_size & tmp_table_size：都设为256M（限制内存临时表的大小，避免MySQL把大量数据堆在内存）

动态生效命令：

SET GLOBAL join_buffer_size = 131072;
SET GLOBAL sort_buffer_size = 262144;
SET GLOBAL innodb_buffer_pool_size = 4294967296;
SET GLOBAL max_heap_table_size = 268435456;
SET GLOBAL tmp_table_size = 268435456;

3. 临时关闭约束与索引，降低插入开销 #

导入数据时，InnoDB的主键/唯一索引、外键约束会消耗大量内存和CPU，临时关闭可以大幅降低压力：

-- 关闭外键检查与唯一键检查
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

-- 执行迁移（不管是分区分批还是全量）

-- 完成后恢复检查
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

如果是MyISAM表，可以用ALTER TABLE table2 DISABLE KEYS;先禁用非主键索引，导入后再ENABLE KEYS;重建，速度和内存占用都会更优。

4. 按数据范围分批迁移（无分区依赖方案） #

如果不想按分区操作，可以用主键或时间字段分批拉取数据，每次处理一小批：

SET @start_id = 0;
SET @batch_size = 100000; -- 每次10万行，可根据内存情况调整

WHILE @start_id < (SELECT MAX(id) FROM table1) DO
    INSERT INTO table2 
    SELECT * FROM table1 
    WHERE id BETWEEN @start_id AND @start_id + @batch_size;
    
    SET @start_id = @start_id + @batch_size + 1;
    COMMIT; -- 及时提交事务，释放内存
END WHILE;

注意：如果没有自增主键，可以用其他唯一标识字段（比如时间戳）来划分范围。

避坑提醒 #

• 操作前一定要备份原表数据，避免迁移过程中出现数据丢失；
• 生产环境尽量在低峰期操作，避免影响业务；
• 迁移后要验证数据一致性，比如对比两张表的行数、分区数据量是否一致。

内容的提问来源于stack exchange，提问作者Hans Ekbrand