Oracle数据库总结（二）——数据库优化

8.Oracle优化

8.1 几个重要的概念

（1）高水位

1）概念

所有的oracle段(segments) 都有一个在段内容纳数据的上限，我们把这个上限称为"high water mark"或HWM（高水位）。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块，原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使HWM很象一个水库的历史最高水位，这也就是HWM的原始含义，当然不能说一个水库没水了，就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令，则该表的HWM会被重新置为0。

2）HWM

数据库有如下操作影响：

全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。
即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。

（2）并发parallel

parallel为并行查询，并行查询允许将一个sql select语句划分为多个较小的查询，每个部分的查询并发地运行，然后将各个部分的结果组合起来，提供最终的结果，多用于全表扫描，索引全扫描等，大表的扫描和连接、创建大的索引、分区索引扫描、大批量插入更新和删除。

（3）append

在使用了append选项以后，insert数据会直接加到表的最后面，而不会在表的空闲块中插入数据，使用append会增加数据插入的速度。这个hint是针对整个sql，让数据库以直接加载的方式(direct load)将数据加载入库。这个提示不会检查当前是否有插入所需要的块空间，相反它会直接将数据添加到新块中。这样会浪费空间，但可以提高插入的性能。需要注意的是，数据将被存储在HWM之上的位置。

关于insert /*+ append */我们需要注意以下三点：

1）非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。

2）insert /*+ append */时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。

3）insert /*+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。

（4）静态sql和动态sql

Oracle编译PL/SQL程序块分为两个种：其一为前期联编（early binding），即SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期联编（late binding），即SQL语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常，静态SQL采用前一种编译方式，而动态SQL采用后一种编译方式。

Oracle中提供了Execute immediate语句来执行动态SQL，语法如下：

Excute immediate '动态SQL语句' using '绑定参数列表' returning into '输出参数列表'

8.2 基本sql语句优化

（1）建议不用*来代替所有列名

select语句中可以用*来列出某个表的所有列名，但是这样的写法对Oracle系统来说存在解析的动态问题。Oracle系统会通过查询数据字典将“*”转换成表的所有列名，这会消耗系统时间

（2）用truncate代替delete

当使用delete删除表中的数据行时，Oracle会使用撤销表空间来存放恢复的信息，而使用truncate时不会，所以当系统对表中所有行全部删除时，采用truncate命令更加有效。

（3）在确保完整性的情况下多用commit语句

这样可以释放事务所占的资源

（4）尽量减少表的查询次数

（5）用(not) exists代替(not) in

在子查询中，(not) in子句将执行一个内部的排序与合并， (not) in语句是最低效的，因为它对子查询中的表执行了一个全表遍历，(not) in只适用于结果集是可枚举的或子查询满足的结果集很小的情况。

（6）排序优化

1）多表：小表/关联表放from语句最后一个

2）where条件顺序：能过滤最大数量的条件放在最后面

8.3 delete、update、insert语句的优化

删除数据，修改数据，插入数据，是我们系统目前最影响系统性能的三大操作；但是我们系统关于这三个方面的操作都非常频繁，这三个操作不但影响性能，而且还是导致表和索引高水位的直接元凶；

1）delete优化

delete优化遵循原则是：如果被删除表的总数据量很大，比如份额明细，确认表之类，如果仅仅删除很少的数据量，可以用游标方式获取被删除数据进行删除操作，这种方式不占用系统资源，如果是删除数据量很大可以考虑直接并发delete 删除方式。

有个前提 delete ,update 使用并发方式必须手工开启并发，和结束之后手工关闭并发

alter session enable parallel dml;  
alter session disable parallel dml;

并发优化的效果：多批次清算，删除当日收益，数据量很大，性能提升明显，原有逻辑删除耗时一个小时，现在逻辑几分钟。

2）insert优化

insert 优化方式，对表进行数据插入操作，要确认插入多少数据量，被插入表本身数据量，以及被插入表本身包含的索引情况。总原则就是：尽量一次性批量插入，如果被操作表数据量很大，插入数据量也很大，考虑删除索引只用使用提示/*+append*/的方式操作，不然大数据量的插入带有索引的表的操作很容易卡死，综合考量加自测选择合适的插入方式。

