索引尋找特定的多列鍵，然後按字典順序獲取一些行

考慮以下具有多列索引的範例表：

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。您需要此功能的最明顯的地方是“載入更多”按鈕，您希望在該按鈕上繼續顯示給定起點的索引內容。如果這通常是不可能的，解決方法是首先查詢修復除最後一列以外的所有列，然後進行第二次查詢，依此類推。不過，不得不這樣做會有點可惜。

您指的是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<>小提琴

究竟為什麼它辨識一個而不是另一個尚不清楚。也許可以訪問調試器和/或了解優化器規則的人可以詳細說明。

引用自：https://dba.stackexchange.com/questions/316232