mysql时间序列_MySQL 时间序列数据表格如何优化

采用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能力不足。