- 浏览: 56691 次
文章分类
最新评论
mybatis SQL性能调优
MybatisSQL性能调优
1.Mapper层参数为Map,由Service层负责重载
Mapper由于机制的问题,不能重载,参数一般设置成Map,但这样会使参数变得模糊,如果想要使代码变得清晰,可以通过service层来实现重载的目的,对外提供的Service层是重载的,但这些重载的Service方法其实是调同一个Mapper,只不过相应的参数并不一致。
也许有人会想,为什么不在Service层也设置成Map呢?我个人是不推荐这么做的,虽然为了方便,我在之前的项目中也大量采用了这种方式,但很明显会给日后的维护工作带来麻烦。因为这么做会使你整个MVC都依赖于Map模型,这个模型其实是很不错的,方便搭框架,但存在一个问题:仅仅看方法签名,你不清楚Map中所拥有的参数个数、类型、每个参数代表的含义。
试想,你只对Service层变更,或者DAO层变更,你需要清楚整个流程中Map传递过来的参数,除非注释或者文档良好,否则必须把每一层的代码都了解清楚,你才知道传递了哪些参数。针对于简单MVC,那倒也还好,但如果层次复杂之后,代码会变得异常复杂,而且如果我增加一个参数,需要把每一个层的注释都添加上。相对于注释,使用方法签名来保证这种代码可控性会来得更可行一些,因为注释有可能是过时的,但方法签名一般不太可能是陈旧的。
2.尽量少用ifchoose等语句
Mybatis的配置SQL时,尽量少用ifchoose等标签,能用SQL实现判断的尽量用SQL来判断(CASEWHEN,DECODE等),以便后期维护。否则,一旦SQL膨胀,超级恶心,如果需要调试Mybatis中的SQL,需要去除大量的判断语句,非常麻烦。另一方面,大量的if判断,会使生成的SQL中包含大量的空格,增加网络传输的时间,也不可取。
而且大量的ifchoose语句,不可避免地,每次生成的SQL会不太一致,会导致ORACLE大量的硬解析,也不可取。
我们来看看这样的SQL:
SELECT*FROMT_NEWS_TEXTWHERE1=1
<choose>
<iftest="startdate!=nullandstartdate!=''andenddate!=nullandendate!=''">
ANDPUBLISHTIME>=#{startdate}ANDPUBLISHTIME<=#{enddate}
</if>
<otherwise>
ANDPUBLISHTIME>=SYSDATE-7ANDPUBLISHTIME<=SYSDATE
</otherwise></choose>
这样的if判断,其实是完全没有必要的,我们可以很简单的采用DECODE来解决默认值问题:
SELECT*FROMT_NEWS_TEXTWHEREPUBLISHTIME>=DECODE(#{startdate},NULL,SYSDATE-7,#{startdate})ANDPUBLISHTIME<=DECODE(#{enddate},NULL,SYSDATE,#{enddate})
当然有人会想,引入CASEWHEN,DECODE会导致需要ORACLE函数解析,会拖慢SQL执行时间,有兴趣的同学可以回去做一下测试,看看是否会有大的影响。就个人经验而言,在我的开发过程,没有发现因为函数解析导致SQL变慢的情形。影响SQL执行效率的一般情况下是JOIN、ORDERBY、DISTINCT、PARTITATIONBY等这些操作,这些操作一般与表结构设计有很大的关联。相对于这些的效率影响程度,函数解析对于SQL执行速度影响应该是可以忽略不计的。
另外一点,对于一些默认值的赋值,像上面那条SQL,默认成当前日期什么的,其实可以完全提到Service层或Controller层做处理,在Mybatis中应该要少用这些判断。因为,这样的话,很难做缓存处理。如果startdate为空,在SQL上使用动态的SYSDATE,就无法确定缓存startdate日期的key应该是什么了。所以参数最好在传递至Mybatis之前都处理好,这样Mybatis层也能减少部分ifchoose语句,同时也方便做缓存处理。
当然不使用ifchoose也并不是绝对的,有时候为了优化SQL,不得不使用if来解决,比如说LIKE语句,当然一般不推荐使用LIKE,但如果存在使用的场景,尽可能在不需要使用时候去除LIKE,比如查询文章标题,以提高查询效率。最好的方式是使用lucence等搜索引擎来解决这种全文索引的问题。
总的来说,if与choose判断分支是不可能完全去除的,但是推荐使用SQL原生的方式来解决一些动态问题,而不应该完全依赖Mybatis来完成动态分支的判断,因为判断分支过于复杂,而且难以维护。
3.用XML注释取代SQL注释
Mybatis中原SQL的注释尽量不要保留,注释会引发一些问题,如果需要使用注释,可以在XML中用<!---->来注释,保证在生成的SQL中不会存在SQL注释,从而降低问题出现的可能性。这样做还有一个好处,就是在IDE中可以很清楚的区分注释与SQL。
现在来谈谈注释引发的问题,我做的一个项目中,分页组件是基于Mybatis的,它会在你写的SQL脚本外面再套一层SELECTCOUNT(*)ROWNUM_FROM(....)计算总记录数,同时有另一个嵌套SELECT*FROM(...)WHEREROWNUM>10ANDRONNUM<10*2这种方式生成分页信息,如果你的脚本中最后一行出现了注释,则添加的部分会成为注释的一部分,执行就会报错。除此之外,某些情况下也可能导致部分条件被忽略,如下面的情况:
SELECT*FROMTESTWHERECOL1>1--这里是注释<iftest="a!=nullanda!=''">ANDCOL2=#{a}</if>
即使传入的参数中存在对应的参数,实际也不会产生效果,因为后面的内容实际上是被完全注释了。这种错误,如果不经过严格的测试,是很难发现的。一般情况下,XML注释完全可以替代SQL注释,因此这种行为应该可以禁止掉。
4.尽可能使用#{},而不是${}
Mybatis中尽量不要使用${},尽量这样做很方便开发,但是有一个问题,就是大量使用会导致ORACLE的硬解析,拖慢数据库性能,运行越久,数据库性能会越差。
select*fromT_PRINT_LAYOUTwhereD_RECID=${recId}
最后生成的SQL为:
select*fromT_PRINT_LAYOUTwhereD_RECID=1
即:直接将参数值替换到了原来${recId}的位置,相当于硬拼SQL
select*fromT_PRINT_LAYOUTwhereD_RECID=#{recid,jdbcType=DECIMAL}
最后生成的SQL为:
select*fromT_PRINT_LAYOUTwhereD_RECID=?
即:#{...}被识别为一个SQL参数
关于${},另一个误用的地方就是LIKE,我这边还有个案例:比如一些树型菜单,节点会设计成'01','0101',用两位节点来区分层级,这时候,如果需要查询01节点下所有的节点,最简单的SQL便是:SELECT*FROMTREEWHEREIDLIKE'01%',这种SQL其实无可厚非,因为它也能用到索引,所以不需要特别的处理,直接使用就行了。但如果是文章标题,则需要额外注意了:SELECT*FROMT_NEWS_TEXTWHERETITLELIKE'%OSC%',这是怎么也不会用到索引的,上面说了,最好采用全文检索。但如果离不开LIKE,就需要注意使用的方式:IDLIKE#{ID}||'%'而不是IDLIKE'${ID}%',减少硬解析的可能。
有人觉得使用||会增加ORACLE处理的时间,我觉得不要把ORACLE看得太傻,虽然有时候确实非常傻,有空可以再总结ORACLE傻不垃圾的地方,但是稍加测试便知:这种串联方式,对于整个SQL的解析执行,应该是微乎其微的。
当然还有一些特殊情况是没有办法处理的,比如说动态注入列名、表名等。对于这些情况,则比较棘手,没有找到比较方便的手段。由于这种情况出现的可能性会比较少,所以使用${}倒也不至于有什么太大的影响。当然你如果有代码洁癖的话,可以使用ORACLE的动态执行SQL的机制Executeimmediate,这样就可以完全避免${}出现的可能性了。这样会引入比较复杂的模型,这个时候,你就需要取舍了。
针对于以上动态SQL所导致的问题,最激进的方式是全部采用存储过程,用数据库原生的方式来解决,方便开发调试,当然也会带来问题:对开发人员会有更高的要求、存储过程的管理等等,我这边项目没有采用过这种方式,这里不做更多的展开。
5.大量数据的批量处理(变量绑定)
大量数据(条数>10000)做insert时,如果按常规方式,每条insertintotable(...)values(...);来提交,速度巨慢。改善性能的思路是多条insert批量提交。
oracle环境中,有一种批量insert的小技巧,原理是insertinto...selectfrom...,套在mybatis上,变形为:
INSERTINTOT_TEST
(ID,COL_A,COL_B)
SELECTSEQ_TEST.NEXTVAL,A.*
FROM(
SELECT'A1','B1'FROMDUAL
UNIONALLSELECT'A2','B2'FROMDUAL
UNIONALLSELECT'A3','B3'FROMDUAL
UNIONALLSELECT'A4','B4'FROMDUAL
UNIONALLSELECT'A5','B5'FROMDUAL
UNIONALLSELECT'A6','B6'FROMDUAL
)A
中间的部分非常有规律,可以用foreach标签生成,参考下面的片段
<insertid="insertBatch2"parameterType="ctas.entity.SharkFlt">
<selectKeykeyProperty="recId"order="BEFORE"resultType="Long">
selectSEQ_CTAS_SHARK_FLT.nextvalasrecIdfromdual
</selectKey>
insertintoCTAS_SHARK_FLT(<includerefid="Base_Column_List"/>)SELECTSEQ_TEST.NEXTVAL,A.*
FROM(
<foreachcollection="list"item="item"index="index"open=""close=""separator="unionall">
select#{item.awbType,jdbcType=VARCHAR},#{item.awbPre,jdbcType=VARCHAR},...fromdual
</foreach>
)A
</insert>
即使这样,也不能直接run,oracle中一次执行的sql语句长度是有限制的,如果最后拼出来的sql字符串过长,会导致执行失败,所以java端还要做一个分段处理,参考下面的处理:
List<SharkFlt>data=newArrayList<SharkFlt>();
for(TSharkFltf:sharkFlts){
data.add(getSharkFlt(f));
}
System.out.println(data.size());
longbeginTime=System.currentTimeMillis();
System.out.println("开始插入...");
SqlSessionFactorysqlSessionFactory=ctx.getBean(SqlSessionFactory.class);
SqlSessionsession=null;
try{
session=sqlSessionFactory.openSession(ExecutorType.BATCH,false);
inta=2000;//每次提交2000条
intloop=(int)Math.ceil(data.size()/(double)a);
List<SharkFlt>tempList=newArrayList<SharkFlt>(a);
intstart,stop;
for(inti=0;i<loop;i++){
tempList.clear();
start=i*a;
stop=Math.min(i*a+a-1,data.size()-1);
System.out.println("range:"+start+"-"+stop);
for(intj=start;j<=stop;j++){
tempList.add(data.get(j));
}
session.insert("ctas.importer.writer.mybatis.mappper.SharkFltMapper.insertBatch2",tempList);
session.commit();
session.clearCache();
System.out.println("已经插入"+(stop+1)+"条");
}
}catch(Exceptione){
e.printStackTrace();
session.rollback();
}finally{
if(session!=null){
session.close();
}
}
longendTime=System.currentTimeMillis();
System.out.println("插入完成,耗时"+(endTime-beginTime)+"毫秒!");
标绿的这几行是关键,这一段逻辑会经常使用,为了重用,可以封装一下:
/**
*批量提交数据
*@paramsqlSessionFactory
*@parammybatisSQLIdSQL语句在MapperXML文件中的ID
*@paramcommitCountEveryTime每次提交的记录数
*@paramlist要提交的数据列表
*@paramlogger日志记录器
*/
private<T>voidbatchCommit(SqlSessionFactorysqlSessionFactory,StringmybatisSQLId,intcommitCountEveryTime,List<T>list,Loggerlogger){
SqlSessionsession=null;
try{
session=sqlSessionFactory.openSession(ExecutorType.BATCH,false);
intcommitCount=(int)Math.ceil(list.size()/(double)commitCountEveryTime);
List<T>tempList=newArrayList<T>(commitCountEveryTime);
intstart,stop;
LongstartTime=System.currentTimeMillis();
for(inti=0;i<commitCount;i++){
tempList.clear();
start=i*commitCountEveryTime;
stop=Math.min(i*commitCountEveryTime+commitCountEveryTime-1,list.size()-1);
for(intj=start;j<=stop;j++){
tempList.add(list.get(j));
}
session.insert(mybatisSQLId,tempList);
session.commit();
session.clearCache();
}
LongendTime=System.currentTimeMillis();
logger.debug("batchCommit耗时:"+(endTime-startTime)+"毫秒");
}catch(Exceptione){
logger.error("batchCommiterror!",e);
e.printStackTrace();
session.rollback();
}finally{
if(session!=null){
session.close();
}
}
}
对应的,如果是批量update,也是类似的思路,只不过要注意一点:oracle环境中,多条语句提交的sql语句为
begin
updatexxxsetxxx=xxx;
updatexxxsetxxx=xxx;
end;
用mytais拼的时候,参考下面的写法:
<updateid="updateBatch"parameterType="java.util.List">
<foreachcollection="list"item="item"index="index"open="begin"close="end;">updatexxxsetx=#{item.x,jdbcType=VARCHAR}wherex=#{item.x,jdbcType=VARCHAR};</foreach>
</update>
关于批量提交的性能,Oracle环境下,我大概测试了一下:
insertinto...selectxxx
unionallselectyyy
unionallselectzzz;
最快,其次是
begin
insertinto...values...;
insertinto...values...;
end;
当然最慢是逐条insert提交,最后谈下Spring与mybatis集成后,AOP事务管理对批量提交的影响,通常情况下,我们会这样配置AOP事务管理:
<tx:adviceid="txAdvice"transaction-manager="transactionManager">
<tx:attributes>
<tx:methodname="do*"read-only="false"rollback-for="java.lang.Exception"/>
<tx:methodname="*"propagation="SUPPORTS"read-only="true"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcutid="pc"expression="execution(*ctas.service.*.*(..))"/>
<aop:advisorpointcut-ref="pc"advice-ref="txAdvice"/>
</aop:config>
这样,ctas.service(及子包)下的所有方法都被拦截,而且只有do开头的方法,具有可写的事务(即:能insert/update/delete记录),而其它方法是只读事务(即:只能select数据),但是我们前面谈到的批量提交操作,都是写代码手动提交的,不需要spring管理,所以配置中需要将某些方法排除,可以约定self开头的方法,由开发者自己管理事务,不需要spring代为管理,上面的配置要改成:
<aop:config>
<aop:pointcutid="pc"expression="execution(*ctas.service.*.*(..))and!execution(*ctas.service.*.self*(..)))"/>
<aop:advisorpointcut-ref="pc"advice-ref="txAdvice"/>
</aop:config>
通过and!execution(...)将self开头的方法排除就可以了,前面的批量操作代码写到selfXXX方法中。
关于批量提交,还有一种情况:父子表的批量插入。思路还是一样的,但是SQL的写法有点区别,原理参考下面的语句(Oracle环境),注意标绿的变量
DECLARE
BASE_IDINTEGER;
DETAIL_IDINTEGER;
BEGIN
--第1组记录
SELECTSEQ_T_BASE.NEXTVALINTOBASE_IDFROMDUAL;
INSERTINTOT_BASE(ID,FEE)VALUES(BASE_ID,?);
SELECTSEQ_T_DETAIL.NEXTVALINTODETAIL_IDFROMDUAL;
INSERTINTOT_DETAIL(ID,BASE_ID,FEE)VALUES(DETAIL_ID,BASE_ID,?);
SELECTSEQ_T_DETAIL.NEXTVALINTODETAIL_IDFROMDUAL;
INSERTINTOT_DETAIL(ID,BASE_ID,FEE)VALUES(DETAIL_ID,BASE_ID,?);
--第2组记录
SELECTSEQ_T_BASE.NEXTVALINTOBASE_IDFROMDUAL;
INSERTINTOT_BASE(ID,FEE)VALUES(BASE_ID,?);
SELECTSEQ_T_DETAIL.NEXTVALINTODETAIL_IDFROMDUAL;
INSERTINTOT_DETAIL(ID,BASE_ID,FEE)VALUES(DETAIL_ID,BASE_ID,?);
SELECTSEQ_T_DETAIL.NEXTVALINTODETAIL_IDFROMDUAL;
INSERTINTOT_DETAIL(ID,BASE_ID,FEE)VALUES(DETAIL_ID,BASE_ID,?);
--...
END;
xml映射文件中的写法:
<insertid="insertBatch"parameterType="java.util.List">
DECLARE
base_idINTEGER;
detail_idINTEGER;
<foreachcollection="list"item="item"index="index"open="begin"close="end;">
selectseq_t_base.nextvalintobase_idfromdual;
insertintot_base(id,fee)values(base_id,#{item.baseEntity.fee,jdbcType=DECIMAL});
<foreachcollection="item.details"item="detail"index="index">
selectseq_t_detail.nextvalintodetail_idfromdual;
insertintot_detail(id,base_id,fee)values(detail_id,base_id,#{detail.fee,jdbcType=DECIMAL});
</foreach>
</foreach>
</insert>
publicclassBaseDetailDto{
privateTBasebaseEntity;
privateList<TDetail>details;
publicTBasegetBaseEntity(){
returnbaseEntity;
}
publicvoidsetBaseEntity(TBasebaseEntity){
this.baseEntity=baseEntity;
}
publicList<TDetail>getDetails(){
returndetails;
}
publicvoidsetDetails(List<TDetail>details){
this.details=details;
}
}
相关推荐
包括MyBatis-Plus提供的批量更新,JdbcTemplate提供的批量更新,在xml中循环拼接sql批量更新、case when语句批量更新、replace into方式批量更新、ON DUPLICATE KEY UPDATE批量更新。 适用于对Spring Boot和数据库...
包括MyBatis-Plus提供的批量更新,JdbcTemplate提供的批量更新,在xml中循环拼接sql批量更新、case when语句批量更新、replace into方式批量更新、ON DUPLICATE KEY UPDATE批量更新。 适用于对Spring Boot和数据库...
各类图表的展示(折线图,饼图,直方图等),使用了layui的弹出层、菜单、文件上传、富文本编辑、日历、选项卡、数据表格等 Oracle关系型数据库以及非关系型数据库(Redis),Oracle 性能调优(PL/SQL语言,SQL查询...
Oracle关系型数据库以及非关系型数据库(Redis),Oracle 性能调优(PL/SQL语言,SQL查询优化,存储过程等),用Redis做中间缓存,缓存数据 实现异步处理,定时任务,整合Quartz Job以及Spring Task 邮件管理功能...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
JVM性能调优 JVM类加载机制详解 JVM内存模型 类字节码文件深度剖析 垃圾收集机制详解 十种垃圾收集器详解 JVM调优工具详解 GC日志详细分析 JVM调优实战 Mysql性能调优 SQL执行原理详解 索引底层剖析 执行计划与SQL...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...
- MyBatis - Hibernate - Maven、Gradle - Git、SVN - IntelliJ IDEA、Eclipse 6. 微服务: - RESTful API - Spring Cloud Netflix - Docker、Kubernetes 7. 缓存: - Redis - Memcached 8. 消息...