数据库优化方法整理（开发人员实用）「建议收藏」

很多程序员总是问如何学习数据库优化，有没有好的教材之类的问题。在书店也看到了许多数据库优化的专业书籍，但是感觉更多是面向 DBA 或者是 PL/SQL 开发方面的知识，个人感觉不太适合普通程序员。而要想做到数据库优化的高手，不是花几周，几个月就能达到的，这并不是因为数据库优化有多高深，而是因为要做好优化一方面需要有非常好的技术功底，对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识，另一方面是需要花大量时间对特定的数据库进行实践测试与总结。

作为一个程序员，我们也许不清楚线上正式的服务器硬件配置，我们不可能像DBA那样专业的对数据库进行各种实践测试与总结，但我们都应该非常了解我们 SQL 的业务逻辑，我们清楚SQL 中访问表及字段的数据情况，我们其实只关心我们的SQL是否能尽快返回结果。那程序员如何利用已知的知识进行数据库优化？如何能快速定位 SQL 性能问题并找到正确的优化方向？

面对这些问题，笔者总结了一些面向程序员的基本优化法则，本文将结合实例来坦述数据库开发的优化知识。

数据库访问优化法则简介

法则说明

要正确的优化 SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们 SQL 主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是 2Mbps，硬盘是每分钟 7200 转等等。因此，为了快速找到 SQL 的性能瓶颈点，我们也需要了解我们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。

指标说明

从图上可以看到基本上每种设备都有两个指标： 延时（响应时间）：表示硬件的突发处理能力； 带宽（吞吐量）：代表硬件持续处理能力。

从上图可以看出，计算机系统硬件性能从高到代依次为： CPU——Cache(L1-L2-L3)——内存——SSD 硬盘——网络——硬盘

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

1. CPU 及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；
2. 网络：结果数据传输、SQL 请求、远程数据库访问；
3. 硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

优化法则

根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则：

这个优化法则归纳为 5 个层次：

1. 减少数据访问（减少磁盘访问）
2. 返回更少数据（减少网络传输或磁盘访问）
3. 减少交互次数（减少网络传输）
4. 减少服务器CPU开销（减少CPU及内存开销）
5. 利用更多资源（增加资源）

由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低,优化效果也是最大的。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。

优化效果和成本说明

以下是每个优化法则层级对应优化效果及成本经验参考：

接下来，我们针对5种优化法则列举常用的优化手段并结合实例分析。

Oracle 数据库两个基本概念

数据块(Block)

数据块是数据库中数据在磁盘中存储的最小单位，也是一次IO访问的最小单位，一个数据块通常可以存储多条记录，数据块大小是DBA在创建数据库或表空间时指定，可指定为2K、4K、8K、 16K 或 32K 字节。下图是一个 Oracle 数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块；

ROWID

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

数据库访问优化法则详解

减少数据访问

创建并使用正确的索引

数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的 DBA 也不一定能完全做到最优。 索引会大大增加表记录的 DML(INSERT,UPDATE,DELETE) 开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。

索引常见问题

索引有哪些种类？ 常见的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用,B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，

以下是 B-TREE 索引的简单介绍：

B-TREE 索引也称为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE 索引的内容包括根节点、分支节点、叶子节点。

叶子节点内容：索引字段内容+表记录 ROWID 根节点，

分支节点内容：当一个数据块中不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树的顺序及各层级间的引用关系。

普通的BTREE索引结构示意图如下所示：

如果我们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示： 

图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。

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

代码100分

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

代码100分查询条件	不能使用索引原因
INDEX_COLUMN <> ?
INDEX_COLUMN not in (?,?,...,?)	不等于操作不能使用索引
function(INDEX_COLUMN) = ?
INDEX_COLUMN + 1 = ?
INDEX_COLUMN || "a" = ? 经过普通运算或函数运算后的索引字段不能使用索引
INDEX_COLUMN like "%"||?
INDEX_COLUMN like "%"||?||"%" 含前导模糊查询的Like语法不能使用索引
INDEX_COLUMN is null	B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引
NUMBER_INDEX_COLUMN="12345"
CHAR_INDEX_COLUMN=12345

