charindex代替like并非"更快更全面"解决方法

charindex代替like并非"更快更全面"
请大家支持我的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

转义百分号类似下面方法：

SQL code

select  *  from(select 'a%b' as s union select 'ab' )twhere s like '%\%%' escape '\' ;

反而，like可以实现比charindex更复杂的通配功能，比如partindex函数才支持的方括号。

SQL code

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

然后：说一下是否更快，这个需要有测试数据，不是我相信快就快的，sql优化结果谁都无法预料。我在windows2008+sqlserver2005中的测试结果是没有索引一样快，有索引like快。

特别强调一下虽然是%%的like，索引还是起作用的。

测试数据如下：

SQL code

use  tempdbgoif (object_id ('t_test' )> 0 )drop table t_testgocreate table t_test (f1 varchar (100 ), f2 varchar (100 ), f3 varchar (100 ))goinsert into t_test select newid (), newid (), newid ()go 1000create index i_test on t_test (f1 )go 

我们看两组sql语句的查询计划

第一组是：
 

SQL code

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

[解决办法]
学习了
[解决办法]
顶下jj，学习
[解决办法]
学习了,呵呵,上次宝鸭有测试过这两种的索引
[解决办法]
向楼主学习来的
[解决办法]
飘过
[解决办法]
学习
[解决办法]
牛人。学习
[解决办法]
xuexi
[解决办法]
牛人。学习
[解决办法]
学习
[解决办法]
学习.
[解决办法]

探讨

学习

------解决方案--------------------

学习
[解决办法]
未必用索引就比全表扫描快
[解决办法]
支持剪剪！
[解决办法]
支持JJ
[解决办法]
很不错的帖子,我很赞同这个观点,索引是一种多叉树状结构,枝节点到表的关系是地址转换而已,树枝是一种算法,很久以前在看数据存储的时候我就这么理解.

探讨

大概解释一下我个人的理解，索引的存储方式是一个特定数据结构的树，查找可以被优化，不必遍历整个树的所有节点所以最快，而索引扫描需要遍历所有树的节点所以稍慢但仍然要比表扫描快。

[解决办法]
请问关于

SQL code

select  *  from (select  'amm_99'  as  s union  select  'happyflystone' ) twhere s like '%[0-9]%'
[解决办法]
SQL还是需要活学活用滴。
[解决办法]
学习了。
[解决办法]
学习
[解决办法]
剪剪这个还是会引起争议的。

因为like使用索引扫描还是使用全表扫描，还要取决于所查结果的记录数占总记录数一个比例的大小。
like 不一定用索引扫描,charindex也不是仅能使用表扫描。


但剪剪所指文章中的like不能like%，我支持。

[解决办法]
好好學習
[解决办法]
谢谢剪子哥发起这种讨论

参与讨论：

如果你测试一下，就会知道，charindex('abc',f1)=1和charindex('abc',f1)>0的效率是一样的。这样我们就能看出来，索引查找要比索引扫描快，索引扫描要比表扫描快。大概解释一下我个人的理解，索引的存储方式是一个特定数据结构的树，查找可以被优化，不必遍历整个树的所有节点所以最快，而索引扫描需要遍历所有树的节点所以稍慢但仍然要比表扫描快。


表扫描比索引扫描快的时候：读取表中90%以上的数据，用索引不如用表扫描快（听说的）
索引扫描比索引查找快的时候：读取目标节点中90%以上的数据时，用索引查找不如用索引扫描快（别人博客说的）

另外，在聚集索引上，CHARINDEX也能用到索引


[解决办法]
标题中的'并非'取的好.

学习JJ
[解决办法]
探讨

go 1000改成go 100000
然后这两个可能都会是表扫描。

从存储上来讲，这两个对io的成本应该是相同的。
如果执行计划不同，那么我会认为在算法上，可能有少许差异，或是sql server优化器失误。

[解决办法]
嗯嗯，记下
[解决办法]
学习

优化的最终结果肯定是测试说了算

关于表扫描和索引扫描，我认为一般是索引扫描快，主要是一般情况下索引比表的字段少，所以每页（或者说每个节点）放的行数多，总页数就少。（当然有反过来的，如果索引包含了所有字段而且填充因子不是100，就会出现反过来的情况，不过一般认为，包含所有字段的索引是不合理的）

[解决办法]
向牛人们学习
[解决办法]
学到很多！ 好帖子！！ 顶！！
[解决办法]
探讨

学习

[解决办法]
呵呵学习了。。。
[解决办法]
好人，好贴！
[解决办法]
很好
[解决办法]
hao
[解决办法]
事实胜于雄辩
[解决办法]
收到。
------解决方案-------------------- 
 
又看不到自己的回复了
[解决办法]
坐地板
[解决办法]
学习
[解决办法]
太强大了
[解决办法]
LZ博客收藏了
平常只见过like,用得都不多

学习了
[解决办法]
 长见识了
[解决办法]
剪剪的话更可信
[解决办法]
强~~！！
[解决办法]
mark
[解决办法]
支持
[解决办法]
学习。
[解决办法]
学习！学习还是学习 楼主牛
[解决办法]
不明真相的群总前来围观，目前情绪稳定。。
[解决办法]
飘过
[解决办法]
厉害
[解决办法]
學習。
[解决办法]
馬克，學習一下~
[解决办法]
学习
[解决办法]
学习
[解决办法]
very strong
[解决办法]
探讨

引用:
引用:
引用:
go 1000改成go 100000
然后这两个可能都会是表扫描。

从存储上来讲，这两个对io的成本应该是相同的。
如果执行计划不同，那么我会认为在算法上，可能有少许差异，或是sql server优化器失误。


如果所有的查询都得到最优化，那么SQL就没有技巧可言

如果花大量的时间比较得到最优，说不定找一个折中的方案早就有了结果，所以不是失误，是中庸


我宁可希望sql多花一点时间生成一个最优化，然后缓存起来被千万次的调用
也不希望生成一个折中的方案,
何况仅仅是简单的sql.
你认为是中庸也好，我认为是sql server优化失误也罢，总之，sql server 应该实现这样的功能但它有实总会差强人意。





那sql server会让你失望


[解决办法]
正事不干, 在这些没有用的地方瞎扯

真正的优化,不在乎一句语句的得失. 数据库 设计好了,自然就优化了

实在不行, 就把表里面的历史数据全部移走,这样就快了吧
[解决办法]
sf
[解决办法]
学习
[解决办法]
学习，顶！
[解决办法]
跟你学习
[解决办法]
顶JJ
[解决办法]
OK@@认真学习
[解决办法]
说得有道理
[解决办法]
确实如楼主所说，有些首页上出现的博文写的确实一般，甚至有可能会早晨误导。我前几天就看过一篇写集中计算的，看题目很吸引人，抱着学习的态度虔诚的阅读，结果越看越觉得不对劲，幸好看了看大家的评论，原来质疑声一片。
------解决方案-------------------- 
 
看看
[解决办法]
en
[解决办法]
学习，学习
[解决办法]
kankan
[解决办法]
学习了
[解决办法]
不错，学习 

 

查看更多 下一篇