导读：许多同学在运用Go和数据库打交道的过程中，经常会遇到一些反常不知道为什么，本文从SQL衔接池的原理进行剖析，模拟了一些比方对反常的现象进行解读剖析，并给出一些常见的应对手段，期望能帮助到大家。

全文12795字，预计阅览时刻32分钟

有许多同学遇到了 MySQL 查询缓慢的问题，其或许表现为 SQL 句子很简略，但是查询耗时很长。或许是因为这样一些原因所造成的。

1、资源未及时开释

Go 的 sql 包运用的是长衔接办法让 Client 和 SQL Server 交互，为了避免 SQL Server 链接过多，一般会在 Client 端限制最大衔接数。

下面是sql 的衔接池的状况图（设置了最大翻开衔接数的情况）：

SQL Client 和 Server 交互后，有些成果回来的是一个流(Stream)，此刻的网络衔接(Conn)是被 Stream 对象持续运用的，Client 需求迭代读取成果，读取完结后应立即封闭流以回收资源(开释 conn)。

比方最长用的DB.QueryContext 办法即是如此：

// QueryContext 查询一些成果
// query：select * from test limit 10
func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error)
type Rows struct{
    Close( ) error 
    ColumnTypes( ) ( [ ]*ColumnType, error) 
    Columns( ) ( [ ]string, error) 
    Err( ) error 
    Next( ) bool 
    NextResultSet( ) bool 
    Scan(dest ...any) error
}

当还有成果的时分(即Rows.Next()==true 时)，阐明还有成果未读取出来，此刻必须调用 Rows.Close() 办法来对流进行封闭以开释衔接（让当时衔接变为空闲状况以让其他逻辑能够运用该衔接）。

1.1 试验1-不调用 Rows.Close()

若不调用 Close 又会怎样呢？下面做一个试验来调查一下：

select * from user;
+----+-------+---------------------+----------+--------+
| id | email | register_time       | password | status |
+----+-------+---------------------+----------+--------+
|  2 | dw    | 2011-11-11 11:01:00 | d        |      0 |
+----+-------+---------------------+----------+--------+
1 row in set (0.03 sec)

packagemain
import(
"context"
   "database/sql"
   "encoding/json"
   "fmt"
   "sync"
"time"
_"github.com/go-sql-driver/mysql"
)
funcmain(){
db,err:=sql.Open("mysql","root:@tcp(127.0.0.1:3306)/test")
iferr!=nil{
panic(err)
   }
db.SetMaxOpenConns(1)
//发动一个单独的协程，用于输出DB的状况信息
gofunc(){
tk:=time.NewTicker(3*time.Second)
defertk.Stop()
forrangetk.C{
bf,_:=json.Marshal(db.Stats())
fmt.Println("db.Stats=",string(bf))
      }
}()
//发动10个协程，一起查询数据
varwgsync.WaitGroup
fori:=0;i<10;i++{
wg.Add(1)
gofunc(idint){
deferwg.Done()
queryOne(id,db)
}(i)
   }
wg.Wait()
fmt.Println("finish")
}
funcqueryOne(idint,db*sql.DB){
start:=time.Now()
rows,err:=db.QueryContext(context.Background(),"select*fromuserlimit1")
iferr!=nil{
panic(err)
   }
//deferrows.Close()
//没有从Rows里读取成果，也没有调用rows.Close
fmt.Println("id=",id,"hasNext=",rows.Next(),"cost=",time.Since(start))
}

履行后将输入如下内容：

id= 0 hasNext= true cost= 9.607371ms
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}

解读一下状况数据：

{
    "MaxOpenConnections": 1,  // 最大翻开衔接数，和代码设置的共同，是 1
    "OpenConnections": 1,     // 已翻开的衔接数    
    "InUse": 1,               // 正在运用的衔接数
    "Idle": 0,                // 空闲衔接数
    "WaitCount": 9,           // 等候衔接数
    "WaitDuration": 0,        // 等候总耗时（在等候退出时才计数）
    "MaxIdleClosed": 0,       // 超越最大 idle 数所封闭的衔接总数 
    "MaxIdleTimeClosed": 0,   // 超越追到 idle 时刻所封闭的衔接总数
    "MaxLifetimeClosed": 0    // 超越最大生命周期所封闭的衔接总数
}

