SQL Server 2005 大表 COUNT(*) 效能優化：從全表掃描到瞬時回傳的三大策略

痛點：百萬列大表 COUNT(*) 為何這麼慢？

執行 SELECT COUNT(*) FROM Sales.SalesOrderDetail (121,317 列) 時，執行計畫顯示：

• Index Scan (81%)： 掃描整個叢集索引/堆表的每一頁。
• Stream Aggregate (19%)： 逐列累加計數。

原因：SQL Server 必須實際掃描所有列以確保計數準確 (考慮交易隔離、未提交資料、幽靈記錄等)，無法直接從中繼資料取得。

策略一：系統目錄檢視近似值 (毫秒級、適用監控/估算)

SELECT
    OBJECT_NAME(p.object_id) AS TableName,
    SUM(p.rows) AS RowCount
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID('Sales.SalesOrderDetail')
  AND p.index_id IN (0, 1);  -- 0=Heap, 1=Clustered Index
GROUP BY p.object_id;

• 原理： 讀取 sys.partitions.rows 中繼資料，非交易一致，可能包含未提交/已刪除未清理列。
• 適用： 容量規劃、監控儀表板、大表刪除進度估算。**不可用於業務邏輯精確計數。**
• 維護： `DBCC UPDATEUSAGE` 可校正中繼資料偏差。

策略二：觸發器維護計數表 (精確、寫入負擔微增、讀取極快)

-- 1. 建立計數表
CREATE TABLE dbo.TableRowCounts (
    SchemaName   SYSNAME NOT NULL,
    TableName    SYSNAME NOT NULL,
    RowCount     BIGINT  NOT NULL DEFAULT 0,
    LastUpdated  DATETIME NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (SchemaName, TableName)
);

-- 2. 建立 AFTER INSERT/DELETE 觸發器 (範例: SalesOrderDetail)
CREATE TRIGGER trg_SalesOrderDetail_Count
ON Sales.SalesOrderDetail
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @delta INT = (SELECT COUNT(*) FROM inserted) - (SELECT COUNT(*) FROM deleted);
    IF @delta <> 0
    BEGIN
        UPDATE dbo.TableRowCounts
        SET RowCount = RowCount + @delta,
            LastUpdated = GETDATE()
        WHERE SchemaName = 'Sales' AND TableName = 'SalesOrderDetail';
        IF @@ROWCOUNT = 0
            INSERT dbo.TableRowCounts (SchemaName, TableName, RowCount)
            VALUES ('Sales', 'SalesOrderDetail', @delta);
    END
END;

• 查詢： `SELECT RowCount FROM dbo.TableRowCounts WHERE SchemaName='Sales' AND TableName='SalesOrderDetail';` (Index Seek，< 1ms)。
• 優點： 交易一致、精確、讀取無負擔。
• 缺點： 寫入路徑增加觸發器開銷；大量批次載入時觸發器逐列執行效能差 (可改用 `MERGE` + `OUTPUT` 批次更新計數表)。
• 注意： `TRUNCATE TABLE` 不觸發 Trigger，需手動重設計數或禁用 TRUNCATE 權限。

策略三：索引檢視 - 企業版自動匹配、標準版需 NOEXPAND (精確、免維護代碼)

-- 1. 建立 SCHEMABINDING 檢視
CREATE VIEW dbo.vw_SalesOrderDetail_Count
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) AS RowCount
FROM Sales.SalesOrderDetail;

-- 2. 建立唯一叢集索引使其物質化
CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesOrderDetail_Count
ON dbo.vw_SalesOrderDetail_Count (RowCount);  -- 單一列索引

• 查詢：
  • 企業版/開發版：直接查原表 `SELECT COUNT(*) FROM Sales.SalesOrderDetail`，優化器**自動重寫**使用索引檢視 (Instant)。
  • 標準版/Express：需指定 Hint `SELECT RowCount FROM dbo.vw_SalesOrderDetail_Count WITH (NOEXPAND);`。
• 原理： 索引檢視物質化聚合結果，DML 時自動維護 (內部機制)，無需手寫 Trigger。
• 限制： 檢視定義受限 (不支援外部聯結、子查詢、非確定性函數等)；大量載入時索引維護成本同策略二。

策略四：Columnstore 索引 (SQL Server 2012+、資料倉儲場景極致)

CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesOrderDetail
ON Sales.SalesOrderDetail;
-- COUNT(*) 透過 Segment 消除 + 字典編碼，極速完成

適用於唯讀/少寫入的大型事實表，壓縮率 10x，掃描速度 100x。

方案選型決策表

避坑指南

• 不要用 `COUNT(1)` 或 `COUNT(Column)` 期望比 `COUNT(*)` 快： 優化器視為相同，皆需掃描。
• 不要在 WHERE 條件下用 `COUNT(*)` 當存在性檢查： 用 `IF EXISTS (SELECT 1 FROM ... WHERE ...)` 瞬間返回。
• 大表分頁不要用 `COUNT(*)` 取總頁數： 用 Keyset Pagination (WHERE ID > @lastId) 免總數。
• 統計資料過期導致計畫錯誤： 定期 `UPDATE STATISTICS` 或啟用 `AUTO_UPDATE_STATISTICS`。

結語

大表 COUNT(*) 效能問題本質是「精確一致性」與「掃描成本」的矛盾。選擇策略核心在於：**業務是否容忍近似值？寫入頻率如何？版本支援何種特性？** 現代架構傾向於 **索引檢視** (OLTP) 或 **Columnstore** (DW) 實現免維護精確計數，配合應用層快取 (Redis) 進一步卸載資料庫讀取壓力。