儲存順序與結果順序

這是主鍵中指定的排序順序的衍生問題，但排序是在 SELECT 上執行的。

@Catcall關於儲存順序（聚集索引）和輸出順序的主題

很多人認為聚集索引可以保證輸出的排序順序。但這不是它的作用。它保證了磁碟上的儲存順序。 例如，請參閱此部落格文章。

我已閱讀 Hugo Kornelis 的部落格文章，並了解索引不能保證 sql 伺服器以特定順序讀取記錄。然而，我很難接受我不能在我的場景中假設這一點？

CREATE TABLE [dbo].[SensorValues](
 [DeviceId] [int] NOT NULL,
 [SensorId] [int] NOT NULL,
 [SensorValue] [int] NOT NULL,
 [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
 [DeviceId] ASC,
 [SensorId] ASC,
 [Date] DESC
) WITH (
   FILLFACTOR=75,
   DATA_COMPRESSION = PAGE,
   PAD_INDEX = OFF,
   STATISTICS_NORECOMPUTE = OFF,
   SORT_IN_TEMPDB = OFF,
   IGNORE_DUP_KEY = OFF,
   ONLINE = OFF,
   ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON)
 ON [MyPartitioningScheme]([Date])

我原來的查詢是這樣的：

SELECT TOP 1 SensorValue
 FROM SensorValues
 WHERE SensorId = 53
   AND DeviceId = 3819
   AND Date < 1339225010
 ORDER BY Date DESC

但我建議我也可以使用這個（請閱讀下面的解釋）：

SELECT TOP 1 SensorValue
 FROM SensorValues
 WHERE SensorId = 53
   AND DeviceId = 3819
   AND Date < 1339225010

如您所見，我的表行很小（16 字節），而且我只有一個索引，一個聚集索引。在我的場景中，此時該表包含 100.000.000 條記錄（這很可能會增加十倍）。

當數據庫伺服器查詢這個表時，它有兩種方法來查找我的行，一種是查找主鍵，然後讀取並返回我在 desc 中的值。日期順序，否則必須進行全表掃描。我的結論是，對所有這些記錄進行全表掃描會太慢，因此數據庫伺服器將始終通過其主鍵查找表，從而返回按以下順序排序的值Date DESC

讓我試著解釋一下為什麼你不應該這樣做，為什麼你永遠不應該假設一個 SQL 產品會以特定的順序返回一個結果集，除非你指定了，無論索引是集群的還是非集群的，B 樹或R-Trees 或 kd-trees 或 fractal-trees 或 DBMS 正在使用的任何其他奇異索引。

---

您的原始查詢告訴 DBMS 搜尋SensorValues表，查找與 3 個條件匹配的行，按Date降序排列這些行，僅保留第一行，最後選擇並僅返回該SensorValue列。

SELECT TOP 1 SensorValue
 FROM SensorValues
 WHERE SensorId = 53
   AND DeviceId = 3819
   AND Date < 1339225010
 ORDER BY Date DESC ;

這些是您給 DBMS 的非常具體的命令，每次執行查詢時結果很可能是相同的（如果您有多個與條件匹配的行並且具有相同的最大Date但不同SensorValue，但讓我們假設其餘的對話在您的表中不存在這樣的行）。

DBMS 是否必須按照我上面描述的確切方式執行此操作才能執行此查詢？不，當然不是，你知道的。它可能不讀取表，而是從索引中讀取。或者，如果它認為更好（更快），它可能會使用兩個索引。或三個。或者它可能使用記憶體結果（不是 SQL Server，而是其他 DBMS 記憶體查詢結果）。或者它可能會使用一次並行執行而不是下一次執行。或者…（添加影響執行和執行計劃的任何其他功能）。

但可以保證的是，每次執行它時，它都會返回完全相同的結果——只要沒有插入、刪除或更新行。

---

現在讓我們看看你的建議是什麼：

SELECT TOP 1 SensorValue
 FROM SensorValues
 WHERE SensorId = 53
   AND DeviceId = 3819
   AND Date < 1339225010 ;

此查詢告訴 DBMS 搜尋SensorValues表，查找符合 3 個條件的行，按降序對這些行進行Date排序，不關心順序，只保留一行- 最後 - 選擇並僅返回SensorValue列。

因此，它基本上與第一個相同，只是它告訴您只需要​​一個與條件匹配的結果，而不關心哪個結果。

現在，我們可以假設由於聚集索引，它總是會給出相同的結果嗎？

- 如果它每次都使用這個聚集索引，是的。

但它會使用它嗎？

- 不。

為什麼不？

- 因為它可以。查詢優化器可以在每次執行語句時自由選擇執行路徑。無論它當時認為適合該聲明的路徑是什麼。

但是使用聚集索引不是獲得結果的最佳/最快方法嗎？

**- 不，並非總是如此。**這可能是您第一次執行查詢。第二次，它可能會使用記憶體結果（如果 DBMS 有這樣的功能，而不是 SQL Server *）。第 1000 次結果可能已從記憶體中刪除，並且可能存在另一個結果。比如說，您之前已經執行過這個查詢：

SELECT TOP 1 SensorValue
 FROM SensorValues
 WHERE SensorId = 53
   AND DeviceId = 3819
   AND Date < 1339225010
 ORDER BY Date ASC ;         --- Notice the `ASC` here

並且記憶體的結果（來自上述查詢）是另一個不同的結果，它仍然符合您的條件，但不是您（想要的）排序中的第一個。你已經告訴 DBMS 不要關心訂單。

好的，所以只有記憶體會影響這個？

- 不，還有很多其他的事情。

• 當時 DBMS 認為其他索引更適合此查詢。
• 開發人員更改或完全刪除了您擁有的此聚集索引。
• 您或其他一些開發人員添加了另一個索引，優化器認為它比 CI 更有效。
• 你更新到一個新版本，新的優化器有一個小錯誤，或者它的排名和選擇執行計劃的方式發生了變化。
• 統計數據已更新。
• 而是選擇了並行執行。

*：SQL Server 不記憶體查詢結果，但企業版確實具有高級掃描功能，這有點相似，因為並發查詢可能會得到不同的結果。不過，不確定何時開始。（謝謝​​@Martin Smith 的提示。）

---

我希望您確信您永遠不應依賴 SQL 查詢將按特定順序返回結果，除非您指定。並且永遠不要使用TOP (n)without ORDER BY，除非您當然只想要結果中的 n 行並且您不在乎返回哪些行。

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