我有一个包含数百万行的用户表。我正在实现一个搜索功能,允许某人通过键入用户名来查找用户。这个自动完成功能需要非常快。假设在mysql中,列索引使用类似于{string}%的方法来加速查询,那么以下方法的性能是否足以在200ms内返回(注意:内存开销不是问题,用户名最多30个字符)。
创建一个usersearch表,该表具有用户表的外键和索引的ngram username列:
USERSEARCH
user_id username_ngram
-------------------------
1 crazyguy23
1 razyguy23
1 azyguy23
1 zyguy23
...
查询将是:
SELECT user_id FROM myapp.usersearch WHERE username_ngram LIKE {string}%
LIMIT 10
我知道存在第三方解决方案,但出于其他原因,目前我想远离它们。这种方法在速度方面可行吗?如果db需要检查所有o(30n)行,其中n是用户数,那么我是否高估了索引的威力?
3条答案
按热度按时间i2byvkas1#
可能不会。这个
union distinct
将处理每个子查询以完成。如果您只需要任意行,请使用以下短语:
这至少可以为通用前缀节省大量时间,比如
'S'
.也就是说,这只是返回一个任意的10个列表
user_id
那时候可能还有更多。我不知道你的申请速度是否够快。你必须通过测试一组合适的数据来做出判断。
s4chpxco2#
假设固态硬盘,那应该很快,是的。
下面是一些进一步的优化:
我会加一个
DISTINCT
因为多次返回同一个用户id是没有意义的。尤其是在搜索非常常见的前缀时,例如单个字母。也可以考虑只搜索至少3个字母的输入。less往往是毫无意义的(因为希望你的用户名至少有3个字符长),而且对你的数据库来说是不必要的。
如果您不想再添加任何列(我希望您没有,因为这个表是用来快速搜索的!),我们可以做得更好。交换列。生成主键(username\u ngram,user\u id)。这样,您就可以直接在主键上搜索(注意结果字母顺序的额外好处!好。。。匹配后缀的字母顺序,即不是完整的用户名。)
确保你有一个关于用户id的索引,以便在需要更改用户名时能够替换用户的所有内容(为此,只需删除该用户标识的所有行并插入全新的行。)
也许我们可以做得更好。因为这只是为了快速搜索,所以可以使用
READ_UNCOMMITTED
. 如果我没弄错的话,这样可以避免放置任何读锁,而且应该更快。它可以读取未提交的数据,但是。。。之后,您只需在另一个表中查询任何生成的用户id,如果该用户仍在创建中,则可能找不到它们。你没有失去任何东西。:)tuwxkamq3#
我认为您需要使用mysql全文索引来提高性能。您需要更改语法以使用全文索引。
创建全文索引:
CREATE FULLTEXT INDEX ix_usersearch_username_ngram ON usersearch(username_ngram);
mysql官方文档如何使用全文索引:https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html