Get best of the week

显示搜索结果和性能问题

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

图片

分页选项1


想到的最简单的选择是以其最经典的形式对搜索结果进行分页。


假设应用程序使用关系数据库。在这种情况下，要以这种形式显示信息，您将需要执行两个SQL查询：

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

以使用测试MS SQL数据库AdventureWorks for 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），作为结果的最大记录数（计数）。
  • 要返回的第一条记录的序列号（startIndex），要返回的最后一条记录的序列号（endIndex）。

乍一看，这似乎太基础了，没有什么区别。但这不是-最方便和通用的选项是第二个选项（startIndex，count）。有几个原因：

  • +1 , , pageIndex pageSize . , 50 . , , . «+1» , , 1 51, — 51 101 .. 51 pageIndex, 52 102 .. , — «» , .
  • 第三个选项根本没有意义，因为要在大多数数据库中执行查询，您仍然需要传输数量，而不是最后一条记录的索引。让我们从endIndex减去startIndex并进行基本算术运算，但这在这里是多余的。

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

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

有其他选择，但它们也不完美。这些方法中的第一种称为``键集分页''或``查找方法''，其内容如下：接收到一部分后，您可以记住页面上最后一条记录中字段的值，然后使用它们来获取下一部分。例如，我们执行了以下请求：

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或Cosmos DB-当除数据之外的查询返回特殊标识符时，您可以使用该标识符获取下一批数据。如果此标识符的生存期是无限的（如在Comsos DB中一样），则这是一种实现分页之间顺序转换的分页的好方法（上述选项2）。其可能的缺点：并非所有DBMS都受支持；接收到的下一部分标识符可能具有有限的生存期，通常不适合实现用户交互（例如ElasticSearch滚动API）。

复杂过滤


我们使任务进一步复杂化。假设需要实施所谓的多面搜索，这对于在线商店的每个人都是熟悉的。在这种情况下，基于订单表的上述示例在这种情况下并不是很能说明问题，因此我们将从AdventureWorks数据库切换到产品表：


分面搜索的想法是什么？这样，对于每个过滤器元素，考虑到在所有其他类别中选择的过滤器，将显示与该标准匹配的记录数。

例如，如果在此示例中选择“自行车”类别和颜色“黑色”，则该表将仅显示黑色自行车，但同时显示：

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

这是此类情况下的输出示例：


如果除了标记“服装”类别外，表格还将显示可用的黑色衣服。“颜色”部分中的黑色产品数量也将根据新条件重新计算，仅在“类别”部分中没有任何变化...我希望这些示例足以理解熟悉的多面搜索算法。

现在想象一下如何在关系基础上实现。每组条件（例如“类别”和“颜色”）将需要单独的请求：

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 , ?» , «». «». , , . -. , , .
  • search engine , Solr, ElasticSearch, Sphinx . «» . , , — . , search engine , : , , ; search engine . — . « ». «» , , . , - transactional outbox .


  1. — , . «» «» — , :
    • — .
    • , , , . - — .
  2. , — #2.
  3. faceted search, :
    • .
    • search engine Solr, ElasticSearch, Sphinx . , , .
  4. faceted search . , , .
  5. SQL , , , ( «» ). , — «». , .

More articles:


All Articles