闲谈SQL脚本优化

摘要

闲来无事,便想寻找大师级别优化SQL脚本案例,也算是读后留点笔记,摘录内容都会链接到所参考网址,如有冒犯,还望博主见谅;有些文章只有多动手多动脑才能理解其中的意思,看了需要实际操作,才不枉大师们的良苦用心;

一、正确的建立索引 [摘录:我是如何在SQLServer中处理每天四亿三千万记录的]

首先,我们需要明白几个索引的要点:

  • 索引之后,按索引字段重复最少的来排序,会达到最优的效果。以我们的表来说,如果建立了No的聚集索引,把No放在where子句的第一位是最佳的,其次是Id,然后是MgrObjId,最后是时间,时间索引如果表是一个小时的,最好不要用
  • where子句的顺序决定了查询分析器是否使用索引来查询。比如建立了MgrObjId和Id的索引,那么where MgrObjId='' and Id='' and Dtime=''就会采用索引查找,而where Dtime='' and MgrObjId='' and Id=''则不一定会采用索引查找。
  • 把非索引列的结果列放在包含列中。因为我们条件是MgrObjId和Id以及Dtime,因此返回结果中只需包含Dtime和Value即可,因此把Dtime和Value放在包含列中,返回的索引结果就有这个值,不用再查物理表,可以达到最优的速度。

如何在SQLServer中处理亿万级别的数据(历史数据),可以按以下方面进行:

  • 去掉表的所有索引
  • 用SqlBulkCopy进行插入
  • 分表或者分区,减少每个表的数据总量
  • 在某个表完全写完之后再建立索引
  • 正确的指定索引字段
  • 把需要用到的字段放到包含索引中(在返回的索引中就包含了一切)
  • 查询的时候只返回所需的字段
二、数据查询优化[摘录:记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题]
  • 在SQLServer中,ROW_NUMBER的分页应该是最高效的了,而且兼容SQLServer2005以后的数据库
  • 通过“欺骗”查询引擎的小技巧,可以控制查询引擎部分的优化过程
  • ROW_NUMBER分页在大页数时存在性能问题,可以通过一些小技巧进行规避

    • 尽量通过cte利用索引
    • 把不参与where条件的表放到分页的cte外面
    • 如果参与where条件的表过多,可以考虑把不参与分页的表先做一个临时表,减少IO
    • 在较大页数的时候强制使用hash join可以减少io,从而获得很好的性能
  • 使用with(forceseek)可以强制查询因此进行索引查询

上一篇:bzoj 2653: middle (主席树+二分)


下一篇:Xshell中文乱码怎么处理?