mysql - SQL查询运行速度非常慢

Question

我正在运行此查询来搜索数据库:

SELECT
    IFNULL(firstname, '') AS firstname,
    IFNULL(lastname, '') AS lastname,
    IFNULL(age, ' ') AS age,
    email,
    telephone,
    comments,
    ref
FROM person
RIGHT JOIN
    order ON person.oID = order.ref
WHERE
    LOWER(firstname) LIKE LOWER ('%{$search}%') OR
    LOWER(lastname) LIKE LOWER ('%{$search}%') OR
    LOWER(email) LIKE LOWER ('%{$search}%') OR
    LOWER(telephone) LIKE LOWER ('%{$search}%') OR
    LOWER(ref) LIKE LOWER ('%{$search}%');

它正在进行大量处理，但如何才能更快地获得这些结果？该页面加载大约需要 6-7 秒，如果我在 PHPMyAdmin 中运行查询，则查询需要 3-4 秒才能运行。它的数据库并不大，只有 3000 个条目左右。我已向 ref、email、firstname 和 lastname 列添加了索引，但这似乎并没有使任何差异。有人可以帮忙吗？

Answer 1

这个查询速度慢的原因是你以最慢的方式组合了 MySQL 的两个方便但速度慢的功能。

1. FUNCTION(column) LIKE %matchstring% 需要扫描表格；没有有序索引可以帮助满足此搜索，因为它是 unanchored 。

2. condition OR condition OR condition 要求每个 OR 子句重新扫描表一次。

如果您正确设置了列排序规则，您还碰巧忽略了 MySQL 的搜索已经不区分大小写的事实。

最后，尚不清楚您要对经过 RIGHT JOIN 的表数据做什么。结果集中的哪些列来自该表？如果您不需要该表中的数据，请将其删除。

所以，总而言之，你所拥有的就是慢x多。

那么，如何解决这个问题呢？最重要的是您要尽可能多地删除这些 unanchored 扫描。如果你可以将它们更改为

 email LIKE '{$search}%' 

因此，LOWER() 函数和 LIKE 项中的前导 % 可以被消除，您将获得巨大的胜利。

如果这种全网搜索功能对您的应用程序至关重要，您应该考虑使用 MySQL 全文搜索。

或者您可以考虑在表中创建一个新列，该新列是您当前搜索的所有列的串联，这样您只需搜索一次。

<小时/>

编辑以解释LIKE缓慢

如果 haystack 列已建立索引，则搜索 haystack LIKE 'needle%' 运行得相当快。这是因为 BTREE 样式索引本质上是有序的。为了以这种方式搜索，MySQL 可以随机访问第一个可能的匹配项，然后顺序扫描到最后一个可能的匹配项。

但是搜索 haystack LIKE '%needle%' 无法使用随机访问来查找索引中第一个可能的匹配项。第一个可能的匹配可能在任何地方。因此它必须一一扫描haystack的所有值来寻找needle。