sql语句优化

问题查找及措施

问题查找

  1. 需要找到具体的代码,对其进行一对一优化,而非一直把关注点放在服务器和sql平台
  2. 降低简化每个事务中处理的问题,尽量不要让一个事务拖太长的时间

例如文件上传时,应将文件上传这一步放在事务外面

  1. 微软建议

 

 

4.启动sql定时执行计划

怎么启动sqlserver代理服务-百度经验 (baidu.com)

 

 

  1. 启动SQL Server Profiler,获取耗时语句
  2. 部分语句可使用[数据库引擎优化顾问]获取优化意见。(存储过程是不能的)

措施

  • 首先你要知道是否跟sql语句有关,确保不是机器开不开机,服务器硬件配置太差
  • 接着使用sql性能检测工具--sql server profiler,分析出sql慢的相关语句,就是执行时间过长,占用系统资源,cpu过多的
  • 通过下文《sql优化方法跟技巧》,避免一些不合理的sql语句,取暂优sql
  • 再然后判断是否使用了合理的统计信息。sql server中可以自动统计表中的数据分布信息,定时根据数据情况,更新统计信息,是很有必要的
  • 确认表中使用了合理的索引,见下文《索引》
  • 数据太多的表,要分区,缩小查找范围

 

相关资料来源

sql语句的优化分析 - 张龙豪 - 博客园 (cnblogs.com)

sql优化(原理,方法,特点,实例) - -涂涂- - 博客园 (cnblogs.com)

sql优化方法跟技巧

select优化

1.保证不查询多余的列与行。

  • 尽量避免select * 的存在,使用具体的列代替*,避免多余的列
  • 使用where限定具体要查询的数据,避免多余的行
  • 使用top,distinct关键字减少多余重复的行

2.慎用distinct关键字

distinct在查询一个字段或者很少字段的情况下使用,会避免重复数据的出现,给查询带来优化效果。

但是查询字段很多的情况下使用,则会大大降低查询效率

3.慎用union关键字

使用union all能对union进行一定的优化

4.判断表中是否存在数据

禁用*号查数量,查是否存在:select count(*) from product

可以用:select top(1) id from product。查是否存在

  1. 6.       连接查询的优化

减少连接表的数据数量可以提高效率

  1. 少使用like,及时要用可以仅在后面加一个%
  2. 复杂的运算,等取出来到客户端后再处理
  3. 尽量使用多表查询代替子查询
  4. Order by 的列最好是索引列,最好不要是可为空的列。但是注意日期列不适合建索引。
  5. 排序影响性能(测试,如果优化了,写成通用的测C#)
  6. 先进行小表连接再大表
  7. 尽量减少直接的运算
14. 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

Where优化

1.  用EXISTS替代IN、用NOT EXISTS替代NOT IN:

(高效)SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB')

 (低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

 
2.  IS NULL 或IS NOT NULL操作(判断字段是否为空):用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。
3.  Where后面条件的顺序,将可以能更多排除数据减少数据量的条件放在最前面
4.  用EXISTS替换DISTINCT:

(低效): SELECT  DISTINCT  DEPT_NO,DEPT_NAME  FROM  DEPT D , EMP E WHERE  D.DEPT_NO = E.DEPT_NO

(高效): SELECT  DEPT_NO,DEPT_NAME  FROM  DEPT D  WHERE  EXISTS ( SELECT ‘X'  FROM  EMP E  WHERE E.DEPT_NO = D.DEPT_NO);

6.避免在索引列上使用计算 
    WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.举例: 
    低效: 
      SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 
    高效: 
      SELECT … FROM DEPT WHERE SAL > 25000/12;
7.用>=替代> 
 高效: 
      SELECT * FROM  EMP  WHERE  DEPTNO >=4 
    低效: 
      SELECT * FROM EMP WHERE DEPTNO >3 
    两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录

 

Insert、update、delete优化

1.避免循环插入、修改、删除

2.也避免超大批量的修改、删除

书写规范

sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行。

其他

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
2. 对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差
3. count(*)要杜绝
4.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定
5.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log 
6. 尽量避免使用游标操作大量的数据行

索引

措施

1.  解析SQL语句, 估算索引的利用率
2.  查询优化统计信息,由sql自动创建的,也可以自建

Create Statistics的作用 - 吾非无心 - 博客园 (cnblogs.com)

 

未确认项

1.总是使用索引的第一个列:
  如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
2.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的(有待确认)

SQL Server 索引重建脚本 - 每天进步多一点 - 博客园 (cnblogs.com)

注意事项

1.尽量不要对索引列进行计算、类型改变,否则相当于索引无效

2.尽量不要对索引列使用!= ,not 等,否则相当于索引无效

 

优化目标

sqlserver性能调优中的逻辑读,物理读,预读是什么意思 - Jearay - 博客园 (cnblogs.com)

 

 

热门相关:骑士归来   霸皇纪   仗剑高歌   刺客之王   仗剑高歌