本文对三种数据库访问包(sql.DB、worm、gorm)进行两种查询类型(获取单行数据、获取多行数据)的性能进行对比测试。另外为了使大家对预处理的性能有所了解,增加了worm开启预处理下的性能测试环节。
const dsn_test = "account:pwd@tcp(127.0.0.1:3306)/db?charset=utf8&parseTime=True"
func initMySql(cnnstr string) (*sql.DB, error) {
dbcnn, err := sql.Open("mysql", cnnstr)
if err != nil {
return nil, err
}
err = dbcnn.Ping()
if err != nil {
return nil, err
}
return dbcnn, nil
}
func BenchmarkDbQueryRow(b *testing.B) {
dbcnn, _ := initMySql(dsn_test)
b.StopTimer()
b.StartTimer()
for i := 0; i < b.N; i++ {
var ent User
rows, err := dbcnn.Query("select id,name,age from user where id=? limit 1", 1)
if err != nil {
b.Error(err)
return
}
if !rows.Next() {
b.Error(err)
rows.Close()
return
}
err = rows.Scan(&ent.DB_id, &ent.DB_name, &ent.Age)
if err != nil {
b.Error(err)
rows.Close()
return
}
rows.Close()
}
b.StopTimer()
}
func BenchmarkModelGet(b *testing.B) {
dbcnn, _ := initMySql(dsn_test)
InitEngine(&dialectMysql{}, dbcnn)
ShowSqlLog(false)
b.StopTimer()
b.StartTimer()
for i := 0; i < b.N; i++ {
var ent User
_, err := Model(&ent).Where("id=?", 1).Select("id", "name", "age").Get()
if err != nil{
b.Error(err)
return
}
}
b.StopTimer()
}
func BenchmarkModelGet(b *testing.B) {
db, err := gorm.Open(mysql.Open(dsn_test), &gorm.Config{})
if err != nil {
b.Fatal(err)
}
b.StopTimer()
b.StartTimer()
for i := 0; i < b.N; i++ {
var user User
db.Where("id = ?",1).First(&user)
}
b.StopTimer()
}
func BenchmarkModelGetWithCache(b *testing.B) {
dbcnn, _ := initMySql(dsn_test)
InitEngine(&dialectMysql{}, dbcnn)
ShowSqlLog(false)
UsePrepare(true)
b.StopTimer()
b.StartTimer()
for i := 0; i < b.N; i++ {
var ent User
_, err := Model(&ent).Where("id=?", 1).Select("id", "name", "age").Get()
if err != nil{
b.Error(err)
return
}
}
b.StopTimer()
}
pkg: sql.DB
BenchmarkDbQueryRow-2 3547 326038 ns/op 899 B/op 23 allocs/op
pkg: worm
BenchmarkModelGet-2 3247 352400 ns/op 1932 B/op 32 allocs/op
pkg: gorm
BenchmarkModelGet-2 2780 367584 ns/op 4467 B/op 73 allocs/op
pkg: worm(开启预处理)
BenchmarkGetWithCache-2 8248 183477 ns/op 1809 B/op 29 allocs/op
func BenchmarkDbQueryRows(b *testing.B) {
dbcnn, _ := initMySql(dsn_test)
b.StopTimer()
b.StartTimer()
var arr []User
for i := 0; i < b.N; i++ {
var ent User
rows, err := dbcnn.Query("select id,name,age from user where id>? and name is not null", 0)
if err != nil {
b.Error(err)
return
}
for rows.Next() {
err = rows.Scan(&ent.DB_id, &ent.DB_name, &ent.Age)
if err != nil {
b.Error(err)
rows.Close()
return
}
arr = append(arr, ent)
}
rows.Close()
}
b.StopTimer()
}
func BenchmarkModelFind(b *testing.B) {
dbcnn, _ := initMySql(dsn_test)
InitEngine(&dialectMysql{}, dbcnn)
ShowSqlLog(false)
b.StopTimer()
b.StartTimer()
for i := 0; i < b.N; i++ {
var arr []User
err := Model(&User{}).Where("id>? and name is not null", 0).Select("id", "name", "age").Find(&arr)
if err != nil{
b.Error(err)
return
}
}
b.StopTimer()
}
func BenchmarkModelFind(b *testing.B) {
db, err := gorm.Open(mysql.Open(dsn_test), &gorm.Config{})
if err != nil {
b.Fatal(err)
}
b.StopTimer()
b.StartTimer()
for i := 0; i < b.N; i++ {
var users []User
db.Where("id>? and name is not null", 0).Find(&users)
}
b.StopTimer()
}
func BenchmarkModelFindWithCache(b *testing.B) {
dbcnn, _ := initMySql(dsn_test)
InitEngine(&dialectMysql{}, dbcnn)
ShowSqlLog(false)
UsePrepare(true)
b.StopTimer()
b.StartTimer()
for i := 0; i < b.N; i++ {
var arr []User
err := Model(&User{}).Where("id>? and name is not null", 0).Select("id", "name", "age").Find(&arr)
if err != nil{
b.Error(err)
return
}
}
b.StopTimer()
}
pkg: sql.DB
BenchmarkDbQueryRows-2 3234 429018 ns/op 8361 B/op 50 allocs/op
pkg: worm
BenchmarkModelFind-2 2562 466247 ns/op 5451 B/op 79 allocs/op
pkg: gorm
BenchmarkModelFind-2 2342 491657 ns/op 9040 B/op 239 allocs/op
pkg: worm(开启预处理)
BenchmarkFindWithCache-2 4662 258889 ns/op 5330 B/op 76 allocs/op