从上面的输出能够看出，一共发动了 10 个协程，只要一个协程的 queryOne 办法成功履行了，其他 9 个协程的都是处于等候状况。

1.2 试验2-调用 Rows.Close()

若将 queryOne 办法的，“// defer rows.Close()” 的注释去掉，即变为：

func queryOne(id int, db *sql.DB) {
    start := time.Now()    
    rows, err := db.QueryContext(context.
Background(),"select*fromuserlimit1")
    if err != nil {       
       panic(err)    
}
deferrows.Close()//翻开了此处的注释，Close办法会开释资源
fmt.Println("id=",id,"hasNext=",rows.Next(),"cost=",time.Since(start))
}

履行后，会输出如下内容：

# go run main.go
id= 9 hasNext= true cost= 4.082448ms
id= 3 hasNext= true cost= 5.670052ms
id= 8 hasNext= true cost= 5.745443ms
id= 5 hasNext= true cost= 6.238615ms
id= 6 hasNext= true cost= 6.520818ms
id= 7 hasNext= true cost= 6.697782ms
id= 4 hasNext= true cost= 6.953454ms
id= 1 hasNext= true cost= 7.1079ms
id= 0 hasNext= true cost= 7.3036ms
id= 2 hasNext= true cost= 7.464726ms
finish

上述输出成果阐明一切的 10 个协程都成功履行完结。

1.3 试验3- 运用带超时的 Context

补充，上述调用 QueryContext 办法的时分，运用的是context.Background()，所以是共同阻塞的效果。实践在运用的时分，传入的 context 一般是有超时时刻或许支撑撤销的，相似这样：

func  queryOne(id int, db *sql.DB) {
    start := time.Now() 
    ctx,cancel:=context.WithTimeout(context.Background(),time.Second) // 要害     
    defer cancel()  // 要害。若将此行替换为 _=cancel,又是别的一种成果了
    rows, err := db.QueryContext(ctx , "select * fro m user  limit 1") 
     if err != nil { 
       // panic (err) 
       fmt.Println("BeginTx failed:",err)        
       return 
    } 
    // defer rows.Close () // 翻开了此处的注 释，Close  办法会开释资源     
fmt.Println("id=",id,"hasNext=",rows.Next(),"cost=",time.Since(start))
}

运行后能够调查到，一切的 10 个协程也都履行成功了：

id= 9 hasNext= true cost= 1.483715ms
id= 3 hasNext= true cost= 175.675s
id= 6 hasNext= true cost= 1.277596ms
id= 1 hasNext= true cost= 174.307s
id= 7 hasNext= true cost= 108.061s
id= 4 hasNext= true cost= 115.072s
id= 2 hasNext= true cost= 104.046s
id= 0 hasNext= true cost= 96.833s
id= 8 hasNext= true cost= 123.758s
id= 5 hasNext= true cost= 92.791s
finish

因为 context 是带超时的，并且履行完结后会调用 defer cancel() 将 ctx 撤销，所以即使没有运用 rows.Close 开释资源，ctx 在被cancel后也会立即开释资源。

若是将 defer cancel() 换为 _=cancel ,又是别的一种成果了，咱们将看到的是：

d= 9 hasNext= true cost= 2.581813ms
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded
BeginTx failed: context deadline exceeded

1.4 解决计划

小结：

咱们应该运用QueryContext 这类支撑传入 context 的函数，并且传入带超时操控的 context，并且在逻辑履行完结后，应运用 defer 办法将 context 撤销。
关于回来一个流类型的成果，运用完结后必定需求调用 Close 办法以开释资源。
一切 *sql.DB、*sql.Tx、*sql.Stmt 的回来 *Conn、*Stmt、*Rows 这几种类型的都需求 Close：

type DB/Tx/Stmt struct{
   Conn(ctx context.Context) (*Conn, error)
   Prepare(query string) (*Stmt, error)
   PrepareContext(ctx context.Context, query string) (*Stmt, error)
   Query(query string, args ...any) (*Rows, error)
   QueryContext(ctx context.Context, query string, args ...any) (*Rows, error)
}

