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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值