期待优化MySQL执行时间来解决我的数据库中的瓶颈问题

Question

问题：我有1000台设备每过5分钟就需要发送一个json post请求到一个命令服务器。命令服务器有一个请求队列，并使用工作人员来解析json文件并用该信息查询数据库。我有一个数据库级别存在的瓶颈，这使得这个具有挑战性。

例如起见假设这是从一个特定的传感器收集的此信息：

data: { 
     'time': '2017-10-05 17:25:00', 
     'squirrels spotted': 1, 
     'spotter_sensor': 992 
} 

我在数据库中插入数据的表看起来像这样的下方。我将5分钟的计数汇总到他们最近收集的小时内。所以最近一小时显示5分钟前的最新数据。

|-------------------------------------------------------------- 
| spotter_sensor_id | hour_collected  | squirrels spotted | 
|-------------------------------------------------------------- 
| 992   | 2017-10-05 17:00:00 | 5     | 
---------------------------------------------------------------- 

为了做到这一点，我有：（伪代码）

UPDATE table SET squirrels_spotted = squirrels_spotted + '$squirrels spotted' 
     where spotter_sensor_id = '992' 
     and hour_collected = '$hour_collected' 
IF (ROWS_AFFECTED == 0) 
INSERT INTO table VALUES(992, '$new_hour', 0) 

如果在分析数据库的执行时间，执行上述语句在SQL现有小时我：

（为一个介质的MySQL AWS RDS实例）

问题是2秒，因为简单更新太长。这只允许我在这5分钟内只处理150个传感器。我不明白为什么执行一个查询需要很长时间。

我是否必须更改业务逻辑，因为无法在5分钟内完成所有传感器的处理，或者有更好的方法吗？

Answer 1

出于性能，你需要INDEX(spotter_sensor_id, hour_collected)

对于更少的代码，你需要IODKU：

INSERT INTO table 
     (spotter_sensor_id, hour_collected, squirrels_spotted) 
    VALUES 
     ('992', '$hour_collected, $squirrels_spotted) 
    ON DUPLICATE KEY UPDATE 
     squirrels_spotted = squirrels_spotted + VALUES(squirrels_spotted);