SQL Server 不使用索引

我對為什麼我的查詢沒有使用我認為是選擇性索引的原因感到非常困惑。

我的模型由索賠、聯繫人和電話號碼組成。每個索賠有 1 個聯繫人，每個聯繫人有多個電話號碼。聲明可以有狀態，電話號碼有類型。

我在狀態聲明上添加了一個索引，它包括 ContactID。

create index Status on tClaim(Status) include (Name,ContactID)

我在電話上添加了包含號碼的 ContactID 和類型的索引。

create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)

我正在嘗試編寫一個查詢，該查詢返回所有狀態為“贏”的索賠以及索賠聯繫人的相應“家庭電話”。我已經嘗試了兩種方法。一個包括加入通訊錄，一個不包括。兩者都不會產生我期望的計劃。

select 
   c.ID,
   c.Name,
   p.Number 
from
    tClaim c 
    left join tContactPhone p on
       c.ContactID=p.ContactID and p.Type='Home'
where
   c.Status = 'Won'

select 
   c.ID,
   c.Name,
   p.Number
from
    tClaim c
    inner join tContact co on  
       co.id=c.ContactID
    left join tContactPhone p on
       co.ID=p.ContactID and p.Type='Home'
where
   c.Status = 'Won'

我回來的計劃拒絕使用 tContactPhone.ContactID_Type。它建議按類型進行索引，這沒有意義，因為它似乎沒有 ContactId 選擇性。

粘貼計劃

這是我用來創建範例數據集以進行測試的腳本。請注意，我的實際數據集更大，命名更好，欄位更多；但這是為了複製我的情況而提煉出來的

$$ AKA I don’t even like the naming conventions and data generation, but it gets the job done :) $$

/*
       Create Tables and Constraints
*/
CREATE TABLE tContact(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](100) NOT NULL,
   CONSTRAINT [pkey_tContact] PRIMARY KEY CLUSTERED 
   (
       [ID] ASC
   )
)
GO

CREATE TABLE tContactPhone(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [ContactID] [int] NOT NULL,
   [Type] [nvarchar](25) NOT NULL,
   [Number] [nvarchar](12) NOT NULL,
   CONSTRAINT [pkey_tContactPhone] PRIMARY KEY CLUSTERED 
   (
       [ID] ASC
   )
)
GO

ALTER TABLE tContactPhone  WITH CHECK ADD  CONSTRAINT FK_tContactPhones FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO

ALTER TABLE tContactPhone CHECK CONSTRAINT FK_tContactPhones
GO

CREATE TABLE tClaim(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [ContactID] [int] NOT NULL,
   [Name] [nvarchar](50) NOT NULL,
   [Status] nvarchar(10) not null,
   CONSTRAINT [pkey_tClaim] PRIMARY KEY CLUSTERED 
   (
       [ID] ASC
   )
)
GO

ALTER TABLE tClaim  WITH CHECK ADD  CONSTRAINT FK_tClaim FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO


/*
       Add Test Data
*/
declare @Count int = 0
declare @ContactID int =0

while(@Count<100000)
begin
   set @Count = @Count+1
   insert into tContact(Name)
   select 'Name' + convert(nvarchar(10),@Count)    

   set @ContactID= SCOPE_IDENTITY()

   insert into tContactPhone(ContactID,Number,Type)
   select @ContactID,@Count+1,'Home'
   union select @ContactID,@Count+1,'Cell'

   insert into tClaim(ContactID,Name,Status)
   select @ContactID, convert(nvarchar(10),@ContactID)+'_ClaimName',case @Count % 25 when 0 then 'Won' else 'Closed' end
end

/*
       Add Indexes for Queries
*/
create index Status on tClaim(Status) include (Name,ContactID)
create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)

如果您通過 (ContactID,ID) 對 tContactPhone 進行集群，而不是在 ID 上使用聚集索引，在 ContactID 上使用單獨的非聚集索引，那麼您可以獲得幾乎同樣好的索引計劃。例如

CREATE TABLE tContactPhone(
   [ContactID] [int] NOT NULL,
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Type] [nvarchar](25) NOT NULL,
   [Number] [nvarchar](12) NOT NULL,
   CONSTRAINT [pkey_tContactPhone] PRIMARY KEY CLUSTERED 
   (
       [ContactID],[ID] 
   )
)

對於“子表”來說，這通常是一種性能更好的模式，因為聚集索引也支持外鍵。

您有多少韓元索賠（佔索賠總數的百分比）？如果該百分比低於 1%（或者如果您的索賠少於一千個），那麼在 ContactID_Type 中為每個聯繫人執行嵌套循環連接和索引查找可能是有意義的。否則，散列連接（或合併連接）可能會更好（因為使用索引查找讀取大部分 tContactPhone 表的效率低於使用掃描讀取整個表的效率）。

如果將雜湊聯接與 ContactID_Type 索引一起使用，則 Type 列不能用於查找。要使用查找，它需要一個以 Type 列作為第一個鍵列的索引。這就是優化器建議在 Type 列上使用索引的原因（因為它希望從該索引中讀取更少的行）。

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