文章内容
2017/9/22 22:51:41,作 者: 黄兵
sql优化实战:排序字段——到底是time还是ID
查到有这样一段话,很耗CPU资源:
set statistics io on
set statistics time on
SELECT TOP 10 FeedBackID,UserID,ContentID,[Content],
Time,AddType,IP FROM CYZoneFeedBack
where contentid in (select articleid from cyzonearticle where userid=@user and delflag=0 and publishtype<>'b')
and CYZoneFeedBack.DelFlag =0 order by CYZoneFeedBack.Time desc
分析是这样的:
原来是排序造成了这么多开销。罪魁祸首在于 order by CYZoneFeedBack.Time 这句话,后改成:
set statistics io on
set statistics time on
SELECT TOP 10 FeedBackID,UserID,ContentID,[Content],
Time,AddType,IP FROM CYZoneFeedBack
where contentid in (select articleid from cyzonearticle where userid=107and delflag=0 and publishtype<>'b')
and CYZoneFeedBack.DelFlag =0 order by CYZoneFeedBack.feedbackid desc
set statistics time on
SELECT TOP 10 FeedBackID,UserID,ContentID,[Content],
Time,AddType,IP FROM CYZoneFeedBack
where contentid in (select articleid from cyzonearticle where userid=107and delflag=0 and publishtype<>'b')
and CYZoneFeedBack.DelFlag =0 order by CYZoneFeedBack.feedbackid desc
执行计划变为:
很明显省掉了排序的操作。有时候,排序和时间是有相关性的,而聚集索引,没有建在时间上,会导致排序成本的增加,恰当的利用自增ID来做时间排序,也能省掉很多开销。
评论列表