SQL优化二(SQL性能调优)

一·、前言

这篇博文内容非原创，是我们公司的架构师给我们做技术培训的时候讲的内容，我稍微整理了下，借花献佛。这篇博文只是做一个大概的科普介绍，毕竟SQL优化的知识太大了，几乎可以用一本书来介绍。另外，博主对SQL优化也是刚刚接触，也有很多不了解的地方，说的不对的地方，还请大家指正，共勉！

二、ORACLE服务器

所谓oracle服务器指的是一个数据库管理系统，它包括一个oracle实例（动态）和一个oracle数据库（静态）。

 

oracle实例 是一个运行的概念，提供了一种访问数据库的方式，由SGA和一些后台服务进程组成，DBWn PMON CKPT LGWR SMON是必备的后台进程，而ad queue，rac，shared server，ad replication则是可选的。连接到oracle实例有三种途径:

 1、如果用户登陆到运行oracle实例的操作系统上，则通过进程间通信进行访问

 2、C/S结构访问

 3、三层结构

 

oracle数据库 是一个被统一处理的数据的集合，从物理角度来说包括三类文件，数据文件、控制文件、重做日志文件。

 

PMON 监控其他后台进程，并且在服务器进程或者转发器进程异常终止之后执行恢复。pmon负责清理数据库的buffer cache，并且释放客户端进程使用的资源。比如说pmon重置当前活动的事务表，释放不需要的locks，清理进程id（隐式回滚）

 

SMON 负责系统级别的清理工作

1.执行实例恢复。

2.恢复异常的transaction（实例恢复期间 file or tablespace被置为offline状态），smon会在他们置为online的时候执行恢复。

3.清理不使用的临时segments。比如当创建index的时候需要分配临时extent，如果操作失败，smon负责清理这些临时空间。

4.在使用字典管理表空间的时候合并连续的空闲extent。smon为定期监控。其他进程如果需要的话也会通知smon。

 

Database Writer Process (DBWn) 负责将更改的buffer 从db buffer cache中写到datafile中去，通过一个dbwn进程（dbw0）就足够了，但是也可以配置更多额外的dbwr进程，它可以提升频繁更改的数据库系统的性能。当然额外的dbw进程对于单处理器系统是没有任何用处的。

 

Log Writer Process (LGWR) 管理这redo log buffer。lgwr写buffer中连续的部分到online redo log 中。因为分离了更改数据库buffer的任务：dbwn散列写buffer到disk中，执行快速的顺序写到redo，所以数据库提升了性能。

1.用户提交了一个事务。

2. redo log switch 发生

3. 从上一次lgwr写操作开始已经过去了3秒

4. redo log buffer 三分之一满或者已经存储了1mb的数据量

5. dbwn必须写更改的数据到磁盘上面。

 

CKPT 更新控制文件以及数据文件头部的检查点信息，并且给dbwn信号去写数据块到磁盘上面。检查点信息包括：检查点位置，scn，恢复时开始的redo log 位置，类似这样的信息。

 

Recoverer Process (RECO) 在分布式数据库中，reco进程自动的解决分布式事务发生错误的情况。

三、分析语句阶段优化 

硬解析：SQL语句从用户进程提交到oracle，经过分析装载到共享SQL区域（shared pool）。如果SQL语句不在shared pool，需要进行语句解析，即硬解析。

软解析：如果SQL语句在shared pool，就可以直接进入执行阶段。

优化技巧1:语法分析需要耗费很多资源，要尽量避免进行语法分析，即硬解析。

优化技巧2:即软解析时，当Shared pool没有空间时，oracle会根据LRU算法(最近最少使用页面置换算法)更新SQL区域，所以适当增加shared_pool,可以存放更多解析后的SQL来提高效率。

 

Oracle Optimizer(查询优化器)：是Oracle在执行SQL之前分析语句的工具,Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行，主要有以下两种方式:

• RBO(rule-base optimizer):优化器遵循Oracle内部预定的规则,句法驱动和数据字典驱动。
• CBO(cost-based optimizer):依据语句执行的代价，主要指对CPU和内存的占用，优化器在判断是否使用CBO时，要参照表和索引的统计信息统计表驱动，统计信息要在对表做analyze后才会有。

优化技巧3:Oracle8及以后版本，推荐用CBO方式，Oracle10G此功能已经很强大。

Oracle优化器的优化模式主要有五种： 

• Choose：默认模式。根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式。
• Rule：基于规则优化，忽略任何统计信息
• First rows：与Choose类似。不同的是如果表有统计信息，它将以最快的方式返回查询结果，以获得最佳响应时间。
• First_rows_n：与Choose类似。不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。
• All rows：完全基于CBO的模式。当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。没有统计信息则使用RBO方式。

 Oracle Optimizer 运行级别怎么修改？