3）update优化

update 优化方式和delete类似，无论是update，delete，insert，尽量考虑批量操作，这样能整体上避免很多由于被操作数据量的增加导致耗时不稳定耗时增加的问题。

4）delete，update，insert 频繁的操作会导致表的高水位，高水位两大影响，一是影响性能，二是影响空间，对于大表定期降低高水位作用非常有必要，降低高水位最常用的方法有重建表和数据泵备份恢复，这两个方法安全可靠。

重建表例子：

create table TableDemo_1 parallel 32 as select * from tsharedetail a;  
drop table TableDemo purge  --然后删除原表  
Rename TableDemo_1 to TableDemo;   
alter table TableDemo noparallel;   
--最后重建索引

8.4 索引优化

1.索引优化描述

sql语句中索引的作用非常巨大，有约一半的性能问题和索引因素有关，可能是缺少索全表扫描导致，可能是多余索引，不应该走索引而执行计划选择了索引路径。

（1）合理使用索引

创建主键和唯一索引的主要目的除了数据的完整性和一致性之外，还具有提高查询速度的作用。此外，创建一般索引的目的就是为了提高查询速度。

什么情况下应该选择索引执行路径，大前提是要确定，涉及查询的表的原有数据量，以及查询结果集的数据量，条件字段在表中是否具有作为索引字段的特性。比如该字段是唯一性数据，或者非常具有过滤定位结果集的字段，这样的前提下，如果涉及的sql语句结果集很少的情况下就尽量考虑索引。如果sql语句对应的结果集很大，和接近原表的三分之一以上，这种情况下尽量直接考虑全表扫描，避免出现索引的方式获取数据结果集。

1）何时使用索引

在利用索引的情况下，由于只从表中选择部分行，所以能够提高查询速度。对于只从总行数中查询2%~4%的表，可以考虑创建索引。下面是创建索引的基本原则：

表的主键、外键必须有索引，Oracle外键不加索引会导致死锁；
以查询关键字为基础，表中的行随机排序；
包含的列数相对比较少的表；
表中的大多数查询都包含相对简单的WHERE从句；
经常以查询关键字为基础的表，并且该表中的行遵从均匀分布；
缓存命中率低，并且不需要操作系统权限。

（2）索引列和表达式的选择

创建索引时选择索引列的原则：

where从句频繁使用的关键字
sql语句中频繁使用表连接的关键字，即经常与其他表进行连接的表，在连接字段上应该建立索引
可选择性高（重复性少的）关键，例如：表示性别的数据列，由于只有男女两种值，就属于选择性低
对于取值较少的关键字或表达式，不要采用标准的B树索引，可以考虑建立位图索引
不要将那些频繁修改的列作为索引列
不要使用包含操作符或函数的 where 从句中的关键字作为索引列，如果需要的话，可以考虑建立函数索引
如果大量并发的insert、update和delete语句访问了父表或者子表，则考虑使用完整性约束的外部键作为索引
在选择索引列时，还要考虑该索引所引起的insert、update和delete操作是否值得

（3）避免全表扫描大表

以下情况会进行全表扫描，应尽量避免：

所查询的表没有索引
需要返回所有的行
带like并使用'%'，这样的语句就是全表扫描
对索引主列有限制条件，但使用了函数
带有is null，is not null或 != 等字句也导致全表扫描

8.5 数据库分库分表优化

（原文地址：https://www.cnblogs.com/12lisu/p/15477072.html）

数据库分库分表的目的在于，减小数据库的单库单表负担，将压力分散到不同的库和表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用，此外，可以很大的缓解表锁的问题。

