Oracle优化的方法

古城微笑少年丶 2021-06-26 16:06 533阅读 0赞

想做到数据库优化的高手，不是花几周，几个月就能达到的，这并不是因为数据库优化有多高深，而是因为要做好优化一方面需要有非常好的技术功底，对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识，另一方面是需要花大量时间对特定的数据库不断的进行实践测试与总结。

针对数据库的优化，可以已`Oracle`为基点，从Oracle外部因素和`Oracle`本身的性能两部分考虑。

## 一、Oracle的外部因素： ##

Oracle的外部因素包括`CPU，cache L1，L2，L3,` 内存，网卡，普通硬盘/SSD硬盘。 这些硬件基于生产条件的限制，也就存在了相应的数据处理的瓶颈。

下面是外部因素，对数据I/O处理的性能指标：  
![在这里插入图片描述][2018122822563718.png]  
通过图片可以看出来，有两个指标：

延时（响应时间）：表示硬件处理突发事件的反应能力。

带宽（吞吐量）：代表硬件的持续处理能力。

根据数据库知识，我们可以列出每种硬件主要的工作内容：

CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；

网络：结果数据传输、SQL请求、远程数据库访问（`dblink`）；

硬盘/SSD硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

从上图可以看出，计算机系统硬件性能从高到低依次为：

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

可见`SSD`硬盘性能的平均值远远高于普通硬盘。值得一提的是，阿里办公环境全部为`MAC`系统，而`MAC`系统就是`SSD`硬盘。

下面给出优化建议：

从实际意义上来讲，根据需求，数据访问的需求只会越来越大，通过SQL语句减少访问量的做法，对大数据而言，效果提升也是有限的

对于Oracle以外的因素，可以从两点进行考虑，第一就是更换服务器的硬件设施，将普通硬盘换成`SSD`固态硬盘，第二点也就是基于分布式的思想，增加多台计算机充当服务器对数据进行处理。

## 二、从Oracle内部的自身性能进行优化 ##

从Oracle内部自身考虑的话，可以从如下5个方向考虑：

1.  减少数据访问 ---- 索引的使用 　　　　　　　　　　（减少磁盘访问）
2.  返回更少的数据　　----分页的应用　　　　　　　　　（减少网络传输或磁盘访问）
3.  减少交互次数　　---- 减少`I/O`的访问，逻辑上的优化 （减少网络传输）
4.  减少数据库服务器`CPU`运算　　----增加客户端的运算　（减少CPU及内存开销）
5.  利用更多的资源　---- 分布式的尝试　　　　　　　　（增加资源）

### 1、减少数据访问 ###

#### 1.1、正确认识索引，创建索引 ####

索引会大大增加表记录的`DML(INSERT,UPDATE,DELETE)`开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。

如果查询需求特别巨大，同时满足一次插入，偶尔修改，不删除，这样的情况下可以充分考虑增加数据库索引，来提高查询性能

#### 1.2、SQL什么条件会使用索引？ ####

当字段上建有索引时，通常以下情况会使用索引：

INDEX_COLUMN = ?
    
    　　　　　　INDEX_COLUMN > ?
    
    　　　　　　INDEX_COLUMN >= ?
    
    　　　　　　INDEX_COLUMN < ?
    
    　　　　　　INDEX_COLUMN <= ?
    
    　　　　　　INDEX_COLUMN between ? and ?
    
    　　　　　　INDEX_COLUMN in (?,?,...,?)
    
    　　　　　　INDEX_COLUMN like ?||'%'（后导模糊查询）
    
    　　　　　　T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）

#### 1.3、SQL什么条件不会使用索引？ ####

<table> 
 <thead> 
  <tr> 
   <th align="left">查询条件</th> 
   <th align="left">不能使用索引原因</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>INDEX_COLUMN &lt;&gt; ?</code> <br> <code>INDEX_COLUMN not in (?,?,...,?)</code></td> 
   <td align="left">不等于操作不能使用索引</td> 
  </tr> 
  <tr> 
   <td align="left"><code>function(INDEX_COLUMN) = ?</code> <br><code>INDEX_COLUMN + 1 = ?</code> <br> <code>INDEX_COLUMN || 'a' = ?</code></td> 
   <td align="left">经过普通运算或函数运算后的索引字段不能使用索引</td> 
  </tr> 
  <tr> 
   <td align="left"><code>INDEX_COLUMN like '%'||?</code> <br> <code>INDEX_COLUMN like '%'||?||'%'</code></td> 
   <td align="left">含前导模糊查询的<code>Like</code>语法不能使用索引</td> 
  </tr> 
  <tr> 
   <td align="left"><code>INDEX_COLUMN is null</code></td> 
   <td align="left"><code>B-TREE</code>索引里不保存字段为<code>NULL</code>值记录，因此<code>IS NULL</code>不能使用索引</td> 
  </tr> 
  <tr> 
   <td align="left"><code>NUMBER_INDEX_COLUMN='12345'</code> <br> <code>CHAR_INDEX_COLUMN=12345</code></td> 
   <td align="left">Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。</td> 
  </tr> 
  <tr> 
   <td align="left"><code>a.INDEX_COLUMN=a.COLUMN_1</code></td> 
   <td align="left">给索引查询的值应是已知数据，不能是未知字段值。</td> 
  </tr> 
 </tbody> 
