charindex代替like並非"更快更全面"

 

    最近csdn的編輯們在社區和網站首頁的標題讓人有些受不了，一個吸引眼球的大紅專題點進去只是一個聊聊數位普通文章..這種做法用來八卦也就算了，用來封裝技術文章那是相當不負責的。

    一個普普通通的技術博文，不管觀點對錯，水平如何，作者的拿出來分享的做法是值得肯定的，但在沒有經過論證，人為在加上一個漂亮的副標題打到技術專區的首頁上，難免誤導不明真相的群眾。

 

   

 

文章出處

    http://database.ctocio.com.cn/336/9159836.shtml

 

 

大概這就叫傳說中的以訛傳訛，就算作者認為他的方法更全面，人家也沒有說更快...

 

別的不扯了，我寫此文的目的以來是為了督促一下csdn官方，發布在首頁上的文章要仔細審核，起碼得專業精神還是需要的。二來讓不明白真相的群眾明白一下。我們就從更快更全面說開來：

 

首先：先明確掉全面這個問題，想like百分比符號很簡單，協助文檔裡面就有

 

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htm

 

轉義百分比符號類似下面方法：

  select * from
( select 'a%b' as s union select 'ab' ) t
where s like '%/%%' escape '/' ;

 

反而，like可以實現比charindex更複雜的通配功能，比如partindex函數才支援的方括弧

 

  select * from ( select 'amm_99' as s union select 'happyflystone' ) t
where s like '%[0-9]%'

 

 

然後：說一下是否更快，這個需要有測試資料，不是我相信快就快的，sql最佳化結果誰都無法預料。我在windows2008+sqlserver2005中的測試結果是沒有索引一樣快，有索引like快。

特彆強調一下雖然是%%的like，索引還是起作用的。

 

測試資料如下：

 

  use tempdb
go
if ( object_id ( 't_test' ) > 0 ) drop table t_test
go
create table t_test ( f1 varchar ( 100 ), f2 varchar ( 100 ), f3 varchar ( 100 ))
go
insert into t_test select newid (), newid (), newid ()
go 1000

create index i_test on t_test ( f1 )
go

 

我們看兩組sql語句的查詢計劃

 

第一組是： select * from t_test where f1 like '%abc%'
select * from t_test where charindex ( 'abc' , f1 ) > 0

 

結果如下：

 

很明顯是like因為有索引掃描(rid是行標誌符)而速度快於charindex，這裡我們需要理解表掃描，索引掃描和索引尋找的區別。為了說明這個問題，我們再看一下 like 'abc%'和charindex('abc',f1)=1的區別。

 

 

如果你測試一下，就會知道，charindex('abc',f1)=1和charindex('abc',f1)>0的效率是一樣的。這樣我們就能看出來，索引尋找要比索引掃描快，索引掃描要比表掃描快。大概解釋一下我個人的理解，索引的儲存方式是一個特定資料結構的樹，尋找可以被最佳化，不必遍曆整個樹的所有節點所以最快，而索引掃描需要遍曆所有樹的節點所以稍慢但仍然要比表掃描快。