首先,我想说的是,数据库的设计已经交给了我,我被要求提高查询的性能。我不应该修改表的字段(我相信将一些字符移到int可以提高性能…)。
这张桌子有很多行(我大约有14*10^6行,而且还在增长…)
我想提高如下查询的性能:
SELECT
timestampms/1000 as secs,
round(avg(data),2) as data,
FROM_UNIXTIME(timestampms/1000,'%m/%Y') as control
FROM externalsensor
WHERE (model='electric' and id=4 AND sensorid='TRI1VA' AND rawdata < 65535 )
AND timestampms BETWEEN '1272454583000' AND '1303990583000'
GROUP BY control
ORDER BY secs ASC;
上述表格为:
CREATE TABLE `externalsensor` (
`model` varchar(50) NOT NULL DEFAULT 'desconocido',
`timestampms` char(13) DEFAULT NULL,
`amtype` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned DEFAULT NULL,
`version` smallint(5) unsigned DEFAULT NULL,
`interval` smallint(5) unsigned DEFAULT NULL,
`counter` int(10) unsigned DEFAULT NULL,
`sensorid` varchar(20) DEFAULT NULL,
`rawdata` smallint(5) unsigned DEFAULT NULL,
`data` decimal(20,10) DEFAULT NULL,
KEY `temps` (`timestampms`),
KEY `sensor` (`model`,`id`,`sensorid`,`timestampms`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
解释性选择显示:
+----+-------------+----------------+-------+---------------+--------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+--------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | externalsensor | range | temps,sensor | sensor | 92 | NULL | 194443 | Using where; Using temporary; Using filesort |
+----+-------------+----------------+-------+---------------+--------+---------+------+--------+----------------------------------------------+
查询花费了20秒,我想知道是否有人看到了改进查询的方法。感谢您的建议:)
更新
在所有这些很棒的推荐,以及这些家伙给我的MySql教训之后,我决定做以下事情:
因为我想要的是按月(一年)、周(一年)和小时(一天)的数据摘要,所以我决定创建一个包含所有这些信息的摘要表,并由cron中的脚本自动更新。
通过这样做,我不会查询200.000行,我相信它会有所改进。当然,我会考虑所有关于索引、char->int和这些人所建议的所有东西的建议。
我还没有开始开发,但一旦我完成,我有新的结果,我会更新与他们这篇文章。
我希望我能接受所有的答案,但那是不可能的。。。谢谢大家。
最佳答案
通过将计算存储在表中,尝试从select中删除计算。
在您的情况下,timestampms/1000 as secs
和FROM_UNIXTIME(timestampms/1000,'%m/%Y') as control
可以在secs字段和控制字段中预计算,并填充ON INSERT和ON UPDATE触发器。如果不修改字段,则添加一些。
“control”用于GROUP BY,因此您应该通过在这个新的控件列上添加索引来获得很好的好处secs'用于排序,所以同样适用。您甚至可能需要基于这两列的一些索引。
关于mysql - 提高Mysql查询的性能(行数和时间戳“问题”),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/5818140/