主持人 > 博客 > 管理 > 搜索结果输出和性能问题

搜索结果输出和性能问题

我们熟悉的所有应用程序中的典型场景之一是根据一定的条件搜索数据并以易于阅读的形式显示它。 还可能有用于排序、分组和分页的附加选项。 从理论上讲，这个任务是微不足道的，但在解决它时，许多开发人员会犯一些错误，从而导致生产力下降。 让我们尝试考虑解决此问题的各种选项，并提出选择最有效的实施方案的建议。

搜索结果输出和性能问题

分页选项#1

我想到的最简单的选项是以最经典的形式逐页显示搜索结果。

搜索结果输出和性能问题
假设您的应用程序使用关系数据库。 在这种情况下，要以此形式显示信息，您将需要运行两个 SQL 查询：

  • 获取当前页面的行。
  • 计算与搜索条件相对应的总行数 - 这是显示页面所必需的。

让我们以测试 MS SQL 数据库为例来看看第一个查询 冒险工厂 2016年服务器。 为此，我们将使用 Sales.SalesOrderHeader 表：

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

上面的查询将返回列表中的前 50 个订单，按添加日期降序排序，换句话说，即最近的 50 个订单。

它在测试库上运行得很快，但我们看一下执行计划和 I/O 统计数据：

搜索结果输出和性能问题

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

您可以通过在查询运行时运行 SET STATISTICS IO ON 命令来获取每个查询的 I/O 统计信息。

从执行计划中可以看出，最消耗资源的选项是按添加日期对源表的所有行进行排序。 问题是表中出现的行越多，排序就越“困难”。 实际中应该避免这样的情况，所以我们给添加日期添加一个索引，看看资源消耗是否发生了变化：

搜索结果输出和性能问题

Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

显然情况已经好很多了。 但所有问题都解决了吗？ 让我们更改查询以搜索商品总成本超过 100 美元的订单：

SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

搜索结果输出和性能问题

Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

我们遇到了一个有趣的情况：查询计划并不比前一个差多少，但实际的逻辑读取次数几乎是全表扫描的两倍。 有一个出路 - 如果我们从一个已经存在的索引创建一个复合索引，并将商品总价添加为第二个字段，我们将再次获得 165 次逻辑读取：

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

这一系列的例子可以持续很长一段时间，但是我在这里想表达的两个主要想法是：

  • 向搜索查询添加任何新标准或排序顺序都会对搜索查询的速度产生重大影响。
  • 但是，如果我们只需要减去部分数据，而不是所有与搜索词匹配的结果，则有很多方法可以优化此类查询。

现在让我们继续讨论一开始提到的第二个查询 - 计算满足搜索条件的记录数。 让我们举同样的例子 - 搜索超过 100 美元的订单：

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

鉴于上述综合指数，我们得到：

搜索结果输出和性能问题

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

查询遍历整个索引这一事实并不奇怪，因为 SubTotal 字段不在第一个位置，因此查询无法使用它。 通过在 SubTotal 字段上添加另一个索引解决了该问题，结果只提供了 48 个逻辑读取。

您可以再举几个计数数量请求的示例，但本质仍然是相同的： 接收一条数据和统计总数是两个根本不同的请求，并且每个都需要自己的优化措施。 一般来说，您无法找到对两个查询同样有效的索引组合。

因此，在开发此类搜索解决方案时应明确的重要要求之一是查看找到的对象总数对于企业来说是否真的很重要。 经常发生这样的情况：不。 在我看来，通过特定页码进行导航是一种范围非常狭窄的解决方案，因为大多数分页场景看起来就像“转到下一页”。

分页选项#2

我们假设用户不关心知道找到的对象总数。 让我们尝试简化搜索页面：

搜索结果输出和性能问题
事实上，唯一改变的是没有办法导航到特定的页码，而且现在这个表不需要知道有多少页码就可以显示它。 但问题出现了——表格如何知道是否有下一页的数据（以便正确显示“下一页”链接）？

答案很简单：您可以从数据库中读取比显示所需的多一条记录，这条“附加”记录的存在将显示是否还有下一部分。 这样，您只需运行一个请求即可获取一页数据，这显着提高了性能，并且更容易支持此类功能。 在我的实践中，有一个案例，拒绝统计记录总数使结果交付速度加快了4-5倍。

这种方法有几个用户界面选项：“后退”和“前进”命令，如上例所示，“加载更多”按钮，它只是将新部分添加到显示的结果中，“无限滚动”，它可以工作遵循“加载更多”的原则，但获取下一部分的信号是让用户将所有显示的结果滚动到最后。 无论采用哪种视觉解决方案，数据采样的原理都是相同的。

分页实现的细微差别

