节点js(getConnection) [英] Node js (getConnection)

问题描述

var nodePort = 3030;
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var db = require('mysql');
var dbPool = db.createPool({
    host : 'localhost',
    user : 'root',
    password : '1234',
    database : 'test',
    port : 3306
});


app.use( bodyParser.json() );
app.get('/api/db', function(req, res){
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("SELECT * FROM person", function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success',
                        err : '',
                        err_type : '',
                        fields : Fields,
                        rows : Rows,
                        length : Rows.length
                    });
                    objConn.release();
                }//else
            });
        }//else
    });
});
/*
app.get('/api/db:id', function(req, res){
    var id = req.params.id;
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("SELECT * FROM person WHERE id = ? ",[id], function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success',
                        err : '',
                        err_type : '',
                        fields : Fields,
                        rows : Rows,
                        length : Rows.length
                    });
                    objConn.release();
                }//else
            });
        }//else
    });
});
*/
app.post('/api/db', function(req, res){
    if(!req.body.tableName){
        var data = {
            ID : req.body.id,
            Name : req.body.name
        }
        tableName = 'person';
    }else{
        var data = {
            email : req.body.email,
            regid : req.body.regid
        }
        tableName = 'users';
    }//else
    console.log(req.body.regid);
    console.log(req.body.tableName);
    console.log(req.body.email);
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("INSERT INTO "+tableName+" SET ? ", data, function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success'
                    });
                    objConn.release();
                    if(!req.body.tableName){ gcmSend(); }
                }//else
            });
        }//else
    });
});

app.put('/api/db', function(req, res){
    var id = req.body.id;
    var data = {
        Name : req.body.name
    }
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("UPDATE person SET ? WHERE ID = ? ", [data,id], function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success'
                    });
                    objConn.release();
                    gcmSend();
                }//else
            });
        }//else
    });
});

app.delete('/api/db/:id', function(req, res){

    var id = req.params.id;
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("DELETE FROM person WHERE ID = ? ",[id], function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success'
                    });
                    objConn.release();
                    gcmSend();
                }//else
            });
        }//else
    });
});

function gcmSend(){

    message = new gcm.Message({
        collapseKey: 'demo',
        delayWhileIdle: true,
            timeToLive: 3,
            data: {
                title: 'Node.js den mesaj gönderildi'
            }
    });
    sender.send(message, registrationIds, 4, function (err, result) {
        console.log(result);
    });
}

function sendError(res, iStatusCode, strResult, strType, objError){
    res.send({
        results : strResult,
        err : objError.type,
        err_type : strType
    });
}

app.listen(nodePort);
console.log('App listening on port' + nodePort);

我写了一些代码来与nodejs mysql连接，我打开了每个操作的新连接(post，get，put，delete)并发布.这很好吗?还是一种连接更好?一个连接中的所有操作或每个操作一个连接中的所有操作之间有什么区别?

Hi,
I wrote some codes to connection with nodejs mysql, i opened new connection each operation (post,get,put,delete) and release. Is this good approaching? or one connection is better? what is the difference between all operations in one connection or one connection for each operation?

推荐答案

要澄清-Node.js是不是单线程的.您的应用程序代码是在一个线程中执行的，但是在后台它会在需要时使用它们-看一下

To clarify - Node.js is not single-threaded. Your application code is executed in one thread, but under the hood it uses them when needed - take a look here (both the answer and the comments below it):

对于node.js上的Javascript程序，只有一个线程.

To a Javascript program on node.js, there is only one thread.

如果您正在寻找技术，node.js可以自由使用线程 如果底层操作系统需要，则解决异步I/O 它.

If you're looking for technicalities, node.js is free to use threads to solve asynchronous I/O if the underlying operating system requires it.

并且:

就node.js的用户(即Javascript程序员)而言， 涉及到的抽象是只有一个线程.在 在基础运行时(v8)的情况下，它在内部使用线程 例如，它可以自由地进行配置 不会将该信息泄露给Javascript.

As far as the user of node.js (ie the Javascript programmer) is involved, the abstraction is that there is only a single thread. In the case of the underlying runtime (v8), it uses threads internally for - for example - profiling, and it may do so freely as long as it doesn't leak that information up to the Javascript.

换句话说，如果您深入实际运行时，您将 找到多个线程来帮助保持单个Javascript线程 运行顺利.

In other words, if you dive down inside the actual runtime, you will find more than one thread helping to keep the single Javascript thread running smoothly.

如您所见，您使用的mysql模块要求您传递query()方法的回调(可能还有更多).因此，当您调用它时，将继续执行代码，并在数据库结果到达时调用回调.

As you can see the mysql module you use requires you to pass a callback for the query() method (and probably for many more). So when you call it, the execution of your code continues and the callback is called when the results from database arrive.

关于您的问题-您并未为每个请求创建新的连接.看一下mysql模块的 readme 文件，

As for your question - you are not creating a new connection for every request. Take a look at the readme file of the mysql module, the Pooling Connections section:

连接是由池延迟创建的.如果配置池 最多允许100个连接，但只能同时使用5个， 仅建立5个连接.连接也循环 循环方式，从顶部开始进行连接 池并返回到底部.

Connections are lazily created by the pool. If you configure the pool to allow up to 100 connections, but only ever use 5 simultaneously, only 5 connections will be made. Connections are also cycled round-robin style, with connections being taken from the top of the pool and returning to the bottom.

