•一、数据库优化法则

1、减少数据访问（减少硬盘访问）

2、返回更少数据（减少网络传输或磁盘访问）

3、减少交互次数（减少网络传输）

4、减少服务器CPU开销（减少CPU及内存开销）

5、利用更多的资源（增加资源）

•二、Oracle数据库两个基本概念

1、数据块(Block)

数据块是数据库中数在磁盘中存储的最小单位，也是一次IO访问的最小单位，一个数据块大小DBA在创建数据库或表空间时指定，可指定为2k、4K、8K、16K或32K字节，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块 。

2、ROWID

ROWID是每条记录在数据库中的唯一标识，通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号 。

•三、数据库访问优化法则详解

•1、减少数据访问

•1.1、创建并使用正确的索引

•索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍 。

•常见的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少。

•B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等。

•一个表中可以建多个索引，一个索引也可以由多个字段组成，称为组合索引。

•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（两个表通过索引字段关联）

•SQL什么条件不会使用索引？

•我们一般在什么字段上建索引？

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

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

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

•3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

•这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：

•小表(记录数小于10000行的表)：筛选比例<10%；

•大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

• 单条记录长度≈字段平均内容长度之和+字段数*2

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

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

•索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？

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

•索引对于Insert性能降低56%

•索引对于Update性能降低47%

•索引对于Delete性能降低29%

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

•只通过索引访问数据

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

•如：selectid,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表。

•切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。

•优化SQL执行计划

•SQL执行计划是关系型数据库最核心的技术之一，它表示SQL执行时的数据访问算法。由于业务需求越来越复杂，表数据量也越来越大，程序员越来越懒惰，SQL也需要支持非常复杂的业务逻辑，但SQL的性能还需要提高，因此，优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外，还需要有一套优秀的算法库来提高SQL性能。

•目前ORACLE有SQL执行计划的算法约300种，而且一直在增加，所以SQL执行计划是一个非常复杂的课题，一个普通DBA能掌握50种就很不错了，就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法，但并不表示我们无法优化执行计划，因为我们常用的SQL执行计划算法也就十几个，如果一个程序员能把这十几个算法搞清楚，那就掌握了80%的SQL执行计划调优知识。

•二、返回更少的数据

•1、数据分页处理：客户端分页(应用程序或者浏览器)、应用服务器分页、数据库SQL分页。

   客户端分页：

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

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

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

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

•应用服务器分页

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

     List list=executeQuery(“select

* from employee order by id”);

     Int count=list.size();

     ListsubList= list.subList(10,20);

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

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

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

•数据库SQL分页

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

       一个SQL计算总数量

       一个SQL返回分页后的数据

        优点：性能好

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

•2、只返回需要的字段

通过去除不必要的返回字段可以提高性能，例：

  调整前：select * from product where company_id=?;

  调整后：select id,name from product where company_id=?;

优点：

  1、减少数据在网络上传输开销

  2、减少服务器数据处理开销

  3、减少客户端内存占用

  4、字段变更时提前发现问题，减少程序BUG

  5、如果访问的所有字段刚好在一个索引里面，则可以使用纯索引访问提高性能。

  缺点：增加编码工作量

  由于会增加一些编码工作量，所以一般需求通过开发规范来要求程序员这么做，否则等项目上线后再整改工作量更大。

      如果你的查询表中有大字段或内容较多的字段，如备注信息、文件内容等等，那在查询表时一定要注意这方面的问题，否则可能会带来严重的性能问题。如果表经常要

查询并且请求大内容字段的概率很低，我们可以采用分表处理，将一个大表分拆成两个一对一的关系表，将不常用的大内容字段放在一张单独的表中。如一张存储上 传文件的表：

      T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）

     我们可以分拆成两张一对一的关系表：

     T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）

     T_FILECONTENT（ID, FILE_CONTENT）

  通过这种分拆，可以大大提少T_FILE表的单条记录及总大小，这样在查询T_FILE时性能会更好，当需要查询FILE_CONTENT字段内容时再访问T_FILECONTENT表。

•3、减少交互次数

•batch DML

          数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

•In List

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

       for :var in ids[] do begin

    select* frommytablewhere id=:var;

       end;

  我们也可以做一个小的优化， 如下所示，用ID

INLIST的这种方式写SQL：

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

     通过这样处理可以大大减少SQL请求的数量，从而提高性能。那如果有10000个ID，那是不是全部放在一条SQL里处理呢？答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制，如ORACLE的IN里就不允许超过1000个值。

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

•设置Fetch

Size

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

String  vsql = "select * from t_employee";

PreparedStatement pstmt = conn.prepareStatement(vsql);

pstmt.setFetchSize(1000);

iBatis的SqlMapping配置文件可以对每个SQL语句指定fetchsize大小，如下所示：

<select  id="getAllProduct"  resultMap="HashMap"  fetchSize="1000">

        select  *  from employee

</select>

•使用存储过程

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

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

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

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

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

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

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

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

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

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

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

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