mysql - 搜索多个字段mysql的最快方法

Question

我目前在一个 mysql 用户表中有一个名字和姓氏字段，以及一个可以输入两者的搜索框，所以我的查询基本上是

SELECT name,surname FROM user
WHERE CONCAT(name,' ',surname) LIKE '@search%'

这工作正常，但在大表上速度很慢，即使名称和姓氏列都已编入索引，并且当搜索被分解时它很快，有没有办法索引连接的字段或者它们是否已经编入索引当两个字段都被索引时，切换到全文索引会更快吗？

Answer 1

我假设 @search 被某些东西取代了吗？

如果您正在搜索“单词”，那么FULLTEXT 的工作速度显着 更快。但是，请注意它的局限性(停用词、最小字长等)。

那个特定的尝试，CONCAT(name,' ',surname) LIKE '@search%'，只会检查 name 的开头是否有 '@search'；我怀疑这是否是你想要的。你想要什么？

请记住，name LIKE '@search%' 比 name LIKE '%search%' 快很多，因为它可以使用 INDEX(name )，我假设你有？

如果您同时拥有 INDEX(name) 和 INDEX(surname)，那么这会相当快:

SELECT ...
    WHERE name LIKE '@search%'
UNION DISTINCT
SELECT ...
    WHERE surname LIKE '@search%';

这是因为两个索引都可以使用。否则，它们不太可能同时被使用；取而代之的是“表扫描”。 (如果您使用的是旧版本的 MySQL，则 OR 的使用效果不佳。)

但是，如果用户以“%”或“_”开始@search，它将变得缓慢，因为前导通配符会导致表扫描。

同时，如果您不检查各种特殊字符，您很容易受到 SQL 注入(inject)攻击!

澄清后

由于用户分别输入名称，因此您可以更轻松地进行测试:

WHERE name    LIKE '@name%'
  AND surname LIKE '@surname%'

然后同时拥有这两个(以防用户缩短其中一个):

INDEX(name, surname)
INDEX(surname, name)

那些比你拥有的INDEX(name), INDEX(surname)。

由于数据输入似乎是单个字段，请使用您的客户端编程语言将其拆分为@name 和@surname。 (这是可能的，但在 SQL 中很困惑；请参阅 SUBSTRING_INDEX()。)

阅读“复合索引”以了解为什么这些索引更好，以及为什么原始 CONCAT 特别低效。