系統最佳化總結（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

系統最佳化總結（1）----MySql批處理