【文件属性】：
文件名称：SQL性能优化
文件大小：20KB
文件格式：RAR
更新时间：2018-01-09 04:08:06
SQL
1 性能优化
1.1 避免频繁 commit，尤其是把 commit 写在循环体中每次循环都进行commit。
1.2 使用绑定变量，避免常量的直接引用。
示例：以下书写不符合本规范。
INSERT INTO sm_users
(user_id, user_name, created_by, creation_date)
VALUES (1, 'Tang', -1, SYSDATE);
建议用如下方式操作：
DECLARE
v_user_id sm_users.user_id%TYPE;
v_user_name sm_users_user_name%TYPE;
v_created_by sm_users.created_by%TYPE;
v_creation_date sm_users.creation_date%TYPE;
BEGIN
...
INSERT INTO sm_users
(user_id, user_name, created_by, creation_date)
VALUES (v_user_id, v_user_name, v_created_by, v_creation_date);
END;
1.3 Operator 的使用规范
IN
比较容易写及清晰易懂
但效能是比较低的
ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
NOT IN
此操作是强列推荐不使用的，因为不能应用表的索引。
推荐方案：用NOT EXISTS 或（Outer-Join+判断为空）方案代替
例如：
SELECT deptno
FROM dept
WHERE deptno NOT IN(SELECT deptno
FROM emp)
建议写成：
SELECT deptno
FROM dept, emp
WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL
<>
永远不会用到索引的
推荐方案：用其它相同功能的操作运算代替，如:
a<>0 改为 a>0 or a<0
a<>’’ 改为 a>’’
IS NULL 或IS NOT NULL
一般是不会应用索引的，因为B-tree索引是不索引空值的。
推荐方案：用其它相同功能的操作运算代替，如:
a is not null 改为 a>0 或a>’’
不允许字段为空，而用一个default代替空值，如业扩申请中状态区位不允许为空， default为申请。
> 及 <
有索引就会采用索引查找
但有的情况下可以对它进行优化
如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。
LIKE
LIKE可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。性能肯定大大提高。
UNION
SQL在运行时先取出数个查询的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
实际大部分应用中是不会产生重复的记录，推荐采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
Exists
示例：当有 A、B 两个结果集，当结果集 B 很大时，A 较小时，适用 exists，如：
SELECT *
FROM a
WHERE EXISTS(SELECT 1
FROM b
WHERE a.COLUMN = b.COLUMN);
当结果集 A 很大时，B 很小时，适用 in,如：
SELECT *
FROM a
WHERE a.COLUMN IN(SELECT b.COLUMN
FROM b)
1.4 SQL书写的影响
同一功能同一性能不同写法SQL的影响
Select * from zl_yhjbqk
Select * from dlyx.zl_yhjbqk（带表所有者的前缀）
Select * from DLYX.ZL_YHJBQK（大写表名）
Select * from DLYX.ZL_YHJBQK（中间多了空格）
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。
WHERE后面的条件顺序影响
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。
查询表顺序的影响
在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）
对条件字段的一些优化
采用函数处理的字段不能利用索引，如：
substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)， 优化处理： sk_rq>=trunc(sysdate) and sk_rq50，优化处理：ss_df>30
‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’
注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型
条件内包括了多个本表的字段运算时不能进行索引，如：
ys_df>cx_df，无法进行优化
qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’
HINT
是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示
目标方面的提示：
COST（按成本优化）
RULE（按规则优化）
CHOOSE（缺省）（ORACLE自动选择成本或规则进行优化）
ALL_ROWS（所有的行尽快返回）
FIRST_ROWS（第一行资料尽快返回）
执行方法的提示：
USE_NL（使用NESTED LOOPS方式联合）
USE_MERGE（使用MERGE JOIN方式联合）
USE_HASH（使用HASH JOIN方式联合）
索引提示：
INDEX（TABLE INDEX）（使用提示的表索引进行查询）
其它高级提示(如并行处理等等)
1.5 索引的规则：
建立索引常用的原则如下：
1. 表的主键、外键必须有索引
2. 数据量超过 1000 行的表应该有索引
3. 经常与其它表进行连接的表，在边接字段上应建立索引
4. 经常出现在 where 子句中的字段且过滤性极强的，特别是大表的字段，应该建立索引
5. 索引字段，尽量避免值为 null
6. 复合索引的建立需要仔细分析；尽量考虑用单字段索引代替：
正确选择复合索引中的第一个字段，一般是选择性较好的且在 where 子句中常的字段上。
复合索引的几个字段是否经常同时以and方式出现在where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引。
如果复合索引中包含的字段经常单独出现在 where 子句中，则分解为多个单字段索引。
如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段。
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
7. 频繁 DDL 的表，不要建立太多的索引
8. 删除无用的索引，避免对执行计划造成负面影响
9. 让 SQL 语句用上合理的索引，合理让 SQL 语句使用索引的原则如下：
首先，看是否用上了索引，对于该使用索引而没有用上索引的 SQL 语句，应该想办法用上索引。
其次，看是否用上正确的索引了，特别复杂的 SQL 语句，当其中 where 子句包含多个带有索引的字段时，更应该注意索引的选择是否合理。错误的索引不仅不会带来性能的提高，相反往往导致性能的降低。
针对如何用上合理的索引，以 Oracle 数据中的例子进行说明：
任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。
避免不必要的类型转换，要了解“隐藏”的类型转换。
增加查询的范围，限制全范围的搜索。
索引选择性低，但资料分布差异很大时，仍然可以利用索引提高效率。
Oracle 优化器无法用上合理索引的情况下，利用 hint 强制指定索引。
使用复合索引且第一个索引字段没有出现在 where 中时，建议使用 hint 强制。
1.6 索引使用优化
建立Plan_Table
CREATE TABLE PLAN_TABLE
(
STATEMENT_ID VARCHAR2(30),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER(38),
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER(38),
ID NUMBER(38),
PARENT_ID NUMBER(38),
POSITION NUMBER(38),
OTHER LONG
)
Syntax
说明：
explain plan set statement_id = user_define for select ...
将结果显示
SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name,
POSITION
FROM plan_table
START WITH ID = 0 AND STATEMENT_ID = user_define
CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = user_define
示例
如要测试下面SQL：
SELECT c.short, a.cday, a.card_no, a.qty
FROM sales.stockiohis a, sales.product_info b, sales.vendor c
WHERE a.card_no = b.card_no
AND b.vendorid = c.vendorid
AND a.produce_no = '2007090001'
AND a.CATEGORY = '10'
AND a.iotype = '1'
新增文件：例 d:\mydoc\plan.sql '0001'为user_define为使用者自定义编号
EXPLAIN PLAN SET STATEMENT_ID = '0001' FOR
SELECT 'X'
FROM sales.stockiohis a ,sales.product_info b ,sales.vendor c
WHERE a.card_no = b.card_no AND b.vendorid = c.vendorid
AND a.produce_no = '2007090001' AND a.CATEGORY = '10' AND a.iotype = '1'
/
SET arraysize 1
SET line 100
COLUMN op format a40
COLUMN object_name format a20
COLUMN options format a20
SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name,
POSITION
FROM plan_table
START WITH ID = 0 AND STATEMENT_ID = '0001'
CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = '0001'
/
DELETE FROM plan_table
WHERE STATEMENT_ID = '0001'
/
COMMIT
/
结果
1.7 避免不必要的排序
说明：对查询结果进行排序会大大的降低系统的性能，group与union都会对数据作排序，要耗费较多的内存，视状况用union all既可，不然有时数据太大又要进行union的排序，会导致Oracle数据库SORT_AREA_SIZE不足发生系统错误。
1.8 对于数字型的Primary Key，建议用序列 sequence 产生。
说明：除非是单据的单号，要求必须是唯一，并且依据流水号不可以跳号，不然在大量交易的表格中，不在乎跳耗时，要取得唯一的Primary Key 建议使用Oracle Sequence这样速度会较快，而且不会有锁定(Lock)的问题。
【文件预览】：
SQL性能優化.doc
