Scene:
Simulate a day's data, each 10 seconds, traverse 1000 devices, each device simulates a real-time data, the total amount of data is: 24*60*60/10*1000 = 8.64 million records.
--------------------------------------------------------------------------------------------------------------- ------
Strategy: For time-phased, splicing SQL statement query.
1, to a device, the average day per hour, the time spent 3 minutes.
2, the Domainid+devicecode+eventtime index, the time cost 10 seconds.
--------------------------------------------------------------------------------------------------------------- ------
The above strategy, the efficiency is too poor. Modified to average the data for all devices per hour and write to the database.
1, per hour, according to the Domainid+devicecode group, to find out the average of each device, using group grouping, found that the efficiency is too poor.
2, search out all distinct Domainid, devicecode, traverse the cursor one by one to find the average efficiency is very high. When writing to a database, bulk write using transactions.
Troubleshooting MySQL Performance issues