</table>

有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引

如：我们`company`表建了一个`id+name`的组合索引，以下SQL是不能使用索引的

Select * from company where name=?

因为ID一般都会被设为主键，而主键已经起到了唯一约束的作用，不需要再设为索引。

#### 1.4、我们一般在什么字段上建索引？ ####

这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

1、字段出现在查询条件中，并且查询条件可以使用索引；

2、语句执行频率高，一天会有几千次以上；

以下是一些字段是否需要建`B-TREE`索引的经验分类：

<table> 
 <thead> 
  <tr> 
   <th align="left"></th> 
   <th align="left">字段类型</th> 
   <th align="left">常见字段名</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">需要建索引的字段</td> 
   <td align="left">主键</td> 
   <td align="left">ID,PK</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">外键</td> 
   <td align="left">PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">有对像或身份标识意义字段</td> 
   <td align="left">HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO</td> 
  </tr> 
  <tr> 
   <td align="left">索引慎用字段,需要进行数据分布及使用场景详细评估</td> 
   <td align="left">日期</td> 
   <td align="left">GMT_CREATE,GMT_MODIFIED</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">年月</td> 
   <td align="left">YEAR,MONTH</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">状态标志</td> 
   <td align="left">PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">类型</td> 
   <td align="left">ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">区域</td> 
   <td align="left">COUNTRY,PROVINCE,CITY</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">操作人员</td> 
   <td align="left">CREATOR,AUDITOR</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">数值</td> 
   <td align="left">LEVEL,AMOUNT,SCORE</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">长字符</td> 
   <td align="left">ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT</td> 
  </tr> 
  <tr> 
   <td align="left">不适合建索引的字段</td> 
   <td align="left">描述备注</td> 
   <td align="left">DESCRIPTION,REMARK,MEMO,DETAIL</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">大字段</td> 
   <td align="left">FILE_CONTENT,EMAIL_CONTENT</td> 
  </tr> 
 </tbody> 
</table>

#### 1.5、如何知道SQL是否使用了正确的索引？ ####

简单SQL可以根据索引使用语法规则判断，复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用，需要到数据库中查看SQL真实的执行计划，这个话题比较复杂，详见SQL执行计划专题介绍。

#### 1.6、索引对DML(`INSERT,UPDATE,DELETE`)附加的开销有多少？ ####

这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：

索引对于`Insert`性能降低56%  
索引对于`Update`性能降低47%  
索引对于`Delete`性能降低29%

因此对于**写IO压力**比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。

如果不涉及增删改，可以考虑使用索引

#### 1.7、如何创建索引 ####

有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。

如：`select id,name from company where type='2';`

如果这个SQL经常使用，我们可以在`type,id,name`上创建组合索引

create index my_comb_index on company(type,id,name);

有了这个组合索引后，SQL就可以直接通过`my_comb_index`索引返回数据，不需要访问`company`表。

### 2、返回更少的数据 ###

一般指的是分页技术

#### 2.1、客户端(应用程序或浏览器)分页 ####

\-----缺点大于优点，不建议使用

将数据从应用服务器全部下载到本地应用程序或浏览器，在应用程序或浏览器内部通过本地代码进行分页处理

优点：编码简单，减少客户端与应用服务器网络交互次数

缺点：首次交互时间长，占用客户端内存

适应场景：客户端与应用服务器网络延时较大，但要求后续操作流畅，如手机`GPRS`，超远程访问（跨国）等等。

#### 2.2、应用服务器分页 ####

\--------- 很少有数据库系统不支持分页，但是在服务器中进行分页的效率与在`Oracle`中进行分页的效率对比，目前还未知

将数据从数据库服务器全部下载到应用服务器，在应用服务器内部再进行数据筛选。以下是一个应用服务器端`Java`程序分页的示例：

List list=executeQuery(“select * from employee order by id”);
    
    Int count= list.size();
    
    List subList= list.subList(10, 20);

