最近在开发的一个go项目中遇到一个很奇怪的问题,使用gorm去查询数据的时候速度很慢,不同的代码方式差异很大,四种查询方式代码示例如下:
gorm 的版本为 gorm.io/gorm v1.23.2
// gorm 的版本为 gorm.io/gorm v1.23.2
// req.KnowledgeIds 字符串元素个数在1500左右
var ids []uint
if req.KnowledgeIds != "" {
//方式一 参数化查询 占位符 此种形式的查询统计到需要花费2900ms+
txd := global.Db().Raw("SELECT q.id FROM mch_tk_questions as q INNER JOIN mch_tk_knowledges AS k on k.q_vid = q.id WHERE q.mid = 123 AND q.bid = 456 AND q.deleted_at IS NULL AND (k.mid = 123 AND k.bid = 456) AND k.knowledge_id in ? AND k.deleted_at IS NULL GROUP BY `q`.`id` ORDER BY id DESC LIMIT 10 OFFSET 100000", utils.String2UintSlice(req.KnowledgeIds, ",")).Find(&ids)
if txd.Error != nil {
global.Logger.Error("查询id失败", zap.Error(txd.Error))
global.RespFailList(c, utils.RespList(0, 1, nil), "retrive_fail", nil)
return
}
//方式二 直接拼接到sql语句里 此种形式的查询统计到需要花费600ms
txd = global.Db().Raw("SELECT q.id FROM mch_tk_questions as q INNER JOIN mch_tk_knowledges AS k on k.q_vid = q.id WHERE q.mid = 123 AND q.bid = 456 AND q.deleted_at IS NULL AND (k.mid = 123 AND k.bid = 456) AND k.knowledge_id in (" + req.KnowledgeIds + ") AND k.deleted_at IS NULL GROUP BY `q`.`id` ORDER BY id DESC LIMIT 10 OFFSET 100000").Find(&ids)
if txd.Error != nil {
global.Logger.Error("查询id失败", zap.Error(txd.Error))
global.RespFailList(c, utils.RespList(0, 1, nil), "retrive_fail", nil)
return
}
//方式三 此种形式的查询统计到需要花费2900ms+
var resp []uint
knowledgeIds := utils.String2UintSlice(req.KnowledgeIds, ",")
tx := global.Db().Table("mch_tk_questions as q")
tx.Where("q.mid = ? AND q.bid = ? AND q.deleted_at IS NULL", 123, 456)
tx.Joins("INNER JOIN mch_tk_knowledges AS k on k.q_vid = q.id")
tx.Where("k.mid = ? AND k.bid = ?", 123, 456)
tx.Where("k.knowledge_id in ?", knowledgeIds)
tx.Where("k.deleted_at IS NULL")
tx.Select("q.id")
tx.Group("q.id")
tx.Order("q.id DESC")
tx.Limit(10).Offset(100000)
tx.Find(&resp)
if tx.Error != nil {
global.Logger.Error("查询id失败", zap.Error(tx.Error))
global.RespFailList(c, utils.RespList(0, 1, nil), "retrive_fail", nil)
return
}
//方式四 此种形式的查询统计到需要花费600ms
tx = global.Db().Table("mch_tk_questions as q")
tx.Where("q.mid = ? AND q.bid = ? AND q.deleted_at IS NULL", 123, 456)
tx.Joins("INNER JOIN mch_tk_knowledges AS k on k.q_vid = q.id")
tx.Where("k.mid = ? AND k.bid = ?", 123, 456)
tx.Where("k.knowledge_id in (" + req.KnowledgeIds + ")")
tx.Where("k.deleted_at IS NULL")
tx.Select("q.id")
tx.Group("q.id")
tx.Order("q.id DESC")
tx.Limit(10).Offset(100000)
tx.Find(&resp)
if tx.Error != nil {
global.Logger.Error("查询id失败", zap.Error(tx.Error))
global.RespFailList(c, utils.RespList(0, 1, nil), "retrive_fail", nil)
return
}
}
上述四种方式打印的sql如下,截取了执行慢的日志记录做了一些隐私处理,测试过程中相同的sql复制下来在阿里云 dms的执行时间是600ms左右:
May 06 11:58:27 SZH-MANAGE ys[4043179]: [2595.127ms] [rows:6] SELECT q.id FROM mch_tk_questions as q INNER JOIN mch_tk_knowledges AS k on k.q_vid = q.id WHERE q.mid = 123 AND q.bid = 456 AND q.deleted_at IS NULL AND (k.mid = 123 AND k.bid = 456) AND k.knowledge_id in (187549741883945976,187549741884011512,187549741884077048,...1500个uint类型的数据) AND k.deleted_at IS NULL GROUP BY `q`.`id` ORDER BY id DESC LIMIT 10 OFFSET 121160
同样的sql语句通过mysql客户端查询耗时600ms左右,和上述方式二和方式四的时间是一致的。
不明白为什么按照gorm框架手册指导的方式耗时竟然这么大,
方式一和方式二对比,方式三和方式四对比,用占位符和直接拼接的性能差异竟然这么大
各位有遇见过这种情况吗