系统优化总结（1）----MySql批处理

随着业务的蓬勃发展，我们的服务器日均访问量从年初的二三十万增加到现在的800万左右，对系统的原有架构和性能都是一个很大的挑战，所以最近对系统做了一次较大的优化升级来应对日益增长的服务器压力，对原来的业务逻辑和代码都做了重构，性能优化方面遵循了一个重要的原则：尽量减少请求与数据直接交互的次数和频度，其中一个重要的手段就是MySql批处理

这次重点对文章浏览模块进行了优化，这也是目前最大的流量入口，每天都有大量的文章浏览请求，每次有效浏览都对应一次用户加分操作和一次加分log记录，这样就会有频繁的数据库交互，也影响响应速度，所以加分和log记录在异步处理的基础上都进行了批处理。

1.MySql批量写入
MySQL批量写入语法是：
INSERT INTO table (field1,field2,field3) VALUES (“a”,”b”,”c”), (“a1”,”b1”,”c1”),(“a2”,”b2”,”c2”);

我们的数据层使用的mybatis框架，其动态sql语法对应的批处理是：

<insert id="batchInsert" parameterType="java.util.List">
   insert into 
    <include refid="ScoreDetailTable"/> 
    (user_id, score, type, archive,score_reason, created_at, updated_at,
     object_id,object_data,relation_user_id,relation_user_type)
    values
    <foreach collection="list" item="item" index="index" separator="," >
      (#{item.userId,typeHandler=idHandler}, 
       #{item.score,jdbcType=BIGINT}, 
       #{item.type,jdbcType=VARCHAR}, false, 
       #{item.scoreReason,jdbcType=VARCHAR},now(), now(),
       #{item.objectId,typeHandler=idHandler},#{item.objectData,jdbcType=VARCHAR},
       #{item.relationUserId,typeHandler=idHandler},
       #{item.relationUserType,jdbcType=VARCHAR})
    </foreach>
</insert>

如上批量写入mysql可以大量减少与数据库的交互，减轻数据的压力，比之前逐个单独写入，批量写入时日志量（MySQL的binlog和innodb的事务让 日志）减少，降低了日志刷盘的数据量和频率，从而提高效率。同时也能减少SQL语句解析的次数，减少网络传输的IO，性能上有一定提升。

2.MySql批量删除
这里的批量删除是指多条件下的批量删除，比如你需要根据用户Id删除多个用户的有关记录，mybatis中的具体写法如下，挺简单：

<delete id="delete" parameterType="list">
<![CDATA[
delete user_log where fid in
]]>
<foreach collection="list" item="id"  open="(" separator="," close=")">  
   #{id} 
 </foreach>
 </delete>

这样就避免根据id逐个删除。

3.Mysql批量更新

这次优化中应用了两种批量更新的方法：一是： update table set field=x where id in(…),mybatis对应的写法实例如下：

<update id="batchUpdateRequstStatus"  parameterType="java.util.List"> 
 update vdlm_view_request_info set archive=true where id in
  <foreach collection="list" item="item" index="index"    open="(" separator="," close=")" >
    #{item.id,typeHandler=idHandler}
  </foreach>
</update>

第二种批量更新运用了case when语法，在给用户批量加分时需要根据用户ID和加分类型给不同的用户加不同的分值。mybatis中case when具体应用：

<update id="batchUpdateUserScore" parameterType="java.util.List">
 update vdlm_user_score 
   <trim prefix="set" suffixOverrides=",">
      <trim prefix="total_score=case" suffix="end,">
         <foreach collection="userScoreList" item="item" index="index">
           when (user_id=#{item.userIdtypeHandler=idHandler} and 
type=#{item.type,jdbcType=VARCHAR}) then total_score + #{item.totalScore,jdbcType=DECIMAL}
                </foreach>
                 else total_score
        </trim>
    </trim>
</update>

对应的SQL实例如下：

  update vdlm_user_score
  set total_score=case when (user_id=104007841 and
  type='totalscore') then
  total_score + 4
  when (user_id=105085333 and
  type='totalscore') then
  total_score + 5
  when (user_id=102013322 and
  type='totalscore') then
  total_score + 1
  else total_score end

4.MySql多条件批量查询统计

有这样一个需求：查询统计每天得分前三百名的用户和每个用户不同得分类型的分值及次数。使用case when ，一条sql就可以搞定：

SELECT user_id,
    sum( CASE WHEN type = 'forward' or  type = 'view' THEN score ELSE 0 END ) AS `totaolscore`,
    sum( CASE WHEN type = 'forward' THEN score ELSE 0 END ) AS `forwardscore`,
    sum( CASE WHEN type = 'view' THEN score ELSE 0 END ) AS `viewscore`,
    COUNT( CASE WHEN type = 'forward' THEN 1 ELSE NULL END ) AS `forwardcount`, 
    COUNT( CASE WHEN type = 'view' THEN 1 ELSE NULL END ) AS `viewcount` 
FROM vdlm_user_score_log WHERE  created_at>="2015-05-17 00:00:00" and created_at<"2015-05-17 23:59:59"
GROUP BY user_id ORDER BY `totaolscore` DESC LIMIT 300

本文链接http://blog.csdn.net/song19890528/article/details/46467243

更多内容欢迎关注个人微信公众号，一起成长！