如何优化MySQL插入查询性能？解决超时及连接丢失问题

我有一条INSERT...SELECT查询，有时运行时间超过30秒，触发「error code 2013. lost connection to mysql query」错误，查询语句如下：

INSERT INTO elogbook_get_boardid
SELECT DISTINCT
Y.`LOTID`, 
`Board_ID`, 
`Serial_Number`, 
coalesce(CASE WHEN A.`Serial_Number` = X.`board_sn` THEN 'In Use' ELSE 
A.`status` END, '') AS `Status`,
coalesce(Y.`LOT_LOCATION`,'') AS `chamber`, 
coalesce(X.`created_date`, '') AS `Start Date`, 
coalesce(Y.`BINOUT_DUE_DATE`, '') AS `Est End`, 
CURRENT_TIMESTAMP AS `Current Time` 
FROM hardware_tracking_msa.HAST_Detail A 
JOIN skynet_msa.lots_to_hast_boards X 
LEFT JOIN skynet_msa.labs_inventory Y 
ON X.`lotid` = Y.`LOTID` 
ON A.`Serial_Number` = X.`board_sn`;

该查询每次仅插入约60行，但运行效率极低，以下是补充信息及优化方案：

补充信息 #

目标表elogbook_get_boardid结构 #

CREATE TABLE `elogbook_get_boardid` (
  `LOTID` varchar(45) DEFAULT NULL,
  `Board_ID` varchar(45) DEFAULT NULL,
  `Serial_Number` varchar(45) DEFAULT NULL,
  `Status` varchar(45) DEFAULT NULL,
  `chamber` varchar(45) DEFAULT NULL,
  `Start Date` varchar(45) DEFAULT NULL,
  `Est End` varchar(45) DEFAULT NULL,
  `Current Time` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

目标表字段详情 #

EXPLAIN SELECT结果 #

EXPLAIN ANALYZE结果 #

-> Table scan on <temporary>  (actual time=0.001..0.007 rows=80 loops=1)
-> Temporary table with deduplication  (cost=11716.50 rows=116480) (actual time=2.047..2.056 rows=80 loops=1)        
-> Filter: (convert(hardware_tracking_msa.A.Serial_Number using utf8mb4) = X.board_sn)  (cost=11716.50 rows=116480) (actual time=1.207..1.825 rows=82 loops=1)            
-> Inner hash join (<hash>(convert(hardware_tracking_msa.A.Serial_Number using utf8mb4))=<hash>(X.board_sn))  (cost=11716.50 rows=116480) (actual time=1.206..1.807 rows=82 loops=1)                
-> Table scan on A  (cost=1.91 rows=1456) (actual time=0.031..0.458 rows=1456 loops=1)                
-> Hash                    
-> Nested loop left join  (cost=61.67 rows=80) (actual time=0.069..1.042 rows=80 loops=1)                        
-> Table scan on X  (cost=8.67 rows=80) (actual time=0.021..0.490 rows=80 loops=1)                        
-> Filter: (X.lotid = Y.LOTID)  (cost=0.56 rows=1) (actual time=0.007..0.007 rows=1 loops=80)                            
-> Single-row index lookup on Y using PRIMARY (LOTID=X.lotid)  (cost=0.56 rows=1) (actual time=0.006..0.006 rows=1 loops=80)

优化方案 #

从执行计划可以看出，查询慢的核心原因是全表扫描、字符集转换开销、临时表去重，针对性优化如下：

1. 添加索引消除全表扫描 #

当前A表和X表的连接字段无索引，导致全表扫描和哈希连接，直接添加索引：

-- 给X表的board_sn加索引，用于连接A表
CREATE INDEX idx_lots_to_hast_boards_board_sn ON skynet_msa.lots_to_hast_boards(board_sn);

-- 给A表的Serial_Number加索引，加速连接匹配
CREATE INDEX idx_hast_detail_serial_number ON hardware_tracking_msa.HAST_Detail(Serial_Number);

2. 统一字符集避免转换开销 #

EXPLAIN ANALYZE显示A.Serial_Number需要转换为utf8mb4才能和X.board_sn匹配，字符集不一致会导致每次连接都做转换，消耗CPU和时间：

• 先检查两个字段的字符集：

  SHOW FULL COLUMNS FROM hardware_tracking_msa.HAST_Detail LIKE 'Serial_Number';
  SHOW FULL COLUMNS FROM skynet_msa.lots_to_hast_boards LIKE 'board_sn';
  

• 将两个字段统一为相同字符集（比如utf8mb4）：

  ALTER TABLE hardware_tracking_msa.HAST_Detail 
  MODIFY COLUMN Serial_Number VARCHAR(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  

3. 移除不必要的DISTINCT #

执行计划显示连接后仅82行，去重后80行，DISTINCT会触发临时表去重，增加额外开销：

• 如果业务逻辑能保证X.board_sn是唯一值，连接后不会产生重复数据，直接删除SELECT后的DISTINCT；
• 如果必须去重，可通过调整连接逻辑（比如确认X表的唯一性约束）避免重复，替代DISTINCT的临时表开销。

4. 优化目标表结构（可选） #

目标表所有字段为字符串类型且无索引，虽然不影响当前查询，但可优化后续使用体验：

• 将日期字段改为DATETIME类型，避免字符串存储的空间浪费和转换开销：

  ALTER TABLE elogbook_get_boardid 
  MODIFY COLUMN `Start Date` DATETIME DEFAULT NULL,
  MODIFY COLUMN `Est End` DATETIME DEFAULT NULL,
  MODIFY COLUMN `Current Time` DATETIME DEFAULT CURRENT_TIMESTAMP;
  

• 若后续需基于Serial_Number查询，可添加索引：

  CREATE INDEX idx_elogbook_serial_number ON elogbook_get_boardid(Serial_Number);
  

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