IT评测·应用市场-qidao123.com

标题: sql语句优化 [打印本页]

作者: 络腮胡菲菲 时间: 2023-4-19 14:12
标题: 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)

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)