今天遇到一个很奇葩的问题,也可能是自己的经验不足。。。
select ID,Name,AddTime from Tab with(nolock) where AddTime>= '2017-10-01' AND AddTime<='2017-12-01'
搜索的时候竟然用了两秒钟,当然是建了索引的(非聚集索引),这个时候搜索竟然 聚集索引扫描........
然后通过加了一个语句,提升到了19毫秒
SELECT ID,Name,AddTime FROM Tab WITH(NOLOCK) WHERE AddTime>= '2017-10-01' AND AddTime<='2017-12-01' ORDER BY AddTime DESC
使用的是MsSqlserver
然后在网上找了一些资料也不能找到原因。
了解的大神们,懂得留个言。再次谢过
聚集索引建在哪个字段的?
聚集索引自增ID上,AddTime建立的是非聚集索引,按道理根据AddTime搜索不是根据AddTime跑索引吗?
@母鸡轰: 通常是走AddTime索引,但不知道为什么SQL Server现在认为走聚集索引扫描会比AddTime索引快,建议的解决方法:在AddTime索引中包含Name列
@dudu: 我现在不了解的就是为什么加上 ORDER BY AddTime 之后,就跑AddTime这个非聚集索引了,不明白里面的原理
@母鸡轰: SQL Server的执行引擎再笨,也不至于在 ORDER BY AddTime 时走聚集索引吧
@dudu: 默认排序是默认自增ID(主键/聚集索引)对吧,默认使用ID排序了
@母鸡轰: 是的,如果没有指定排序字段,会按照聚集索引进行排序
@dudu: 我现在的问题就是为什么使用DateTime类型作为索引的时候,不跑索引而跑聚集索引了,这就是我想要知道的原因,共勉!!
@母鸡轰: 重建一下AddTime字段的索引试试
@dudu: 试过了,无效
你的总数据量?数据依赖AddTime的分布情况是怎么样的?
AddTime数据分布还算均匀,总的数据量在五千多万
@母鸡轰: 你查询范围的数据有多少?
@Daniel Cai: 一百六十万左右
@母鸡轰: 这种应该是因为你select的字段索引是无法覆盖,而lookup所产生的io开销太大让db认为这里选择索引扫描还不如走全表扫,而你加了order by在这里可能跟加hint有类似效果,sql纠结了半天觉得这样走索引可能会好些。
ps下,你一次性撸这么多数据出来干什么?这么大的结果集返回换什么索引都不好使
@Daniel Cai:
SELECT COUNT(t1.AddTime),isnull(Sum(t1.Money),0.00)
FROM Table1 t1 WITH(NOLOCK)
LEFT JOIN Table2 t2 WITH(NOLOCK) ON t1.ID=t2.UID
WHERE t1.AddTime BETWEEN '2017-10-02 17:30:13'AND '2018-01-02'
@Daniel Cai: SELECT COUNT(t1.AddTime),isnull(Sum(t1.Money),0.00)
FROM Table1 t1 WITH(NOLOCK)
LEFT JOIN Table2 t2 WITH(NOLOCK) ON t1.ID=t2.UID
WHERE t1.AddTime BETWEEN '2017-10-02 17:30:13'AND '2018-01-02'
@Daniel Cai: SELECT COUNT(t1.AddTime),isnull(Sum(t1.Money),0.00)
FROM Table1 t1 WITH(NOLOCK)
LEFT JOIN Table2 t2 WITH(NOLOCK) ON t1.ID=t2.UID
WHERE t1.AddTime BETWEEN '2017-10-02 17:30:13'AND '2018-01-02'
我主要在测试这个统计结果的时候,试了下执行,AddTime没办法跑索引
@母鸡轰: 如果单纯只是为了使用上那个索引的话你在语句中加hint吧,还有这个里面left join能换成inner join么(满足需求)?
@Daniel Cai: 时间范围在30万的就用不到索引,只能看看sqlserver的优化策略了
谢谢你的回答了!
@母鸡轰: 这种场景下你单个字段的索引可能并不太合适了。你试下在ID列再建一个索引,然后include进来AddTime和Money看下情况(不是dba,只是随便猜测)。
@Daniel Cai: 这种方式不适合我们系统的业务,可能导致一些功能使用异常,目前使用强制索引方式 with(nolock,index(索引名称))
统计信息是否更新?addTime字段的数据类型是什么(smalldatetime和后面datetime做比较就很糟糕)?