golang中mysql连接池使用_vlambda技术博客

vlambda
2021-07-31

golang中mysql连接池使用

点击文末“阅读原文”解锁资料！

在使用golang来处理数据库的时候，为了提升性能，往往都会使用连接池，有些人往往会自己实现一个连接池，用来互用mysql连接，但是如果你稍微细心一点，就会发现内建的sql包已经实现了连接池。sql.Open函数实际上式返回一个连接池对象，而不是单个连接。

golang本身没有提供链接mysql的驱动，但是却定义了数据库的标准接口(内建的sql包), 第三方开发实现这些接口就完成了相应驱动的开发。第三方提供mysql的驱动比较多，遵循官方sql接口规范的也有好几个, 但是使用最广的,github上星最多应该是https://github.com/go-sql-driver/mysql, 以下的所有操作都以该驱动进行演示。

1.数据库的基本操作

这里主要介绍数据库操作中一些常见操作，比如建表，以及数据的增删改查。

首先，我们需要创建一张表，用于存储数据, 我们可以通过db的Exec来执行SQL语句，比如下面是一个创建表的函数:

func createTable() {
 db, err := sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 checkErr(err)
 table := `CREATE TABLE IF NOT EXISTS test.user (
 user_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
 user_name VARCHAR(45) NOT NULL COMMENT '用户名称',
 user_age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年龄',
 user_sex TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户性别',
 PRIMARY KEY (user_id))
 ENGINE = InnoDB
 AUTO_INCREMENT = 1
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_general_ci
 COMMENT = '用户表'`
 if _, err := db.Exec(table); err != nil {
  checkErr(err)
 }
}

有了表过后，我们需要插入数据, 理论上可以将插入的SQL语句准备好, 填入Exec即可，但是sql已经对这种常用的场景抽象出了一个Prepare方法，Prepare方法将SQL的逻辑和数据剥离开来，通过占位符来生成一个SQL表达式(statement),然后表达式执行时，传入具体的需要插入的数据:

func insert() {
 db, err := sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 checkErr(err)
 stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)
 checkErr(err)
 res, err := stmt.Exec("tony", 20, 1)
 checkErr(err)
 id, err := res.LastInsertId()
 checkErr(err)
 fmt.Println(id)
}

插入数据过会我们就可以，从中查询数据记录了，查询出来的数据以行为单位进行组织（Rows)， Row包含字段和值，通过rows.Columns()获取字段，通过rows.Next()获取值，这里需要注意Next()这个方法，它和python里面的生成器概要很像，Next返回一个bool值，表示是否有新的row数据准备好了，如果准备好了，使用rows.Scan()来获取准备好的数据

func query() {
 db, err := sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 checkErr(err)
 rows, err := db.Query("SELECT * FROM user")
 checkErr(err)
 for rows.Next() {
    var userId int
    var userName string
    var userAge int
    var userSex int
    rows.Columns()
    err = rows.Scan(&userId, &userName, &userAge, &userSex)
    checkErr(err)
    fmt.Println(userId)
    fmt.Println(userName)
    fmt.Println(userAge)
    fmt.Println(userSex)
 }
}

这样扫描我们的确能获取到数据，但是数据并没有被友好的组织起来，在python的mysql驱动中提供一个简单方法可以将这些行数据组织成一个dict返回，因此在golang中，我们可以将rows的数据组织成一个map返回，方便使用。

func queryToMap() {
 db, err := sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 checkErr(err)
 rows, err := db.Query("SELECT * FROM user")
 checkErr(err)
 //字典类型
 //构造scanArgs、values两个数组，scanArgs的每个值指向values相应值的地址
 columns, _ := rows.Columns()
 scanArgs := make([]interface{}, len(columns))
 values := make([]interface{}, len(columns))
 for i := range values {
  scanArgs[i] = &values[i]
 }
 for rows.Next() {
  //将行数据保存到record字典
  err = rows.Scan(scanArgs...)
  record := make(map[string]string)
  for i, col := range values {
   if col != nil {
    record[columns[i]] = string(col.([]byte))
   }
  }
  fmt.Println(record)
 }
}

接下来是数据的更新, 更新和数据的插入原理一致，只是在准备的SQL里面通过WHERE指定条件，以更新指定的数据记录。

func update() {
 db, err := sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 checkErr(err)
 stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
 checkErr(err)
 res, err := stmt.Exec(21, 2, 1)
 checkErr(err)
 num, err := res.RowsAffected()
 checkErr(err)
 fmt.Println(num)
}

最后是数据的删除，同理

func remove() {
 db, err := sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 checkErr(err)
 stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
 checkErr(err)
 res, err := stmt.Exec(1)
 checkErr(err)
 num, err := res.RowsAffected()
 checkErr(err)
 fmt.Println(num)
}

2.如何设置连接池

数据库标准接口里面有3个方法用于设置连接池的属性: SetConnMaxLifetime, SetMaxIdleConns, SetMaxOpenConns

SetConnMaxLifetime: 设置一个连接的最长生命周期，因为数据库本身对连接有一个超时时间的设置，如果超时时间到了数据库会单方面断掉连接，此时再用连接池内的连接进行访问就会出错, 因此这个值往往要小于数据库本身的连接超时时间
SetMaxIdleConns: 连接池里面允许Idel的最大连接数, 这些Idel的连接就是并发时可以同时获取的连接,也是用完后放回池里面的互用的连接, 从而提升性能。
SetMaxOpenConns: 设置最大打开的连接数，默认值为0表示不限制。控制应用于数据库建立连接的数量，避免过多连接压垮数据库。