上面给出的所有查询示例都使用“偏移+计数”方法，查询本身指定结果行的顺序以及需要返回的行数。 首先，让我们看看在这种情况下如何最好地组织参数传递。 在实践中，我遇到了以下几种方法：

  • 请求的页面序号（pageIndex）、页面大小（pageSize）。
  • 返回的第一条记录的序号（startIndex），结果中的最大记录数（count）。
  • 返回的第一条记录的序号（startIndex），返回的最后一条记录的序号（endIndex）。

乍一看，这似乎很简单，没有什么区别。 但事实并非如此 - 最方便和通用的选项是第二个（startIndex，count）。 有几个原因：

  • 对于上面给出的+1条目校对方法，第一个带有pageIndex和pageSize的选项非常不方便。 例如，我们想要每页显示 50 个帖子。 根据上述算法，您需要多读取一条记录。 如果服务器上没有实现这个“+1”，那么对于第一页，我们必须请求从 1 到 51 的记录，对于第二页，我们必须请求从 51 到 101 的记录，依此类推。 如果指定页面大小为 51 并增加 pageIndex，则第二页将从 52 返回到 102，依此类推。 因此，在第一个选项中，正确实现转到下一页的按钮的唯一方法是让服务器校对“额外”行，这将是一个非常隐含的细微差别。
  • 第三个选项根本没有意义，因为要在大多数数据库中运行查询，您仍然需要传递计数而不是最后一条记录的索引。 从 endIndex 中减去 startIndex 可能是一个简单的算术运算，但在这里是多余的。

现在我们来描述一下通过“偏移+数量”实现分页的缺点：

  • 检索每个后续页面将比前一页更昂贵且更慢，因为数据库仍然需要根据搜索和排序标准“从头开始”遍历所有记录，然后停在所需的片段处。
  • 并非所有 DBMS 都支持这种方法。

有一些替代方案，但它们也不完美。 第一种方法称为“键集分页”或“seek方法”，如下：接收到一部分后，可以记住页面上最后一条记录中的字段值，然后使用它们来获取下一部分。 例如，我们运行以下查询：

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

在最后一条记录中，我们得到了订单日期值“2014-06-29”。 然后要获取下一页，您可以尝试执行以下操作：

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

问题是 OrderDate 是一个非唯一字段，上面指定的条件可能会丢失很多必需的行。 为了使该查询更加明确，您需要向条件添加一个唯一字段（假设 75074 是第一部分中主键的最后一个值）：

SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
   OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

此选项可以正常工作，但通常很难优化，因为条件包含 OR 运算符。 如果主键的值随着 OrderDate 的增加而增加，则可以通过仅保留按 SalesOrderID 进行筛选来简化条件。 但如果主键的值和结果排序的字段之间没有严格的相关性，那么在大多数 DBMS 中都无法避免这种 OR。 我知道的一个例外是PostgreSQL，它完全支持元组比较，上面的条件可以写成“WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)”。 给定具有这两个字段的复合键，这样的查询应该相当容易。

例如，可以找到第二种替代方法 ElasticSearch 滚动 API или 宇宙数据库 — 当请求除了数据之外还返回一个特殊标识符时，您可以使用该标识符获取下一部分数据。 如果此标识符具有无限的生命周期（如在 Comsos DB 中），那么这是通过页面之间的顺序转换实现分页的好方法（上面提到的选项#2）。 它可能的缺点：并非所有 DBMS 都支持它； 生成的下一个块标识符可能具有有限的生命周期，这通常不适合实现用户交互（例如ElasticSearch滚动API）。

复杂过滤

让我们把任务进一步复杂化。 假设需要实现所谓的分面搜索，这对于在线商店的每个人来说都非常熟悉。 上面基于订单表的示例在这种情况下不是很说明问题，所以让我们从 AdventureWorks 数据库切换到 Product 表：

搜索结果输出和性能问题
分面搜索背后的想法是什么？ 事实上，对于每个过滤器元素，都会显示满足此条件的记录数 考虑到在所有其他类别中选择的过滤器.

例如，如果我们在此示例中选择自行车类别和黑色，则表格将仅显示黑色自行车，但是：

  • 对于“类别”组中的每个条件，该类别的产品数量将显示为黑色。
  • 对于“颜色”组的每个标准，将显示该颜色的自行车数量。

以下是此类条件的结果输出示例：

搜索结果输出和性能问题
如果您还检查“服装”类别，表格还将显示有库存的黑色衣服。 “颜色”部分中的黑色产品数量也会根据新的条件重新计算，只有“类别”部分不会发生任何变化......我希望这些例子足以理解通常的分面搜索算法。

现在让我们想象一下如何在关系基础上实现这一点。 每组条件（例如类别和颜色）都需要单独的查询：

SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
  INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC

搜索结果输出和性能问题

SELECT Color, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC

搜索结果输出和性能问题
这个解决方案有什么问题？ 它非常简单——它的扩展性不好。 每个过滤器部分都需要一个单独的查询来计算数量，并且这些查询并不是最简单的。 在在线商店中，某些类别可能有几十个过滤器部分，这可能是一个严重的性能问题。

