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

jinjazz 2009-09-14 03:08:12

加精

请大家支持我的blog
http://blog.csdn.net/jinjazz/archive/2009/09/14/4551008.aspx

最近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的效率是一样的。这样我们就能看出来，索引查找要比索引扫描快，索引扫描要比表扫描快。大概解释一下我个人的理解，索引的存储方式是一个特定数据结构的树，查找可以被优化，不必遍历整个树的所有节点所以最快，而索引扫描需要遍历所有树的节点所以稍慢但仍然要比表扫描快。

...全文

2177 133 打赏收藏转发到动态举报

写回复

用AI写文章

133 条回复

切换为时间正序

请发表友善的回复…

发表回复

lzdzx 2012-04-07

打赏
举报

学习了。。。

wangbin2667 2011-08-04

打赏
举报

这里还有提一下，数据的可选择性对优化也是有所影响的，在可选择性低，表数据少的时候表扫描可能会快于索引搜索，在可选择性高并且数据量大的情况下索引要比表扫描快很多

wangbin2667 2011-08-04

打赏
举报

这个事情不能一概而论在测试数据环境已经机器本身的性能上有所不同，那么当前得到的结果也会不同，楼主的测试数据有些小如果你测试百万级别，千万级别的数据又会是另外一个样，只能是根据实际的情况选择最好的方案，没有绝对的事情，尤其对数据库优化来说

zxh302 2010-03-18

打赏
举报

学习了,呵呵

sdwfjack 2009-09-21

打赏
举报

不错，有技术水准。

david0927cs2006 2009-09-18

打赏
举报

支持这种对技精益求精的精神

学习一下

顶

claro 2009-09-18

打赏
举报

[Quote=引用 19 楼 chuifengde 的回复:]
请问关于SQL codeselect*from (select'amm_99'as sunionselect'happyflystone' ) twhere slike'%[0-9]%'
这条语句中的'amm_99' 和'happyflystone'。你是索引找到的还是缓冲命中的？
[/Quote]这个要探究。
官方描述前置%的like无法引用索引。

kate_sun 2009-09-18