優化 25+ 百萬行的查詢

我正在使用 MS SQL，我必鬚根據不同的條件在同一張表上執行多個查詢。起初我在原始表上執行每個查詢，儘管它們都共享一些過濾（即日期、狀態）。這花了很多時間（大約 2 分鐘）。

數據行有重複，所有索引都是非集群的。我只對我的標準的 4 列感興趣，結果應該只輸出計數，對於所有查詢。

需要的列：TABLE、FIELD、AFTER、 ，並且每個和DATE都有一個索引。DATE``TABLE

在創建一個只包含我需要的欄位的臨時表後，它下降到 1:40 分鐘，這仍然很糟糕。

CREATE TABLE #TEMP
(
   TABLE VARCHAR(30) NULL,
   FIELD VARCHAR(30) NULL,
   AFTER VARCHAR(1000) NULL,
   DATE DATETIME,
   SORT_ID INT IDENTITY(1,1)
)
CREATE CLUSTERED INDEX IX_ADT ON #TEMP(SORT_ID)

INSERT INTO #TEMP (TABLE, FIELD, AFTER, DATE)
   SELECT TABLE, FIELD, AFTER, DATE 
   FROM mytbl WITH (NOLOCK)
   WHERE TABLE = 'OTB' AND
   FIELD = 'STATUS'

執行此->（受影響的 216598 行）

由於並非所有查詢都依賴於日期範圍，因此我沒有將其包含在查詢中。問題是僅插入. 上述插入耗時1:19 分鐘

我想為幾個查詢執行這樣的東西：

SELECT COUNT(*) AS COUNT
FROM #TEMP
WHERE AFTER = 'R' AND
DATE >= '2014-01-01' AND
DATE <= '2015-01-01'

插入的問題比選擇的問題多，但是臨時表的行數比原始表少得多，這可能比多次遍歷表要好。

我該如何優化呢？

編輯

我已經刪除了排序 ID，我認為問題主要在於選擇而不是插入。這是一個猜測。

我無法在任何索引上創建唯一的，因為沒有唯一的欄位或行。

我正在使用 SQL Server 2012。

表資訊：它是一個堆，具有以下空間使用情況：

name    rows        reserved    data        index_size  unused
mytbl   24869658    9204568 KB  3017952 KB  5816232 KB  370384 KB

問題主要是關於如何優化select語句：

SELECT [TABLE], [FIELD], [AFTER], [DATE]
FROM mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB' AND
[FIELD] = 'STATUS'

刪除多餘的投影並添加假定的dbo模式：

SELECT [AFTER], [DATE] 
FROM dbo.mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB'
AND FIELD = 'STATUS';

沒有像([TABLE],[FIELD]) INCLUDE ([AFTER],[DATE])SQL Server 這樣的索引有兩個主要選項：

1. 完全掃描堆（3GB+）；要麼
2. 找到匹配[TABLE] = 'OTB'and的行[FIELD] = 'STATUS'（使用），然後對每行IDX6執行堆 (RID) 查找以檢索和列。[AFTER]``[DATE]

優化器是選擇堆掃描還是使用 RID 查找的索引查找取決於估計的[TABLE] = 'OTB'和[FIELD] = 'STATUS'謂詞的選擇性。檢查查找的估計行數是否與實際相符。如果沒有，請更新您的統計資訊。如果該條件具有合理的選擇性，則使用強制使用索引的表提示測試查詢。INDEX(0)如果優化器目前正在選擇索引查找，請使用或FORCESCAN提示掃描堆來測試性能。

除此之外，您可以通過刪除一些未使用的空間 (370MB) 來稍微改進堆掃描。在 SQL Server 2008 中，這可以通過重建堆來完成。堆中未使用的空間通常是由於在沒有獲取表鎖的情況下執行的刪除（沒有表鎖，不會從堆中釋放空頁）。出於這個原因，經常刪除的表通常最好儲存為聚群表。

堆掃描的性能取決於有多少表儲存在記憶體中，必須從磁碟讀取多少，頁面有多滿，持久儲存的速度，掃描是 I/O 還是 CPU 密集型（並行性可以提供幫助）。