要避免该问题出现，一般只需求如上例，增加上 defer rows.Close() 即可。

若是运用的 GDP 结构，读取 Rows 成果，能够运用 mysql.ReadRowsClose 办法，在读取完结后，会主动的 Close。比方：

type user struct {
IDint64`ddb:"id"`
Statusuint8`ddb:"status"`
}
func readUsers(ctx context.Context)([]*user,error)
    rows, err := cli.QueryContext(ctx, "select * from user where status=1 limit 5")     
    if err != nil {         
       return nil,err     
}
varuserList[]*user
err=mysql.ReadRowsClose(rows,&userList)
returnuserList,err
}

或许是 QueryWithBuilderScan：

b := &SimpleBuilder{
  SQL: "SELECT id,name from user where id=1",
 }
 type user struct{
  Name string `ddb:"name"`
  ID int `ddb:"id"`
 }
 var us []*user
 err = mysql.QueryWithBuilderScan(ctx, client, b, &us)

2、业务不完整

翻开一个业务(Tx)后，必须提交(Commit)或许回滚(Rollback)，否则会业务不完整，也会导致 Client 端资源(衔接)不开释。

func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error)
type Tx 
func (tx *Tx) Commit() error    // 提交业务
func (tx *Tx) Rollback ( ) error  // 回滚业务
func (tx *Tx) Exec(query string, args ...any) (Result, error) 
func (tx *Tx) ExecContext(ctx context.Context, query string, args ...any) (Result, error) 
func (tx *Tx) Prepare(query string) (*Stmt, error) 
func (tx *Tx) PrepareContext(ctx context.Context, query string) (*Stmt, error) 
func (tx *Tx) Query(query string, args ...any) (*Rows, error) 
func (tx *Tx) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error) 
func (tx *Tx) QueryRow(query string, args ...any) *Row 
func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...any) *Row 
func (tx *Tx) Stmt(stmt *Stmt) *Stmt 
func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt

2.1 和 PHP 的区别

别的需求留意的是，运用 Go标准库的 DB.BeginTx 办法敞开一个业务后，会得到一个业务对象 Tx，要让一批 SQL 在一个业务里履行需求让这些 SQL 在此 Tx 对象上履行。这点和 PHP 的是不相同的，比方在 PHP 中是这样运用业务：

<?php
/*开始一个业务，封闭主动提交*/
$dbh->beginTransaction();
 /*在全有或全无的基础上刺进多行记录（要么悉数刺进，要么悉数不刺进）*/
$sql='INSERTINTOfruit(name,colour,calories)VALUES(?,?,?)';
$sth=$dbh->prepare($sql);
foreach($fruitsas$fruit){
$sth->execute(array(
$fruit->name,
$fruit->colour,
$fruit->calories,
));
}
/*提交更改*/
$dbh->commit();
// 此代码来自 https://www.php.net/manual/zh/pdo.commit.php

而运用 Go 的业务是这样的：

import (
  "context"
  "database/sql"
  "log"
)
var (
  ctx context.Context
  db  *sql.DB
)
func main() {
  tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
  if err != nil {
    log.Fatal(err)
  }
  id := 37
    // 运用 Tx 履行 Update 句子，而不是持续运用 db.Exec
  _, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
  if execErr != nil {
    _ = tx.Rollback()
    log.Fatal(execErr)
  }
  if err := tx.Commit(); err != nil {
    log.Fatal(err)
  }
}
// 此代码来自于：https://pkg.go.dev/database/sql@go1.18.3#example-DB.BeginTx

2.2 试验

下面持续试验业务不完整的影响，主体部分和上述相同，queryOne 办法变成如下这样：

funcqueryOne(idint,db*sql.DB){
tx,err:=db.BeginTx(context.Background(),nil)
iferr!=nil{
panic(err)
   }
//defertx.Rollback()
start:=time.Now()
rows,err:=tx.QueryContext(context.Background(),"select*fromuserlimit1")
iferr!=nil{
panic(err)
   }
deferrows.Close()
//业务没有回滚、提交
fmt.Println("id=",id,"hasNext=",rows.Next(),"cost=",time.Since(start))
}

