MySQL Case sharing: system messages and mysql Case sharing

MySQL Case sharing: system messages and mysql Case sharing

Once upon a time, a master led a group of apprentices to build a software system for the customer. One day, the customer raised a new requirement to send system messages to all users in the system. Since the system was just launched, there were dozens of users in the system. In order to test his apprentice, the master assigned the requirement to his apprentice and asked everyone to make a set of solutions, so he had the following story.

After receiving the task, the disciples thought of creating a system message table. Each time a system message is sent, the data is saved in the vocabulary, you can read personal system messages from the table. The user information table model is as follows:

Based on the above database model, the disciples made different implementation schemes, as shown below:

Solution 1:

Small A is an acute child. After receiving the task. Immediately started his programming philosophy: to retrieve all users in the system, traverse all users, and insert a system message during each iteration. The pseudocode is as follows:

1234567

List<FavUser> userList = favUserService.getAllUser();for(FavUser favUser : userList){    SysMessage sysMessage = new SysMessage();    ...    sysMessage.setReceiveUserId(favUser.getUserId());    sysMessageService.addSysMessage(sysMessage);}

Because there are few users in the system, test A several times and find that the system runs well, the solution is submitted.

Solution 2:

After receiving the task, Mr. B thought that he should retrieve all the user Ids in the system, traverse these ids, and insert a system message during each iteration. Based on this, the pseudocode of Small B is as follows:

1234567

List<Integer> userIdsList = favUserService.getAllUserIds();for(Integer userId : userIdsList ){    SysMessage sysMessage = new SysMessage();    ...    sysMessage.setReceiveUserId(userId);    sysMessageService.addSysMessage(sysMessage);}

Due to the small number of users in the system, the Small B has been tested several times and found that the system is running well, and the solution was submitted.

Solution 3:

After receiving the task, taking into account the system message inserted each time, except for the different user IDs, the other data items are the same, so he thought of batch data insertion. Because MySQL database supports batch data insertion, Xiao C has designed the executed SQL statements and pseudo code:

The executed SQL statement is as follows:

123456789

<insert id="addBatchSysMessage" parameterType="com.favccxx.favsoft.SysMessage" >     insert into sys_message (MESSAGE_TITLE,  MESSAGE_CONTENT, MESSAGE_STATUS, RECEIVE_USER_ID,  RECEIVE_TIME, CREATE_TIME        )     values     <foreach collection="list" item="item" index="index" separator=",">           ( #{item.messageTitle,jdbcType=VARCHAR},#{item.messageContent,jdbcType=VARCHAR}, #{item.messageStatus,jdbcType=CHAR}, #{item.receiveUserId,jdbcType=INTEGER},#{item.receiveTime,jdbcType=TIMESTAMP},  #{item.createTime,jdbcType=TIMESTAMP}       )      </foreach>  </insert>

The pseudocode is as follows:

12345678

List<FavUser> userList = favUserService.getAllUser();List<SysMessage> dataList = new ArrayList<SysMessage>();for(FavUser favUser : userList){    sysMessage.setReceiveUserId(favUser.getUserId());    dataList.add(sysMessage);}List<SysMessage> subList = dataList.subList(0,1000);sysMessageService.addBatchSysMessage(subList);

Small C has added thousands of simulated users to the system, and the test system runs well. However, when users in the system are increased to tens of thousands, the speed of sending system messages is obviously slow. As a result, small C inserts every 10,000 entries in groups, and the system runs well.

Solution 4

After receiving the task, Xiao D also considers batch data insertion. But unlike Xiao C, he wants to execute an SQL statement to complete batch data insertion. That is, the messages to be sent are first stored in the database, and then queried through MySQL, and the data is inserted into the system message at the same time. The MySQL Design of Small D is as follows:

123456789101112131415161718

<insert id="addAllSysMessage" parameterType="com.favccxx.favsoft.SysMessage" >    insert into sys_message ( MESSAGE_TITLE,           MESSAGE_CONTENT, MESSAGE_STATUS, RECEIVE_USER_ID,           RECEIVE_TIME, CREATE_USER_ID, CREATE_TIME    )    select        a.MESSAGE_TITLE as MESSAGE_TITLE,        a.MESSAGE_CONTENT as MESSAGE_CONTENT,        0 as MESSAGE_STATUS,        b.user_id AS RECEIVE_USER_ID,        now() as RECEIVE_TIME,         now() as CREATE_TIME    from sys_message_send_info a,    (         select user_id FROM auth_user    ) b    where sendInfoId=#{sendInfoId} </insert>

1	int insertCount = sysMessageService.addAllSysMessage(sendInfoId);

D inserts 0.1 million simulated users into the system. After testing, the system runs well. The following is the time spent sending messages to 113508 users.

"Cute test" is sent to [113508] users in the system, in milliseconds [2241] "Fdgsdfg" is sent to [113508] users in the Group, in milliseconds [2236] System messages of all Platform Users are sent to [113508] users in the system, in milliseconds [1916] "Flying" is sent to [113508] users in the system, in milliseconds [1217] "Test User Group 33" is sent to [113508] users in the Group, in milliseconds [1617]

The story above is a scenario we often encounter during development. To implement a function in the system, there are often many methods that are first implemented, the simplest code is not necessarily the most efficient. The code itself is a Black Box. do not consider the existing performance of the program, but also consider the extended performance of the program.