Golang上每次全局数据库连接与每次打开连接之间的性能差异 [英] Performance difference between global database connection and opening connection everytime on Golang
问题描述
在当前项目中，每次用户发出请求时，我都会打开一个新的数据库连接。例如：
In my current project I was opening a new database connection every time when user makes request. For example:
func login(w http.ResponseWriter, r *http.Request) {
...
db, err := sqlx.Connect("postgres", "user=postgres password=*** dbname=postgres")
if err != nil {
ErrorWithJSON(w, err.Error(), http.StatusBadRequest)
return
}
db.SetMaxIdleConns(0)
db.SetConnMaxLifetime(time.Second * 30)
user, err := loginManager(db, m)
...
err = db.Close()
}
当我搜索其他人的代码时，我我已经看到大多数开发人员都为数据库连接创建了一个全局变量，将其设置在主变量上，然后在整个项目中使用该变量。
When I searched for other people's code, I've seen that most of the developers create a global variable for database connection, set it on the main and use this variable on entire project.
我想知道这些方法之间的区别？如果我使用全局变量，那么当5个不同的用户发出注册/登录等请求时会有任何延迟。如果会有延迟，我是否应该创建多个数据库连接并将它们存储在切片中以供将来使用，以便我可以在用户使用时随机选择提出要求。就像一个简单的负载均衡器一样，我不知道吗？
I was wondering is there any difference between these approaches? If I use global variable will there be any latency when 5 different users makes requests for register/login etc. If there will be latency, should I create multiple database connections and store them in a slice for future requests so I can pick randomly when users make request. Like a simple load balancer, I don't know?
很抱歉有多个问题。谢谢！
Sorry for multiple questions. Thank you!
推荐答案
是的，可能会有巨大的性能差异（取决于您所运行查询的性质和有关系统和服务器配置的信息）。
Yes, there might be a huge performance difference (might be several order of magnitude depending on the nature of queries you run and on system and server configuration).
sqlx.DB 类型包装（嵌入）一个 sql.DB 类型，用于管理连接池：
The sqlx.DB type wraps (embeds) an sql.DB type, which manages a pool of connections:
DB是代表零个或多个基础连接池的数据库句柄。可以安全地供多个goroutine并发使用。
DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.
sql程序包会自动创建并释放连接；它还维护空闲连接的空闲池。如果数据库具有每个连接状态的概念，则只能在事务中可靠地观察到这种状态。
The sql package creates and frees connections automatically; it also maintains a free pool of idle connections. If the database has a concept of per-connection state, such state can only be reliably observed within a transaction.
每次打开新连接时， 后台中必须发生很多事情：必须解析连接字符串，必须建立TCP连接，必须执行身份验证/授权，必须在双方（客户端和服务器）分配资源等。这些只是主要的，显而易见的事情。尽管其中一些可以提供，优化，缓存实现，但与拥有单个 DB 实例（可能具有多个已建立的经过身份验证的连接）相比，仍然存在大量开销。
Every time you open a new connection, a lot of things have to happen in the "background": connection string has to be parsed, a TCP connection has to be estabilished, authentication / authorization must be performed, resources must be allocated at both sides (client and server) etc. These are just the main, obvious things. Even though some of these may be provided / implemented optimized, cached, there is still a significant overhead compared to having a single DB instance which might have multiple established, authenticated connections ready in a pool, waiting to be used / utilized.
也引用 sql.Open（） ：
返回的数据库对于由多个goroutine并发使用，并维护其自己的空闲连接池。 因此，Open函数应仅被调用一次。
sqlx.Connect（） 调用了 sqlx.Open（） 与相同sql.Open，但返回一个* sqlx.DB代替 。
因此，总的来说，请使用单个全局 sqlx.DB 或 sql.DB 实例，并在任何地方共享/使用它。它为您提供自动连接和连接池管理。这将为您提供最佳性能。您可以使用 DB.SetConnMaxLifetime来微调连接池。 （） ， DB.SetMaxIdleConns（） 和 DB.SetMaxOpenConns（） 方法。
So all in all, use a single, global sqlx.DB or sql.DB instance, and share / use that everywhere. It provides you automatic connection- and connection pool management. This will provide you the best performance. You may fine-tune the connection pool with the DB.SetConnMaxLifetime(), DB.SetMaxIdleConns() and DB.SetMaxOpenConns() methods.
空闲连接（ DB.SetMaxIdleConns（））是当前不在使用中但坐在游泳池中等待有人捡起的那些。您绝对应该拥有其中的一些，例如其中5或10，甚至更多。 DB.SetConnMaxLifetime（）控制使用新连接的时间。一旦它变老了，它将被关闭（如果需要，将打开一个新的）。您不应该更改此设置，默认行为是永不终止连接。基本上所有默认设置都是明智的，只有在遇到性能问题时才应使用它们。另外，请阅读这些方法的文档以清楚了解情况。
Idle connections (DB.SetMaxIdleConns()) are those that are not in-use currently, but sitting in the pool, waiting for someone to pick them up. You should definitely have some of these, e.g. 5 or 10 of them, or even more. DB.SetConnMaxLifetime() controls how long a new connection may be used. Once it grows older than this, it will be closed (and a new one will be opened if needed). You shouldn't change this, default behavior is never to expire connections. Basically all defaults are sensible, you should only play with them if you experience performance problems. Also, read docs of these methods to have a clear picture.
请参阅以下类似的可能重复的问题：
See this similar, possible duplicate question:
这篇关于Golang上每次全局数据库连接与每次打开连接之间的性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
