Sql语句如何优化:
1、了解ORACLE解析执行SQL的过程;
2、Oracle SQL语句执行顺序
(8)SELECT (9) DISTINCT (11) <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10)ORDER BY <order_by_list>
- 使用PL/SQL Developer工具中的解释计划(快捷键F5),看是否使用到索引;MySql可使用EXPLAIN命令;
TABLE ACCESS FULL的就是全表扫描的;
INDEX UNIQUE SCAN 的是用到索引的;
但有一种索引要警惕: INDEX FULL SCAN,有可能显示的其他的索引,并不是条件语句中的字段,因此基本相当于全表扫描,应注意此种情况;
还可以通过oracle提供的OEM工具,监控数据库的运行情况,找出SQL运行消耗资源较多的语句。界面如下:
在SQL优化指导中,会给出一些优化的建议,可供优化时参考。
- 解决方案:
4.1优化SQL语句
(1)避免在where条件语句中对索引使用函数,也不要使用计算列;
a.不好的写法:TO_DATE(A.REG_TIME_POINT, 'yyyy-mm-dd hh24:mi:ss') > SYSDATE - 2
当在索引列上进行操作之后,索引将会失效。正确做法应该是将值计算好再传入进来。
优化后的写法:A.REG_TIME_POINT> to_char(SYSDATE - 2, 'yyyy-mm-dd hh24:mi:ss')
b.不好的写法:select id from t where num/2=100
好的写法:select id from t where num=100*2
c.select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:select id from t where name like 'abc%'
(2)减少使用合并字段进行检索;
比如:AND (A.PATIENT_ID || A.VISIT_ID || A.ORDER_NO || TO_CHAR(A.EXEC_SCHEDULE)) IN (....)
(3)使用系统函数运算时的写法:
优化前:AND sysdate - discharge_date_time < 3
优化后: AND discharge_date_time > SYSDATE - 3
(4)尽量不要使用select *,而要指定查询的列;
在应用程序、包和过程中限制使用select * from table这种方式。
(5)关于count
执行效率上:
列名为主键,count(列名)会比count(1)快
列名不为主键,count(1)会比count(列名)快
如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(*)
如果有主键,则 select count(主键)的执行效率是最优的
如果表只有一个字段,则 select count(*)最优。
(6)谨慎使用模糊查询
当模糊匹配以%开头时,该列索引将失效,若不以%开头,该列索引有效。
"select * from people p where p.id like 'parm1%' ";
(7)不要使用列号
select people_name,pepole_age from people order by name,age;
优于
select people_name,pepole_age from people order by 6,8;
(8)优先使用UNION ALL,避免使用UNION
UNION 因为会将各查询子集的记录做比较,故比起UNION ALL ,通常速度都会慢上许多。
(9)使用not exist代替not in
select * from orders where customer_name not exist (select customer_name from customer);
代替
select * from orders where customer_name not in(select customer_name from customer);
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。
(10)exist和in的区别
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。因此,in用到的是外表的索引, exists用到的是内表的索引。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;
select * from A where exists (select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
(11)避免在索引列上做如下操作
◆避免在索引字段上使用<>,!=
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出现数据类型转换(比如某字段是String类型,参数传入时是int类型)
(12)WHERE后面的条件顺序影响
Oracle从下到上处理Where子句中多个查询条件,所以表连接语句应写在其他Where条件前,可以过滤掉最大数量记录的条件必须写在Where子句的末尾
(13)SQL语句脚本均尽量使用大写;ORACLE执行时会先转换成大写字母。
4.2建索引
4.2.1条件语句中使用的关联字段应尽量是索引中的字段;
4.2.2如果条件中的关联字段未使用到索引,应先分析语句中所引用的关联字段,在建立索引时选择用到的索引;
4.2.3如果语句条件左侧使用了函数的,应在建索引时,该字段也应该包含该函数;
4.2.4索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
4.3养成好的编程习惯
4.3.1编写SQL语句时应考虑是否用到了索引;
4.3.2除了校验SQL语句的正确性外,应反复校验SQL语句的执行效率;
4.3.3在编写统计类报表时,多表关联的复杂语句时,要采用分段执行、分段校验的方法进行验证;
彩蛋:Tosska SQL Tuning Expert for Oracle 2.0.3_x64.exe,这个据说可以帮助做自动优化,有兴趣的可以试下,下载地址:
https://tosska.com/TSE%20Files/Tosska%20SQL%20Tuning%20Expert%20for%20Oracle%202.0.3_x64.exe