[SQL] Compare Operator - where "LIKE" and "CHARINDEX"

• sql 使用 where 下列使否影響查詢成本
• LIKE
• CHARINDEX
• NOT LIKE
• 不同 LIKE 使用方式
• Query Optimizer 是否會依"回傳的資料列數"多少，選擇不同處理方法
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Production.Product 

• Step 2. 查詢 CASE 1

select * from Production.Product where Name like 'M%'
select * from Production.Product where CHARINDEX('M', Name) > 0
select * from Production.Product where Name like 'ML C%'
select * from Production.Product where CHARINDEX('ML C', Name) > 0

• Step 3. 查詢結果
• 查詢 1, 2：當只有查詢 1 個字元 LIKE 與 CHARINDEX，所查詢成本是一樣
• 查詢 3, 4：當提高準確度的 LIKE 查詢，速度快於 CHARINDEX 

• Step 3. 查詢 CASE 2

select * from Production.Product where Name like 'M%'
select * from Production.Product where Name like 'ML C%'

select * from Production.Product with (index(AK_Product_Name)) 
where Name like 'M%'

• Step 4. 查詢結果
• "索引搜尋"的相對總查詢成本 (相對於批次 x 索引搜尋成本)，如下：
• 查詢 1：16% x 100% = 16%
• 查詢 2：9% x 46%= 4.14%
• 查詢 3：75% x 6% = 4.50%
• 查詢 1, 2：查詢 2 的 LIKE 中得到更多的資料，使在相對查詢成本較快
• 查詢 1, 3：因多 "索引間查閱"會耗掉相對總查詢成本 70.5 % (75% x 94%)，使得整體成本變高

• Step 5. 查詢 CASE 3

select * from Production.Product with (index(AK_Product_Name)) 
where Name like 'M%'

select * from Production.Product with (index(AK_Product_Name)) 
where Name like '%M%'

select * from Production.Product with (index(AK_Product_Name)) 
where Name not like 'M%'

• Step 6. 查詢結果
• 查詢 1 (索引搜尋)：24% x 6% = 1.44%
• 查詢 2 (索引掃描)：32% x 8%= 2.56%
• 查詢 3 (索引掃描)：45% x 5% = 2.25%

• Result：
1. 高準確度的 LIKE 查詢，速度快於 CHARINDEX 
2. 如 LIKE 的第 1 個字元為明確有資料 (like 'M%')，才能使用 "索引搜尋"，否則只能使用 "索引掃描" 
3. 強制使用 "資料表提示" (with (index(AK_Product_Name))) 來指定所要使用的 "索引" 未必比較好
• REF：
• SQL Server 效能調校