如何讓我的查詢使用可用的索引

我在 SQL 中有以下時間序列表：

CREATE TABLE [dbo].[SensorData](
[DateTimeUtc] [datetime2](2) NOT NULL,
[SensorId] [int] NOT NULL,
[Key] [varchar](20) NOT NULL,
[Value] [decimal](19, 4) NULL,
CONSTRAINT [PK_SensorData] PRIMARY KEY CLUSTERED 
(
 [SensorId] ASC,
 [Key] ASC,
 [DateTimeUtc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = 
ON, Data_Compression=PAGE) ON PS_Daily(DateTimeUtc))

現在基於此索引，每個查詢都需要在查詢 where 過濾器中具有以下參數：

[SensorId],
[Key],
[DateTimeUtc]

當查詢具有所有三個時，查詢會按預期返回非常快。

現在我被一個特定的查詢困住了，它沒有任何特定的值[Key]。例如：

檢查感測器是否有任何數據：過去 12 小時內的 1234。在這種情況下，我們有一個DateTime和SensorId的過濾器值。但是這個查詢返回真的很慢。

添加Where [Key] is not null會使 SQL 命中該索引嗎？

我知道簡單的答案是在表上添加一個新索引，只有 [SensorId],[DateTimeUtc]; 但是，這將根據其大小為數據庫添加大量空間，並且還會減慢插入速度。

有什麼方法可以讓上述查詢命中聚集索引？

我使用聚集索引鍵順序的原因是，在閱讀了應該如何排序之後，最獨特的值應該是第一個。

我跑了EXEC sp_spaceused [SensorData]

詢問：

SELECT 
CASE WHEN ( EXISTS (SELECT 
   1 AS [C1]
   FROM [dbo].[SensorData] AS [Extent1]
   WHERE ([Extent1].[DateTimeUtc] > @p__linq__0) AND ([Extent1].[DateTimeUtc] <= @p__linq__1) AND ([Extent1].[SensorId] = @p__linq__2)
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

每個時間段每個SensorId通常有大約 10 到 40 個不同的鍵。但我們顯然每天每個SensorId 都會儲存這 10 到 40 個密鑰數千次。

您是否考慮過使用 ADX（Azure 數據資源管理器）來儲存此表？這樣的查詢對於 ADX 來說是輕而易舉的事。ADX 是 Azure 上的時間序列優化集群。對我來說，這似乎是這類問題的自然解決方案。

它將管理分區、索引，您可以自動創建保留策略。

如果您打算使用 Power BI（或 Grafana）使用數據，您還可以在 ADX 上執行 Direct Query，因此在處理數據模型時無需導入大量數據。此外，用於 ADX 的連接器非常支持查詢折疊，因此如果您使用的是 Power BI（例如），則針對您的視覺對象的查詢將即時轉換為 Kusto。

如果您的感測器數據來自事件中心或 IoT 中心，您可以將數據直接攝取到 ADX（如果它是受支持的文件類型），甚至可以使用流分析創建自定義反序列化器來攝取各種數據。

如果您需要更多上下文，請告訴我。

您目前的聚集索引定義(SensorId, Key, DateTimeUtc)將涵蓋所有三個欄位上的謂詞查詢。它還應該涵蓋使用該定義的任何順序子集的任何謂詞（從左到右閱讀），例如謂詞 usingSensorId和Key或什至謂詞僅 using SensorId。這是因為您在索引定義中列出欄位的順序是該索引的 B-Tree 的排序順序。

在您的情況下，B-Tree 目前按SensorIdthen排序Key，最後按DateTimeUtc. 支持您提到的其他案例的更有用的索引設計是重新排列定義中的列，以便(DateTimeUtc, SensorId, Key)改為（如評論中所述）。該單個聚集索引將涵蓋您使用所有三個欄位進行過濾的案例，並且它支持您只想知道給定感測器的給定日期範圍是否有任何數據的案例。如果您想知道任何給定日期範圍內是否有任何數據，它甚至還涵蓋了您。

正如評論中所討論的，如果您打算對 進行範圍過濾DateTimeUtc，您可能會發現更好的性能仍然領先於SensorId您正在過濾的相等匹配。但是您仍然可以在聚集索引定義中排列列，因為(SensorId, DateTimeUtc, Key)它涵蓋了上述兩個案例。

因此，只需重新排列聚集索引定義中的列，您就可以在不引入任何新索引且不佔用任何額外磁碟空間的情況下實現目標。

旁注：您可以使用系統儲存過程sp_spaceused幾乎立即找出給定表的大小（按行和消耗的磁碟大小）。例如EXEC sp_spaceused 'dbo.SensorData';.

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