mysql - 优化 MySQL 全文查询

Question

我有一张医疗诊断代码表，用户可以根据该表执行关键字搜索。我有一列描述性文本以及一列同义词，两者都被考虑在内。结果以自动建议的格式显示，当前的查询实现对于部署来说太慢了:

SELECT
   ID AS data, CONCAT('[', ICD10, '] ', description) AS value,
   MAX(MATCH(description) AGAINST("fracture forearm current init oth" IN BOOLEAN MODE) +
      (MATCH(synonyms) AGAINST("fracture forearm current init oth" IN BOOLEAN MODE) * 0.5)) AS relevance
FROM Code
WHERE
   (MATCH(description) AGAINST("fracture forearm current init oth" IN BOOLEAN MODE) OR
    MATCH(synonyms) AGAINST ("fracture forearm current init oth" IN BOOLEAN MODE)) AND
   isPCS = 0 AND
   isEnabled = 1 AND
   ICD10 IS NOT NULL AND
   description IS NOT NULL
GROUP BY ID
ORDER BY relevance DESC
LIMIT 100

表中有约 170K 行，尽管后四个静态约束将其减少为约 94K 行，其中约 16K 行具有同义词。一个典型的查询在我的桌面 (i7-4770K) 上需要 0.45 秒，在我们的开发服务器(低端 Xeon)上大约需要 0.75 秒。删除 ORDER BY 关键字将其分别减少到 0.02 和 0.05 秒。

我原以为与全文搜索相比，对结果进行排序会很简单，但事实并非如此。我错过了明显的低效率吗？

我也在研究最终在 Lucene/Solr 之上重建此功能(欢迎提出意见/建议)，但我想更好地理解此行为，并且优化的临时解决方案也不会造成伤害.

Answer 1

如果您按相关性限制 100 排序，这意味着 MySQL 必须找到所有符合您条件的行，评估您的相关性 公式，进行文件排序，然后取其中的前 100 个。

如果不排序，则表示MySQL要找到任意100行符合条件的行，可以在那里停止执行。

所以不是找到结果后的文件排序导致速度变慢，而是必须在执行文件排序之前找到所有结果(并且可能有超过 100 行至少有一些单词您正在寻找)。

但实际上您可以在这里使用一个优化:在您的两个列上一起使用全文索引:

CREATE FULLTEXT INDEX idxft_Code_descr_syn ON Code (description, synonyms);

然后直接在两列一起搜索，直接按全文相关度排序，不重新计算:

SELECT
   ID AS data, CONCAT('[', ICD10, '] ', description) AS value,
   MATCH(description, synonyms) 
     AGAINST("fracture forearm current init oth" IN BOOLEAN MODE) AS relevance
FROM Code
WHERE
  MATCH(description, synonyms) 
    AGAINST("fracture forearm current init oth" IN BOOLEAN MODE) AND
  isPCS = 0 AND
  isEnabled = 1 AND
  ICD10 IS NOT NULL AND
  description IS NOT NULL
ORDER BY relevance
LIMIT 100

与当前订单相比，这会略微改变您的相关性，因为它不会对 synomym 列和 description 列进行权衡，但由于结果已经标准化对于他们自己的单列，您当前的权重可能无论如何都没有达到预期的效果。

order by relevance 仍然需要全表搜索，但由于全文索引的工作方式(它们应该按相关性排序)，你可能会得到一个下降的 speedbump它(尽管您提到的任何专用搜索引擎都比通用 MySQL 更快。如果 170k 行需要它们，您可以进行测试。更多的 RAM 有时也值得一试。但这是一个完全不同的话题。 )