情况如下:
表名: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匹配其它条件的吗?
表名: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了
like '%admin'
like '%admin%'
以上两种写法会让索引失效。
like 'admin%'
这个索引有效。
全文索引如果只是长度不大的字符串,可以用建立索引的方式来提高查询的效率,也可以考虑使用charindex()函数来替代like。 如果是大文本的数据,建议使用全文索引
遇到类似的问题,无法从软件和代码角度优化,那就要从硬件角度下手,比如这里的性能瓶颈应该集中在磁盘I/O上,那就使用磁盘阵列。 或者条件允许的话,使用分布式数据库系统,在多台数据库服务器上进行分布式查询。 解决办法总是有的,只是受制于外部环境。
还有全文索引其实是不错的,但客户已经觉得导入过慢了,现在有时一天要导好几批资料入库,都是十几万的数据导入。全文索引使得导入性能大大降低。难到sqlserver2005不能自己根据where条件进行数据分流查询吗??虽然我总表1000万数据,但我加入 Where 省份='湖北' and 资料年份=2010 and 地址 like '%汉%'...... 后,前两个条件是索引已经可以将数据量缩小到40万了,为何后面的条件不在这40万里进行查询??SQL05让我很受伤,现在我所郁闷的是where中只要含有一个like,好像所有索引都失效,为什么会这样呢??
先通过年份或省份上的索引检索出30万条记录,然后再在这30万条记录中查找满足"地址 like '%汉%'"的记录,即使这样也不一件容易的事,在这30万条记录中查找满足"地址 like '%汉%'"的记录,你可以估计一下要耗时多久,我感觉3分可能是正常的(平均下来每分钟10万条,每秒钟1600条)2.情况二:
优化器基于现有的数据分布统计进行预估,如果它认为情况一中的方案所消耗的成本高于全表扫描,则有可能会忽略年份或省份上的索引,直接变成全表扫描。看看查询计划吧,我感觉你的应该是情况一,而且就算是情况一,从你提供的表结构来看,我认为3分钟也是正常的。
---使用索引优化数据库查询效率
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)=1002.对索引列使用了'%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')
先把执行计划贴出来看下,我到现在都没搞清楚是不是全表扫描你上面说的把数据删除到只剩40w条,再执行就快了,这个可比性不太强,你那剩下的40w中被前面条件中的索引过滤后可能剩下的就不多了,再在这不多的记录中做like运算,耗时肯定不一样的sql是基于成本的,成本这个东西没有绝对值,比如说(我下面都是打比方,这东西没有绝对的,反正优化器最大,它说成本高就成本高,说成本低就成本低):情况一:
如果有个表总共10w条记录,优化器预计命中5%,就是500条
那么大多数情况下索引有效,因为命中比例不高,绝对数据量也不多,优化器认为使用索引的成本低于全表扫描情况二:
如果有个表总共500条记录,,优化器预计命中90%,就是450条
那么索引可能无效,因为绝对数据量不多,但命中比例很高,优化器可能认为使用索引的成本高于全表扫描情况二:
如果有个表总共1000w条记录,,优化器预计命中5%,就是50w条
那么索引也有可能无效,因为命中比例不高,但绝对数据量很多,优化器也可能认为使用索引的成本高于全表扫描