System Optimization Summary (1)----MySQL batch processing

With the rapid development of our business, the average daily traffic of our servers increased from two hundred thousand or three hundred thousand in the beginning of the year to about 8 million, which is a great challenge to the original architecture and performance of the system, so we have made a large optimization upgrade recently to cope with the increasing server pressure. The original business logic and code have been refactored, performance optimization has followed an important principle: to minimize the number and frequency of direct interaction between requests and data, one of the important means is the MySQL batch processing

This focus on the article Browsing module optimization, which is currently the largest traffic entrance, every day there are a large number of article browsing requests, each effective browsing is corresponding to a user plus points and a log record, so there will be frequent database interaction, also affect the response speed, So the addition and log records are batched on the basis of asynchronous processing.

1.MYSQL Bulk Write
The MySQL bulk write syntax is:
INSERT into table (field1,field2,field3) VALUES ("A", "B", "C"), ("A1", "B1", "C1"), ("A2", "B2", "C2");

Our data layer uses the MyBatis framework, and its dynamic SQL syntax corresponds to the batch processing:

<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 <foreachcollection="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>

such as the bulk write to MySQL can greatly reduce the interaction with the database, reduce the pressure on the data, more than the previous individual write, bulk write log volume (MySQL binlog and InnoDB transaction let log), reduce the log brush disk data volume and frequency, thereby improving efficiency. At the same time, it can reduce the number of SQL statement parsing, reduce the IO of network transmission, and improve the performance.

2.MYSQL Bulk Delete
The bulk deletion here refers to the multi-condition of the bulk deletion, such as you need to delete multiple users according to user ID related records, mybatis in the specific wording as follows, quite simple:

<delete id="Delete" parametertype="List"><! [Cdata[delete user_log where FID in]]><foreach collection="List" item="id"open="("  Separator="," close=")">  #{id}</foreach> </Delete>

This avoids the deletion based on the ID individually.

3.Mysql Batch Update

Two methods of batch updating are applied in this optimization: one is: Update table set field=x where ID in (...), MyBatis corresponds to the following example:

<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>  

The second batch update uses the case when syntax, in giving the user the batch time-sharing needs to add different scores to different users according to the user ID and the addition classification type. MyBatis in case-specific application:

<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>

The corresponding SQL instance is as follows:

Update Vdlm_user_score Set Total_score=case when(user_id=104007841  and   Type=' Totalscore ') ThenTotal_score +4   when(user_id=105085333  and   Type=' Totalscore ') ThenTotal_score +5   when(user_id=102013322  and   Type=' Totalscore ') ThenTotal_score +1  ElseTotal_scoreEnd

4.MYSQL Multi-condition batch query statistics

There is a need to query statistics for the top 300 users per day and the score and number of different scoring types for each user. With case, a single SQL can be done:

SELECTUSER_ID,sum( Case  whenType =' Forward ' orType =' View '  ThenScoreELSE 0 END) as ' Totaolscore ',sum( Case  whenType =' Forward '  ThenScoreELSE 0 END) as ' Forwardscore ',sum( Case  whenType =' View '  ThenScoreELSE 0 END) as ' Viewscore ',COUNT( Case  whenType =' Forward '  Then 1 ELSE NULL END) as ' Forwardcount ',COUNT( Case  whenType =' View '  Then 1 ELSE NULL END) as ' Viewcount '  fromVdlm_user_score_logWHEREcreated_at>="2015-05-17 00:00:00"  andcreated_at<"2015-05-17 23:59:59"GROUP  byuser_idORDER  by ' Totaolscore ' DESCLIMIT -

This article links http://blog.csdn.net/song19890528/article/details/46467243

System Optimization Summary (1)----MySQL batch