sql - 列存储索引 - 偏移获取查询的性能缓慢

Question

我们在 Azure 数据库(高级层)上有大约 3500 万行的 Fact 表，该表启用了集群列存储索引以提高查询性能。

我们使用下面类似的代码在 Fact 表上进行了分页(在 Elastic Search 上建立索引):

SELECT *
FROM [SPENDBY].[FactInvoiceDetail]
ORder by id
offset 1000000 rows fetch next 1000 rows only

但是这个查询执行的很慢，甚至超过 10 分钟，它还没有完成。如果我们改用 TOP，效果会非常好，大约需要 30 秒:

SELECT TOP 1000 * 
FROM [SPENDBY].[FactInvoiceDetail]
WHERE ID > 1000000 
ORDER BY Id

offset-fetch 查询的估计执行计划:

我不确定我是否了解 offset-fetch 查询在集群列存储索引上的性能是否很差。

这个表还有很多外键上的非集群 B-tree 索引和 Fact 表的 Id 上的一个唯一索引，以提高性能

这个offset-fetch查询的执行计划:

https://pastebin.com/BM8MXQMg

Answer 1

这里有几个问题。

1) Ordering BTree index is not a covering index for the paging query.

2) The rows must be reconstructed from the CCI.

3) The offset is large.

分页查询需要排序列上的 BTree 索引来计算应返回哪些行，如果该 BTree 索引不包括所有请求的列，则需要对每一行进行行查找。这是查询计划中的“嵌套循环”运算符。

但是行存储在 CCI 中，这意味着每一列都在一个单独的数据结构中，读取单行需要为每一列、每一行提供一个逻辑 IO。这就是为什么这个查询特别昂贵。以及为什么 CCI 不是分页查询的糟糕选择。排序列上的聚集索引，或排序列上包含剩余请求列的非聚集索引会好得多。

这里的次要和较小的问题是大偏移量。 SQL 必须跳过偏移的行，并在其进行时对其进行计数。所以这将读取 BTree 叶级页面的前 N ​​页以跳过行。