gorm查询性能慢(in查询 ，?占位符)

Question

最近在开发的一个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框架手册指导的方式耗时竟然这么大，
方式一和方式二对比，方式三和方式四对比，用占位符和直接拼接的性能差异竟然这么大
各位有遇见过这种情况吗

Answer 1

引自免费微信小程序：皆我百晓生

针对您提供的GORM查询性能问题，这里有几个可能的原因和优化建议：

1. 参数化查询：在SQL语句中使用占位符（问号）可以防止SQL注入攻击，并且有助于数据库优化器重用执行计划。在您的例子中，前两种方法使用了字符串拼接，这可能会导致每次执行时都创建新的执行计划，而第三种方法虽然使用了参数化查询，但是它将整个知识ID列表作为单个参数传递给in操作符，这同样可能导致性能问题。

2. 批量插入：对于大量的知识ID，可以尝试使用MySQL的批量插入功能，如ON DUPLICATE KEY UPDATE语句，但这取决于您的具体业务需求和表结构。

3. 索引优化：检查您的表是否有适当的索引，特别是用于连接、过滤和排序的列。例如，mid, bid, deleted_at, 和 knowledge_id 都应该是索引列。

4. 分批处理：当处理大量数据时，一次性加载所有数据可能会消耗大量内存。您可以考虑分批处理查询结果，比如每次只获取一部分数据。

根据这些信息，我可以为您提供一种优化后的查询实现，利用了参数化的IN查询并进行了分批处理：

const batchSize = 100 // 根据实际场景调整分批大小

func batchProcess识IDs(knowledgeIds []uint) ([][]uint, error) {
    var result [][]uint
    for i := 0; i < len(knowledgeIds); i += batchSize {
        end := i + batchSize
        if end > len(knowledgeIds) {
            end = len(knowledgeIds)
        }

        ids := knowledgeIds[i:end]
        tx := global.Db()
        tx.Table("mch_tk_questions as q")
        tx.Joins("INNER JOIN mch_tk_knowledges AS k ON k.q_vid = q.id")
        tx.Where("q.mid = ? AND q.bid = ? AND q.deleted_at IS NULL", 123, 456)
        tx.Where("k.mid = ? AND k.bid = ?", 123, 456)
        tx.Where("k.knowledge_id IN (?)", ids)
        tx.Where("k.deleted_at IS NULL")
        tx.Select("q.id")
        tx.Group("q.id")
        tx.Order("q.id DESC")

        var resp []uint
        err := tx.Limit(batchSize).Offset(i).Find(&resp).Error
        if err != nil {
            return nil, err
        }
        result = append(result, resp)
    }
    return result, nil
}

func main() {
    resp := make([]uint, 0)
    batches, err := batchProcess知识IDs(utils.String2UintSlice(req.KnowledgeIds, ","))
    if err != nil {
        global.Logger.Error("查询id失败", zap.Error(err))
        global.RespFailList(c, utils.RespList(0, 1, nil), "retrive_fail", nil)
        return
    }

    for _, batch := range batches {
        resp = append(resp, batch...)
    }

    // 使用resp进行后续处理...
}

这个版本的代码将查询分解为多个批次，并且每个批次都会应用适当的参数化查询。同时，确保您的数据库表上有正确的索引，这对于性能至关重要。如果问题仍然存在，那么可能是数据库服务器的配置或硬件限制导致的，此时可能需要进一步的性能分析和调优。