首先,我想说的是,数据库的设计已经交给了我,我被要求提高查询的性能。我不应该修改表的字段(我相信将一些字符移到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 secsFROM_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/

10-16 14:53
查看更多