数据库连接最佳实践 [英] Database connection best practice

问题描述

我有一个使用net/http的应用程序.我向http注册了一些处理程序，这些处理程序需要从数据库中获取一些东西，然后我们才能继续编写响应并完成请求.

I've an app that uses net/http. I register some handlers with http that need to fetch some stuff from a database before we can proceed to writing the response and be done with the request.

我的问题是关于哪种最佳实践是连接到该数据库的问题.我希望此功能以每分钟一个请求或每秒10个请求的速度工作.

My question is in about which the best pratice is to connect to this database. I want this to work at one request per minute or 10 request per second.

每次请求进入时，我都可以连接到每个处理程序中的数据库.(这会为每个请求生成与mysql的连接吗?)

I could connect to database within each handler every time a request comes in. (This would spawn a connection to mysql for each request?)

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "net/http"
    "fmt"
)

func main() {

    http.HandleFunc("/",func(w http.ResponseWriter, r *http.Request) {
        db, err := sql.Open("mysql","dsn....")
        if err != nil {
            panic(err)
        }
        defer db.Close()

        row := db.QueryRow("select...")
        // scan row

        fmt.Fprintf(w,"text from database")
    })

    http.ListenAndServe(":8080",nil)
}

我可以在应用启动时连接到数据库.每当需要使用数据库时，我都会对其进行Ping操作，如果数据库已关闭，我将重新连接至该数据库.如果尚未关闭，请继续使用.

I could connect to database at app start. Whenever I need to use the database I Ping it and if it's closed I reconnect to it. If it's not closed I continue and use it.

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "net/http"
    "fmt"
    "sync"
)

var db *sql.DB
var mutex sync.RWMutex

func GetDb() *sql.DB {

    mutex.Lock()
    defer mutex.Unlock()

    err := db.Ping()
    if err != nil {
        db, err = sql.Open("mysql","dsn...")
        if err != nil {
            panic(err)
        }
    }

    return db
}

func main() {

    var err error
    db, err = sql.Open("mysql","dsn....")
    if err != nil {
        panic(err)
    }

    http.HandleFunc("/",func(w http.ResponseWriter, r *http.Request) {

        row := GetDb().QueryRow("select...")
        // scan row

        fmt.Fprintf(w,"text from database")
    })

    http.ListenAndServe(":8080",nil)
}

这些方法中哪一种是最好的，还是有另一种更好的方法?让多个请求使用同一个数据库连接是一个坏主意吗?

Which of these ways are the best or is there another way which is better. Is it a bad idea to have multiple request use the same database connection?

我很不高兴会创建一个运行到mysql连接限制中的应用程序，但我不想忽略存在限制的事实.

It's unlikly I will create an app that runs into mysql connection limit, but I don't want to ignore the fact that there's a limit.

推荐答案

最好的方法是在应用启动时一次创建数据库，然后再使用此句柄.另外， sql.DB 类型对于并发使用是安全的，因此您甚至不需要互斥来锁定它们的使用.最后，根据您的驱动程序，数据库句柄将自动重新连接，因此您无需自己执行此操作.

The best way is to create the database once at app start-up, and use this handle afterwards. Additionnaly, the sql.DB type is safe for concurrent use, so you don't even need mutexes to lock their use. And to finish, depending on your driver, the database handle will automatically reconnect, so you don't need to do that yourself.

这篇关于数据库连接最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！