提升MySQL查询与许多LIKE条件

Question

有一个约25K行的表，用户可以提供关键字和负关键字来过滤行。当用户添加了大量关键字和/或负面关键字时，速度会减慢。

查询如下：

SELECT id, title, description
FROM entities
WHERE 
(
title LIKE '%keyword_1%' OR description LIKE '%keyword_1%' 
OR title LIKE '%keyword_2%' OR description LIKE '%keyword_2%' 
OR title LIKE '%keyword_3%' OR description LIKE '%keyword_3%' 
)
AND
(
title NOT LIKE '%negative_keyword_1%' OR description NOT LIKE '%negative_keyword_1%' 
OR title NOT LIKE '%negative_keyword_2%' OR description NOT LIKE '%negative_keyword_2%' 
OR title NOT LIKE '%negative_keyword_3%' OR description NOT LIKE '%negative_keyword_3%' 
)

例如，一个包含9个关键字和130个否定关键字的查询大约需要7秒。也许有更好的解决方案来过滤那些没有LIKE的行？也许整个逻辑是错误的。

尝试了MATCH((AGINST((-由于某些原因，它比LIKE慢。

Answer 1

对于像您这样的情况，尝试过滤数据库中的阳性匹配，然后测试它们在内存中是否存在阴性关键字可能会有所帮助。所以试试这样的东西：

SELECT id, title, description
FROM entities
WHERE 
(
title LIKE '%keyword_1%' OR description LIKE '%keyword_1%' 
OR title LIKE '%keyword_2%' OR description LIKE '%keyword_2%' 
OR title LIKE '%keyword_3%' OR description LIKE '%keyword_3%' 
)

这将为您提供与阳性关键字匹配的结果。然后，您可以过滤掉内存中包含否定关键字的匹配项。

也给索引一个与LIKE一起使用的机会。这将使您的搜索选项从CONTAINS减少到STARTS WITH，但这可能足以满足您的情况。语法为LIKE 'keyword_1%'。

全文搜索

另一种选择是通过在title和description列上定义全文索引来使用MySQL全文搜索。

CREATE FULLTEXT INDEX idx_title ON entities(title);
CREATE FULLTEXT INDEX idx_description ON entities(description);

或者，您可以将这两列合并为一列，用于搜索目的。那么你只需要一个全文索引。

查询语法如下：

MATCH (title) AGAINST ('keyword_1') 

而不是

title LIKE '%keyword_1%'

对于这个搜索，我还建议只过滤数据库中的阳性匹配，然后过滤掉内存中包含阴性关键字的匹配。