首页 新闻 会员 周边 捐助

记一次数据库时间范围搜索慢的原因

0
[已解决问题] 解决于 2018-01-15 10:12

记一次数据库时间范围搜索慢的原因

 

今天遇到一个很奇葩的问题,也可能是自己的经验不足。。。

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

然后在网上找了一些资料也不能找到原因。

了解的大神们,懂得留个言。再次谢过

sql
母鸡轰的主页 母鸡轰 | 初学一级 | 园豆:74
提问于:2018-01-02 16:09
< >
分享
最佳答案
0

聚集索引建在哪个字段的?

奖励园豆:5
dudu | 高人七级 |园豆:30757 | 2018-01-02 18:33

聚集索引自增ID上,AddTime建立的是非聚集索引,按道理根据AddTime搜索不是根据AddTime跑索引吗?

母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 09:21

@母鸡轰: 通常是走AddTime索引,但不知道为什么SQL Server现在认为走聚集索引扫描会比AddTime索引快,建议的解决方法:在AddTime索引中包含Name列

dudu | 园豆:30757 (高人七级) | 2018-01-03 09:28

@dudu: 我现在不了解的就是为什么加上 ORDER BY AddTime 之后,就跑AddTime这个非聚集索引了,不明白里面的原理

母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 09:42

@母鸡轰: SQL Server的执行引擎再笨,也不至于在 ORDER BY AddTime 时走聚集索引吧

dudu | 园豆:30757 (高人七级) | 2018-01-03 10:35

@dudu: 默认排序是默认自增ID(主键/聚集索引)对吧,默认使用ID排序了

母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 10:37

@母鸡轰: 是的,如果没有指定排序字段,会按照聚集索引进行排序

dudu | 园豆:30757 (高人七级) | 2018-01-03 10:52

@dudu: 我现在的问题就是为什么使用DateTime类型作为索引的时候,不跑索引而跑聚集索引了,这就是我想要知道的原因,共勉!!

母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 10:54

@母鸡轰: 重建一下AddTime字段的索引试试

dudu | 园豆:30757 (高人七级) | 2018-01-03 10:55

@dudu: 试过了,无效

母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 10:56
dudu | 园豆:30757 (高人七级) | 2018-01-03 13:35
其他回答(2)
1

你的总数据量?数据依赖AddTime的分布情况是怎么样的?

Daniel Cai | 园豆:10424 (专家六级) | 2018-01-03 11:07

AddTime数据分布还算均匀,总的数据量在五千多万

支持(0) 反对(0) 母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 11:11

@母鸡轰: 你查询范围的数据有多少?

支持(0) 反对(0) Daniel Cai | 园豆:10424 (专家六级) | 2018-01-03 11:32

@Daniel Cai: 一百六十万左右

支持(0) 反对(0) 母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 11:37

@母鸡轰: 这种应该是因为你select的字段索引是无法覆盖,而lookup所产生的io开销太大让db认为这里选择索引扫描还不如走全表扫,而你加了order by在这里可能跟加hint有类似效果,sql纠结了半天觉得这样走索引可能会好些。

ps下,你一次性撸这么多数据出来干什么?这么大的结果集返回换什么索引都不好使

支持(0) 反对(0) Daniel Cai | 园豆:10424 (专家六级) | 2018-01-03 11:54

@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' 

支持(0) 反对(0) 母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 11:56

@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' 

支持(0) 反对(0) 母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 11:57

@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没办法跑索引

支持(0) 反对(0) 母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 11:57

@母鸡轰: 如果单纯只是为了使用上那个索引的话你在语句中加hint吧,还有这个里面left join能换成inner join么(满足需求)?

支持(0) 反对(0) Daniel Cai | 园豆:10424 (专家六级) | 2018-01-03 12:01

@Daniel Cai: 时间范围在30万的就用不到索引,只能看看sqlserver的优化策略了

谢谢你的回答了!

支持(0) 反对(0) 母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 13:45

@母鸡轰: 这种场景下你单个字段的索引可能并不太合适了。你试下在ID列再建一个索引,然后include进来AddTime和Money看下情况(不是dba,只是随便猜测)。

支持(0) 反对(0) Daniel Cai | 园豆:10424 (专家六级) | 2018-01-03 18:12

@Daniel Cai: 这种方式不适合我们系统的业务,可能导致一些功能使用异常,目前使用强制索引方式 with(nolock,index(索引名称))

支持(0) 反对(0) 母鸡轰 | 园豆:74 (初学一级) | 2018-01-03 18:27
0

统计信息是否更新?addTime字段的数据类型是什么(smalldatetime和后面datetime做比较就很糟糕)?

CareySon | 园豆:247 (菜鸟二级) | 2018-01-15 11:17
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册