MySQL多表关联插入时丢失连接，咨询分步执行可行性

嘿，这个问题我之前处理过类似的场景，咱们来一步步拆解解决它～

首先，出现lost connection to MySQL的核心原因，大概率是你一次性关联4张大数据量表（两张600K、两张50K）时，查询需要加载大量数据到内存，导致内存耗尽、查询超时，或者数据库连接被强制断开。

分步执行是否可行？当然是首选方案！ #

你提到的分步关联插入思路完全正确，通过拆分查询、用临时表存储中间结果，能大幅降低单次查询的资源占用，避免连接中断。具体可以这么做：

步骤1：创建临时表存储第一组关联结果 #

先把whs_asset和dfd的关联结果存到临时表，这两张表的关联逻辑最直接：

CREATE TEMPORARY TABLE temp_step1 AS
SELECT Iwa.a_id, whfd.* 
FROM ICT.whs_asset Iwa 
INNER JOIN wh.dfd whfd ON whfd.field_id = Iwa.o_id;

临时表默认只在当前会话有效，不用手动删除，会话结束会自动清理。

步骤2：关联临时表与dd表，生成第二组中间结果 #

给临时表的dt_id加个索引（加快后续关联速度），再关联wh.dd表：

CREATE INDEX idx_temp_step1_dt_id ON temp_step1(dt_id);

CREATE TEMPORARY TABLE temp_step2 AS
SELECT 
  ts1.a_id, 
  wh.id AS wh_id, 
  ts1.s_id, 
  ts1.f_name, 
  ts1.d_type, 
  ts1.d_size, 
  ts1.d_precision, 
  ts1.is_nullable, 
  ts1.d_value, 
  ts1.is_indexed, 
  ts1.field_id
FROM temp_step1 ts1
INNER JOIN wh.dd wh ON ts1.dt_id = wh.id;

步骤3：关联最终表并插入数据 #

最后把temp_step2和w_table关联，插入目标表：

CREATE INDEX idx_temp_step2_wh_id ON temp_step2(wh_id);

INSERT INTO `ICT`.`whs_field` (`a_id`, `table_id`, `s_id`, `f_name`, `d_type`, `d_size`, `d_precision`, `nul`, `d_value`, `ind`, `o_id`)
SELECT 
  ts2.a_id, 
  ivt.a_id, 
  ts2.s_id, 
  ts2.f_name, 
  ts2.d_type, 
  ts2.d_size, 
  ts2.d_precision, 
  ts2.is_nullable, 
  ts2.d_value, 
  ts2.is_indexed, 
  ts2.field_id
FROM temp_step2 ts2
INNER JOIN ICT.w_table ivt ON ivt.o_id = ts2.wh_id;

其他补充优化方案 #

如果分步后还是有问题，或者想进一步提高效率，可以试试这些方法：

1. 分批插入 #

如果临时表的数据量依然很大，那就分批次插入目标表，比如每次插入1000条（避免单次插入占用过多IO和内存）：

SET @last_id = 0;
REPEAT
  INSERT INTO `ICT`.`whs_field` (`a_id`, `table_id`, `s_id`, `f_name`, `d_type`, `d_size`, `d_precision`, `nul`, `d_value`, `ind`, `o_id`)
  SELECT 
    ts2.a_id, 
    ivt.a_id, 
    ts2.s_id, 
    ts2.f_name, 
    ts2.d_type, 
    ts2.d_size, 
    ts2.d_precision, 
    ts2.is_nullable, 
    ts2.d_value, 
    ts2.is_indexed, 
    ts2.field_id
  FROM temp_step2 ts2
  INNER JOIN ICT.w_table ivt ON ivt.o_id = ts2.wh_id
  WHERE ts2.a_id > @last_id
  ORDER BY ts2.a_id
  LIMIT 1000;
  
  SET @last_id = (SELECT MAX(a_id) FROM `ICT`.`whs_field`);
UNTIL ROW_COUNT() = 0 END REPEAT;

2. 优化关联字段的索引 #

检查所有关联字段是否有索引，这是提升查询速度的关键：

• 给wh.dfd的field_id和dt_id加索引：

  CREATE INDEX idx_dfd_field_id ON wh.dfd(field_id);
  CREATE INDEX idx_dfd_dt_id ON wh.dfd(dt_id);
  

• 给ICT.whs_asset的o_id加索引：

  CREATE INDEX idx_whs_asset_o_id ON ICT.whs_asset(o_id);
  

• 给ICT.w_table的o_id加索引：

  CREATE INDEX idx_w_table_o_id ON ICT.w_table(o_id);
  

（如果字段已经是主键或唯一键，就不需要重复加索引了）

3. 调整MySQL配置参数 #

如果是因为内存不足或超时导致的连接中断，可以调整以下配置（修改my.cnf/my.ini后重启MySQL生效）：

• max_allowed_packet：增大到64M或128M，避免数据包过大被截断；
• wait_timeout/interactive_timeout：增大到3600秒，延长连接超时时间；
• innodb_buffer_pool_size：如果用的是InnoDB引擎，设置为服务器内存的50%-70%，提升缓存效率。

总结 #

分步执行拆分查询是解决这个问题最直接有效的方法，再结合索引优化、分批插入，基本能搞定连接中断的问题。如果还是不行，再调整MySQL的配置参数来适配大数据量查询。

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