Sqlx Gorm 对比

170W数据下，sqlx和gorm查询不同条数数据性能对比，机器配置 8核/16G，mysql分别最大连接数为1 和 最大连接数500最大空闲连接数100的情况下测试。

package tesstimport ("fmt""testing""time""github.com/jmoiron/sqlx""gorm.io/driver/mysql""gorm.io/gorm"
)type ShardingDB struct {ID        uint64    `db:"id" gorm:"column:id"`DBID      string    `db:"db_id" gorm:"column:db_id"`Host      string    `db:"host" gorm:"column:host"`Port      int32     `db:"port" gorm:"column:port"`User      string    `db:"user" gorm:"column:user"`Password  string    `db:"password" gorm:"column:password"`Memo      string    `db:"memo" gorm:"column:memo"`State     int32     `db:"state" gorm:"column:state"`CreatedAt time.Time `db:"create_time" gorm:"column:create_time"`UpdatedAt time.Time `db:"update_time" gorm:"column:update_time"`
}// TableName returns table name of sharding_db.
func (s *ShardingDB) TableName() string {return "sharding_db"
}var (dbhostsip  = "127.0.0.1"  // IP地址dbport     = 3306         // Portdbusername = "root"       // 用户名dbpassword = "admin"      // 密码dbname     = "test" // 表名
)func Benchmark(b *testing.B) {dsn := fmt.Sprintf("%s:%s@(%s:%d)/%s?charset=utf8&parseTime=True&loc=UTC",dbusername, dbpassword, dbhostsip, dbport, dbname)limits := []int{5,50,500,10000,}sqlxDB, _ := sqlx.Connect("mysql", dsn)sqlxDB.SetMaxOpenConns(500)sqlxDB.SetMaxIdleConns(100)gormDB, err := gorm.Open(mysql.Open(dsn), &gorm.Config{NowFunc: func() time.Time { return time.Now().UTC().Round(time.Microsecond) }})db, _ := gormDB.DB()db.SetMaxOpenConns(500)db.SetMaxIdleConns(100)fmt.Println("=============================== CPU:8 MEM:16G MaxOpenConns:500 MaxOpenConns:100 ====================================")for _, lim := range limits {lim := lim// Benchmark sqlxb.Run(fmt.Sprintf("sqlx limit:%d", lim), func(b *testing.B) {for i := 0; i < b.N; i++ {q := fmt.Sprintf("SELECT * FROM sharding_db ORDER BY id LIMIT %d", lim)res := []ShardingDB{}err := sqlxDB.Select(&res, q)if err != nil {b.Fatal(err)}}})if err != nil {panic(err)}// Benchmark gormDBb.Run(fmt.Sprintf("gormDB limit:%d", lim), func(b *testing.B) {for i := 0; i < b.N; i++ {for i := 0; i < b.N; i++ {var res = []ShardingDB{}err := gormDB.Order("id").Limit(lim).Find(&res).Errorif err != nil {b.Fatal(err)}}}})fmt.Println("==================================================================================================================")}
}

测试结果：

=============================== CPU:8 MEM:16G MaxOpenConns:1 ====================================
goos: linux
goarch: amd64
pkg: go-operation-demo/sqlx/bench-test-demo
Benchmark/sqlx_limit:5-8         	   10000	    102170 ns/op	    7275 B/op	     134 allocs/op
Benchmark/gormDB_limit:5-8       	     100	  13250163 ns/op	 1199221 B/op	   29300 allocs/op
==================================================================================================================
Benchmark/sqlx_limit:50-8        	    4472	    270684 ns/op	   52652 B/op	    1082 allocs/op
Benchmark/gormDB_limit:50-8      	     100	  52683096 ns/op	 7362504 B/op	  250000 allocs/op
==================================================================================================================
Benchmark/sqlx_limit:500-8       	     814	   1480242 ns/op	  480048 B/op	   10536 allocs/op
Benchmark/gormDB_limit:500-8     	     100	 395335077 ns/op	68082795 B/op	 2455402 allocs/op
==================================================================================================================
Benchmark/sqlx_limit:10000-8     	      39	  28053826 ns/op	14964052 B/op	  210053 allocs/op
Benchmark/gormDB_limit:10000-8   	      14	1086946301 ns/op	254799549 B/op	 6860912 allocs/op
==================================================================================================================
PASS
ok  	go-operation-demo/sqlx/bench-test-demo	66.189s

=============================== CPU:8 MEM:16G MaxOpenConns:500 MaxIdleConns:100 ====================================
goos: linux
goarch: amd64
pkg: go-operation-demo/sqlx/bench-test-demo
Benchmark/sqlx_limit:5-8         	   10000	    100589 ns/op	    7274 B/op	     134 allocs/op
Benchmark/gormDB_limit:5-8       	     100	  13322948 ns/op	 1199224 B/op	   29300 allocs/op
==================================================================================================================
Benchmark/sqlx_limit:50-8        	    4651	    265969 ns/op	   52652 B/op	    1082 allocs/op
Benchmark/gormDB_limit:50-8      	     100	  52195341 ns/op	 7362505 B/op	  250000 allocs/op
==================================================================================================================
Benchmark/sqlx_limit:500-8       	     820	   1466335 ns/op	  480048 B/op	   10536 allocs/op
Benchmark/gormDB_limit:500-8     	     100	 395699610 ns/op	68082802 B/op	 2455402 allocs/op
==================================================================================================================
Benchmark/sqlx_limit:10000-8     	      40	  28599357 ns/op	14964058 B/op	  210053 allocs/op
Benchmark/gormDB_limit:10000-8   	      15	1158298778 ns/op	272999555 B/op	 7350977 allocs/op
==================================================================================================================
PASS
ok  	go-operation-demo/sqlx/bench-test-demo	68.395s

总结：

Sqlx 查询时间和内存占用远远小于 Gorm。

Sqlx 封装对比

对于系统db层面来说，每个model都需要CRUD操作，如果不对Sqlx再封装一层的话，使用起来需要写大量的Sqlx的冗余代码。但是如果封装一层的话，就需要考虑性能消耗和临时变量的消耗，这里仅举一个Select的示例。

测试代码

package databaseimport ("fmt""log""testing""time""github.com/jmoiron/sqlx"_ "gorm.io/driver/mysql"
)type ShardingDB struct {ID        uint64    `db:"id" gorm:"column:id"`DBID      string    `db:"db_id" gorm:"column:db_id"`Host      string    `db:"host" gorm:"column:host"`Port      int32     `db:"port" gorm:"column:port"`User      string    `db:"user" gorm:"column:user"`Password  string    `db:"password" gorm:"column:password"`Memo      string    `db:"memo" gorm:"column:memo"`State     int32     `db:"state" gorm:"column:state"`CreatedAt time.Time `db:"create_time" gorm:"column:create_time"`UpdatedAt time.Time `db:"update_time" gorm:"column:update_time"`
}// TableName returns table name of sharding_db.
func (s *ShardingDB) TableName() string {return "sharding_db"
}var (dbhostsip  = "127.0.0.1"  // IP地址dbport     = 3306         // Portdbusername = "root"       // 用户名dbpassword = "admin"      // 密码dbname     = "test" // 表名
)func Benchmark(b *testing.B) {dsn := fmt.Sprintf("%s:%s@(%s:%d)/%s?charset=utf8&parseTime=True&loc=UTC",dbusername, dbpassword, dbhostsip, dbport, dbname)limits := []int{5,50,500,10000,}fmt.Println("=============================== CPU:8 MEM:16G MaxOpenConns:500 MaxIdleConns:100 ====================================")for _, lim := range limits {lim := lim// Benchmark sqlxb.Run(fmt.Sprintf("sqlx query limit:%d", lim), func(b *testing.B) {sqlxDB, err := sqlx.Connect("mysql", dsn)if err != nil {log.Fatal(err)}sqlxDB.SetMaxOpenConns(1)for i := 0; i < b.N; i++ {sqlxQuery(sqlxDB, lim)}})// 指针使用方式不可行，没办法映射字段// b.Run(fmt.Sprintf("sqlx ptr query limit:%d", lim), func(b *testing.B) {// 	sqlxDB, err := sqlx.Connect("mysql", dsn)// 	if err != nil {// 		log.Fatal(err)// 	}// 	sqlxDB.SetMaxOpenConns(1)//// 	for i := 0; i < b.N; i++ {// 		sqlxPtrQuery(sqlxDB, lim)// 	}// })b.Run(fmt.Sprintf("sqlx select query limit:%d", lim), func(b *testing.B) {sqlxDB, err := sqlx.Connect("mysql", dsn)if err != nil {log.Fatal(err)}sqlxDB.SetMaxOpenConns(1)for i := 0; i < b.N; i++ {sqlxSelectQuery(sqlxDB, lim)}})// 指针使用方式不可行，没办法映射字段// b.Run(fmt.Sprintf("sqlx select ptr query limit:%d", lim), func(b *testing.B) {// 	sqlxDB, err := sqlx.Connect("mysql", dsn)// 	if err != nil {// 		log.Fatal(err)// 	}// 	sqlxDB.SetMaxOpenConns(1)//// 	for i := 0; i < b.N; i++ {// 		sqlxSelectPtrQuery(sqlxDB, lim)// 	}// })fmt.Println("==================================================================================================================")}
}func sqlxQuery(sqlxDB *sqlx.DB, lim int) (shardingDB []ShardingDB) {q := fmt.Sprintf("SELECT * FROM sharding_db ORDER BY id LIMIT %d", lim)err := sqlxDB.Select(&shardingDB, q)if err != nil {log.Fatal(err)}return
}func sqlxPtrQuery(sqlxDB *sqlx.DB, lim int) (shardingDB *[]ShardingDB) {q := fmt.Sprintf("SELECT * FROM sharding_db ORDER BY id LIMIT %d", lim)err := sqlxDB.Select(shardingDB, q)if err != nil {log.Fatal(err)}return
}func sqlxSelectQuery(sqlxDB *sqlx.DB, lim int) (shardingDB []ShardingDB) {q := fmt.Sprintf("SELECT * FROM sharding_db ORDER BY id LIMIT %d", lim)err := SqlxSelect(sqlxDB, &shardingDB, q)if err != nil {log.Fatal(err)}return
}func sqlxSelectPtrQuery(sqlxDB *sqlx.DB, lim int) (shardingDB *[]ShardingDB) {q := fmt.Sprintf("SELECT * FROM sharding_db ORDER BY id LIMIT %d", lim)err := SqlxSelect(sqlxDB, shardingDB, q)if err != nil {log.Fatal(err)}return
}

封装举例

package databaseimport "github.com/jmoiron/sqlx"var (SqlxSelect = sqlxSelectFunc
)// ================== raw execute func ==================
func sqlxSelectFunc(db *sqlx.DB, dest interface{}, query string, args ...interface{}) error {query, args, err := sqlx.In(query, args...)if err != nil {return err}err = db.Select(dest, query, args...)return err
}

测试结果

=============================== CPU:8 MEM:16G MaxOpenConns:500 MaxIdleConns:100 ====================================
goos: linux
goarch: amd64
pkg: go-operation-demo/sqlx/bench-test-demo/pkg-comp
Benchmark/sqlx_query_limit:5-8         	   12006	     99514 ns/op	    7276 B/op	     134 allocs/op
Benchmark/sqlx_select_query_limit:5-8  	   10000	    101496 ns/op	    7276 B/op	     134 allocs/op
==================================================================================================================
Benchmark/sqlx_query_limit:50-8        	    4536	    263568 ns/op	   52654 B/op	    1082 allocs/op
Benchmark/sqlx_select_query_limit:50-8 	    4440	    264001 ns/op	   52655 B/op	    1082 allocs/op
==================================================================================================================
Benchmark/sqlx_query_limit:500-8       	     835	   1447305 ns/op	  480066 B/op	   10536 allocs/op
Benchmark/sqlx_select_query_limit:500-8         	     826	   1446552 ns/op	  480066 B/op	   10536 allocs/op
==================================================================================================================
Benchmark/sqlx_query_limit:10000-8              	      40	  26950958 ns/op	14964317 B/op	  210055 allocs/op
Benchmark/sqlx_select_query_limit:10000-8       	      40	  26891873 ns/op	14964402 B/op	  210054 allocs/op
==================================================================================================================
PASS
ok  	go-operation-demo/sqlx/bench-test-demo/pkg-comp	10.580s