直接JOIN百万级表会卡死或超时，因内存不足、磁盘临时文件暴涨、索引失效导致查询从秒级变小时级；需分块JOIN、建临时索引、优化执行计划。

为什么直接 JOIN 百万级表会卡死或超时

因为数据库默认走嵌套循环或哈希连接时，内存不够、磁盘临时文件暴涨、B+树索引失效，导致查询从秒级变成小时级。尤其当关联字段没索引、数据倾斜（比如一个 user_id 对应 10 万条日志），JOIN 就容易触发全表扫描+大量排序。

• 检查执行计划：用 EXPLAIN ANALYZE 看是否走了 Seq Scan 或 Hash Join 但 Rows Removed by Filter 极高
• 确认关联字段类型一致：INT 和 VARCHAR 强制隐式转换，索引直接失效
• 避免在 ON 条件里写函数：如 ON UPPER(a.name) = UPPER(b.name)，索引无法命中

分块 JOIN 的实操要点（以 PostgreSQL 为例）

核心思路是把大表按主键/时间范围切片，每次只处理几万行，避免单次内存爆掉，也方便失败重试。

• 用 WHERE id BETWEEN ? AND ? 切分主表，别用 LIMIT OFFSET——后者越往后越慢
• 每块执行前加 SET work_mem = '256MB'（仅当前 session），提升哈希表构建速度
• 结果写入临时表时用 CREATE TEMP TABLE AS SELECT ...，比逐条 INSERT 快 5–10 倍
• 示例分块逻辑：

  DO $$ 
  DECLARE i INT; 
  BEGIN 
    FOR i IN 0..9 LOOP 
      INSERT INTO result_table 
      SELECT * FROM big_table a 
      JOIN small_table b ON a.user_id = b.id 
      WHERE a.id BETWEEN i*100000 + 1 AND (i+1)*100000; 
    END LOOP; 
  END $$;

什么时候该建临时索引？怎么建才不拖慢写入

临时索引不是“所有 JOIN 都要建”，而是针对高频关联、且原表无可用索引的场景。建错位置或时机，反而让后续 INSERT/UPDATE 变慢。

• 只在关联字段上建，不要多列组合索引——除非 WHERE 里同时用了这些字段
• 用 CONCURRENTLY 创建（PostgreSQL）：CREATE INDEX CONCURRENTLY idx_temp_user_id ON big_table (user_id)，避免锁表
• 用完立刻删：DROP INDEX IF EXISTS idx_temp_user_id，别留着占空间、拖慢写操作
• 注意：MySQL 不支持 CONCURRENTLY，得选业务低峰期建，或用 ALGORITHM=INPLACE

JOIN 后数据去重与写入的隐藏开销

很多人只盯着 JOIN 本身，却忽略结果集膨胀后带来的二次处理成本——比如关联后记录翻 10 倍，DISTINCT 或 GROUP BY 会瞬间吃光内存。

• 优先在 JOIN 条件里收窄：比如加 AND b.status = 'active'，比 JOIN 完再 WHERE 更早过滤
• 避免 SELECT *：只取真正需要的字段，减少网络传输和排序内存占用
• 写入目标表前，先用 CREATE UNLOGGED TABLE（PostgreSQL）暂存，比普通表快 2–3 倍；MySQL 可用 ENGINE=MEMORY，但注意大小限制
• 如果最终要进分区表，确保 JOIN 结果已按分区键排序，否则写入时会频繁跨分区定位

实际跑通的关键，往往不在 JOIN 语法本身，而在于你是否清楚哪一步在读磁盘、哪一步在等锁、哪一步正把内存刷成 swap。分块和临时索引只是工具，真正要盯住的是执行计划里每一行的 Actual Total Time 和 Buffers 统计。