关于SQL优化（面试重点）

SQL优化

1、为什么要SQL优化？

随着软件技术的不断发展，系统性能越来越重要。

造成SQL语句性能不佳大概有两个原因：

1、开发人员只关注查询结果的正确性，而忽视了查询语句的效率。

2、开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。

2、什么叫好的SQL语句？

• 尽量简单化，模块化
• 易读，易维护
• 节省资源：比如（CPU、内存、避免全盘扫描、少排序）
• 不造成死锁

3、实现SQL优化有哪些方法吗？

3.1 避免复杂的多表联查

3.2 避免使用"*"

当你想要检索表中所有的列时,SELECT * FROM TABLE;确实是一个非常方便的方法，但是它的执行效率非常低，实际上，ORACLE在解析的过程中，需要通过查询数据字典，将“*”依次转换成所有的列名，这一步操作呢意味着会消耗更多的时间。

3.3 避免使用耗费资源的操作

带有 DISTINCT、 UNION、 MINUS、 INTERSECT、 ORDER BY的SQL语句会启用SQL引擎执行耗费资源的排序功能，以上  DISTINCT 需要一次排序操作，而其他的至少需要执行两次排序。
通常，带有 UNION、 MINUS、 INTERSECT、 ORDER BY的SQL语句都可以用其他方法重写。

3.4 用 EXISTS 替换 DISTINCT

例如：
低效率：
SELECT DISTINCT DEPTNO,DNAME FROM DEPT D,EMP E WHERE D.DEPTNO = E.DEPTNO;
高效率：
SELECT DEPTNO,DNAME FROM DEPT D WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPTNO = D.DEPTNO);

3.5 用 UNION ALL 替换 UNION

档SQL需要UNION俩个查询结果集时。这俩个结果集会以UNION ALL 的方式被合并，然后再输出最终结果前进行排序。

3.6 创建索引，根据索引查询

举个例子：
假设我的表中有10条数据，我要查询id为8的数据， SELECT * FROM TABLE WHERE ID = 8;
再没索引的情况下，以上的SQL语句最坏的结果可能会从头遍历一次查询8次。
而索引呢，是B-Tree 结构，以二分查法去查询数据。当我对我的id列创建索引后，如下图：
先判读 8 属于 1-5 的范畴，还是 6-10 的范畴，

也就是说，当我为id创建索引后，那么会先判断 ID = 8 属于 1-5 还是 6-10 ，进入 6-10 后，再判断输入 6-7还是 8-9 还是 10，进入 8-9，则拿到 id=8的数据了。这样它的执行效率远比普通列要快。

3.7 避免再索引列上进行计算

where子句中，如果索引列是函数的一部分。优化器将不会使用索引，而使用全表扫描。

3.8 避免再索引列上使用NOT

3.9 避免使用前置通配符

SELECT * FROM WHERE ENAME LIKE '%R';
这种情况下，ORACLE将使用全盘扫描。

3.10 避免再索引列上使用 IS NULL 和 IS NOT NULL

因为空值不存在索引列中，所以 WHERE子句中对索引列进行空值比较将使 ORACLE停用该索引

3.11 减少访问数据库的次数

3.12 使用DECODE来减少处理时间

3.13 用WHERE替换HAVING子句

sql语句的执行顺序：
from --> 
where -->
group by -->
having -->
order by

3.14 用NOT EXISTS替代 NOT IN

在子查询中，NOT IN 子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。使用NOT EXISTS子句可以有效地利用索引。尽可能使用NOT EXISTS来替代NOT IN，尽管二者都使用了NOT （不能使用索引而降低速度），NOT EXISTS要比NOT IN 查询效率跟高。
例1：
	select dname,deptno from dept where deptno not in(SELECT deptno from emp);
例2：
	select dname,deptno from dept where not exists(select deptno from emp where dept.deptno = emp.deptno);

如上：
	例2要比例1的执行性能好很多。
	因为1中对emp进行了全表扫描，这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。

3.15 用>=替代>

例如：
	select * from emp where deptno >= 4;
	和
	select * from emp where deptno > 3;
以上第一条的效率要比第二条的效率高，因为第一条在执行的时候是直接锁定 4 的，然后再往后查询，而第二条需要先排查前3条，再往后查询。

3.16 尽量多使用commit

事务是消耗资源的，大事务还容易引起死锁。

3.17 用 TRUNCATE替代DELETE

当使用 DELETE 的时候，会产生事务。而 TRUNCATE 则不会。

3.18 关于COUNT()

COUNT()函数的如下，依次降低：
COUNT(索引/主键)
COUNT(*)
COUNT(1)