SQL的优化（持续更新）——Oracle数据库学习笔记

SQL的优化

1. 使用绑定变量来替代多次重复操作
2. 避免使用隐式转换
3. 驱动表较小使用in，驱动表较大使用exists

select num from user wher num in (select num from b);
# 替换为
select num from user where exists (select id from class where num=user.num);

4. 尽量使用数字型字段，只含数值信息的字段尽量不要设计为字符型
5. 尽可能使用varchar/nvarchar代理char/nchar
6. 不要用select * from table，要查询具体字段
7. 尽量使用表变量代替临时表
8. 避免频繁创建和删除临时表
9. 临时表适当使用，如需要重复引用大型表或表中某个数据集。对于一次性事件，最好使用导出表
10. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert
11. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定
12. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
13. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效
14. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息
15. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
16. 尽量避免大事务操作，提高系统并发能力

---

导致引擎放弃使用索引进行全表扫描的情况

• 查询时尽量避免全表扫描，首先考虑在where和order by涉及的列上建立索引

• 在where子句中使用!=或<>操作符，也会导致

• 在where子句中对字段进行null值判断，也会导致

select id from user where num is null;
# 可以在num上将null设置为0，再进行如下查询
select id from user where num=0;

• 在where子句中使用or来连接条件

select id from user wher num=10 or num=20;
# 可以改为以下这种
select id from user where num=10
union all
select id from user where num=20;

• 使用like时不建议搜索词前加“%”
• in和not也要慎用，如：

select id from user where num in (1,2,3);
# 对于连续的数值，能用between就不要用in了
select id from user where num between 1 and 3;

• 在where子句中使用参数时，最好指定索引名

select id from user where num=@num;
# 改为
select id from user with(index(索引名)) where num=@num;

• where条件左边为列名，右边为值，不建议在列名上添加转化，建议在值上添加转化
  以下写法不推荐

select id from user where num/2=100;
select if from user where substring(name,1,3)='abc';
select if from user where datediff(day,createdate,'2020-11-11')=0'
# 应改为
select id from user where num=100*2;
select id from user where name like 'abc%';
select id from user where createdate>='2020-11-11' and createdate<'2020-11-12';

---