从池中检索到先前的连接时，会发送一个ping数据包 被发送到服务器以检查连接是否仍然良好.

When a previous connection is retrieved from the pool, a ping packet is sent to the server to check if the connection is still good.

当您呼叫dbPool.getConnection()时，仅在池中没有可用连接时才创建连接-否则，它仅从其顶部抓取一个.调用objConn.release()会将连接释放回池-它没有断开连接.此调用使它可以被应用程序的其他部分重用.

When you call dbPool.getConnection() the connection is created only if there are no more available connections in the pool - otherwise it just grabs one from the top of it. Calling objConn.release() releases the connection back to the pool - it's not being disconnected. This call allows it to be reused by other parts of your application.

总结:

• 为每个请求创建新连接并不是一个好主意，因为它将在应用程序和数据库计算机上使用更多资源(CPU，RAM).
• 对所有请求使用一个连接也是错误的，因为如果任何一项操作花费很长时间才能完成连接，则会挂起并使所有其他请求都等待它.
• 使用连接池是一个好主意，它使您可以同时对数据库执行多项操作，即使其中一项需要很长时间才能完成.

• Creating new connection for every request is not a good idea as it will use more resources (CPU, RAM) on both your app's and database's machines.
• Using one connection for all requests is also wrong because if any of the operations take a long time to complete your connection will hang making all other requests wait for it.
• Using a connection pool is a great idea that allows you to perform multiple operations on your database at the same time, even if one of them takes a long time to complete.

更新: 要回答评论中的问题:

Update: To answer the questions from comments:

对于每个请求使用一个连接时，mysql模块必须打开一个新的套接字，连接到数据库并在进行查询之前进行身份验证-这会花费时间并消耗一些资源.因此，这是一个不好的方法.

When you are using one connection for every request the mysql module has to open a new socket, connect to database and authenticate before you make your query - this takes time and eats some resources. Because of that it's a bad approach.

另一方面，当仅使用一个连接(而不是连接池)时，运行需要很长时间才能完成的查询将阻止该连接上的所有其他查询，直到连接完成-这意味着其他任何请求都必须等待.这也是一个不好的方法.

On the other hand, when using only one connection (not connection pool), running a query that takes a long time to complete will block any other queries on that connection until it completes - which means that any other request will have to wait. It's also a bad approach.

为每个请求创建一个新的连接池非常类似于使用新的连接，除非您多次调用pool.getConnection()-否则甚至更糟(占用创建新连接所用的资源并乘以pool.getConnection()通话).

Creating a new connection pool for every request is pretty much like using new connection, unless you call pool.getConnection() multiple times - then it's even worse (take the resources used by creating a new connection and multiply it by the number of pool.getConnection() calls).

要进一步阐明每个操作的一个连接与一个连接中的所有操作问题:

每个连接中的每个操作都在上一个连接完成后开始(它是同步的，但不是在客户端)，因此，如果您的表有几十亿行并发出SELECT * FROM yourtable，将需要一些时间才能完成，阻止此连接上的所有操作，直到连接完成.

Each operation in every connection is started after the previous one completes (it's synchronous, but not on the client side), so if you have a table with a few billion rows and issue SELECT * FROM yourtable it will take some time to complete, blocking every operation on this connection until it finishes.

如果您需要并行执行的每个操作都有一个连接(例如，对于每个请求)，问题将消失.但是如前所述，打开新连接需要时间和资源，这就是引入连接池概念的原因.

If you have one connection for each operation that needs to be issued in parallel (eg. for every request) the problem disappears. But as stated previously opening a new connection requires time and resources which is why the connection pool concept was introduced.

因此答案是:对所有请求使用一个连接池(就像您在示例代码中所做的那样)-连接的数量将根据应用程序的流量而相应缩放.

So the answer is: use one connection pool for all requests (like you do in your example code) - the number of connections will scale accordingly to the traffic on your app.

更新#2:

基于这些评论，我看到我也应该解释连接池背后的概念.它的工作方式是在连接池为空的情况下启动应用程序，并初始化以创建最多 n 个连接(默认情况下，mysql模块为10个).

Based on the comments I see that I should also explain the concept behind connection pools. How it works is that you start an app with a connection pool empty and initialized to create a maximum of n connections (afaik it's 10 for mysql module by default).

无论何时调用dbPool.getConnection()，它都会检查池中是否有任何可用的连接.如果有，它会抓住一个(使其不可用)，否则，它将创建一个新的.如果达到连接限制并且没有可用的连接，则会引发某种异常.

Whenever you call dbPool.getConnection() it checks if there are any available connections in the pool. If there are it grabs one (makes it unavailable), if not it creates a new one. If the connection limit is reached and there are no available connections some kind of exception is raised.

调用connection.release()会将连接释放回池，以便再次可用.

Calling connection.release() releases the connection back to the pool so it is available again.

使用一个池为整个应用程序仅获得一个全局连接是完全错误的，并且违反了概念本身(您可以通过手动创建连接来完成相同的操作)，因此使用连接池我的意思是使用应该使用的连接池-在需要它们时从连接池中获取连接.

Using a pool to get only one global connection for a whole app is totally wrong and against the concept itself (you can do the same thing by just creating the connection manually), so by use a connection pool I mean use a connection pool as it was supposed to be used - to get connections from it when you need them.

这篇关于节点js(getConnection)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！