there is a table to store temperature information for displaying broken lines on the page. The
structure is as follows
CREATE TABLE `temp` (
`id` varchar(40) NOT NULL,
`duuid` varchar(40) DEFAULT NULL,
`temp` int(11) DEFAULT NULL,
`recordTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `duuid` (`duuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8; duuid is the uuid of the device,
temp is the temperature value
recordTime is the recording time
this data scans the temperature information of the real-time status table of the device into this table through events and stored procedures every 30min.
the maximum number of devices is 65025, that is, each 30min inserts 65025 pieces of data
data is saved for 30 days
, that is, the maximum amount of data in the database is 93636000
query statements are as follows:
SELECT duuid, temp, recordTime from temp WHERE duuid = "xxx" AND recordTime BETWEEN "2018-11-01 00:00:00" AND "2018-12-01 00:00:00" ORDER BY recordTime; directly query 30-day data, 1440
using innodb engine
for my development machine sub-query time is about 23s
after adding joint index (duuid,recordTime), the query time is about 15s
MyISAM engine is about 3s
but as far as I am concerned, is it feasible to use MyISAM?
or is there a more optimized solution without dividing the table?
and the efficiency of deletion is really too low
DELETE from temp WHERE recordTime < "xxx"it takes 111 minutes to execute, about 312w, and once a day