sql语句优化
问题查找及措施
问题查找
- 需要找到具体的代码,对其进行一对一优化,而非一直把关注点放在服务器和sql平台
- 降低简化每个事务中处理的问题,尽量不要让一个事务拖太长的时间
例如文件上传时,应将文件上传这一步放在事务外面
- 微软建议
4.启动sql定时执行计划
怎么启动sqlserver代理服务-百度经验 (baidu.com)
- 启动SQL Server Profiler,获取耗时语句
- 部分语句可使用[数据库引擎优化顾问]获取优化意见。(存储过程是不能的)
措施
- 首先你要知道是否跟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。查是否存在
- 6. 连接查询的优化
减少连接表的数据数量可以提高效率
- 少使用like,及时要用可以仅在后面加一个%
- 复杂的运算,等取出来到客户端后再处理
- 尽量使用多表查询代替子查询
- Order by 的列最好是索引列,最好不要是可为空的列。但是注意日期列不适合建索引。
- 排序影响性能(测试,如果优化了,写成通用的测C#)
- 先进行小表连接再大表
- 尽量减少直接的运算
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)