oracle中sql优化
问题描述:刚开始做项目的时候没啥感觉,只用能出来结果,sql随便写,但是后来用户的数据量达到几万条是,在访问系统,发现很多功能加载都很慢,有的页面一个简单的关联
查询居然要花费30多秒,实在是不能忍,所以自己研究了一下sql优化技巧,特此记录一下:
1. exists和in的使用
参考文档:https://www.cnblogs.com/liyasong/p/sql_in_exists.html
(1) exists是用循环(loop)的方式,由outer表的记录数决定循环的次数,所以,外表的记录数少,适合用exists;
用法:EXISTS后面跟一个子查询,当该查询可以查询出至少一条记录时返回真(not为不存在)
SELECT * FROM t_f_hz t1 WHERE EXISTS (SELECT f_hz_id FROM T_F_T_POSSIBLELIST t2 WHERE f_hz_id = t1.pk_id and f_hz_id = 1);
(2) in先执行子查询,子查询的返回结果去重之后,在执行主查询,所以,子查询的返回结果越少,越适合用该方式。
SELECT * FROM prj WHERE f_name IN (SELECT f_name FROM POSSIBLELIST WHERE f_hz_id = '1531104');
这个查询语句中,当表中possiblelist查询出来的数据较少时,使用in,此时会将possiblelist表中查询出来的数据数量n(假设)
和prj表中的数据m(假设)做一个笛卡尔积,共(m*n)条数据,然后在查找出符合条件的数据。
如果prj中数据较少时,应该使用exists,此时会先查询主表prj,然后用表中的每一条数据依次去判断where后面的条件是否成立,
如果成立则返回true不成立则返回false。如果返回的是true的话,则该行结果保留,如果返回的是false的话,则删除该行,最后将得到的结果返回。
exists (SELECT f_name FROM POSSIBLELIST WHERE f_hz_id = '1531104');
总结:如果两个表一个较大,一个较小,则子查询大的用exists,子查询小的用in。
当只显示一个表的数据(只显示prj表的数据),关系条件只有一个where f_name in ()时用in比较合适。
当只显示一个表的数据,关系条件不止一个,此时使用in就不方便了,可以使用exists。
SELECT * FROM t1 WHERE EXISTS (SELECT fk_id FROM t2 WHERE f_hz_id = t1.f_hz_id AND f_pac_id = t1.f_pac_id)
(3)not in 和not exists:
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
2. is null和is not null优化
where子句中的is null和is not null将不会使用索引而是进行全表搜索,因此需要通过改变查询方式,分情况讨论等办法,去掉
where子句中的is null和is not null改为 A >0 或者 A> ' '。
select * from t1 where fk_pid is null --优化如下:将为空的字段转为不为空的值(确保数据中是不会出现1) select * from t1 where nvl(fk_pid,'1') = '1' select * from t1 where fk_pid is null --优化如下: select * from t1 where fk_pid = nvl(fk_pid,'1')
3. 多张表关联查询优化
参考文档:https://www.cnblogs.com/liuyitian/p/5249597.html
https://blog.csdn.net/tanga842428/article/details/52265991
(1) 多张表关联查询中,将数据量小的表写在from的最右边。
原因:oracle在解析sql语句的时候对from子句后面的表名是从右往左解析的,是先扫描最右边的表,
然后在扫描左边的表,然后用左边的表匹配数据,匹配成功后就合并。在RBO优化器模式下,表应按结果记录数从大到小的顺序从左到右来排列,
因为表间连接时,最右边的表会被放到嵌套循环的最外层。最外层的循环次数越少,效率越高。
如果有3个以上的表连接查询,那就需要将交叉表(中间表)放到最右边。
-- 6484500条 282ms select count(*) from t1, t2; --6484500 400ms select count(*) from t2,t1; --64845 60ms select count(*) from t1; --100 55ms select count(*) from t2;
(2) where子句连接顺序
oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,
那些可以过滤掉最大数量记录的条件必须写在where子句的末尾。但是网上有很多中说法,有的说是过滤
最大数据量的条件放在最右边,也有的说是放在最左边。但是根据自己的测试结果,感觉放在最左边和最右边对性能影响不是很大。
3. 模糊查询like优化
oracle内部对instr进行了优化处理。
select * from table_hz hz where instr(hz.f_name,#{f_name,jdbcType=VARCHAR})>0
4.distinct优化
distinct在查询中经常用来去重,