面向程序员的数据库访问性能优化法则5

面向程序员的数据库访问性能优化法则5
2011年05月18日
　　3.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中的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 * from employee
　　3.4、使用存储过程
　　大型数据库一般都支持存储过程，合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中，但是又不可能一条SQL完成，这时你需要如下3步操作：
　　a：将A表数据全部取出到客户端；
　　b：计算出要更新的数据；
　　c：将计算结果更新到B表。
　　如果采用存储过程你可以将整个业务逻辑封装在存储过程里，然后在客户端直接调用存储过程处理，这样可以减少网络交互的成本。
　　当然，存储过程也并不是十全十美，存储过程有以下缺点：
　　a、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。
　　b、学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。
　　c、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。
　　d、存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。
　　e、如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。
　　f、为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。
　　个人观点：普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。
　　3.5、优化业务逻辑
　　要通过优化业务逻辑来提高性能是比较困难的，这需要程序员对所访问的数据及业务流程非常清楚。
　　举一个案例：
　　某移动公司推出优惠套参，活动对像为VIP会员并且2010年1，2，3月平均话费20元以上的客户。
　　那我们的检测逻辑为：
　　select avg(money) as avg_money from bill wherephone_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 wherephone_no='13988888888' and date between '201001' and '201003';
　　if avg_money>20 then
　　begin  select vip_flag from member wherephone_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 billwhere phone_no='13988888888' and date between '201001' and '201003';
　　if avg_money>20 then
　　begin    执行套参();  end;end;这样就只有1%的VIP会员才会做检测平均话费，最终大大减少了SQL的交互次数。
　　以上只是一个简单的示例，实际的业务总是比这复杂得多，所以一般只是高级程序员更容易做出优化的逻辑，但是