Gorm In 占位符替换大数据量耗时太久解决方法

1.问题

在 gorm 中使用 in 语法如下:

var db *gorm.DB
// 初始化db（省略）
db, err := gorm.Open(mysql.New(mysql.Config{
		DSN: "user:pwd@tcp(host:port)/db?charset=utf8&parseTime=True&loc=Local", 
	}), &gorm.Config{})
	
err = db.Debug().Where("id in(?)", ids).Find(&model).Error

当表中数据量大且 切片 ids 数据量大时，查询会变得很慢

2.原因

gorm 将 ids 替换 sql 占位符 ? 耗时久

3.解决方案

3.1.方案①

可以手动将切片拼接，再替换占位符

err := db.Debug().Where("id in(?)", strings.Join(ids,",")).Find(&model).Error

• 优点：处理简单，影响范围小
• 缺点：所有场景都需要修改

3.2.方案②

另外可以在初始化 db 时添加 interpolateparams 参数为 MySQL 开启 interpolateparams 以减少 roundtrip

db, err := gorm.Open(mysql.New(mysql.Config{
		DSN: "user:pwd@tcp(host:port)/db?charset=utf8&interpolateParams=true&parseTime=True&loc=Local", 
	}), &gorm.Config{})

注意：
使用该方案不能同时开启 gorm 预编译PrepareStmt，否则耗时仍然很久（原因未知）

• 优点：只需修改初始化 db 代码
• 缺点：影响范围广，不能与部分编码一起使用【若一起使用，有sql注入风险】

4.环境复现

1. 创建表

CREATE TABLE `models_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `created_at` int DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51023 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. 定义model

type ModelsTest struct {
	Id        int
	Name      string
	CreatedAt int64 
}

func (ModelsTest) TableName() string {
	return "models_test"
}

3. 初始化db

func GetBiReportDb() (*gorm.DB, error) {
	db, err := gorm.Open(mysql.New(mysql.Config{
			DSN: "user:pwd@tcp(host:port)/db?charset=utf8&parseTime=True&loc=Local", 
		}), &gorm.Config{})
	// 处理 error
	return db, nil
}

var db *gorm.DB

func init() {
	db, _ = GetBiReportDb()
}

4. 批量插入数据

func TestInQuery(t *testing.T) {
	var datas []map[string]interface{}

	for i := 0; i < 50000; i++ {
		data := make(map[string]interface{})
		data["Name"] = fmt.Sprintf("test%v", i)

		datas = append(datas, data)
	}

	// Create from map
	err := db.Table("declaring_models_test").Debug().CreateInBatches(&datas, 500).Error
	// 处理 error
	... 
}

5. 查询数据

func TestInQuery(t *testing.T) {
	...
	
	var ids []string
	for i := 0; i < 30000; i++ {
		ids = append(ids, strconv.Itoa(i))
	}

	now := time.Now().Unix()
	err := db.Debug().Where("id in(?)", ids).Find(&d).Error
	// 处理 error
	fmt.Println("查询用时：", time.Now().Unix()-now, "s")
}

5.参考资料

1. gorm缓存预编译语句
2. 为 MySQL 开启 interpolateparams 以减少 roundtrip
3. gorm性能优化