在SQL中搜索的最有效方法是什么？

Question

我有一个包含75,000+行记录的数据库，每天会添加500+条记录。

每一行都有标题和描述。

我创建了一个RSS订阅，可以根据特定搜索词提供最新的条目（例如，http://site.com/rss.rss?q=Pizza将输出与“Pizza”相关的RSS）。

我想知道编写此SQL查询的最佳方法是什么。目前我的查询语句如下：

SELECT * 
FROM 'table' 
WHERE (('title' LIKE %searcherm%) OR ('description' LIKE %searcherm%))
LIMIT 20;

但问题是执行这个查询需要花费2到10秒钟的时间。

有没有更好的方法来编写这个查询，我是否需要缓存结果（如何实现缓存？），或者是否可以通过更改数据库结构来加速查询（如使用索引）？

- supercoolville

---

可能更快的方法是将处理分成两个简单查询的单独线程/分叉：select from table where title limit 20 然后 select from table where description limit 20然后使用您的服务器端技术进行连接/列表合并。 - Brandt Solovij

Answer 1

对此比较简单的解决方案是在这两个字段上加入FULLTEXT索引，然后使用该索引进行搜索。

ALTER TABLE table ADD FULLTEXT(title, description);

那么如果你需要执行搜索，你需要进行以下操作：

SELECT id FROM table
WHERE MATCH (title, description) AGAINST ('keyterm');

全文索引搜索是大多数SQL数据库中包含的自动化解决方案。相比于使用LIKE操作，它速度更快。这也针对你的具体情况进行了优化，因为你只对自然语言搜索项感兴趣。

此外，全文索引还有一些限制性算法用于检测相关性。你可以在这里了解更多信息。

编辑

在修改语句中，我忘记了全文索引名称，应该是:

ALTER TABLE table ADD FULLTEXT ft_index_name(title, description);

- gtr32x

---

请注意，这仅适用于MyISAM表，而不是InnoDB。 - Francis Avila

非常感谢！！！我测试了一下，平均搜索速度提高了14倍！！！！！ - supercoolville

全文搜索是否也适用于数字？ - RoboticsNovice

@FrancisAvila 自 MySQL 5.6 起，InnoDB 支持全文搜索。 - Ragowit

Answer 2

尝试：

SELECT * FROM table
WHERE MATCH (title,description) AGAINST (searchterm);

请确保您在标题和描述上添加完整的文本索引。

不要试图重新发明轮子。MATCH和AGAINST是mysql提供的，可以做到这一点并使您的生活更加轻松。但是，请注意全文搜索仅适用于MyISAM表。您也可以绕过InnoDb。您只需通过修改表格添加FT索引即可：

ALTER TABLE table ADD FULLTEXT(title,description);

- jerrymouse

---

这是唯一可行的答案。请提到您需要向这些列添加全文索引，并且必须使用MyISAM表。 - Francis Avila

Answer 3

如果您使用的查询中包含 LIKE '%term%'，则无法使用索引。只有在使用类似于 'term%' 的查询时才能使用索引。想象一下带有选项卡的通讯录，您可以快速找到以字母 L 开头的联系人，但是要查找具有某个位置上出现 on 的联系人，则必须扫描整个地址簿。

更好的选择可能是使用全文索引：

CREATE FULLTEXT INDEX title_desc
ON table (title, description)

然后在查询中：

SELECT title, description FROM table
WHERE MATCH (title, description) AGAINST ('+Pizza')

- stivlo

Answer 4

我会选择JohnB或gtr32x的答案（全文索引）。为了补充他们的答案，有一种手动创建简单全文索引的方法，它非常简单且超级快...

将标题和描述拆分成关键词，并将它们放入一个Keywords表中，该表具有指向原始RSS文章的外键。确保Keywords中的关键字列已经建立索引。然后你可以这样做：

SELECT DISTINCT ra.* 
FROM RssArticle ra
INNER JOIN Keywords k ON k.ArticleID = ra.ArticleID
   WHERE k IN ( 'SearchTerm1', 'SearchTerm2', 'SearchTerm3')
LIMIT 20;

而且它很快！

- Diego

Answer 5

尝试以下四个查询之一：

select * from myTable where concat_ws(' ',title,description) like '%pizza%';
select * from myTable where concat_ws(' ',title,description) regexp '.*pizza+.*';
select title,description from myTable where concat_ws(' ',title,description) like '%pizza%';
select title,description from myTable where concat_ws(' ',title,description) regexp '.*pizza+.*';

关键是在搜索之前使用 concat

- tribal

---

这个程序大约快了2倍，但不如MATCH AGAINST快。 - supercoolville

Answer 6

一些提示：在您的选择语句中删除*，仅提取搜索条件，并确保为正在搜索的列添加索引。

SELECT `title`,`description` 
FROM `table` 
WHERE `title` LIKE '%$searchterm%' OR `description` LIKE '%$searchterm%' LIMIT 25;

- Ben Ashton

---

在你的选择语句中去掉星号，只提取搜索条件 - 那么你怎么知道他想要检索什么？ - Karoly Horvath

我不确定，但根据他的WHERE子句，我可以确定他至少需要标题和描述。无论如何，最好指定表而不是通配符，他正在询问如何加快查询速度，消除通配符是第一步。 - Ben Ashton

有一个限制为20。我怀疑它是否会产生任何可测量的差异。 - Karoly Horvath

普通索引在这里无法帮助你，因为有前导的“%”。它们只会占用空间而不被使用。（此外，您的搜索词没有正确转义。）您可以在MyISAM表上使用全文索引和MATCH运算符，但是不能在其他类型的表上使用。 - Francis Avila

Answer 7

1. 你为 title 和 description 创建了索引吗？
2. 你应该考虑使用 Sphinx 来实现全文搜索功能。

---

谢谢你的评论 Tyler。

我重申我的答案：

1）在 title 和 description 列上创建索引，但您的查询将受到下面示例的限制，这对于查找所有相关行并不理想：

SELECT * 
FROM 'table' 
WHERE title LIKE 'searcherm%' OR description LIKE 'searcherm%'
LIMIT 20;

2) 像其他人提到的那样，使用MySQL全文搜索，但你只能使用MyISAM表引擎，因为它不适用于InnoDB。不过，在MySQL中，你可以混合使用引擎，因此即使你的所有其他表都是InnoDB，你也可以将此表设置为MyISAM。

3) 使用外部全文搜索引擎，例如Sphinx。这将为您提供更相关的搜索结果（MySQL全文搜索还有很多需要改进的地方），性能也会更好，并且它会将全文搜索的负担从数据库中抽象出来。

- JohnB

---

这里索引没有帮助。像'%foo%'这样的搜索永远不会使用索引。这是他真正的问题。 - Tyler Eaves