mysql - 如何在低规范系统的大表上提高 MySQL 性能？

Question

我有一个 Raspberry Pi，一直用来记录从一堆 1-Wire 传感器收集的家庭温度数据。数年来，这些数据已全部收集到 MySQL 数据库中。我使用 Raspbian 发行版和默认的 MySQL 配置。我现在有一个包含超过一百万条记录的表，我的 Pi 运行查询非常慢。

下面是 TemperatureRecords 表的描述:

+--------------+----------+------+-----+---------------------+-------+
| Field        | Type     | Null | Key | Default             | Extra |
+--------------+----------+------+-----+---------------------+-------+
| timeRecorded | datetime | NO   | PRI | 0000-00-00 00:00:00 |       |
| sensorName   | char(3)  | NO   | PRI |                     |       |
| tempValue    | float    | YES  |     | NULL                |       |
+--------------+----------+------+-----+---------------------+-------+

我记录了日期和时间、一个 3 个字符的传感器名称和温度值。我想运行两个查询，一个选择某个传感器的当前(最近)温度，一个选择当天的最高温度。

当前温度示例:

SELECT ROUND(tempValue, 1) as tempValue 
FROM TemperatureRecords 
WHERE DATE(timeRecorded) = '2015-01-20' AND HOUR(timeRecorded) = '20' AND sensorName = 'abc' 
ORDER BY timeRecorded DESC LIMIT 1;

最高温度示例:

SELECT MAX(tempValue) 
FROM TemperatureRecords 
WHERE DATE(timeRecorded) = '2015-01-20' AND sensorName = 'abc';

不幸的是，这些可能需要 10-20 秒才能完成，这太慢了，尤其是当我想从多个传感器检索数据以同时显示在网页上时。

我已经尝试向表中添加额外的索引，但这并没有带来任何改善，而且我不确定我是否完全理解索引。我还尝试使用/usr/share/doc/mysql-server-5.5/examples/my-small.cnf 中的小型系统的 MySQL 配置，有人告诉我这可能会提高性能但无济于事。

我对 MySQL 的了解有些基础。现在我的数据已经变得如此庞大，我是否对 Raspberry Pi 抱有很大的期望，或者我可以做些什么来改进我的设置？

Answer 1

罪魁祸首是WHERE DATE(timeRecorded) = '2015-01-20' - 它计算DATE()对于所有行(无论它们是否匹配)并且不允许使用索引。

尝试:WHERE timeRecorded >= '2015-01-20 00:00:00' and timeRecorded < '2015-01-21 00:00:00'

参见 this SO question了解详情!