• Instance级:修改启动参数在init<SID>.ora文件中设定OPTIMIZER_MODE，需要数据库重启
• Session级:（JDBC或者Hibernate或者一次连接），通过alter session set optimizer_mode = value修改，忽略instance级
• Statement级:通过在SQL语句中加如Hint(隐语)实现,表明对语句块选择基于开销的优化方法,并获得最佳响应时间,忽略instance级和session级

SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 

DBMS_STATS包工具做CBO代价分析

DBMS_STATS：dbms_stats包下面一共有40多个存储过程. 对执行计划的生成非常重要。常见的有：

分析数据库（包括所有的用户对象和系统对象）：gather_database_stats
分析用户所有的对象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats
删除数据库统计信息：delete_database_stats
删除用户方案统计信息：delete_schema_stats
删除表统计信息：delete_table_stats
删除索引统计信息：delete_index_stats
删除列统计信息：delete_column_stats
设置表统计信息：set_table_stats
设置索引统计信息：set_index_stats
设置列统计信息：set_column_stats
可以查看表 DBA_TABLES来查看表是否与被分析过，如：
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES
通常使用的比较多的主要是DBMS_STATS.GATHER_TABLE_STATS和 DBMS_STATS.GATHER_INDEX_STATS。

使用步骤:

1、首先创建一个分析表，该表是用来保存之前的分析值。

SQL> begin
2 dbms_stats.create_stat_table(ownname => 'scott',stattab => 'STAT_TABLE');
3 end;
4 /

2、分析表信息。可以参考这篇博客

exec dbms_stats.gather_schema_stats(
ownname          => 'SCOTT',
options          => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt       => 'for all columns size repeat',
degree           => 15
)

3、将执行计划导入到STAT_TABLE中

exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ; 

 

4、查看执行计划表

select * from  stat_table; 

四、执行计划阶段优化

全表扫描(Full Table Scans)

Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件,采用多块读的方式使一次I/O能读取多块数据块，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，注意,只有全表扫描才能使用多块读的操作。

优化技巧4:通过设置db_block_multiblock_read_count和db_block_size来适当增加一次I/O可读的数据块。

优化技巧5:避免使用select * from 减少物理读，逻辑读（* 要走系统字典表，查看这张表有哪些字段）,最好制定需要返回的字段。

优化技巧6:较小的表使用全表扫描，效率更高；较大的表应避免全表扫描，除非涉及全表记录10%以上的查询；避免给记录数少的表建立索引，避免索引开销。

优化技巧7:指定过滤谓词 where，尽可能缩小查询范围（能过滤掉大部分记录的字段应该放在右边,因为sql语句是从右至左执行的）。

通过ROWID的表存取(Table Access by ROWID)

ROWID记录了记录行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，可以说是整个数据库都在用的索引，是Oracle存取单行数据的最快方法。这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。

select empno from emp where rowid='AAAR3sAAEAAAACXAAA';

索引扫描(Index Scan)

通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。

索引唯一扫描(index unique scan)

通过唯一索引查找一个数值经常返回单个ROWID,如果存在UNIQUE 或PRIMARY KEY 约束（约束只有一行记录匹配），Oracle实现索引唯一性扫描。

select empno from emp where empno=7369;

索引范围扫描(index range scan)

使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符，有以下三种情况会导致引起索引范围扫描:

• 在唯一索引列上使用了range操作符(> < <> >= <= between)
• 在组合索引上，只使用部分列进行查询，导致查询出多行
• 对非唯一索引列上进行的任何查询。

select empno from emp where empno>7369;

索引全扫描(index full scan)

什么时候会引起索引全扫描呢?当不使用谓词逻辑where;所有查询结果数据都必须从索引中可以直接得到;需要排序操作，比如order by。

 select empno from emp order by empno;

index full scan使用单块读方式有序读取索引块，产生db file sequential reads事件，当采用该方式读取大量索引全扫描，效率低下

索引快速扫描(index fast full scan)

与索引全扫描很相似，只是不涉及排序动作。

select /*+ index_ffs(emp pk_emp) */empno from emp; //对指定的表执行快速全索引扫描,而不是全表扫描的办法.  

index fast full scan使用多块读的方式读取索引块，产生db file scattered reads 事件，读取时高效，但为无序读取

优化技巧7:对于只从表中查询出总行数的2%到4%行的表时，可以考虑创建索引。
优化技巧8:不要将那些频繁修改的列作为索引列，频繁修改会导致不必要的索引开销。
优化技巧9:不要使用包含函数或操作符放入WHERE从句中的关键字作为索引，会导致索引失效，可以考虑使用函数索引。
优化技巧10:在组合索引中，没有按照建立时的索引关键字顺序描述，比如xyz变成了yxz，也会导致索引失效。
优化技巧11:如果在表中要建立索引的一列或多列上使用了函数或表达式，则创建的是基于函数的索引。基于函数的索引预先计算函数或表达式的值，并将结果存储在索引中。B树索引和bitmap索引也是函数索引
优化技巧12:排序动作能不做就不做，增加系统开销的同时还会使快速索引失效。

 备注:

