这种在MySQL中进行快速子字符串搜索的策略够快吗？

提问者：小点点

这种在MySQL中进行快速子字符串搜索的策略够快吗？

我有一个有数百万行的用户表。我正在实现一个搜索功能，允许某人通过键入用户名或姓名来查找用户。这个自动完成功能需要非常快。考虑到在MySQL中，列索引使用{string}%加快查询速度，以下方法的性能是否足以在200ms内返回？（注意:这里内存开销不是问题，用户名/名称最多30个字符）。

创建一个USERSEARCH表，该表具有指向user表的外键和索引的ngram username和name列:

    USERSEARCH

    user_id    username_ngram    name_ngram
    ---------------------------------------
    1          crazyguy23        John Smith  
    1          razyguy23         ohn Smith    
    1          azyguy23          hn Smith 
    1          zyguy23           n Smith
    ...

然后查询为:

    SELECT user_id FROM myapp.usersearch WHERE username_ngram LIKE {string}%
    UNION DISTINCT
    SELECT user_id FROM myapp.usersearch WHERE name_ngram LIKE {string}%
    LIMIT 10

我知道第三方解决方案是存在的，但出于其他原因，我现在想远离他们。就速度而言，这种做法是否可行？如果db需要检查所有O(30n)行（其中n是用户数），我是否高估了索引的威力？

好处:限制10是否会在第一次选择中发现10个匹配项时停止整个查询？或者限制10过滤器发生在联合的末尾？

共1个答案

匿名用户

可能不是。 Union Distinction将处理每个子查询直到完成。

如果只需要任意行，可以将其表述为:

(SELECT user_id
 FROM myapp.usersearch
 WHERE username_ngram LIKE {string}%
 LIMIT 10
) UNION DISTINCT
(SELECT user_id
 FROM myapp.usersearch
 WHERE username_ngram LIKE {string}%
 LIMIT 10
)
LIMIT 10;

这至少可以为常见的前缀节省大量的时间，比如，'s'。

也就是说，当可能有更多user_id时，这只返回一个包含10个user_id的任意列表。

我不知道对于你的应用程序来说，速度是否足够快。你必须通过测试一组适当的数据来做出判断。