Oracle sql 性能优化（一）

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第16天，点击查看活动详情 >>

性能优化

2.1【推荐】尽量减少数据库负担

说明：

当执行每条 SQL 语句时, ORACLE 在内部执行了许多工作：解析 SQL 语句、估算索引

的利用率、绑定变量、读数据块等。减少访问数据库的次数，就能实际上减少 ORACLE

的工作量

\

2.2【推荐】避免大表关联，大表关联可能存在性能问题

\

2.4【强制】禁止使用“SELECT *”这样的语句，特别是在程序代码内部

说明：

当需要查询表中的所有列时，也需列出所有的字段名。

Note：如果有子查询，而且子查询有列名的，可以使用 select * ，样例可参看 2.19

查询分页场景的举例。

举例：

Demo

Avoided

SELECT *

FROM CUST

WHERE CUST_ID = 1

Preferred

SELECT CUST_ID, CUST_CODE, CUST_NAME, CUST_TYPE, CERT_ID,

PARENT_ID, DELIVER_METHOD, ZIPCODE

FROM CUST

WHERE CUST_ID = 1

\

2.5【推荐】尽量避免多表的关联操作

说明：

关联表越多，需要 Oracle 调度的资源就越多。SQL 应尽量简化，查询类语句只查询

业务所需的数据，不查询无关数据表。

特别需要关注数据量巨大的表关联操作，使用不当会引发系统故障。

\

2.6【推荐】尽量使用 UNION ALL 代替 UNION

说明：

UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进

行排序运算，删除重复的记录再返回结果，如果表数据量大的话可能会导致用磁盘进

行排序。

UNION ALL 操作只是简单的将两个结果合并后就返回，所以可能存在重复记录。

需要结合业务需求分析使用 UNION ALL 的可行性。

\

2.7【强制】禁止 SQL 内层使用 ORDER BY 和 GROUP BY 排序操作

说明：

Note：查询分页场景下例外

\

2.8【推荐】尽量减少外层使用 ORDER BY 和 GROUP BY 排序操作

说明：

大量的排序操作影响系统性能，如必须使用排序操作，尽量建立在有索引的列上。

举例：

Demo

Avoided

SELECT A.SUBS_ID,

A.PREFIX,

A.ACC_NBR,

A.ACCT_ID,

A.CUST_ID,

B.PROD_STATE,

B.COMPLETED_DATE,

B.OFFER_ID,

B.SUBS_PLAN_ID

FROM (SELECT SUBS_ID, PREFIX, ACC_NBR, ACCT_ID, CUST_ID

FROM SUBS

ORDER BY SUBS_ID) A,

PROD B

WHERE A.SUBS_ID = B.PROD_ID

AND B.PROD_STATE = 'A'

ORDER BY PROD_ID

Preferred

SELECT A.SUBS_ID,

A.PREFIX,

A.ACC_NBR,

A.ACCT_ID,

A.CUST_ID,

B.PROD_STATE,

B.COMPLETED_DATE,

B.OFFER_ID,

B.SUBS_PLAN_ID,

B.INDEP_PROD_ID

FROM (SELECT SUBS_ID, PREFIX, ACC_NBR, ACCT_ID, CUST_ID FROM

SUBS) A,

PROD B

WHERE A.SUBS_ID = B.PROD_ID

AND B.PROD_STATE = 'A'

ORDER BY PROD_ID

\

2.9

索引的使用

2.9.1【强制】不允许对索引列进行计算

举例：

Demo

Avoided

SELECT ......

FROM EMP

WHERE HIRE_DATE + 100 =TO_DATE('20080411', 'YYYYMMDD')

Preferred

SELECT ......

FROM EMP

WHERE HIRE_DATE = TO_DATE('20080411','YYYYMMDD') - 100

\

2.9.2 【强制】注意比较值与索引列数据类型的一致性，避免使用数据库的类型自动转换功能

举例：

Demo

Avoided

vSubsId VARCHAR(20);

vSubsId := ‘10001’;

SELECT PREFIX, ACC_NBR

FROM SUBS

WHERE SUBS_ID := vSubsId

Note：vSubsID 为字符串型变量

Preferred

nSubsId NUMBER;

nSubsId := 10001;

SELECT PREFIX, ACC_NBR

FROM SUBS

WHERE SUBS_ID := nSubsId

Note：nSubsId 为数值型变量

\

2.9.3 【推荐】对于复合索引，SQL 语句的 WHERE 查询条件总是使用索引的第一列

说明：

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被 where 子句

引用时,优化器才会选择使用该索引。

举例：

Demo

Avoided

SELECT PROD_ID, ATTR_ID, VALUE, EFF_DATE, EXP_DATE

FROM PROD_ATTR_VALUE

WHERE ATTR_ID = :ATTR_ID

【说明】：

PROD_ATTR_VALUE

表 有 复 合 索 引

PK_PROD_ATTR_VALUE(PROD_ID,ATTR_ID)

Preferred

SELECT PROD_ID, ATTR_ID, VALUE, EFF_DATE, EXP_DATE

FROM PROD_ATTR_VALUE

WHERE PROD_ID = :PROD_ID

AND ATTR_ID = :ATTR_ID

【说明】：

PROD_ATTR_VALUE

表 有 复 合 索 引

PK_PROD_ATTR_VALUE(PROD_ID,ATTR_ID)

\

2.9.4 【推荐】使用 Oracle 的函数索引解决空字段导致索引失效的问题

说明：

如果索引字段有空值，而且空值所占数据量较小，使用 IS NULL 判断查询，会导致

索引失效，此场景建议建索引时使用 表名(列名,0) 的方法，可以走索引提高效率。

举例：

Demo

Avoided

SELECT SUBS_ID, CUST_ID, ACCT_ID, PREFIX, ACC_NBR

FROM SUBS

WHERE PPS_PWD IS NULL

Note：索引 IDX_PPS_PWD ON SUBS(PPS_PWD)，索引失效，全表扫描

Preferred

SELECT SUBS_ID, CUST_ID, ACCT_ID, PREFIX, ACC_NBR

FROM SUBS

WHERE PPS_PWD IS NULL

Note：索引 IDX_PPS_PWD ON SUBS(PPS_PWD,0)，走索引

\

2.9.5 【推荐】对于索引的比较，尽量避免使用不等于（!=）

举例：

Demo

Avoided

SELECT ACCOUNT_NAME

FROM TEST

WHERE AMOUNT != 0

Preferred

SELECT ACCOUNT_NAME

FROM TEST

WHERE AMOUNT > 0

\

2.10【推荐】在 IF/ELSE 类型的查询中，可使用 DECODE 替代

说明：

使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。

举例：

Demo

Avoided

SELECT COUNT(1), SUM(SAL)

FROM EMP

WHERE DEPT_NO = '0020'

AND ENAME LIKE 'SMITH%';

SELECT COUNT(1), SUM(SAL)

FROM EMP

WHERE DEPT_NO = '0030'

AND ENAME LIKE 'SMITH%';

Preferred

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%';