优点：编码简单，只需要一次`SQL`交互，总数据与分页数据差不多时性能较好。

缺点：总数据量较多时性能较差。

适应场景：数据库系统不支持分页处理，数据量较小并且可控。

#### 2.3、数据库SQL分页 ####

\---------目前来看性能最优

采用数据库`SQL`分页需要两次`SQL`完成

一个SQL计算总数量

一个SQL返回分页后的数据

优点：性能好

缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。

`oracle`数据库一般采用`rownum`来进行分页，常用分页语法有如下两种：

直接通过`rownum`分页：

select * from (
    
             select a.*,rownum rn from
    
                       (select * from product a where company_id=? order by status) a
    
             where rownum<=20)
    
    where rn>10;

数据访问开销=`索引IO`\+`索引全部记录结果对应的表数据IO`

采用`rowid`分页语法

优化原理是通过纯索引找出分页记录的`ROWID`，再通过`ROWID`回表返回数据，要求内层查询和排序字段全在索引里。

create index myindex on product(company_id,status);
    
    select b.* from (
    
             select * from (
    
                       select a.*,rownum rn from
    
                                (select rowid rid,status from product a where company_id=? order by status) a
    
                       where rownum<=20)
    
             where rn>10) a, product b
    
    where a.rid=b.rowid;

数据访问开销=`索引IO`\+`索引分页结果对应的表数据IO`

实例：

一个公司产品有1000条记录，要分页取其中20个产品，假设访问公司索引需要50个IO，2条记录需要1个表数据IO。

那么按第一种`ROWNUM`分页写法，需要`550(50+1000/2)`个IO，按第二种`ROWID`分页写法，只需要60个IO(50+20/2);

### 3、减少交互次数 ###

数据的增删改查四个操作中，插入和查找普遍存在大批量的操作，较少交互次数也是主要针对这两个操作。

但是，一般项目中都是存在一次插入多次查询，甚至千万次查询，所以，这里还是主要考虑减少查询的交互次数。

#### 3.1 使用 `in List` ####

很多时候我们需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库，如下所示：

for :var in ids[] do begin
    
      select * from mytable where id=:var;
    
    end;

我们也可以做一个小的优化， 如下所示，用`ID IN LIST`的这种方式写SQL：

select * from mytable where id in(:id1,id2,...,idn);

通过这样处理可以大大减少SQL请求的数量，从而提高性能。在优化中 n个ID放到一个`List`中，这样查询的交互数量只有1。

那如果有10000个ID，那是不是全部放在一条SQL里处理呢？答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制，如`ORACLE`的`IN`里就不允许超过1000个值。

另外当前数据库一般都是采用基于成本的优化规则，当IN数量达到一定值时有可能改变SQL执行计划，从索引访问变成全表访问，这将使性能急剧变化。随着SQL中IN的里面的值个数增加，SQL的执行计划会更复杂，占用的内存将会变大，这将会增加服务器CPU及内存成本。

评估在IN里面一次放多少个值还需要考虑应用服务器本地内存的开销，有并发访问时要计算本地数据使用周期内的并发上限，否则可能会导致内存溢出。

综合考虑，一般IN里面的值个数超过20个以后性能基本没什么太大变化，也特别说明不要超过100，超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本。

#### 3.2、设置`Fetch Size` ####

当我们采用`select`从数据库查询数据时，数据默认并不是一条一条返回给客户端的，也不是一次全部返回客户端的，而是根据客户端`fetch_size`参数处理，每次只返回`fetch_size`条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时，可以加大`fetch_size`，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。

#### 3.3、使用存储过程 ####

大型数据库一般都支持存储过程，合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中，但是又不可能一条SQL完成，这时你需要如下3步操作：

a：将A表数据全部取出到客户端；

b：计算出要更新的数据；

c：将计算结果更新到B表。

如果采用存储过程你可以将整个业务逻辑封装在存储过程里，然后在客户端直接调用存储过程处理，这样可以减少网络交互的成本。

当然，存储过程也并不是十全十美，存储过程有以下缺点：

a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。

b、学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。

c、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。

d、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。

e、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。

f、为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24\*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。

个人观点：普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。

### 4、减少数据库服务器CPU运算 ###

#### 4.1、绑定变量的使用 ####

绑定变量是指SQL中对变化的值采用变量参数的形式提交，而不是在SQL中直接拼写对应的值。

非绑定变量写法：`Select * from employee where id=1234567`

绑定变量写法：

Select * from employee where id=?
    
    Preparestatement.setInt(1,1234567)

Java中`Preparestatement`就是为处理绑定变量提供的对像，绑定变量有以下优点：

