快速字符串匹配的MySQL查询

Question

我有一个MySQL数据库（InnoDB），其中包含一个名为users的表，其中有一个username列。在一个Ruby on Rails网站上，我有一个功能，允许用户通过他们的用户名搜索某个人。在输入时，网站会建议用户搜索谁（带bootstrap的typeahead）。目前，我正在使用MySQL中的查询：SELECT `username` FROM users WHERE `username` LIKE 'the_query%' LIMIT 15; 存在的问题是速度。该表大约有500,000行，像这样的查询似乎需要大约一秒钟的时间，当即时生成猜测时，这太慢了。我该怎么做才能提高性能？100毫秒以下的速度将是合适的。也许有比使用MySQL处理搜索更好的方法？

- IvyCode

Answer 1

正如Keshan所提到的那样，我最终在用户名列上添加了一个FULLTEXT索引（之前只有唯一索引），这似乎将查询速度提高了10倍。查询大约需要90到120毫秒。

- IvyCode

---

是的，因为它只会找到 LIKE 找到的十分之一。 - Martin Zvarík

Answer 2

如果您从用户名+密码创建MD5哈希，则速度会更快。因为每行的长度始终为32个字符，所以您可以在此字段上设置索引，并使用索引字段上的非常快速搜索。

- Mutatos

Answer 3

• 在用户表中创建索引以获得更快的结果（这将确实帮助更快地返回结果）。
• 确保用户在输入一些字符后即可获得结果（不仅限于单个字符搜索）。

- parveen

---

我忘了提到我已经在用户名列上创建了索引。虽然那个检查可能会有些帮助，但我仍想允许单字符搜索。谢谢！ - IvyCode

Answer 4

如果您只想从username字符串的开头进行搜索，并与子字符串匹配，那么使用子字符串可能会更快： SELECT `username` FROM users WHERE SUBSTRING(`username`, 0, CHAR_LENGTH('the_query'))='the_query';

- concat

---

我尝试了一下，但似乎速度没有改善。另外，我已经更正了查询语句为“SUBSTRING（username，1，CHAR_LENGTH ...）”。感谢您的帮助！ - IvyCode

在这种情况下，我同意用户的观点：索引将是提高速度最简单的方法。虽然我没有太多的经验，但也许一个FULLTEXT索引会有所帮助？否则，您可能需要创建一个新的、包含第一个字符的ASCII代码的索引列，并首先使用该标准进行搜索？ - concat

Answer 5

尝试限制获得的匹配数。如果是搜索栏，您不能在搜索列表中显示成千上万条记录。但从上面的查询中，我猜服务器将所有结果发送到前端。然后您可以从中筛选记录。除此之外，请尝试使用以下查询。

将记录数限制为10条...

SELECT username FROM users WHERE username LIKE 'the_query%' LIMIT 10;

- Keshan Fernando

---

哎呀，忘了提到我将它限制在15个上。将其降低到5个，查询仍然需要大约1秒钟的时间。 - IvyCode

SELECT username FROM users WHERE MATCH (username) AGAINST ('the_query') - Keshan Fernando

FULLTEXT 索引完成了任务。谢谢！ - IvyCode

@lvyCode，你的查询速度现在是多少？ - Dyrandz Famador