一个 MySQL 连接在 Golang 中应该保持多长时间活跃?

huangapple go评论81阅读模式
英文:

How long should a mysql connection live in golang

问题

我正在使用Golang与MySQL,并阅读这个文档(http://go-database-sql.org/accessing.html)。文档中提到:

虽然在使用完毕后关闭数据库是惯用的做法,但sql.DB对象被设计为长期存在。不要频繁地打开和关闭数据库。

我不确定连接应该保持多久。目前,如果需要,我在每个HTTP请求中打开连接。这样做是否太频繁了?

英文:

I am using mysql with golang and reading this doc. It says

>Although it’s idiomatic to Close() the database when you’re finished with it, the sql.DB object is designed to be long-lived. Don’t Open() and Close() databases frequently.

I don't know how long should the connnection be. For now, I open the connection each http request if necessary. Is it too frequently?

答案1

得分: 16

在同一份文件的后面,有如下内容:

> 相反,为每个需要访问的不同数据存储创建一个 sql.DB 对象,并在程序完成访问该数据存储之前保持它的打开状态。根据需要传递它,或者以某种全局可用的方式使其可用,但保持它的打开状态。不要在一个短暂的函数中打开和关闭它。相反,将 sql.DB 作为参数传递给该短暂的函数。

> 如果你不将 sql.DB 视为长期存在的对象,可能会遇到一些问题,比如连接的重用和共享不佳、可用网络资源耗尽,或者由于大量 TCP 连接保持在 TIME_WAIT 状态而导致的偶发故障。这些问题表明你没有按照 database/sql 的设计方式使用它。

打开和关闭数据库连接是一项昂贵的操作,所以你应该尽量避免这样做。你绝对不希望在每个请求之后关闭连接(除非每天只有几个请求,即使是这样,只要应用程序运行,你也可以保持连接打开)。

database/sql 包在底层使用了连接池,所以你不必担心管理多个连接。

英文:

Further down in the same document it reads:

> Instead, create one sql.DB object for each distinct datastore you need to access, and keep it until the program is done accessing that datastore. Pass it around as needed, or make it available somehow globally, but keep it open. And don’t Open() and Close() from a short-lived function. Instead, pass the sql.DB into that short-lived function as an argument.
>
> If you don’t treat the sql.DB as a long-lived object, you could
> experience problems such as poor reuse and sharing of connections,
> running out of available network resources, or sporadic failures due
> to a lot of TCP connections remaining in TIME_WAIT status. Such
> problems are signs that you’re not using database/sql as it was
> designed.

Opening and closing database connections is a costly operation so you want to avoid that as much as possible. you definitely don't want to close the connection after each request (unless there's only a few a day and even then you can keep it open as long as the app runs)

The database/sql package uses connection pooling under the hood, so you don't have to worry about managing multiple connections.

答案2

得分: 9

没有权威的答案,因为这取决于你使用的驱动程序。如果你使用以下任一驱动程序:

...那么你根本不需要关闭sql.DB。在这些情况下,sql.DB表示一个连接池,在处理程序/请求/函数中使用它意味着你只是从该池中使用一个连接(如果可用)。

例如:

var db *sql.DB

func setup() error {
    var err error
    db, err = sql.Open(...)
    if err != nil {
        log.Fatal(err)
    }
    
    // 其他设置相关的活动
}

func main() {
    err := setup()
    if err != nil {
        log.Fatal(err)
    }

    // 这里不需要调用`defer db.Close()`
    http.ListenAndServe(...)
}

func SomeHandler(w http.ResponseWriter, r *http.Request) {
    u := User{}
    res, err := db.GetThings(&u)
    // 这里不需要关闭它 - 在大多数情况下,它只是从我们的sql.DB连接池中动态获取的连接
}
英文:

There is no authoritative answer, as it depends on the driver you're using. If you're using either:

... then you shouldn't close your sql.DB at all. The sql.DB in those cases represents a connection pool, and using it in your handlers/requests/functions means you are just using a connection from that pool (if available).

e.g.

var db *sql.DB

func setup() error {
    var err error
    db, err = sql.Open(...)
    if err != nil {
        log.Fatal(err)
    }
    
    // Other setup-related activities
}

func main()
    err := setup()
    if err != nil {
        log.Fatal(err)
    }

    // No need to call `defer db.Close()` here
    http.ListenAndServe(...)
}

func SomeHandler(w http.ResponseWriter, r *http.Request) {
    u := User{}
    res, err := db.GetThings(&u)
    // No need to close this here - it's just a connection dynamically pulled
    // from our sql.DB connection pool in most cases
}

huangapple
  • 本文由 发表于 2015年4月10日 18:05:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/29558653.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定