SQL 查詢優化案例研究：提升水位監測系統性能

背景

我們的水位監測系統面臨著一個重要的挑戰：主要的數據查詢速度緩慢，造成了「執行逾時到期。在作業完成之前超過逾時等待的時間，或是伺服器未回應。」的問題，導致系統一直發送LINE通知錯誤。這個案例研究詳細介紹了我們如何通過優化 SQL 查詢來顯著提升系統性能。

初始情況

系統概況

目前總共有 120 個監測站點（stt_no）
每 10 分鐘向 WaterLevelGaugeHistory 表匯入一次數據
需要查詢每個站點最近 24 小時的最新數據

原始查詢

初始查詢涉及多個表的聯接和子查詢，執行時間約為 1 分鐘以上。

SELECT DISTINCT 
    WL.[ID], WL.[stt_name], /* 其他欄位 ... */
    WLH.[measure_time], WLH.[val], WLH.[Status], WLH.cstatus, WLH.[Epower], WLH.nodata
FROM [dbo].[WaterLevelGauge] WL
LEFT JOIN [dbo].[WaterLevelGauge_detailed_View] WLD 
    ON WL.[stt_name] = WLD.[MonitoringStationNumber]
LEFT JOIN (
    SELECT [ID], [stt_no], [dev_id], [measure_time], [val], [Status], [cstatus], [nodata], [power] AS Epower, 
    ROW_NUMBER() OVER (PARTITION BY [stt_no] ORDER BY [measure_time] DESC) AS row_num 
    FROM [dbo].[WaterLevelGaugeHistory] 
    WHERE [dev_id] IS NOT NULL
) WLH ON WL.[stt_no] = WLH.[stt_no] AND WLH.row_num = 1
WHERE WL.[memo2] <> '停用' OR WL.[memo2] IS NULL;

優化過程

步驟 1：分析查詢瓶頸

我們發現 WaterLevelGaugeHistory 表的大量數據是造成查詢緩慢的主要原因，觀察的時候已經資料量已經來到了五百萬筆以上。

步驟 2：重構子查詢

我們重新設計了子查詢，以更高效地過濾和排序數據：

SELECT
    [ID], [stt_no], [dev_id], [measure_time], [val], [Status], 
    [cstatus], [nodata], [power] AS Epower
FROM (
    SELECT 
        [ID], [stt_no], [dev_id], [measure_time], [val], [Status], 
        [cstatus], [nodata], [power],
        ROW_NUMBER() OVER (PARTITION BY [stt_no] ORDER BY [measure_time] DESC) AS row_num
    FROM [dbo].[WaterLevelGaugeHistory]
    WHERE [dev_id] IS NOT NULL
        AND [measure_time] >= DATEADD(HOUR, -24, GETDATE())  -- 只查詢最近24小時的數據
) ranked
WHERE row_num = 1

步驟 3：添加時間過濾

我們添加了時間過濾條件，只查詢最近 24 小時的數據，這大大減少了需要處理的數據量。

步驟 4：索引優化

我們創建了一個針對性的索引來支持查詢：

-- 在 WaterLevelGaugeHistory 表上創建優化的複合索引
CREATE NONCLUSTERED INDEX IX_WaterLevelGaugeHistory_stt_no_measure_time
ON [dbo].[WaterLevelGaugeHistory] ([stt_no], [measure_time] DESC)
INCLUDE ([val], [Status], [cstatus])
WITH (SORT_IN_TEMPDB = ON);

-- 在 WaterLevelGauge 表上創建索引
CREATE NONCLUSTERED INDEX IX_WaterLevelGauge_stt_name
ON [dbo].[WaterLevelGauge] ([stt_name])
WITH (SORT_IN_TEMPDB = ON);

-- 更新統計資訊
UPDATE STATISTICS [dbo].[WaterLevelGaugeHistory] 
WITH FULLSCAN;

UPDATE STATISTICS [dbo].[WaterLevelGauge] 
WITH FULLSCAN;

這個索引包含了所有需要的列，支持索引覆蓋查詢，進一步提高了性能。

優化結果

經過優化後，我們取得了令人矚目的成果：

原始查詢時間：約 1 分鐘
優化後查詢時間：約 1 秒
性能提升：超過 98%

這種程度的性能改進遠遠超出了我們最初的預期，幾乎達到了即時響應的水平。

深入分析顯著提升的原因

精確的數據過濾：通過限制查詢範圍到最近 24 小時，我們大幅減少了需要處理的數據量。考慮到我們的系統每 10 分鐘更新一次數據，這意味著每個站點最多只需處理 144 條記錄（24小時 * 6次/小時）。
高效的索引利用：我們創建的複合索引（stt_no, measure_time, dev_id）完美匹配了查詢模式。這使得 SQL Server 能夠快速定位到每個站點的最新記錄，而無需掃描大量數據。
子查詢優化：重構後的子查詢使用了 ROW_NUMBER() 函數，配合 PARTITION BY 和高效索引，使得 SQL Server 能夠極快地識別每個站點的最新記錄。
索引覆蓋：通過在索引中包含所有需要的列，我們實現了索引覆蓋查詢。這意味著 SQL Server 可以直接從索引中檢索所有所需的數據，而無需訪問實際的表數據頁，大大減少了 I/O 操作。
數據分布的優勢：由於我們只有 120 個監測站點，並且每個站點的數據都很規律（每 10 分鐘一條），這種數據分布特性非常有利於查詢優化和索引使用。
減少了複雜的連接操作：優化後的查詢減少了對 WaterLevelGauge 和 WaterLevelGauge_detailed_View 的連接操作，這些操作在原始查詢中可能造成了額外的性能開銷。