SQL server 系统优化--通过执行计划优化索引(3)
刚刚在客户优化监控系统中,通过DMVs发现一条(通过HQL语句生成)sql语句,执行时间要7205ms,语句如下:
select top 30 this_.id as id10_0_, this_.objid as objid10_0_, this_.objname
as objname10_0_, this_.mid as mid10_0_, this_.logtype as logtype10_0_, this_.logdesc as logdesc10_0_, this_.submitor as submitor10_0_, this_.submitdate as submitdate10_0_,
this_.submittime as submittime10_0_, this_.submitip as submitip10_0_, this_.col1 as col11_10_0_,this_.col2 as col12_10_0_, this_.col3 as col13_10_0_ from log this_ where not exists (select 'X' from Delobj del where del.objid=this_.id and del.objtable='Log') order by this_.submitdate desc, this_.submittime desc
as objname10_0_, this_.mid as mid10_0_, this_.logtype as logtype10_0_, this_.logdesc as logdesc10_0_, this_.submitor as submitor10_0_, this_.submitdate as submitdate10_0_,
this_.submittime as submittime10_0_, this_.submitip as submitip10_0_, this_.col1 as col11_10_0_,this_.col2 as col12_10_0_, this_.col3 as col13_10_0_ from log this_ where not exists (select 'X' from Delobj del where del.objid=this_.id and del.objtable='Log') order by this_.submitdate desc, this_.submittime desc
执行sql语句的执行计划:
分析:这里发现整个sql语句最占用资源的地方就是排序了,占了90%,而且表log中大约有超过46万行数据。排序的成本很高,我们发现是通过:
[dbo].[log].submitdate 降序, [dbo].[log].submittime 降序
排序是在submitdate和submittime的排序。由于我们在log表中列id建立了聚集索引,这时由于排序是用到了时间列,所以成本很高,这时我们需要重新重构聚集索引。
是必须在(submitdate,submittime)建立聚集索引,由于前期在设计表时的不合理,造成了时间字段教长,有18个字节,大大超过了8个字节的datetime类型,不太适合建立聚集索引。
这时为保证查询性能,在submitdate字段建立聚集索引,而不是建立(submitdate,submittime)复合聚集索引,适当提高速度。
总结:
1,字段类型和长短的是很重要,不然在后续修改和优化是很困难。
2,对与聚集索引选择一般在需要排序,进行范围选择和group by等字段上建立
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· AI技术革命,工作效率10个最佳AI工具