Oracle db开发的时候,对SQL性能质量的优化成本远比在后期的成本要低。作为数据库开发人员,很应该有写高性能sql意识习惯。比如,访问表的方式有全表扫描和通过ROWID访问表,可以不走全表扫描可以完成的,就尽量规避提升性能。SQL语句的优化过程可以是定位问题的语句;检查执行计划中优化器的统计信息;分析相关表的记录数,索引情况;改写sql,使用hint,调整索引,表分析;有些SQL语句不具备优化的可能或者优化效果不突出,需要优化数据处理的方式。
以下是ORACLE 语句类型:
1.DDL (Data Definition Language)数据库定义语言
CREATE/ALTER/DROP/TRUNCATE/COMMENT/RENAME
2.DML (DataManipulation Language)数据操纵语言
INSERT/UPDATE/DELETE/MERGE/CALL/EXPLAIN PLAN/LOCK TABLE
3.DCL(Data ControlLanguage)数据库控制语言 授权,角色控制等
GRANT/REVOKE
4.TCL(TransactionControl Language)事务控制语言
SAVEPOINT 设置保存点/ROLLBACK 回滚/SETTRANSACTION
5.查询语句
SELECT
访问路径的方式:1.FULL: 执行全表扫描。2.ROID: 根据ROWID进行扫描。3.INDEX: 根据某个索引进行扫描。
在SQL性能优化方面有什么经验?这个问题我们先可以从配置数据库的参数,提升数据库硬件性能的薄弱点,再来从sql语句方面的提升。
sql编码的时候,要考虑尽量减少服务器的资源消耗主要是磁盘IO;设计的时候尽量依赖oracle的优化器并选择合适的索引,索引的双重效应。
编码的时候注意:
0.合理利用索引,避免大表全表扫描。
1.合理进行中间表落地。
2.多用commit,释放资源。
查询语句的性能提升点:
1.在写查询语句的时候,在查询子句中不要嵌套子查询,因为每次执行的时候都需要去解析子查询语句,导致效率变慢,where子句同理。
2.较少非必要粒度,不需要的字段不要查询出来。
3.查询的时候用‘*’,oralce会通过查数据字典来转换成所有的列名,消耗更多的时间。
4.用distinct的时候,可以考虑用group by来代替,提高性能。distinct需要一次排序。
5.where 子句中索引列是函数的部分,优化器将使用全表扫描,尽量规避。
6.where 子句的解析是从下而上的,多大可能缩小数据范围的条件放最下面。
7.用where代替 having having是在检索出所有结果后再进行过滤的。这个过程需要排序和总计,消耗资源。
8.用exist和 not exist来代替 in和 not in。规避全表扫描。Exists只检查行的存在,而 in检查实际值。EXISTS与IN的使用效率的问题,通常情况下采用exists要比in 效率高,因为IN不走索引,但要看实际情况具体使用:
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
9.规避那些因为语句导致不可以使用索引的情况。比如用了<>,和字段拼接后比较。
10.where子句中少用内联视图。
11.尽量不使用索引,以启用智能扫描。
12.表关联尽量用hash jion。
删除语句的性能提升点:
1.如果可以,用truncate代替 delete。delete下回滚段会存放可以恢复的信息,truncate则不会,所以效率高。
2.删除数据最好是利用分区或临时表中转,truncate表或者分区。
3.使用并行DML可以有效提升速度。
4.如果追求性能而又不允许适用truncate,可参考通过临时表中转,将删除转换为CATS操作。
插入语句的性能提升点:
1.可以的话直接在高水位进行插入,即用append 提示可最大程度提升性能,但是适用场景受限。
2.插入处理可以适用DML并行提升性能。
更新语句的性能提升点:
1.更新的时候注意批量更新,减小IO消耗。
2.如果更新量大在大表中,初始化方式也可以选择用merge,但是要注意控制。
3.简单的大数据量更新,可以适用DML并行处理。
4.关联更新中如果有复杂的子查询,先将只查询落地。