SQL优化

秒速五厘米 2022-04-24 15:46 199阅读 0赞

#                                 SQL优化 #

## 1、SQL语句尽量用大写 ##

因为oracle解析SQL语句时，总是先把小写的字母转换成大写的再执行。

##  2、使用表的别名 ##

当在SQL语句中连接多个表时，尽量使用表的别名并把别名缀于每个列上，这样一来，就可以减少解析时间并减少由列歧义引起的语法错误。

## 3、选择最有效的表名顺序 ##

oracle的解析器按照从右到左的顺序处理from子句中的表，from子句中写在最后的表将最先处理，在from子句中包含多个表的情况下，必须选择记录数最少的表放在最后。

如果有3个以上的表连接查询，那就需要选择交叉表作为基础表，交叉表是指被其他表引用的表。

--No.1  tableA 100w条记录  tableB 1w条记录 执行速度 十秒级

SELECT COUNT(\*) FROM tableA,tableB;

--No.2  执行速度百秒级甚至更高

SELECT COUNT(\*) FROM tableB,tableA;

上面的结果肯定是No.2比No.1效率更高。

SELSET COUNT(**1**) FROM tableA a,tableB b ,tableC c WHERE a.id=b.id AND a.id=c.i；

上面的sql中tableA 就称为交叉表，根据oracle对From子句从右向左的扫描方式，应该把交叉表放在最末尾，然后才是最小表，所以上面的应该这样写

\--tableA a 交叉表

\--tabelB b 100w

\--tableC c 1w

SELECT COUNT(1) FROM tableB b ,tableC c ,tableA a WHERE a.id=b.id AND a.id=c.id;

这种写法对大数据量会非常有用，大家谨记，也是很常用的。

## 4、where子句后面的条件过滤 ##

oracle对where子句后面的条件过滤是自下而上，从右到左扫描的，所以和from子句一样一样的，把过滤条件排个序，按过滤数据的大小，自然就是 可**以过滤掉最大数据记录的条件必须写在where子句的末尾最下面，最右边**，以此类推。

## 5、在select的时候少用\* ##

因为oracle的查询器会把\*转换为表的全部列名，这个会浪费时间，所以在大表中少用。

## 6、使用rowid，rownum ##

常用语分页，删除查询重复记录，非常实用。

--查找重复记录
    SELECT* FROM  tableA  a WHERE
     a.ROWID> (
     SELECT MIN(ROWID) FROM tableB b WHERE 
     a.column=b.column
     ) 
    
    --删除相同记录
    DELETE FROM  tableA  a WHERE
     a.ROWID> (
     SELECT MIN(ROWID) FROM tableB b WHERE 
     a.column=b.column
     ) 
    
    --分页 start=10 limit=10
    --end 为 start + limit
    SELECT * FROM 
    (
      SELECT A.*,Rownum rn FROM 
        (SELECT * FROM tableA ORDER BY id) A
      WHERE rownum <= 20
    ) b WHERE rn> 10  ORDER BY id DESC

## 7、存储过程中多用commit（谨慎使用） ##

这样程序的性能能得到提高，需求也会因为commit所释放的资源而减少。

## 8、不用in，not in，用exists,not exists来代替 ##

## 9、用表连接替换exists ##

## 10、用where子句代替having子句 ##

## 11、用exists替换distinct ##

## 12、通常避免在索引列上使用not ##

我们要避免在索引列上使用not，not会产生 和在索引列上使用函数相同的影响，当oracle遇到not，他就会停止使用索引转而执行全表扫描。

## 13、避免在索引列上使用计算 ##

## 14、用>=替代> ##

## 15、避免在索引列上使用is null 和is not null ##

## 16、总是使用索引的第一个列 ##

如果索引是建立在多个列上，只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。这也是一条街简单而重要的规则，当仅使用索引的第二个列时，优化器使用了全表扫描而忽略了索引。

## 17、用union-all替换union（如果有可能的话） ##

当SQL语句需要union两个查询结果集合时，这两个结果集合会以union-all的方式被合并，然后再输入最终结果并排序。如果用union-all替代union，这样排序就不是必要的了。效率就会因此得到提高。不过需要注意的是，union-all将会重复输出两个结果集合中相同的记录，因此还是要从实际业务需求分析使用union-all的可行性。

## 18、避免改变索引列的类型 ##