LIKE 使用索引，CHARINDEX 沒有？

這個問題與我的舊問題有關。以下查詢需要 10 到 15 秒才能執行：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0) 

在一些文章中，我看到使用索引CAST並CHARINDEX不會從索引中受益。還有一些文章說 usingLIKE '%abc%'不會從索引中受益，但LIKE 'abc%'會：

http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where https://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries http://www.sqlservercentral.com/Forums/Topic186262-8-1.aspx#bm186568

就我而言，我可以將查詢重寫為：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'

此查詢提供與前一個查詢相同的輸出。我為 column 創建了一個非聚集索引Phone no。當我執行此查詢時，它只需1 秒即可執行。與之前的14 秒相比，這是一個巨大的變化。

如何LIKE '%123456789%'從索引中受益？

為什麼列出的文章聲明它不會提高性能？

我嘗試重寫要使用的查詢CHARINDEX，但性能仍然很慢。為什麼不像查詢那樣CHARINDEX從索引中受益LIKE？

使用查詢CHARINDEX：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [Company].dbo.[customer]
WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

執行計劃：

使用查詢LIKE：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [Company].dbo.[customer]
WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'

執行計劃：

LIKE ‘%123456789%’ 如何從索引中受益？

只有一點點。查詢處理器可以掃描整個非聚集索引來查找匹配項，而不是整個表（聚集索引）。非聚集索引通常比建立它們的表要小，因此掃描非聚集索引可能會更快。

不利的一面是，查詢所需的任何未包含在非聚集索引定義中的列都必須在基表中逐行查找。

優化器根據成本估算在掃描表（聚集索引）和使用查找掃描非聚集索引之間做出決定。估計成本在很大程度上取決於優化器希望您的LIKE或CHARINDEX謂詞選擇多少行。

為什麼列出的文章聲明它不會提高性能？

對於不以萬用字元開頭的條件，SQL Server 可以執行索引的LIKE部分掃描，而不是掃描整個事物。例如，可以通過僅測試索引記錄和（確切的邊界值取決於排序規則）來正確評估。LIKE 'A%``>= 'A'``< 'B'

這種查詢可以利用 b-tree 索引的查找能力：我們可以>= 'A'使用 b-tree 直接查找第一條記錄，然後按索引鍵順序向前掃描，直到找到一條< 'B'測試失敗的記錄。由於我們只需要將LIKE測試應用於較少的行數，因此性能通常更好。

相比之下，LIKE '%A不能變成部分掃描，因為我們不知道從哪裡開始或結束；任何記錄都可能以 結尾'A'，因此我們無法改進掃描整個索引並單獨測試每一行。

我嘗試重寫要使用的查詢CHARINDEX，但性能仍然很慢。為什麼不像CHARINDEXLIKE 查詢那樣從索引中受益？

在這兩種情況下，查詢優化器在掃描表（聚集索引）和掃描非聚集索引（使用查找）之間有相同的選擇。

根據成本估算在兩者之間做出選擇。碰巧 SQL Server 可能對這兩種方法產生不同的估計。對於LIKE查詢的形式，估計可能能夠使用特殊的字元串統計資訊來產生一個相當準確的估計。該CHARINDEX > 0表格根據猜測生成估計值。

不同的估計足以使優化器CHARINDEX為LIKE. 如果您強制CHARINDEX查詢使用帶有提示的非聚集索引，您將獲得與 for 相同的計劃LIKE，並且性能將大致相同：

SELECT
   [Customer name],
   [Sl_No],
   [Id]
FROM dbo.customer WITH (INDEX (f))
WHERE 
   CHARINDEX('9000413237', [Phone no]) >0;

兩種方法在執行時處理的行數是相同的，只是LIKE在這種情況下表單會產生更準確的估計，因此查詢優化器會選擇更好的計劃。

如果您發現自己LIKE %thing%經常需要搜尋，您可能需要考慮我在 SQL Server 中的 Trigram Wildcard String Search 中所寫的一種技術。

SQL Server以對查詢可用LIKE但不能由CHARINDEX.

有關此內容的更多資訊，請參閱字元串摘要統計部分。

一些重要的警告是，任何萬用字元的轉義都必須使用專有的方括號技術而不是ESCAPE關鍵字來完成，並且對於超過 80 個字元的字元串，只使用第一個和最後 40 個字元。

WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

將只對不等式謂詞使用標準猜測，即將返回 30% 的行。

查詢（在您的LIKE情況下）大概估計與謂詞匹配的行數要少得多。

請注意，前導萬用字元仍會阻止索引查找。仍會掃描整個索引，但它使用的是比聚集索引更窄的不同索引。較窄的索引不涵蓋查詢使用的所有列，因此第二個計劃需要鍵查找來檢索失去的列。

這個計劃極不可能以 30% 的估計被選中。SQL Server 會認為掃描整個聚集索引並避免那麼多查找更便宜。有關其他範例，請參閱有關引爆點的這篇文章。

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