sql2005 count求和where后带like异常慢，求支招！

[开发爱好者9527]

情况如下：
表名：che 列数:23 字段类型:主键ID(guid)，其它为int或nvarchar(100)
索引:ID唯一聚集索引,非聚集索引(省份，资料年份)
查询如下：
Select count(*) From Che--接近1000万--耗时1秒
Select count(*) From Che Where 省份='湖北' and 资料年份=2010--40万数据--耗时0秒
Select count(*) From Che Where 省份='湖北' and 资料年份=2010 and 地址 like '%汉%'--30万数据--耗时3分+
问题：
为什么第三句查询如此慢？
and中带了like其它索引都会失效的吗？
它不是先把有索引的查询后再and匹配其它条件的吗？

[昵称被占用了]

非聚集索引(省份，资料年份)
把这个索引改成include 地址字段

可能2005不支持include，那只好设法用2008了

[开发爱好者9527]

[Quote=引用 1 楼 haiwer 的回复:]
非聚集索引(省份，资料年份)
把这个索引改成include 地址字段

可能2005不支持include，那只好设法用2008了
[/Quote]
省份，资料年份 是我们要求客户固定的，为了更好的数据分流，减小IO压力，但其它条件我们不能限制，后面还有其它指标的模糊查询，不止地址一个的,唉，为什么两个索引条件后再加个like，速度就差这么多呢，MS不能在40万数据中进行like呢，为什么是重新全表扫描，无视前面的索引条件呢

[开发爱好者9527]

Mark

[chuanzhang5687]

我只说下用like时注意的事项。
like '%admin'
like '%admin%'
以上两种写法会让索引失效。
like 'admin%'
这个索引有效。 

[chuanzhang5687]

http://topic.csdn.net/t/20040821/10/3295983.html
全文索引



如果只是长度不大的字符串，可以用建立索引的方式来提高查询的效率，也可以考虑使用charindex()函数来替代like。

如果是大文本的数据，建议使用全文索引


遇到类似的问题，无法从软件和代码角度优化，那就要从硬件角度下手，比如这里的性能瓶颈应该集中在磁盘I/O上，那就使用磁盘阵列。

或者条件允许的话，使用分布式数据库系统，在多台数据库服务器上进行分布式查询。

解决办法总是有的，只是受制于外部环境。

[开发爱好者9527]

[Quote=引用 4 楼 chuanzhang5687 的回复:]
SQL code
我只说下用like时注意的事项。
like '%admin'
like '%admin%'
以上两种写法会让索引失效。
like 'admin%'
这个索引有效。
[/Quote]
谢谢解答，道理我懂，可like'%..%'这是客户最需要的功能了,从业务出发，就个是避免了了的。
还有全文索引其实是不错的，但客户已经觉得导入过慢了，现在有时一天要导好几批资料入库，都是十几万的数据导入。全文索引使得导入性能大大降低。难到sqlserver2005不能自己根据where条件进行数据分流查询吗？？虽然我总表1000万数据，但我加入 Where 省份='湖北' and 资料年份=2010 and 地址 like '%汉%'...... 后，前两个条件是索引已经可以将数据量缩小到40万了，为何后面的条件不在这40万里进行查询？？SQL05让我很受伤，现在我所郁闷的是where中只要含有一个like，好像所有索引都失效，为什么会这样呢？？

[chuanzhang5687]

可不可以创建一个临时表或者新表，然后把满足这个Where 省份='湖北' and 资料年份=2010条件的放到里面，再从这张表中去数据？[Quote=引用 6 楼 myadm 的回复:]

引用 4 楼 chuanzhang5687 的回复:
SQL code
我只说下用like时注意的事项。
like '%admin'
like '%admin%'
以上两种写法会让索引失效。
like 'admin%'
这个索引有效。

谢谢解答，道理我懂，可like'%..%'这是客户最需要的功能了,从业务出发，就个是避免了了的。
还有全文索引其实是不错的，但客户已经觉得导……
[/Quote]

[chuanzhang5687]

如果仅仅是因为一个like的话，估计微软也走不到这一步。去想想解决办法吧。没有做不到的只有想不到的[Quote=引用 6 楼 myadm 的回复:]

引用 4 楼 chuanzhang5687 的回复:
SQL code
我只说下用like时注意的事项。
like '%admin'
like '%admin%'
以上两种写法会让索引失效。
like 'admin%'
这个索引有效。

谢谢解答，道理我懂，可like'%..%'这是客户最需要的功能了,从业务出发，就个是避免了了的。
还有全文索引其实是不错的，但客户已经觉得导……
[/Quote]

[勿勿]

支持。
[Quote=引用 4 楼 chuanzhang5687 的回复:]
SQL code
我只说下用like时注意的事项。
like '%admin'
like '%admin%'
以上两种写法会让索引失效。
like 'admin%'
这个索引有效。
[/Quote]

[--小F--]

---使用索引优化数据库查询效率
1.不宜创建索引的情形
(1)经常插入，修改和删除的表
(2)数据量比较小的表，因为查询优化器在搜索索引时所花费的时间可能会大于遍历全表的数据所需要的时间

