Oracle sql 性能优化(二)
性能优化
2.11【推荐】用 CASE … WHEN … THEN … ELSE … END 格式减少表的扫描次数
说明:
在我们的开发维护工作中常常必须基于多组数据表计算不同的聚集,需要多次扫
描业务数据表,而 CASE 语句就可以把多次扫描合并成一次来完成。
举例:
Demo
Avoided
SELECT COUNT(0)
FROM BLACKLIST_SUBS
WHERE BLACKLIST_TYPE='A'
AND SUBS_ID=24147;
SELECT COUNT(0)
FROM BLACKLIST_SUBS
WHERE BLACKLIST_TYPE='B'
AND SUBS_ID=24147;
SELECT COUNT(0)
FROM BLACKLIST_SUBS
WHERE BLACKLIST_TYPE='C'
AND SUBS_ID=24147;
Preferred
SELECT COUNT(CASE BLACKLIST_TYPE WHEN 'A' THEN 1 ELSE NULL
END) A,
COUNT(CASE BLACKLIST_TYPE WHEN 'B' THEN 1 ELSE NULL
END) B,
COUNT(CASE BLACKLIST_TYPE WHEN 'C' THEN 1 ELSE NULL
END) C
FROM BLACKLIST_SUBS
WHERE SUBS_ID = 24147;
\
2.12【推荐】用 WHERE 子句替换 HAVING 子句
说明:
避免使用 HAVING 子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤,
这个处理需要排序、统计等操作。如果能通过 WHERE 子句限制记录的数目,那就能
减少这方面的开销。
举例:
Demo
Avoided
SELECT REGION, AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION != 'SYDNEY'
AND REGION != 'PERTH'
Preferred
SELECT REGION, AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION != 'SYDNEY'
AND REGION != 'PERTH'
GROUP BY REGION
\
2.13【推荐】LIKE 子句尽量前端匹配
说明:
LIKE 参数使用得非常频繁,因此如果能够对于 LIKE 子句使用索引,将很好地提高
查询的效率。
举例:
Demo
Avoided
SELECT CUST_ID, CUST_NAME
FROM CUST
WHERE CUST_NAME LIKE '%Tom%'
Preferred
SELECT CUST_ID, CUST_NAME
FROM CUST
WHERE CUST_NAME LIKE 'Tom%'
\
2.14【强制】程序代码中禁止使用 DB_LINK
\
2.15【推荐】其他场景下尽量不使用 DB_LINK
说明:
在查询语句中使用 DB_LINK 时,有可能会带来性能问题,比如下面一条 SQL 语句:
SELECT * FROM EVENT_USAGE_1120 A, SUBS@link_cc B
WHERE A.SUBS_ID = B.SUBS_ID
AND B.ACC_NBR='186123456789';
有可能会把 SUBS@link_cc 的全表数据全部加载到当前实例中,导致网络传输负载剧
增,很大程度上造成当前主机压力过大,影响系统性能。
一般来说比较合适的做法是,通过代码直连到 @link_cc 所在的主机,先把 SUBS_ID
查询出来,然后再把 SUBS_ID 作为条件查询 EVENT_USAGE_***表。这样虽然一条 SQL
拆成了两条,但实际效果在很大程度上要大大优于单条 SQL。
\
2.16【强制】避免超长 SQL,SQL 长度不可超过 4000 长度。
说明:
超长 SQL 往往导致难以理解,并可能伴随性能隐患。同时,应尽量减少 SQL 语句复
杂度,用简单的 SQL 完成任务,复杂的业务逻辑尽量由业务代码来实现。
\
2.17【推荐】SQL 子查询嵌套不宜超过 3 层
说明:
禁止使用多层的 SQL 嵌套,除了分页查询,一般 SQL 语句建议不超过 3 层嵌套,
过于复杂的 SQL 可根据业务逻辑拆分为多条 SQL 来实现。
\
2.18【推荐】避免不必要的排序
说明:
避免不必要的排序,对查询结果进行排序会大大降低系统性能。应将大多数的排序
工作交给应用层去完成。
\
2.19
【推荐】查询分页场景,建议如下分页格式,先查询过滤出一部分数据,再做下一层过滤查询
举例:
Demo
Avoided
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT OWNER, CREATED, OBJECT_ID, OBJECT_TYPE
FROM T1
WHERE OWNER = 'SYS'
ORDER BY OBJECT_ID DESC) A)
WHERE RN > 10
and RN <= 20
Preferred
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT OWNER, CREATED, OBJECT_ID, OBJECT_TYPE
FROM T1
WHERE OWNER = 'SYS'
ORDER BY OBJECT_ID DESC) A
WHERE ROWNUM <= 20)
WHERE RN > 10
版权声明: 本文为 InfoQ 作者【默默的成长】的原创文章。
原文链接:【http://xie.infoq.cn/article/fe17c84a9b3db181a86cc2c11】。文章转载请联系作者。
评论