数据库性能优化与SQL查询技巧-优快云博客

优化调整级别

对于ORACLE数据库的数据存取，主要有四个不同的调整级别，
第一级调整是操作系统级包括硬件平台
CPU不够快
内存不够
IO读写太慢
网络负担太重
第二级调整是ORACLE RDBMS级的调整
数据块的大小
内存数据缓存区
日志缓存区
分区管理：将表分离在若干不同的表空间上，可以改善表的维护、查询性能
第三级是数据库设计级的调整
三范式
反规范化
第四级是SQL语句级。

通常依此四级调整级别对数据库进行调整、优化，数据库的整体性能会得到很大的改善。

反规范化

⑴反规范的必要性
是否规范化的程度越高越好呢？答案是否定的，应根据实际需要来决定，因为“分离”越深，产生的关系越多，结构越复杂。关系越多，

连接操作越频繁，而连接操作是最费时间的，在数据库设计中特别对以查询为主的数据库设计来说，频繁的连接会严重影响查询速度。所以，

在数据库的设计过程中有时故意保留非规范化约束，或者规范化以后又反规范，这样做通常是为了改进数据库的查询性能，加快数据库系统的响应速度。
⑵反规范技术
常用的反规范技术有合理增加冗余列、派生列，或分割表几种。反规范化的好处是降低连接操作的需求、降低外码和索引数目，减少表的个数，

从而提高查询速度，这对于性能要求相对较高的数据库系统来说，能有效地改善系统的性能，但相应的问题是可能影响数据的完整性，加快查询速度的同时降低修改速度。

反规范化技术

1. 分割表
分割表包括水平分割和垂直分割。
水平分割是按照行将一个表分割为多个表，这可以提高每个表的查询速度，但查询、更新时要选择不同的表，统计时要汇总多个表，

因此应用程序会更复杂。垂直分割是对于一个列很多的表，若某些列的访问频率远远高于其它列，就可以将主键和这些列作为一个表，

将主键和其它列作为另外一个表。

通过减少列的宽度，增加了每个数据页的行数，一次I/O就可以扫描更多的行，从而提高了访问每一个表的速度。但是由于造成了多表连接，

所以应该在同时查询或更新不同分割表中的列的情况比较少的情况下使用。
2. 保留冗余列
当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的列，以避免表之间的连接过于频繁。

由于对冗余列的更新操作必须对多个表同步进行，所以一般在冗余列的数据不经常变动的情况下使用。
3. 增加派生列
派生列是由表中的其它多个列计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间。
create view empnamephonepfx (empid,pfxVoice,fullname)
as select emplid, substring(phonevoice,5,3),rtrim(firstname)+rtrim(mdlInl)+rtrim(lastname) from emp

ORACLE 优化器

ORACLE的优化器共有2种: RULE (基于规则) COST (基于成本)

基于规则的优化Rule-Based
它查看查询、检查某种结构以及在where子句中索引列的使用等，然后使用这些信息绝对对数据的一种访问路径
基于成本的优化器Cost-Based
1、优化器在可能的执行路径及用户提示的基础下制定执行SQL语句的计划
2、根据表、聚簇和索引在数据字典中存放的统计信息和数据分布的统计信息，计算出每个计划的代价
3、比较各个计划的代价，取代价最低者来执行

访问Table的方式

ORACLE 采用两种访问表中记录的方式: 全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

通过ROWID访问表你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息.

.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法

,因此那些基于索引列的查询就可以得到性能上的提高.

用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构.

通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引.

同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.

除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护,

每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O .

因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢

SQL优化实例

选择最有效率的表名顺序(只在基于规则的优化器中有效)
WHERE子句中的连接顺序
SELECT子句中避免使用 *
减少访问数据库的次数
最高效的删除重复记录方法
用TRUNCATE替代DELETE
尽量多使用COMMIT
用Where子句替换HAVING子句
使用表的别名(Alias)
用EXISTS替代IN
用NOT EXISTS替代NOT IN 用表连接替换EXISTS
用EXPLAIN PLAN 分析SQL语句
避免在索引列上使用计算
用>=替代>
避免在索引列上使用IS NULL和IS NOT NULL
用UNION-ALL 替换UNION ( 如果有可能的话)
需要当心的WHERE子句

选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理.

在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们

.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),

最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录表 TAB2 1 条记录

选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间快选择TAB1作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间慢

WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,

那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 例如:
(低效)
SELECT * FROM EMP E WHERE SAL > 5000 AND JOB = ‘MANAGER’

AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
(高效)
SELECT * FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO) AND SAL > 5000 AND JOB = ‘MANAGER';

SELECT子句中避免使用 *

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.
不幸的是,这是一个非常低效的方法.
实际上,ORACLE在解析的过程中, 会将‘*’ 依次转换成所有的列名,
这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等.

由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.

例如, 以下有三种方法可以检索出雇员号等于0342或0291的职员.
方法1 (最低效)
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;
方法2 (次低效)
用游标
方法3 (高效)
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342
Or EMP_NO = 291;

最高效的删除重复记录方法 ( 因为使用了ROWID)

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

create table b ( col1 number, col2 number ) insert into b values(1,10);

insert into b values(2,20);

insert into b values(1,20); insert into b values(1,10);

delete from b where b.rowid>(select min(x.rowid) from b x where b.col1=x.col1 and b.col2=x.col2 )

关于ROWID

ROWID就是唯一标志记录物理位置的一个ID
Oracle的物理扩展ROWID有18位，每位采用64位编码，分别用A~Z、a~z、0~9、+、/共64个字符表示。

A表示0，B表示1，……Z表示25，a表示26，……z表示51，0表示52，……，9表示61，+表示62，/表示63

用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,

ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)

而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用