文档章节

数据库访问优化之三:减少交互次数

o
 osc_z1hvg4cu
发布于 2018/04/21 10:50
字数 3342
阅读 8
收藏 0

精选30+云产品,助力企业轻松上云!>>>

1、batch DML

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

假设要向一个普通表插入1000万数据,每条记录大小为1K字节,表上没有任何索引,客户端与数据库服务器网络是100Mbps,以下是根据现在一般计算机能力估算的各种batch大小性能对比值:

 

 单位:ms

No batch

Batch=10

Batch=100

Batch=1000

Batch=10000

服务器事务处理时间

0.1

0.1

0.1

0.1

0.1

服务器IO处理时间

0.02

0.2

2

20

200

网络交互发起时间

0.1

0.1

0.1

0.1

0.1

网络数据传输时间

0.01

0.1

1

10

100

小计

0.23

0.5

3.2

30.2

300.2

平均每条记录处理时间

0.23

0.05

0.032

0.0302

0.03002

 

从上可以看出,Insert操作加大Batch可以对性能提高近8倍性能,一般根据主键的Update或Delete操作也可能提高2-3倍性能,但不如Insert明显,因为Update及Delete操作可能有比较大的开销在物理IO访问。以上仅是理论计算值,实际情况需要根据具体环境测量。

2、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评估。

3、设置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次再取平均值:

fetchsize

 elapse_time(s)

1

20.516

2

11.34

4

6.894

8

4.65

16

3.584

32

2.865

64

2.656

128

2.44

256

2.765

512

3.075

1024

2.862

2048

2.722

4096

2.681

8192

2.715

 

 

 

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>

更多参考:JDBC读取数据优化-fetch size

4、使用存储过程

大型数据库一般都支持存储过程,合理的利用存储过程也可以提高系统性能

如你有一个业务需要将A表的数据做一些加工然后更新到B表中,但是又不可能一条SQL完成,这时你需要如下3步操作:

a:将A表数据全部取出到客户端;

b:计算出要更新的数据;

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

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

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

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

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

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

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

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

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

 

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

5、优化业务逻辑

要通过优化业务逻辑来提高性能是比较困难的,这需要程序员对所访问的数据及业务流程非常清楚。

举一个案例:

某移动公司推出优惠套参,活动对像为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的交互次数。

 

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

6、使用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();

}
View Code

以上代码实际执行时间为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);

         }

}
View Code

调整后的代码实际执行时间为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

    }

}
View Code

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

 

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
程序员数据库访问的优化的一些思考

一、数据库访问优化的五个法则 在实际开发,我们主要是需要对SQL语句进行优化,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?根据木桶原理可以知道,最慢的设备...

BravoZu
2014/03/04
208
0
MYSQL数据库访问性能优化之减少交互次数

一、批量提交 jdbc支持batch的提交处理方法,当一次性要往表中插入1000万条数据时,如果采用普通处理,交互次数为1000万次,按每秒钟可以向数据库服务器提交10000次估算,要完成所有工作需要...

osc_r1i0f044
2019/10/16
2
0
面向程序员的数据库访问性能优化法则

特别说明: 1、 本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识; 2、 本文许多示例及概念是基于Oracle数据库描述,对于其它关系型数据库也...

五味格子
2011/05/11
32
0
数据库访问性能优化(二)

1.2、只通过索引访问数据 有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的...

冷暖己知
2016/07/01
0
0
数据库优化方法整理(开发人员实用)

特别说明: 本文只是面对数据库应用开发的程序员,不适合专业 DBA ,DBA 在数据库性能优化方面需要了解更多的知识; 本文许多示例及概念是基于 Oracle 数据库描述,对于其它关系型数据库也可...

科比可比克
04/05
58
0

没有更多内容

加载失败,请刷新页面

加载更多

使用amoeba实现mysql读写分离

转载马士兵连老师笔记 使用amoeba实现mysql读写分离 1、什么是amoeba? Amoeba(变形虫)项目,专注 分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、...

兵荒马乱的青春
3分钟前
0
0
学Vue,就要学会vue JSX(二)

学习JSX,先了解一下createElement 提到JSX,不可避免的就要提到createElement,当你看完本节,你会发现,奇怪的知识又增多了。ok,我们接着上一部分继续讲。这一次的准备工作是了解createEleme...

osc_kurqu050
4分钟前
0
0
学Vue,就要学会vue JSX(三)

是时候使用JSX代替createElement了 接着上面的讲,当我们看到上面用createElement去实现组件,太麻烦了,别说工作效率提高了,就是那些嵌套可以嵌套正确就很赞了,所以我们需要用JSX去简化整...

osc_tq5hz9vv
5分钟前
0
0
protocol buffer使用

protocol buffer使用例子 protocol buffer是什么 https://developers.google.com/protocol-buffers 这是protocol buffer的官方网站,上边有详细的使用方式。 一般常见的序列和反序列方式就是...

RandomObject
6分钟前
0
0
小白的前端之路-HTMl

HTML——超文本标记语言 HTMl里面有标签,标签又分为单标签和双标签,也分为行级元素和块级元素 标签是用<>包裹起来的,而且必须要有<>,否则会直接显示在浏览器上面哦 现在介绍一下常用标签...

osc_e45irv7l
7分钟前
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部