javascript - 请求花费的时间太长 ~1s Node Express API(TTFB 问题)

Question

我正在从前端( Angular )获取请求，并使用 SQL Server (mssql) 通过服务器端代码( Node js/express 应用程序)获取数据。我认为问题在于我正在为我点击的每个端点打开一个连接并关闭它，因此这将是我每次都第一次点击数据库 - (第一个字节的时间)。

相比之下，我的实际内容下载时间非常短。我不确定如何优化此应用程序以使其具有全局连接池，并且每个端点都仅连接到该池。我可能是错的，让我知道!谢谢。

应用程序.js

const express = require('express')
const bodyParser = require('body-parser')
const path = require('path')
const cors = require('cors')
const compression = require('compression')
const helmet = require('helmet')
const expressSanitizer = require('express-sanitizer')
const jwt = require('jwt-simple');
const subjects = require('./routes/subjects')
const responseTime = require('response-time')

const app = express()
// const app = express.createServer()
const port = 3000

var corsOptions = {
    origin: 'http://localhost:8100',
    optionsSuccessStatus: 200 // some legacy browsers (IE11, various SmartTVs) choke on 204 
}

//added security
app.use(helmet())

// //set logger
// app.use(logger)

//cors options
app.use(cors(corsOptions))

//body parser middleware
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({extended: false}))

// Mount express-sanitizer here
app.use(expressSanitizer()) // this line follows bodyParser() instantiations

//set static path
app.use(express.static(path.join(__dirname, 'client')))
// app.use(express.static(path.join(__dirname, '../../www')))

//Use response time
app.use(responseTime())

// set our default template engine to "ejs"
// which prevents the need for using file extensions
app.set('view engine', 'ejs')

//gzip compression
app.use(compression())

//set views for error and 404 pages
app.set('views', path.join(__dirname, 'views'))


app.use('/subjects/v1', subjects)

app.listen(port, () => {
    console.log('server started on port 3000')
})

主题.js

const express = require('express')
const sql = require('mssql')
const router = express.Router()
const config = require('../../services/db')

// Select subjects
router.get('/', (req, res) => {
    new sql.ConnectionPool(config).connect().then(pool => {

        let sqlString = `sql string`

        return pool.request().query(sqlString)
    }).then(result => {
        let rows = result.recordset

        let paginationHeader = {
            totalCount: result.rowsAffected[0],
            pageSize: req.query.pageSize,
            currentPage: req.query.pageNumber
        }
        res.setHeader('X-Pagination', JSON.stringify(paginationHeader))
        res.setHeader('Access-Control-Allow-Origin', '*')
        res.setHeader('Access-Control-Expose-Headers', 'X-Pagination', JSON.stringify(paginationHeader))

        sql.close();
        return new sql.ConnectionPool(config).connect()
    }).then(pool => {

        let sqlString = `sql string `

        return pool.request().query(sqlString)
    }).then(result => {
        let rows = result.recordset

        res.status(200).json(rows);
        sql.close();
    }).catch(err => {
        console.log(".catch ERROR:", err)
        res.status(500).send({ message: err})
        sql.close();
    });

});


// Select id
router.get('/:id', (req, res) => {
    new sql.ConnectionPool(config).connect().then(pool => {

        let sqlString = `SQL string here`

        return pool.request().input('input_parameter', sql.Int, req.params.id).query(sqlString)
    }).then(result => {
        let rows = result.recordset[0]

        res.status(200).json(rows);
        sql.close();
    }).catch(err => {
        res.status(500).send({ message: err})
        sql.close();
    });
})

module.exports = router

编辑基于以下答案减少了 TTFB 时间，但时间仍然很长。

主题.js

const express = require('express')
    const sql = require('mssql')
    const router = express.Router()
    const config = require('../../services/db')
    const connectPool = new sql.ConnectionPool(config).connect()

    // Select subjects
    router.get('/', (req, res) => {
        connectPool.then(pool => {

            let sqlString = `sql string`

            return pool.request().query(sqlString)
        }).then(result => {
            let rows = result.recordset

            let paginationHeader = {
                totalCount: result.rowsAffected[0],
                pageSize: req.query.pageSize,
                currentPage: req.query.pageNumber
            }
            res.setHeader('X-Pagination', JSON.stringify(paginationHeader))
            res.setHeader('Access-Control-Allow-Origin', '*')
            res.setHeader('Access-Control-Expose-Headers', 'X-Pagination', JSON.stringify(paginationHeader))


            return connectPool
        }).then(pool => {

            let sqlString = `sql string `

            return pool.request().query(sqlString)
        }).then(result => {
            let rows = result.recordset

            res.status(200).json(rows);

        }).catch(err => {
            console.log(".catch ERROR:", err)
            res.status(500).send({ message: err})

        });

    });


    // Select id
    router.get('/:id', (req, res) => {
        connectPool.then(pool => {

            let sqlString = `SQL string here`

            return pool.request().input('input_parameter', sql.Int, req.params.id).query(sqlString)
        }).then(result => {
            let rows = result.recordset[0]

            res.status(200).json(rows);

        }).catch(err => {
            res.status(500).send({ message: err})

        });
    })

Answer 1

您应该在路由器外部的 subjects.js 文件中将池创建为常量，这样它只会被实例化一次。像这样:

const express = require('express')
const sql = require('mssql')
const router = express.Router()
const config = require('../../services/db')
const pool = new sql.ConnectionPool(config).connect()

然后您将修改您的端点以改为使用此连接池。

一种常见的做法是将此任务卸载到数据库服务之类的东西中。一个单独的文件，用于设置连接池和通常的日志记录或其他有用的方法，然后将它们一起导出。