go mysql数据库连接池的使用

  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)