mssql sqlserver 中亿万条数据表如何做到秒查


摘要:
下文讲述优化公司的报表,使用报表从1个小时的运行效率提升到半秒不到就显示出数据的方法分享(非powerBI),如下所示:
实验环境:sql server 2008 R2


该系统是我集团公司的BOSS基础业务管理系统,已运行15年之久,期间经过多次的版本迭代,系统累计了大量的数据。系统数据库中,主营业务记录表的数据都达到了上亿条,里面缓存表都达到了百万级,数据报表的压力可想而知。。。
由于长期无人监管,大家都抱着和稀泥的方式去做事,所以即使无法查询出数据,也无人反馈,可以说公司的IT已经不做事了(因为大家都好多年没涨薪水了。。。)公司的各项信息系统都处于无人监管的状态。最近几年市场竞争激烈,老板才想起来,要搞一下管理,所以才想到了IT,才想到了管理,当然也想到了我们的系统该修一修了。
下文将讲述,我们第一步对数据报表的修复

实践思路:

1.把索引都不补上
索引都补上之后,发现情况还是没有好转,数据的检索速度没有任何改善,
2.对表分区
当我对表 按照日期进行分区后,所有的检索都没有任何改善
3.对sql语句进行分析:
我们发现where条件中采用很多如下的写法:

where 条件中有 like ‘%’+变量+’%’
where 条件中有 case end函数
表连接中存在多次连接同一表的操作
sql返回的数据中存在*

发现了sql脚本中的问题时,此时我们需要做的工作是对sql脚本进行改写:

1.剔除 where 条件中case end函数
对查询条件所对应的表建立临时表,然后将临时表的数据进行汇总
使用临时表可以减少表扫描
2.将in修改为exists ,not in变更为 not exists关键字
3.同使用部门确认,将 where 条件中有 like ‘%’+变量+’%’ 变更为 where 条件中有 like ‘%’+变量+”的可行性
4.在对表的读取时,加入 with(nolock)

通过以上的改写后,sql脚本的运行时间从1小时变更为半秒中。

通过反复的测试和总结,这次效率提升点在于使用了临时表,减少物理表的扫描次数,提升了系统性能