Oracle 在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。。

特别说明

经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。 有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引 如：我们 company 表建了一个 id+name 的组合索引，以下 SQL 是不能使用索引的

Select * from company where name=?

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

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

1. 字段出现在查询条件中，并且查询条件可以使用索引；
2. 语句执行频率；
3. 通过字段条件可筛选的记录集很小，区分度比较高，比如name字段、而性别字段就不适合创建索引。
4. 主要大字段谨慎创建索引

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

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

索引对 DML(INSERT,UPDATE,DELETE) 附加的开销有多少？ 这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：

1. 索引对于 Insert 性能降低 56%
2. 索引对于 Update 性能降低 47%
3. 索引对于 Delete 性能降低 29% 因此对于写 IO 压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。

覆盖索引

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

代码100分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 表。 还是拿字典举例：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有 1000 页，拼音目录有 20 页，那我们的数据访问成本相当于全表访问的 50 分之一。

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

返回更少的数据

数据分页处理

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

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

1. 优点：编码简单，减少客户端与应用服务器网络交互次数
2. 缺点：首次交互时间长，占用客户端内存

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

应用服务器分页

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

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

1. 优点：编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。
2. 缺点：总数据量较多时性能较差。 适应场景：数据库系统不支持分页处理，数据量较小并且可控。

数据库 SQL 分页

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

1. 一个 SQL 计算总数量
2. 一个 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);

只返回需要的字段

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

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

优点：

1. 减少数据在网络上传输开销
2. 减少服务器数据处理开销
3. 减少客户端内存占用
4. 字段变更时提前发现问题，减少程序 BUG
5. 如果访问的所有字段刚好在一个索引里面，则可以使用纯索引访问提高性能。

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

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

减少交互次数

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 * from mytable where id=:var;
end;

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

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

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

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

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

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

设置 Fetch Size

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

以下是jdbc测试的代码，采用本地数据库，表缓存在数据库 CACHE 中，因此没有网络连接及磁盘 IO 开销，客户端只遍历游标，不做任何处理，这样更能体现 fetch 参数的影响：

String vsql ="select * from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(1000);
ResultSet rs = pstmt.executeQuery(vsql);
int cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
    for (int i = 1; i <= cnt; i++) {
       o = rs.getObject(i);
    }
}

测试示例中的 employee 表有 100000 条记录，每条记录平均长度 135 字节

以下是测试结果，对每种 fetchsize 测试 5 次再取平均值：

Oracle jdbc fetchsize 默认值为10，由上测试可以看出 fetchsize 对性能影响还是比较大的，但是当 fetchsize 大于100 时就基本上没有影响了。fetchsize 并不会存在一个最优的固定值，因为整体性能与记录集大小及硬件平台有关。

根据测试结果建议当一次性要取大量数据时这个值设置为100左右，不要小于 40 。注意， fetchsize 不能设置太大，如果一次取出的数据大于 JVM 的内存会导致内存溢出，所以建议不要超过 1000，太大了也没什么性能提高，反而可能会增加内存溢出的危险。

注：图中 fetchsize 在 128 以后会有一些小的波动，这并不是测试误差，而是由于 resultset 填充到具体对像时间不同的原因，由于 resultset 已经到本地内存里了，所以估计是由于 CPU 的 L1,L2 Cache 命中率变化造成，由于变化不大，所以笔者也未深入分析原因。

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

<select id="getAllProduct" resultMap="HashMap" fetchSize="1000">
select * from employee
</select>

使用存储过程

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

1. 将A表数据全部取出到客户端；
2. 计算出要更新的数据；
3. 将计算结果更新到 B 表。

如果采用存储过程你可以将整个业务逻辑封装在存储过程里，然后在客户端直接调用存储过程处理，这样可以减少网络交互的成本。 当然，存储过程也并不是十全十美，存储过程有以下缺点：

