如何加快對有 6500 萬條記錄的表的查詢

select hash_id, hash_value,
           substring(T.cri, 1, 7),
           substring(T.cri, 9, D2.Pos-9),        
           substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1),
           substring(T.cri, D3.Pos + 1, D4.Pos - D3.Pos - 1),
           substring(T.cri, D4.Pos+1, len(T.cri) - 6 - D4.Pos),
           substring(right(cri,5),1,1), 
           substring(right(cri,3),1,1), 
           substring(right(cri,1),1,1), 
           ref_count,
           compartment_id,
           cg_id, 
           cri_version_id 
from hash_key as T WITH(index(IX_hashkey_hashid_covering_refcount),nolock)
   cross apply (select charindex('?', T.cri, 31)) as D2(Pos)
   cross apply (select charindex('?', T.cri, D2.Pos+1)) as D3(Pos)
   cross apply (select charindex('?', T.cri, D3.Pos+1)) as D4(Pos)                 
where hash_id between 1 and 10000

創建表：

create table hash_key_binary 
( 
 hash_id bigint not null identity, 
 hash_value nvarchar(100) not null, 
 cri nvarchar(100) not null, 
 ref_count_checksum int not null default(0), 
 compartment_id int , 
 cg_id int , 
 cri_version_id int, 
 primary key (hash_value, cg_id), 
 constraint fk_hash_compartment foreign key (compartment_id) references compartment(compartment_id) on delete no action, 
 constraint fk_hash_compartmentgroup foreign key (cg_id) references compartment_group(group_id) on delete no action
)

這些是此表上存在的索引：

CREATE NONCLUSTERED INDEX IX_hashkey_hashid_covering_refcount 
 ON hash_key (hash_id ASC) 
 INCLUDE (ref_count) 

CREATE NONCLUSTERED INDEX IX_hashkey_cri 
 ON hash_key (cri ASC)

我認為您最大的問題是架構。

• 您應該嘗試查看是否可以減少行佔用空間。例如，真的需要 nvarchar 嗎？使用 Varchar 會將大小減半。這也可以將您的處理時間縮短一半。HashID 真的需要 BigInt 嗎？你只有 65M 行，一個普通的 INT 綽綽有餘，又是大小的一半。您保存的每個字節將獲得 65Mb 的數據。
• 您應該看看是否可以使用單獨的欄位而不是使用文本操作，這將簡化查詢、提高完整性並很可能提高速度。
• nvarchar(100) 上的主鍵不是一個好主意，它應該在您的標識列上。然後讓它成群。我確信這將消除對索引提示的需要。
• 而不是使 (hash_value, cg_id) 成為主鍵，而是將其設置為唯一索引
• 確保你有一個好的維護計劃來減少索引碎片

如果此查詢是您系統的主要關注點，您應該在集群上hash_id使用二級索引來強制執行主鍵。

然而，這是一個次優化。很可能，您在執行所有這些字元串操作時會消耗大量 CPU 週期。cri如果輸入值都落入一個小子集，則可能有一種方法可以預先計算不同值的結果。

此外，根據散列鍵的分佈，您可能無法在這樣的查詢中獲得完美的並行性。一個簡單的重寫可以顯著改變您可以並行使用的核心數量。

如果您有機會向表中添加列，為什麼不創建包含 D1、D2、D3 值的持久計算列呢？它們可以在INSERT時間上非常快速地計算出來，這將幫助您分攤SELECT批處理請求的成本。

請為我上面的問題添加更多上下文，我將添加有關可能重寫此查詢的詳細資訊。

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