履行后输入和上述没有 rows.Close 相似：

id= 9 hasNext= true cost= 11.670369ms
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}
db.Stats= {"MaxOpenConnections":1,"OpenConnections":1,"InUse":1,"Idle":0,"WaitCount":9,"WaitDuration":0,"MaxIdleClosed":0,"MaxIdleTimeClosed":0,"MaxLifetimeClosed":0}

同样，一共发动了 10 个协程，只要一个协程的 queryOne 办法成功履行了，其他 9 个协程的都是处于等候状况。

若将上述queryOne 办法中的// defer tx.Rollback() 的注释翻开，则一切 10 个协程都能够成功履行完结。

2.3 解决计划

要避免业务不完整，要保证业务要么被 Commit，要么被 Rollback。

若是运用的 GDP 结构，能够运用 mysql.BeginTx 办法来运用业务。该计划能够更安全的运用业务，会主动的依据函数回来值来决定是 Commit 仍是 Rollback，若业务函数出现了 panic 也会主动的 Rollback。

// 业务逻辑函数的界说，在此函数内实现业务内的增修改查
// 回来 error==nil 则 tx.Commit(),否则 tx.Rollback()
type doFunc func(ctx context.Context, qe QueryExecuto r) error 
func BeginTx(ctx context.Context, cli CanBeginTx, opts *sql.TxOptions, do doFunc) error

varclimysql.Client
updateUserNameByID:=func(ctxcontext.Context,iduint64,namestring)error{
//运用BeginTx办法，能更省心的处理业务
err:=mysql.BeginTx(ctx,cli,nil,func(ctxcontext.Context,qemysq.QueryExecutor)error{
//其他的数据库更新逻辑略
b1:=&mysql.SimpleBuilder{}
b1.Append("selectnamefromuserwhereuid=?",id)
varoldNamestring
iferr:=mysql.QueryRowWithBuilderScan(ctx,qe,b1,&oldName);err!=nil{
returnerr
}
ifoldName=="诸葛亮"||oldName==name{
//回来err，mysql.BeginTx办法将会回滚业务
returnfmt.Errorf("不需求更新，业务整体回滚")
}
b2:=&mysql.SimpleBuilder{}
b2.Append("updateusersetname=?whereid=?",name,id)
_,err:=mysql.ExecWithBuilder(ctx,qe,b2)
iferr!=nil{
returnerr
}
//回来nil，mysql.BeginTx办法将会提交业务
returnnil
})
returnerr
}

3、其他原因

3.1 不支撑预处理

默认一般会运用预处理的办法来提升 SQL 的安全性，避免发生 SQL 注入的问题。

若是在厂内运用集群版MySQL：DDBS(DRDS)，其对 prepare 支撑的并不好，运用后会导致性能特别差。或许表现为，本应该几毫秒回来的查询，实践上要数百毫秒乃至数秒才能回来。此刻需求在参数中增加上装备项 interpolateParams=true ，封闭 prepare 功用来解决。

Name = "demo"
# 其他装备项略
[MySQL] 
Username     = "example"
# 其他参数略
DSNParams ="charset=utf8&timeout=90s&collation=utf8mb4_unicode_ci&parseTime=true&interpolateParams=true"

4、怎么排查

咱们能够利用 DB 的 Stats() 接口回来的数据来剖析是否存在上述问题。在上述章节中，咱们就是打印此数据来调查 Client 的状况信息。

{
"MaxOpenConnections" : 1 ,  // 最大翻开衔接数，和代码设置的共同，是 1    
"OpenConnections" : 1 ,     // 已翻开的衔接数        
"InUse" : 1 ,               // 正在运用的衔接数    
"Idle" : 0 ,                // 空闲衔接数    
"WaitCount" : 9 ,           // 等候衔接数    
"WaitDuration" : 0 ,        // 等候总耗时（在等候退出时才计数）    
"MaxIdleClosed" : 0 ,       // 超越最大 idle 数所封闭的衔接总数     
"MaxIdleTimeClosed" : 0 ,   // 超越追到 idle 时刻所封闭的衔接总数    
"MaxLifetimeClosed" : 0    // 超越最大生命周期所封闭的衔接总数
}

