golang mysql 如何设置最大连接数和最大空闲连接数

野性酷女 2023-06-01 06:23 24阅读 0赞

本文介绍golang 中连接MySQL时，如何设置最大连接数和最大空闲连接数。

关于最大连接数和最大空闲连接数，是定义在golang标准库中`database/sql`的。

文中例子连接MySQL用的SQL driver package是`github.com/go-sql-driver/mysql`.

设置最大连接数的接口是

func (db *DB) SetMaxOpenConns(n int)

设置连接MySQL可以打开的最大连接数。

如果n <= 0，表示打开的连接数没有限制。

默认为0，也就是不限制连接数。

另一个与连接数相关的参数是`MaxIdleConns`,表示最大空闲连接数。

如果`MaxIdleConns` 大于0，且大于`MaxOpenConns`，那么将调整`MaxIdleConns`等于`MaxOpenConns`，有多余的连接时会关闭多余的连接。

设置最大空闲连接数的接口是：

func (db *DB) SetMaxIdleConns(n int)

如果n<=0，表示不使用空闲连接池，即一个连接如果不使用，不会放入空闲连接池。因此，这种方式不会复用连接，每次执行SQL语句，都会重新建立新的连接。

默认的最大空闲连接数为2：  
`const defaultMaxIdleConns = 2`

关于打开的连接和空闲的连接之间的关系，补充一下：

打开的连接 = 正在使用的连接(inuse) + 处于空闲状态的连接(idle)

下面对最大连接数和最大空闲连接数做下测试和验证。

# 1.最大连接数测试 #

首先设置最大打开的连接数为1，接着开启20个goroutine，每个goroutine执行sql语句，打印执行sql使用的连接的connection id。并执行耗时的sql语句占用连接，观察其他需要执行SQL的goroutine的执行情况。

例子代码如下：

package main
    
    import (
            "database/sql"
            "log"
    
            _ "github.com/go-sql-driver/mysql"
    )
    
    var DB *sql.DB
    var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"
    
    func Init() {
            var err error
            DB, err = sql.Open("mysql", dataBase)
            if err != nil {
                    log.Fatalln("open db fail:", err)
            }
    
            DB.SetMaxOpenConns(1)
    
            err = DB.Ping()
            if err != nil {
                    log.Fatalln("ping db fail:", err)
            }
    }
    
    func main() {
            Init()
            
            //开启20个goroutine
            for i:=0; i < 20; i++ {
                    go one_worker(i)
            }
            
            select {
            }
    
    }
    
    func one_worker(i int) {
            var connection_id int
            err := DB.QueryRow("select CONNECTION_ID()").Scan(&connection_id)
            if err != nil {
                    log.Println("query connection id failed:", err)
                    return
            }
    
            log.Println("worker:", i, ", connection id:", connection_id)
    
            var result int
            err = DB.QueryRow("select sleep(10)").Scan(&result)
            if err != nil {
                    log.Println("query sleep connection id faild:", err)
                    return
            }
    
    }

**output**

2019/10/02 18:14:25 worker: 2 , connection id: 55
    2019/10/02 18:14:25 worker: 17 , connection id: 55
    2019/10/02 18:14:25 worker: 11 , connection id: 55
    2019/10/02 18:14:35 worker: 3 , connection id: 55
    2019/10/02 18:14:45 worker: 0 , connection id: 55
    2019/10/02 18:14:45 worker: 4 , connection id: 55
    2019/10/02 18:14:45 worker: 5 , connection id: 55
    2019/10/02 18:15:05 worker: 7 , connection id: 55
    2019/10/02 18:15:25 worker: 15 , connection id: 55
    2019/10/02 18:15:25 worker: 6 , connection id: 55
    2019/10/02 18:15:35 worker: 13 , connection id: 55
    2019/10/02 18:15:45 worker: 19 , connection id: 55
    2019/10/02 18:15:45 worker: 10 , connection id: 55
    2019/10/02 18:15:45 worker: 12 , connection id: 55
    2019/10/02 18:15:55 worker: 14 , connection id: 55
    2019/10/02 18:16:15 worker: 8 , connection id: 55
    2019/10/02 18:16:35 worker: 18 , connection id: 55
    2019/10/02 18:16:35 worker: 1 , connection id: 55
    2019/10/02 18:17:05 worker: 16 , connection id: 55
    2019/10/02 18:17:35 worker: 9 , connection id: 55

**使用show processlist查看连接**

**使用netstat 查看连接**

netstat -an | grep 3306
    tcp4       0      0  127.0.0.1.3306         127.0.0.1.59518        ESTABLISHED
    tcp4       0      0  127.0.0.1.59518        127.0.0.1.3306         ESTABLISHED
    tcp46      0      0  *.3306                 *.*                    LISTEN

从结果可以看到，20个goroutine轮流使用同一个连接(connection id 为55)执行sql语句。

当连接被占用时，其他尝试使用连接的goroutine会被阻塞。直到连接使用完后，其他goroutine才可以使用连接。

即使多个goroutine在执行SQL，也没有创建多个连接。

因此，最大连接数设置生效。

有些读者可能会问，没有看到设置最大空闲连接数，此时最大空间连接数是多少？

前面已经提到，默认的最大空闲连接数是2.

下面再来测试下最大空间连接数。

# 2.最大空闲连接数测试 #

下面例子中，设置最大连接数为1，最大空闲连接数为0.

并且每隔3s执行一条SQL语句。

代码如下：

package main
    
    import (
            "database/sql"
            "log"
            "time"
    
            _ "github.com/go-sql-driver/mysql"
    
    )
    
    var DB *sql.DB
    var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"
    
    func mysqlInit() {
            var err error
            DB, err = sql.Open("mysql", dataBase)
            if err != nil {
                    log.Fatalln("open db fail:", err)
            }
    
            DB.SetMaxOpenConns(1)
            DB.SetMaxIdleConns(0)
    
            err = DB.Ping()
            if err != nil {
                    log.Fatalln("ping db fail:", err)
            }
    }
    
    func main() {
            mysqlInit()
    
            for {
                    execSql()
                    time.Sleep(3*time.Second)
            }
    }
    
    
    func execSql() {
            var connection_id int
            err := DB.QueryRow("select CONNECTION_ID()").Scan(&connection_id)
            if err != nil {
                    log.Println("query connection id failed:", err)
                    return
            }
    
            log.Println("connection id:", connection_id)
    }

output:

2019/10/13 23:06:00 connection id: 26
    2019/10/13 23:06:03 connection id: 27
    2019/10/13 23:06:06 connection id: 28
    2019/10/13 23:06:09 connection id: 29
    2019/10/13 23:06:12 connection id: 30
    2019/10/13 23:06:15 connection id: 31
    2019/10/13 23:06:18 connection id: 32
    2019/10/13 23:06:21 connection id: 33
    2019/10/13 23:06:24 connection id: 34
    2019/10/13 23:06:27 connection id: 35
    2019/10/13 23:06:30 connection id: 36
    2019/10/13 23:06:33 connection id: 37
    2019/10/13 23:06:36 connection id: 38

从结果中可以看出，每次执行SQL使用的连接connection id都不同。

设置最大空闲连接数为0，每次执行SQL后，连接不会放入空闲连接池，而是会被关闭，下次执行SQL时，会重新建立新的连接。

# 3.参考 #

[Connection pool and timeouts][]

[Golang MySQL 连接和连接池][Golang MySQL]

[Connection pool and timeouts]: https://github.com/go-sql-driver/mysql#connection-pool-and-timeouts
[Golang MySQL]: https://www.jianshu.com/p/340eb943be2e