如果在您調查了以上所有內容後性能仍然無法接受，請嘗試為新索引提出理由。如果在您的 SQL Server 版本上可用，則給定查詢的可能過濾索引將是：

CREATE INDEX index_name
ON dbo.mytbl ([DATE],[AFTER])
WHERE [TABLE] = 'OTB'
AND [FIELD] = 'STATUS';

還要考慮索引壓縮，如果它可用且有益的話。如果沒有某種新的索引，您可以做的事情相對較少來提高給定查詢的性能。

我認為這裡有更改索引的情況，因為：

• 你有一個任務要做（這些多個查詢）
• 數據倉庫卷（25+ 百萬行）和
• 性能問題。

對於 SQL Server 2012 中引入的非聚集列儲存索引，這也是一個很好的案例，即在具有許多列的大表上匯總/聚合幾列。

儘管這些索引具有使表成為只讀的副作用（分區切換除外），但它們可以在適當的條件下改變聚合查詢的性能。可以通過將索引或簡單的分區切換數據刪除並重新創建到表中來管理只讀方面。

我設置了一個簡單的測試台來模仿您的設置，並且看到性能有了很好的改進：

USE tempdb
GO

SET NOCOUNT ON
GO

-- Create a large table
IF OBJECT_ID('dbo.largeTable') IS NOT NULL
DROP TABLE dbo.largeTable
GO
CREATE TABLE dbo.largeTable ( 

   [TABLE] VARCHAR(30) NULL,
   FIELD VARCHAR(30) NULL,
   [AFTER] VARCHAR(1000) NULL,
   [DATE] DATETIME,
   SORT_ID INT IDENTITY(1,1),

   pad VARCHAR(100) DEFAULT REPLICATE( '$', 100 )
)
GO

-- Populate table
;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
   CROSS JOIN master.sys.columns c2
   CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.largeTable ( [TABLE], FIELD, [AFTER], [DATE] )
SELECT 
   x.tableName, 
   y.field,
   z.[after],
   DATEADD( day, rn % 1111, '1 Jan 2012' )
FROM cte c
   CROSS JOIN ( VALUES ( 'OTB' ), ( 'AAA' ), ( 'BBB' ), ( 'CCCC' ) ) x ( tableName )
   CROSS JOIN ( VALUES ( 'STATUS' ), ( 'TIME' ), ( 'POWER' ) ) y ( field )
   CROSS JOIN ( VALUES ( 'R' ), ( 'X' ), ( 'Z' ), ( 'A' ) ) z ( [after] )

CHECKPOINT

GO 5

EXEC sp_spaceused 'dbo.largeTable'
GO

SELECT MIN([DATE]) xmin, MAX([DATE]) xmax, FORMAT( COUNT(*), '#,#' ) records
FROM dbo.largeTable
GO

-- Optionally clear cache for more comparable results; DO NOT RUN ON PRODUCTION SYSTEM!!
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
--GO

DECLARE @startDate DATETIME2 = SYSUTCDATETIME()

SELECT COUNT(*) AS COUNT
FROM dbo.largeTable
WHERE [AFTER] = 'R' 
 AND [DATE] >= '2014-01-01' 
 AND [DATE] <= '2015-01-01'

SELECT DATEDIFF( millisecond, @startDate, SYSUTCDATETIME() ) diff1
GO

-- Add the non-clustered columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX _cs ON dbo.largeTable ( [TABLE], FIELD, [AFTER], [DATE] )
GO

-- Optionally clear cache for more comparable results; DO NOT RUN ON PRODUCTION SYSTEM!!
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
--GO

-- Check query again
DECLARE @startDate DATETIME2 = SYSUTCDATETIME()

SELECT COUNT(*) AS COUNT
FROM dbo.largeTable
WHERE [AFTER] = 'R' 
 AND [DATE] >= '2014-01-01' 
 AND [DATE] <= '2015-01-01'

SELECT DATEDIFF( millisecond, @startDate, SYSUTCDATETIME() ) diff2
GO

我的結果，6 秒 v 0.08 秒：

總之，試著和你的老闆一起建立一個案例來改變索引，或者至少創建某種通宵的過程，將這些記錄分割到一個只讀的報告表/數據庫中，你可以在其中進行工作，並添加索引適合該工作量。

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