SQL优化的一些实用方法

墨蓝 2022-05-28 11:05 264阅读 0赞

1、任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。因为只有该字段中有null值，即使创建了索引其实也  
是没有用的，所以创建索引应该在有值的字段上创建;

2、使用该sql语句将不会使用索引：select \* from employee where last\_name like ‘%cliton%';  
   这样的话会使用索引:     select \* from employee where last\_name like ‘cliton%';

3、Order by语句  
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中(象联接或  
者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们  
会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by  
子句中使用表达式。

4、 选择最有效率的表名顺序(只在基于规则的优化器中有效)：   
ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句  
中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection  
table)作为基础表, 交叉表是指那个被其他表所引用的表.

5、 SELECT子句中避免使用 ‘ \* ‘：   
ORACLE在解析的过程中, 会将’\*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

6、 减少访问数据库的次数，能使用一条sql语句查询出来的最好使用一条语句直接查询出来。  整合简单,无关联的数据库访问：   
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

7、通过内部函数提高SQL效率.： 复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的

8、 使用表的别名(Alias)：   
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起  
的语法错误.

9、 用EXISTS替代IN、用NOT EXISTS替代NOT IN：   
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的  
效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个  
全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.   
例：  
 (低效)select \* from z\_shangbiao u where u.ann\_nnum!='' and u.ann\_nnum  in( select e.user\_account FROM  t\_evaluation e)  
（高效）select \* from t\_user u where u.account!='' and EXISTS( select e.user\_account FROM  t\_evaluation e where  
u.account=e.user\_account)

10、 用索引提高效率： 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每  
当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁  
盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：

11、用EXISTS替换DISTINCT：   
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查  
询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：   
(低效):   
SELECT  DISTINCT  DEPT\_NO,DEPT\_NAME  FROM  DEPT D , EMP E WHERE  D.DEPT\_NO = E.DEPT\_NO   
(高效):   
SELECT  DEPT\_NO,DEPT\_NAME  FROM  DEPT D  WHERE  EXISTS ( SELECT ‘X’ FROM  EMP E  WHERE E.DEPT\_NO = D.DEPT\_NO);

12、sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

13、 避免在索引列上使用计算．   
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．   
举例:   
低效：   
SELECT … FROM  DEPT  WHERE SAL \* 12 > 25000;   
高效:   
SELECT … FROM DEPT WHERE SAL > 25000/12;

14、用>=替代>   
高效:   
SELECT \* FROM  EMP  WHERE  DEPTNO >=4   
低效:   
SELECT \* FROM EMP WHERE DEPTNO >3

15、 用UNION替换OR (适用于索引列)   
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效.  
如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC\_ID 和REGION上都建有索引.   
高效:   
SELECT LOC\_ID , LOC\_DESC , REGION   
FROM LOCATION   
WHERE LOC\_ID = 10   
UNION   
SELECT LOC\_ID , LOC\_DESC , REGION   
FROM LOCATION   
WHERE REGION = “MELBOURNE”   
低效:   
SELECT LOC\_ID , LOC\_DESC , REGION   
FROM LOCATION   
WHERE LOC\_ID = 10 OR REGION = “MELBOURNE”   
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

16、 总是使用索引的第一个列：   
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重  
要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引   
例：比如你创建了索引（account,age,classno）三个字段为索引，那么你的sql语句使用where的时候应该先使用account，此时才能使用索  
引，如果你的where的第一个条件使用的是age或者classno那么将进行全表搜索，而忽略了索引。

17、用UNION-ALL 替换UNION ( 如果有可能的话)：   
当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION  
ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各  
位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT\_AREA\_SIZE这块内存. 对于这块内存的  
优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量   
低效：   
SELECT  ACCT\_NUM, BALANCE\_AMT   
FROM  DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = ’31-DEC-95′   
UNION   
SELECT ACCT\_NUM, BALANCE\_AMT   
FROM DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = ’31-DEC-95′   
高效:   
SELECT ACCT\_NUM, BALANCE\_AMT   
FROM DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = ’31-DEC-95′   
UNION ALL   
SELECT ACCT\_NUM, BALANCE\_AMT   
FROM DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = ’31-DEC-95′

18、避免改变索引列的类型.  
比如age字段为int型，然后你查询的时候写的是 where age='13'，虽然查询出来的结果是一样的但是效率会降低，因为数据库内部多做了个  
操作，自动对字符123 进行TO\_NUMBER(‘123′) 处理

19、 需要当心的WHERE子句:   
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.   
在下面的例子里, (1)‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘||’是字符  
连接函数. 就象其他函数那样, 停用了索引. (3) ‘+’是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,  
这将会启用全表扫描.

20、 优化GROUP BY,尽量使用where代替having:   
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.   
低效:   
SELECT JOB , AVG(SAL)   
FROM EMP   
GROUP by JOB   
HAVING JOB = ‘PRESIDENT’   
OR JOB = ‘MANAGER’   
高效:   
SELECT JOB , AVG(SAL)   
FROM EMP   
WHERE JOB = ‘PRESIDENT’   
OR JOB = ‘MANAGER’   
GROUP by JOB

21、不要使用SELECT \*   
这点不太容易做到。可是，如果在SELECT中指定你所需要的列，那将会带来以下的好处：   
1 减少内存耗费和网络的带宽   
2 你可以得到更安全的设计   
3 给查询优化器机会从索引读取所有需要的列

本文章阅读参考：http://www.jfox.info/SQL-you-hua.html 然后有加一些自己的理解。如有不正确的地方请多加指正(\*￣︶￣)

博客原文地址：https://my.oschina.net/zrz1992/blog/1789208