主页 / dba / 问题 / 316232
Accepted
John
Asked: 2022-08-31 07:34:38 +0800 CST

索引寻找特定的多列键，然后按字典顺序获取一些行

  • 772

考虑以下具有多列索引的示例表：

create table BigNumbers (
    col1 tinyint not null,
    col2 tinyint not null,
    col3 tinyint not null,

    index IX_BigNumbers clustered (col1, col2, col3)
)

DECLARE @n INT = 100;

DECLARE @x1 INT = 0;
DECLARE @x2 INT = 0;
DECLARE @x3 INT = 0;

SET NOCOUNT ON;

WHILE @x3 <= @n BEGIN
    SET @x2 = 0;
    WHILE @x2 <= @n BEGIN
        SET @x1 = 0;
        WHILE @x1 <= @n BEGIN
            insert into BigNumbers values (@x1, @x2, @x3);
            SET @x1 = @x1 + 1;
        END;
        SET @x2 = @x2 + 1;
    END;

 SET @x3 = @x3 + 1;
END;

我现在的目标是从该索引中获取几行，从给定的键开始。

听起来微不足道的事情有点复杂，因为在 SQL 中没有简单的方法来表达索引所在的字典顺序：

DECLARE @x1 INT = 60;
DECLARE @x2 INT = 40;
DECLARE @x3 INT = 98;

select top 5 *
from BigNumbers 
where
  col1 > @x1 or
 (col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3)))
order by col1, col2, col3

正确的结果是：

60  40  98
60  40  99
60  40  100
60  41  0
60  41  1

但是，查询计划告诉我这使用索引扫描。

底层索引应该能够查找并返回大于或等于(@x1, @x2, @3)索引顺序的前几行，但由于 SQL 无法轻松表达此意图，因此查询规划器似乎无法接受提示，而是进行扫描.

索引提示无济于事，并且FORCESEEK给出了一个可怕的计划。

有趣的是，以下两列版本有效：

select top 5 *
from BigNumbers 
where
  col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3))
order by col1, col2, col3

我不确定为什么会这样，但该计划不仅使用搜索，它还正确报告只触及了 5 行：

在此处输入图像描述

我想知道是否有人知道一种方法可以通过简单的搜索可靠地查询大于或等于给定值元组的几行索引。

数据库在其更高层次的抽象下掩盖了这种基本能力，这似乎很奇怪。

如果有人有兴趣知道这是什么问题，我正在为 SQL 数据库开发一个通用 UI。您需要此功能的最明显的地方是“加载更多”按钮，您希望在该按钮上继续显示给定起点的索引内容。如果这通常是不可能的，解决方法是首先查询修复除最后一列以外的所有列，然后进行第二次查询，依此类推。不过，不得不这样做会有点可惜。

sql-server index

2 个回答

  1. Best Answer

    您指的是row-comparison，您将其用于Keyset Pagination query。在支持它的DBMS 中，您可以简单地执行

    where (col1, col2, col3) >= (@x1, @x2, @x3)

    但是 SQL Server 不支持这一点。它支持的是多个范围内的Index Seek。因此，单个索引搜索变为两个或三个，但编译器可以理解和维护排序，因此它有效地充当一个范围内的单个搜索。

    它在许多不同类型的查询中使用它，主要是IN列表和OR查询。它还在行比较逻辑上使用它，这在执行 Keyset Pagination 时非常有用。

    在您的情况下，它没有识别这种模式。这似乎是因为您使用嵌套的布尔逻辑来表达它。它将成功识别以下逻辑，在语义上完全相同

    where (col1 = @x1 and col2 = @x2 and col3 >= @x3)
 or (col1 = @x1 and col2 > @x2)
 or (col1 > @x1)

    计划

    db<>小提琴

    究竟为什么它识别一个而不是另一个尚不清楚。也许可以访问调试器和/或了解优化器规则的人可以详细说明。

    • 8

  2. 在一个完美的世界中，人们将能够以任何逻辑等效的形式编写查询，并且优化器将在所有情况下产生相同的最佳执行计划。

    这不是一个实际的提议——优化器的工作时间有限，并且对可能的转换的了解不完整。因此，我们有时需要以特定的书面形式表达我们的要求以获得最佳结果。

    SQL Server 确实做出了一些努力来标准化（规范化）提交语句的逻辑形式，但它们并不详尽。例如，逻辑子句被转换为否定范式(NNF)，而不是合取式(CNF) 或析取式 (DNF) 范式。向 NNF 的转换总是简单而紧凑；对于 CNF 或 DNF，情况并非总是如此。

    在您的情况下，这很遗憾，因为 DNF 形式恰好很容易派生并且与索引匹配逻辑很好地配合使用，如另一个答案所示：

    DECLARE 
    @x1 tinyint = 60,
    @x2 tinyint = 40,
    @x3 tinyint = 98;

SELECT TOP (5) 
    BN.col1, 
    BN.col2, 
    BN.col3 
FROM dbo.BigNumbers AS BN
WHERE 
    (BN.col1 = @x1 AND BN.col2 = @x2 AND BN.col3 >= @x3)
    OR (BN.col1 = @x1 AND BN.col2 > @x2)
    OR (BN.col1 > @x1)
ORDER BY 
    BN.col1, 
    BN.col2, 
    BN.col3;

    这将生成一个TRIVIAL执行计划，其中包含三个单独的在聚集索引查找中的查找操作，按顺序执行（使用短路）：

    多重搜索计划

    如前所述，理想的情况是 SQL Server 实现行构造函数，并根据需要将逻辑扩展为索引匹配的最佳形式。长期以来一直要求，但令人沮丧的是尚未交付。目前，我们需要以特定的方式编写基于锚的分页查询。

    一个效率略低但仍然不错的解决方案是：

    DECLARE 
    @x1 tinyint = 60,
    @x2 tinyint = 40,
    @x3 tinyint = 98;

SELECT BN.col1, BN.col2, BN.col3 
FROM dbo.BigNumbers AS BN 
WHERE BN.col1 = @x1 
AND BN.col2 = @x2 
AND BN.col3 >= @x3

UNION ALL 

SELECT BN.col1, BN.col2, BN.col3 
FROM dbo.BigNumbers AS BN 
WHERE BN.col1 = @x1 
AND BN.col2 > @x2

UNION ALL 

SELECT BN.col1, BN.col2, BN.col3 
FROM dbo.BigNumbers AS BN 
WHERE BN.col1 > @x1

ORDER BY BN.col1, BN.col2, BN.col3
OFFSET 0 ROWS 
FETCH FIRST 5 ROWS ONLY;

    合并串联计划

    这个计划有三个单次查找，但每个都必须产生至少一行用于合并串联比较，所以没有一个可以完全短路。

    db<>小提琴

    • 5

相关问题