1. 不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。
2. 学习成本高， DBA 一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。
3. 业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。
4. 存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。
5. 如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。
6. 为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于 java 的 class 文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障( Oracle 数据库就存在这样的问题)。

个人建议

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

优化业务逻辑

要通过优化业务逻辑来提高性能是比较困难的，这需要程序员对所访问的数据及业务流程非常清楚。 举一个案例： 某移动公司推出优惠套参，活动对像为 VIP 会员并且 2010 年1，2，3月平均话费 20 元以上的客户。 那我们的检测逻辑为：

select avg(money) as avg_money from bill where phone_no="13988888888" and date between "201001" and "201003";
select vip_flag from member where phone_no="13988888888";
if avg_money>20 and vip_flag=true then
begin
  执行套参();
end;

如果我们修改业务逻辑为:

select avg(money) as  avg_money from bill where phone_no="13988888888" and date between "201001" and "201003";
if avg_money>20 then
begin
  select vip_flag from member where phone_no="13988888888";
  if vip_flag=true then
  begin
    执行套参();
  end;
end;

通过这样可以减少一些判断 vip_flag 的开销，平均话费 20 元以下的用户就不需要再检测是否 VIP 了。

如果程序员分析业务， VIP 会员比例为1%，平均话费 20 元以上的用户比例为90%，那我们改成如下：

select vip_flag from member where phone_no="13988888888";
if vip_flag=true then
begin
  select avg(money) as avg_money from bill where phone_no="13988888888" and date between "201001" and "201003";
  if avg_money>20 then
  begin
    执行套参();
  end;
end;

这样就只有1%的 VIP 会员才会做检测平均话费，最终大大减少了 SQL 的交互次数。

以上只是一个简单的示例，实际的业务总是比这复杂得多，所以一般只是高级程序员更容易做出优化的逻辑，但是我们需要有这样一种成本优化的意识。

使用 ResultSet 游标处理记录

现在大部分Java框架都是通过 jdbc 从数据库取出数据，然后装载到一个 list 里再处理，list 里可能是业务 Object ，也可能是 hashmap 。 由于JVM内存一般都小于4G，所以不可能一次通过sql把大量数据装载到list里。为了完成功能，很多程序员喜欢采用分页的方法处理，如一次从数据库取 1000 条记录，通过多次循环搞定，保证不会引起 JVM Out of memory 问题。

以下是实现此功能的代码示例，t_employee 表有 10 万条记录，设置分页大小为 1000：

d1 = Calendar.getInstance().getTime(); vsql = "select count(*) cnt from t_employee";
pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
         cnt = rs.getInt("cnt");
}
Integer lastid=0;
Integer pagesize=1000;
System.out.println("cnt:" + cnt);
String vsql = "select count(*) cnt from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
         cnt = rs.getInt("cnt");
}
Integer lastid = 0;
Integer pagesize = 1000;
System.out.println("cnt:" + cnt);
for (int i = 0; i <= cnt / pagesize; i++) {
         vsql = "select * from (select * from t_employee where id>? order by id) where rownum<=?";
         pstmt = conn.prepareStatement(vsql);
         pstmt.setFetchSize(1000);
         pstmt.setInt(1, lastid);
         pstmt.setInt(2, pagesize);
         rs = pstmt.executeQuery();
         int col_cnt = rs.getMetaData().getColumnCount();
         Object o;
         while (rs.next()) {
                   for (int j = 1; j <= col_cnt; j++) {
                            o = rs.getObject(j);                    }
                   lastid = rs.getInt("id");
         }
         rs.close();          pstmt.close(); }

以上代码实际执行时间为 6.516 秒

很多持久层框架为了尽量让程序员使用方便，封装了 jdbc 通过 statement 执行数据返回到 resultset 的细节，导致程序员会想采用分页的方式处理问题。实际上如果我们采用 jdbc 原始的 resultset 游标处理记录，在 resultset 循环读取的过程中处理记录，这样就可以一次从数据库取出所有记录。显著提高性能。