若运用的是 GDP 结构，咱们能够通过如下几种手段来调查此数据。

4.1 集成 GDP 使用面板

在百度厂内，GDP 结构（百度内部的 Go Develop Platform，具有易用性好、易扩展、易调查、安稳可靠的特色，被数千模块运用）提供了一个叫做”GDP使用面板”的功用模块，该模块提供了可视化的 UI 让咱们能够非常便利的检查、调查使用的各种状况信息。比方能够检查体系信息、文件体系信息、网络状况信息、编译信息、go runtime信息、结构里各种组件的状况信息（如服务发现的工作状况、MySQL、Redis 等各种 Client 的衔接池信息等）。

集成该功用非常简略，只需求增加 2 行装备性代码。

完结集成后，能够通过 http://ip:port/debug/panel/?tab=servicer 来拜访此面板，找到对应的 servicer 后（页面的地址是 /debug/panel/?tab=servicer&key={servicer_name} ），页面上的 “MySQL ClientStats”段落即为当时 MySQL Client 的 Stats 信息。比方：

4.2 集成监控

GDP 结构的标准化目标监控才能已经将一切 MySQL Client 的 Stats 信息进行了采集输出。能够以 prometheus 或许 bvar 格式输出。

完结集成后，拜访 http://ip:port/metrics/service 即可检查到对应的目标项，大致是这样的：

client_connpool{servicer="demo_mysql",stats="ConnType"} 1
client_connpool{servicer="demo_mysql",stats="IPTotal"} 1
client_connpool{servicer="demo_mysql",stats="InUseAvg"} 0
client_connpool{servicer="demo_mysql",stats="InUseMax"} 0
client_connpool{servicer="demo_mysql",stats="InUseTotal"} 0
client_connpool{servicer="demo_mysql",stats="NumOpenAvg"} 0
client_connpool{servicer="demo_mysql",stats="NumOpenCfg"} 100
client_connpool{servicer="demo_mysql",stats="NumOpenMax"} 0
client_connpool{servicer="demo_mysql",stats="NumOpenTotal"} 0

能够对上述目标增加报警，以帮咱们更快发现并定位到问题。

4.3 输出到日志

若不选用上述 2 种计划，还能够选用发动一个异步协程，定期将 Stats 信息输出到日志的计划，以便利咱们剖析定位问题。

————————END————————

推荐阅览：

百度交易中台之钱包体系架构浅析

根据宽表的数据建模使用

百度评论中台的规划与探索

根据模板装备的数据可视化平台

怎么正确的评测视频画质

小程序发动性能优化实践

咱们是怎么穿过低代码 “⽆⼈区”的：amis与爱速搭中的要害规划

移动端异构运算技能-GPU OpenCL 编程（基础篇）

Go 语言使用 MySQL 的常见故障分析和应对方法

1、资源未及时开释

1.1 试验1-不调用 Rows.Close()

1.2 试验2-调用 Rows.Close()

1.3 试验3- 运用带超时的 Context

1.4 解决计划

2、业务不完整

2.1 和 PHP 的区别

2.2 试验

2.3 解决计划

3、其他原因

3.1 不支撑预处理

4、怎么排查

4.1 集成 GDP 使用面板

4.2 集成监控

4.3 输出到日志

作者信息

Go 语言使用 MySQL 的常见故障分析和应对方法

1、资源未及时开释

1.1 试验1-不调用 Rows.Close()

1.2 试验2-调用 Rows.Close()

1.3 试验3- 运用带超时的 Context

1.4 解决计划

2、业务不完整

2.1 和 PHP 的区别

2.2 试验

2.3 解决计划

3、其他原因

3.1 不支撑预处理

4、怎么排查

4.1 集成 GDP 使用面板

4.2 集成监控

4.3 输出到日志

相关文章

我为什么放弃Go语言？

使用Alfred + Gitee搭建免费图床

kubernetes 基于jenkins spinnaker的ci/cd实践二-sonarqube的安装与简单使用

STM32F103单片机PWM单脉冲输出模式

作者信息