如何通过关联表实现sentences表自连接查询？优化超时问题

数据表结构 #

现有两张数据表：

sentences 表 #

id | lang | sentence 

links 表 #

sentence_id | translation_id

实际查询使用的是与sentences结构一致的sentences_unicode表。

查询故障 #

执行以下SQL查询波兰语中包含"pasek"的句子及其翻译时，出现超时并触发报错：

MySQL said: Documentation
#2006 - MySQL server has gone away

对应的SQL语句：

SELECT * 
FROM sentences_unicode AS s
INNER JOIN links AS l ON l.sentence_id = s.id
INNER JOIN sentences_unicode AS t ON l.translation_id = t.id
WHERE s.lang = 'pol' AND s.sentence LIKE "%pasek%"

推测原因是表内有数百万条数据，无索引导致全表扫描，查询效率过低。

已尝试方案 #

仅能成功执行单关联查询，耗时约26秒：

SELECT * 
FROM sentences_unicode AS s
INNER JOIN links AS l ON l.sentence_id = s.id
WHERE s.lang = 'pol' AND MATCH (s.sentence) AGAINST ('pasek' IN NATURAL LANGUAGE MODE)

需求 #

寻求更高效的方式，获取符合搜索条件的句子及其对应翻译。

1. 给关键字段添加索引 #

当前表结构无任何索引，这是查询缓慢的核心原因，优先添加以下索引：

• 给sentences_unicode表添加复合索引（适配语言+文本查询）：

  CREATE INDEX idx_sentences_lang_sentence ON sentences_unicode (lang, sentence(255));
  -- 若使用全文搜索，需创建全文索引：
  CREATE FULLTEXT INDEX ft_idx_sentences_sentence ON sentences_unicode (sentence);
  

• 给links表添加关联字段索引（加速表关联）：

  -- 单字段索引
  CREATE INDEX idx_links_sentence_id ON links (sentence_id);
  CREATE INDEX idx_links_translation_id ON links (translation_id);
  -- 或联合索引（更适配多关联场景）
  CREATE INDEX idx_links_sentence_translation ON links (sentence_id, translation_id);
  

2. 优化查询语句 #

• 避免SELECT *，只查询需要的字段，减少数据传输量：

  SELECT s.id AS src_id, s.lang AS src_lang, s.sentence AS src_sentence,
         t.id AS trans_id, t.lang AS trans_lang, t.sentence AS trans_sentence
  FROM sentences_unicode AS s
  INNER JOIN links AS l ON l.sentence_id = s.id
  INNER JOIN sentences_unicode AS t ON l.translation_id = t.id
  WHERE s.lang = 'pol' 
    AND MATCH(s.sentence) AGAINST('pasek' IN NATURAL LANGUAGE MODE);
  

• 若必须使用LIKE，尽量使用前缀匹配（仅当关键词在文本开头时有效）：

  WHERE s.lang = 'pol' AND s.sentence LIKE 'pasek%'
  

3. 分步骤拆分查询，降低关联压力 #

先筛选出符合条件的源句子ID，再关联获取翻译内容：

-- 第一步：临时存储符合条件的波兰语句子ID
CREATE TEMPORARY TABLE temp_src_ids 
SELECT id FROM sentences_unicode 
WHERE lang = 'pol' AND MATCH(sentence) AGAINST('pasek' IN NATURAL LANGUAGE MODE);

-- 给临时表加索引，加速后续关联
CREATE INDEX idx_temp_src_id ON temp_src_ids(id);

-- 第二步：关联获取翻译内容
SELECT s.sentence AS src_sentence, t.sentence AS trans_sentence
FROM temp_src_ids AS temp
INNER JOIN sentences_unicode AS s ON temp.id = s.id
INNER JOIN links AS l ON s.id = l.sentence_id
INNER JOIN sentences_unicode AS t ON l.translation_id = t.id;

-- 清理临时表
DROP TEMPORARY TABLE temp_src_ids;

4. 调整MySQL配置参数 #

针对server has gone away报错，可适当调整超时及数据包参数（修改my.cnf/my.ini后重启服务）：

wait_timeout = 300
interactive_timeout = 300
max_allowed_packet = 64M

内容的提问来源于stack exchange，提问作者Michał Ziobro