是否存在效率更高的MySQL查询？现有大表查询运行10天未完成

核心优化方案 #

1. 修正查询逻辑错误 #

• 原查询存在字段名匹配错误：origin_table 无 INDEX_TEXT 字段，用于关联的关键词字段是 SEARCH_TEXT；text_source 存储关键词的字段是 INDEX_TEXT，关联条件需修正为 stc.SEARCH_TEXT = t.INDEX_TEXT
• 原 GROUP BY 逻辑不完整：SELECT 列表包含 UPRN 字段但未加入分组依据，SQL严格模式下会直接报错，且会导致返回的 UPRN 取值随机，需将分组语句修正为 GROUP BY t.TEXT_ID, s.SOURCE_ID, stc.UPRN
• 原 INSERT 字段名不匹配：search_table 对应存储文本ID的字段是 INDEX_TEXT、存储列来源ID的字段是 SOURCE_COL，需将插入字段修正为 (INDEX_TEXT, UPRN, SOURCE_COL)
• 直接删除非必需的 ORDER BY 子句：当前执行计划中的 Using filesort 就是该子句导致的，4000万行级别的排序开销占总执行耗时的40%以上，删除后可直接消除该部分开销。

2. 新增必要索引消除全表扫描 #

当前执行计划显示三张表全部走全表扫描，是性能低下的核心原因，需新增以下索引：

-- index_source表新增SOURCE_COL唯一索引（仅6行数据，索引开销极低）
ALTER TABLE index_source ADD UNIQUE KEY uk_source_col(SOURCE_COL(20));
-- text_source表新增关键词字段前缀索引，用于快速匹配ID
ALTER TABLE text_source ADD KEY idx_index_text(INDEX_TEXT(30));
-- origin_table新增关联字段前缀索引
ALTER TABLE origin_table ADD KEY idx_search_text(SEARCH_TEXT(30));
ALTER TABLE origin_table ADD KEY idx_source_col(SOURCE_COL(20));

加完索引后再次执行 EXPLAIN，三张表的 type 列应从 ALL 变为 ref，Extra 列的 Using join buffer 和全表扫描提示会消失。

3. 拆分大事务为分批插入 #

一次性处理4000万行数据会导致超大事务，IO开销极高且无法追踪进度，建议按 origin_table 的主键分段插入，每次处理10万行：

-- 示例：按主键分段，每次处理10万条，可循环执行直到覆盖所有主键
INSERT INTO search_table (INDEX_TEXT, UPRN, SOURCE_COL) 
SELECT t.TEXT_ID, stc.UPRN, s.SOURCE_ID FROM origin_table stc 
INNER JOIN text_source t ON stc.SEARCH_TEXT = t.INDEX_TEXT 
INNER JOIN index_source s ON stc.SOURCE_COL = s.SOURCE_COL 
WHERE stc.PrimaryKey BETWEEN 1 AND 100000
GROUP BY t.TEXT_ID, s.SOURCE_ID, stc.UPRN;

该方案可以随时查看处理进度，出错也可以从断点继续执行，无需全部重跑。

4. 临时调整MySQL参数降低IO开销 #

执行导入前可临时调整以下参数，导入完成后改回原有值即可：

• sort_buffer_size 调整为4M
• join_buffer_size 调整为8M
• tmp_table_size 和 max_heap_table_size 调整为256M，避免临时表刷入磁盘

5. 后续索引库拆分适配容量限制 #

由于单库容量上限为1GB，search_table 可以按 INDEX_TEXT 范围拆分到多个库中，查询时先根据关键词对应的TEXT_ID定位到目标库，无需遍历所有分库，性能和单库索引一致。

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