再发两个SQL语句效率，附执行时间图

Question

select  id,title from info where cityid=11 order by id desc select  id,title from info where substring(city,1,2)='11' order by id desc 其中cityID为INT数据类型  city为varchar数据类型 已经发过一个帖子，大家都认为第一个快，我也这么认为，可是我把city和CITYID都建索引后大家自己看结果吧结果是差不多，甚至第二个稍快
我想问的是为什么会这样?请懂得索引的高手支招？

Answer 1

其中试验1，2，3，4，5，9列为第二个SQL语句的执行结果
其中试验6，7，8，9为第一个SQL语句的执行结果

Answer 2

不好意思，上面打错了
其中试验1，2，3，4，5，10列为第二个SQL语句的执行结果 
其中试验6，7，8，9为第一个SQL语句的执行结果

Answer 3

你能给我点实验数据和表结构吗。
欢迎加入MSN群：[email protected]

Answer 4

还有 order by 这个应该去掉，如果要加，需要和索引一致，也就是说你的索引也应该降序

Answer 5

除了order by对你的测试影响之外，还有可能受数据库缓存影响，你应该把第一次运行的测试结果忽略，因为以后别人都有可能是从内存中提取数据的
  

Answer 6

substring(city,1,2)='11'  不是导致行计算吗？根本用不上索引呀，LZ 给个 city 非索引的 test 再看看

Answer 7

呵呵，楼主先插入几十万条数据在测试吧？
用sql server profiler测就可以。

Answer 8

这个跟你的SQL 的原理有关
SQL会将一些需要编译，优化的语句进行缓存，而简单的SQL语句则不做处理第一个SQL语句直接使用的是CityID ，这样的简单语句在SQL中属于直接就可以执行的，无需缓存，因此SQL会直接扫描索引来直接加载行。
而第二个语句中带有Substring语句，这样的语句很明显是需要占用cpu来计算的，因此在对此语句进行预编译以后，SQL会将该语句加载至缓存，以便下次使用的时候直接从内存读取，不用再次编译，所以性能会有所提高。
换句话说，第二种情况之所以会快是因为缓存的原因，如果不怕麻烦你可以每次从新启动SQL服务，或者执行SQL语句之间的间隔时间长一些，就会发现不一样的结果而且你上面的测试只是显示出了从客户端到服务端的一些时间信息，如果你在服务器上用Profiler工具检测CPU ，IO读取等等信息的话，应该更为准确以上根据SQL的原理来解释，实际结果以Profiler工具为准，记得保证没有缓存因素在内。

Answer 9

sql2005自带的
查询--》显示估计的执行计划
大家可以自己测，我是在远程数据库上测的，8万多条记录

Answer 10

sql2005没有用过 不知道效果如何！

Answer 11

13楼的cenychen 说的好像有点对，但上面第一次执行的并不比第二次慢

Answer 12

经过多次交替查询分析，几乎可以确定第一个比第二个稍快
出现执行最长时间的几率总是出现在第二个SQL上
这里是用第二个SQL语句做的8万条记录分页，大家可以体验下速度www.zhaoyizhao.net/jisu.aspx

Answer 13

哎，老打错，是：
经过多次交替查询分析，几乎可以确定第2个比第1个稍快 
出现执行最长时间的几率总是出现在第1个SQL上 
这里是用第2个SQL语句做的8万条记录分页，大家可以体验下速度www.zhaoyizhao.net/jisu.aspx

Answer 14

如果city不建索引明显不如第一个
也就是虽然是varchar数据类型，但建索引还是会明显提高效率

Answer 15

同意樓上的說法﹐一個 field 盡管有建立index,但該field在where 中套用了函數﹐index就無效

Answer 16

测试应该没问题，测试TOP 前几千条或全部的约8万条数据就是这个结果
你数据再多也是这样啊，远程查询连接数据库查询8万条数据差不多要等30秒了

Answer 17

你用的谁家产品就找谁要资料去，我不用sql2005，所以不知道
但是我知道mysql中关闭查询缓存可以这样做
SELECT SQL_NO_CACHE * from ...

Answer 18

不太可能 
varchar 计算列 还能快？

Answer 19

select  id,title from info where cityid=11 order by id desc select  id,title from info where city like '11%' order by id desc 應該是第一個快,第二個改為以上應該會更快!substring()會導致無法使用索引

Answer 20

(1).
 select  id,title from info where cityid=11 order by id desc  返回8万多条记录，所以就算是cityid上建有索引，优化器也不可能选择它，因为要搭配Book LoopUp操作去取title跟id,这样的随机IO太昂贵了，还不如Clustered Index scan成本低, 所以这里应该是一个Clustered Index scan的操作  建议CityID的索引这么建:
   Create index idx_Name ON Info (CityID) INCLUDE (id,title)(2).
   select  id,title from info where substring(city,1,2)='11' order by id desc 
   这个不用说，大家都知道索引用不上,同样是一个Clustered index scan.

Answer 21

第一个语句用的是index seek, 第二个语句用的是index scan.
区别就是一个是直接在索引上定位,一个要扫描整个索引.表扫描?基本上不会.除非这个表很小.

Answer 22

你把city和CITYID都建索引了吗？
都建索引后再试试？？

Answer 23

你跑一下　执行计划　看看！那里应该由你要的答案
你的索引是desc 得的吗? 如果是ASC 的索引 且数据量不大 那就很有可能 第二条快!建议搂住用SQL2000的话 索引优化向导跑一下他会提示您建什么样子的索引！　很可能你索引不合适所以造成这个状态！

Answer 24

第二次要清一下缓存 
另外cityid 是不是没有 11....的纪录?

Answer 25

索引搜索并不是等效率的,我也总认为运算和不需要运算肯定是不需要运算的快.
int类型和varchar类型的存储大小不同,而每一个页面都是固定大小,因此两个索引存储所耗用的页面数不同,因此在扫描的时候所花的时间一定是有区别的.
具体我也说不清楚,我只能想到这方面的因素了.

Answer 26

上面这个因为我ID = 11只有一条结果，所以改成id<1100了，两条语句结果一样多。

Answer 27

我又搜了下别的语句，建了索引跟不建索引的区别：
同一格式的语句，比如
select  id,title from info where cityid<1100 order by id desc 结果1000条记录 和
select  id,title from info where cityid<50000 order by id desc 结果50000条记录不建索引，不论多少条结果，用的时间都一样。上面两个都是16。
建了索引，结果越多，用的时间越多，第一个16，第二个88。楼主看看你的第一条的结果是不是比第2条多很多？

Answer 28

晕！上面两条SQL语句执行的结果数据行数是一样多的，否则怎么测啊。你一个10万条，一个1条这样测试有什么意义？！
再说明下：
select  id,title from info where cityid=11 order by id desc 
select  id,title from info where substring(city,1,2)='11' order by id desc 
这两条SQL语句的执行的结果有相同行数的数据，大概8万条数据
（执行这SQL行数就相同了：update set info set cityid=substring(city,1,2) ）
其中ID建索引，排序为DESC,CITY建索引排序为默认ASC，cityID建索引排序为默认ASC
大家测试的时候一定要保证两条SQL执行的结果有相同行数的数据，哎，这个也要说明么