2.适合创建索引的情形
(1)为where子句中出现的列创建索引
(2)创建组合索引
(3)为group by 子句中出现的列创建索引

3.聚集索引的设计原则
(1)该列的数值是唯一的或者很少有重复的记录
(2)经常使用between ...and..按顺序查询的列
(3)定义identity的唯一列.
(4)经常用于对数据进行排序的列.

---无法使用索引的select语句
1.对索引列使用了函数,如：
select * from tb where max(id)=100

2.对索引列使用了'%xx'，如：
select * from tb where id like '%1'
需要注意的不是所有使用like关键字的select 语句都无法使用索引,比如
select * from tb where id like '1%'就可以使用索引

3.在where子句中对列进行类型转换(其实也是使用到了函数)

4.在组合索引的第1列不是使用最多的列,如在下面3个查询语句中建立组合索引,按顺序包含col2,col1,id列；
select * from tb where id='1' and col1='aa'
select id,sum(col1) from tb group by id
select * from tb where id='2' and col2='bb'
则第一句和第二句无法使用到索引 所以需要注意组合索引的顺序

5.在where 子句中使用in关键字的某些句子
当在in关键字后面使用嵌套的select语句，将无法使用在该列上定义的索引
如：
select 
 *
from
 ta 
where
 id 
in
 (select id from tb where ....)

--这样可以用到索引
select * from tb where id in('1','2')

[yubofighting]

fulltext，我最近也在学习

[开发爱好者9527]

看来只能使用全文索引或分区表了 鱼和熊掌不可兼得啊 Sql2005不算大型数据库 感觉我的这种需求在 海量数据中是常见的 已经和客户沟通加入两列固定的条件实现数据分流 看来悲剧了 Mssql就不能先找出最快的部分条件在And其它条件吗 为啥呢 ？有人遇到过么？

[开发爱好者9527]

5687兄，根据非聚集联合索引查找出数据插入临时表，再进行其它查询，应该会提速不少，但这个插入动作数据越大就会越慢的，对查询结果数据大的不太合适

[开发爱好者9527]

[Quote=引用 9 楼 bapi 的回复:]

1.情况一：
先通过年份或省份上的索引检索出30万条记录，然后再在这30万条记录中查找满足"地址 like '%汉%'"的记录，即使这样也不一件容易的事，在这30万条记录中查找满足"地址 like '%汉%'"的记录，你可以估计一下要耗时多久，我感觉3分可能是正常的（平均下来每分钟10万条，每秒钟1600条）

2.情况二：
优化器基于现有的数据分布统计进行预估，如果它认为情况一中的方案……
[/Quote]
有道理，但我如果复制这个表删除只有40w数据，有Like也很快的，差不多1秒，我感觉1000万数据是Sql05的瓶颈，何况我的列数较多，它为什么不在我那40万里检索，时间那么久肯定无视了我前面的两条件，做了次全表扫描

[bapi]

1.情况一：
先通过年份或省份上的索引检索出30万条记录，然后再在这30万条记录中查找满足"地址 like '%汉%'"的记录，即使这样也不一件容易的事，在这30万条记录中查找满足"地址 like '%汉%'"的记录，你可以估计一下要耗时多久，我感觉3分可能是正常的（平均下来每分钟10万条，每秒钟1600条）

2.情况二：
优化器基于现有的数据分布统计进行预估，如果它认为情况一中的方案所消耗的成本高于全表扫描，则有可能会忽略年份或省份上的索引，直接变成全表扫描。

看看查询计划吧，我感觉你的应该是情况一，而且就算是情况一，从你提供的表结构来看，我认为3分钟也是正常的。

[bapi]

[Quote=引用 11 楼 myadm 的回复:]

有道理，但我如果复制这个表删除只有40w数据，有Like也很快的，差不多1秒，我感觉1000万数据是Sql05的瓶颈，何况我的列数较多，它为什么不在我那40万里检索，时间那么久肯定无视了我前面的两条件，做了次全表扫描
[/Quote]

先把执行计划贴出来看下，我到现在都没搞清楚是不是全表扫描

你上面说的把数据删除到只剩40w条，再执行就快了，这个可比性不太强，你那剩下的40w中被前面条件中的索引过滤后可能剩下的就不多了，再在这不多的记录中做like运算，耗时肯定不一样的

sql是基于成本的，成本这个东西没有绝对值，比如说（我下面都是打比方，这东西没有绝对的，反正优化器最大，它说成本高就成本高，说成本低就成本低)：

情况一：
如果有个表总共10w条记录，优化器预计命中5%，就是500条
那么大多数情况下索引有效，因为命中比例不高，绝对数据量也不多，优化器认为使用索引的成本低于全表扫描

情况二：
如果有个表总共500条记录，，优化器预计命中90%，就是450条
那么索引可能无效，因为绝对数据量不多，但命中比例很高，优化器可能认为使用索引的成本高于全表扫描

情况二：
如果有个表总共1000w条记录，，优化器预计命中5%，就是50w条
那么索引也有可能无效，因为命中比例不高，但绝对数据量很多，优化器也可能认为使用索引的成本高于全表扫描