sql - 在为自动完成/迭代搜索提供结果时，您如何优化数据库性能？

Question

注意:在这个问题中，我使用术语“自动完成”(或“迭代搜索”)来指代返回搜索结果，例如就像 Google 搜索为您提供的那样。此外，我的问题不是特定于 Web 应用程序与胖客户端应用程序。

通常如何构造 SQL SELECT 查询来为此类查询提供良好的性能，尤其是在任意大的数据集上？在搜索仅基于前 n 个字符(最简单的情况)进行查询的情况下，我是否仍然在每次按键时发出一个新的 SELECT 结果 FROM sometable WHERE entry LIKE...。即使使用各种形式的缓存，这似乎也可能导致性能不佳。

如果您希望搜索字符串返回带有前缀匹配、子字符串匹配等的结果，这是一个更加困难的问题。查看搜索联系人列表的情况，您可能会返回匹配 FirstName + LastName、LastName + FirstName 或任何其他子字符串的结果。

Answer 1

Google、Yahoo 等搜索使用全文索引来生成高性能的关键字列表。

如果您对单个单词列进行迭代搜索，则不需要全文索引和关键字。您可以在索引列本身上使用 LIKE。

由于您在输入时进行搜索，因此您只进行前缀匹配。使用 LIKE 子句和通配符进行“前缀”搜索时，您的索引列仍将获得正常性能。

SELECT last_name FROM users WHERE last_name LIKE 'Adam%'

如果您需要从另一端搜索，您将需要一个反向索引，但幸运的是，人们不会向后输入。

您将为每个“迭代搜索”发出一个新的 SELECT 语句，但在计时器上。只有当他们停止输入时，您才发出另一个查询。您将使用 LIMIT 或 TOP 限制结果集，以便查询可以在填充 10 条左右的记录后立即完成。此外，这样您只需通过网络发送 10 条记录。

SELECT last_name FROM users WHERE last_name LIKE 'Adam%' LIMIT 10

当然，为了获得最佳性能，last_name 将是主索引。索引允许数据库在不命中实际记录的情况下获取值。主索引通常是连续的，这使得它们更快。

如果偶然，您正在搜索一列，但返回另一列，则使用复合索引，以便数据库引擎仍然可以从索引本身获取值，而不会命中记录。

SELECT first_name FROM users WHERE last_name LIKE 'Adam%' LIMIT 10

对于上述查询，主索引将是 (last_name, first_name)。

计时器是性能的关键。您可以调整计时器以获得所需的性能。