前段时间去西南某项目做sql优化和性能测试的相关工作,期间也查询了很多资料。现在总结下sql性能优化相关的知识以便以后用到。
1. sql优化相关
2. 硬解析和软解析
3. 执行计划
一、sql优化相关
1、where子句的连接顺序
Oracle采用自下而上的顺序解析where子句,所以可以过滤掉较多记录数的条件应该写在where子句的末尾。
2、select子句尽量避免使用*
通过*会查询数据库字典来将其转换成对应的列名,带来额外的查询开销。推荐在查询时列出具体的column。
3、 整合简单的查询语句
如果有几个简单的查询语句,可以将他们整合到一个连接查询中。这样可以减少数据库IO开销。
4、 使用Truncate而非Delete
使用delete删除记录时,Oracle会保存删除信息以备恢复。Truncate全表删除时不保存回滚信息不能恢复,效率更快。有外键约束时不能删除数据或删表。
5、 使用(not)exists替代(not)in
在针对许多基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。使用exists/not exists可以提高查询效率。1
2
3
4select loan_no,invoice_type,outstanding
from cl_invoice a
where outstanding > 0
and exists (select 1 from cl_loan where loan_no = a.loan_no and loan_type = '200');
6、 用表连接替换exists
7、 避免在索引上使用计算(索引失效相关)
where子句中不要对索引进行计算,否则会导致索引失效进行全表扫描。同不要在where子句等号的左边进行函数算数和其他表达式运算。1
2select loan_key,borrower from cl_drawdown_tbl where dd_key / 100 >= 20; -- 低效
select loan_key,borrower from cl_drawdown_tbl where dd_key >= 20 * 100; -- 高效
8、where子句中不要在索引列上使用not
同上,会使索引失效。
9、避免在索引列上使用is null和is not null1
select client_short from fm_client where client_no is null;
10、用≥代替>
使用≥会使扫描的记录数更少效率更高。
11、用union替代or(适用于索引列)
通常情况下,用union替换where子句中的or更好。对索引列使用or将会造成全表扫描。1
2
3
4
5select * from cl_loan where loan_key = 89 or borrower = '10000002918'; -- 低效会进行全表扫描
select * from cl_loan where loan_key = 89
UNION
select * from cl_loan where borrower = '10000002918'; -- 高效
12、优化group by
- 可以通过提前将不需要的记录过滤掉,来提高group by语句的效率。(用where替换having)
- 对于还要继续进行被查询的结果集,先不要做group by分组操作。
1
2
3select job,avg(sal) from emp group by job HAVING job = 'PRESIDENT' or job = 'MANAGER'; --低效
select job,avg(sal) from emp where job = 'PRESIDENT' or job = 'MANAGER' group by job; --高效
13、总是使用索引的第一列(前导列)
如果索引建立在多个列上,只有当它的第一个列被where子句引用时,语句才会选择使用该索引。这是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。
14、避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。在非必要的情况下应减少使用。
15、sql语句尽量使用大写
保证sql一致性,防止硬解析。
16、使用聚合函数
AVG()、MAX()、MIN()、SUM()、COUNT()、MEDIAN()等。
17、利用rowid提升update性能1
update dba ta set prov_code = (select area_code from dave tb where ta.id = tb.id);
查看update期间session执行时间:1
select sid,target,time_remaining,elapsed_seconds,message,sql_id from v$session_longops where sid = 138; -- 耗时长
利用rowid提升update性能:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24DECLARE
CURSOR c_cur is
SELECT a.area_code, b.ROWID ROW_ID
FROM dave a, dba b
WHERE a.id = b.id
ORDER BY b.ROWID; -- 对于大表的update,按照rowid排序是必要的。
v_counter NUMBER;
BEGIN
v_counter := 0;
FOR row IN cur LOOP
UPDATE dba
SET prov_code = row.area_code
WHERE ROWID = row.ROW_ID;
v_counter := v_counter + 1;
-- 分次提交以免事务较大
IF (v_counter >= 1000)
THEN
COMMIT;
v_counter := 0;
END IF;
END LOOP;
COMMIT;
END;
二、硬解析和软解析
Oracle对sql语句进行处理包含以下几个步骤:
- 语法检查(syntax check):检查sql的拼写是否正确
- 语义检查(semantic check):如检查执行sql的用户权限等
- 对sql进行解析(prase):对sql进行解析,生成解析树和执行计划
- 执行sql,返回结果
第三步sql解析分为硬解析和软解析:
- 硬解析:在Share pool中找不到与该sql相匹配的执行计划时会重新生成解析树和执行计划。
- 软解析:如果在Shared Pool中找到了与之完全相同的sql解析好的结果后会跳过生成解析树和执行计划,大大缩短查询时间。
创建解析树和执行计划有巨大的开销,所以应该极力避免硬解析。在开发中尽量保持sql代码的一致性和绑定变量。
Oracle会对sql语句进行HASH运算计算hash值,再与共享池中的hash值逐一比较。可以通过访问v$sql、v$sqlarea、v$sqltext等数据字典中的hash_value列得出1
2
3
4
5
6
7
8select dd_key,invoice_type,due_date,billed_amt,outstanding from cl_invoice_tbl where dd_key = 6295100;
select address,hash_value,executions,sql_text from v$sql where upper(sql_text) like '%SELECT DD_KEY,INVOICE_TYPE,DUE_DATE,BILLED_AMT,OUTSTANDING FROM CL_INVOICE_TBL WHERE DD_KEY = 6295100%';
ADDRESS HASH_VALUE EXECUTIONS SQL_TEXT
----------------------------------------------------
00000000D3AA8D60 2347740352 1 select...
尽量保持sql语句的一致性:
例如 select empno from emp; 和 select empno from Emp; 尽管区别只是查询的表对象使用了大小写,但是会使用硬解析产生不同的执行计划。
绑定变量:
select empname from emp where empno = 7369; 和 select empname from emp where empno = 7788; 只是where子句中的条件不同,依旧会产生不同的执行计划。
绑定变量(bind var)是指在DML语句中使用一个占位符,即使用冒号后面紧随变量名的方式(预编译),形如:1
select empname from emp where empno = :v_empno;
在绑定变量后,变量值在查询时提供。该查询只编译一次,随后会把查询计划缓存在共享池中,后续类似的查询便能使用软解析。
三、执行计划
Oracle执行计划是对一条查询语句执行过程或访问路径的描述。
- 执行计划的常用列字段含义:
- 基数(Rows):当前操作返回结果集的行数。
- 字节(Bytes):执行该步骤返回的字节数。
- 耗费(Cost-%CPU):执行成本,用于说明sql执行的开销,越小越好。
- 时间(Time):该操作大约耗时。
执行顺序
根据Operation缩进来判断,缩进最多的最先执行,缩进相同时,最上面的最先执行。表的访问方式
1、TABLE ACCESS FULL(全表扫描)
Oracle读取表中的所有行,并逐一检查是否满足where限制条件。数据量太大的表不建议使用全表扫描,除非提取的记录数占全表10%左右。
2、TABLE ACCESS BY ROWID(通过ROWID获取数据)
ROWID是由Oracle自动加在表中每行最后的一列伪列,一旦一行数据插入后,则其对应的ROWID在该行的生命周期内是唯一的,即使发生行迁移,该行的ROWID值也不变。通过ROWID可以快速定位到目标记录上,这是Oracle中存取单行数据最快的方法
3、TABLE ACCESS BY INDEX SCAN(索引扫描)
分类 | 描述 | 说明 |
---|---|---|
index unique scan | 索引唯一扫描 | 针对唯一性索引,每次最多返回一条记录.表中某个字段存在unique、primary key约束时,通常使用唯一索引扫描 |
index range scan | 索引范围扫描 | 使用一个索引获取多行数据。 1. 在唯一索引列上使用范围运算符「>/≥/</≤/between」 2. 使用组合索引的部分列进行查询「必须包含前导列,否则会进行全表扫描」 3. 对非唯一索引进行查询 都会触发索引范围扫描 |
index full scan | 索引全表扫描 | 与全表扫描对应,只是查询出来的所有数据都必须从索引中可以直接获取 |
index fast full scan | 索引快速扫描 | 扫描索引中所有的数据,与index full scan类似,但是显著的区别是它不对查询出的数据进行排序 |
- 更多关于Oracle执行计划的资料见「Oracle性能优化-读懂执行计划」
本文链接: http://www.xiaopeng.pro/articles/12c134cc.html
版权声明: 本原创文章采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。转载请注明出处!