节点MySQL尽可能快地执行多个查询 [英] Node MySQL execute multiple queries the fastest possible

问题描述

最快的方法是将查询发送到MYSQL，然后返回到输出:

Which is the fastest method gets the query to MYSQL, and then comes back to output:

console.log(查询完成"，结果)"

console.log('queries finished', results)"

有没有更好的方法?请解释您的答案！

Is there an even better method? Please explain your answer!

谢谢！

方法1:

var connection = mysql.createConnection({multipleStatements: true});

connection.query('SELECT ?; SELECT ?', [1, 2], function(err, results) {
  if (err) throw err;

  console.log('queries done', results);
});

方法2:

const Db = mysql.createPool({
    connectionLimit: 7,
    dateStrings: true,
    multipleStatements: true
});

Db.getConnection(function(err, connection) {
    if(err) console.log(err);

    connection.query(`
        SELECT "1" AS "first";
        SELECT "2" AS "second";`, function(err, results) {
            connection.release();

            if(err) console.log(err);
            console.log('queries done', results); 
        }                    

    );

});   

方法3:

const Db = mysql.createPool({
    connectionLimit: 7,
    dateStrings: true,
    multipleStatements: true
});

Db.getConnection(function(err, connection) {
    async.parallel([
        function(callback) {
            connection.query(`
                SELECT "1" AS "first"`, function(err, done) {
                    callback(err, done);
                }
            );                 
        },

        function(callback) {
            connection.query(`
                SELECT "2" AS "second"`, function(err, done) {
                    callback(err, done);
                }
            );                
        }
    ], function(err, results) {
        connection.release();
        if(err) console.log(err);

        console.log('queries finished', results);
    });
});

方法4:

const Db = mysql.createPool({
    connectionLimit: 7,
    dateStrings: true,
    multipleStatements: true
});

async.parallel([
    function(callback) {
        Db.getConnection(function(err, connection) {
            connection.query(`
                SELECT "1" AS "first"`, function(err, done) {
                    connection.release();
                    callback(err, done);
                }
            );
        });
    },
    function(callback) {  
        Db.getConnection(function(err, connection) {
            connection.query(`
                SELECT "2" AS "second"`, function(err, done) {
                    connection.release();
                    callback(err, done);
                }
            );
        });
    }
], function(err, results) {
    if(err) console.log(err);
    console.log('queries finished', results);
}); 

我没有发布它，但是方法3和4也可以在没有连接池的情况下完成.还有使用npm模块异步的承诺，什么是最快的?为什么！谢谢.

And I didn't post it, but method 3 and 4 could also be done a without connection pool as well. There's also promises over using the npm module async, what is the fastest and why!? Thanks.

推荐答案

方法1和2相似，不同之处在于，如果使用了池中的所有连接，则池会创建一个连接.

Method 1 and 2 are similar except that Pool creates a connection if all connections in the pool are used.

要确定哪种速度更快，您需要了解应用程序服务器与数据库服务器之间的计算能力与网络带宽之间的关系.

To determine which is faster, you need to know the computing power vs network bandwidth between your application server and your database server.

这是为什么:

在方法1和2中，您使用单个连接来执行多个查询.如果数据库机的处理能力快于发送/接收多个查询的网络带宽，则方法1和2效率更高.如果数据库机的处理能力比带宽慢(例如，两个应用程序/mysql服务器都位于同一台机器上)，则理论上方法3和4会更快.

In method 1 and 2, you are using a single connection to execute multiple queries. If the processing power of the database machine is faster than then network bandwidth to send/receive multiple queries, then method 1 and 2 is more efficient. If the processing power of the database machine is slower than the bandwidth(e.g. both application / mysql server resides on the same machine), then method 3 and 4 will theoretically be faster.

另一个因素是语句是否相互依赖.因为步骤1和2本质上是同步运行语句，所以整个操作集是原子/一致操作.对于方法3和4，由于尽管它们是并行触发的，但它们是异步运行的，因此在某些情况下，较晚的语句要比较早的语句更早完成.如果发生这种情况并且存在依赖性，则方法3和4将破坏您的数据.

Another factor is whether the statements depend on one another. Because step 1 and 2 essentially runs the statements synchronously, the entire set of operation is an atomic / consistent operation. For method 3 and 4, because they run asynchronously although they are triggered in parallel, there can be instances where a later statement complete earlier than an earlier statement. If that happens and there's dependency, method 3 and 4 will corrupt your data.

TL; DR

• 快速+同步(插入/更新，然后选择结果)= 方法1、2(使用池来减少打开新连接的需要)
• 快速+异步(大量插入)=方法3、4
• 应用程序/数据库服务器之间的慢速连接=方法1,2

• Fast + Synchronous (insert/update, then select results) = Method 1, 2 (Use pooling to reduce the need to open new connections)
• Fast + Asynchronous(mass inserts) = Method 3, 4
• Slow connection between application/database server = Method 1,2

这篇关于节点MySQL尽可能快地执行多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！