Go Mysql数据库连接池的使用

建立一个mysql连接池将极大的缩短应用的响应时间, 减少频繁的io开销以及gc, 这在golang里面也是很容易就实现的

示例代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
// main.go
package main

import (
"database/sql"
"fmt"
_ "github.com/GO-SQL-Driver/MySQL"
"net/http"
"time"
)

var db *sql.DB

func init() {
db, _ = sql.Open("mysql", "root:123123@tcp(localhost:33060)/go?charset=utf8")
// 设置最大连接数
db.SetMaxOpenConns(300)
// 设置最大空闲连接数
db.SetMaxIdleConns(100)
// 设置每个链接的过期时间
db.SetConnMaxLifetime(time.Second * 5)
err := db.Ping()
checkErr(err)

}

func main() {
startServer(":9999")
}

func startServer(port string) {
http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
record := doSomething()
fmt.Fprintln(w, record)
})

fmt.Printf("listening http://localhost%s\n", port)
err := http.ListenAndServe(port, nil)
checkErr(err)
}

func doSomething() map[string]string {
rows, err := db.Query("SELECT * FROM test LIMIT 1")
checkErr(err)
defer rows.Close()

columns, _ := rows.Columns()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for j := range values {
scanArgs[j] = &values[j]
}

record := make(map[string]string)
for rows.Next() {
//将行数据保存到record字典
err = rows.Scan(scanArgs...)
for i, col := range values {
if col != nil {
record[columns[i]] = string(col.([]byte))
}
}
}

// fmt.Println(record)

return record
}

func doSomething2() map[string]string {
dbConn, _ := sql.Open("mysql", "root:123123@tcp(localhost:33060)/go?charset=utf8")
defer dbConn.Close()

rows, err := dbConn.Query("SELECT * FROM test LIMIT 1")
checkErr(err)
defer rows.Close()

columns, _ := rows.Columns()
scanArgs := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for j := range values {
scanArgs[j] = &values[j]
}

record := make(map[string]string)
for rows.Next() {
//将行数据保存到record字典
err = rows.Scan(scanArgs...)
for i, col := range values {
if col != nil {
record[columns[i]] = string(col.([]byte))
}
}
}

// fmt.Println(record)

return record
}

func checkErr(err error) {
if err != nil {
panic(err)
}
}

测试代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
// main_test.go
package main

import (
"testing"
)

func TestDoSomething(t *testing.T) {
record := doSomething()

t.Log(record)
}

func BenchmarkDoSomething(b *testing.B) {
for i := 0; i < b.N; i++ {
doSomething()
}
}

func BenchmarkDoSomethingParallel(b *testing.B) {
b.RunParallel(func(pb *testing.PB) {
for pb.Next() {
doSomething()
}
})
}

func BenchmarkDoSomethingWithoutPoll(b *testing.B) {
for i := 0; i < b.N; i++ {
doSomething2()
}
}

func BenchmarkDoSomethingWithoutPollParallel(b *testing.B) {
b.RunParallel(func(pb *testing.PB) {
for pb.Next() {
doSomething2()
}
})
}

测试输出对比:

1
2
3
4
5
6
7
8
9
10
$ go test --bench=.

goos: darwin
goarch: amd64
BenchmarkDoSomething-4 20000 67898 ns/op
BenchmarkDoSomethingParallel-4 50000 29347 ns/op
BenchmarkDoSomethingWithoutPoll-4 1000 8400516 ns/op
BenchmarkDoSomethingWithoutPollParallel-4 2000 8167164 ns/op
PASS
ok _/Users/gpf/Documents/go/code/mysql_curd/pool 28.857s

关键就是这几个方法:

1
2
3
4
5
6
7
db, _ = sql.Open("mysql", "root:123123@tcp(localhost:33060)/go?charset=utf8")
// 设置最大连接数
db.SetMaxOpenConns(300)
// 设置最大空闲连接数
db.SetMaxIdleConns(100)
// 设置每个链接的过期时间
db.SetConnMaxLifetime(time.Second * 5)

设置超时时间很有必要, 避免出现server端主动关闭的情况 mysql has gone away...

代码来源