#函数索引
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
# 则可以在查询语句中使用函数索引：
SELECT a
FROM table_1
WHERE a + b * (c - 1) < 100;

五、多表关联查询操作

任何N（N大于2）张表之间的操作都将转化为两张表之间的关联操作，查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合，也可以是表的部分行数据的集合，或者说集合筛选后的集合都成为row source。无论连接操作符如何，典型的连接类型共有3种：

排序合并连接(Sort Merge Join (SMJ))

select aa.CREATEPERSONNAME, bb.CREATEPERSON
from tbl_comm_commonticket aa, tbl_ybgz_ticket bb
where aa.CREATEPERSONNAME = bb.CREATEPERSON
order by aa.CREATEPERSONNAME, bb.CREATEPERSON

排序属于代价很高的操作，特别对于大表。因此经常避免使用排序合并连接方法，但是如果2个row source都已经预先排序（比如primary Key索引），则这种连接方法可以选用。

嵌套循环(Nested Loops (NL))

分为驱动表(OUTER TABLE)和内层表(INNER TABLE)。因为嵌套循环，所以外层循环的次数越少越好，因此一般将数据量较小表或满足条件的row source较小的表作为驱动表(用于外层循环)的理论依据。

select /*+USE_NL(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

嵌套循环返回已经连接的行，而不必等待所有的连接操作处理完才返回数据 ，所以提高了响应速度。如果OUTER TABLE比较小，并且在INNER TABLE上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。另外，这种连接方式，是在RBO优化器中。

哈希连接(Hash Join)

散列基本原理是：使用一个下标范围比较大的数组来存储元素。可以设计一个函数（哈希函数，也叫做散列函数），使得每个元素的关键字都与一个函数值（即数组下标，hash值）相对应，于是用这个数组单元来存储这个元素；但是，不能够保证每个元素的关键字与函数值是一一对应的，因此极有可能出现对于不同的元素，却计算出了相同的函数值，这样就产生了“冲突”，换句话说，就是把不同的元素分在了相同的“类”之中。 总的来说，“直接定址”与“解决冲突”是哈希表的两大特点。

散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。

hash join只有在CBO方式下可以使用；Oracle初始化参数HASH_JOIN_ENABLED决定是否启用hash join；pga_aggregate_target指定散列连接可用的内存大小；尽量使内层表生成的散列表最小，最好能够全部载入内存；主要用于等值连接。

select /*+USE_HASH(emp,dept)*/ * from emp,dept where emp.deptno=dept.deptno;

六、其他

优化技巧13:避免使用不确定操作符<>,!= 或者 where 子句中使用 or 来连接条件，因为会引起全表扫描; or 可以用union或者(union all) 代替。

select * from emp where empno>7369 union select * from emp where empno<7369;

优化技巧14:Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。
优化技巧15:当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用 。
优化技巧16:对数据类型不同的列进行比较时，会使索引失效。
优化技巧17:UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。

SQL> select deptno from emp union select deptno from dept;
SQL> select deptno from emp union all select deptno from dept;

优化技巧18:Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式。
优化技巧19:相同的Sql语句，要保证查询字符完全相同，大小写，空格位置，利用shared_pool，防止相同的Sql语句被多次分析，使用变量绑定。
优化技巧20:调整SQL语句的目的是为了在执行中使资源的使用减少到最小。除了选择使用不同的SQL语法来优化执行代价，还可以通过调整执行顺序优化SQL。
优化技巧21:Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。因此NOT EXISTS比NOT IN效率稍高，相应更快。但是(NOT) EXISTS 不等于(NOT) IN。

SQL>select * from emp where deptno in (select deptno from dept);
SQL>select * from emp where exists (select deptno from dept where emp.deptno=dept.deptno);

优化技巧22:可以多使用视图进行软解析，视图只是把你要用的sql进行保存而已，你需要担心的是视图中的sql会不会效率太低，而不用担心视图的耗时。
优化技巧23:适当的时候强制使用rule会获得更高效率；调试SQL时关注执行计划和执行代价。
优化技巧24:避免视图嵌套使用，尤其是针对视图排序，筛选等操作。
优化技巧25:不同版本数据库的执行计划差别可能很大。
优化技巧26:不是只有select..是查询，所有的DML操作都含有查询过程。

七、SQL分析工具

EXPLAIN PLAN

使用步骤：

1、SQL> explain plan for select * from emp,dept where emp.deptno=dept.deptno;

2、select * from table(dbms_xplan.display);

3、

AUTOTRACE

使用步骤:

1、set autotrace on (可能会报Cannot SET AUTOTRACE的错误，参考这篇博客解决)

2、select * from emp,dept where emp.deptno=dept.deptno;

3、