Go工程选择开源分库分表中间件可用性测试

近期在寻找Go工程可以用的开源分库分表中间件，找了3个：ShardingSphere-Proxy，Kingshard，Gaea，下面给出测试过程和对比结果

ShardingSphere-Proxy

https://github.com/apache/shardingsphere
有apache基金会支持，社区活跃, star 20.2k

Kingshard

https://github.com/flike/kingshard
个人项目, github已经不更新了，star 6.4k

Gaea

https://github.com/XiaoMi/Gaea
小米团队发布，最近更新是2024年9月，还算比较新，star 2.7k

分表设置

t_user 分10个表
sharding-proxy是 t_user_0...9
kingshard和Gaea是 t_user_0000...0009

测试代码

 package main  import ( 	"database/sql" 	"encoding/json" 	"fmt" 	"math/rand" 	"sharding/internal/models" 	"strings" 	_ "github.com/go-sql-driver/mysql" // 导入 MySQL 驱动 	"log" )  const ( 	// 定义颜色的 ANSI 转义序列 	Reset  = "33[0m" 	Red    = "33[31m" 	Green  = "33[32m" 	Yellow = "33[33m" 	Blue   = "33[34m" )  func main() {  	log.Println(Red + "shardingsphere-proxy test" + Reset) 	dsn_proxy := "sharding:sharding@tcp(127.0.0.1:13308)/sharding_user?charset=utf8mb4&parseTime=True&loc=Local" 	sharding_query(dsn_proxy)  	log.Println(Yellow + "kingshard test" + Reset) 	dsn_kingshard := "kingshard:kingshard@tcp(127.0.0.1:9696)/sharding_user?charset=utf8mb4&parseTime=True&loc=Local" 	sharding_query(dsn_kingshard)  	log.Println(Blue + "gaea test" + Reset) 	dsn_gaea := "sharding_gaea:sharding_gaea@tcp(127.0.0.1:13306)/sharding_user?charset=utf8mb4&parseTime=True&loc=Local"  	sharding_query(dsn_gaea) }  func sharding_query(dsn string) {  	//sharding proxy  	shardingProxyConn, err := sql.Open("mysql", dsn) 	if err != nil { 		log.Println(err) 		return 	} 	defer shardingProxyConn.Close()  	id := rand.Intn(10) 	userName := fmt.Sprintf("test%d", id) 	notFound := false  	// 完整sql,无传参 	var uid uint64 	valScope := strings.ReplaceAll(userName, "'", "\'") 	err = shardingProxyConn.QueryRow(fmt.Sprintf("select id from t_user where login_name = '%s'", valScope)).Scan(&uid) 	if err != nil { 		if err == sql.ErrNoRows { 			log.Println("query 1 fail 1: ", err.Error()) 			notFound = true 		} else { 			log.Println("query 1 fail 2:", err) 		} 	} else { 		log.Println("query 1 success, uid=", uid) 	}  	// sql传参 	err = shardingProxyConn.QueryRow("select id from t_user where login_name = ?", userName).Scan(&uid) 	if err != nil { 		if err == sql.ErrNoRows { 			log.Println("query 2 fail 1: ", err.Error()) 			notFound = true 		} else { 			log.Println("query 2 fail 2:", err) 		} 	} else { 		log.Println("query 2 success, uid=", uid) 	} 	// create 	if notFound { 		// sharding-proxy 有 id 自动生成配置,这里方便测试需要，指定id 		sqlIns := "INsert into t_user (id, login_name,passwd,email) values (?, ?, ?, ?)" 		result, err1 := shardingProxyConn.Exec(sqlIns, id, userName, "test1234", "test@test.com") 		if err1 != nil { 			log.Println("insert fail:", err1) 		} else { 			rowsAffected, _ := result.RowsAffected() 			log.Println("insert RowsAffected ", rowsAffected) 		} 	}  	//Select  	rows, err := shardingProxyConn.Query("select id,login_name,email,create_time,update_time from t_user where login_name like ? limit 10", "%test%") 	if err != nil { 		log.Println(err) 		return 	} 	defer rows.Close()  	var userList []models.UserModel 	for rows.Next() { 		var u models.UserModel 		e := rows.Scan(&u.ID, &u.LoginName, &u.Email, &u.CreatedTime, &u.UpdateTime) 		if e != nil { 			log.Println("row scan err:", e) 			continue 		} 		userList = append(userList, u) 	} 	j, _ := json.Marshal(userList) 	log.Println("select result:", string(j)) }  

测试结论

shardingsphere-proxy 4.1

1. 分表查询不支持占位符传参，插曲：php测试过支持传参使用
2. 分表只支持全SQL

kingshard

1. 分表查询不支持占位符传参，插曲：php测试过支持传参使用
2. 分表查询支持全SQL

Gaea

1. 分表查询支持占位符传参
2. 分表查询支持全SQL

结论对比

Go代码的工程应用想用分库分表中间件，推荐次序

第1首选优先Gaea，因为支持占位符传参，github最近更新是2024年9月
第2是shardingsphere-proxy 4.1，因为不支持占位符传参，go工程想要用，就全得转换成完整SQL，但是配置比较简单，容易部署，有apache基金会支持，社区活跃
第3是kingshard，因为不支持占位符传参，就全得转换成完整SQL，并且github已经不更新了

测试结果日志

2025/04/19 17:04:24 shardingsphere-proxy test 2025/04/19 17:04:25 query 1 success, uid= 1119859588875681792 [mysql] 2025/04/19 17:04:25 packets.go:64 [warn] unexpected seq nr: expected 4, got 5 2025/04/19 17:04:25 query 2 fail 2: commands out of sync. You can't run this command now [mysql] 2025/04/19 17:04:25 packets.go:64 [warn] unexpected seq nr: expected 8, got 17 2025/04/19 17:04:25 select result: null  2025/04/19 17:04:25 kingshard test 2025/04/19 17:04:25 query 1 success, uid= 4 2025/04/19 17:04:25 query 2 fail 2: Error 1105 (HY000): prepare error ERROR 1146 (42S02): Table 'sharding_user.t_user' doesn't exist 2025/04/19 17:04:25 Error 1105 (HY000): prepare error ERROR 1146 (42S02): Table 'sharding_user.t_user' doesn't exist  2025/04/19 17:04:25 gaea test 2025/04/19 17:04:25 query 1 fail 1:  sql: no rows in result set 2025/04/19 17:04:25 query 2 fail 1:  sql: no rows in result set 2025/04/19 17:04:25 insert RowsAffected  1 2025/04/19 17:04:25 select result: [{"created_time":"2025-04-19T00:51:23+08:00","update_time":"2025-04-19T00:51:23+08:00","id":1,"Email":"test@test.com","LoginName":"test1","Passwd":"","LoginStatus":0,"LastLoginTime":null,"LoginCount":0}...]