【SQL语句优化的几种方式】

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>

1. 使用PL/SQL Developer工具中的解释计划（快捷键F5），看是否使用到索引；MySql可使用EXPLAIN命令；

 

 TABLE ACCESS FULL的就是全表扫描的；

INDEX UNIQUE SCAN 的是用到索引的；

但有一种索引要警惕： INDEX FULL SCAN,有可能显示的其他的索引，并不是条件语句中的字段，因此基本相当于全表扫描，应注意此种情况；

还可以通过oracle提供的OEM工具，监控数据库的运行情况，找出SQL运行消耗资源较多的语句。界面如下：

在SQL优化指导中，会给出一些优化的建议，可供优化时参考。

1. 解决方案：

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