mysql - 希望优化 MySQL 执行时间以解决数据库中的限制瓶颈

Question

问题:我有 1000 个设备，需要每 5 分钟向命令服务器发送一个 json post 请求。命令服务器有一个请求队列，并使用workers解析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 中现有一小时内执行上述语句时，我得到:

(for a medium MySQL AWS RDS Instance)

问题是 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);