采用MySQL(阿里云的RDS)保存时间序列数据(TSDB),但是条件查询数据时间不稳定。采用定时删除部分数据,优化数据,但是返回时间依然有10~20秒。
数据表结构
show create table period
+---------+----------------+
| Table | Create Table |
|---------+----------------|
| period | CREATE TABLE `period` (
`recId` bigint(20) NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL,
`snrCode` varchar(9) NOT NULL,
`mode` tinyint(1) unsigned NOT NULL,
`phy1` tinyint(1) unsigned NOT NULL,
`phy2` tinyint(1) unsigned NOT NULL,
`phy3` tinyint(1) unsigned NOT NULL,
`phy4` tinyint(1) unsigned NOT NULL,
`phy5` tinyint(1) unsigned NOT NULL,
`alarm` tinyint(2) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`recId`),
KEY `devId` (`snrCode`),
KEY `time` (`time`),
KEY `IDX_SNRCODE_RECID` (`snrCode`,`recId`)
) ENGINE=InnoDB AUTO_INCREMENT=29481805 DEFAULT CHARSET=utf8 COMMENT='Period data' |
+---------+----------------+
1 row in set
Time: 0.002s
总数据量
select count(*) from period
+------------+
| count(*) |
|------------|
| 10084880 |
+------------+
1 row in set
Time: 27.609s
查询语句
最简单的就是针对时间段内特定设备的查询。
SELECT count(*) from period where snrCode = 'A1H470038' and time < 20160929120000 and time > 20160928120000
+------------+
| count(*) |
|------------|
| 8083 |
+------------+
1 row in set
Time: 16.226s
第二种查询。
SELECT count(*) from period where time < 20160929120000 and time > 20160928120000 and `snrCode` = 'A1H470038'
+------------+
| count(*) |
|------------|
| 8083 |
+------------+
1 row in set
Time: 19.911s
Explain查询。
explain SELECT count(*) from period where time < 20160929120000 and time > 20160928120000 and `snrCode` = 'A1H470038'
+------+---------------+---------+--------+------------------------------+-------+-----------+-------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|------+---------------+---------+--------+------------------------------+-------+-----------+-------+--------+------------------------------------|
| 1 | SIMPLE | period | ref | devId,time,IDX_SNRCODE_RECID | devId | 29 | const | 432500 | Using index condition; Using where |
+------+---------------+---------+--------+------------------------------+-------+-----------+-------+--------+------------------------------------+
1 row in set
Time: 0.003s
explain SELECT count(*) from period where snrCode = 'A1H470038' and time < 20160929120000 and time > 20160928120000
+------+---------------+---------+--------+------------------------------+-------+-----------+-------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|------+---------------+---------+--------+------------------------------+-------+-----------+-------+--------+------------------------------------|
| 1 | SIMPLE | period | ref | devId,time,IDX_SNRCODE_RECID | devId | 29 | const | 432500 | Using index condition; Using where |
+------+---------------+---------+--------+------------------------------+-------+-----------+-------+--------+------------------------------------+
1 row in set
Time: 0.003s
不知道是否还有优化的余地?是否是MySQL IOP能力不足。