システム最適化総括(1)----MySqlバッチ

業務の盛んな発展に従って、私達のサーバーの日平均アクセス量は年初の二三十万から今の800万ぐらいまで増加して、システムの元のアーキテクチャと性能に対してすべて1つの大きい挑戦で、だから最近システムに対して1回の大きい最適化のアップグレードをして日に日に増加するサーバーの圧力に対応して、元の業務のロジックとコードに対してすべて再構築しました.パフォーマンスの最適化は、要求がデータと直接対話する回数と頻度を最小限に抑えるという重要な原則に従っています.その重要な手段の一つはMySqlバッチです.
今回の重点は文章閲覧モジュールを最適化し、これも現在最大の流量入口であり、毎日大量の文章閲覧要求があり、有効な閲覧のたびに1回のユーザー加点操作と1回の加点log記録に対応し、このように頻繁なデータベースインタラクションがあり、応答速度にも影響を与える.したがって,加点とlog記録は非同期処理に基づいてバッチ処理を行った.
1.MySql一括書込みMySQL一括書込み構文は、INSERT INTO table(field 1,field 2,field 3)VALuES(「a」,「b」,「c」)、(「a 1」,「b 1」,「c 1」)、(「a 2」,「b 2」,「c 2」)である.
動的sql構文に対応するバッチは、mybatisフレームワークを使用しています.

<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に基づいて1つずつ削除することを避ける.
3.Mysql一括更新
今回の最適化では、update table set field=x where id in(...)という2つの一括更新方法が適用され、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>

第2のバッチ更新は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多条件ロット照会統計
1日300位以内のスコアを取得するユーザーと、ユーザーごとに異なるスコアタイプのスコアと回数を統計する必要があります.case whenを使用すると、sql 1本でできます.

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