网站 › 『 Sybase ASE 』 › 条件中使用like的疑问

guilber 发表于 2007-6-15 16:54:19

条件中使用like的疑问

<p>有两条语句，查询结果一样，执行计划不同，执行效率也不同，但不知效率为什么又这么大的差距</p><p>语句一：select count(*) from&nbsp;table1 where usernumber like '1332222%'</p><p>&nbsp;&nbsp;&nbsp; STEP 1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The type of query is SELECT.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Evaluate Ungrouped COUNT AGGREGATE.</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM TABLE<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Nested iteration.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index : idx_table1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Forward scan.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Positioning by key.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index contains all needed columns. Base table will not be read.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Keys are:<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; usernumber&nbsp; ASC<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Using I/O Size 2 Kbytes for index leaf pages.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; With LRU Buffer Replacement Strategy for index leaf pages.<br/></p><p>语句二：select count(*) from table1 where usernumber like '1332222'+'%'</p><p>&nbsp;&nbsp;&nbsp; STEP 1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The type of query is SELECT.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Evaluate Ungrouped COUNT AGGREGATE.</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM TABLE<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Nested iteration.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index : idx_table1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Forward scan.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Positioning at index start.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index contains all needed columns. Base table will not be read.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Using I/O Size 2 Kbytes for index leaf pages.<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; With MRU Buffer Replacement Strategy for index leaf pages.<br/></p>

flybean 发表于 2007-6-20 08:51:51

执行效率是怎么比较的？两次测试的先后顺序？之间有没有重新启动ＡＳＥ？

guilber 发表于 2007-6-20 13:12:24

<p>table1有500万的数据，使用语句1，瞬间便能查出结果；使用语句2，查询时间为四十几秒</p><p>同样找到一张表，数据有70万左右，类似语句1和语句2的查询，均有如上的效率差别现象</p><p>另外，查询前后未重启ASE，查询先后并未改变现象。</p><p>另外从计划中可看出</p><p>语句1：</p><p>With LRU Buffer Replacement Strategy for index leaf pages.<br/></p><p>语句2：</p><p>With MRU Buffer Replacement Strategy for index leaf pages.<br/></p><p>查询了一些资料，不知是否与此有关。</p>

guilber 发表于 2007-6-21 16:54:32

没人回答，自己顶！

诺华 发表于 2007-6-26 10:14:58

涉及到一个搜索变量问题。如果你用like '1332222%'，可以使用搜索变量，实际上转换为大于和小于；如果你使用like '1332222'+'%'，则属于有算术表达式，就不能使用搜索变量。而由于所需列都在索引里面，只是一个索引覆盖，当然效率差很多了！

ehxz 发表于 2007-6-26 12:06:01

最好用字符串,先把表达式,那怕是简单的+组合起来再SQL处理试试.对数据库来说使用的处理机制不一样,效率就差远了.

查看完整版本: 条件中使用like的疑问