ORACLE-SQL性能优化(3）

秒变学霸的18岁码农

于 2023-05-21 20:36:06 发布

阅读量724

点赞数

文章标签：数据库 oracle sql

本文链接：https://blog.csdn.net/2301_76957510/article/details/130796314

版权

2. 给优化器更明确的命令

自动选择索引

如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．

在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．

举例:

SELECT ENAME FROM EMP

WHERE EMPNO = 2326 AND DEPTNO = 20 ;

这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．

TABLE ACCESS BY ROWID ON EMP

INDEX UNIQUE SCAN ON EMP_NO_IDX

至少要包含组合索引的第一列

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));

Table created.

SQL> create index multindex on multiindexusage(inda,indb); Index created.

SQL> set autotrace traceonly

SQL> select * from multiindexusage where inda = 1; Execution Plan

----------------------------------------------------------

SELECT STATEMENT Optimizer=CHOOSE
0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE) SQL> select * from multiindexusage where indb = 1;

Execution Plan

----------------------------------------------------------

SELECT STATEMENT Optimizer=CHOOSE
0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'

很明显当仅引用索引的第二个列时优化器使用了全表扫描而忽略了索引

避免在索引列上使用函数

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

举例:

低效：

SELECT . FROM DEPT

WHERE SAL * 12 > 25000;

高效: SELECT .

FROM DEPT

WHERE SAL > 25000/12;

避免使用前置通配符

WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO LIKE '%109204421';

在这种情况下，ORACLE将使用全表扫描.

避免在索引列上使用NOT

通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他

就

会停止使用索引转而执行全表扫描.举例:

低效: (这里,不使用索引) SELECT .

FROM DEPT

WHERE DEPT_CODE NOT = 0;

高效: (这里,使用了索引) SELECT .

FROM DEPT

WHERE DEPT_CODE > 0;

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

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此

记录. 如果至少有一个列不为空，则记录存在于索引中．

如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!

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

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

避免出现索引列自动转换

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.

假设EMP_TYPE是一个字符类型的索引列.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE USER_NO = 109204421

这个语句被ORACLE转换为:

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES

WHERE TO_NUMBER(USER_NO) = 109204421

因为内部发生的类型转换, 这个索引将不会被用到!

在查询时尽量少用格式转换

 如用 WHERE a.order_no = b.order_no

 不用

WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)

= TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)

3.减少访问次数

减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作:

解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等.

由此可见, 减少访问数据库的次数 , 就能实际上减少

ORACLE的工作量.

类比，工程实施

使用DECODE来减少处理时间

例如:

SELECT COUNT(*)，SUM(SAL) FROM EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE ‘SMITH%’;

SELECT COUNT(*)，SUM(SAL) FROM EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE ‘SMITH%’;

你可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL

FROM EMP WHERE ENAME LIKE ‘SMITH%’;

减少对表的查询

在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:

低效

SELECT TAB_NAME FROM TABLES

WHERE TAB_NAME = ( SELECT TAB_NAME

FROM TAB_COLUMNS WHERE VERSION = 604)

AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

高效

SELECT TAB_NAME FROM TABLES

WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER)

FROM TAB_COLUMNS WHERE VERSION = 604)

4. 细节上的影响

WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时，WHERE子句中排

在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。

 如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：

select * from emp e,dept d

where d.deptno >10 and e.deptno =30 ;

 如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。

select * from emp e,dept d

where e.deptno =30 and d.deptno >10 ;

WHERE子句 ——函数、表达式使用

 最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。

Order by语句

 ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或

者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是

重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

联接列

对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。

select * from employss where

first_name||''||last_name ='Beill Cliton';

系统优化器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。

select * from employee where

first_name ='Beill' and last_name ='Cliton';

带通配符（%）的like语句

通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。

select * from employee where last_name like '%cliton%';

在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：

select * from employee where last_name like 'c%';

用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

例如:

低效:

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION GROUP BY REGION

HAVING REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’

高效

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’

GROUP BY REGION

顺序

WHERE > GROUP > HAVING

用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进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。

用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)

的唯一性验证。

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