mysql遇到内存都装不下的数据表，如果要进行去重，该如何操作？

在MySQL中，当数据表大到内存都装不下时，直接对整个表进行去重（如DISTINCT或GROUP BY）会导致严重的性能问题，甚至OOM（内存溢出）。此时，必须采用 “分而治之” 的策略，避免一次性加载全表数据。以下是几种可行且安全的方案：

方案1：利用索引+分批处理（推荐）

核心思路：利用索引有序性，按主键或唯一键分批处理，避免全表扫描。

步骤：

确保表有自增主键（如id），或唯一索引。
按主键范围分批（如每次处理100万行），利用WHERE id BETWEEN x AND y限制范围。
每批内去重：用临时表或INSERT IGNORE/REPLACE INTO插入到结果表。
重复步骤2-3，直到覆盖全表。

示例代码：

-- 假设原表为big_table，目标表为unique_table（结构相同）
-- 1. 创建结果表（无重复）
CREATE TABLE unique_table LIKE big_table;
ALTER TABLE unique_table ADD UNIQUE KEY unique_idx (col1, col2, col3); -- 去重字段

-- 2. 分批插入（假设主键是id）
SET @batch_size = 1000000;
SET @min_id = 1;
SET @max_id = (SELECT MAX(id) FROM big_table);

WHILE @min_id <= @max_id DO
    INSERT IGNORE INTO unique_table (col1, col2, col3, ...)
    SELECT col1, col2, col3, ...
    FROM big_table
    WHERE id BETWEEN @min_id AND @min_id + @batch_size - 1;
    
    SET @min_id = @min_id + @batch_size;
END WHILE;

如果插入的数据量还是过大会导致内存挤爆，可以考虑下面做法：
使用临时表
使用临时表来分批处理数据，可以减少对目标表的直接操作，从而降低内存占用。
创建临时表
CREATE TEMPORARY TABLE temp_table LIKE big_table;
ALTER TABLE temp_table ADD UNIQUE KEY unique_idx (col1, col2, col3);
分批插入到临时表
SET @batch_size = 100000; -- 调整为更小的分批大小
SET @min_id = 1;
SET @max_id = (SELECT MAX(id) FROM big_table);

WHILE @min_id <= @max_id DO
    INSERT IGNORE INTO temp_table (col1, col2, col3, ...)
    SELECT col1, col2, col3, ...
    FROM big_table
    WHERE id BETWEEN @min_id AND @min_id + @batch_size - 1;
    
    SET @min_id = @min_id + @batch_size;
END WHILE;
将临时表数据插入目标表
INSERT IGNORE INTO unique_table (col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM temp_table;
删除临时表
DROP TEMPORARY TABLE temp_table;

方案2：外部排序+文件去重（无需内存）

如果无索引或去重字段非索引，可借助外部排序工具（如Linux sort）：

步骤：

导出数据到文件（仅去重字段+主键）：

SELECT col1, col2, col3, id INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ',' FROM big_table;

外部排序去重（利用磁盘排序）：

sort -t',' -k1,3 -u /tmp/data.csv > /tmp/unique.csv

回写数据库：
- 将unique.csv导入临时表，再通过主键关联原表更新。

方案3：利用MySQL分区表（需提前设计）

如果表本身是分区表（如按哈希或范围分区），可以：

对每个分区单独去重（ALTER TABLE ... OPTIMIZE PARTITION或CREATE TABLE ... SELECT DISTINCT）。
合并结果到新的分区表。

避免的方法：

避免SELECT DISTINCT ...全表：会触发全表扫描+临时表（磁盘临时表可能溢出）。
避免GROUP BY无索引字段：会导致filesort（磁盘排序），极慢。
避免DELETE ... JOIN大表自连接：会锁表+生成大量undo log。

🔍 总结选择：

场景	推荐方案
有自增主键	方案1（分批+索引）
无索引+允许外部工具	方案2（sort去重）
分区表	方案3（分区处理）

⚠️ 注意事项：

磁盘空间：确保有足够空间存储临时文件或结果表。
日志关闭：可临时关闭binlog（SET sql_log_bin=0）加速插入（需谨慎）。
监控进度：用SHOW PROCESSLIST或information_schema.TABLES查看行数。

[MySQL] MySQL遇到内存都装不下的数据表，如果要进行去重，该如何操作？