1、防止SQL注入

2、提高SQL可读性

3、提高SQL解析性能，不使用绑定变更我们一般称为硬解析，使用绑定变量我们称为软解析。

第1和第2点很好理解，做编码的人应该都清楚，这里不详细说明。关于第3点，到底能提高多少性能呢，下面举一个例子说明：

假设有这个这样的一个数据库主机：

2个4核CPU

100块磁盘，每个磁盘支持IOPS为160

业务应用的SQL如下：

select * from table where pk=?

这个SQL平均4个IO（3个索引IO+1个数据IO）

IO缓存命中率75%（索引全在内存中，数据需要访问磁盘）

SQL硬解析CPU消耗：1ms （常用经验值）

SQL软解析CPU消耗：0.02ms（常用经验值）

是否使用绑定变量

CPU支持最大并发数

磁盘IO支持最大并发数

不使用

2*4*1000=8000

100\*160=16000

使用

2*4*1000/0.02=400000

100\*160=16000

从以上计算可以看出，不使用绑定变量的系统当并发达到8000时会在CPU上产生瓶颈，当使用绑定变量的系统当并行达到16000时会在磁盘IO上产生瓶颈。所以如果你的系统CPU有瓶颈时请先检查是否存在大量的**硬解析**操作。

#### 4.2、减少比较操作 ####

我们SQL的业务逻辑经常会包含一些比较操作，如`a=b`，`a<b`之类的操作，对于这些比较操作数据库都体现得很好，但是如果有以下操作，我们需要保持警惕：

`Like`模糊查询，如下所示：

a like ‘%abc%’

`Like`模糊查询对于数据库来说不是很擅长，特别是你需要模糊检查的记录有上万条以上时，性能比较糟糕，这种情况一般可以采用专用Search或者采用全文索引方案来提高性能。

不能使用索引定位的大量`In List`，如下所示：

a in (:1,:2,:3,…,:n)   ----n>20

如果这里的a字段不能通过索引比较，那数据库会将字段与`in`里面的每个值都进行比较运算，如果记录数有上万以上，会明显感觉到SQL的CPU开销加大，这个情况有两种解决方式：

a、 将in列表里面的数据放入一张中间小表，采用两个表`Hash Join`关联的方式处理；

b、 采用`str2varList`方法将字段串列表转换一个临时表处理，关于`str2varList`方法可以在网上直接查询，这里不详细介绍。

#### 4.3、大量复杂运算在客户端进行 ####

什么是复杂运算，一般认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、`3DES`及`BASE64`数据加密算法等等。

如果有大量这类函数运算，尽量放在客户端处理，一般CPU每秒中也只能处理1万-10万次这样的函数运算，放在数据库内不利于高并发处理

### 5、利用更多资源 ###

#### 5.1、客户端多进程访问 ####

\-----通常意义上的分布式

多进程并行访问是指在客户端创建多个进程(线程)，每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢。

例如：

我们有10000个产品ID，现在需要根据ID取出产品的详细信息，如果单线程访问，按每个IO要5ms计算，忽略主机CPU运算及网络传输时间，我们需要50s才能完成任务。如果采用5个并行访问，每个进程访问2000个ID，那么10s就有可能完成任务。

以下是一些如何设置并行数的基本建议：

如果瓶颈在服务器主机，但是主机还有空闲资源，那么最大并行数取主机CPU核数和主机提供数据服务的磁盘数两个参数中的最小值，同时要保证主机有资源做其它任务。

如果瓶颈在客户端处理，但是客户端还有空闲资源，那建议不要增加SQL的并行，而是用一个进程取回数据后在客户端起多个进程处理即可，进程数根据客户端CPU核数计算。

如果瓶颈在客户端网络，那建议做数据压缩或者增加多个客户端，采用`map reduce`的架构处理。

如果瓶颈在服务器网络，那需要增加服务器的网络带宽或者在服务端将数据压缩后再处理了。

总结：  
1、减少数据访问 ---- 索引的使用  
2、返回更少的数据　　----分页的应用、也是减少I/O的访问  
3、减少交互次数　　---- 减少I/O的访问，逻辑上的优化  
4、减少数据库服务器CPU运算　　----增加客户端的运算  
5、利用更多的资源　---- 分布式的尝试  
6、主体思想保证服务器到不了并发瓶颈，在这个前提下，尝试优化  
7、常用的优化策略首选索引，其次是交互次数和分页技术的使用  
8、在数据量极其庞大的前提下，首选分布式处理方式

[2018122822563718.png]: /images/20210626/98520125c7d141e7862c05f9c9c18059.png