这里需要注意的是，采用 resultset 游标处理记录时，应该将游标的打开方式设置为 FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)，否则会把结果缓存在 JVM 里，造成 JVM Out of memory 问题。

代码示例：

String vsql ="select * from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(100);
ResultSet rs = pstmt.executeQuery(vsql);
int col_cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
         for (int j = 1; j <= col_cnt; j++) {
                   o = rs.getObject(j);          }
}

调整后的代码实际执行时间为 3.156 秒

从测试结果可以看出性能提高了1倍多，如果采用分页模式数据库每次还需发生磁盘 IO 的话那性能可以提高更多。 iBatis 等持久层框架考虑到会有这种需求，所以也有相应的解决方案，在 iBatis 里我们不能采用 queryForList 的方法，而应用该采用 queryWithRowHandler 加回调事件的方式处理，如下所示：

MyRowHandler myrh=new MyRowHandler();
sqlmap.queryWithRowHandler("getAllEmployee", myrh);
 
class MyRowHandler implements RowHandler {
    public void handleRow(Object o) {
       //todo something
    }
}

iBatis的 queryWithRowHandler 很好的封装了 resultset 遍历的事件处理，效果及性能与 resultset 遍历一样，也不会产生 JVM 内存溢出。

减少数据库服务器 CPU 运算

使用绑定变量

绑定变量是指 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 解析性能，不使用绑定变更我们一般称为硬解析，

使用绑定变量为何会提高 SQL 解析性能，这个需要从数据库 SQL 执行原理说明，一条 SQL 在 Oracle 数据库中的执行过程如下图所示：

1. 当一条 SQL 发送给数据库服务器后，系统首先会将SQL字符串进行hash运算，得到 hash 值后再从服务器内存里的 SQL 缓存区中进行检索，如果有相同的 SQL 字符，并且确认是同一逻辑的SQL语句，则从共享池缓存中取出 SQL 对应的执行计划，根据执行计划读取数据并返回结果给客户端。
2. 如果在共享池中未发现相同的 SQL 则根据 SQL 逻辑生成一条新的执行计划并保存在 SQL 缓存区中，然后根据执行计划读取数据并返回结果给客户端。
3. 为了更快的检索 SQL 是否在缓存区中，首先进行的是 SQL 字符串 hash 值对比，如果未找到则认为没有缓存，如果存在再进行下一步的准确对比，所以要命中 SQL 缓存区应保证 SQL 字符是完全一致，中间有大小写或空格都会认为是不同的 SQL 。
4. 如果我们不采用绑定变量，采用字符串拼接的模式生成 SQL ,那么每条 SQL 都会产生执行计划，这样会导致共享池耗尽，缓存命中率也很低。

一些不使用绑定变量的场景：

1. 数据仓库应用，这种应用一般并发不高，但是每个SQL执行时间很长， SQL 解析的时间相比 SQL 执行时间比较小，绑定变量对性能提高不明显。数据仓库一般都是内部分析应用，所以也不太会发生 SQL 注入的安全问题。
2. 数据分布不均匀的特殊逻辑，如产品表，记录有 1 亿，有一产品状态字段，上面建有索引，有审核中，审核通过，审核未通过3种状态，其中审核通过 9500 万，审核中 1 万，审核不通过 499 万。 要做这样一个查询：

select count(*) from product where status=?

采用绑定变量的话，那么只会有一个执行计划，如果走索引访问，那么对于审核中查询很快，对审核通过和审核不通过会很慢；如果不走索引，那么对于审核中与审核通过和审核不通过时间基本一样； 对于这种情况应该不使用绑定变量，而直接采用字符拼接的方式生成 SQL ，这样可以为每个 SQL 生成不同的执行计划，如下所示。

select count(*) from product where status="approved"; //不使用索引
select count(*) from product where status="tbd"; //不使用索引
select count(*) from product where status="auditing";//使用索引

合理使用排序

Oracle 的排序算法一直在优化，但是总体时间复杂度约等于 nLog(n) 。普通 OLTP 系统排序操作一般都是在内存里进行的，对于数据库来说是一种 CPU 的消耗.