分库分表又分为垂直拆分和水平拆分。垂直方向主要针对的是业务，水平方向主要针对的是数据。

1.垂直拆分

（1）单库

在系统初期，业务功能相对来说比较简单，系统模块较少。为了快速满足迭代需求，减少一些不必要的依赖。更重要的是减少系统的复杂度，保证开发速度，我们通常会使用单库来保存数据。

系统初期的数据库架构如下：

此时，使用的数据库方案是：一个数据库包含多张业务表。用户读数据请求和写数据请求，都是操作的同一个数据库。

（2）分表

随着业务的发展，不断的添加新功能。导致单表中的字段越来越多，开始变得有点不太好维护了。一个用户表就包含了几十甚至上百个字段，管理起来有点混乱。

此时就需要分表，将用户表拆分为：用户基本信息表和用户扩展表。

用户基本信息表中存的是用户最主要的信息，比如：用户名、密码、别名、手机号、邮箱、年龄、性别等核心数据，这些信息跟用户息息相关，查询的频次非常高。而用户扩展表中存的是用户的扩展信息，比如：所属单位、户口所在地、所在城市等等，非核心数据，这些信息只有在特定的业务场景才需要查询，而绝大数业务场景是不需要的。

所以通过分表把核心数据和非核心数据分开，让表的结构更清晰，职责更单一，更便于维护。

除了按实际业务分表之外，我们还有一个常用的分表原则是：把调用频次高的放在一张表，调用频次低的放在另一张表。

有个非常经典的例子就是：订单表和订单详情表。

（3）分库

当系统功能非常完善时，需要按业务功能，划分不同领域了。把相同领域的表放到同一个数据库，不同领域的表，放在另外的数据库。具体拆分过程如下：

这样按领域拆分之后，每个领域只用关注自己相关的表，职责更单一了，一下子变得更好维护了。

（4）分库分表

有时候按业务，只分库，或者只分表是不够的。比如：有些财务系统，需要按月份和年份汇总，所有用户的资金。这就需要做：分库分表了。每年都有个单独的数据库，每个数据库中，都有12张表，每张表存储一个月的用户资金数据。

这样分库分表之后，就能非常高效的查询出某个用户每个月，或者每年的资金了。

此外，还有些比较特殊的需求，比如需要按照地域分库，比如：华中、华北、华南等区，每个区都有一个单独的数据库。

甚至有些游戏平台，按接入的游戏厂商来做分库分表。

2.水平拆分

（1）单库

在系统初期，由于用户非常少，所以系统并发量很小。并且存在表中的数据量也非常少。这时的数据库架构如下：

此时，使用的数据库方案同样是：一个master数据库包含多张业务表。用户读数据请求和写数据请求，都是操作的同一个数据库，该方案比较适合于并发量很低的业务场景。

（2）主从读写分离

当用户数量增加时，现用户的请求当中，读数据的请求占据了大部分，真正写数据的请求占比很少。众所周知，数据库连接是有限的，它是非常宝贵的资源。而每次数据库的读或写请求，都需要占用至少一个数据库连接。如果写数据请求需要的数据库连接，被读数据请求占用完了，不就写不了数据了？这样问题就严重了。

为了解决该问题，我们需要把读库和写库分开。于是，就出现了主从读写分离架构：

考虑刚开始用户量还没那么大，选择的是一主一从的架构，也就是常说的一个master一个slave。所有的写数据请求，都指向主库。一旦主库写完数据之后，立马异步同步给从库。这样所有的读数据请求，就能及时从从库中获取到数据了（除非网络有延迟）。如果主库挂了，可以升级从库为主库，将所有读写请求都指向新主库，系统又能正常运行了。

但这里有个问题就是：如果用户量确实有些大，如果master挂了，升级slave为master，将所有读写请求都指向新master。但此时，如果这个新master根本扛不住所有的读写请求，该怎么办？这就需要一主多从的架构了：