加快MySQL中巨大的多个查询语句的性能

I have node.js script which is running as a cron job on the server.

我有node.js脚本,它在服务器上作为cron作业运行。

It asyncronously receieves the data from external sources (several sumultaneous requests) and updates database with multiple queries statements where UPDATE queries are separated with semicolons.

它异步接收来自外部源(几个同时请求)的数据,并使用多个查询语句更新数据库,其中UPDATE查询以分号分隔。

One 8000-UPDATES multi-query runs about 55 seconds.

一个8000-UPDATES多查询运行大约55秒。

Is there the way to speed somehow up the total database updating process?

有没有办法以某种方式加速整个数据库更新过程?

The single query is very simple - like

单个查询非常简单 - 就像

 UPDATE my_table SET field1 = smth WHERE id = some_id;

The index is created for id field.

为id字段创建索引。

1 个解决方案

#1

Reducing the number of queries performed is going to be your biggest win. Perhaps an INSERT...ON DUPLICATE KEY UPDATE?

减少执行的查询次数将是您最大的胜利。也许INSERT ......在重复键更新?

INSERT INTO my_table (id, field1) 
    VALUES
        (1, 'smth'),
        (10, 'smth2'),
        (88, 'smth3'),
        (23, 'smth4'),
        (68, 'smth5')
    ON DUPLICATE KEY UPDATE
        id = VALUES(id),
        field1 = VALUES(field1);