Oracle数据库 sql优化

Oracle数据库 sql优化

1.表的访问的方式

​ 全表扫描方式

​ 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块的方式优化全表扫描。这种方式通常情况效率低。

​ 通过ROWID访问表 （相当于mysql limit）

​ 采用基于ROWID的访问方式访问表, ROWID包含了表中记录的物理位置信息，ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问 ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高。这种方式通常情况效率高。

2.合理调整From 后面的表顺序

​ Oracle解析器是按照从右到左的顺序处理From之后的表，From子句中写在最后的表(驱动表)将被最先处理，为了高效率需要选择记录最少的表作为驱动表。

3.Where子句的连接顺序

​ Oracle采用自下而上的顺序解析Where子句，所以要将过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

4.Select 子句中不要使用”*“

​ 把需要的的字段列出来，“*”可能把不需要的字段也列出来是需要查询时间。

5.使用索引提高效率

​ 普通索引

create index index_text_txt on test(txt);
1

​ 唯一索引

create unique index <index_name> on <table_name>(<coiumn_name>);
1

​ 组合索引

create index <index_name> on <table_name>(<column_name1><column_name2>);
1

​ 删除索引

drop index <index_name>;
1

6.sql 语句用大写

​ Oracle解析sql,都需要把小写转换为大写。

7.避免索引失效细节

7.1 使用不等于操作符(<>,!=)

​ 下面这种情况，dept_id有一个索引，sql语句执行一次就全表扫描。

select * from dept where staff_num != 1000;
1

​ 通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了

select * from dept shere staff_num < 1000 or dept_id > 1000;
1

​ 7.2 使用 is null 或者 is not null

​ 使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值

​ 7.3 使用函数 或者 计算

​ 如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引，从而使用全表扫描。

select id from t where substring(name,1,3)='abc'
-- name以abc开头的id应改为:
select id from t where name like 'abc%'
1
2
3

select id from t where num/2=100
-- 应改为:
select id from t where num=100*2
1
2
3

7.4 比较不匹配的数据类型

​ dept_id数据类型是varchar2字段，但是用不匹配的数据类型查找，导致全表扫描。

select * from dept dept_id = 1000;
--改为：
select * from dept dept_id ='1000';
1
2
3

​ 7.5 使用like子句

Column1 like 'aaa%'   -- 是可以的
Column1 like '%aaa%'  --用不到索引
1
2

7.6 使用in 用exists代替

select num from a where num in(select num from b)
-- 用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
1
2
3

​ 7.7 to_char 与 to_date

-- 不用到索引:  dt是建了索引的
Select * from test where to_char(dt,'yyyymmdd'’) ='20041010';
-- 用到索引:
select * from test where dt >=to_date('20041010','yyyymmdd') and dt < to_date('20041010','yyyymmdd') + 1 

1
2
3
4
5

​ 7.8 如果能不用排序，则尽量避免排序

​ 用到排序的情况有集合操作。Union ,minus ,intersect等,注：union all 是不排序的。Order by、 Group by、Distinct、In 有时候也会用到排序，确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，内存排序的速度是硬盘排序的1万倍