代码上使用就很简单了, 初始化db时，根据需求设置好连接池。

var db *sql.DB
 
func init() {
    db, _ = sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
    db.SetMaxOpenConns(2000)
    db.SetMaxIdleConns(1000)
 db.SetConnMaxLifetime(time.Minute * 60)
    db.Ping()
}

3.性能对比

连接池对性能的提升还是很明显的, 下面我们就测试对比一下使用连接池和不使用连接池时的性能差别。测试代码如下(不使用连接池时注释掉连接池相关设置):

package main
import (
 "database/sql"
 "fmt"
 "log"
 "net/http"
 "time"
 _ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
func init() {
 db, _ = sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 db.SetMaxOpenConns(2000)
 db.SetMaxIdleConns(1000)
 db.SetConnMaxLifetime(time.Minute * 60)
 db.Ping()
 createTable()
 insert()
}
func main() {
 startHttpServer()
}
func createTable() {
 db, err := sql.Open("mysql", "root:passwd@tcp(127.0.0.1:3306)/test?charset=utf8")
 checkErr(err)
 table := `CREATE TABLE IF NOT EXISTS test.user (
 user_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
 user_name VARCHAR(45) NOT NULL COMMENT '用户名称',
 user_age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年龄',
 user_sex TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户性别',
 PRIMARY KEY (user_id))
 ENGINE = InnoDB
 AUTO_INCREMENT = 1
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_general_ci
 COMMENT = '用户表'`
 if _, err := db.Exec(table); err != nil {
  checkErr(err)
 }
}
func insert() {
 stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)
 checkErr(err)
 res, err := stmt.Exec("tony", 20, 1)
 checkErr(err)
 id, err := res.LastInsertId()
 checkErr(err)
 fmt.Println(id)
}
func queryToMap() []map[string]string {
 var records []map[string]string
 rows, err := db.Query("SELECT * FROM user")
 defer rows.Close()
 checkErr(err)
 //字典类型
 //构造scanArgs、values两个数组，scanArgs的每个值指向values相应值的地址
 columns, _ := rows.Columns()
 scanArgs := make([]interface{}, len(columns))
 values := make([]interface{}, len(columns))
 for i := range values {
  scanArgs[i] = &values[i]
 }
 for rows.Next() {
  //将行数据保存到record字典
  err = rows.Scan(scanArgs...)
  record := make(map[string]string)
  for i, col := range values {
   if col != nil {
    record[columns[i]] = string(col.([]byte))
   }
  }
  records = append(records, record)
 }
 return records
}
func startHttpServer() {
 http.HandleFunc("/pool", pool)
 err := http.ListenAndServe(":9090", nil)
 if err != nil {
  log.Fatal("ListenAndServe: ", err)
 }
}
func pool(w http.ResponseWriter, r *http.Request) {
 records := queryToMap()
 fmt.Println(records)
 fmt.Fprintln(w, "finish")
}
func checkErr(err error) {
 if err != nil {
  fmt.Println(err)
  panic(err)
 }
}

带连接池的测试结果:

➜  ~ ab -c 100 -n 1000 'http://localhost:9090/pool'
This is ApacheBench, Version 2.3 <$Revision: 1706008 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking localhost (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests
Server Software:
Server Hostname:        localhost
Server Port:            9090
Document Path:          /pool
Document Length:        0 bytes
Concurrency Level:      100
Time taken for tests:   0.832 seconds
Complete requests:      1000
Failed requests:        928
   (Connect: 0, Receive: 0, Length: 928, Exceptions: 0)
Total transferred:      114144 bytes
HTML transferred:       6496 bytes
Requests per second:    1201.65 [#/sec] (mean)
Time per request:       83.219 [ms] (mean)
Time per request:       0.832 [ms] (mean, across all concurrent requests)
Transfer rate:          133.95 [Kbytes/sec] received
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    4   4.6      2      18
Processing:     8   79 107.2     47     489
Waiting:        0   71 107.9     40     488
Total:         12   82 106.4     49     491
Percentage of the requests served within a certain time (ms)
  50%     49
  66%     59
  75%     67
  80%     80
  90%    159
  95%    394
  98%    450
  99%    479
 100%    491 (longest request)

去除连接池的设置后的测试结果:

➜  ~ ab -c 100 -n 1000 'http://localhost:9090/pool'
This is ApacheBench, Version 2.3 <$Revision: 1706008 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking localhost (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests
Server Software:
Server Hostname:        localhost
Server Port:            9090
Document Path:          /pool
Document Length:        0 bytes
Concurrency Level:      100
Time taken for tests:   1.467 seconds
Complete requests:      1000
Failed requests:        938
   (Connect: 0, Receive: 0, Length: 938, Exceptions: 0)
Total transferred:      115374 bytes
HTML transferred:       6566 bytes
Requests per second:    681.83 [#/sec] (mean)
Time per request:       146.664 [ms] (mean)
Time per request:       1.467 [ms] (mean, across all concurrent requests)
Transfer rate:          76.82 [Kbytes/sec] received
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    1   1.7      1      19
Processing:     8  139 106.8    109     415
Waiting:        0  133 110.7     81     415
Total:         10  141 107.4    110     418
Percentage of the requests served within a certain time (ms)
  50%    110
  66%    210
  75%    237
  80%    250
  90%    285
  95%    321
  98%    378
  99%    393
 100%    418 (longest request)

4.结论

同样的并发情况下, 使用连接池比没使用快一倍, 在高并发的情况下，应该更明显。

往期推荐

vlambda博客
学习文章列表