每次进行查询语句之前，如果都对数据库进行连接操作,将是消耗了无用的时间,如果使用连接池的话,将会减少连接的耗时,下面是对使用连接池和不使用连接池的测试
//不使用连接池
func BenchmarkHello2(b *testing.B) {
open, _ := sql.Open("mysql", "root:root@(127.0.0.1:3306)/go_test?charset=utf8&"+
"parseTime=True&loc=Local")
open.SetMaxOpenConns(100)
open.SetMaxIdleConns(0)
b.ResetTimer()
for i := 0; i <= b.N; i++ {
open.Exec("select * from user ")
}
}
###使用连接池
func BenchmarkHello3(b *testing.B) {
open, _ := sql.Open("mysql", "root:root@(127.0.0.1:3306)/go_test?charset=utf8&"+
"parseTime=True&loc=Local")
open.SetMaxOpenConns(100)
open.SetMaxIdleConns(8)
b.ResetTimer()
for i := 0; i <= b.N; i++ {
open.Exec("select * from user ")
}
}
上面的结果,我的系统使用连接池将是不使用连接池的十倍速度之差
下面通过分析go是如何实现数据库的连接池,了解连接池的内部实现,也学习下go代码的设计
- 连接池常用的方法
db,err := sql.Open("mysql","root") //链接操作
db.SetMaxOpenConns(100) //设置最大连接数
db.SetMaxIdleConns(8) //设置连接池 不设置默认为2 设置为0不使用
db.SetConnMaxLifetime(1000) //一个链接的最大超时时间
db.Exec() //执行一个查询
- 创建连接池
//连接池的创建
func Open(driverName, dataSourceName string) (*DB, error) {
//使用锁,获取当前使用的驱动
//对于解锁的操作,并没有使用defer操作,对于肉眼可见的操作,建议使用完了就直接解锁,而不要使用defer
driversMu.RLock()
driveri, ok := drivers[driverName]
driversMu.RUnlock()
if !ok {
return nil, fmt.Errorf("sql: unknown driver %q (forgotten import?)", driverName)
}
//判断该驱动是否实现了(driver.DriverContext)类型
if driverCtx, ok := driveri.(driver.DriverContext); ok {
//调用驱动实现的链接方法
connector, err := driverCtx.OpenConnector(dataSourceName)
if err != nil {
return nil, err
}
//创建数据库连接池
return OpenDB(connector), nil
}
return OpenDB(dsnConnector{dsn: dataSourceName, driver: driveri}), nil
}
### 连接池对象的生成
func OpenDB(c driver.Connector) *DB {
//构建上下文
ctx, cancel := context.WithCancel(context.Background())
//生成连接池对象
db := &DB{
connector: c,
openerCh: make(chan struct{}, connectionRequestQueueSize),
resetterCh: make(chan *driverConn, 50),
lastPut: make(map[*driverConn]string),
connRequests: make(map[uint64]chan connRequest),
stop: cancel,
}
//启动两个go函数,这两个函数稍后看
go db.connectionOpener(ctx)
go db.connectionResetter(ctx)
//返回对象
return db
}
连接池的创建代码,可以知道下面几个点
- 需要自己实现驱动,go也为我们使用了 _ "github.com/go-sql-driver/mysql" 在连接池的创建时候需要引入这个驱动实现
- 在创建链接的时候,go并没有真正的去链接数据库,只是返回了一个连接池对象
- 启动了两个go协程去处理
连接池的对象返回了,真正创建连接池的时候,是在执行exec,query方法之中
### 执行查询
func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error) {
var res Result
var err error
//最大执行次数,这里是为了防止未获取到连接池,最多尝试两次
for i := 0; i < maxBadConnRetries; i++ {
//执行查询
res, err = db.exec(ctx, query, args, cachedOrNewConn)
if err != driver.ErrBadConn {
break
}
}
//查询返回了连接池错误,再次尝试一次
if err == driver.ErrBadConn {
return db.exec(ctx, query, args, alwaysNewConn)
}
return res, err
}
### 连接池的获取
func (db *DB) conn(ctx context.Context, strategy connReuseStrategy) (*driverConn, error) {
//如果已经关闭 直接返回错误
db.mu.Lock()
if db.closed {
db.mu.Unlock()
return nil, errDBClosed
}
// Check if the context is expired.
//如果上下文返回了cancel()直接返回错误
select {
default:
case <-ctx.Done():
db.mu.Unlock()
return nil, ctx.Err()
}
//获取连接池最大生存时间
lifetime := db.maxLifetime
// Prefer a free connection, if possible.
numFree := len(db.freeConn)
//如果空闲链接大于0 并且使用缓存
if strategy == cachedOrNewConn && numFree > 0 {
//获取第一个
conn := db.freeConn[0]
//这里使用的复制,而没有直接生成一个,好像是性能更好,稍后研究
copy(db.freeConn, db.freeConn[1:])
//出对列
db.freeConn = db.freeConn[:numFree-1]
conn.inUse = true
db.mu.Unlock()
//如果过期的话,就关闭,返回错误
if conn.expired(lifetime) {
conn.Close()
return nil, driver.ErrBadConn
}
// Lock around reading lastErr to ensure the session resetter finished.
conn.Lock()
//查询链接是否有错误
err := conn.lastErr
conn.Unlock()
if err == driver.ErrBadConn {
conn.Close()
return nil, driver.ErrBadConn
}
return conn, nil
}
// Out of free connections or we were asked not to use one. If we're not
// allowed to open any more connections, make a request and wait.
//如果当前链接已经大于最大连接了,需要创建链接
if db.maxOpen > 0 && db.numOpen >= db.maxOpen {
// Make the connRequest channel. It's buffered so that the
// connectionOpener doesn't block while waiting for the req to be read.
//生成一个channel
req := make(chan connRequest, 1)
reqKey := db.nextRequestKeyLocked()
db.connRequests[reqKey] = req
db.waitCount++
db.mu.Unlock()
waitStart := time.Now()
// Timeout the connection request with the context.
select {
//收到取消channel 从map中删除
case <-ctx.Done():
// Remove the connection request and ensure no value has been sent
// on it after removing.
db.mu.Lock()
delete(db.connRequests, reqKey)
db.mu.Unlock()
atomic.AddInt64(&db.waitDuration, int64(time.Since(waitStart)))
select {
default:
case ret, ok := <-req:
//收到添加channel 执行添加操作
if ok && ret.conn != nil {
db.putConn(ret.conn, ret.err, false)
}
}
return nil, ctx.Err()
case ret, ok := <-req:
//收到channel关闭请求
atomic.AddInt64(&db.waitDuration, int64(time.Since(waitStart)))
if !ok {
return nil, errDBClosed
}
if ret.err == nil && ret.conn.expired(lifetime) {
ret.conn.Close()
return nil, driver.ErrBadConn
}
if ret.conn == nil {
return nil, ret.err
}
// Lock around reading lastErr to ensure the session resetter finished.
ret.conn.Lock()
err := ret.conn.lastErr
ret.conn.Unlock()
if err == driver.ErrBadConn {
ret.conn.Close()
return nil, driver.ErrBadConn
}
return ret.conn, ret.err
}
}
//执行添加操作
db.numOpen++ // optimistically
db.mu.Unlock()
ci, err := db.connector.Connect(ctx)
if err != nil {
db.mu.Lock()
db.numOpen-- // correct for earlier optimism
db.maybeOpenNewConnections()
db.mu.Unlock()
return nil, err
}
db.mu.Lock()
dc := &driverConn{
db: db,
createdAt: nowFunc(),
ci: ci,
inUse: true,
}
db.addDepLocked(dc, dc)
db.mu.Unlock()
return dc, nil
}
func (db *DB) putConn(dc *driverConn, err error, resetSession bool) {
db.mu.Lock()
//如果是使用的 报错
if !dc.inUse {
if debugGetPut {
fmt.Printf("putConn(%v) DUPLICATE was: %s\n\nPREVIOUS was: %s", dc, stack(), db.lastPut[dc])
}
panic("sql: connection returned that was never out")
}
//获取堆栈信息
if debugGetPut {
db.lastPut[dc] = stack()
}
dc.inUse = false
for _, fn := range dc.onPut {
fn()
}
dc.onPut = nil
//如果是错误的 maybe一个新的链接
if err == driver.ErrBadConn {
// Don't reuse bad connections.
// Since the conn is considered bad and is being discarded, treat it
// as closed. Don't decrement the open count here, finalClose will
// take care of that.
db.maybeOpenNewConnections()
db.mu.Unlock()
dc.Close()
return
}
if putConnHook != nil {
putConnHook(db, dc)
}
if db.closed {
// Connections do not need to be reset if they will be closed.
// Prevents writing to resetterCh after the DB has closed.
resetSession = false
}
if resetSession {
if _, resetSession = dc.ci.(driver.SessionResetter); resetSession {
// Lock the driverConn here so it isn't released until
// the connection is reset.
// The lock must be taken before the connection is put into
// the pool to prevent it from being taken out before it is reset.
dc.Lock()
}
}
added := db.putConnDBLocked(dc, nil)
db.mu.Unlock()
if !added {
if resetSession {
dc.Unlock()
}
dc.Close()
return
}
if !resetSession {
return
}
select {
default:
// If the resetterCh is blocking then mark the connection
// as bad and continue on.
dc.lastErr = driver.ErrBadConn
dc.Unlock()
case db.resetterCh <- dc:
}
}
func (db *DB) maybeOpenNewConnections() {
numRequests := len(db.connRequests)
if db.maxOpen > 0 {
numCanOpen := db.maxOpen - db.numOpen
if numRequests > numCanOpen {
numRequests = numCanOpen
}
}
for numRequests > 0 {
db.numOpen++ // optimistically
numRequests--
if db.closed {
return
}
//发生一个channel
db.openerCh <- struct{}{}
}
}
- 启动的两个协程处理了什么
//创建一个新的链接
func (db *DB) connectionOpener(ctx context.Context) {
for {
select {
case <-ctx.Done():
return
//收到了channel 执行函数
case <-db.openerCh:
db.openNewConnection(ctx)
}
}
}
//关闭连接
func (db *DB) connectionResetter(ctx context.Context) {
for {
select {
case <-ctx.Done():
close(db.resetterCh)
for dc := range db.resetterCh {
dc.Unlock()
}
return
//收到关闭请求 关闭了链接
case dc := <-db.resetterCh:
dc.resetSession(ctx)
}
}
}
通过两个协程和方法,使连接池维持在一个silice中,需要查询的时候从链接池中去获取
- 到底连接池设置多少比较合适
waitCount int64 // Total number of connections waited for.
maxIdleClosed int64 // Total number of connections closed due to idle.
maxLifetimeClosed int64
Db结构体中有这个三个数,我们可以查看这三个数和活动链接的数量,如果关闭的次数太多了话,可以适量的增加连接数,初始值的话,可以设置为核数*2
