requirement analysis

On each device, add the programs running on the current device and the list of programs running in history.

Click the program list of historical operation to display the cycle time of the clicked program.

BU_Machine_CurrProgram

At present, the company has arranged 50 devices in the plant area, and there is a scheduled task on the server to scan the running programs on 50 devices every 10 seconds, and then update the bu_ Machine_ Data in currprogram table

In other words, bu_ Machine_ There are always 50 records in the currprogram table, which stores the programs currently running on the device.

BU_Machine_CurrProgramList

select count(1) from BU_Machine_CurrProgramList; – 1526

At the same time, the scheduled task will also scan the task list (all task lists) being executed and waiting to be executed in the device hard disk and insert it into the BU_Machine_CurrProgramList table.

BU_Machine_CurrProgram stores the program files (Updates) currently running on the device.

BU_Machine_CurrProgramList stores all program files (insert s) in the current hard disk of the device.

The difference between the two operations leads to a huge difference in the amount of data.

(if device A is executing 001 program, execute it for 120 seconds, and 100 programs from 001 to 099 are stored on the hard disk)

Bu at this time_ Machine_ A record of equipment a, program 001, is stored in the currprogram table.

But BU_Machine_CurrProgramList will store all program files on device A for 12 times, i.e. 1200 records, which is only one device, only 100 records)

If there are 50 devices, what if the hard disk of each device can store 1000 program files? I can't imagine.

BU_ Machine_ For the data in the currprogramlist table, verify whether the task list exists in the database after reading the task list within 10 seconds of the scheduled task. If it exists, it will not be inserted.

Fortunately, if the hard disk on the device is full of program files, it will trigger the emptying operation.

Bu at this time_ Machine_ The data in the currprogramlist table will also be cleared.

BU_Machine_Program_History

The most unfortunate thing is coming, BU_Machine_Program_History stores the program history, i.e. bu_ Machine_ The data in currprogramlist will always be backed up to this table, and the data of this table will never be emptied.

select count(1) from BU_Machine_Program_History; – 33585357

At present, there are 33450148 data in the table, which is more than 30 million.

Hundreds of millions of data a year, my darling.

Because the amount of data is too large, at present, click the device to display the current running ID of the device and the list of programs existing in the hard disk. When you click a task list, the real time of the program running in the device will be displayed.

Moreover, the default query time of a single program is to query the data volume of a month with the current time as the end time.

The where condition is good to judge. First, the device ID, program name, PName, and time.

Not much to say, build a composite index.

ALTER TABLE BU_Machine_Program_History ADD INDEX idx_Mac_ID_PName_AddTimeStamp(Mac_ID, PName, AddTimeStamp);

BU_Machine_Params_History

This table stores the cycle time of the program.

select count(1) from BU_Machine_Params_History; – 4829574

Backend API

Get program list (currently running program, historical program list)

BuStatusListController.java

	/**
     * Query the historical program list according to the machine ID
     */
    @GetMapping("/getPNameList/{macId}")
    public AjaxResult getPNameList(@PathVariable(value = "macId") Integer macId){
        List<Map<String, Object>> pNameList = statusListService.getPNameListByMacId(macId);
        return AjaxResult.success(pNameList);
    }

BuStatusListServiceImpl.java

@Override
    public List<Map<String, Object>> getPNameListByMacId(Integer macId) {
        Map<String, Object> result = new HashMap<>();
        result.put("current",machineProgramHistoryMapper.selectCurrentPNumByMacId(macId));
        result.put("history",machineProgramHistoryMapper.selectPNumListByMacId(macId));
        List<Map<String, Object>> list = new ArrayList();
        list.add(result);
        return list;
    }

Mapper.xml

<select id="selectPNumListByMacId" parameterType="Long" resultType="Map">
        select a.PNum,a.PName,a.PSize from BU_Machine_CurrProgramList a
        <where>
            <if test="macId != null "> and a.Mac_ID = #{macId}</if>
            and a.IsDel = 0
        </where>
    </select>


    <select id="selectCurrentPNumByMacId" parameterType="Long" resultType="Map">
        select PNum,PName,PSize from BU_Machine_CurrProgram
        <where>
            <if test="macId != null "> and Mac_ID = #{macId}</if>
            and IsDel = 0
        </where>
    </select>

The above statement queries the program list under the device.

Click a program to query its cycle time.

BuStatusListController.java

	/**
     * Query the cycle time list according to the machine ID and program name PName
     */
    @GetMapping("/getCycleTimeList")
    public AjaxResult getCycleTimeList(@RequestParam Map<String, Object> params){
        List<Map<String, Object>> cycleTimeList = statusListService.getCycleTimeListByMap(params);
        return AjaxResult.success(cycleTimeList);
    }

BuStatusListServiceImpl.java

@Override
    public List<Map<String, Object>> getCycleTimeListByMap(Map<String, Object> params) {
        return machineProgramHistoryMapper.selectCycleTimeListByMap(params);
    }

Mapper.xml

<resultMap id="programHistoryMap" type="java.util.HashMap">
        <result property="addTimeStamp"    column="AddTimeStamp"    />
        <result property="cycleTime"    column="CycleTime"    />
    </resultMap>

    <select id="selectCycleTimeListByMap" parameterType="Map" resultMap="programHistoryMap">
        SELECT  AddTimeStamp,CycleTime FROM( SELECT a.AddTimeStamp,
            IFNULL(b.CycleTime,0) as CycleTime
            FROM BU_Machine_Program_History a LEFT JOIN BU_Machine_Params_History b ON a.Mac_ID = b.Mac_ID
                AND a.AddTimeStamp = b.AddTimeStamp
            <where>
                <if test="macId != null "> and a.Mac_ID = #{macId}</if>
                <if test="pName != null  and pName != ''"> and a.PName = #{pName}</if>
                AND a.AddTimeStamp > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH))*1000
            </where>
        ) c WHERE  c.CycleTime > 0
    </select>

I made a filter. If the cycle time is 0, the records will be eliminated. In this way, the number of effective records will be less than 100, rather than hundreds of thousands of original records.