SQL优化
程序员文章站
2022-04-19 22:14:09
写作背景 写作背景 最近项目上碰到一些存储过程执行十几个小时的，影响到了系统和数据库性能，于是决定分析一下，优化后再项目组内强调，java开发中封装的SQL，数据库开发时的SQL注意事项，通俗易懂，仅供参考。 分析SQL性能差异，通过查看执行计划（通过PLSQL下F5）看耗费点和耗费情况，示例图如下 ......
- 写作背景
最近项目上碰到一些存储过程执行十几个小时的，影响到了系统和数据库性能，于是决定分析一下，优化后再项目组内强调，java开发中封装的SQL，数据库开发时的SQL注意事项，通俗易懂，仅供参考。
- 分析SQL性能差异，通过查看执行计划（通过PLSQL下F5）看耗费点和耗费情况，示例图如下。
- 找到具体耗费点后针对耗费点进行优化，优化的方法常用的有以下几种：
1、SQL书写优化，这个是常用的优化方式，具体注意事项见下方“SQL书写注意事项”
SQL的执行顺序：FROM、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、UNION、ORDER BY，而WHERE子句中执行是从后往前，索引比较精确的条件往后放。
2、根据具体情况创建主键、索引、唯一索引或联合索引等。
3、对于数据量比较大，数据变化比较大的业务表，表分析也可以尝试，参考如下SQL（必须在命令窗口执行）
exec dbms_stats.gather_table_stats(OWNNAME=> '用户', TABNAME=> '表名', ESTIMATE_PERCENT=>30, CASCADE=>true, DEGREE=>16,GRANULARITY=>'all');
4、使用强制索引hint
SELECT /*+ index(a IDX_DEAL_STATE) */ * FROM 表名 A WHERE 条件 order by 字段
- SQL书写注意事项：
1、选择最优效的表名顺序（只在基于规则优的化器）：大表在前，小表在后。
2、where子句的连接顺序：可以过滤掉更大数量记录的条件放在最后。
3、select子句避免使用“*”，因为Oracle解析过程中会将“*”一次转换成所有的列名，这部分属于额外消耗。
4、在SQL*Plus、SQL*Forms和Pro*C中重新设置ARRAYSIZE参数，可以增加每次数据库访问的检索数量，建议值为200.
6、使用Decode函数来减少处理时间
7、整合简单、无关的数据库访问（尽量整合到一个查询中）
8、删除重复记录（使用rowid等）
9、使用Truncate代替Delete，不再处理回滚段信息，减少额外消耗。
10、尽量多使用commit，释放更多资源
11、用where子句替换Having子句
12、减少对表的查询（特别是含有子查询的SQL中）
13、通过内部函数提高SQL效率
14、使用表的别名（Alias），在每个Column前缀增加别名，能够减少解析识别和Column歧义
15、用Exists替代IN，用NOT Exists替代NOT IN（可以用外连接Outer Joins替换）
16、识别‘低效’执行的SQL语句（大SQL）
17、用索引提高效率
18、用Exists替换Distinct
19、SQL语句用大写的，因为Oracle总是先解析sql语句，把小写的字母转换成大写的再执行。
20、在拼接的SQL串中尽量少用连接符，例如“+”
21、避免在索引列上使用NOT
22、避免在索引上使用计算，因为会导致优化器不再使用索引而导致全表扫描，例如SAL*12>25000调整为SAL>25000/12
23、用>=替代>
24、用Union替代OR（适用于索引列），会导致全表扫描，如果非要用OR，那就需要返回记录最少的索引列写在最前面
25、用IN替代OR
26、避免在索引列上使用IS NULL和IS NOT NNULL，会导致无法使用该索引。
27、总是使用索引的第一个列（针对联合索引）
28、用UNION ALL替换UNION（如果可能的话，UNION还有去除重复数据的逻辑）
3、select子句避免使用“*”，因为Oracle解析过程中会将“*”一次转换成所有的列名，这部分属于额外消耗。
4、在SQL*Plus、SQL*Forms和Pro*C中重新设置ARRAYSIZE参数，可以增加每次数据库访问的检索数量，建议值为200.
6、使用Decode函数来减少处理时间
7、整合简单、无关的数据库访问（尽量整合到一个查询中）
8、删除重复记录（使用rowid等）
9、使用Truncate代替Delete，不再处理回滚段信息，减少额外消耗。
10、尽量多使用commit，释放更多资源
11、用where子句替换Having子句
12、减少对表的查询（特别是含有子查询的SQL中）
13、通过内部函数提高SQL效率
14、使用表的别名（Alias），在每个Column前缀增加别名，能够减少解析识别和Column歧义
15、用Exists替代IN，用NOT Exists替代NOT IN（可以用外连接Outer Joins替换）
16、识别‘低效’执行的SQL语句（大SQL）
17、用索引提高效率
18、用Exists替换Distinct
19、SQL语句用大写的，因为Oracle总是先解析sql语句，把小写的字母转换成大写的再执行。
20、在拼接的SQL串中尽量少用连接符，例如“+”
21、避免在索引列上使用NOT
22、避免在索引上使用计算，因为会导致优化器不再使用索引而导致全表扫描，例如SAL*12>25000调整为SAL>25000/12
23、用>=替代>
24、用Union替代OR（适用于索引列），会导致全表扫描，如果非要用OR，那就需要返回记录最少的索引列写在最前面
25、用IN替代OR
26、避免在索引列上使用IS NULL和IS NOT NNULL，会导致无法使用该索引。
27、总是使用索引的第一个列（针对联合索引）
28、用UNION ALL替换UNION（如果可能的话，UNION还有去除重复数据的逻辑）
UNION和UNION ALL的区别：
1）对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
2）对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
3）从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。
29、用Where替代Order BY
30、避免改变索引列的类型，例如Empno='123',写成empno=to_number('123')
31、需要当心的where子句
1）'!='不等于，不使用索引
2）'+'加，不使用索引
3）'||'连接函数，不使用索引
4）相同的索引列不能互相比较，将会启用全表扫描
32、如果检索数据量超过30%的表中记录数，使用索引将没有明显效果。
33、避免使用耗费资源的操作，例如Distinct、UNION、Minus、InterSect、Order By等SQL语句会启动SQL引擎。
34、优化Group by，可以通过将不需要的记录在Group by之前过滤掉。
35、select count(*)和select count(1)区别
1)没有主键用count(1)
2)有主键用count(主键)
3)如果表只有一个字段，用count(*) 最快
4)count(1)/count(*)都包括对NULL的统计，count(列)不包括NULL的统计。
5)select 1比select * 好，此时用sum(1)
36、Is Null与IS NOT NULL 都不允许使用索引
37、合理使用通配符，例如 like "%"，第一个字符尽量不用通配符，否则不能走索引。
38、Order by语句，任何在order by语句的非索引项或者有计算表达式都将降低查询速度。
39、Not两种写法：
where not (ststus='VALID')
where ststus<>'VALID'
30、避免改变索引列的类型，例如Empno='123',写成empno=to_number('123')
31、需要当心的where子句
1）'!='不等于，不使用索引
2）'+'加，不使用索引
3）'||'连接函数，不使用索引
4）相同的索引列不能互相比较，将会启用全表扫描
32、如果检索数据量超过30%的表中记录数，使用索引将没有明显效果。
33、避免使用耗费资源的操作，例如Distinct、UNION、Minus、InterSect、Order By等SQL语句会启动SQL引擎。
34、优化Group by，可以通过将不需要的记录在Group by之前过滤掉。
35、select count(*)和select count(1)区别
1)没有主键用count(1)
2)有主键用count(主键)
3)如果表只有一个字段，用count(*) 最快
4)count(1)/count(*)都包括对NULL的统计，count(列)不包括NULL的统计。
5)select 1比select * 好，此时用sum(1)
36、Is Null与IS NOT NULL 都不允许使用索引
37、合理使用通配符，例如 like "%"，第一个字符尽量不用通配符，否则不能走索引。
38、Order by语句，任何在order by语句的非索引项或者有计算表达式都将降低查询速度。
39、Not两种写法：
where not (ststus='VALID')
where ststus<>'VALID'