通常在这些陈述之后我会得到一些解决方案，即：

  • 将所有数量计数合并到一个查询中。 从技术上讲，使用 UNION 关键字可以实现这一点，但它不会对性能有太大帮助 - 数据库仍然必须从头开始执行每个片段。
  • 缓存数量。 几乎每次我描述问题时都会向我建议这一点。 需要注意的是，这通常是不可能的。 假设我们有 10 个“方面”，每个方面都有 5 个值。 与在同一在线商店中看到的情况相比，这是一个非常“温和”的情况。 一个方面元素的选择会影响其他 9 个方面的数量，换句话说，对于每个标准组合，数量可能不同。 在我们的示例中，用户总共可以选择 50 个条件；因此，将有 250 种可能的组合。没有足够的内存或时间来填充这样的数据数组。 在这里你可以反对并说并非所有组合都是真实的，并且用户很少选择超过 5-10 个标准。 是的，可以进行延迟加载并仅缓存已选择的数量，但是选择越多，此类缓存的效率就越低，并且响应时间问题就越明显（特别是如果数据集定期更改）。

幸运的是，此类问题长期以来已有相当有效的解决方案，可以在大量数据上进行可预测的工作。 对于这些选项中的任何一个，将构面的重新计算和接收结果页面划分为对服务器的两个并行调用并组织用户界面，使得按构面加载数据“不会干扰”显示搜索结果。

  • 尽可能少地调用“方面”的完全重新计算。 例如，不要在每次搜索条件发生变化时重新计算所有内容，而是查找符合当前条件的结果总数并提示用户显示它们 - “找到 1425 条记录，显示吗？” 用户可以继续更改搜索词或单击“显示”按钮。 只有在第二种情况下，所有获取结果和重新计算所有“方面”数量的请求才会被执行。 在这种情况下，正如您可以轻松看到的，您将必须处理一个请求以获取结果总数及其优化。 这种方法在很多小型网上商店都可以找到。 显然，这不是解决这个问题的灵丹妙药，但在简单的情况下它可以是一个很好的折衷方案。
  • 使用搜索引擎查找结果并计算方面，例如 Solr、ElasticSearch、Sphinx 等。 所有这些都旨在构建“facet”，并且由于倒排索引，可以非常有效地完成此操作。 搜索引擎如何工作，为什么在这种情况下它们比通用数据库更有效，有哪些实践和陷阱 - 这是另一篇文章的主题。 这里我想提请大家注意的是，搜索引擎不能替代主数据存储；它只是作为补充：主数据库中与搜索相关的任何变化都会同步到搜索索引中； 搜索引擎通常只与搜索引擎交互，并不访问主数据库。 这里最重要的一点是如何可靠地组织这种同步。 这一切都取决于“反应时间”的要求。 如果主数据库中的更改与其在搜索中“表现”之间的时间并不重要，您可以创建一个服务，每隔几分钟搜索最近更改的记录并为其建立索引。 如果您想要最短的响应时间，您可以实施类似的方法 交易发件箱 将更新发送到搜索服务。

发现

  1. 实现服务器端分页是一个非常复杂的问题，并且仅对于快速增长或简单的大型数据集才有意义。 对于如何评估“大”或“快速增长”，没有绝对准确的方法，但我会遵循这种方法：
    • 如果接收到完整的数据集合，考虑到服务器时间和网络传输，通常符合性能要求，则在服务器端实现分页是没有意义的。
    • 可能存在一种情况，预计在不久的将来不会出现性能问题，因为数据很少，但数据收集在不断增长。 如果将来的某组数据可能不再满足前面的一点，最好立即开始分页。
  2. 如果业务方面没有严格要求显示结果总数或显示页码，并且您的系统没有搜索引擎，那么最好不要实现这些要点并考虑选项#2。
  3. 如果对分面搜索有明确的要求，那么在不牺牲性能的情况下，您有两种选择：
    • 不要在每次搜索条件更改时重新计算所有数量。
    • 使用 Solr、ElasticSearch、Sphinx 等搜索引擎。 但应该理解的是，它不能成为主数据库的替代品，而应该作为主存储的补充来解决搜索问题。
  4. 此外，在分面搜索的情况下，将搜索结果页面的检索和计数分成两个并行请求是有意义的。 计数数量可能比获得结果花费更长的时间，而结果对用户来说更重要。
  5. 如果您使用 SQL 数据库进行搜索，则与此部分相关的任何代码更改都应该在适当数据量（超过实时数据库中的数据量）上进行良好的性能测试。 还建议对数据库的所有实例（尤其是“实时”实例）上的查询执行时间进行监控。 即使在开发阶段查询计划一切正常，但随着数据量的增长，情况可能会发生明显变化。

来源： habr.com

添加评论