不得不了解的SQL优化

不得不了解的SQL优化

**本文以oracle为例。**

因为之前工作是以oracle为数仓的，然后目前做政府项目也是以oracle作为数据库的，所以SQL优化方面还是收集了一些。
之前面试时有个面试官问我，如何知道SQL是否优化成功，然后我说因为我用的是plsql，执行时间在工具里都是能看到的，优化后执行时间快就表示优化成功了，从面试官的脸上我看到了我回答的并不是对的，所以我想问一下各位，这个问题该如何回答？

以下几种是自己亲测可以优化的：
（1）SELECT子句中避免使用‘’：
Oracle在解析的过程中, 会将‘’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中，为了满足一个条件,往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例子：
(高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
（3）SQL语句用大写的；因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行
（4）避免在索引列上使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
（5）优化GROUP BY：
提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。
低效: SELECT JOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ 高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP JOB
（6）WHERE子句中的连接顺序：
Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
（7）用Where子句替换HAVING子句：
避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销