1. 怎么优化sql可以减少内存耗量,cpu计算量,IO吞吐、增加并发能力
一、不要使用SELECT *
在SELECT中指定你所需要的列,会 减少内存耗费,IO吞吐。
二、统一sql的写法
其实就是大小写不同,查询分析器就认为是两句不同的SQL语句,必须进行两次解析。生成2个执行计划。所以作为程序员,应该保证相同的查询语句在任何地方都一致,多一个空格都不行!减少cpu计算量。
三、不要把sql语句写的太复杂
越简单的SQL语句被重用的可能性越高。而复杂的SQL语句只要有一个字符发生变化就必须重新解析,然后再把这一大堆垃圾塞在内存里。可想而知,数据库的效率会何等低下。减少cpu计算量。
四、采用临时表暂存中间结果
简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在tempdb中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。
五、过滤条件多的放在后面
如果第一个过滤条件过滤掉很多的数据,第二次比较就减少很多,执行循序根据数据库的执行计划而有所不同。减少了cpu的计算量。
六、使用like查询时请注意
关键词%yue%,由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%,减少cpu的计算量,提高性能
七、使用索引
索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。如果只是查询的表而不是经常修改的,建议用索引,以内存为代价,减少cpu计算量
八、只有在必须的情况下才使用begin tran
有些情况下,我们需要显式声明begin tran,比如做“插、删、改”操作需要同时修改几个表,要求要么几个表都修改成功,要么都不成功。begin tran 可以起到这样的作用,它可以把若干SQL语句套在一起执行,最后再一起commit。好处是保证了数据的一致性,但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前,所有SQL语句锁住的资源都不能释放,直到commit掉。减少内存耗量,增加并发能力
九、不使用not
我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子: ... where not (status ='VALID')
对这个查询,可以改写为不使用NOT: ... where status <>'INVALID'; 减少cpu计算
十、用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销,减少cpu运算。
2、从表设计方面减少内存耗量,cpu计算量,IO吞吐、增加并发能力
一、树表设计:
树状表都是使用ID和IDParent两个字段来表示树关系。对树进行查找只能使用自关联方式,不光写法麻烦而且记录多的时候查询性能会非常差。建议在设计树表的时候可以考虑加入treePath字段,记载到该节点记录需要经历的树路径。虽然会增加Insert和Update的成本。但是对查询树关系非常有帮助。可以避免大部分的自关联查询。 树状表都是使用ID和IDParent两个字段来表示树关系。对树进行查找只能使用自关联方式,不光写法麻烦而且记录多的时候查询性能会非常差。建议在设计树表的时候可以考虑加入treePath字段,记载到该节点记录需要经历的树路径。虽然会增加Insert和Update的成本。但是对查询树关系非常有帮助。可以避免大部分的自关联查询。减少cpu计算,增加硬盘占用。
二、分区表:
记录超过一百万的表要考虑是否需要使用分区表。对于能够明确确定分区字段,并且经常通过分区访问记录的。分区表会提高查询性能。减少IO吞吐,减少cpu计算,内存耗量,增加硬盘占用。
三、冗余字段:
一些关系查询只查询类似Code、Name等很少的字段。可以考虑将频繁需要关联查询的这种字段冗余到主表中。这种表设计会要求同步更新两个字表,对于大部分查询,会减少表的关联,提高查询性能。对于自定义项引用自由项的需求,使用这种冗余设计能保证报表查询的便利,能避免联查表提高了查询效率。减少IO吞吐、减少cpu计算,增加并发能力。