曾在 PC 机做过测试，单核普通 CPU 在1秒钟可以完成100万条记录的全内存排序操作，所以说由于现在 CPU 的性能增强，对于普通的几十条或上百条记录排序对系统的影响也不会很大。

但是当你的记录集增加到上万条以上时，你需要注意是否一定要这么做了，大记录集排序不仅增加了 CPU 开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降，这种需求需要与 DBA 沟通再决定，取决于你的需求和数据，所以只有你自己最清楚，而不要被别人说排序很慢就吓倒。 以下列出了可能会发生排序操作的SQL语法：

Order by
Group by
Distinct
Exists子查询
Not Exists子查询
In子查询
Not In子查询
Union（并集），Union All也是一种并集操作，但是不会发生排序，如果你确认两个数据集不需要执行去除重复数据操作，那请使用Union All 代替Union。
Minus（差集）
Intersect（交集）
Create Index

减少比较操作

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

Like 模糊查询，如下所示：

a like ‘%abc%’

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

大量复杂运算在客户端处理

什么是复杂运算，一般我认为是一秒钟 CPU 只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、 3DES 及 BASE64 数据加密算法等等。 如果有大量这类函数运算，尽量放在客户端处理，一般 CPU 每秒中也只能处理1万-10万次这样的函数运算，放在数据库内不利于高并发处理。

利用更多的资源

客户端多进程并行访问

多进程并行访问是指在客户端创建多个进程(线程)，每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢。所以使用这种方式最好与 DBA 或系统管理员进行沟通后再决定是否采用。

例如： 我们有 10000 个产品 ID，现在需要根据 ID 取出产品的详细信息，如果单线程访问，按每个 IO 要 5ms 计算，忽略主机 CPU 运算及网络传输时间，我们需要 50s 才能完成任务。如果采用 5 个并行访问，每个进程访问 2000 个 ID，那么 10s 就有可能完成任务。 那是不是并行数越多越好呢，开 1000 个并行是否只要 50ms 就搞定，答案肯定是否定的，当并行数超过服务器主机资源的上限时性能就不会再提高，如果再增加反而会增加主机的进程间调度成本和进程冲突机率。

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

1. 如果瓶颈在服务器主机，但是主机还有空闲资源，那么最大并行数取主机 CPU 核数和主机提供数据服务的磁盘数两个参数中的最小值，同时要保证主机有资源做其它任务。
2. 如果瓶颈在客户端处理，但是客户端还有空闲资源，那建议不要增加 SQL 的并行，而是用一个进程取回数据后在客户端起多个进程处理即可，进程数根据客户端 CPU 核数计算。
3. 如果瓶颈在客户端网络，那建议做数据压缩或者增加多个客户端，采用 map reduce 的架构处理。
4. 如果瓶颈在服务器网络，那需要增加服务器的网络带宽或者在服务端将数据压缩后再处理了。

数据库并行处理

数据库并行处理是指客户端一条 SQL 的请求，数据库内部自动分解成多个进程并行处理，如下图所示：

并不是所有的 SQL 都可以使用并行处理，一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问，所以需要指定 SQL 并行的提示，如下所示：

select /*+parallel(a,4)*/ * from employee;

并行的优点： 使用多进程处理，充分利用数据库主机资源（ CPU,IO ），提高性能。 并行的缺点：

1. 单个会话占用大量资源，影响其它会话，所以只适合在主机负载低时期使用；
2. 只能采用直接 IO 访问，不能利用缓存数据，所以执行前会触发将脏缓存数据写入磁盘操作。

总结

数据库优化是工作中必备技能、在工作中要有这样的意识去提高自己的编码水平，这篇文章主要是从实用性的角度总结了一些优化的方法，具体的业务还是需要具体分析，实际学习过程中需要举一反三，了解原理。

最后如果觉得这篇文章对你有作用，请关注一下公众号，后面也会慢慢的分享一些工作中的其他内容。